共計 17133 個字符，預計需要花費 43 分鐘才能閱讀完成。

本篇內容介紹了“MySQL 為什么有時候會選錯索引”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

今天在生產環境中看到一個慢 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 
 `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 
 `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 
 `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 
 `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 
 `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 
 `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 * 
 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 * 
 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 
 `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 
 `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 為什么有時候會選錯索引”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！