MySQL索引失效场景大全（附案例），新手必避的10个坑

前言

很多项目明明建了索引，查询依然很慢，90% 原因是：索引失效。

本篇整理10 个高频索引失效场景，每个场景带错误案例+正确写法，新手直接避坑。

测试表：user，索引：INDEX idx_age (age)

一、10大索引失效场景（带案例）

1. 索引字段使用函数 → 失效

❌ 失效：

SELECT * FROM user WHERE ABS(age) = 20;

✅ 原则：索引字段不能参与函数运算

2. 索引字段隐式类型转换 → 失效

字段 varchar，却用数字查询：

❌ 失效：

SELECT * FROM user WHERE phone = 13800138000;

✅ 正确：字符串匹配字符串

SELECT * FROM user WHERE phone = 13800138000;

3. 使用 != <> 不等于 → 大概率失效

❌ 容易失效：

SELECT * FROM user WHERE age != 20;

4. 使用 OR 连接非索引字段 → 整体失效

age 有索引，name 无索引：

❌ 失效：

SELECT * FROM user WHERE age=20 OR name='张三';

5. LIKE 以 % 开头 → 索引失效

❌ 失效：

SELECT * FROM user WHERE name LIKE '%三';

✅ 有效：

SELECT * FROM user WHERE name LIKE '张%';

6. NOT IN / NOT EXISTS → 索引失效

❌ 失效：

SELECT * FROM user WHERE age NOT IN (18,20);

7. 复合索引不满足最左前缀 → 失效

索引：(age,status)

❌ 失效：只查 status

SELECT * FROM user WHERE status=1;

8. ORDER BY 字段无索引、或顺序不匹配 → 失效

导致文件排序、性能极差。

9. 数据量过少、MySQL 认为全表扫描更快 → 不走索引

表数据极少时，索引会失效，属于 MySQL 优化机制。

10. 索引列允许 NULL，查询 is null 部分场景失效

NULL 值会导致索引效率大幅下降甚至失效。

二、索引失效万能口诀

函数、类型转换、百分号开头、or 乱连、not 否定、不满足最左前缀，全部失效。