索引失效分析

开篇：明明建了索引，为什么还是全表扫描？

你信心满满地给查询字段加上了索引，EXPLAIN 一看——type = ALL，全表扫描。索引白建了？

这种情况比想象中常见。索引不是建了就一定会用，最终决定"用不用索引、用哪个索引"的是 MySQL 的优化器。优化器基于成本预估来做选择——它不看语义，不看你的意图，只看哪种方案的 I/O + CPU 成本最低。

理解索引失效的各种场景，是写出高性能 SQL 的必修课。

---

一、索引失效的十大场景

场景 1：违反最左前缀原则

联合索引 (a, b, c) 要求查询从最左列开始匹配。跳过最左列，索引完全用不上。

-- 有联合索引 idx_abc(a, b, c)

-- 能走索引：包含最左列 a
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 不走索引：缺少最左列 a
SELECT * FROM t WHERE b = 2 AND c = 3;

另外，跳过中间列后，右边的列也失效。比如 WHERE a = 1 AND c = 3 只能用到 a，c 用不上（但 ICP 索引下推可能会用 c 做过滤，减少回表）。

场景 2：对索引列使用函数

在索引列上套函数后，MySQL 无法直接在 B+ 树中定位，只能全表扫描后逐行计算。

-- create_time 有索引

-- 走索引
SELECT * FROM orders WHERE create_time = '2024-01-01';

-- 不走索引：YEAR() 函数改变了列值
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

解决方案：改写 SQL 避免对列使用函数：

-- 用范围查询代替函数
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

或者在 MySQL 8.0 中使用函数索引：

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

场景 3：对索引列做计算

和函数类似，对索引列做算术运算也会导致失效。

-- age 有索引

-- 走索引
SELECT * FROM student WHERE age = 12;

-- 不走索引：对列做了加法
SELECT * FROM student WHERE age + 1 = 12;

-- 走索引：把计算移到等号右边
SELECT * FROM student WHERE age = 12 - 1;

规则很简单：让索引列"干干净净"地出现在等号左边，所有计算都放到右边。

场景 4：隐式类型转换

当查询条件的类型和列定义的类型不一致时，MySQL 会做隐式类型转换，导致索引失效。

-- name 是 VARCHAR 类型

-- 走索引
SELECT * FROM student WHERE name = 'abc';

-- 不走索引：传了数字，MySQL 把 name 列转成数字来比较
SELECT * FROM student WHERE name = 123;

这里的关键在于 MySQL 的转换方向：是把列转成数字，还是把参数转成字符串。如果是把列转了，就等于在列上套了函数，索引自然失效。

反过来，INT 列传字符串则没问题——MySQL 会把字符串 '123' 转成数字 123，列本身不变：

-- age 是 INT 类型
-- 走索引：MySQL 把 '1' 转成数字 1，列本身不变
SELECT * FROM student WHERE age = '1';

记忆口诀

字符串列传数字 → 列被转 → 索引失效。
数字列传字符串 → 参数被转 → 索引正常。

场景 5：LIKE 左模糊

LIKE 的模糊匹配遵循最左前缀原则：

-- name 有索引

-- 走索引：左边确定
SELECT * FROM t WHERE name LIKE 'abc%';
SELECT * FROM t WHERE name LIKE 'ab%cd';

-- 不走索引：左边不确定
SELECT * FROM t WHERE name LIKE '%abc';
SELECT * FROM t WHERE name LIKE '%abc%';

道理和查字典一样：你知道一个词以"ab"开头，可以翻到"ab"那一节；你只知道以"bc"结尾，那就只能从头翻到尾。

优化方案：

1. 尽量用右模糊 LIKE 'xxx%'
2. 如果必须左模糊，考虑用全文索引或 Elasticsearch
3. MySQL 5.7+ 可以用虚拟列 + 反转的技巧来优化 LIKE '%xxx'：

-- 创建一个虚拟列，存储 name 的反转值
ALTER TABLE t ADD COLUMN v_name VARCHAR(50)
  GENERATED ALWAYS AS (REVERSE(name)) VIRTUAL;
ALTER TABLE t ADD INDEX idx_v_name(v_name);

-- 查 name 以 'abc' 结尾，变成查 v_name 以 'cba' 开头
SELECT * FROM t WHERE v_name LIKE 'cba%';

场景 6：范围条件右边的列失效

联合索引中，一旦某列出现范围条件，它右边的列就无法利用索引了。

-- 联合索引 idx_age_class_name(age, classId, name)

-- name 的索引失效：classId > 20 是范围条件，右边的 name 用不上
SELECT * FROM student
WHERE age = 30 AND classId > 20 AND name = 'abc';
-- 实际只用到 age 和 classId（范围扫描）

实践原则：创建联合索引时，把可能出现范围查询的列放到最右边。

场景 7：不等于 (!= / <>)

不等于条件通常无法利用索引的有序性——你没法在 B+ 树中定位"不等于某个值"的区间。

-- age 有索引

-- 不走索引（取决于数据量和分布）
SELECT * FROM student WHERE age != 18;
SELECT * FROM student WHERE age <> 18;

但这不是绝对的。如果是主键的不等于比较，优化器可能仍然选择索引：

-- 可能走索引：主键索引的 range 扫描
SELECT * FROM student WHERE id != 18;

而且如果是覆盖索引，即使有 !=，扫描索引树也比全表扫描划算。

场景 8：IS NOT NULL

-- name 有索引

-- 走索引
SELECT * FROM t WHERE name IS NULL;

-- 通常不走索引
SELECT * FROM t WHERE name IS NOT NULL;

IS NULL 等于精确匹配"空值"，可以走索引。IS NOT NULL 相当于"不等于 NULL"，逻辑类似于 !=，通常不走。

实践建议

设计表时尽量给字段设置 NOT NULL DEFAULT（数字默认 0，字符串默认 ''），从根源上避免 NULL 带来的索引问题。

场景 9：OR 条件包含无索引列

-- age 有索引，classId 没有索引

-- 不走索引：OR 连接了一个无索引列
SELECT * FROM student WHERE age = 10 OR classId = 100;

OR 的语义是"满足其中一个就行"。即使 age 有索引能过滤一部分，classId 没索引还是要全表扫描。两者取并集——既然 classId 这边必须全表扫，那 age 的索引也没意义了，干脆整体全表扫。

但如果 OR 两边的列都有索引，且都是等值比较，MySQL 可能触发索引合并（index_merge）：

-- age 和 name 各有独立索引
SELECT * FROM student WHERE age = 10 OR name = 'abc';
-- 可能显示 type=index_merge, Using union(age, name)

场景 10：字符集不一致

当两个表的字符集不同时，JOIN 条件中的列会做隐式字符集转换，等同于在列上套了函数，索引失效。

-- 表 A 是 utf8，表 B 是 utf8mb4
-- ON A.name = B.name 时，MySQL 会把 utf8 转成 utf8mb4
-- 相当于在 A.name 上套了 CONVERT() 函数，索引失效

预防措施：统一数据库和表的字符集。 推荐全部使用 utf8mb4。

---

二、LIKE 模糊查询的深度优化

除了前面提到的虚拟列反转技巧外，还有一种方案：分段存储。

比如你要查手机号的后 4 位，可以在表中增加一个 phone_last4 字段，专门存储后 4 位并建索引。查询时直接 WHERE phone_last4 = '1234'，精确匹配走索引。

ALTER TABLE users ADD COLUMN phone_last4 CHAR(4) AS (RIGHT(phone, 4)) STORED;
CREATE INDEX idx_phone_last4 ON users(phone_last4);

SELECT * FROM users WHERE phone_last4 = '1234';

这种方案适合查询模式固定的场景——你知道总是要查后 4 位，就把它独立出来。

---

三、函数和表达式导致的失效

3.1 传统规则：函数 = 失效

在 MySQL 8.0 之前，索引列上使用任何函数都会导致索引失效，因为索引是基于列的原始值排序的，函数改变了值。

-- 失效的常见写法
WHERE YEAR(create_time) = 2024
WHERE UPPER(name) = 'JOHN'
WHERE LENGTH(code) = 6
WHERE stuno + 1 = 900001

3.2 MySQL 8.0 函数索引

8.0 引入函数索引（Functional Index），允许对表达式建索引：

-- 对字符串转小写建索引
CREATE INDEX idx_lower_name ON users ((LOWER(name)));

-- 对邮箱域名建索引
CREATE INDEX idx_domain ON users ((SUBSTRING_INDEX(email, '@', -1)));

-- 对 JSON 字段的某个属性建索引
CREATE INDEX idx_status ON orders ((JSON_UNQUOTE(JSON_EXTRACT(info, '$.status'))));

函数索引的限制：

• 表达式必须是确定性的（同输入同输出），NOW()、RAND() 不行
• 存储函数和全文检索函数不能用
• 会增加写操作的维护成本

---

四、类型转换的隐式陷阱

类型转换是最容易被忽略的索引失效原因之一，因为 SQL 不会报错，只是默默变慢。

4.1 MySQL 的类型转换规则

当字符串和数字比较时，MySQL 的规则是：把字符串转成数字。

这意味着：

-- name 是 VARCHAR

-- name = 123 → MySQL 做 CAST(name AS SIGNED) = 123
-- 相当于在 name 上套了函数 → 索引失效

-- age 是 INT

-- age = '20' → MySQL 做 age = CAST('20' AS SIGNED) → age = 20
-- 转换发生在常量上，列不变 → 索引正常

4.2 字符集转换也是隐式转换

除了数字/字符串之间的转换，不同字符集之间的转换也会导致失效。最常见的场景是 utf8 和 utf8mb4 混用——MySQL 会把 utf8 列转成 utf8mb4 来比较，等于在列上套了函数。

---

五、OR 条件与 IN 的区别

5.1 OR 的行为

前面提到，OR 两边如果有一个没索引，整个查询就不走索引。即使两边都有索引，也不一定走——取决于是否触发索引合并。

而且，当 OR 两边涉及范围条件时，也可能不走索引：

-- name 和 age 各有索引

-- 走索引：OR 两边都是等值
SELECT * FROM t WHERE name = 'Hollis' OR age = 18;

-- 不走索引：OR 中有范围条件 >
SELECT * FROM t WHERE name = 'Hollis' OR age > 18;

5.2 IN 的行为

IN 在值比较少时，MySQL 会把它优化成多个等值条件的 OR，通常可以走索引。但如果 IN 中的值太多，优化器可能认为全表扫描更划算：

-- 走索引
SELECT * FROM t WHERE name IN ('a', 'b');

-- 值很多时，可能不走索引
SELECT * FROM t WHERE name IN ('a', 'b', 'c', ..., 'z');

5.3 NOT IN 和 NOT EXISTS

NOT IN 通常不走索引（原因类似 !=）。如果需要"排除某些值"的查询，可以考虑用 LEFT JOIN ... IS NULL 或 NOT EXISTS 来优化。

---

六、EXPLAIN 实战：如何判断索引是否生效

索引是否生效，最靠谱的方法是看 EXPLAIN 的输出。重点关注以下几个字段：

6.1 type（访问类型）

从好到差的排序：

type	含义	说明
const	主键/唯一索引等值查询	最快，最多返回一行
eq_ref	JOIN 中主键/唯一索引匹配	每次 JOIN 最多一行
ref	非唯一索引等值查询	常见的好情况
range	索引范围扫描	BETWEEN、>、<、IN 等
index	全索引扫描	扫了整棵索引树，但比全表扫描好
ALL	全表扫描	最差，需要优化

理想情况：type 至少是 ref 或 range。 如果看到 ALL，说明索引没有被用上。

6.2 key（实际使用的索引）

如果 key = NULL，说明没有用到任何索引。possible_keys 显示的是"可能用到的索引"，key 才是"实际用到的"。

6.3 rows（预估扫描行数）

这是优化器预估需要扫描的行数，不是精确值。rows 越小越好。

6.4 Extra（额外信息）

Extra 值	含义
Using index	覆盖索引，无需回表
Using index condition	索引下推（ICP）
Using where	Server 层还需要额外过滤
Using filesort	需要额外排序，没用上索引的有序性
Using temporary	用了临时表，通常需要优化

不希望看到的：Using filesort、Using temporary。 它们意味着额外的内存/磁盘开销。

6.5 判断索引生效的检查清单

1. key 不为 NULL → 用到了索引
2. type 是 ref/range/const 等 → 索引利用方式良好
3. Extra 是 NULL 或 Using index 或 Using index condition → 正常
4. rows 合理（远小于表总行数）→ 索引过滤效果好

如果 type = ALL, key = NULL, Extra = Using where，说明完全没走索引，需要排查原因。

---

七、常见面试题精选

Q1：索引失效怎么排查？

1. 用 EXPLAIN 查看执行计划，重点看 type、key、Extra
2. 如果没走索引，逐一检查：是否缺少索引？是否违反最左前缀？是否有函数/计算/隐式转换？
3. 如果走了索引但还是慢，检查：是否选错了索引（force index 对比）？扫描行数是否合理？是否需要覆盖索引？

Q2：为什么 MySQL 会选错索引？

优化器基于成本预估选索引，影响成本的因素包括：区分度、选择性、是否覆盖索引、是否有 ORDER BY、索引大小等。

选错的常见原因：

• 统计信息过时：用 ANALYZE TABLE 更新
• 数据分布不均匀：优化器预估的行数和实际差距大
• ORDER BY 干扰：为了避免排序，优化器可能选择了过滤性差的索引

解决方案：FORCE INDEX 强制指定索引、更新统计信息、优化查询语句、调整索引设计。

Q3：LIKE '%xx' 不走索引，有什么优化方案？

1. 改为右模糊 LIKE 'xx%'（如果业务允许）
2. 虚拟列反转 + 前缀查询（MySQL 5.7+）
3. 分段存储：把需要查的后缀独立成一个字段并建索引
4. 使用全文索引或搜索引擎（如 Elasticsearch）

Q4：用了索引还是很慢，可能是什么原因？

1. 选错索引：优化器选了一个区分度差的索引，扫描行数很多
2. 大量回表：索引命中很多行，每行都要回表取数据
3. 数据分布不均匀：索引的某些值对应大量数据，过滤效果差
4. SQL 本身有问题：SELECT *、多表 JOIN、子查询等
5. 硬件瓶颈：磁盘 I/O 跑满、内存不足导致 Buffer Pool 命中率低

Q5：什么时候索引失效反而更好？

听起来反直觉，但确实存在：

场景一：表很小。 几十行的表，全表扫描比走索引更快（免去了索引树遍历的开销）。

场景二：优化器选错了索引。 一条 SQL 可以走 A 索引和 B 索引，优化器选了 A 但 A 的过滤性很差。如果能让 A 索引"失效"（比如在 A 列上加 +0），优化器就会转而选择更高效的 B 索引。

-- 优化器因为 ORDER BY id 选了主键索引，但 state 索引过滤性更好
-- 让 id 索引失效，迫使优化器选 state 索引
SELECT * FROM tasks
WHERE state = 'INIT' AND id >= 100
ORDER BY id + 0  -- id + 0 让主键索引失效
LIMIT 100;

---

小结

失效场景	一句话解释
违反最左前缀	查询不包含联合索引的最左列
函数/计算	对索引列套函数或做运算
隐式类型转换	VARCHAR 列传数字，列被转成数字
左模糊 LIKE	%xxx 或 %xxx% 无法利用索引有序性
范围条件右边列	范围查询后的列索引失效
!= / IS NOT NULL	类似全表扫描的语义
OR 含无索引列	一边无索引，整个不走
字符集不一致	JOIN 时隐式字符集转换

排查三步走：1. 用 EXPLAIN 定位问题 → 2. 检查索引设计和 SQL 写法 → 3. 必要时 FORCE INDEX 或重建索引。