Appearance
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 谓词、索引定义、数据类型/字符集、统计信息逐项排除,不能只背“失效口诀”。分层要点:
- 两类失效:结构不可用时,索引根本无法定位(
possible_keys往往为 NULL);成本不选时,索引能用但优化器算下来更贵(possible_keys有、key为 NULL)。前者要改写 SQL/补索引,后者要更新统计、做覆盖索引、调开关或接受全表,处理方式完全不同。 - 列上做运算/函数:
DATE(create_time)=...、id+1=10、CAST(col AS ...)、CONCAT(col,...)、JSON_EXTRACT(...)都会让索引存的原值无法直接比较;应改写成对列本身的范围条件,或用 8.0 函数索引/生成列。 - 隐式类型转换:字符串列与数字比较时,MySQL 把字符串列转成数字(列上发生函数),索引失效;数字列与字符串比较通常只转换常量,索引仍可用。字符集/排序规则不一致、日期与字符串格式不匹配也会触发转换。
- LIKE / OR / NOT / NULL:前导
%无法确定索引前缀;OR可能走 index merge,也可能全表;!=/NOT IN可能走范围但选择性差;IS NULL可以用索引,col = NULL永远不成立(NULL 语义,不是索引问题)。 - 索引结构边界:联合索引看最左前缀;范围列(
>、<、BETWEEN)会截断后续列的定位能力,但后续列仍可被 ICP 过滤、被覆盖索引读取;IN通常按多个等值区间处理,和范围列不同。前缀索引只存前缀,不能覆盖完整值,也不能按完整列排序。 - 优化器成本:小表、结果占比高、回表随机 IO 多、统计过期/数据倾斜、
ORDER BY ... LIMIT偏好排序索引,都会让优化器主动放弃索引;FORCE INDEX可能把计划变得更差。判断要看optimizer_trace的候选成本,而不是只看“有没有索引”。 - SQL 结构与对象:JOIN 被驱动表无索引会走 BNL/hash join;子查询/派生表可能物化;
UNION去重会建临时表;GROUP BY/ORDER BY与索引顺序不一致会 filesort/temporary;分区裁剪失效、视图TEMPTABLE、JSON/TEXT/全文/空间索引类型不匹配也都会“看起来没走索引”。 - 排查与修复顺序:先 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 → 可用索引顺序,避免 filesorttext
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 IN | status != 1 | 可能走范围但选择性差 | 改写/接受 |
NOT LIKE | NOT LIKE 'abc%' | 否定条件无法定位 | 改写/全文 |
col = NULL | WHERE col = NULL | 永远不成立(NULL 语义) | IS NULL |
REGEXP | name REGEXP '^a' | 通常无法用 B+ 树 | 全文/ES/前缀改写 |
| 函数索引表达式不匹配 | 索引建在 LOWER(name),查询用 UPPER(name) | 表达式不同,无法匹配 | 统一表达式/改写 |
- 索引结构边界:
| 场景 | 示例 | 原因 | 修复 |
|---|---|---|---|
| 违反最左前缀 | (a,b,c) 查 b=1 | 无法定位 | 补 (b)/调整索引 |
| 范围截断 | a=1 AND b>2 AND c=3 | c 不能继续定位 | 接受 ICP/调整列序 |
| 列序错误 | (a,c,b) 查 a,b,c | b 在范围后 | 调整列序 |
| 前缀索引 | 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/IN | range_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/加索引 |
UNION | UNION 去重 | 临时表 | UNION ALL |
GROUP BY/ORDER BY | 顺序与索引不一致 | filesort/temporary | 调整索引/列序 |
HAVING 聚合 | HAVING COUNT(*)>1 | 聚合后过滤 | 子查询/接受 |
| 分区裁剪失效 | 条件不含分区键 | 扫所有分区 | 带分区键/检查裁剪 |
视图 TEMPTABLE | ALGORITHM=TEMPTABLE | 物化 | 改 MERGE/直接查基表 |
| JSON 直接查 | JSON_EXTRACT(doc,'$.x')=1 | JSON 无 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 要优化 |
Extra | Using index/index condition/filesort/temporary | 覆盖/ICP/排序/临时表 |
SHOW WARNINGS | 改写后的 SQL | 看隐式转换/函数 |
optimizer_trace | 候选成本 | 为什么没选 |
SHOW INDEX | 列序/基数/Visible | 索引定义/不可见 |
EXPLAIN ANALYZE | 实际行数/时间 | 估算 vs 实际 |
| 慢日志 | Top SQL | 排查入口 |
深挖点(面试官常追的 6 个):
- “失效”不等于“没索引”:
possible_keys有、key为空,说明优化器考虑过但算下来更贵;这时加索引可能没用,甚至更糟。只有possible_keys为空,才更像结构上不可用。这个区分能避免“见全表就加索引”的反射。 - 隐式转换的方向决定生死:字符串列 = 数字,转换发生在列上,索引失效;数字列 = 字符串,转换发生在常量上,索引通常仍可用。字符集/collation 不一致同理,转换可能落在列上。看
SHOW WARNINGS能看到改写后的cast。 - 最左前缀、范围截断、ICP 是三件事:最左前缀决定“能不能开始用”;范围列决定“后面的列还能不能定位”;ICP 决定“定位不到时能不能在索引层先过滤”。
IN是多个等值区间,常能让后续列继续用;>/BETWEEN则会截断。 OR、NOT IN、!=不是绝对失效:同列OR可走 range;不同列OR可能 index merge;!=/NOT IN可能走范围,只是结果占比高时优化器选全表。真正危险的是OR连接了无索引列,以及NOT IN遇到 NULL 的语义问题。ORDER BY ... LIMIT是“选错索引”的高发区:优化器可能为了排序索引扫描大量行,而 WHERE 条件其实很 selective;prefer_ordering_index(8.0.21)可以关掉这种偏好,或加 hint/改写。用optimizer_trace看两个计划的成本。- 8.0 新特性会制造“假失效”:函数索引要求表达式完全匹配;skip scan 只适合前导列低基数;不可见索引本来就不参与选择;降序索引只解决混合排序;直方图只改善估算,不替代索引。看到这些特性,先确认版本、表达式和开关。
- “失效”不等于“没索引”:
追问链:
- 索引失效分几类? → 结构不可用(索引无法定位)和成本不选(能用但更贵);前者
possible_keys常为空,后者有候选但key为空。 - 怎么快速判断是“用不上”还是“不想用”? → 看 EXPLAIN 的
possible_keys和key:都为空是结构问题;有候选但没选是成本/统计问题;再看type、rows、filtered。 - 列上函数为什么导致失效? → 索引按列原值排序,函数后的值没有索引;只能改写成对原值的范围条件,或用函数索引/生成列。
DATE(create_time)=...怎么改? → 改成create_time >= '2024-01-01' AND create_time < '2024-01-02',保留列本身。id+1=10怎么改? → 代数移项成id=9,不要在列上做运算。- 隐式转换什么方向会失效? → 字符串列与数字比较时,字符串列被转成数字,索引失效;数字列与字符串比较通常只转常量,索引可用。
- 为什么字符串列 = 数字会失效? → MySQL 按数字比较,对列做
CAST(col AS DOUBLE),等于在列上套函数,无法用原值索引。 - 数字列 = 字符串为什么不失效? → 字符串常量被转成数字,列本身不变,索引仍按原值可用。
- 日期比较怎么写? → 用范围
>= 当天 AND < 次日;不要对列套DATE()/DATE_FORMAT(),也不要用不匹配的字符串格式。 - 字符集/collation 不一致为什么失效? → 比较时需要转换字符集/排序规则,转换可能落在列上;JOIN 两列 collation 不同尤其常见,统一字符集/collation。
- 前导
%怎么办? → 普通 B+ 树无法定位;用全文索引、ES、反向索引,或改成前缀匹配LIKE 'abc%'。 LIKE 'abc%'一定走索引吗? → 通常可以,但要看 collation、是否被函数包裹、是否使用前缀索引;不能一概而论。OR一定失效吗? → 不一定。同列OR可走 range;不同列各有索引可能 index merge;连接无索引列才容易全表。OR怎么优化? → 能合并成同列范围就合并;否则拆UNION ALL,或补联合索引;index merge只是兜底。!=/NOT IN一定失效吗? → 不一定,可能走范围,但结果占比高时优化器会选全表;NOT IN还要注意 NULL 语义。IS NULL能走索引吗? → 能。InnoDB 索引会存 NULL,IS NULL可以用索引;col = NULL永远不成立,不是索引问题。col = NULL为什么查不到? → SQL 三值逻辑,NULL 与任何值比较结果都是 UNKNOWN;要用IS NULL/IS NOT NULL。- 最左前缀是什么? → 联合索引
(a,b,c)从最左列开始连续使用;跳过a一般不能用,8.0 skip scan 在低基数场景例外。 (a,b,c)查b能用吗? → 一般不能;除非 8.0 skip scan 且a基数很低、优化器认为划算。- 范围列后的
c还能用吗? → 不能继续定位;可以被 ICP 过滤、被覆盖索引读取;排序能否用要看 ORDER BY 是否以范围列为起点。 IN和>对后续列有什么区别? →IN是多个等值区间,后续列可能继续用;>/</BETWEEN是范围,后续列不能定位。- 联合索引列序错了怎么发现? → 看
key_len是否只用到前几列、Extra是否 filesort、rows是否偏大;对照索引定义和 WHERE 列序。 - 前缀索引为什么不能覆盖/排序? → 只存列的前 n 个字符,没有完整值;覆盖需要回表校验,排序也无法按完整列保证。
- 索引不可见会怎样? → 优化器完全忽略它,和删除效果类似,但可以快速改回 VISIBLE;用于安全下线。
- 小表为什么全表扫描? → 全表只需少量页,走二级索引再回表更贵;这不是故障,不要盲目加索引。
- 结果占比高为什么全表? → 回表随机 IO + 大量行,优化器估算全表顺序扫更便宜;可做覆盖索引或接受。
- 统计信息过期怎么处理? →
ANALYZE TABLE;检查innodb_stats_persistent、采样页数、自动重算;数据倾斜时建直方图。 - 直方图解决什么问题? → 改善非均匀数据的基数估算,帮助优化器选对计划;它不创建索引,也不改变数据访问结构。
ORDER BY ... LIMIT为什么选错? → 优化器可能为排序索引扫描大量行;用prefer_ordering_index=off、hint 或改写,比较两个计划的成本。FORCE INDEX能解决问题吗? → 能短期止血,但数据分布变化后会反噬;优先改 SQL、补索引、更新统计,FORCE INDEX只做兜底。- JOIN 没索引会怎样? → 被驱动表全表扫,走 BNL/hash join;OLTP 高并发下代价高,给被驱动表 join 列加索引。
- 子查询/UNION 为什么用不上索引? → 可能物化成临时表、去重排序,索引在临时表上不一定存在;改写 EXISTS/JOIN、用
UNION ALL。 - GROUP BY/ORDER BY 怎么用索引? → 让分组/排序列与索引前缀、方向一致;范围列会截断后续排序;否则 filesort/temporary。
- 分区表为什么没裁剪? → WHERE 不含分区键或分区表达式不匹配;分区裁剪失效会扫所有分区,索引也救不了。
- JSON/TEXT 怎么建索引? → JSON 用生成列/函数索引;TEXT 用前缀索引或全文索引;不能直接对完整 JSON/TEXT 建普通 B+ 树索引。
- 8.0 函数索引/skip scan 能救哪些场景? → 函数索引救“表达式完全匹配”的查询;skip scan 救前导列低基数且未被条件使用的场景;都不是万能的。
- 怎么用 EXPLAIN/optimizer_trace 定位? → EXPLAIN 看候选/选择/type/key_len/rows/filtered/Extra;
SHOW WARNINGS看改写;optimizer_trace看候选成本和被拒原因。 - 修复索引失效的优先级? → 先改写 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 → 修复优先级与生产案例。