| |
First, Introduction
Find a slow query, the query time 1.57s, check the presence of the correct combination of the index table structure where field conditions, under the right circumstances optimizer should choose the combination of the index, rather than why can cause slow query when slowlog inspection of a business database yet ? Let us look at this article to slowly analysis.
Second, the analysis
Case in MySQL database version 5.6.16 will produce sql environment make the appropriate changes, where conditions remain unchanged. Readers can test other versions.
root @ rac1 10:48:11> explain select id,
-> Gmt_create,
-> Gmt_modified,
-> Order_id,
-> Service_id,
-> Seller_id,
-> Seller_nick,
-> Sale_type
-> From lol
-> Where seller_id = 1501204
-> And service_id = 1
-> And sale_type in (3, 4)
-> And use_status in (3, 4, 5, 6)
-> And process_node_id = 6 order by id desc limit 0,20 \ G
*************************** 1. row ******************** *******
id: 1
select_type: SIMPLE
table: lol
type: index
possible_keys: idx_sellerid, idx_usestatus_saletype, idx_sellerid_saletype, idx_sidustsvidtype
key: PRIMARY
key_len: 8
ref: NULL
rows: 3076
Extra: Using where
1 row in set (0.00 sec)
analysis
MySQL execution plan chosen by the main key to access the data. Note that the implementation plan is the access type index, while the index means that the SQL query when the secondary index, the index of the two full-index scan, there is no filtering
This behavior is unreasonable, because where conditions are contained in the query, a reasonable implementation of the plan should be access type range.
We forced the index to see the results
root @ rac1 10:48:07> explain select id,
-> Gmt_create,
-> Gmt_modified,
-> Order_id,
-> Service_id,
-> Seller_id,
-> Seller_nick,
-> Sale_type
-> From lol force index (idx_sidustsvidtype)
-> Where seller_id = 1501204
-> And service_id = 1
-> And sale_type in (3, 4)
-> And use_status in (3, 4, 5, 6)
-> And process_node_id = 6 order by id desc limit 0,20 \ G
*************************** 1. row ******************** *******
id: 1
select_type: SIMPLE
table: lol
type: range
possible_keys: idx_sidustsvidtype
key: idx_sidustsvidtype
key_len: 19
ref: NULL
rows: 5178
Extra: Using where; Using filesort
1 row in set (0.00 sec)
analysis
Plus mandatory implementation plan is in line with the index after the expected time to perform sql decreased from 1.57s to 0.01s. Therefore, we speculate is the optimizer to choose an index when there is a problem
We found that combination of Source and optimize_trace first stage optimization when the optimizer did choose the idx_sidustsvidtype and opted range accessed because sql statements contained in order by, the optimizer attempts to optimize order by limit the time
Clear the way of quick access to saved variables (the original is preserved range, but was asked to empty), eventually found the cost of using the sort index (here id) is higher than the composite index (here idx_sidustsvidtype) when, chose idx_sidustsvidtype
But the tragedy is that this time the right of access method has been emptied, can not be restored, and this is the root cause of this bug # 78993 of.
According to the analysis, we can also use another workaround ---- remove the order by. Of course, this all business development and communication invasion must confirm sql result set is unique, if not the only, or to use other methods.
root @ rac1 10:48:15> explain select id,
-> Gmt_create,
-> Gmt_modified,
-> Order_id,
-> Service_id,
-> Seller_id,
-> Seller_nick,
-> Sale_type
-> From lol
-> Where seller_id = 1501204
-> And service_id = 1
-> And sale_type in (3, 4)
-> And use_status in (3, 4, 5, 6)
-> And process_node_id = 6 \ G
*************************** 1. row ******************** *******
id: 1
select_type: SIMPLE
table: lol
type: range
possible_keys: idx_sellerid, idx_uts_stp, idx_sid_stpe, idx_sidustsvidtype
key: idx_sidustsvidtype
key_len: 19
ref: NULL
rows: 5178
Extra: Using where
1 row in set (0.00 sec)
Third, the summary
a modifying SQL, add the correct hint.
b's order by removing unnecessary needs and developing communication confirm whether the impact of the business logic.
c modify optimization bug, keep multiple access paths, do not clean up the quick access method saved variables found when the price is higher than the composite index orderby, you can choose the best access path. |
|
|
|