MySQL 索引失效的常见场景与底层原因解析

索引是 MySQL 性能优化的核心武器，但很多开发者都遇到过“明明建了索引，查询却依然慢如蜗牛”的情况。这背后往往是索引失效在作祟。本文将系统梳理索引失效的常见场景，并深入剖析其底层原因，帮助你从“知其然”到“知其所以然”。

一、为什么索引会失效？

在深入具体场景之前，有必要先理解 MySQL 优化器的工作原理。MySQL 的查询优化器是基于成本模型（Cost-Based Optimizer, CBO）来选择执行计划的。优化器会估算走索引和全表扫描各自的代价，然后选择代价更低的方案。

索引失效的本质，是优化器判断“走索引的代价 ≥ 全表扫描的代价”，或者 SQL 写法导致 B+ 树索引无法被有效利用。理解这一点，后面的场景就很容易融会贯通。

二、常见索引失效场景及底层解析

1. 对索引列使用函数或表达式

-- 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 索引有效
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

底层原因：B+ 树索引存储的是列的原始值，并按原始值排序。当对列施加函数后，索引中并不存在 YEAR(created_at) 的结果，优化器无法利用 B+ 树的有序性进行快速定位，只能逐行计算函数值再比较，退化为全表扫描。

2. 隐式类型转换

-- phone 列是 VARCHAR 类型，但传入的是数字
SELECT * FROM users WHERE phone = 13800138000;

底层原因：当字符串列与数字比较时，MySQL 会将字符串列隐式转换为数字再比较。这相当于对索引列施加了 CAST(phone AS SIGNED) 函数，索引自然失效。反之，如果数字列与字符串比较，则字符串会被转为数字，索引仍然有效。记住口诀：字符串列遇数字，索引必失效。

3. 前导模糊查询（LIKE '%xxx'）

-- 索引失效
SELECT * FROM users WHERE name LIKE '%张%';

-- 索引有效
SELECT * FROM users WHERE name LIKE '张%';

底层原因：B+ 树索引是按照字符从左到右的顺序排列的。LIKE '张%' 能确定前缀，可以在 B+ 树中定位到起始位置后顺序扫描。而 LIKE '%张%' 无法确定起始位置，只能全表扫描逐个匹配。这就像查字典时知道拼音首字母能快速翻到对应页，但只知道中间某个字母就只能一页页翻。

4. 联合索引不满足最左前缀原则

-- 联合索引 (a, b, c)
SELECT * FROM t WHERE b = 1 AND c = 2;  -- 索引失效
SELECT * FROM t WHERE a = 1 AND c = 2;  -- a 走索引，c 不走

底层原因：联合索引的 B+ 树是先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。如果跳过 a 直接查 b，B+ 树中 b 的值是全局无序的（只在 a 相同的小范围内有序），无法进行二分查找。因此必须从最左列开始连续匹配。

5. 使用 OR 连接非索引列

-- 假设 name 有索引，age 无索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

底层原因：OR 要求两个条件中任一满足即可。即使 name 能走索引，age 条件仍需全表扫描来补全结果集，优化器权衡后往往直接选择全表扫描。如果 OR 两侧的列都有索引，则可能使用索引合并（Index Merge）。

6. 范围查询后的列无法使用索引

-- 联合索引 (a, b, c)
SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 3;

底层原因：a 等值匹配后，b 在 a 的范围内有序，可以做范围扫描。但 b 进行范围查询后，c 在 b 的范围内虽然有序，但 b 有多个值，导致 c 全局无序，无法再用索引定位。因此 c 只能作为过滤条件在回表后筛选。

7. 使用不等于（!= 或 <>）或 NOT IN

SELECT * FROM users WHERE status != 1;

底层原因：B+ 树适合快速定位“等于某个值”或“某个范围”的数据。不等于意味着要查找除某个值之外的所有数据，这通常覆盖了大部分数据行，优化器认为不如直接全表扫描。

8. IS NULL / IS NOT NULL 的误区

实际上，MySQL 的 B+ 树索引是存储 NULL 值的，IS NULL 和 IS NOT NULL 在很多情况下是可以走索引的。是否走索引取决于 NULL 值的比例和表的数据量。这一点常被误传为“索引失效”，需要根据执行计划具体分析。

三、如何判断索引是否失效？

使用 EXPLAIN 命令查看执行计划是关键：

EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;

重点关注以下字段：

• type：ALL 表示全表扫描，ref/range/eq_ref 等表示使用了索引。
• key：实际使用的索引名称，为 NULL 表示未使用索引。
• rows：预估扫描行数，越大越可能全表扫描。
• Extra：出现 Using filesort、Using temporary 需警惕。

四、总结与建议

实践建议：

1. 索引列上避免任何函数和运算，把计算放到条件值一侧。
2. 保证查询条件的数据类型与列定义一致。
3. 联合索引设计时，将等值查询列放前面，范围查询列放后面。
4. 用 EXPLAIN 验证每一个慢查询的执行计划，不要凭感觉判断。
5. 记住核心原则：索引失效的本质是 B+ 树的有序性被破坏，或优化器判断走索引不划算。

理解底层原理，比死记硬背场景更重要。当你真正理解了 B+ 树的结构和优化器的成本模型，索引失效的各种场景都能自行推导出来。

未经允许不得转载：任鹏个人博客 » MySQL 索引失效的常见场景与底层原因解析