溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“MySQL為什么有時候會選錯索引”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
今天在生產環境中看到一個慢SQL，是個核心業務表，數據1300萬+
看一下表索引：
mysql>show index from `order` +-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+ | order | 0 | PRIMARY | 1 | id | A | 10493505 | | | | BTREE | | | | order | 0 | uidx_order | 1 | order_seq | A | 10512924 | | | | BTREE | | | | order | 1 | idx_user | 1 | user_id | A | 1995181 | | | YES | BTREE | | | | order | 1 | idx_shop | 1 | shop_id | A | 53933 | | | YES | BTREE | | | | order | 1 | idx_out_channel | 1 | out_channel | A | 524 | | | YES | BTREE | | | | order | 1 | idx_out_channel | 2 | out_order_no | A | 10512924 | | | YES | BTREE | | | | order | 1 | idx_order_time | 1 | order_time | A | 9867734 | | | | BTREE | | | | order | 1 | idx_update_time | 1 | update_time | A | 8305698 | | | | BTREE | | | | order | 1 | idx_create_time | 1 | create_time | A | 9951390 | | | | BTREE | | | +-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+ 返回行數：[9]，耗時：4 ms.
mysql>SELECT id,order_seq,user_id FROM `ORDER` WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+---------------------+----------------------------------+ | id | order_seq | user_id | +--------------+---------------------+----------------------------------+ | 6068129 | 20161128183300861 | d4b0c318b28a46968718dddbaf4775c0 | | 6118611 | 20161206171509550 | d4b0c318b28a46968718dddbaf4775c0 | | 6885081 | 20170427104933189 | d4b0c318b28a46968718dddbaf4775c0 | | 7720299 | 2017101718252243 | d4b0c318b28a46968718dddbaf4775c0 | | 10319613 | 201905281103186182 | d4b0c318b28a46968718dddbaf4775c0 | | 505498 | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 | | 10840144 | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 | +--------------+---------------------+----------------------------------+ 返回行數：[7]，耗時：18534 ms.
耗時18s，這個查詢速度肯定是不能接受的。
我們看一下執行計劃：
mysql>EXPLAIN SELECT id,order_seq,user_id FROM `ORDER` WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+ | 1 | SIMPLE | ORDER | | index | idx_user | idx_order_time | 5 | | 2705 | 0.01 | Using where | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+
執行計劃中看到，這個SQL走索引idx_order_time，根據經驗判斷，此索引效率很差。而掃描行數為2705，慢日志顯示掃描行數為13,347,074，二者相差甚遠，那么為什么會出現如此大的差異呢？
選擇索引是優化器的工作。而優化器選擇索引的目的，是找一個最優的執行方案，并用最小的代價去執行語句。在數據庫里面，掃描行數是影響執行代價的因素之一。掃描的行數越少，意味著訪問磁盤數據的次數越少，消耗的CPU資源越少。
當然，掃描行數并不是唯一的判斷標準，優化器還會結合是否使用臨時表，是否排序等因素進行綜合判斷。掃描行數是怎么判斷的？
MySQL在真正執行SQL之前，并不能準確的判斷滿足這個條件的數據有多少行，只能按統計信息來估算行數。
索引的統計信息就是索引的“區分度”，一個索引不同的值越多，這個索引的區分度就越好，而一個索引上不同的值的個數，我們稱之為“基數”，基數越大，索引的區分度越好。
若強制使用idx_user索引，看下執行情況：
mysql>SELECT id,order_seq,user_id FROM `ORDER` force index(idx_user) WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+---------------------+----------------------------------+ | id | order_seq | user_id | +--------------+---------------------+----------------------------------+ | 6068129 | 20161128183300861 | d4b0c318b28a46968718dddbaf4775c0 | | 6118611 | 20161206171509550 | d4b0c318b28a46968718dddbaf4775c0 | | 6885081 | 20170427104933189 | d4b0c318b28a46968718dddbaf4775c0 | | 7720299 | 2017101718252243 | d4b0c318b28a46968718dddbaf4775c0 | | 10319613 | 201905281103186182 | d4b0c318b28a46968718dddbaf4775c0 | | 505498 | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 | | 10840144 | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 | +--------------+---------------------+----------------------------------+
查詢速度還是很快的，看一下執行計劃：
mysql>explain SELECT id,order_seq,user_id FROM `ORDER` force index(idx_user) WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ | 1 | SIMPLE | ORDER | | ref | idx_user | idx_user | 163 | const | 77706 | 1 | Using index condition; Using where; Using filesort | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ 返回行數：[1]，耗時：4 ms.
如果換成數據行數少一些的user_id
mysql>EXPLAIN SELECT id,order_seq,user_id FROM `ORDER` WHERE delete_flag = 0 AND user_id = '1e41c833fc6f4f57b490a4627a4170dc' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ | 1 | SIMPLE | ORDER | | ref | idx_user | idx_user | 163 | const | 13 | 1 | Using index condition; Using where; Using filesort | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ 返回行數：[1]，耗時：4 ms. mysql>SELECT id,order_seq,user_id FROM `ORDER` WHERE delete_flag = 0 AND user_id = '1e41c833fc6f4f57b490a4627a4170dc' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+---------------------+----------------------------------+ | id | order_seq | user_id | +--------------+---------------------+----------------------------------+ | 10397123 | 2019092523044218361 | 1e41c833fc6f4f57b490a4627a4170dc | +--------------+---------------------+----------------------------------+ 返回行數：[1]，耗時：4 ms.
對比一下兩個user_id對應的數據量：
返回行數：[1]，耗時：4 ms. mysql>select count(*) from order01 where user_id='1e41c833fc6f4f57b490a4627a4170dc' +--------------------+ | count(*) | +--------------------+ | 15 | +--------------------+ 返回行數：[1]，耗時：4 ms. mysql>select count(*) from order01 where user_id='d4b0c318b28a46968718dddbaf4775c0' +--------------------+ | count(*) | +--------------------+ | 38611 | +--------------------+ 返回行數：[1]，耗時：14 ms.
總結：在此業務場景中，MySQL優化器認為檢索38000行數據然后進行排序要比檢索15行數據排序代價大得多，所以選擇了有序的索引idx_order_time，但未必是最快的執行計劃。
但是，此處還有一個疑問，如果對于user_id：d4b0c318b28a46968718dddbaf4775c0，不使用limit分頁，執行計劃是什么樣呢？
mysql>SELECT * FROM ORDER01 WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time desc +--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+ | id | order_seq | order_type | order_flag | user_id | user_mobile | user_nick | shop_id | shop_name | pay_status | pay_time | receiver_address_id | receiver_name | receiver_mobile | receiver_address | cancel_time | cancel_reason | channel | out_channel | out_order_no | out_store_name | order_time | over_time | display_status | order_status | sale_channel | sale_mode | remark | delete_flag | create_time | update_time | +--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+ | 11153421 | 201911091339555506 | 1 | 1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545 | 總部-客服-補單 | 29e541d6da9b4aae8957409ca03c6670 | 清悠 | 1 | 2019-11-09 13:40:10 | 2666265 | 總部-客服-補單 | 13718903545 | 東城區 王府井 王府井 總部補單 | | 0 | | 0 | 201911091339555506 | | 2019-11-09 13:39:55 | | 2 | 200 | 1 | 1 | | 0 | 2019-11-09 13:39:55 | 2019-11-09 13:40:10 | | 7720299 | 2017101718252243 | 1 | 1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545 | 總部-客服-補單 | ad41dba7bf5c4b69b03e0222878cb2b0 | 蝶舞 | 1 | 2017-10-17 18:25:26 | 2282099 | 總部-客服-補單 | 13718903545 | 2號線; 地鐵7號線 華強北 總部補單 | | 0 | | 0 | 2017101718252243 | | 2017-10-17 18:25:22 | | 2 | 200 | 1 | 1 | | 0 | 2017-10-17 18:25:22 | 2017-10-17 18:25:22 | | 6885081 | 20170427104933189 | 1 | 1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545 | 總部-客服-補單 | c6092260f92643098f7f56e68560d8c0 | 木蘭花 | 1 | 2017-04-27 10:49:39 | 2264946 | 總部-客服-補單 | 13718903545 | 天河北商圈 | | 0 | | 0 | 20170427104933189 | | 2017-04-27 10:49:33 | | 2 | 200 | 1 | 1 | | 0 | 2017-04-27 10:49:33 | 2017-04-27 10:49:33 | | 6118611 | 20161206171509550 | 1 | 1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545 | 總部-客服-補單 | 7a0cd4d60f52423fb757b0be1ab55be6 | 娟子 | 1 | 2016-12-06 17:15:12 | 1904075 | 總部-客服-補單 | 13718903545 | 廣東省深圳市南山區深南大道 科技園 | | 0 | helijia | 0 | 20161206171509550 | | 2016-12-06 17:15:09 | | 2 | 200 | 1 | 1 | | 0 | 2016-12-06 17:15:09 | 2016-12-06 17:15:09 | | 6068129 | 20161128183300861 | 1 | 1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545 | 總部-客服-補單 | f6f4612493654695ac4c6bac6df67672 | 美天 | 1 | 2016-11-28 18:33:03 | 1544109 | 總部-客服-補單 | 13718903545 | 青羊區金河路口寬窄巷子 寬窄巷子 | | 0 | helijia | 0 | 20161128183300861 | | 2016-11-28 18:33:00 | | 2 | 200 | 1 | 1 | | 0 | 2016-11-28 18:33:00 | 2016-11-28 18:33:00 | +--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+ 返回行數：[5]，耗時：152 ms. mysql>explain SELECT * FROM ORDER01 WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time desc +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ | 1 | SIMPLE | ORDER01 | | ref | idx_user | idx_user | 163 | const | 75800 | 1 | Using index condition; Using where; Using filesort | +--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+ 返回行數：[1]，耗時：4 ms.
查詢速度很快，執行計劃走了user_id字段的索引。為什么會出現這樣的情況呢？
查閱了相關資料，對于order by limit這樣的排序，當檢索到的數據較多的時候，排序消耗是很大的，這個時候由于優化器選擇了有序的idx_order_time而導致執行索引選擇錯誤。
優化辦法：
1、強制使用索引idx_user；
2、創建組合索引idx_uid_ordertime(user_id,order_time)
mysql>alter table `ORDER` add index idx_uid_ordertime(user_id,order_time) 執行成功，耗時：60334 ms. mysql>SELECT id,order_seq,user_id FROM `ORDER` WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+---------------------+----------------------------------+ | id | order_seq | user_id | +--------------+---------------------+----------------------------------+ | 6068129 | 20161128183300861 | d4b0c318b28a46968718dddbaf4775c0 | | 6118611 | 20161206171509550 | d4b0c318b28a46968718dddbaf4775c0 | | 6885081 | 20170427104933189 | d4b0c318b28a46968718dddbaf4775c0 | | 7720299 | 2017101718252243 | d4b0c318b28a46968718dddbaf4775c0 | | 10319613 | 201905281103186182 | d4b0c318b28a46968718dddbaf4775c0 | | 505498 | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 | | 10840144 | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 | +--------------+---------------------+----------------------------------+ 返回行數：[7]，耗時：86 ms. mysql>explain SELECT id,order_seq,user_id FROM `ORDER` WHERE delete_flag = 0 AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' AND display_status = 2 ORDER BY order_time asc LIMIT 0,20 +--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+ | 1 | SIMPLE | ORDER | | ref | idx_user,idx_uid_ordertime | idx_uid_ordertime | 163 | const | 72772 | 1 | Using index condition; Using where | +--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+ 返回行數：[1]，耗時：4 ms.
“MySQL為什么有時候會選錯索引”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
