我刚工作的时候，有次上线了个新功能，结果 SQL 查询慢得要命。DBA 帮我一看执行计划，发现索引失效了，全表扫描 2000 万条数据。

从那以后，我每次写完 SQL 都会检查索引是否失效，避免线上事故。

今天咱们就来聊聊 MySQL 索引失效的七种情况，看完这篇，你就能避开 90% 的索引失效坑。

索引失效是啥？

索引失效指的是：你以为 SQL 会走索引，结果 MySQL 优化器觉得走索引更慢，放弃了索引，改成全表扫描。

验证索引是否失效

-- 看执行计划
EXPLAIN SELECT * FROM users WHERE age = 25;

如果 key 字段是 NULL，说明索引失效了。

情况 1：对索引字段用函数

问题：对索引字段用函数，索引失效。

-- age 有索引，但用了函数，索引失效
EXPLAIN SELECT * FROM users WHERE LEFT(name, 5) = 'Alice';

为什么失效？ 索引存的是字段的原始值，不是函数计算后的值。MySQL 没法用索引快速定位，只能全表扫描。

优化方案：改成范围查询或者等号查询。

-- 优化后：用 LIKE 前缀匹配（能用索引）
SELECT * FROM users WHERE name LIKE 'Alice%';

如果一定要用函数，考虑生成列（Generated Column）+ 索引。

-- 创建生成列，存 name 的前 5 个字符
ALTER TABLE users ADD COLUMN name_prefix CHAR(5) AS (LEFT(name, 5)) STORED;

-- 给生成列加索引
CREATE INDEX idx_name_prefix ON users(name_prefix);

-- 查询时用生成列
SELECT * FROM users WHERE name_prefix = 'Alice';

情况 2：隐式类型转换

问题：查询条件的类型和索引字段的类型不一致，导致隐式类型转换，索引失效。

-- age 是 INT，但查询条件传了字符串，索引失效
EXPLAIN SELECT * FROM users WHERE age = '25';

为什么失效？ MySQL 会把索引字段 age 转换成字符串，再和 '25' 比较（相当于对索引字段用了函数）。

优化方案：查询条件的类型和索引字段的类型保持一致。

-- 优化后：传 INT
SELECT * FROM users WHERE age = 25;

隐藏得更深的类型转换

-- user_id 是 VARCHAR(50)，但查询条件传了 INT，索引失效
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

为什么失效？ MySQL 会把 user_id 转换成数字，再和 123 比较（相当于对索引字段用了函数）。

优化方案：查询条件的类型和索引字段的类型保持一致。

-- 优化后：传字符串
SELECT * FROM orders WHERE user_id = '123';

情况 3：模糊查询前缀通配符

问题：LIKE 查询用前缀通配符（% 开头），索引失效。

-- name 有索引，但 LIKE 前缀通配符，索引失效
EXPLAIN SELECT * FROM users WHERE name LIKE '%Alice';

为什么失效？ 索引是有序的，LIKE '%Alice' 没法用索引快速定位（因为 '%' 匹配任意字符）。

优化方案 1：改成后缀通配符（% 结尾）。

-- 优化后：LIKE 后缀通配符（能用索引）
SELECT * FROM users WHERE name LIKE 'Alice%';

优化方案 2：用覆盖索引（只查索引字段）。

-- 优化后：覆盖索引（能用索引）
SELECT name FROM users WHERE name LIKE '%Alice';

优化方案 3：用全文索引（FULLTEXT INDEX）。

-- 创建全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_idx_name (name);

-- 用 MATCH AGAINST 查询
SELECT * FROM users WHERE MATCH(name) AGAINST('Alice');

情况 4：OR 连接的条件中有字段没索引

问题：OR 连接的条件中，只要有一个字段没索引，整个索引失效。

-- age 有索引，但 name 没索引，索引失效
EXPLAIN SELECT * FROM users WHERE age = 25 OR name = 'Alice';

为什么失效？ MySQL 认为，既然 name 没索引，那还不如全表扫描（一次扫描搞定），不用先走索引再全表扫描。

优化方案 1：给 OR 连接的所有字段加索引。

-- 给 name 加索引
CREATE INDEX idx_name ON users(name);

-- 再执行查询，索引生效
SELECT * FROM users WHERE age = 25 OR name = 'Alice';

优化方案 2：用 UNION 代替 OR。

-- 优化后：UNION（两个子查询都能走索引）
SELECT * FROM users WHERE age = 25
UNION ALL
SELECT * FROM users WHERE name = 'Alice';

情况 5：联合索引没遵循最左前缀

问题：联合索引有"最左前缀"原则，如果查询条件没用到最左前缀，索引失效。

-- 联合索引 (age, name)
CREATE INDEX idx_age_name ON users(age, name);

-- 没用到最左前缀 age，索引失效
EXPLAIN SELECT * FROM users WHERE name = 'Alice';

为什么失效？ 联合索引是先按 age 排序，再按 name 排序。如果没指定 age，MySQL 没法快速定位。

优化方案：查询条件遵循最左前缀原则。

-- 能用索引的情况：
WHERE age = 25 AND name = 'Alice'  -- 用到 (age, name)
WHERE age = 25                     -- 用到 (age)
WHERE age > 20 AND age < 30        -- 用到 (age)

-- 不能用索引的情况：
WHERE name = 'Alice'                -- 没用到最左前缀 age

如果一定要查 name，考虑单独给 name 建索引。

CREATE INDEX idx_name ON users(name);

情况 6：索引选择性太差

问题：如果索引字段的选择性太差（比如性别字段，只有男/女两个值），MySQL 可能放弃索引，选择全表扫描。

-- gender 有索引，但选择性太差，MySQL 可能放弃索引
EXPLAIN SELECT * FROM users WHERE gender = '男';

为什么失效？ 如果 gender='男' 的记录占全表的 50%，那走索引还得回表 50% 的记录，不如全表扫描（减少随机 I/O）。

优化方案 1：如果业务允许，加其他条件缩小范围。

-- 优化后：加其他条件，缩小范围
SELECT * FROM users WHERE gender = '男' AND age = 25;

优化方案 2：用覆盖索引（只查索引字段，不需要回表）。

-- 优化后：覆盖索引
SELECT gender FROM users WHERE gender = '男';

情况 7：优化器估计走索引更慢

问题：MySQL 优化器会根据统计信息估算走索引的成本，如果估计走索引更慢，会放弃索引。

-- 假设 users 表有 1000 万行，age=25 的有 900 万行
-- MySQL 估计走索引要回表 900 万次，不如全表扫描
EXPLAIN SELECT * FROM users WHERE age = 25;

为什么失效？ 如果符合条件的记录太多，走索引要回表很多次（随机 I/O），全表扫描反而更快（顺序 I/O）。

优化方案 1：加 LIMIT 限制返回行数。

-- 优化后：加 LIMIT，MySQL 可能走索引
SELECT * FROM users WHERE age = 25 LIMIT 10;

优化方案 2：用覆盖索引（只查索引字段，不需要回表）。

-- 优化后：覆盖索引
SELECT age FROM users WHERE age = 25;

优化方案 3：FORCE INDEX 强制走索引（不推荐，除非你确定索引更快）。

-- 强制走 idx_age 索引
SELECT * FROM users FORCE INDEX(idx_age) WHERE age = 25;

实战建议

1. 每次写完 SQL 都用 EXPLAIN 检查

这是最重要的建议。很多线上慢查询，都是因为索引失效，导致全表扫描。

2. 避免对索引字段用函数

如果一定要用函数，考虑生成列 + 索引。

3. 查询条件的类型和索引字段的类型保持一致

避免隐式类型转换，导致索引失效。

4. 模糊查询尽量用后缀通配符

如果一定要用前缀通配符，考虑全文索引。

5. OR 连接的条件要给所有字段加索引

或者改用 UNION 代替 OR。

6. 联合索引要遵循最左前缀原则

如果查询条件不遵循最左前缀，考虑单独给字段建索引。

7. 索引选择性太差时，考虑覆盖索引或者加其他条件缩小范围

如果优化器估计走索引更慢，会放弃索引。

总结

• 索引失效的七种情况：
• 1. 对索引字段用函数
• 2. 隐式类型转换
• 3. 模糊查询前缀通配符
• 4. OR 连接的条件中有字段没索引
• 5. 联合索引没遵循最左前缀
• 6. 索引选择性太差
• 7. 优化器估计走索引更慢
• • 实战建议：每次写完 SQL 都用 EXPLAIN 检查、避免对索引字段用函数、查询条件的类型和索引字段的类型保持一致、模糊查询尽量用后缀通配符、OR 连接的条件要给所有字段加索引、联合索引要遵循最左前缀原则、索引选择性太差时考虑覆盖索引
    如果你能把这七种情况讲清楚，并且能优化索引失效的 SQL，面试官绝对觉得你有实战经验。

---

实战代码都在我本地跑过，你可以放心复制。 如果有问题，欢迎评论区交流！