MySQL 索引失效的常见原因
在 MySQL 中，即使你为某列建立了索引，也有不少情况会导致索引失效，从而退化为全表扫描，影响查询性能。以下是常见导致索引失效的场景，附有示例说明：
✅ 正常使用索引的前提
- 查询条件中字段在索引列上。
- 字段的数据类型与索引字段一致。
- 没有对索引列进行不必要的函数或运算。
- 使用了支持索引的操作符，如
=,<,>,BETWEEN,IN等。 - 索引列
like查询或者复合索引遵循最左匹配原则。
❌ 常见导致索引失效的情况
1. 使用函数或表达式对索引列进行处理
-- 假设 `name` 列有索引
SELECT * FROM user WHERE LEFT(name, 3) = 'Tom';
使用函数
LEFT(name, 3)，MySQL 无法利用索引，需要对所有行执行函数运算。
2. 数据类型不一致（隐式转换）
-- phone 为 varchar 类型
SELECT * FROM user WHERE phone = 13800138000;
数字字面量与 varchar 类型对比时，会触发隐式类型转换，导致索引失效。
建议写成：
SELECT * FROM user WHERE phone = '13800138000';
3. 对索引列做运算
-- age 列有索引
SELECT * FROM user WHERE age + 1 = 18;
索引列做了计算，MySQL 无法使用索引。
改写为：
SELECT * FROM user WHERE age = 17;
4. 使用 != 或 <> 操作符
SELECT * FROM user WHERE age != 30;
!=会导致索引失效，因为它不能有效筛选范围。
5. 使用 OR 且不是所有字段都有索引
SELECT * FROM user WHERE name = 'Tom' OR age = 30;
如果
name和age不是都建了索引，可能导致不能使用索引。
改进方法：对 name 和 age 都建立索引，或改为 UNION 形式。
6. 模糊查询前缀带通配符（如 %abc）
SELECT * FROM user WHERE name LIKE '%Tom';
前面有
%，无法利用索引，只能全表扫描。
推荐使用：
SELECT * FROM user WHERE name LIKE 'Tom%';
7. 多列联合索引未按最左前缀使用
假设建了联合索引 (name, age)：
SELECT * FROM user WHERE age = 20; -- ❌ 无法使用索引
SELECT * FROM user WHERE name = 'Tom' AND age = 20; -- ✅ 使用索引
只使用联合索引中的第二列时会失效。
8. 使用 IS NULL / IS NOT NULL 的情况
SELECT * FROM user WHERE name IS NOT NULL;
对于
IS NOT NULL，可能不会使用索引（取决于 MySQL 版本、数据分布）。
9. 使用 NOT IN / NOT EXISTS
SELECT * FROM user WHERE id NOT IN (1, 2, 3);
NOT IN无法使用索引，尤其当有 NULL 值时。
推荐使用 LEFT JOIN ... IS NULL 替代。
10. 范围条件之后紧跟的索引列失效
-- 联合索引(name, age)
SELECT * FROM user WHERE name > 'Tom' AND age = 20;
范围查询 (
name > 'Tom') 之后的age = 20不再使用索引。
✅ 如何判断是否使用了索引？
使用 EXPLAIN 分析 SQL 执行计划：
EXPLAIN SELECT * FROM user WHERE name = 'Tom';
关注 key、possible_keys、rows、Extra 字段信息。