Don't panic when something happens, record it first: mysql in slow query optimization

This article brings you the problem of mysql slow query optimization. I will take you step by step to analyze SQL, check and optimize. Let's take a look at it together. I hope it will be helpful to everyone.

Remember once mysql slow query optimization - a live demonstration of the production environment to-do list took 5~6 seconds to load the results; suddenly, the product The manager couldn't stand it anymore. As a senior developer with many years of experience, he thought about it, and his face was filled with laughter.

However, with many years of experience in the arena, don’t panic when things happen and just take a photo first.

Recommended learning: "MySQL Video Tutorial"

The first step is to analyze SQL

 ***from event i 
 left join project p on i.project_id = p.project_code 
 left join dict d on i.type_id = d.id 
 left join record re on re.incident_id = i.id
 left join type it on it.id = i.type_id 
 where i.version_flag = 0 and i.flow_id in (大量条件)***复制代码

When flow_id in access For a large number of conditions, SQL directly slows down, from the previous 80ms to 5.8 seconds. In addition, there are many related tables here.

The second step is to check the index and execute explain

When we check the index and find that re.incident_id and i.flow_id are not indexed, so, great joy, the problem is found Ok, build the index; however, when executing SQL, it is found that there are no eggs! As smart as I am, I opened explain directly and found that the type of record is all, which means there is no index.

why?why?

The third step is to check whether the field type, length and character type of the two associated fields are consistent

When comparing fields The type and field length were found to be exactly the same. After a short period of depression, I found a new clue - the character type of the id of the

event table is:

The character type of incident_id in the record table is:

Decisively and uniformly use utf8mb4 to maintain unity with the project team; explain again, and the time is as low as 1 second, manually.

The fourth step is to force the use of index operations

mysql will perform full-text search by default if a table is if the index base is too small , so if the business volume of the table is too large, but the index field is basically the same data or null, you still need to write the forced index in sql; use the forced index solution in sql and add force after left join index(alarm_id)——

The fifth step, IN is usually indexed

Only when the data behind IN is If the number of matches in the data table exceeds

30%, the entire table is scanned without using the index. Therefore, whether IN uses the index or not is related to the amount of subsequent data. A large amount of data can be processed using left join.

The above is the detailed content of Don't panic when something happens, record it first: mysql in slow query optimization. For more information, please follow other related articles on the PHP Chinese website!