Analysis on Causes of slow sorting SQL upgrade to MySQL 5.6

Analysis on Causes of slow sorting SQL upgrade to MySQL 5.6

Background:
After a service DB is upgraded from MySQL 5.5 to MySQL 5.6, the same SQL Execution time has soared from milliseconds to 20 seconds. The SQL text is as follows:
Select * from big_table as t
Where (t. plan_TYPE = 1 or t. plan_TYPE = 3)
And t. limit_TYPE in (10)
And t. xx_ID = 25773208367
And t. USER_ID in (133174222100)
Plan by t. gmt_create desc, t. ID desc limit 1, 10;

Exploration process:
View the current execution plan
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: t
Type: range
Possible_keys: idx_xx_id, idx_gmt_create
Key: idx_gmt_create
Key_len: 17
Ref: NULL
Rows: 6816016
Extra: Using index condition; Using where
1 row in set (0.00 sec)

Table Index Distribution
Primary key ('id '),
KEY 'idx _ xx_id '('xx _ id', 'Plan _ type', 'user _ id '),
......
KEY 'idx _ gmt_create '('limit _ type', 'xx _ id', 'gmt _ create ')
This SQL statement selects the index idx_gmt_create. Because it complies with the leftmost prefix policy, filesort is not used for sorting. Its access path is roughly as follows:
1. Scan the idx_gmt_create leaf node in reverse order to search for the tuples (limit_type, xx_id) =;
2 back to the table, verify that the primary key record corresponding to this tuples meets the constraints (plan_type, user_id) = (1 or 3, 133174222100). If yes, the count is added to 1; otherwise, the record is discarded, continue to scan the next tuples;
3. When the Count reaches 10, stop scanning and return the corresponding 10 records to the client;

Root @ 03:20:56> select limit_type, count (*) from big_table group by limit_type;
+ ------------- + ---------- +
| Limit_type | count (*) |
+ ------------- + ---------- +
| NULL | 226865 |
| Nine | 463346 |
| Ten | 13353116 |
+ ------------- + ---------- +
3 rows in set (3.13 sec)
As the bootstrap column of the composite index, the limit_type field has an astonishing low selectivity, which is one of the main reasons for slow query.
In this example, if other fields are powerful enough, the execution can be completed quickly even if limit_type = 10,

Check the distribution of xx_id, which is also malformed. Unfortunately, this SQL statement selects the one with the most candidate rows.
Root @ 04:01:12> select xx_id, count (*) from big_table where limit_type = 10 group by xx_id plan by xx_id desc;
+ ------------- + ---------- +
| Xx_id | count (*) |
+ ------------- + ---------- +
| 25773208367/13352433 |
| 25770261347 | 2 |
| 258809681/148 |
| 1 | 2100 |
+ ------------- + ---------- +
4 rows in set (5.79 sec)
If xx_id = 1, the SQL statement can be returned after a maximum of 2100 records are compared, and execution is completed soon;
Even if xx = 25773208367, if you can quickly find the primary key records that meet the non-index field constraints, the SQL statement will soon be executed. mysql is verifying the massive (limit_type, xx_id) = () after the tuples, 10 primary key records that meet the (plan_type, user_id) constraints at the same time are collected. Based on this, we can reverse the user_id that first meets all constraints, the query logic is as follows:
Select user_id, count (*) from big_table t
Where limit_type = 10 and xx_id = 25773208367
And (t. plan_TYPE = 1 or t. plan_TYPE = 3)
Group by user_id having count (*)> = 10 plan by gmt_create desc limit 1, 5;
Execution result
+ ------------ + ---------- +
| User_id | count (*) |
+ ------------ + ---------- +
| 1851362558 | 15 |
| 2118141658 | 11 |
| 1, 2641244918 | 14 |
| 2448823838 | 17 |
| 16375410 | 32 |
+ ------------ + ---------- +
5 rows in set (1 min 12.42 sec)
If you choose to replace 25773208367, for example, 1851362558, the execution plan has not changed. The SQL statement that needs to run for more than 20 seconds is executed within 200 milliseconds.

 

The execution plan of Version 5.5 is
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: t
Type: range
Possible_keys: idx_xx_id
Key: idx_xx_id
Key_len: 18
Ref: NULL
Rows: 116
Extra: Using where; Using filesort
Although filesort is used, few candidate rows are filtered by the index idx_xx_id, so the execution time is very short.

Solution
1. Modify the SQL statement and add the force index (idx_xx_id). This solution is not flexible enough;
2. Modify the SQL statement and change the sorting field from gmt_create to gmt_modified. idx_xx_id is selected because index sorting 5.6 cannot be used. This scheme may cause errors in returned data;
3. Modify the SQL statement and set t. change limit_TYPE in (10) to t. limit_TYPE> 9 and t. limit_TYPE <11. The optimizer considers that SQL does not meet the leftmost prefix of the index and does not use idx_gmt_create any more. This seems a bit cheap. It also indicates that the mysql optimizer is not smart enough.

Conclusion
5.6 The optimizer has made a lot of improvements. Taking this SQL statement as an example, we chose idx_gmt_create to save filesort. The reason why the operation slows down is that the table field data distribution is too uneven, this SQL statement unfortunately satisfies various pitfalls. This is an accident.

This article permanently updates the link address: