索引失效的 12 种原因

一句话

索引失效 = MySQL 优化器决定不用你建的索引去执行。 永远用 EXPLAIN 验证，不要靠记忆。

12 种失效场景

1. WHERE 列上做函数 / 表达式

-- ❌ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE id + 1 = 100;

-- ✅ 改写：把函数挪到右边
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM users WHERE id = 99;

MySQL 8.0+ 支持 函数索引：ALTER TABLE users ADD INDEX idx_year ((YEAR(created_at)))

2. 隐式类型转换

-- 字段是 VARCHAR，传了 INT
-- ❌ 走全表扫
SELECT * FROM users WHERE phone = 13800138000;

-- ✅
SELECT * FROM users WHERE phone = '13800138000';

3. LIKE 以 % 开头

-- ❌
SELECT * FROM users WHERE name LIKE '%张';

-- ✅ 前缀匹配能走索引
SELECT * FROM users WHERE name LIKE '张%';

-- 想中间匹配？用全文索引或 ES

4. OR 中有非索引列

-- ❌ phone 没索引，整条 SQL 失效
SELECT * FROM users WHERE id = 1 OR phone = '13800138000';

-- ✅ UNION ALL 拆开
SELECT * FROM users WHERE id = 1
UNION ALL
SELECT * FROM users WHERE phone = '13800138000' AND id != 1;

5. 联合索引违反最左前缀

-- INDEX (a, b, c)
WHERE a = 1               -- ✅
WHERE a = 1 AND b = 2     -- ✅
WHERE a = 1 AND c = 3     -- ✅ 但 c 用不上
WHERE b = 2 AND c = 3     -- ❌
WHERE c = 3               -- ❌

6. 范围查询后的列失效

-- INDEX (a, b, c)
WHERE a = 1 AND b > 10 AND c = 3
-- ✅ a, b 走索引，c 用不上（b 是范围）

7. 负向查询

-- ❌ NOT IN / != / NOT LIKE 通常走全表
SELECT * FROM users WHERE status != 1;

-- ✅ 改正向
SELECT * FROM users WHERE status IN (2, 3, 4);

8. IS NULL / IS NOT NULL（看版本和数据）

-- 看选择性
-- 大部分都是 NULL → IS NOT NULL 走全表
-- 少部分是 NULL → IS NULL 可以用索引
SELECT * FROM users WHERE deleted_at IS NULL;

9. ORDER BY 方向不一致

-- INDEX (a, b)
ORDER BY a ASC, b ASC    -- ✅
ORDER BY a DESC, b DESC  -- ✅（MySQL 8 之前是反向扫）
ORDER BY a ASC, b DESC   -- ❌ 8 之前会 filesort
-- 8.0+ 支持 INDEX (a ASC, b DESC) 显式声明

10. 数据量太少

-- 1000 行的表，优化器认为全表扫比走索引快，直接放弃索引
-- 这不是 bug，是设计

11. 字符集 / 校对规则不一致

-- JOIN 时左右两表的字段字符集不同
-- 隐式转换 → 索引失效
-- 修复：统一用 utf8mb4

12. 优化器统计信息过期

-- 大量增删改后，索引选择性估算不准
ANALYZE TABLE users;   -- 刷新统计信息

排查工具

-- 1. EXPLAIN 看 type / key / rows
EXPLAIN SELECT ...;

-- 2. 看优化器实际怎么改写的
EXPLAIN FORMAT=TREE SELECT ...;     -- MySQL 8.0+
EXPLAIN ANALYZE SELECT ...;         -- 真实执行

-- 3. 慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 4. 强制走某索引（兜底，少用）
SELECT * FROM users FORCE INDEX(idx_age) WHERE age = 25;

参考

• MySQL 文档 - Index Hints: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
• 《高性能 MySQL》第 6 章「查询性能优化」