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字段显示为ALLrows预估接近两千万Extra列里还有Using where和Using filesort。我当时的第一反应是索引是不是没建上但检查之后发现create_time索引明明存在。后来才反应过来问题出在WHERE条件里的DATE函数上——它对索引列做了函数运算MySQL无法利用B树有序性去范围扫描只能把索引列的所有值都“加工”一遍再过滤于是干脆选择了全表扫描。从原理上讲B树索引的有序性建立在“列本身的原始值”上一旦套上函数索引里存储的原始键值和查询条件里的加工后值就无法直接对应优化器自然无从下手。1.2 定位失效方式的三个关键动作这个问题的定位并不复杂但当时有效帮助我快速收敛的三个动作你可以先记下来。第一打开慢查询日志和当前执行的日志开关把具体SQL和实际执行计划抓出来。尤其在生产环境不要凭记忆猜直接用SHOW INDEX FROM order_detail确认索引是否存在、字段和顺序对不对。也可以用SET profiling1开启profiling拿到更详细的每个步骤耗时。如果慢查询日志里同时出现了多条类似的SQL最好用pt-query-digest这类工具做一次聚合分析找出共性往往几个关键字就能暴露问题。第二用EXPLAIN看执行计划。重点看type字段是不是从const、ref掉到了ALL看possible_keys是否出现了但key是空这两条是最直观的索引失效信号。我习惯同时打开EXPLAIN ANALYZEMySQL 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并且是“从左到右连续”的。最容易犯的错是跳过最左列直接查cWHERE 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的索引部分被浪费了。这不是“索引整个失效”而是“部分失效”。很多同学在排查时看到typerange以为没问题但看key_len会发现它其实比完全等值匹配短了一截。要优化可以把b2改写成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但下面这些就会触发filesortORDER 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 BYMySQL会先尝试用索引完成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字段从好到差大概有systemconsteq_refrefrangeindexALL。如果出现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执行计划结果的关键列是typeALL,possible_keysidx_create_time,keyNULL,rows19000000,ExtraUsing where。虽然possible_keys写出了idx_create_time但key是NULL说明因为函数运算优化器直接放弃索引。把SQL改成WHERE create_time 2024-11-01 00:00:00再看type变成rangekey变成idx_create_timerows降到几十万问题清晰可见。这就是用工具还原真相的过程。如果用的是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使用month11性能才恢复正常。所以冗余字段一定要和SQL标准配合否则就是白白占空间。5.3 用慢查询日志和巡检脚本主动发现问题被动等告警很难受不如主动“排雷”。生产环境可以开启慢查询日志定期扫描mysqldumpslow结果把那些长时间执行的SQL全部拎出来做EXPLAIN。再配合一个月跑一次的索引统计信息更新ANALYZE TABLE能有效防止统计值过期导致优化器跑偏。我自己习惯用一条命令导出TOP慢SQLmysqldumpslow -s at -t 20 /var/log/mysql/slow.log然后对每个出现次数多的SQL执行EXPLAIN重点看是否有索引失效。可以发现一个现象很多慢SQL并不是每一次都慢而是数据增长到某个量级后突然变慢。这就是因为优化器基于过旧的统计信息做出了错误判断。定期ANALYZE TABLE的成本很低但收益非常可观。如果你用的是MySQL 5.7及以上还可以设置innodb_stats_auto_recalc1让表数据变化超过10%时自动重新计算统计信息。5.4 优化器不够聪明时该怎么办有些情况下SQL已经写对了但优化器还是选择全表扫描。比如一个表上有索引但选择性太低重复值太多或者统计信息不准。这时候先强制执行一下试试FORCE INDEX看是否真的更快。不要长期依赖它只是临时排查手段。重新分析统计信息执行ANALYZE TABLE table_name让优化器“刷新认知”。重写SQL逻辑把大查询拆成小查询把复杂的关联拆成两步通常让优化器更清晰。必要时调整索引结构增加覆盖索引把SELECT列都放进索引减少回表成本。这里还要提一个和索引失效容易混淆的话题——索引下推Index Condition PushdownICP。当联合索引(a,b)中a条件满足后如果对b的过滤能下推到索引层MySQL会把Using index condition打在Extra里。这不叫失效反而是5.6之后的一个优化手段。你千万不要一看到Using index condition就觉得炸了要分清场景。ICP是针对“索引内字段条件无法走最左连续匹配”时的一种补偿它把部分WHERE过滤条件下推到存储引擎在读取索引记录时就做判断减少回表次数。虽然它不能像范围匹配那样精确利用索引顺序但已经比完全回表后再过滤要高效。理解了这一点再看Extra就会更从容。说到底索引失效不是玄学背后都是B树的有序性和优化器的成本核算。只要你在写SQL时多问一句“这个条件能不能直接利用索引树的有序性”多数坑都能绕过去。我自己习惯在新项目核心SQL上线前把EXPLAIN输出截个图当作准入条件久而久之线上“突然变慢”的报警少了大半。索引优化是一项需要持续投入耐心的工作但它带来的稳定性和性能收益远比一时赶工的价值要大。