MySQL 索引失效的 8 个常见场景与排查思路

前言

索引是 MySQL 调优中最常用的手段，但很多同学发现：明明加了索引，查询还是全表扫描。本文结合 EXPLAIN 输出，总结 8 类典型的索引失效场景，并给出排查思路，帮助大家在日常开发中少踩坑。

1. 对索引列使用函数或表达式

当索引列出现在函数或表达式中时，优化器往往无法直接利用 B+ 树的有序性，只能逐行计算后再比较。

SELECT * FROM users WHERE DATE(create_time) = '2026-11-10';
SELECT * FROM orders WHERE amount + 1 > 100;

改写为范围条件即可命中索引：

SELECT * FROM users
WHERE create_time >= '2026-11-10 00:00:00'
  AND create_time <  '2026-11-11 00:00:00';

2. 隐式类型转换

如果手机号字段是 varchar，却用数字去查询，MySQL 会把字符串列转成数字再比较，索引随之失效。

-- phone 是 varchar(20)
SELECT * FROM users WHERE phone = 13800138000;    -- 可能失效
SELECT * FROM users WHERE phone = '13800138000';  -- 命中

3. 前导模糊匹配 LIKE '%xxx'

B+ 树按前缀有序存放，左侧不确定时无法二分定位，因此以百分号开头的模糊查询通常走全表扫描。

SELECT * FROM articles WHERE title LIKE '%MySQL%';  -- 失效
SELECT * FROM articles WHERE title LIKE 'MySQL%';   -- 命中

4. 联合索引不满足最左前缀

联合索引 (a, b, c) 只能从 a 开始依次使用，跳过左侧列就相当于放弃了索引入口。

INDEX idx_abc (a, b, c)
SELECT * FROM t WHERE b = 1;                      -- 失效
SELECT * FROM t WHERE a = 1 AND c = 3;            -- 只用上 a
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;  -- 全部用上

5. 使用 OR 且一侧无索引

OR 两侧只要有一侧没有索引，优化器可能直接选择全表扫描，于是另一侧的索引也白加了。

SELECT * FROM t WHERE indexed_col = 1 OR non_indexed_col = 2;

可以改写为 UNION ALL，或者给 non_indexed_col 也补上索引。

6. 否定条件与 IS NOT NULL

否定条件（!=、NOT IN、NOT LIKE）通常难以利用索引；IS NOT NULL 在多数情况下也会导致索引失效，因为 NULL 的分布信息有限。

SELECT * FROM t WHERE id != 10;
SELECT * FROM t WHERE id NOT IN (1, 2, 3);
SELECT * FROM t WHERE name IS NOT NULL;

7. 数据分布倾斜导致优化器放弃索引

当某个值占了全表 80% 时，优化器会认为回表成本高于全表扫描，主动放弃索引。这不是索引失效，而是基于成本的理性选择。

SELECT * FROM orders WHERE status = 'pending';  -- 90% 都是 pending

8. 字符集或排序规则不一致

当两张表关联、字符集或 collation 不同时，索引列会被隐式转换，导致索引失效，这类问题在跨库迁移时尤为常见。

-- a.name 是 utf8mb4，b.name 是 utf8
SELECT * FROM a JOIN b ON a.name = b.name;

排查思路

1. 先用 EXPLAIN 看 type、key、rows、Extra 四个关键列；
2. type 为 ALL 说明全表扫描，key 为 NULL 说明没有走索引；
3. Extra 出现 Using filesort 或 Using temporary 时也要警惕；
4. 必要时用 FORCE INDEX 验证假设，再决定是改写 SQL 还是调整索引。

结语

索引不是“加上就快”。理解 B+ 树的有序性、最左前缀原则和优化器的成本模型，才能写出真正命中索引的 SQL。建议在提交慢查询前，先养成 EXPLAIN 的习惯。