MySQL为什么有时候会选错索引
发表于：2026-11-03 作者：千家信息网编辑
千家信息网最后更新 2026年11月03日，本篇内容介绍了"MySQL为什么有时候会选错索引"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！今天在
千家信息网最后更新 2026年11月03日MySQL为什么有时候会选错索引
本篇内容介绍了"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为什么有时候会选错索引"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！
索引
总部
数据
排序
选择
代价
情况
速度
区分度
查询
检索
有时候
不同
有序
业务
信息
内容
因素
基数
宽窄
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
互联网科技
亿人帮互联网科技有限公司
互联网最新的科技
上海挚品互联网科技
广州宏创互联网科技
互联网科技ppt模板
四个字互联网科技公司取名
科技赋能互联网健康
剑昆互联网络科技有限公司临桂
广西互联网科技孵化中心
迪庆互联网科技招生
洛阳共同富裕互联网科技有限公司
佛山市拉法互联网科技有限公司
明优互联网科技有限公司
工业互联网展湾科技股份有限公司
广州梦谷互联网科技有限公司领导
恒生互联网科技趋势
宁波允成互联网科技有限公司
互联网企业和网络科技有限公司
互联网科技企业文化
南京欣网互联网络科技
伟宏互联网科技
天津摩尔互联网科技
互联网科技大会案例
太原新华互联网科技学校怎么样
深圳市运趣互联网科技有限公司
科技与互联网的利与弊
中国互联网有什么科技含量
互联网时代科技改变生活
互联网新科技动漫ppt素材
