数据库索引失效总结

声明：如果没有特别说明，默认指 MySQL 的 InnoDB 存储引擎。涉及版本行为差异的地方会单独标注（以 MySQL 8.0 为主要视角，对照 5.7）。

索引建好了不等于会走。面试里问"索引为什么会失效"，本质是在考两件事：

你是否理解 B+ 树索引的结构性限制（有些写法在结构上就没法用索引定位）；
你是否理解 优化器的成本决策（能用但"不划算"时，优化器会主动放弃索引）。

把这两条主线记住，零散的失效场景就都能自己推导出来。

1 索引失效的本质：两类原因

第一类：结构性失效。 B+ 树是按索引列的原始值有序排列的目录：

索引 (a, b) 的 B+ 树示意：

                    [ a=10 ]  [ a=20 ]
                   /          |          \
        [a=10,b=1][a=10,b=5]  [a=20,b=2][a=20,b=8]
             按先 a 后 b 整体有序存放

一旦查询条件不是"拿原始值去和有序目录做前缀比对"，索引就无法定位。对列做函数、左模糊、跳过最左列直接查 b，都是这一类。

第二类：成本性失效。 二级索引叶子节点存的是"主键值"，查到之后还要拿主键回聚集索引再查一次（回表）。如果优化器估算出"满足条件的行太多，回表的随机 IO 比全表扫描的顺序 IO 还贵"，就会放弃索引。

所以严格说，很多"失效"场景的准确表述是：默认情况下通常不走，但优化器最终按成本决定。面试时说出这层 nuance 是加分项。

排查永远以 EXPLAIN 为准，看 key（实际用的索引）、type（访问类型）、rows（估算行数）、Extra。

2 十大经典失效场景

2.1 对索引列做函数或运算

sql

-- 失效：等价于对每一行先算 YEAR() 再比较，B+ 树里存的是原始 datetime
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

-- 改写：保持索引列"干净"，把运算挪到常量侧
SELECT * FROM orders
WHERE create_time >= '2024-01-01 00:00:00'
  AND create_time <  '2025-01-01 00:00:00';

原因：B+ 树中存的是列的原始值，检索时必须能用条件值直接在有序目录里二分。对列套函数后，树的序和比较的序对不上，只能全表逐行套函数。

同理失效的写法还有：

WHERE id + 1 = 10（应写成 id = 9）
WHERE SUBSTR(name, 1, 3) = 'abc'
WHERE DATE(create_time) = '2024-06-06'

版本差异（MySQL 8.0.13+）：支持函数索引/表达式索引，可以直接对表达式建索引：

sql

CREATE INDEX idx_year ON orders ((YEAR(create_time)));

5.7 没有这个能力，只能改写 SQL 或冗余一列。

2.2 隐式类型转换（最高频的生产事故来源）

规则记一句话：字符串列和数字比较时，MySQL 会把列转成数字，等价于对列套了 CAST，于是索引失效。反过来，数字列和字符串比较时，转的是常量，索引还能用。

sql

-- phone 是 varchar，存 '13800001234'
SELECT * FROM user WHERE phone = 13800001234;   -- 不走索引（列被隐式 CAST 成数字）
SELECT * FROM user WHERE phone = '13800001234'; -- 走索引

危险之处在于：这种 SQL 不会返回错误，结果还可能是对的（大多数行能匹配上）。它只在数据量上来之后才暴露为慢查询。更坑的是 ''、'abc' 这类非数字字符串转数字会变成 0，可能匹配到一堆脏数据。

2.3 隐式字符集/排序规则转换

多表 JOIN 时，如果两个关联列字符集或排序规则不一致，MySQL 会在其中一侧做转换。被转换那一侧的索引失效。例如老表是 utf8/utf8mb3 或 latin1，新表是 utf8mb4。

典型场景：5.7 升 8.0 的过渡期，8.0 默认字符集是 utf8mb4、默认排序规则 utf8mb4_0900_ai_ci，而 5.7 建的表多为 utf8mb4_general_ci 或 utf8。两表 collation 不同时 JOIN，慢好几倍。
规范：库内所有表统一 utf8mb4 + 同一 collation；迁移后用 SHOW CREATE TABLE 核对。

2.4 LIKE 以 % 开头

sql

SELECT * FROM user WHERE name LIKE '张%';   -- 走索引：前缀确定，可以定位到"张"开头的区间
SELECT * FROM user WHERE name LIKE '%三';   -- 不走：前缀未知，等于查任意结尾
SELECT * FROM user WHERE name LIKE '%三%';  -- 不走

原因还是目录的序：索引按前缀排序，只有前缀确定才能二分。规避手段：

能改业务语义就改成右模糊；
确实要左模糊/中间匹配，冗余一个反转列 name_reverse，查 LIKE '三%' 后反转回来；
真正的自由文本搜索交给全文索引（FULLTEXT，5.7.6+ 内置 ngram 分词支持中文）或外部搜索引擎。

2.5 不满足最左前缀

联合索引 (a, b, c) 相当于先按 a 排、a 相同按 b 排、再按 c 排。查询条件必须从最左列开始连续命中：

sql

INDEX (a, b, c)

WHERE a = 1 AND b = 2 AND c = 3;  -- a,b,c 都能用于定位
WHERE b = 2 AND c = 3;            -- 不走（跳过了 a）
WHERE a = 1 AND c = 3;            -- 只走 a；c 无法定位（a 相同时 c 并不有序）

注意 MySQL 优化器会把 WHERE c = 3 AND b = 2 AND a = 1 自动调整顺序再匹配。所以 WHERE 里条件的书写顺序无所谓，关键是有没有最左列。

版本差异（MySQL 8.0.13+）：引入了跳跃扫描（Skip Scan）。当联合索引的前导列取值很少（如性别、状态位）时，即使不给前导列条件。优化器也可能按前导列的每个值分别走一次 range，相当于"枚举前缀"。5.7 没有该能力。所以"跳过最左列必失效"这句话在 8.0 里要加限定条件。

2.6 OR 连接了没有索引的列

sql

-- a 有索引，b 没有索引
SELECT * FROM t WHERE a = 1 OR b = 2;  -- 全表扫描

OR 的语义是"两侧任一满足"，只要有一侧无法走索引，就必须扫全表才能保证不漏行。若 a、b 都有索引，优化器可能走 index_merge（对两个索引各做 range 再合并），EXPLAIN 的 type 会显示 index_merge。但 index_merge 效率不一定好（回表多次），有时优化器仍选全表扫描，可用 UNION 改写：

sql

SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;   -- 注意 UNION 有去重语义，业务允许时用 UNION ALL 更快

2.7 范围查询后面的列断掉

sql

INDEX (a, b, c)

WHERE a = 1 AND b > 10 AND c = 3;
-- a、b 用于定位；c 无法继续定位：b 是范围后，"b 区间内"的记录 c 不再整体有序

要点：范围列之后的列不能用于定位。但 5.6+ 的 ICP（Index Condition Pushdown，索引条件下推）可以把 c 的判断下推到存储引擎层。它在扫描索引时就过滤掉不满足的行，减少回表次数。EXPLAIN 的 Extra 出现 Using index condition 就是 ICP 生效。所以准确的说法是"失效于定位，仍可能用于过滤"，这也是一条高频追问。

2.8 区分度低 / 回表成本高

区分度公式：COUNT(DISTINCT col) / COUNT(*)。性别、状态这类列区分度极低：

即使走了索引，也几乎要把整个索引扫一遍，再对每条结果做一次回表随机 IO；
优化器估算后通常直接选全表扫描（顺序 IO + 顺序读，反而快）。

配套知识点：

二级索引回表的代价是"随机 IO"，全表扫描是"顺序 IO"，这是成本对比的原理；
统计信息不准确会导致误判，ANALYZE TABLE t; 可重新采样；
MySQL 8.0 支持直方图统计（ANALYZE TABLE t UPDATE HISTOGRAM ON col;），对没有索引的列给优化器提供分布信息，5.7 没有；
确认索引更优而优化器不选时，可以 WHERE ... FORCE INDEX(idx_xxx) 强制（属于止血手段，慎用）。

2.9 !=、NOT IN、<> 这类"否定条件"

经典说法是"否定条件不走索引"，这个表述不严谨：

WHERE id != 100：如果排除的行很少（结果集占全表比例小），优化器可能走 range；
NOT IN (子查询) 常见问题是子查询展开成相关子查询后性能差，改写为 LEFT JOIN ... IS NULL 或 NOT EXISTS 通常更好；
本质仍是成本问题：否定条件过滤后的结果集一般很大，倾向于全表扫描。

面试回答"!= 会不会失效"的正确姿势：先说不是必然失效，再讲成本决策逻辑。

2.10 IS NOT NULL / IS NULL

IS NULL 和 IS NOT NULL 都可以走索引（B+ 树会把 NULL 排在最前面一起索引）。是否走取决于 NULL/非 NULL 占比——如果非 NULL 占了 95%，IS NOT NULL 的结果集太大，优化器就会弃用索引。所以"IS NOT NULL 必失效"也是以讹传讹。

2.11 排序失效：ORDER BY 不满足最左前缀

严格说这是"索引无法完成排序"，结果是 Using filesort（filesort 不一定是坏事，小结果集内存排序很快）：

sql

INDEX (a, b)

WHERE a = 1 ORDER BY b;        -- 走索引，序天然满足
WHERE a = 1 ORDER BY b DESC;   -- 反向扫索引即可
ORDER BY b;                    -- 跳过 a，filesort
WHERE a > 1 ORDER BY b;        -- a 是范围，b 在 a 的多个区间内不整体有序，通常 filesort

版本差异（MySQL 8.0）：降序索引（INDEX(a ASC, b DESC)）真正生效。5.7 会解析 DESC 关键字但实际忽略，统一按正序存，ORDER BY a ASC, b DESC 只能 filesort；8.0 则能按混合序扫描索引避免 filesort。

3 失效场景速查表

#	场景	典型写法	原因分类	常用对策
1	列上做函数/运算	`YEAR(t)=2024`	结构性	改写为区间；8.0.13+ 建函数索引
2	隐式类型转换	varchar 列 = 数字	结构性	常量侧加引号
3	隐式字符集转换	join 两侧 collation 不同	结构性	统一 utf8mb4
4	左模糊	`LIKE '%xx'`	结构性	冗余反转列/全文检索
5	违反最左前缀	跳过前导列	结构性	调整索引列顺序；8.0.13+ Skip Scan
6	OR 混入无索引列	`a=1 OR b=2`	结构性	补索引或 UNION 改写
7	范围后列断	`a>1 AND b=2`	结构性	范围列尽量放联合索引最后；ICP 兜底
8	区分度低/回表贵	状态列查询	成本性	不强求走索引，考虑覆盖/改设计
9	否定条件	`!=`、`NOT IN`	成本性	按 cost 看，改 NOT EXISTS
10	NULL 判断	`IS NOT NULL`	成本性	看 NULL 占比
11	ORDER BY 前缀断	`ORDER BY b`	排序失效	索引序对齐；8.0 降序索引

4 排查"没走索引"的标准动作

1、EXPLAIN 看 key 是否为 NULL、type 是否 ALL、rows 估算。MySQL 8.0.18+ 可用 EXPLAIN ANALYZE 真实执行并输出每步实际耗时/行数（注意它会真的跑一遍语句）；
2、检查 WHERE 条件是否符合上面 11 类场景，优先怀疑函数、类型转换、左模糊；
3、SHOW CREATE TABLE 核对索引定义、列类型、字符集；
4、怀疑统计信息时 ANALYZE TABLE 后重新 EXPLAIN；
5、确认优化器选错时临时 FORCE INDEX 止血，长期靠改 SQL/改索引。

5 追问链（面试官会怎么往下问）

Q：为什么 LIKE '%x' 不能走索引而 LIKE 'x%' 可以？ A：B+ 树按前缀排序，前缀确定才能二分定位区间；后缀信息在目录里无序。
Q：为什么字符串列 = 数字会让索引失效，反过来数字列 = 字符串却不会？ A：转换规则是"字符串向数字对齐"。前者把列变成数字（等价对列套 CAST）；后者只转常量，列保持干净。
Q：范围查询后断掉的列就完全没用了？ A：不能用于定位，但 5.6+ 的 ICP 可以在存储引擎层用索引列内容先过滤，减少回表，EXPLAIN 里表现为 Using index condition。
Q：!= 到底走不走索引？ A：不绝对，取决于过滤后结果集占比（成本模型）。答"必失效"是减分项。
Q：MySQL 8 在"索引失效"话题上有哪些新东西？ A：函数索引（8.0.13）、Skip Scan（8.0.13）、真正生效的降序索引（8.0）。不可见索引（8.0，ALTER TABLE ... ALTER INDEX ... INVISIBLE，删索引前先隐藏灰度验证，5.7 只能靠 gh-ost/pt-osc 模拟）。直方图辅助成本估算（8.0）。
Q：怎么证明一条 SQL 走没走索引？ A：EXPLAIN 看 key/type/rows/Extra；8.0.18+ 用 EXPLAIN ANALYZE 看真实执行计划和耗时。

6 小结

索引失效分两大类。结构性：B+ 树无法定位，包括函数、隐式转换、左模糊、最左前缀、OR。成本性：能用但不划算，包括区分度低、回表贵、否定条件；
面试答题先分类、再讲原理、最后给对策。并主动带出版本差异（函数索引/Skip Scan/降序索引/不可见索引都是 8.0 的考点）；
所有结论以 EXPLAIN 为准，养成"先看执行计划再下判断"的习惯；
索引列保持"干净"（不参与函数和运算）、类型与字符集对齐、联合索引把范围列放最后。优先覆盖索引避免回表。这四条能避开绝大多数失效场景。

2026-10 依据主题重写（原文件为空壳）。

01-MySQL核心

02-分库分表与分布式ID

03-列存与分析引擎

01-可靠性三件套

02-选型与演进

01-基础内功

02-缓存实战

03-集群与高可用

04-版本与生态

01-IoC与Bean

02-AOP

03-Web与常用机制

04-事务与升级进阶

jboss

netty

tomcat

拓展阅读

JVM内存管理

JVM性能兼容、故障处理

数据库索引失效总结 ​

1 索引失效的本质：两类原因 ​

2 十大经典失效场景 ​

2.1 对索引列做函数或运算 ​

2.2 隐式类型转换（最高频的生产事故来源） ​

2.3 隐式字符集/排序规则转换 ​

2.4 LIKE 以 % 开头 ​

2.5 不满足最左前缀 ​

2.6 OR 连接了没有索引的列 ​

2.7 范围查询后面的列断掉 ​

2.8 区分度低 / 回表成本高 ​

2.9 !=、NOT IN、<> 这类"否定条件" ​

2.10 IS NOT NULL / IS NULL ​

2.11 排序失效：ORDER BY 不满足最左前缀 ​

3 失效场景速查表 ​

4 排查"没走索引"的标准动作 ​

5 追问链（面试官会怎么往下问） ​

6 小结 ​