1. 为什么索引失效值得认真聊一聊每个写过SQL的工程师几乎都经历过这样一幕where条件的字段明明建了索引explain一执行却是type ALLrows显示几万几十万慢查询日志里躺着这个SQL。第一反应是“索引没建上”回头一查索引整整齐齐在表结构里于是开始怀疑MySQL是不是偶尔犯浑。其实MySQL真的不犯浑。所谓索引失效本质上就两件事要么你的SQL写法让索引列失去了原始形态导致查询条件无法与索引中的有序值做比较要么优化器评估完成本之后认为走索引反而比全表扫描更贵干脆就放弃索引。这两件事一个属于“你能不能用到索引”另一个属于“优化器愿不愿意用索引”。网上搜“索引失效的几种情况”能搜出一堆口诀但大多只给结论不讲原因结果就是面试勉强能说线上真碰到一个慢SQL还是没法快速判断。这篇文章我打算从这两条主线展开。前半部分讲B树、回表和优化器的取舍逻辑把“为什么失效”的底层原理讲透中间部分梳理最常见的失效场景每种都配上能复现的SQL和explain分析最后讲怎么用工具快速定位、怎么改写SQL以及在面试和实际项目里容易踩的坑。老实说这块内容很多文章都写过但能把“为什么”讲明白的真不多我希望你看完不是背结论而是能自己推导出结论。2. 先看底层逻辑B树、回表与优化器2.1 索引加速靠的从来不是玄学MySQL InnoDB的索引结构是B树这是一棵平衡多路搜索树叶子节点存数据且按索引键有序排列内部节点只存索引键和指针。因为有序等值查询可以走二分快速定位范围查询可以沿着叶子节点形成的链表顺序读取。这套机制听起来基础但它解释了索引失效的一大半原因——B树的有序性只对“索引列本身的值”有意义。假设你在user表的name字段上建了普通索引B树里存的是name的原始字符串排序后的序列。查询条件写成LIKE 张%MySQL可以将其转换为一个左闭右开的范围定位到第一个“张”开头的叶子节点然后往后顺序扫描这就是range访问。但如果写成LIKE %张前缀位置未知优化器根本不知道从哪个节点开始扫只能把所有叶子节点全部遍历一遍再逐个过滤。索引还在只是这个条件没法利用它的有序性。同样的逻辑如果在where条件里对索引列做函数运算比如WHERE DATE(create_time) 2024-06-01索引里存的是完整的create_time原值而不是DATE函数算出来的结果。为了比较MySQL只能把每一条记录的create_time先过一遍DATE再和常量比较。函数结果的有序序列和索引键的有序序列完全对不上索引自然用不上。这一类问题我愿称之为“语法层面的失效”SQL写法没给索引留活路。2.2 回表成本是优化器放弃索引的最大理由InnoDB的索引分两类聚簇索引和二级索引。聚簇索引的叶子节点直接存整行数据二级索引的叶子节点存的是索引键加主键值。走二级索引查数据通常还要拿着主键回聚簇索引再查一次这个过程就是回表。回表不是免费的每次回表基本对应一次随机IO。如果一张表有100万行二级索引筛选出30万条记录那就得回表30万次。相比之下全表扫描读的是聚簇索引的连续数据页顺序IO配合InnoDB的预读机制成本反而可能更低。优化器在这种场景下选择typeALL并不是索引真“坏”了而是它算了一笔账觉得全扫更快。顺便提一个细节二级索引的叶子节点数量通常远小于聚簇索引因为单条索引记录更短。这带来一个反直觉的结论——COUNT(*)这类聚合查询MySQL反而倾向于扫描最小的二级索引树而不是聚簇索引。如果你建的联合索引字段很长树很“胖”聚合查询反而可能变慢。索引设计和查询成本从来不是孤立问题。2.3 统计信息失真另一种隐蔽的“失效”优化器决定是否走索引主要依赖几张统计信息表的行数、索引基数cardinality、字段的选择性。这些值可以从SHOW INDEX里看到但InnoDB对Cardinality的统计是采样估算的不是逐条精确统计。当表频繁增删改时统计信息可能滞后优化器对成本的评估就会失真导致明明能走索引的SQL它偏不走。这种问题特别隐蔽因为SQL写法完全没问题索引也在但执行计划就是不对。排查方法一般是两个先用ANALYZE TABLE重新收集统计信息再看执行计划是否恢复如果还不行就用FORCE INDEX强制走索引对比强制前后的实际耗时。强制后更快说明优化器的统计信息没跟上强制后更慢说明优化器本来是对的。这个区分逻辑我在后面用EXPLAIN排查的章节里会再展开。3. 七类典型失效场景逐一起底3.1 前导模糊匹配LIKE %xx 和 xx% 天差地别先建一张测试表方便复现CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT, KEY idx_name (name) ) ENGINEInnoDB;插入一批数据后分别执行EXPLAIN SELECT * FROM t_user WHERE name LIKE 张%; EXPLAIN SELECT * FROM t_user WHERE name LIKE %张;第一个查询type是range索引生效第二个查询type是ALL走了全表扫描。原因就是2.1里说的LIKE 张%能转换成前缀范围B树能顺着叶子节点往下找LIKE %张没有确定起点只能全扫。如果业务确实需要“包含某个词”的模糊查询别指望B树索引可以考虑全文索引或外部搜索引擎。只是前缀搜索保持LIKE keyword%的形式就行。这是一个非常经典的面试题也是线上最常见的慢查询来源之一——尤其是那种在文章内容、备注字段上做LIKE %xx%的查询数据量一大就会拖垮数据库。3.2 对索引列使用函数一加工就“失联”在索引列上做任何函数运算或算术运算索引基本就废了。常见的写法有WHERE DATE(create_time) 2024-06-01 WHERE YEAR(create_time) 2024 WHERE price 1 100这几种写法的共同问题是索引列被“加工”了一遍B树里的原始有序序列对不上加工后的值。MySQL只能全表扫描逐行计算并比较。解决办法是改写SQL让索引列保持原样。时间条件尤其典型-- 反例 WHERE DATE(create_time) 2024-06-01 -- 正例 WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00改写后变成范围条件create_time列没有被函数包裹索引就能用上type从ALL变成range。这个场景在实际开发里极高频因为按天统计的需求太多了很多人下意识就写了DATE函数其实换个写法就能快几个数量级。3.3 隐式类型转换字符串和数字的“无声偷袭”隐式类型转换是另一种常见的语法级失效。比如订单表CREATE TABLE t_order ( order_no VARCHAR(32), amount DECIMAL(10,2), KEY idx_order_no (order_no) ); EXPLAIN SELECT * FROM t_order WHERE order_no 20240601;order_no是字符串类型查询条件却是整数。MySQL的规则是如果比较双方类型不一致会把字符串转换成数字再比。因为索引列被隐式转换了B树的有序性又被破坏结果就是全表扫描。这里有个方向性的细节特别容易记混如果列是int条件给的是字符串比如WHERE age 20MySQL会把字符串20转成数字20。因为转换发生在常量这一侧索引列没被影响索引可以正常使用。面试里经常拿这个方向性问题来考答的时候要能讲清楚转换发生在哪一侧。开发时最好的习惯还是让字段类型和参数类型保持一致用PreparedStatement绑定参数别指望MySQL帮你做隐式转换。3.4 OR连接条件一个非索引列“坏了一锅汤”EXPLAIN SELECT * FROM t_user WHERE name 张三 OR age 30;假设name上有索引age上没有。OR语义是只要两边有一个成立就返回MySQL如果走name索引查出结果还得再处理age 30那部分最后合并去重。优化器算下来发现还不如全表扫描一次搞定于是放弃索引。修复思路有两个方向。一是给age也加索引优化器可能走index merge即多个条件分别走索引再取并集但这是优化器自己决定的不能保证100%走。二是改写为UNIONSELECT * FROM t_user WHERE name 张三 UNION SELECT * FROM t_user WHERE age 30;改写后两个条件各自都能用索引。注意UNION默认会做去重如果逻辑上确定不会重复改用UNION ALL能省掉一次排序去重开销。实际业务里我通常优先考虑调整查询逻辑为AND或者补索引让index merge生效UNION是保底而不是最优解。3.5 联合索引的“最左前缀”陷阱联合索引KEY idx_a_b_c (a, b, c)的底层排序规则是先按a排a相同再按b排b相同再按c排。因此查询条件只要不包含最左列aB树的有序性就发挥不出来。下面几种用法都容易失效WHERE b 1 AND c 2; WHERE c 3; WHERE b 1;MySQL 8.0加入了索引跳跃扫描Index Skip Scan允许某些场景跳过最左列直接使用索引但适用条件很苛刻依赖优化器判断不能当通用方案依赖。这里还要澄清一个常见误解“最左前缀”不是只要包含第一个字段就行而是不能跳过中间的字段。比如WHERE a 1 AND c 2a的过滤能用索引但c那部分在索引树里没法继续精确定位只能回表后再过滤。如果你希望c也能帮忙减少回表就得把b也放进查询条件。这个细节在面试、等级考试、实际设计联合索引时都很容易踩。3.6 范围查询截断范围条件后面的列会“失联”联合索引里还有一个重要规则范围条件一出现后续的字段排序就“乱”了。比如WHERE a 1 AND b 10 AND c 3联合索引(a,b,c)下a是等值过滤b是范围过滤c就没办法在索引树里精确定位了。原因很简单在b 10的区间内c的记录不是按c排的没有继续用c定位的基础。设计联合索引时一个非常实用的口诀是“等值在前范围在后”。把等值条件放最左范围条件放最右这样前面等值字段能一路锁住索引顺序范围字段做完范围扫描后就不会再有后续字段“排队等索引”的错觉。我处理过很多因为索引顺序没调好导致联合索引后半段完全浪费的表结构明明索引建了好几个字段真正被用上的就一两个。3.7 排序和GROUP BY索引不是只归WHERE管ORDER BY也能用B树的有序特性来优化但前提是排序字段必须和索引顺序匹配。比如有索引KEY idx_age_name (age, name)执行SELECT * FROM t_user ORDER BY age, name;可以直接从索引顺序读取Extra里不会出现Using filesort。但如果写ORDER BY name没用到最左列age索引就帮不上忙优化器只能filesort。GROUP BY本质上也是先排序后分组情况类似。还有方向问题索引是升序你ORDER BY age DESCMySQL一般还能反向扫描索引但写ORDER BY age ASC, name DESC这种混合方向的排序索引顺序就不匹配了只能另做排序。这个点在实际项目里尤其是分页和列表接口里很常见需要和业务一起分析尽量让排序方式和索引方向保持一致。4. 用EXPLAIN快速定位索引问题4.1 读懂EXPLAIN的关键字段EXPLAIN是排查SQL性能最常用的工具在SQL前加上EXPLAIN关键字即可执行。关键字段里type、key、rows、Extra是最需要关注的四个字段含义判断索引是否失效的关键type访问类型system const eq_ref ref range index ALL。看到ALL基本就可以断定为全表扫描key实际使用的索引NULL表示没有使用任何索引rows预估扫描行数rows太大意味着成本高优化器更容易放弃索引filtered过滤比例越低越好说明索引过滤掉了大量无关数据Extra补充信息Using index表示覆盖索引Using filesort表示排序没用索引Using temporary表示用了临时表type这一列要重点记住几个值。const表示主键或唯一索引等值查询效率最高ref表示普通索引等值查询range表示索引范围扫描index表示扫描了整棵索引树但没回表成本也不低ALL就是全表扫描。一旦看到ALL基本可以确认这条SQL没有用上有效索引得回头查SQL写法和索引设计。4.2 一个线上慢查询的排查实例有次线上报表查询特别慢SQL大概是SELECT order_id, user_id, status, create_time FROM t_order WHERE status 1 AND DATE(create_time) 2024-05-20 ORDER BY create_time DESC LIMIT 20;表上有联合索引idx_status_time (status, create_time)。我第一反应就是DATE函数把create_time列破坏了后面的索引判断会失效。EXPLAIN结果印证了判断typeALLrows显示200多万Extra里还有一个Using filesort。改写后的SQL是SELECT order_id, user_id, status, create_time FROM t_order WHERE status 1 AND create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00 ORDER BY create_time DESC LIMIT 20;再次EXPLAINtype变成rangerows从200多万降到几千Using filesort也消失了。这个查询从2.8秒降到30毫秒左右。顺带说一句这个案例里联合索引的顺序也刚好符合要求status是等值条件在前create_time是范围条件在后两个字段都被索引有效利用。如果当时索引建的是(create_time, status)效果就会差很多。4.3 FORCE INDEX验证优化器有没有“犯迷糊”SQL写法没问题但type还是ALL时我习惯用FORCE INDEX做一次对比验证SELECT * FROM t_user FORCE INDEX (idx_name) WHERE name LIKE %张;强制走索引后如果速度反而更慢说明优化器是对的问题不在索引而在SQL本身。如果强制之后明显更快那就是优化器评估出了问题先执行ANALYZE TABLE t_user;更新统计信息再看执行计划是否恢复。如果统计信息更新完仍然坚持全表扫描再考虑是不是数据分布导致索引选择性太差。这个套路看着简单但能把“语法层面失效”和“优化器层面失效”清楚地区分开。排查慢SQL最忌讳不看explain直接改SQL盲改就是在碰运气。先花两分钟定位再动手稳定可靠得多。5. 让索引真正生效的几个优化手段5.1 覆盖索引让优化器心甘情愿走索引覆盖索引的意思是查询所需的全部字段都包含在二级索引的叶子节点里不需要回表。比如表t_user有索引idx_name_age (name, age)执行SELECT name, age FROM t_user WHERE name 张三;只需要在idx_name_age这棵索引树上就能拿到name和age不需要回聚簇索引EXPLAIN里Extra会显示Using index。回表次数一降优化器自然愿意用索引这招对于修复“优化器因回表成本过高而放弃索引”的问题特别有效。设计覆盖索引时不能贪多索引字段越多占用的存储空间越大写入性能越低。通常是把查询最频繁的select字段叠加到现有联合索引里同时尽量少用SELECT *。如果一个表的核心查询模式就那么两三种为每种模式设计一个覆盖索引是完全值得的。5.2 改写SQL的通用套路除了把函数调用改成范围条件还有几个改写方向值得沉淀成习惯前导模糊查询改为前缀匹配如果必须用LIKE %xx%测试覆盖索引能不能配合索引下推减少回表。OR条件优先补索引让优化器走index merge不行再改UNION ALL。深分页优化LIMIT 100000, 20这种写法可以先把主键范围算出来再用主键关联SELECT * FROM t_user WHERE id ( SELECT id FROM t_user ORDER BY id LIMIT 100000, 1 ) ORDER BY id LIMIT 20;排序字段和WHERE条件不满足联合索引时调整索引字段顺序或者调整查询让排序条件与索引顺序一致。有一点很重要改写SQL时必须保证结果集语义不变尤其是分页场景改动排序方式可能会让翻页结果不一致。我习惯在测试环境先跑新旧版本SQL做结果比对确认无误再上生产。5.3 索引下推和统计信息维护MySQL 5.6开始支持索引下推ICP核心能力是联合索引的部分字段无法用于定位但可以在索引遍历的过程中提前过滤。还是用idx_name_age(name, age)举例查询WHERE name LIKE 张% AND age 20name负责定位范围age的过滤如果没有ICP就得先把所有前缀匹配的记录都回表再在聚簇索引里判断age 20有ICP的话age 20在二级索引遍历时直接被判断回表次数大幅减少。EXPLAIN里Extra显示Using index condition就是ICP生效了。这个特性是自动的前提是过滤字段真的在联合索引里所以设计联合索引时除了考虑等值和范围顺序也要把高频过滤字段塞进去。统计信息维护方面ANALYZE TABLE是手动重算统计信息的手段。对于增删改频繁的表可以考虑合理设置innodb_stats_auto_recalc如果表的数据量变化剧烈定期在低峰期做一次ANALYZE能有效避免优化器因统计信息失真而选错执行计划。注意大表ANALYZE会占用一定资源控制好频率即可。6. 面试、实战中的高频话题与经验总结6.1 面试时怎么回答索引失效面试官问索引失效最好别只背口诀而是按两条主线回答哪些SQL写法让索引列无法参与比较哪些情况让优化器主动放弃索引。语法层面前导模糊匹配、对索引列使用函数、隐式类型转换、联合索引不满足最左前缀、范围查询截断后续列、OR条件包含非索引列。优化器层面统计信息失真、回表成本过高、索引选择性太差、表数据量太小导致全表扫描更快。如果能再补充一句“用覆盖索引可以降低回表成本让优化器重新选择索引”面试观感会明显不同。这能说明你不只是背了结论还理解了索引的成本模型。线上排查时这套回答框架同样是最好的排查路线图。6.2 容易被忽略的灰区与坑点有些场景不一定会让你一眼看出“失效”但确实影响性能。第一表数据量特别小比如几十行的字典表索引可能不生效这不是失效而是优化器觉得没必要第二COUNT(*)会优先扫描最小索引树如果你把联合索引建得很庞大聚合查询反而吃亏第三索引字段的选择性是关键某个字段90%的值都一样这种情况优化器放弃它很合理应该从业务查询模式下手而不是硬调索引。还有一个实战上很重要的建议删索引之前务必谨慎。有些慢SQL可能当前没有触发但删掉索引后一个低频大查询突然冒出来直接就拖垮实例。删除前最好用慢查询日志确认没有相关查询依赖宁可多留一个索引也不要打无准备之仗。线上环境的一个基本原则是变更之前要有回滚方案索引也不例外。6.3 我自己的排查流程分享给你最后总结一下我常用的慢SQL排查流程基本上是固定套路拿到慢SQL执行EXPLAIN先看type、key、rows、Extra。如果type是ALL或index判断SQL写法有没有破坏索引列函数、隐式转换、前导模糊、OR、联合索引顺序逐项排查。写法没问题用FORCE INDEX强制走索引对比耗时。强制走索引明显更快执行ANALYZE TABLE更新统计信息。统计信息正常但优化器仍然不走考虑覆盖索引、改写SQL结构、调整查询逻辑。最后结合数据分布判断索引选择性如果某个字段本身区分度太差优化器放弃它是合理的这时应该从业务角度解决。这个流程我用了很多年基本能解决90%的索引失效类问题。整个过程中最忌讳的就是不看explain直接乱改。有些同事喜欢给所有where字段都加索引以为就万事大吉结果写入变慢查询也没快多少最后还是要回头做减法。索引设计本质上是读和写的权衡没有银弹。多在实践中积累感觉看到执行计划的时候你对成本的理解就会越来越直觉化。我自己是每处理一个慢SQL都会顺手记一条笔记时间长了哪些写法会踩坑、哪些索引设计合理心里基本有数。
