前言
在 MySQL 面试中，除了索引原理，索引失效是另一个高频考点。很多开发者自以为建了索引就能万事大吉，结果一条 SQL 就把索引"干废"了，导致全表扫描，性能急剧下降。
今天我们就来盘点最常见的 10 种索引失效场景，用最简短的例子说明原因，并附上避免方法。看完这篇文章，下次面试官问"什么情况下索引会失效"，你就能对答如流。
如何判断索引是否失效？
在开始之前，先复习一个工具：EXPLAIN。执行 EXPLAIN SELECT ... 后，重点关注两个字段：
type：访问类型，从好到差依次为system>const>eq_ref>ref>range>index>ALL。如果出现ALL（全表扫描），说明索引大概率失效了。key：实际使用的索引，为NULL表示没用到索引。
🔥 索引失效的 10 大场景
1. 违反最左前缀原则（针对联合索引）
错误示例：
-- 假设联合索引 idx_a_b_c (a, b, c)
SELECT * FROM t WHERE b = 2; -- ❌ 索引失效（跳过了 a）
SELECT * FROM t WHERE b = 2 AND c = 3; -- ❌ 索引失效
正确示例：
SELECT * FROM t WHERE a = 1 AND b = 2; -- ✅ 使用索引（用到 a、b）
SELECT * FROM t WHERE a = 1; -- ✅ 使用索引（用到 a）
原理：联合索引 (a, b, c) 相当于创建了 (a)、(a,b)、(a,b,c) 三个索引。查询条件必须从最左边的列开始，跳过左边列直接查右边会导致索引失效。
2. 使用 != 或 <> 操作符
错误示例：
SELECT * FROM user WHERE age != 18; -- ❌ 索引失效
正确示例：
SELECT * FROM user WHERE age < 18 OR age > 18; -- ✅ 可能使用索引
原理：不等于操作符通常会让索引失效（有时优化器会进行索引扫描，但效率低）。
3. 索引列参与了函数运算
错误示例：
-- 假设 create_time 有索引
SELECT * FROM order WHERE DATE(create_time) = '2025-03-17'; -- ❌ 索引失效
正确示例：
SELECT * FROM order WHERE create_time >= '2025-03-17' AND create_time < '2025-03-18'; -- ✅ 使用索引
原理：对索引列使用函数（包括隐式类型转换）会导致索引失效。
4. 类型隐式转换
错误示例：
-- 假设 mobile 字段是 VARCHAR 类型，且建有索引
SELECT * FROM user WHERE mobile = 13888888888; -- ❌ 索引失效
正确示例：
SELECT * FROM user WHERE mobile = '13888888888'; -- ✅ 使用索引
原理：如果字段类型与查询值类型不一致，MySQL 会进行隐式转换，导致索引失效。
5. LIKE 以通配符 % 开头
错误示例：
SELECT * FROM article WHERE title LIKE '%MySQL'; -- ❌ 索引失效
SELECT * FROM article WHERE title LIKE '%MySQL%'; -- ❌ 索引失效
正确示例：
SELECT * FROM article WHERE title LIKE 'MySQL%'; -- ✅ 使用索引
原理：LIKE 模糊查询时，如果通配符在开头，索引失效。
6. OR 连接的条件不是全部有索引
错误示例：
-- 假设 age 有索引，name 没有索引
SELECT * FROM user WHERE age = 18 OR name = '张三'; -- ❌ 索引失效
正确示例：
-- 方案1：为 OR 涉及的每个列都创建索引
SELECT * FROM user WHERE age = 18 OR name = '张三';
-- 方案2：改用 UNION
SELECT * FROM user WHERE age = 18
UNION ALL
SELECT * FROM user WHERE name = '张三';
原理：如果 OR 前后的条件中，有一个列没有索引，另一个列即使有索引也不会使用。
7. 索引列参与了计算或表达式
错误示例：
SELECT * FROM product WHERE price * 0.8 > 100; -- ❌ 索引失效
正确示例：
SELECT * FROM product WHERE price > 100 / 0.8; -- ✅ 使用索引
原理：在索引列上进行算术运算或其他表达式操作，索引失效。
8. 使用 NOT IN 或 NOT EXISTS
错误示例：
SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM order); -- ❌ 可能索引失效
正确示例：
-- 用 LEFT JOIN 优化
SELECT user.* FROM user
LEFT JOIN order ON user.id = order.user_id
WHERE order.user_id IS NULL;
原理：NOT IN 和 NOT EXISTS 可能导致索引失效，尤其是子查询返回大量数据时。
9. 数据分布不均匀，优化器放弃索引
错误示例：
-- 假设 status 字段只有两个值：0 和 1，且 90% 的数据 status=1
SELECT * FROM order WHERE status = 1; -- ❌ 优化器可能不走索引
原理：即使索引可用，如果 MySQL 优化器判断全表扫描比索引更快（比如查询结果占全表很大比例），就会放弃索引。
解决：这种情况通常不需要干预，优化器选择的是最优方案。
10. 索引列是 NULL 值查询
错误示例：
SELECT * FROM user WHERE email IS NULL; -- ❌ 索引失效（取决于数据分布）
正确示例：
-- 建议：设计表时尽量避免列允许为 NULL
CREATE TABLE user (
id INT PRIMARY KEY,
email VARCHAR(255) NOT NULL -- 设置为 NOT NULL
);
原理：对于 IS NULL 或 IS NOT NULL，索引是否失效取决于 MySQL 版本和数据分布。
✅ 总结：索引失效避坑指南
| 场景 | 示例 | 解决 |
|---|---|---|
| 违反最左前缀 | WHERE b=1 (联合索引 a,b) | 查询条件中包含最左列 |
| 使用 != 或 <> | WHERE age != 18 | 用 > 和 < 组合代替 |
| 索引列参与函数 | WHERE DATE(create_time)=... | 改为范围查询 |
| 类型隐式转换 | WHERE mobile=138... | 字符串加引号 |
| LIKE 以 % 开头 | WHERE name LIKE '%张' | 避免前缀 % |
| OR 条件不全有索引 | WHERE age=1 OR name='a' | 所有列建索引或改 UNION |
| 索引列参与计算 | WHERE price*0.8 >100 | 计算放右边 |
| NOT IN / NOT EXISTS | WHERE id NOT IN (…) | 用 LEFT JOIN 改写 |
| 数据分布不均 | WHERE status=1 (90%数据是1) | 优化器选择，不用干预 |
| NULL 查询 | WHERE email IS NULL | 列设为 NOT NULL |
结语
索引失效是 SQL 性能优化的核心问题之一。掌握这些场景，不仅能帮你通过面试，更能在实际工作中写出高效的 SQL。
记住： "SQL 是否使用索引，跟数据库版本、数据量、数据选择度都有关系" 。在实际开发中，多用 EXPLAIN 分析执行计划，不要仅凭经验判断。
本文mysql索引失效文档，适合面试和实际开发参考。索引优化是一个持续的过程，随着数据量增长和业务变化，需要不断调整和优化。
欢迎关注我的公众号【SilkyStarter】 ，加入技术交流群，获取更多 MySQL、Java 干货！
