MySQL索引明明在却不走？六大索引失效场景与排查指南

先说一个我昨天刚处理完的线上问题。一条原本几十毫秒的订单查询某天突然跑到三秒多接口监控直接飘红。我第一反应不是去看代码而是把SQL粘到生产库的从机上执行EXPLAIN结果一眼看到typeALL走的是全表扫描。最气人的是这张表明明建了索引而且索引就在那儿放着优化器就是不用。这类“mysql索引条件都满足查询却不用索引”的问题我相信所有用过MySQL的人都遇到过。它不像语法错误那样会直接报错而是悄无声息地让查询变慢拖垮接口甚至在更新数据的时候把锁的范围放大连累整个业务。这篇就专门聊这个事索引条件明明在为什么MySQL不走索引以及遇到这种情况你该怎么一步步排查、怎么修。文章主要面向两类人一类是被慢查询折腾过的后端开发另一类是刚接触MySQL性能调优、想系统搞懂执行计划的新手。我会把判断依据、常见失效场景、优化器逻辑和实操案例串起来讲尽量用大白话让你看完之后能直接对着自己的SQL排查一遍。1. 先搞清楚“走不走索引”的判断依据1.1 EXPLAIN是最直接的照妖镜不管你是开发还是DBA排查“不走索引”第一步永远是同一件事用EXPLAIN看执行计划。这步别省也别靠猜。EXPLAIN SELECT * FROM orders WHERE order_no 202401010001;执行之后MySQL会返回一行结果里面有十来个字段重点看这几个type、key、rows、Extra。type访问类型从好到差大致是const eq_ref ref range index ALL。如果你看到ALL那基本就是全表扫描索引没起作用看到index说明扫描了整棵索引树虽然比全表好点但通常也不是最优。key实际用到的索引名。如果这一列是NULL说明这条SQL压根没用索引。rowsMySQL预估要扫描的行数。这个数字越大查询通常越慢。Extra额外信息会出现Using where、Using index、Using filesort这些关键字每一个都有特定含义。举个典型例子。某条SQL明明有索引EXPLAIN出来的结果却是这样的type: ALL key: NULL rows: 128000 Extra: Using where这就说明MySQL选择扫描12.8万行索引完全没参与。这样一条SQL数据量小的时候还扛得住一旦表涨到百万级必然出事。1.2 key_len和rows要配合着看很多新手只看type和key忽略了key_len但实际上key_len信息量很大。key_len表示索引中使用到的字节数。比如一个idx(a,b)联合索引如果key_len只有4字节说明只用到了第一个字段a去过滤如果key_len等于8字节说明a和b都用上了。这能帮你判断联合索引是否真的被完整利用。举个例子id是INT类型4字节name是VARCHAR(20)且utf8mb4字符集最多20*480字节加上变长字段的2字节记录一个idx(id, name)联合索引如果key_len4说明只用了id如果key_len86说明id和name都用到了。rows则是优化器估算的扫描行数。注意这是估算值不是精确值。如果rows显示几万但实际结果集只有几十条说明优化器对数据分布的判断出现了偏差这就是后面要讲的统计信息问题。另外看到Extra里出现Using filesort时要注意它不代表真的在磁盘上排序而是说MySQL额外做了一次排序操作没用到索引的有序性。这种SQL就算type是range排序环节照样拖慢速度。1.3 先确认“这是不是真的不该走索引”排查之前一定要破除一个执念不是所有SQL都必须走索引。有时候优化器选全表扫描其实是合理的选择。最常见的场景就是小表。一张表只有几百行数据全表扫描也就几个数据页的事走索引反而要额外的IO去读索引页和回表性能不一定更好。所以当你看到typeALL时先别急着骂优化器看一眼表数据量如果就几千行这可能就是最优解。另一个场景是“过滤后返回的数据占总量的比例太高”。比如一个性别字段如果查询条件是WHERE gender 1而全表90%的数据gender都是1那MySQL会认为走索引还不如直接扫全表划算。因为走索引查到记录之后还得回表去拿整行数据回表的随机IO成本比顺序扫描高得多。所以看到“不走索引”先冷静判断是真不该走还是本该走却因为某些原因没有走。这个判断决定了你接下来的排查方向。2. 最常见的六种索引失效现场2.1 对索引列使用函数或表达式这是最容易踩的坑也是热词里出现“mysql datepart”这类关键词的原因。很多时候我们写SQL会把日期处理成“天”再做比较SELECT * FROM payment_log WHERE DATE(create_time) 2024-06-01;看起来人畜无害但对MySQL来说DATE(create_time)是对索引列做了函数处理索引里存的是原始的datetime值没法直接用来匹配函数结果。于是优化器只能放弃索引对create_time这一列的每一行都执行DATE()函数然后逐个比较。正确的写法是范围查询SELECT * FROM payment_log WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;这样优化器可以直接在B树上做范围定位type能到range。同理索引列参与算术运算也是一样的道理WHERE age 1 18 -- 错误示范 WHERE age 17 -- 正确示范规则很简单索引列如果要和函数、表达式绑在一起索引就废了。2.2 隐式类型转换这个场景在开发环境几乎测不出来一到生产环境就暴雷。典型的例子是订单号字段。建表时用的是VARCHAR类型但应用层传过来的参数是数字类型比如Java里的LongSELECT * FROM orders WHERE order_no 202401010001;MySQL看到等号左边是字符串类型右边是数字会做隐式类型转换。问题在于转换的方向是把字符串转成数字也就是说每一行的order_no都要被CAST一下再比较。这一CAST索引又废了。怎么判断是不是这个问题看EXPLAIN里key是不是NULL再看SQL参数类型和字段定义是否一致。排查命令很简单SHOW CREATE TABLE orders\G看看order_no字段到底是什么类型。如果是VARCHAR应用侧就要保证传入的值是带引号的字符串。还有一类比较容易忽略的字符集问题。两张表关联的时候如果表A的字段是utf8mb4表B的字段是utf8MySQL在join时为了能比较会对其中一侧做字符集转换这个转换会让另一侧的索引失效。所以跨表关联时关联字段的字符集和排序规则最好保持一致。2.3 前导模糊查询模糊查询用不用得上索引取决于通配符的位置WHERE name LIKE 张% -- 可以用索引相当于范围查询 WHERE name LIKE %张% -- 不用索引前导模糊原因不复杂B树的索引是按照字段值顺序排列的“张%”可以定位到“张”开头的区间从前到后扫这个区间就行。但“%张%”意味着只要字符串里包含“张”的都算那只能把整棵索引树或者整张表扫一遍才能确认哪些值符合条件。如果业务确实需要包含匹配有几个处理思路数据量小就全表扫数据量大可以考虑全文索引不过中文分词是个麻烦事更通用的方案是把这类搜索需求丢给专门的搜索引擎。如果只是“以某个固定后缀结尾”的匹配还可以用反转字段加索引的方式但这属于偏门技巧能用上的场景不多。2.4 OR连接了非索引条件OR这个关键字非常容易让优化器“放弃治疗”。SELECT * FROM user WHERE name 张三 OR age 30;就算name上有索引age上没有索引MySQL也很难把“用索引查到张三”和“全表扫age30”的结果合并起来。多数情况下优化器会直接选择全表扫描因为它宁可简单粗暴也不愿意搞复杂的合并操作。解决方案有几种。最常见的是拆成两条SQL用UNION ALL合并SELECT * FROM user WHERE name 张三 UNION ALL SELECT * FROM user WHERE age 30;注意如果你不想看到重复记录用UNION去掉重复但UNION会附带一次排序去重的开销所以如果没有重复风险优先用UNION ALL。顺着热词里“mysql的or能去重吗”多说一句UNION本身有去重效果这是因为UNION会创建临时表并做唯一性检查。但在数据量大、且明确两个条件不会产生重复结果时UNION的开销是纯浪费UNION ALL才是正确选择。2.5 联合索引没遵守最左前缀原则联合索引idx(a, b, c)相当于建了(a)、(a,b)、(a,b,c)三套索引。如果查询条件里没有第一列a那这个联合索引大概率用不上。-- 假设联合索引 idx(user_id, status, create_time) SELECT * FROM orders WHERE status 1 AND create_time 2024-01-01;这个查询完全没带上user_id那idx这个索引对它是不可用的。因为B树先按user_id排序再按status排序最后按create_time排序。你现在跳过user_id直接按status查就相当于在电话簿里不查姓氏直接翻中间找名字叫“建国”的人没法二分开找。还有一点很多人忽视范围查询之后的列会失效。WHERE user_id 100 AND create_time 2024-01-01 AND status 1;create_time用了那create_time后面的status就用不到索引的有序性了key_len会告诉你status有没有吃进去索引。想要status也用上索引得调整条件顺序或者拆分查询。这块MySQL有一个叫“索引下推”ICP的特性能稍微缓解一下但它不是万能的不要把ICP当常规方案用。2.6 ORDER BY和GROUP BY导致的filesort这种问题一般EXPLAIN里type可能是range甚至ref看起来索引走了但Extra里冒出Using filesort查询还是慢。看一个标准错误案例SELECT * FROM orders WHERE user_id 100 ORDER BY create_time DESC;user_id和create_time上各自有单列索引。查询先用user_id索引定位到用户的所有订单然后要对create_time排序。但由于create_time并不是同一个联合索引里的第二列MySQL就得把结果集拿出来重新排一遍。解决办法是把排序字段也塞进联合索引里ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);这样查询时user_id过滤之后拿到的数据已经天然按create_time排好序Using filesort就消失了。GROUP BY本质上也是排序它需要在分组前先按分组字段排序所以同理能跟索引沾上边就尽量让分组字段成为联合索引的一部分。3. 优化器“不听话”时是统计信息和成本计算在作怪3.1 统计信息不准确导致误判你SQL写得没毛病索引也建了但优化器还是选全表扫描这时候就要怀疑统计信息过期了。MySQL优化器决定走哪条路不是拍脑袋而是根据表的统计信息估算成本。如果统计信息和实际数据差异太大估算就会出偏差。特别是频繁UPDATE、DELETE的表索引的基数统计会慢慢不准。解决办法很简单ANALYZE TABLE orders;执行完之后优化器手里的统计信息就被刷新了。我处理过好几次类似问题SQL没变、索引没变、数据量也没暴涨就是ANALYZE之后查询速度突然恢复正常。这种“玄学”问题的真实原因就是统计信息太久没更新。另外可以查一下索引的实际区分度SELECT COUNT(DISTINCT order_no) / COUNT(*) AS selectivity FROM orders;这个比值代表索引的选择性。如果比值太接近1说明索引列区分度高是优质索引如果比值很低比如0.001说明该列大量重复优化器认为用索引不如全表扫。3.2 回表成本压过索引收益有时候索引列本身没问题但你要查的字段太多导致回表次数太多优化器算了一笔账觉得全表扫描更便宜。举个例子表里有10个字段索引只建在create_time一列上。查询条件是WHERE create_time 2024-01-01要返回所有字段。假如这个条件命中了50%的数据那走索引意味着先在索引树里找到5万条记录的位置然后再回表读5万次完整行数据。每次回表是一次随机IO代价不小。此时优化器真有可能选全表扫描毕竟顺序读一遍物理文件也许更快。应对策略就是覆盖索引。让SQL要查的字段都包含在索引里查询就完全不需要回表ALTER TABLE orders ADD INDEX idx_cover (user_id, status, amount);SELECT user_id, status, amount FROM orders WHERE user_id 100;这种情况下EXPLAIN里Extra会显示Using index表示查询所需数据直接从索引树获取零回表。这也是为什么业内一直强调“不要用SELECT *”的原因之一查的字段越多回表成本越高索引被使用的概率越低。3.3 判断是不是优化器误判试试FORCE INDEX如果你确认SQL没写错、索引没问题、统计信息也更新了但优化器就是犟着不走索引这时可以短时间用FORCE INDEX验证一下SELECT * FROM orders FORCE INDEX (idx_create_time) WHERE create_time 2024-01-01;如果FORCE之后查询变快了说明确实是优化器成本评估出了问题或者索引设计有不合理的地方。但FORCE INDEX只能用来验证不能当成长期方案。原因很简单它把你的选择写死了一旦数据分布变化这个强制索引可能不再是好选择。并线上这种“打了补丁”的SQL非常容易被后来接手的人骂。更稳妥的做法是通过增加覆盖索引、改写SQL、调整参数比如optimizer_switch里的某些开关来引导优化器。实在没办法了才留FORCE INDEX而且要在代码注释里写清楚原因和有效期。3.4 更新统计信息之后还是不走还有一种情况统计信息是新的索引区分度也很好但优化器依旧不走索引。这时候要看看是不是SQL本身的写法导致无法使用索引比如对索引列用了函数、隐式转换这类情况前面已经说过。还有一种可能是两个索引都能用优化器选择了另一个索引你看到key不为NULL但性能还是差。这时把EXPLAIN里的possible_keys和key对比一下再用key_len确认是不是选到了不应该用的短索引。复杂的场景还可以用OPTIMIZER_TRACE看优化器的决策过程能看到每一步的成本计算数值。这属于进阶手段大多数情况下前面的排查步骤就够用了。4. “不走索引”的连带反应不只是查询慢4.1 锁的粒度可能被放大这个点很多开发容易忽略不走索引的UPDATE和DELETE危害比不走索引的SELECT大得多。InnoDB引擎的行锁是加在索引记录上的。如果一条UPDATE通过全表扫描定位目标行就会扫描过程中碰到大量行并且在扫描过程中对触达的行加锁。也就是说你要更新的明明只有一条记录但整个表的写入操作可能都被堵住。典型表现就是线上突然大量出现Lock wait timeout exceeded。一查processlist发现一条UPDATE因为条件列没走索引正在扫描全表并持有大量行锁其他会话只能干等。另外不走索引的更新还会扩大间隙锁的范围。比如WHERE status 1这个条件不走索引扫到哪就锁到哪间隙锁会把本来不该锁住的插入范围也锁上严重时直接阻塞业务写入。排查这类问题用SHOW ENGINE INNODB STATUS看锁信息或者查performance_schema.data_locks表能定位到具体锁等待链。核心对策依然是让UPDATE/DELETE的WHERE条件必须走索引宁可先SELECT再用主键去更新也不要把一个全表扫的更新扔到线上。4.2 慢SQL叠加大事务性能直接雪崩不走索引的SQL一旦出现在事务里问题会被放大。一个事务里可能有多条UPDATE每条都全表扫描事务又长时间不提交MVCC的版本链会被拉得很长。其他读请求为了找历史版本得沿着undo log往回找读放大效应非常明显。主从架构下还有另一个隐患主库上这类慢事务执行多久从库上SQL线程就要等多久。我见过一个案例主库一条不走索引的UPDATE跑了十来分钟结果从库延迟直接跳到几十分钟。这时候如果还有人去主库做备份或者大查询延迟只会越滚越大。所以监控MySQL的时候除了看慢查询日志还要关注Seconds_Behind_Master主从延迟秒数。一旦延迟异常优先去主库看有没有不走索引的大事务在跑。这类问题的根治手段依然是回到“让条件列走索引”这条根本原则上来。4.3 用慢查询日志和PROFILE锁定时段排查“不走索引”是否造成系统性影响慢查询日志是最直接的证据来源。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;这样配置之后超过1秒的SQL会被记录下来。注意线上环境long_query_time别设太短否则日志量很大。日志里重点关注Rows_examined和Rows_sentRows_examined语句实际扫描的行数。Rows_sent返回给客户端的结果行数。如果Rows_examined是十万Rows_sent只有一百那大概率就是没用上索引做了大量无效扫描。进一步分析可以用EXPLAIN的rows来对比。慢日志里记录的Rows_examined和EXPLAIN预估的rows如果差距很大基本可以断定统计信息不准或者执行计划有问题。5. 一次完整的真实排查案例和速查清单5.1 案例复盘订单查询从50ms恶化到2.8s某业务订单表数据量180万行表结构大致如下CREATE TABLE orders ( id BIGINT PRIMARY KEY, order_no VARCHAR(32), user_id BIGINT, status TINYINT, amount DECIMAL(10,2), create_time DATETIME, KEY idx_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB;应用反馈某个订单查询接口偶尔超时监控显示该SQL执行时间从平均50ms涨到2.8s。我用EXPLAIN排查EXPLAIN SELECT * FROM orders WHERE order_no 202401010001\G结果type: ALL key: NULL rows: 1800000 Extra: Using where索引就在那里但没走。原因是应用侧传参类型是Long而order_no是VARCHAR触发了前面讲的隐式类型转换。修复方案是让应用侧保证传字符串类型同时SQL里强制加引号SELECT * FROM orders WHERE order_no 202401010001;修复后EXPLAIN结果变成type: ref key: idx_order_no rows: 1查询时间回到40ms以内。这个案例里的教训其实不复杂建索引的人没错写SQL的人也没错错的是表结构和传参类型不一致。所以每次建索引之前先看一下对应字段的类型定义和ORM映射别让类型不匹配成为隐藏的定时炸弹。5.2 案例复盘报表统计的DATE函数惹的祸另一个案例是运营后台的日统计报表。SQL大概是SELECT user_id, COUNT(*) FROM payment_log WHERE DATE(create_time) CURDATE() GROUP BY user_id;表里create_time有索引但这条SQL每次跑都在全表扫描。EXPLAIN结果typeALLkeyNULL。原因就是DATE(create_time)这个函数包裹。改写后SELECT user_id, COUNT(*) FROM payment_log WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY GROUP BY user_id;改完之后type变成range扫描行数从几百万降到当天数据量查询时间下降非常明显。顺带提一句这个场景里GROUP BY user_id要想完全避免临时表建一个(user_id, create_time)或者(create_time, user_id)的联合索引会有帮助。具体选哪个方向取决于业务里哪种查询更频繁。5.3 速查清单从问题到定位十分钟搞定整理一个我常用的排查顺序遇到“索引存在但不走索引”直接照做检查项操作手段关键结论确认SQL是否该走索引EXPLAIN看type、rowstypeALL不一定错小表全表扫是正常索引列有无函数/运算看SQL的WHERE和JOIN条件索引列被函数包裹索引大概率失效字段类型和参数类型对比SHOW CREATE TABLE与传参类型类型不一致会导致隐式转换LIKE和OR的写法检查谓词结构前导%和OR非索引条件都会导致失效联合索引最左前缀用key_len判断跳过首列或范围后断列索引用不完整ORDER BY/GROUP BY查Extra里的filesort排序字段应在索引中统计信息是否过期ANALYZE TABLE统计陈旧会导致优化器误判回表成本评估看查询列考虑覆盖索引回表过多时优化器可能弃用索引FORCE INDEX验证临时强制走索引能验证优化器误判但别当长期方案这个清单我用了很长时间基本能覆盖80%以上的索引失效问题。5.4 我踩过的几个坑顺便分享一下关于FORCE INDEX我多说一句。刚开始接触这个功能的时候我觉得它简直是大杀器SQL一慢就FORCE效果立竿见影。后来有一次索引列的数据分布发生明显变化FORCE指定的索引变成了坑查询反而比全表扫描还慢。那次把我整怕了之后我给自己定了一条规矩FORCE INDEX只允许在排查时用不允许直接留在线上。如果一定要用必须同时提交一个问题单记录“为何优化器选错、后续如何根治”。还有就是建立索引不是越多越好。我见过一张表上建了十几个索引的写入性能惨不忍睹。每个索引都会拖慢INSERT、UPDATE、DELETE的写入速度要更新索引树占用额外磁盘空间。所以排查“不走索引”的时候也别急着加索引按照清单找出真正的原因很多时候加索引恰恰不是最优解。最后再分享一个冷门但有用的技巧。遇到复杂查询一时半会儿看不出问题时在WHERE条件里多写几个可能的裁剪条件甚至调整一下JOIN顺序都可能让优化器走回正轨。优化器这东西说聪明也聪明说笨也笨。它会老老实实按统计信息和成本模型来算你给它多一个“压低成本”的线索它就更容易选对路。