MySQL 5.6.38 optimized instance 1

MySQL 5.6.38 optimized instance 1

Introduction: in daily MySQL SQL statement optimization, you will always encounter various problems. Today, we have encountered a strange problem. Here we will record it for your convenience.

MySQL version: MySQL 5.6.38

1. SQL statement (keyword information has been desensitized ):

SELECT  id , name, headurl, intro, gender, location, job, birthday, source,created_at FROM user  WHERE name LIKE  '%name%'  ORDER BY created_at DESC LIMIT  0 ,  100 ;

2. Table Structure of the table user:

3. SQL Execution Plan and profile information and execution time:

4. optimization idea: In the execution plan, we can see that the index is not used because the SQL statement is a fuzzy query, after executing the SQL statement, we can see that it takes more than 99% of the time to create a Sorting index. We can see that the entire SQL statement only performs sorting ON THE created_at field, therefore, according to the Optimization idea, we need to create an index on the created_at field. The table structure after the index is created:

The red box shows the added index information.

5. The modified SQL Execution Plan, profile, and time consumption information are as follows:

We can see from the above execution plan that the returned data is reduced from 100 rows to rows, and the data volume is greatly reduced; however, after executing the SQL statement, I found that the time consumption was longer than 6 S, and when I analyzed the profile, I found that the time consumption for sending data was 6 s, here, the time consumed by sending data refers to sending data from the engine layer to the server layer or client layer.

I was surprised to find this situation. I don't know what happened to this result. I asked one of my senior engineers after I had no results in multi-party queries. After my detailed descriptions and experiments, he told me: the primary reason is that when the where condition is filtered and sorted, no results are found through the index. As a result, mysql obtains all the indexes for query and then performs a global scan in the retrieved table; if no index is added, the SQL statement returns directly to the table without scanning all the indexes.

To verify this result, I changed the where condition and compared it without adding the created_at field index:

Time consumed when no index is added:

100  rows  in  set  ( 2.53  sec)

Time used to add an index:

100  rows  in  set  ( 0.16  sec)

It is obvious that the speed of adding an index has increased a lot, and the query result is displayed.