MySQL为什么有时候会选错索引

共计 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 小编将为大家输出更多高质量的实用文章！