Shulou(Shulou.com)05/31 Report--

In this issue, Xiaobian will bring you about the reasons for the slow query in MySQL. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

two principles

Two are faster than one:

*** SQL statement, where query statement null, this is caused by the database engine will not use the index, but take a full table scan, such a query will become very slow. If you use 0 instead of null, which is the second SQL statement, the database query runs faster.

Accurate faster than the full table:

Obviously, *** SQL statements execute much faster than the second SQL statement. Because *** SQL statements use precise queries, index queries; the second SQL statement is to retrieve all the data in the table once, equivalent to a full table query, which is very time-consuming and resource-intensive.

The amount of database data queried becomes large

When you query a large amount of data in SQL Server, it will also cause your database to be slow.

For example: I have a data amount of millions of goods table, now I need to check the information of some goods inside, such a query will be very slow oh! For example:

There are millions of data in the table. To find the commodity information you need in this massive amount of data, if you write such SQL statements, the query speed must be slow!

Solution:

Use index:

//--Create index

In this case, query time can be significantly increased. Because of the use of indexes, you can quickly find the information you need in a large amount of data, rather than retrieving the information you need one by one in millions of tables.

Deadlock in database

We know that when a deadlock occurs in a program, the program gets stuck in that position and becomes very slow, very slow, or not moving at all. So, when your SQL statement deadlocks, database queries will also be slow!

Database deadlock phenomenon refers to: two or more SQL statements, competing to access the same data table, and in *** days SQL statement access table, while the data table to lock. The second and third SQL statements will not be able to access the table and wait. If there is no human intervention, it is always in this state, so it is called deadlock.

Solution:

This SQL statement sending deadlock phenomenon is generally caused by bugs. Modify the logical order of the program to give an appropriate logical order of program execution. Avoid locking two resources at the same time. Put SQL statements in a sequential order.

I/O execution response time is too long

We all know how barrels work. It's not the long boards that determine how much water they hold, it's the short boards. Similarly, for databases, the hardware of computer systems-disk I/O-is a short board. During program execution, we often find that I/O in the system has been constantly executing, while the CPU is waiting at leisure. This occurs because disk I/O(read/write speed) is far behind CPU processing speed.

The above is what the reasons for the slow query in MySQL shared by Xiaobian are. If there are similar doubts, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.