阿里、字节面试必问：MySQL 索引失效的 8 种场景，这次彻底搞懂！
前言
索引建了，查询却还是慢？90% 的可能是索引失效了。
阿里、字节面试官最爱问：你知道哪些情况会导致索引失效？怎么解决？
今天一文讲透 8 种索引失效场景，附实战案例 + 避坑指南，面试直接满分回答！🚀
为什么索引会失效？
一句话核心：索引失效的本质是破坏了 B+ 树的有序性。
当 MySQL 无法利用索引的有序结构快速定位数据时，就会放弃索引，走全表扫描。
下面这 8 种场景，就是面试官最爱挖的坑 👇
场景 1：模糊查询 LIKE 以 % 开头
sql
SELECT * FROM user WHERE name LIKE ‘%张’;
❌ 失效原因：% 在左边，字符串排序的“前缀”未知，无法利用 B+ 树的有序性。
✅ 解决方案：
尽量避免 % 开头，可考虑改用全文索引（MATCH AGAINST）
如果业务允许，改用 LIKE ‘张%’（范围查询，可走索引）
场景 2：对索引列使用函数或计算
sql
SELECT * FROM user WHERE YEAR(create_time) = 2026;
❌ 失效原因：索引存的是原始值，不是函数处理后的值。
✅ 解决方案：
改写成范围查询：
create_time BETWEEN ‘2026-01-01’ AND ‘2026-12-31’
如果必须用函数，考虑建立函数索引（MySQL 8.0.13+ 支持）
场景 3：隐式类型转换
sql
– phone 字段是 VARCHAR 类型，但查询条件是数字
SELECT * FROM user WHERE phone = 13800138000;
❌ 失效原因：MySQL 会将字符串隐式转换成数字，相当于对索引列用了 CAST(phone AS SIGNED)，触发了函数操作。
✅ 解决方案：类型保持一致，数字加引号：
WHERE phone = ‘13800138000’
场景 4：联合索引不满足最左前缀
sql
– 联合索引 (a, b, c)
SELECT * FROM table WHERE b = 1 AND c = 2; – 没带 a
❌ 失效原因：B+ 树先按 a 排序，再按 b 排序，跳过 a 直接查 b，无法定位。
✅ 解决方案：
调整查询条件，带上最左列
或根据业务需求，新建以 b 开头的索引
场景 5：范围查询右边的列失效
sql
– 联合索引 (a, b)
SELECT * FROM table WHERE a > 1 AND b = 2;
❌ 失效原因：a 用了范围查询（>），导致 a 字段后面的 b 无法利用索引有序性。
✅ 解决方案：
将范围查询的字段放在联合索引的最后
如果业务无法调整，考虑对 b 单独建索引
场景 6：使用 OR 连接条件（两边不一定有索引）
sql
SELECT * FROM user WHERE id = 1 OR name = ‘张三’;
– 假设 id 有索引，name 无索引
❌ 失效原因：OR 只要有一边没索引，MySQL 就可能放弃索引，走全表扫描（除非用 UNION 优化）。
✅ 解决方案：
确保 OR 两边的字段都有索引
或改写成 UNION：
sql
SELECT * FROM user WHERE id = 1
UNION
SELECT * FROM user WHERE name = ‘张三’;
场景 7：使用 NOT、!=、<>
sql
SELECT * FROM user WHERE status != 1;
❌ 失效原因：不等值操作无法利用 B+ 树的等值查找特性，优化器通常认为全表扫描更快（除非数据分布极不均匀）。
✅ 解决方案：
如果业务允许，改为两个范围查询：status < 1 OR status > 1
如果这种查询很频繁，考虑用索引合并或重新设计索引
场景 8：IS NULL / IS NOT NULL 使用不当
sql
SELECT * FROM user WHERE name IS NOT NULL;
❌ 失效原因：IS NULL 在某些情况下可以走索引，但 IS NOT NULL 通常走不了（除非表中大部分数据都是 NULL）。
✅ 解决方案：
尽量避免对索引列使用 IS NOT NULL
如果业务需要，可以设置默认值（如空字符串 ‘’），然后用 = 查询
总结：索引失效速查表
场景 原因 解决
LIKE ‘%xxx’ 前缀未知 避免 % 开头，或用全文索引
对索引列用函数 无法利用有序值 改范围查询，或用函数索引
隐式类型转换 相当于用了函数 保持类型一致
不满足最左前缀 跳过联合索引左列 调整查询或新建索引
范围查询右边列 范围破坏有序性 范围字段放最后
OR 含无索引列 优化器放弃索引 两边建索引，或用 UNION
NOT、!=、<> 无法等值查找 改范围查询，或接受全表
IS NOT NULL 优化器认为慢 避免使用，用默认值代替
最后一句真理：索引失效，十有八九是因为你写的 SQL 让 B+ 树找不到北了。
下次写 SQL 前，先问自己一句：这列的数据现在是有序的吗？ —— 答案一出，失效自破。
觉得有用？点个「收藏」，面试前翻出来看一遍，直接碾压面试官！🔥
评论区留下你遇到过的“奇葩”索引失效场景，大家一起避坑！👇