Skip to content

4.1-7 索引失效场景全清单 ​

所属:04 数据存储 / 4.1 MySQL 分级:A 状态:◐ 初稿 关联:4.1-5、4.1-6、4.1-8、4.1-9、4.1-10、4.1-16

  • 题型:排查 / 清单

  • 一句话结论:索引失效要分两类看:结构上不能用(列上函数/运算、隐式转换、前导 %、违反最左前缀、范围截断、字符集/collation 不一致)和优化器成本上不愿用(小表、结果占比高、回表太多、统计过期/倾斜、ORDER BY ... LIMIT 选错)。排查先看 EXPLAIN 的 possible_keys/key/type/key_len/rows/filtered/Extra,再对照 SQL 谓词、索引定义、数据类型/字符集、统计信息逐项排除,不能只背“失效口诀”。

  • 分层要点:

    1. 两类失效:结构不可用时,索引根本无法定位(possible_keys 往往为 NULL);成本不选时,索引能用但优化器算下来更贵(possible_keys 有、key 为 NULL)。前者要改写 SQL/补索引,后者要更新统计、做覆盖索引、调开关或接受全表,处理方式完全不同。
    2. 列上做运算/函数:DATE(create_time)=...、id+1=10、CAST(col AS ...)、CONCAT(col,...)、JSON_EXTRACT(...) 都会让索引存的原值无法直接比较;应改写成对列本身的范围条件,或用 8.0 函数索引/生成列。
    3. 隐式类型转换:字符串列与数字比较时,MySQL 把字符串列转成数字(列上发生函数),索引失效;数字列与字符串比较通常只转换常量,索引仍可用。字符集/排序规则不一致、日期与字符串格式不匹配也会触发转换。
    4. LIKE / OR / NOT / NULL:前导 % 无法确定索引前缀;OR 可能走 index merge,也可能全表;!=/NOT IN 可能走范围但选择性差;IS NULL 可以用索引,col = NULL 永远不成立(NULL 语义,不是索引问题)。
    5. 索引结构边界:联合索引看最左前缀;范围列(>、<、BETWEEN)会截断后续列的定位能力,但后续列仍可被 ICP 过滤、被覆盖索引读取;IN 通常按多个等值区间处理,和范围列不同。前缀索引只存前缀,不能覆盖完整值,也不能按完整列排序。
    6. 优化器成本:小表、结果占比高、回表随机 IO 多、统计过期/数据倾斜、ORDER BY ... LIMIT 偏好排序索引,都会让优化器主动放弃索引;FORCE INDEX 可能把计划变得更差。判断要看 optimizer_trace 的候选成本,而不是只看“有没有索引”。
    7. SQL 结构与对象:JOIN 被驱动表无索引会走 BNL/hash join;子查询/派生表可能物化;UNION 去重会建临时表;GROUP BY/ORDER BY 与索引顺序不一致会 filesort/temporary;分区裁剪失效、视图 TEMPTABLE、JSON/TEXT/全文/空间索引类型不匹配也都会“看起来没走索引”。
    8. 排查与修复顺序:先 EXPLAIN 看 possible_keys/key/type/key_len/rows/filtered/Extra;再 SHOW WARNINGS 看改写、optimizer_trace 看成本;然后检查谓词、索引列序、类型/字符集、统计信息;最后改写 SQL、补/调索引、做覆盖、更新统计,压测回归。不要一上来就 FORCE INDEX。
  • 图/流程:

text
EXPLAIN 第一层判断:
  ├─ possible_keys = NULL
  │    → 结构不可用
  │       → 列上函数/运算?隐式转换?前导 %?最左前缀?索引不可见?
  └─ possible_keys 有,key = NULL
       → 优化器不选
          → 结果占比高?小表?回表多?统计过期/倾斜?ORDER BY LIMIT?
text
失效分类:
结构不可用                          成本不选
  函数/运算                          小表
  隐式转换                           结果占比高
  前导 %                             回表成本高
  最左前缀/范围截断                   统计过期/倾斜
  字符集/collation                    ORDER BY ... LIMIT
  → 改写 SQL / 补索引                 → 统计/覆盖/调开关
text
隐式转换方向:
varchar_col = 123
  → CAST(varchar_col AS DOUBLE) = 123
  → 列上发生函数,索引失效

int_col = '123'
  → '123' 转成 123
  → 列本身不变,索引可用
text
idx(a,b,c):
  a=1                          → 用 a
  a=1 AND b=2                  → 用 a,b
  a=1 AND b>2 AND c=3          → 用 a,b;c 只能 ICP
  a=1 AND b IN (1,2) AND c=3   → 多个等值区间,c 可能用上
  b=1                          → 不能用(skip scan 例外)
  a=1 AND b>2 ORDER BY b,c     → 可用索引顺序,避免 filesort
text
a=1 OR b=2(a、b 各有索引)
  → index merge union,或全表
a=1 OR b=2(b 无索引)
  → 全表
改写:
  SELECT ... WHERE a=1
  UNION ALL
  SELECT ... WHERE b=2 AND a<>1
  • 版本/源码锚点:

    • MySQL 5.6:ICP、MRR、BKA。
    • MySQL 5.7:生成列(可建索引)、optimizer cost tables;derived_merge(5.7.6)。
    • MySQL 8.0:函数索引、skip scan(8.0.13)、直方图、不可见索引、降序索引;EXPLAIN ANALYZE、hash join(8.0.18);prefer_ordering_index(8.0.21);derived_condition_pushdown(8.0.22)。
    • 关键默认值:eq_range_index_dive_limit=200(8.0,5.7 为 10)、range_optimizer_max_mem_size=8MB、innodb_stats_persistent_sample_pages=20、innodb_stats_auto_recalc=ON。
    • optimizer_switch 相关:index_condition_pushdown、mrr、mrr_cost_based、index_merge、index_merge_union、index_merge_sort_union、index_merge_intersection、skip_scan、prefer_ordering_index、derived_condition_pushdown。
    • 源码:sql/opt_range.cc(range 分析与索引选择)、sql/opt_costmodel.cc(成本模型)、sql/opt_explain.cc、sql/sql_select.cc(JOIN/子查询)、sql/sql_derived.cc(派生表)、storage/innobase/row/row0sel.cc(row_search_mvcc、ICP)、handler/ha_innodb.cc(index_read_map)、dict/dict0stats.cc(统计信息)。
  • 两类失效:

类型本质典型场景EXPLAIN 信号处理
结构不可用索引无法定位函数、隐式转换、前导 %、最左前缀缺失possible_keys=NULL改写/补索引
成本不选能用但更贵小表、结果占比高、回表多、统计过期possible_keys 有、key=NULL统计/覆盖/调开关
  • 谓词与表达式:
场景示例原因修复
列上函数DATE(create_time)='2024-01-01'列被函数包裹,无法用索引改范围 >= AND <
列上运算id+1=10同上改写 id=9
隐式转换(字符串列)varchar_col=123字符串列转数字,列上发生函数加引号/类型一致
隐式转换(字符集/collation)utf8mb4_col = utf8_col字符集/排序规则转换统一字符集/collation
前导 %LIKE '%abc'无法确定索引前缀全文/ES/反向索引
任意位置 %LIKE '%abc%'同上全文/ES
OR 非索引列a=1 OR b=2(b 无索引)只能全表UNION/补索引
!= / NOT INstatus != 1可能走范围但选择性差改写/接受
NOT LIKENOT LIKE 'abc%'否定条件无法定位改写/全文
col = NULLWHERE col = NULL永远不成立(NULL 语义)IS NULL
REGEXPname REGEXP '^a'通常无法用 B+ 树全文/ES/前缀改写
函数索引表达式不匹配索引建在 LOWER(name),查询用 UPPER(name)表达式不同,无法匹配统一表达式/改写
  • 索引结构边界:
场景示例原因修复
违反最左前缀(a,b,c) 查 b=1无法定位补 (b)/调整索引
范围截断a=1 AND b>2 AND c=3c 不能继续定位接受 ICP/调整列序
列序错误(a,c,b) 查 a,b,cb 在范围后调整列序
前缀索引name(10) 查完整值/排序只存前缀全列索引/调整覆盖
索引列参与计算col*2=10同函数改写
索引不可见ALTER INDEX ... INVISIBLE优化器忽略改回 VISIBLE
索引未建/被删—无可用索引补索引
低选择性/结果占比高status=1 返回 80%全表更便宜联合索引/接受
小表几百行全表更便宜接受
  • 优化器与成本:
场景示例原因修复
统计过期大量增删后计划变差估算错误ANALYZE TABLE
数据倾斜热门值占多数平均基数骗人直方图/改写
回表成本高二级索引命中多随机 IO 估算高覆盖索引/联合索引
ORDER BY ... LIMIT有排序索引但 WHERE 选择性高优化器偏好排序索引prefer_ordering_index=off/hint
大 IN 列表IN (1000 个值)超过 eq_range_index_dive_limit 用统计分批/临时表
range 内存超限复杂 OR/INrange_optimizer_max_mem_size简化 SQL/调大
优化开关关闭index_merge=off/skip_scan=off优化器禁用按需开启
FORCE INDEX 选错强制低效索引人为干预去掉/换索引
  • SQL 结构与特殊类型:
场景示例原因修复
JOIN 被驱动表无索引JOIN b ON a.id=b.a_id全表/哈希连接被驱动表加索引
子查询/派生表IN (SELECT ...)/FROM (SELECT ...)物化/半连接改写 EXISTS/JOIN/加索引
UNIONUNION 去重临时表UNION ALL
GROUP BY/ORDER BY顺序与索引不一致filesort/temporary调整索引/列序
HAVING 聚合HAVING COUNT(*)>1聚合后过滤子查询/接受
分区裁剪失效条件不含分区键扫所有分区带分区键/检查裁剪
视图 TEMPTABLEALGORITHM=TEMPTABLE物化改 MERGE/直接查基表
JSON 直接查JSON_EXTRACT(doc,'$.x')=1JSON 无 B+ 树索引生成列/函数索引
TEXT/BLOB 全列ORDER BY text_col无法全列索引前缀/全文/ES
全文/空间LIKE 查全文列索引类型不匹配MATCH/空间索引
  • 排查命令/字段:
命令/字段看什么判断
EXPLAIN possible_keys候选索引通常 NULL = 结构不可用
key实际选择NULL 但有候选 = 成本不选
type访问方式ALL = 全表;range/ref/eq_ref 较好
key_len用了索引前几列反推最左前缀/列序
rows/filtered估算行数/过滤比例大 rows 小 filtered 要优化
ExtraUsing index/index condition/filesort/temporary覆盖/ICP/排序/临时表
SHOW WARNINGS改写后的 SQL看隐式转换/函数
optimizer_trace候选成本为什么没选
SHOW INDEX列序/基数/Visible索引定义/不可见
EXPLAIN ANALYZE实际行数/时间估算 vs 实际
慢日志Top SQL排查入口
  • 深挖点(面试官常追的 6 个):

    1. “失效”不等于“没索引”:possible_keys 有、key 为空,说明优化器考虑过但算下来更贵;这时加索引可能没用,甚至更糟。只有 possible_keys 为空,才更像结构上不可用。这个区分能避免“见全表就加索引”的反射。
    2. 隐式转换的方向决定生死:字符串列 = 数字,转换发生在列上,索引失效;数字列 = 字符串,转换发生在常量上,索引通常仍可用。字符集/collation 不一致同理,转换可能落在列上。看 SHOW WARNINGS 能看到改写后的 cast。
    3. 最左前缀、范围截断、ICP 是三件事:最左前缀决定“能不能开始用”;范围列决定“后面的列还能不能定位”;ICP 决定“定位不到时能不能在索引层先过滤”。IN 是多个等值区间,常能让后续列继续用;>/BETWEEN 则会截断。
    4. OR、NOT IN、!= 不是绝对失效:同列 OR 可走 range;不同列 OR 可能 index merge;!=/NOT IN 可能走范围,只是结果占比高时优化器选全表。真正危险的是 OR 连接了无索引列,以及 NOT IN 遇到 NULL 的语义问题。
    5. ORDER BY ... LIMIT 是“选错索引”的高发区:优化器可能为了排序索引扫描大量行,而 WHERE 条件其实很 selective;prefer_ordering_index(8.0.21)可以关掉这种偏好,或加 hint/改写。用 optimizer_trace 看两个计划的成本。
    6. 8.0 新特性会制造“假失效”:函数索引要求表达式完全匹配;skip scan 只适合前导列低基数;不可见索引本来就不参与选择;降序索引只解决混合排序;直方图只改善估算,不替代索引。看到这些特性,先确认版本、表达式和开关。
  • 追问链:

    1. 索引失效分几类? → 结构不可用(索引无法定位)和成本不选(能用但更贵);前者 possible_keys 常为空,后者有候选但 key 为空。
    2. 怎么快速判断是“用不上”还是“不想用”? → 看 EXPLAIN 的 possible_keys 和 key:都为空是结构问题;有候选但没选是成本/统计问题;再看 type、rows、filtered。
    3. 列上函数为什么导致失效? → 索引按列原值排序,函数后的值没有索引;只能改写成对原值的范围条件,或用函数索引/生成列。
    4. DATE(create_time)=... 怎么改? → 改成 create_time >= '2024-01-01' AND create_time < '2024-01-02',保留列本身。
    5. id+1=10 怎么改? → 代数移项成 id=9,不要在列上做运算。
    6. 隐式转换什么方向会失效? → 字符串列与数字比较时,字符串列被转成数字,索引失效;数字列与字符串比较通常只转常量,索引可用。
    7. 为什么字符串列 = 数字会失效? → MySQL 按数字比较,对列做 CAST(col AS DOUBLE),等于在列上套函数,无法用原值索引。
    8. 数字列 = 字符串为什么不失效? → 字符串常量被转成数字,列本身不变,索引仍按原值可用。
    9. 日期比较怎么写? → 用范围 >= 当天 AND < 次日;不要对列套 DATE()/DATE_FORMAT(),也不要用不匹配的字符串格式。
    10. 字符集/collation 不一致为什么失效? → 比较时需要转换字符集/排序规则,转换可能落在列上;JOIN 两列 collation 不同尤其常见,统一字符集/collation。
    11. 前导 % 怎么办? → 普通 B+ 树无法定位;用全文索引、ES、反向索引,或改成前缀匹配 LIKE 'abc%'。
    12. LIKE 'abc%' 一定走索引吗? → 通常可以,但要看 collation、是否被函数包裹、是否使用前缀索引;不能一概而论。
    13. OR 一定失效吗? → 不一定。同列 OR 可走 range;不同列各有索引可能 index merge;连接无索引列才容易全表。
    14. OR 怎么优化? → 能合并成同列范围就合并;否则拆 UNION ALL,或补联合索引;index merge 只是兜底。
    15. !=/NOT IN 一定失效吗? → 不一定,可能走范围,但结果占比高时优化器会选全表;NOT IN 还要注意 NULL 语义。
    16. IS NULL 能走索引吗? → 能。InnoDB 索引会存 NULL,IS NULL 可以用索引;col = NULL 永远不成立,不是索引问题。
    17. col = NULL 为什么查不到? → SQL 三值逻辑,NULL 与任何值比较结果都是 UNKNOWN;要用 IS NULL/IS NOT NULL。
    18. 最左前缀是什么? → 联合索引 (a,b,c) 从最左列开始连续使用;跳过 a 一般不能用,8.0 skip scan 在低基数场景例外。
    19. (a,b,c) 查 b 能用吗? → 一般不能;除非 8.0 skip scan 且 a 基数很低、优化器认为划算。
    20. 范围列后的 c 还能用吗? → 不能继续定位;可以被 ICP 过滤、被覆盖索引读取;排序能否用要看 ORDER BY 是否以范围列为起点。
    21. IN 和 > 对后续列有什么区别? → IN 是多个等值区间,后续列可能继续用;>/</BETWEEN 是范围,后续列不能定位。
    22. 联合索引列序错了怎么发现? → 看 key_len 是否只用到前几列、Extra 是否 filesort、rows 是否偏大;对照索引定义和 WHERE 列序。
    23. 前缀索引为什么不能覆盖/排序? → 只存列的前 n 个字符,没有完整值;覆盖需要回表校验,排序也无法按完整列保证。
    24. 索引不可见会怎样? → 优化器完全忽略它,和删除效果类似,但可以快速改回 VISIBLE;用于安全下线。
    25. 小表为什么全表扫描? → 全表只需少量页,走二级索引再回表更贵;这不是故障,不要盲目加索引。
    26. 结果占比高为什么全表? → 回表随机 IO + 大量行,优化器估算全表顺序扫更便宜;可做覆盖索引或接受。
    27. 统计信息过期怎么处理? → ANALYZE TABLE;检查 innodb_stats_persistent、采样页数、自动重算;数据倾斜时建直方图。
    28. 直方图解决什么问题? → 改善非均匀数据的基数估算,帮助优化器选对计划;它不创建索引,也不改变数据访问结构。
    29. ORDER BY ... LIMIT 为什么选错? → 优化器可能为排序索引扫描大量行;用 prefer_ordering_index=off、hint 或改写,比较两个计划的成本。
    30. FORCE INDEX 能解决问题吗? → 能短期止血,但数据分布变化后会反噬;优先改 SQL、补索引、更新统计,FORCE INDEX 只做兜底。
    31. JOIN 没索引会怎样? → 被驱动表全表扫,走 BNL/hash join;OLTP 高并发下代价高,给被驱动表 join 列加索引。
    32. 子查询/UNION 为什么用不上索引? → 可能物化成临时表、去重排序,索引在临时表上不一定存在;改写 EXISTS/JOIN、用 UNION ALL。
    33. GROUP BY/ORDER BY 怎么用索引? → 让分组/排序列与索引前缀、方向一致;范围列会截断后续排序;否则 filesort/temporary。
    34. 分区表为什么没裁剪? → WHERE 不含分区键或分区表达式不匹配;分区裁剪失效会扫所有分区,索引也救不了。
    35. JSON/TEXT 怎么建索引? → JSON 用生成列/函数索引;TEXT 用前缀索引或全文索引;不能直接对完整 JSON/TEXT 建普通 B+ 树索引。
    36. 8.0 函数索引/skip scan 能救哪些场景? → 函数索引救“表达式完全匹配”的查询;skip scan 救前导列低基数且未被条件使用的场景;都不是万能的。
    37. 怎么用 EXPLAIN/optimizer_trace 定位? → EXPLAIN 看候选/选择/type/key_len/rows/filtered/Extra;SHOW WARNINGS 看改写;optimizer_trace 看候选成本和被拒原因。
    38. 修复索引失效的优先级? → 先改写 SQL(去掉列上函数/转换、拆 OR、加范围)→ 再补/调联合索引/覆盖索引 → 再更新统计/直方图 → 最后才考虑 hint/FORCE INDEX。
  • 生产案例/取舍(示例,替换成你的项目):

    • 手机号字段是 varchar,查询写成 phone=13800138000,隐式转换导致全表;加引号后走索引。
    • 订单按天查询写成 DATE(create_time)='2024-01-01',改成范围条件后索引生效。
    • 联合索引 (status, create_time),查询 status=? AND create_time>? ORDER BY id,范围截断导致排序失效;评估 (status, id) 或接受 filesort。
    • a=? OR b=? 两端各有索引,出现 index merge,CPU 偏高;改成 UNION ALL 后稳定。
    • JOIN 两表字符集不同,被驱动表索引失效;统一 utf8mb4/collation 后恢复。
    • 小表全表扫描被误判为故障,盲目加索引反而增加写成本;确认行数后接受。
    • 状态字段返回 60% 行,优化器选全表;不再强行加索引,改为覆盖索引/业务分页。
    • 统计信息过期导致计划突变;ANALYZE TABLE + 持久化统计 + 监控后恢复。
    • 大 IN 列表查询变慢,eq_range_index_dive_limit 导致估算变粗;分批查询/临时表后稳定。
    • 深分页 LIMIT 1000000,20 扫大量行;延迟关联/游标分页后 RT 下降。
    • JSON 字段用 JSON_EXTRACT 查询无法走索引;加生成列 + 索引后恢复。
    • 取舍:改写 SQL 换索引可用,代价是业务代码调整;补索引换读性能,代价是写放大;覆盖索引换少回表,代价是索引变宽;FORCE INDEX 换短期稳定,代价是长期风险。
  • 易错点:

    • 认为 EXPLAIN key=NULL 就一定是索引失效,忽略“成本不选”。
    • 认为 possible_keys 有索引就一定会用。
    • 认为列上函数一定失效,忽略 8.0 函数索引。
    • 认为隐式转换只会发生在字符串列;忽略数字列=字符串通常仍可用。
    • 认为 LIKE 'abc%' 一定走索引,忽略 collation/函数/前缀索引。
    • 认为 OR 一定全表,忽略同列 range 和 index merge。
    • 认为 !=/NOT IN 一定不走索引,忽略范围扫描和选择性。
    • 认为 IS NULL 不能走索引。
    • 认为 col = NULL 能查出 NULL。
    • 认为联合索引任意列都能单独用。
    • 认为范围列后完全没用,忽略 ICP 和“ORDER BY 以范围列为起点”。
    • 认为 IN 和 > 一样会截断后续列。
    • 认为前缀索引可以覆盖完整列值/支持完整排序。
    • 认为索引不可见和删除完全一样,忽略可快速恢复。
    • 认为小表全表扫描是故障。
    • 认为结果占比高加索引就能解决。
    • 认为统计信息实时准确,忽略采样和倾斜。
    • 认为 FORCE INDEX 是正解。
    • 认为加索引一定解决 JOIN 慢。
    • 认为分区表一定自动裁剪。
    • 认为 SELECT * 不影响索引选择。
    • 认为 8.0 所有函数都能直接建函数索引。
  • 30 秒口述稿:

索引失效先分两类:结构上不能用,比如列上函数/运算、隐式转换、前导 %、违反最左前缀、范围截断、字符集不一致;以及优化器成本上不愿用,比如小表、结果占比高、回表太多、统计过期、ORDER BY LIMIT 选错。排查看 EXPLAIN 的 possible_keys 和 key:前者为空是结构问题,有候选但没选是成本问题;再看 type、key_len、rows、filtered、Extra,配合 SHOW WARNINGS 和 optimizer_trace。修复顺序是改写 SQL、补/调索引、更新统计,最后才用 hint。

  • 3 分钟展开顺序:结论(两类失效 + 排查顺序)→ 结构不可用:函数/运算、隐式转换、前导 % → 结构边界:最左前缀、范围截断、IN、前缀索引 → 成本不选:小表/占比/回表/统计/ORDER BY LIMIT → OR/NOT IN/!=/NULL 的真实行为 → JOIN/子查询/UNION/分区/JSON → EXPLAIN 字段与 optimizer_trace → 修复优先级与生产案例。