1. 一次线上慢查询引发的索引失效排查
上周五下午，我正在改一个报表接口，突然告警短信连响了三声：订单表的一条查询SQL平均响应时间从55ms飙升到6.8s。跑过去看了慢查询日志，定位到一条每天要跑几十万次的查询，原本是毫秒级完成的，现在却在全表扫描。这个问题的根源，就是MySQL索引失效。下面把这个排查过程完整复盘一遍，希望能给你一点参考。索引失效不是什么高深的理论，但它在生产环境里的杀伤力往往超过我们的想象，一条原本该走索引的查询变成全表扫描，代价可能就是几秒甚至几十秒的响应延迟，直接影响用户体验。
1.1 慢查询现场还原
当时的SQL大概长这样：
SELECT id, order_no, user_id, amount, status, create_time FROM order_detail WHERE DATE(create_time) = '2024-11-25' AND status = 1 ORDER BY id DESC LIMIT 20;order_detail表有接近两千万行数据，create_time上建有普通索引，status是一个普通int列。正常情况下，这个查询应该先在create_time索引上定位到当天所有记录，再过滤status，最后排序返回20条。可现实是，执行计划里的type字段显示为ALL，rows预估接近两千万，Extra列里还有Using where和Using filesort。
我当时的第一反应是：索引是不是没建上？但检查之后发现create_time索引明明存在。后来才反应过来，问题出在WHERE条件里的DATE函数上——它对索引列做了函数运算，MySQL无法利用B+树有序性去范围扫描，只能把索引列的所有值都“加工”一遍再过滤，于是干脆选择了全表扫描。从原理上讲，B+树索引的有序性建立在“列本身的原始值”上，一旦套上函数，索引里存储的原始键值和查询条件里的加工后值就无法直接对应，优化器自然无从下手。
1.2 定位失效方式的三个关键动作
这个问题的定位并不复杂，但当时有效帮助我快速收敛的三个动作，你可以先记下来。
第一，打开慢查询日志和当前执行的日志开关，把具体SQL和实际执行计划抓出来。尤其在生产环境，不要凭记忆猜，直接用SHOW INDEX FROM order_detail确认索引是否存在、字段和顺序对不对。也可以用SET profiling=1开启profiling，拿到更详细的每个步骤耗时。如果慢查询日志里同时出现了多条类似的SQL，最好用pt-query-digest这类工具做一次聚合分析，找出共性，往往几个关键字就能暴露问题。
第二，用EXPLAIN看执行计划。重点看type字段是不是从const、ref掉到了ALL，看possible_keys是否出现了但key是空，这两条是最直观的索引失效信号。我习惯同时打开EXPLAIN ANALYZE（MySQL 8.0.18+支持），它能反馈每个算子实际耗时和行数，比只看估算值要准确得多。有一次排查时EXPLAIN显示rows只有几百，实际跑起来却扫了几百万行，就是统计信息和真实情况严重脱节，这时依赖估算值很容易被误导。
第三，把SQL中的条件逐个去掉，做“最小复现”。比如去掉DATE(create_time)只留下create_time BETWEEN...，如果执行计划立刻从ALL变成了range，那基本就锁定罪魁祸首了。做这一步时要注意事务隔离级别和当前数据量，最好在一台与生产环境硬件接近的从库上验证，避免在主库上做压力测试干扰业务。如果条件本身就在同一张表，也可以用STRAIGHT_JOIN强制连接顺序，但那只适合调优阶段，不适合上线。
2. 索引失效的高频操作图谱：从函数、隐式转换到前导通配符
上面的案例只是冰山一角。在MySQL里，能让索引失效的操作五花八门，但归结起来，大部分都逃不出下面这几个典型场景。我平时会把这套“失效图谱”存在脑子里，每次写完SQL先对照过一遍，命中率能降低七成。下面逐个拆解，每个场景都会附上实际SQL和优化思路，你能直接对照着手里的查询去排查。
2.1 对索引列做函数运算
这是最典型的失效原因，也是生产环境出现最多的。比如：
WHERE DATE(create_time) = '2024-11-25' WHERE YEAR(create_time) = 2024 WHERE SUBSTRING(name, 1, 3) = 'abc'只要对索引列套了函数，MySQL的优化器就没法直接使用索引去二分查找，因为索引里存的是原始值，而查询条件是函数运算后的结果。一个例外是MySQL 8.0支持函数索引，你可以专门为DATE(create_time)建一个表达式索引，但这对现有查询并不一定划算，因为函数索引会增加写入时的计算开销，也会占用额外的磁盘空间。最直接的方案是把SQL改写成语义等价的形式：
WHERE create_time >= '2024-11-25 00:00:00' AND create_time < '2024-11-26 00:00:00'这样就能命中索引，结果范围也一样。这里有一个容易被忽视的坑：很多人觉得DATE(create_time) = '2024-11-25'只是把时间“截断”了一下，但MySQL的索引排序是基于完整时间值的，哪怕只是截断到天，索引树上的物理顺序也帮不上忙。所以与其事后改写，不如一开始就避免对索引列做任何包装。
2.2 隐式类型转换捣乱
当字段是varchar类型，你却拿一个数字去比较时，MySQL会把两者都转成数字再比较。这个转换发生在索引列上时，索引就失效了。比如：
WHERE user_mobile = 13812345678 -- user_mobile是varchar WHERE order_no = 202411250001 -- order_no是varchar解决办法就两个字：保持一致。要么查询参数里带上引号，要么把列类型改成bigint。有一点值得多说一句：如果你有user_mobile这类号码字段，最好直接用bigint存，省空间还不会被类型转换，但要注意手机号如果用int可能溢出。实际工作中我还遇到过一种更隐蔽的情况：字段是utf8mb4字符集，应用传参是utf8，或者连接串character_set不同，虽然不会直接报错，但会在比较时产生隐式字符集转换，同样让优化器放弃索引。排查这类问题，可以看EXPLAIN里的key_len有没有异常变短，再检查连接参数。
2.3 前导模糊查询
LIKE '%keyword'之所以失效，是因为B+树索引是有序排列的，只能从前向后匹配。当你把通配符放在最前面，等于在最开始就破坏了有序匹配的起点。比如：
WHERE name LIKE '%张' WHERE content LIKE '%MySQL索引%'如果业务真的需要这种搜索，别指望普通索引，应该引入全文索引（MySQL自带全文索引或ElasticSearch）。但如果只是需要“以某个词结尾”的少数查询，可以尝试反转字段存储：
WHERE reversed_name LIKE '张%' -- reversed_name存储的是倒过来的字符串这会牺牲一定的写入复杂度，但能换来索引利用。还有一个小技巧：如果只是需要匹配后缀，并且匹配字符串比较短，也可以在索引列上使用LIKE '张%'，也就是把匹配词倒过来变成前缀匹配，但前提是你能接受额外维护一个反转列。如果业务对实时性要求不高，更推荐用ES或者专门的搜索引擎，因为他们对倒排索引的支持比MySQL成熟得多。
2.4 OR连接了非索引列
OR是一个“或”关系，意味着两边都必须判断。只要其中一个条件没有索引，MySQL就倾向于放弃索引，走全表扫描。例如：
WHERE status = 1 OR type = 2 -- status有索引，type没索引即使两个条件都有索引，优化器也不一定能准确合并索引得到结果（MySQL对索引合并的优化有限），很多时候还是全表扫描更“划算”。改造方案是把OR拆成两个查询用UNION ALL，或者确保所有参与OR的列都建了联合索引，但后者依赖SQL语义，并不总是可行。举个例子，如果业务场景是“查询某一个用户当天创建的订单或者当天下过单的用户”，这种条件本身就是两段独立逻辑，应该拆成两个SQL分别查，再在应用层做合并，而不是硬塞到一个SQL里。使用UNION ALL时要注意两个子查询是否会重复数据，如果需要去重再用UNION，但UNION的排序和去重成本通常不低，要谨慎选择。
2.5 NOT IN、NOT EXISTS与不等于
<>和NOT IN往往会让MySQL放弃索引扫描。原因是B+树索引组织方式适合等值和范围查询，而要找出所有不等于某值的记录，相当于扫描全树的大部分节点；再加上统计信息可能误判这张表里99%都是“不等于给定值”，优化器自然选择全表扫描。
不过这里有个经验之谈：如果你的表上NOT IN的值只占极少比例，并且MySQL统计信息足够准确，它也有小概率走索引。所以别一棍子打死，要看执行计划。但对于绝大多数场景，<>强制走索引往往比全表更慢，我们不要把“索引失效”绝对化。换个思路，如果业务上需要排除某几个状态，可以试着把条件改成IN一个正面的状态列表，例如WHERE status IN (1, 2, 3)，这样索引利用机会会大很多。如果确实要排除，也可以考虑用LEFT JOIN加IS NULL的方式改写，但要注意数据量、连接顺序和额外开销，不一定总是更好。
2.6 索引列参与了数值运算
和函数一个道理，WHERE price * 100 > 500会把price列先算出结果再去比较，索引自然帮不上忙。正确的写法是把运算移到等号另一侧：
WHERE price > 500 / 100建议把这类SQL归类为“标准写法”，在代码评审时重点检查。有人可能会问：“MySQL优化器那么智能，能不能自动把price * 100 > 500改成price > 5？”很遗憾，MySQL的优化器并不会这么智能，尤其是当表达式涉及列和常量混算时，它无法保证做等价变形一定不改变浮点精度或整型溢出，所以宁可保守地全表扫。这时候人工改写是最可靠的。
3. 联合索引和排序场景中最容易踩的失效坑
如果说上面那些是“单列索引的明枪”，那联合索引绝对是“暗箭”。很多失效并不是SQL写错了，而是你对联合索引的理解不够深。这一节我准备把联合索引、范围查询、排序和分组这四个场景放在一起讲，因为它们的底层逻辑是相通的。
3.1 最左前缀：联合索引的第一条军规
联合索引(a, b, c)，实际创建的是一个按a、b、c依次排序的复合结构。MySQL可以命中索引的写法必须符合最左前缀原则：查询条件里必须包含a，并且是“从左到右连续”的。
最容易犯的错是跳过最左列直接查c：
WHERE c = 'xxx' -- 无法命中(a,b,c)索引还有的人喜欢把条件顺序打乱：WHERE b = ? AND a = ?。这点MySQL优化器能做优化，即使顺序不同，它也会重排成a = ? AND b = ?，所以只要最左列存在就行。但如果你给的是WHERE b = ? AND c = ?，缺失了a，就是彻底失效。还有一个容易被忽略的场景：WHERE a IN (...) AND b = ?。如果你在a列用了IN，它依然会走索引，但是b列的后续匹配会受到一些影响，因为IN本质上是一个区间集合，优化器把它当成多区间处理，b列的有序性在每个区间内仍然可以保持，所以严格说b也可以使用，但要看统计和成本。这里建议你直接看EXPLAIN的key_len判断实际用了几个字段，不要凭感觉。
3.2 范围条件会切断后续列的使用
继续用(a,b,c)举例：
WHERE a = 1 AND b > 2 AND c = 3这条SQL里，a和b可以用到索引，但b的范围判断影响到了c列：因为当b是一个不连续的范围时，c在b范围内的排序已经失去意义，MySQL无法继续精确匹配c，所以c的索引部分被浪费了。
这不是“索引整个失效”，而是“部分失效”。很多同学在排查时看到type=range以为没问题，但看key_len会发现它其实比完全等值匹配短了一截。要优化，可以把b>2改写成b in (3,4,5)这种枚举值列表，或者调整联合索引顺序，把等值判断的列放在前面。举例来说，业务常见查询是“按状态和时间范围查数据”，那索引可以设计成(status, create_time)，让status作为等值前缀，时间作为范围后缀，这样两部分都能用上；如果设计成(create_time, status)，那status就会因为create_time的范围而被浪费。设计联合索引时，一定要先列出所有高频查询的条件，把所有等值条件列优先放在最前面，范围条件放后面。
3.3 排序字段忘掉最左前缀，filesort悄悄出现
ORDER BY同样要遵守最左前缀。比如联合索引(a, b, c)，以下排序是可以避免文件排序的：
ORDER BY a, b, c ORDER BY a DESC, b DESC, c DESC WHERE a = 1 ORDER BY b, c但下面这些就会触发filesort：
ORDER BY b, c ORDER BY a, c WHERE a = 1 ORDER BY c, b为什么WHERE a = 1 ORDER BY c, b也不走索引？因为索引顺序是a,b,c，在a等值的情况下，b的排序仍然生效，但你不排序b却排序c，和索引的有序性冲突。这里有个隐藏点：如果所有排序字段方向不一致，比如一个升序一个降序，MySQL 8.0之前也无法利用索引；8.0仅对特定方向支持。写排序时需要多看一眼索引定义。另外还要注意，如果SQL里同时有WHERE过滤和ORDER BY，MySQL会先尝试用索引完成WHERE过滤，再用同一索引完成排序。如果WHERE条件用范围消耗掉了索引的后续列，排序阶段就可能重新面临filesort。这种时候可以考虑索引（等值列, 排序列），把排序需求直接焊死在索引里。
3.4 分组和去重一样受制于索引顺序
GROUP BY在逻辑上会先排序再分组，所以它和ORDER BY一样依赖最左前缀。一个典型的失效场景是：
SELECT status, category, COUNT(*) FROM orders GROUP BY category, status如果索引是(status, category)，那么这里排序顺序就颠倒了，触发临时表和filesort。你可以在EXPLAIN的Extra里看到Using temporary; Using filesort，这就是索引失效带来的连锁反应。临时表可能存储在内存或磁盘上，一旦数据量超过tmp_table_size就会溢写到磁盘，性能急剧下降。优化思路是调整索引顺序为(category, status)，或者把分组查询改写为先用子查询把必要行缩小，再在外面分组。不过要注意，GROUP BY本身带有去重语义，如果业务允许，可以尝试用窗口函数或先排序后去重的方式替代，但两者逻辑要完全一致。实际上，MySQL的GROUP BY实现会把NULL也当成一个分组，所以如果分组列上NULL值很多，也会影响效率，这一点很多人并不清楚。
4. 执行计划下钻：用EXPLAIN破解为什么没走索引
前面说了这么多原因，但你实际写SQL时，不可能背完所有禁忌，更可靠的手段是拿EXPLAIN去验证。我把最常见的检查方法整理成一套“三板斧”，遇到疑似索引失效时照着看。真正的DBA排查问题从来不是靠猜，而是靠这些证据层层下钻。
4.1 type字段：索引可用性的第一信号
EXPLAIN中的type字段从好到差大概有：system>const>eq_ref>ref>range>index>ALL。
- 如果出现
ALL，基本就是全表扫描，索引失效或优化器不想用索引。 - 出现
index时，表示遍历了整棵索引树，不是通过索引定位，而是因为索引树比聚集索引小，优化器选择“扫描索引树”来避免回表，只能算“部分救场”。 range说明用了索引范围扫描，常见于BETWEEN、IN、> < 等，这是健康的。ref、eq_ref、const都是等值命中的情况，是最理想的状态。
有时候你会发现type是index，但key明明有值，于是误以为索引被用上了，其实这里全树扫描的意义和全表差不多，只是由于索引体积小，扫描成本低一点。如果SQL需要返回大量行，index扫描可能比ALL稍好，但依然不理想。真正判断是不是高效命中的关键，还是要结合rows和key_len一起看。
4.2 key_len 和 rows：判断是否“完整用上”了联合索引
key_len是判断联合索引到底用了多少列的核心依据。例如索引(a varchar(50), b int, c datetime)，当SQL只用到a时，key_len只有a那段的长度；用到了a和b就会更长。如果把每次EXPLAIN的key_len记录下来对比，你很容易发现“范围条件切断后续列”的小动作。
rows是优化器预估需要扫描的行数。如果预估行数接近全表行数，即便索引被使用，也可能因为回表成本高而放弃使用。这时候需要看是否可以使用覆盖索引，把要查询的字段都放进索引里，减少回表。比如索引(create_time, status, amount)，而SQL是SELECT create_time, status, amount FROM order_detail WHERE create_time BETWEEN ... AND status = 1，那么所有需要的字段都从索引里拿到，不需要回表，Extra就会出现Using index。如果还要查询order_no，这个字段不在索引里，就会在取出索引记录后回表读取完整行，成本上升。所以覆盖索引在设计时往往是“用空间换时间”的经典手段，对有大量高频、固定字段查询的场景特别有效。
4.3 Extra列里的“Using where”和“Using filesort”
Using where出现在SQL走了某个索引，但还有少量字段在引擎层进一步过滤。它不代表索引失效，但如果你发现在索引命中的情况下仍然大量出现，可能要考虑是否某些查询列没有在索引中，或者索引设计有冗余。比如说索引(a, b)，SQL是WHERE a = 1 AND c = 2，这里a走了索引，c的过滤就必须靠Using where。如果c的过滤选择性很高，那你可能需要把c也加入索引。
Using filesort是排序索引失效的直接证据。它意味着MySQL无法利用已有索引的有序性，必须另起一段内存或磁盘进行排序。要消除它，重点检查ORDER BY和GROUP BY是否对齐了索引列顺序。如果在Extra里看到Using temporary; Using filesort同时出现，通常是GROUP BY或DISTINCT把临时表都用上了，这种时候要格外小心，数据量一大性能会爆炸。
4.4 一个完整的EXPLAIN实战分析
我们用一个例子走一遍：
EXPLAIN SELECT id, user_id, amount FROM order_detail WHERE DATE(create_time) >= '2024-11-01' ORDER BY id DESC执行计划结果的关键列是：type=ALL,possible_keys=idx_create_time,key=NULL,rows=19000000,Extra=Using where。虽然possible_keys写出了idx_create_time，但key是NULL，说明因为函数运算，优化器直接放弃索引。把SQL改成：
WHERE create_time >= '2024-11-01 00:00:00'再看，type变成range，key变成idx_create_time，rows降到几十万，问题清晰可见。这就是用工具还原真相的过程。如果用的是MySQL 8.0.18以上，还可以加上ANALYZE关键字：EXPLAIN ANALYZE SELECT ...，它会返回每个操作的实际执行时间和行数，比静态的EXPLAIN更真实。有一次我被一个奇怪的执行计划误导了很久，EXPLAIN显示全表扫描，但实际执行却很快，后来才发现是因为优化器把所选列都覆盖到了二级索引，而EXPLAIN的旧版本没有展示这个细节。所以工具要尽量用新版本，多参考Extra真实反馈。
5. 索引失效的预防良药：从规范约束到优化实践
无论是定位了一次事故，还是刚刚梳理完全部原因，最终目标都是“少踩坑”。下面这些方法是自己在公司实践了一段时间后，觉得最有用的。它们不是一次性的优化技巧，而是应该固化到日常研发流程里的动作。
5.1 代码评审阶段的SQL规约
我们团队把常见索引失效原因写进了一页SQL开发规约，评审时逐条打勾：
- 禁止对索引列进行函数、运算或隐式类型转换。
- 禁止使用前导模糊查询，除非有全文索引。
- 联合索引必须保证查询条件从左到右持续匹配。
- 排序/分组字段必须与联合索引顺序一致。
- 使用OR时，必须确保所有条件列都有可用索引，尽量改成UNION ALL。
你可能觉得这些约束太机械，但生产事故往往来自“偶尔一次”的小聪明。把它落到评审里，比事后救火强十倍。评审时不要只看SQL本身，还要带上表结构和执行计划。我见过很多团队评审只看代码逻辑，执行计划压根不看，结果上线后慢查询直接打到告警平台。对于新上线的高频查询，我习惯要求开发在PR描述里附上EXPLAIN关键字段截图，并回答“key用的哪个索引”“rows预估多少”“有没有filesort”，这三个问题能堵住绝大多数坑。
5.2 数据模型层面的提前设计
索引失效的也不少是建表时就埋下的雷：
- 字段类型尽量使用数值型或固定长度的字符串，避免不同类型比较。
- 存储手机号、身份证号这类定长字段，直接用char或bigint。
- 冗余“范围查询”的对比值，比如把日期时间拆分成日期和时分两个列，让等值查询有机会走上联合索引。
- 如果业务明确有函数查询需求，优先考虑MySQL 8.0的函数索引，或者把原始值加工结果单独存储一个列。
这里想说一个真实踩过的坑：我们曾经有一张订单表，业务方喜欢按“月”查数据，SQL里写WHERE MONTH(create_time)=11，后来在应用层加了一个month字段来冗余，但是代码没有同步更新，索引倒是建了month，结果SQL还在用MONTH(create_time)，执行计划不光失效，还会因为额外的month索引增加写入开销。后来我们把冗余字段落到表里，并强制要求SQL使用month=11，性能才恢复正常。所以冗余字段一定要和SQL标准配合，否则就是白白占空间。
5.3 用慢查询日志和巡检脚本主动发现问题
被动等告警很难受，不如主动“排雷”。生产环境可以开启慢查询日志，定期扫描mysqldumpslow结果，把那些长时间执行的SQL全部拎出来做EXPLAIN。再配合一个月跑一次的索引统计信息更新（ANALYZE TABLE），能有效防止统计值过期导致优化器跑偏。我自己习惯用一条命令导出TOP慢SQL：
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log然后对每个出现次数多的SQL执行EXPLAIN，重点看是否有索引失效。可以发现一个现象：很多慢SQL并不是每一次都慢，而是数据增长到某个量级后突然变慢。这就是因为优化器基于过旧的统计信息做出了错误判断。定期ANALYZE TABLE的成本很低，但收益非常可观。如果你用的是MySQL 5.7及以上，还可以设置innodb_stats_auto_recalc=1，让表数据变化超过10%时自动重新计算统计信息。
5.4 优化器不够聪明时该怎么办
有些情况下SQL已经写对了，但优化器还是选择全表扫描。比如一个表上有索引但选择性太低（重复值太多），或者统计信息不准。这时候：
- 先强制执行一下试试：
FORCE INDEX，看是否真的更快。不要长期依赖，它只是临时排查手段。 - 重新分析统计信息：执行
ANALYZE TABLE table_name，让优化器“刷新认知”。 - 重写SQL逻辑：把大查询拆成小查询，把复杂的关联拆成两步，通常让优化器更清晰。
- 必要时调整索引结构：增加覆盖索引，把
SELECT列都放进索引，减少回表成本。
这里还要提一个和索引失效容易混淆的话题——索引下推（Index Condition Pushdown，ICP）。当联合索引(a,b)中，a条件满足后，如果对b的过滤能下推到索引层，MySQL会把Using index condition打在Extra里。这不叫失效，反而是5.6之后的一个优化手段。你千万不要一看到Using index condition就觉得炸了，要分清场景。ICP是针对“索引内字段条件无法走最左连续匹配”时的一种补偿，它把部分WHERE过滤条件下推到存储引擎，在读取索引记录时就做判断，减少回表次数。虽然它不能像范围匹配那样精确利用索引顺序，但已经比完全回表后再过滤要高效。理解了这一点，再看Extra就会更从容。
说到底，索引失效不是玄学，背后都是B+树的有序性和优化器的成本核算。只要你在写SQL时多问一句“这个条件能不能直接利用索引树的有序性”，多数坑都能绕过去。我自己习惯在新项目核心SQL上线前，把EXPLAIN输出截个图当作准入条件，久而久之，线上“突然变慢”的报警少了大半。索引优化是一项需要持续投入耐心的工作，但它带来的稳定性和性能收益，远比一时赶工的价值要大。
