为什么MySQL索引会失效？其实只有两种原因

网上讲MySQL索引失效大多数是列一个清单函数、LIKE、类型转换、OR。背下来之后面试官深入追问下去你可能会答不上来。问题出在「视角」上。这些场景看起来各不相同背后其实是两种完全不同的机制在起作用。一种是B树的有序性被破坏索引物理上不可用不管数据量多少、不管统计信息多新都不会走索引。另一种是优化器认为走索引的成本比全表扫描还高主动放弃索引。这篇内容尝试从这两个根因出发把MySQL索引失效的所有场景都列一下。如果你还没看过B树结构和索引组织原理建议先翻一下我之前写的MySQL索引面试速查手册那篇内容那篇讲B树怎么排序、聚簇索引和二级索引怎么组织、回表是怎么回事。理解了那些基础这篇读起来才会比较顺畅。索引失效分两类先给一个全局视角。下面这张图是全文的主线后面每讲一种失效场景都会对应到这张图的某个环节。第一类B树有序性被破坏这类失效的共同特征是查询条件改变了索引列在B树中存储的原始值或者无法在B树上定位扫描起点。索引不是「被放弃」是「用不了」。MySQL在构建范围扫描计划时会逐个检查WHERE条件中的字段是否能在索引上直接比较。如果检查不通过这个索引就直接被排除不会进入成本计算环节。也就是说这类失效发生在成本评估之前索引连参与竞争的机会都没有。对索引列使用函数最经典也最好理解的场景。索引存的是字段的原始值你用函数包住了索引列B树里存的是原始值函数处理后的值在B树里是无序的没法定位。WHERE YEAR(create_time) 2024里create_time上建了索引B树按时间戳排序。YEAR函数把时间戳转成了年份整数这个整数在B树里是无序分布的优化器只能放弃索引。改成范围查询让索引列保持原始值的形式WHERE create_time BETWEEN 2024-01-01 AND 2024-12-31 23:59:59MySQL 8.0引入了函数索引可以针对表达式建索引后面专门讲。函数索引的实际使用率很低。多数项目里改写SQL是更通用的做法函数索引只在改写SQL成本太高比如大量历史代码时才考虑。对索引列进行表达式计算和函数一个道理。索引存的是原始值表达式计算后的值在B树里不一定有序。WHERE id 1 10id是主键但id 1这个表达式让优化器无法直接用id去B树里找。改成id 10 - 1计算移到等号右边索引列保持原始形式就能走了。有人可能会问MySQL为什么不做这个简单的代数变换。技术上能实现但表达式的情况太多样每种都处理的话优化器代码会变得很臃肿。MySQL选择把这个责任交给开发者你保证不在索引列上做计算就行。隐式类型转换这是生产环境里踩坑频率最高的一个。理解它的关键不在于记住结论而在于搞清楚转换发生在哪一侧。表定义phone VARCHAR(20)建了索引。查询时传了数字WHERE phone 13800138000phone是字符串13800138000是数字类型不一致。MySQL的规则是字符串和数字比较时把字符串转成数字。所以这条SQL等价于WHERE CAST(phone AS SIGNED) 13800138000。CAST作用在了phone字段上phone是索引列。索引列被包进了函数和前面讲的函数场景一样索引失效。反过来如果id是INT类型查询时传了字符串WHERE id 1MySQL把字符串1转成数字1转换发生在常量上索引列id没被动过索引正常生效。判断规则就一条转换发生在索引列上索引失效转换发生在常量上索引不受影响。这个坑在实际项目里特别隐蔽。前端传参没有做类型校验的时候都可能触发。排查的时候用EXPLAIN看一眼执行计划type是ALL就知道索引没走。LIKE以通配符开头B树按索引值从左到右排序。LIKE ‘张%’前缀确定B树能定位到以「张」开头的起点正常走索引。LIKE ‘%张’前缀未知B树无法定位扫描起点不能进行索引范围扫描。优化器通常选择全表扫描但如果查询的列都在索引中也可能选择全索引扫描EXPLAIN的type显示index省掉回表。LIKE %张和LIKE %张%这两种写法都走不了索引。如果业务确实需要全模糊匹配有两个方向可以考虑。一是只查索引覆盖的列虽然仍然是全索引扫描EXPLAIN的type显示index但省了回表比全表扫描快。二是把大规模模糊搜索放到Elasticsearch这类搜索引擎里做MySQL不适合干这个。不满足最左前缀联合索引(a, b, c)B树先按a排序a相同再按b排序b相同再按c排序。跳过a直接查bB树里b整体是无序的找不到入口。WHERE b 1和WHERE b 1 AND c 2都走不了联合索引。从a开始才行WHERE a 1和WHERE a 1 AND b 2。MySQL 8.0.13引入了Index Skip Scan对最左前缀规则做了个例外。对于联合索引(a, b)查询只用了b但没用a时如果a的distinct值很少比如性别、状态这种枚举字段优化器可以枚举a的所有distinct值对每个值做一次范围扫描。比如a只有0和1两个值优化器会把WHERE b 5拆成WHERE a 0 AND b 5和WHERE a 1 AND b 5两次扫描合并结果。Skip Scan的适用条件比较严格只能引用一张表不能有GROUP BY或SELECT DISTINCT查询字段必须在索引中覆盖索引前导列必须是等值条件被跳过的列后面必须有范围条件。实际项目里不建议依赖Skip Scan来弥补索引设计的问题。如果联合索引的查询路径和最左前缀不匹配调整索引顺序或者补齐前导列条件才是正道。Skip Scan是一个兜底优化不是一个设计策略。范围查询截断后续列联合索引(a, b, c)a用了范围查询后在a的取值区间内b是无序的b和c不能继续参与索引范围定位。注意这里说的是不能缩小范围扫描的区间不是说b和c完全没用。在某些场景下b和c仍然可能通过Index Condition Pushdown在存储引擎层做过滤或者作为覆盖索引的一部分避免回表。但就索引定位能力而言范围查询之后的列确实帮不上忙了。WHERE a 1 AND b 2 AND c 3key_len只反映用了a列。和不截断因为等值边界点处b仍然有序。BETWEEN和LIKE 前缀%也不截断。设计联合索引的时候把范围查询的字段放在最后等值字段放前面能最大化利用索引。字符集不一致这是一个容易被忽略的场景。JOIN或者WHERE条件涉及两张表的字段比较时如果两个字段的字符集不同MySQL需要在比较前做字符集转换。转换发生在哪一侧决定了索引是否失效。规则和隐式类型转换一样转换发生在索引列上索引失效转换发生在常量上索引不受影响。MySQL的字符集提升规则是utf8mb4优先级高于utf8。如果列是utf8mb4常量是utf8常量被提升为utf8mb4索引列不受影响。如果列是utf8常量是utf8mb4列被转换为utf8mb4索引失效。实际项目中这个坑主要出现在跨库JOIN或者历史表和新表字符集不一致的场景。排查时看SHOW CREATE TABLE的字符集声明确保关联字段的字符集和排序规则一致。第一类场景讲完了。这些场景的共同点在于索引列的原始值被动了或者B树找不到起点索引物理上不可用。回到前面那张流程图这些都发生在Range Optimizer环节索引连进入成本计算的资格都没有。下面来看第二类情况完全不同索引物理上能用是优化器算了一笔账之后主动放弃的。第二类优化器成本决策MySQL的优化器是基于成本的。每条SQL执行前优化器会估算不同执行方案的成本选成本最低的那个。全表扫描的成本和索引扫描的成本都会算一遍谁低用谁。优化器会先算出全表扫描的基线成本然后逐个尝试每个可用索引的范围扫描方案比较成本。如果所有索引方案的成本都比全表扫描高就走全表扫描。这不是索引「失效」了是优化器做了理性选择。理解这一点面试时就不会把所有不走索引的情况都笼统地叫「索引失效」。回表成本过高这是第二类里最常见的场景。二级索引的叶子节点只存索引列值和主键值查到主键后还要去聚簇索引取完整行数据。每次回表都是一次随机IO。如果命中的行数太多回表的随机IO成本加起来可能比全表扫描的顺序IO还高。全表扫描虽然是顺序读整张表但顺序IO的效率远高于随机IO。当索引扫描命中的行数占总行数的比例超过某个阈值时全表扫描的顺序IO反而更快。这个阈值不是硬编码的30%是优化器根据表大小、IO成本模型动态算出来的。30%只是一个经验性的近似值实际场景中可能是20%也可能是40%取决于具体的成本模型参数。优化器在计算时会先算基线成本再把每个索引的扫描成本包括回表成本和基线比较。如果索引方案的成本更低才会选择走索引。解决方案是用覆盖索引把查询需要的字段都纳入索引避免回表。或者只查真正需要的字段不要SELECT *。OR连接一侧无索引OR连接的两个条件优化器需要两侧都能走索引才能考虑Index Merge。如果一侧走不了索引优化器没有合适的低成本方案只能全表扫描。这不是语法层面的限制而是成本决策Index Merge和UNION Rewrite都是可能的例外后面会讲。WHERE id 1 OR name 张三id有索引name没有。OR的语义是两侧满足一个就行。如果只走id索引name的匹配结果就丢了。优化器只能选择全表扫描一次性把所有行过一遍。即使两侧都有索引也不一定走Index Merge。Index Merge的成本包括两次索引扫描加结果集合并如果两个条件各自命中的行数都不少合并后的成本可能比全表扫描还高优化器仍然会选全表扫描。解决方向有两个。给另一侧也建索引让Index Merge有机会触发。或者改写为UNION ALL让每段查询独立走索引SELECT * FROM user WHERE id 1 UNION ALL SELECT * FROM user WHERE name 张三;否定条件!、、NOT IN这类否定条件通常命中的行数占大多数。WHERE status ! 1如果status为1的行只占5%那95%的行都满足条件。走索引意味着大量回表优化器判断全表扫描更划算。这不是绝对的。如果否定条件筛选的行数很少比如WHERE status ! 1但status为1的行占95%优化器也可能选择走索引。关键还是成本计算不是否定条件本身导致失效。业务上能改写成正向条件的尽量改。WHERE status ! 1如果业务上等价于WHERE status IN (0, 2)改过来就行。IS NOT NULL和否定条件类似的逻辑。IS NOT NULL通常命中的行数占大多数优化器倾向于全表扫描。IS NULL反而可能走索引因为NULL值通常很少走索引划算。允许NULL的字段还会带来其他问题COUNT不计入NULL、NOT IN遇到NULL结果不符合预期、额外存储开销。建表时加NOT NULL DEFAULT从设计上避免这些问题。IN列表过长IN列表里的值太多会影响优化器的行数估算精度。MySQL优化器估算IN查询的行数有两种方式。一种叫Index Dive直接深入B树探测每个值对应的行数精确但开销大。另一种叫Index Statistics用索引统计信息估算快但不够精确。切换的阈值由系统变量控制默认值200。超过这个阈值就从Index Dive切换到Index Statistics。切换本身不会直接导致索引失效。但Index Statistics依赖统计信息的准确性如果统计信息过期估算的行数可能严重偏离实际优化器可能据此做出错误决策放弃索引选择全表扫描。统计信息过期优化器做成本估算依赖统计信息表行数、索引基数、数据分布等。统计信息过期优化器的成本估算就会偏可能做出错误的索引选择。常见触发场景大量INSERT/DELETE后没有及时更新统计信息新建索引后统计信息还没收集数据分布极不均匀某个字段值集中在少数几个值上。执行ANALYZE TABLE orders重新采样统计信息。在生产环境上执行要谨慎大表可能需要几秒到几十秒而且可能导致优化器突然改变执行计划。建议在低峰期执行。排查索引失效问题时如果EXPLAIN显示的rows估算值和实际行数差很多先试试ANALYZE TABLE很多时候问题就出在统计信息上。MySQL 8.0的应对手段上面讲了这么多失效场景MySQL 8.0也提供了一些新的手段来应对。函数索引MySQL 8.0支持对表达式建索引直接解决函数导致索引失效的问题。ALTER TABLE orders ADD INDEX idx_year ((YEAR(create_time)));建完函数索引后WHERE YEAR(create_time) 2024就能走索引了。函数索引存储的是表达式计算后的值B树按计算后的值排序所以函数查询能直接定位。函数索引的局限在于每个查询模式都要单独建一个函数索引维护成本高。实际项目里能改写SQL的优先改写SQL函数索引是改写成本太高时的备选方案。隐藏索引隐藏索引Invisible Index是MySQL 8.0引入的特性。把索引设为不可见后优化器会忽略它就像这个索引不存在一样。ALTER TABLE orders ALTER INDEX idx_status INVISIBLE把索引设为不可见。用途是安全地测试索引移除的影响。生产环境里经常有不确定是否还在用的索引直接DROP怕影响线上。先设为INVISIBLE观察一段时间确认没有查询受影响再真正删除。需要恢复的时候执行ALTER TABLE orders ALTER INDEX idx_status VISIBLE一条命令就能切回来。这个特性在索引治理里非常实用。降序索引MySQL 8.0之前建索引时写DESC关键字语法上不报错但实际存储仍然是升序的。MySQL 8.0真正支持了降序索引B树按降序存储。CREATE INDEX idx_time_desc ON orders (create_time DESC)B树按降序存储解决了ORDER BY create_time DESC和索引方向不一致导致Using filesort的问题。如果你的业务有大量的按时间倒序查询降序索引能省掉排序开销。排查工具箱知道索引为什么失效还得知道怎么排查。实际项目里排查索引问题主要靠这几个工具。EXPLAIN最基础也最常用的工具。几个关键字段的判断逻辑type为ALL表示全表扫描没走任何索引。这是排查的起点。key为NULL表示优化器没有选用任何索引。key_len反映联合索引实际用了几个字段用来判断最左前缀是否完整生效。rows是优化器预估的扫描行数和实际行数差太多说明统计信息可能有问题。Extra里的Using filesort表示需要额外排序Using temporary表示用了临时表都值得关注。一个快速诊断的思路先看type是不是ALL如果是检查WHERE条件里有没有触发第一类失效的写法函数、计算、类型转换、LIKE通配符、最左前缀。如果WHERE条件看起来没问题看rows估算是否合理不合理就ANALYZE TABLE更新统计信息。统计信息更新后还不走索引再考虑用Optimizer Trace看优化器到底怎么算的。Optimizer TraceEXPLAIN告诉你优化器选了什么不告诉你为什么。Optimizer Trace能看到优化器完整的思考过程。SET optimizer_trace enabledon; SELECT * FROM orders WHERE user_id 100; SELECT * FROM information_schema.OPTIMIZER_TRACE;Trace的输出是一个JSON里面记录了优化器对每个索引的成本计算、为什么选了A而不是B。能看到全表扫描的基线成本、每个索引的范围扫描成本、最终选择的理由。这个工具在排查「明明有索引为什么不走」的问题时特别有用。线上不建议长期开启有性能开销。ANALYZE TABLE统计信息过期是第二类失效的常见原因。ANALYZE TABLE重新采样统计信息操作简单但效果明显。ANALYZE TABLE orders;执行后优化器会基于新的统计信息重新估算成本。大表上执行要选低峰期执行时间取决于表大小。FORCE INDEX当优化器确实判断错了可以强制指定索引。SELECT * FROM orders FORCE INDEX(idx_user_create) WHERE user_id 100 AND create_time 2024-01-01;FORCE INDEX告诉优化器必须用指定的索引。这个要谨慎使用统计信息更新后优化器的判断可能是对的强制指定反而走了更慢的路径。用之前先用EXPLAIN对比一下两种方案的成本确认走索引确实更快再强制。速查表把所有索引失效场景汇总在下面这张表里。面试前可以过一遍。场景失效类型根本原因是否绝对失效解决方案对索引列使用函数B树有序性破坏函数改变了索引列原始值是改写SQL或用函数索引对索引列表达式计算B树有序性破坏计算后的值在B树中无序是计算移到等号右边隐式类型转换B树有序性破坏CAST作用在索引列上是保持查询条件与字段类型一致LIKE以%开头B树有序性破坏前缀未知无法定位起点是前缀匹配或用搜索引擎不满足最左前缀B树有序性破坏跳过最左列找不到B树入口是8.0有Skip Scan例外调整索引顺序或补齐前导列范围查询截断B树有序性破坏区间内后续列无序是范围字段放索引最后字符集不一致B树有序性破坏转换作用在索引列上是统一字符集和排序规则回表成本过高优化器成本决策回表随机IO成本超过全表扫描否取决于数据量覆盖索引或只查需要的列OR一侧无索引优化器成本决策一侧不走索引则全部不走否取决于条件建索引或改UNION ALL否定条件!、、NOT IN优化器成本决策通常命中大多数行否取决于数据分布改写为正向条件IS NOT NULL优化器成本决策通常命中大多数行否取决于NULL值比例字段加NOT NULL DEFAULTIN列表过长优化器成本决策估算精度下降可能误判否取决于统计信息调大估算阈值或改JOIN统计信息过期优化器成本决策成本估算偏差导致误选否ANALYZE TABLE小结这篇内容尝试换个角度不背场景列表从两个根因出发推导。第一类是B树的有序性被破坏。索引存的是原始值查询条件一旦改变了这个值函数、计算、类型转换、字符集转换或者在B树上找不到起点LIKE通配符、跳过最左列、范围截断索引就物理上不可用。这类失效是绝对的和数据量无关、和统计信息无关。第二类是优化器的成本决策。索引能用但优化器算了一笔账认为走索引的成本比全表扫描还高。回表太多、OR一侧没索引、否定条件命中太多行、IN列表太长导致估算失准、统计信息过期这些都是成本计算的输入出了问题。这类失效不是绝对的同一个SQL在不同数据量、不同统计信息下可能走也可能不走。面试的时候被问到索引失效先分类。哪些是索引物理上不可用哪些是优化器主动放弃。把分类讲清楚再展开具体场景比上来就背「LIKE百分号、函数、类型转换」要有说服力得多。面试官想听的不是你记了多少个场景是你理解不理解背后的机制。排查线上慢查询的时候也一样。先EXPLAIN看type是不是ALL再看WHERE条件有没有触发第一类失效的写法。第一类排除了再看rows估算准不准统计信息是不是过期。这个排查路径比上来就改SQL要靠谱。面试被问到索引失效不用再背场景列表。问自己两个问题就够了B树还能不能定位如果能优化器为什么不用