MySQL 索引失效导致全表扫描怎么定位和修复？

Question

仓乃文 · Accepted Answer

MySQL 索引失效是线上慢查询的首要原因，80% 以上的慢查询都源于此。索引失效后查询退化为全表扫描，百万级数据表可能从毫秒级变为秒级。通过 EXPLAIN 可以精准定位索引失效原因。

定位方法：
```sql
-- 在 SQL 前加 EXPLAIN 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = "Tom";

-- 关键字段解读：
-- type=ALL → 全表扫描，索引失效
-- type=index → 全索引扫描，也不理想
-- type=range/ref/eq_ref/const → 正常
-- possible_keys 有值但 key=NULL → 优化器放弃了索引
-- rows 值接近表总行数 → 索引失效
-- Extra 出现 Using filesort/Using temporary → 需要优化
```

12 大索引失效场景与修复：
```sql
-- 1. 对索引列使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2025; -- 失效
SELECT * FROM users WHERE create_time >= "2025-01-01"; -- 命中

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000; -- phone 是 varchar，失效
SELECT * FROM users WHERE phone = "13800138000"; -- 命中

-- 3. LIKE 左模糊
SELECT * FROM users WHERE name LIKE "%Tom"; -- 失效
SELECT * FROM users WHERE name LIKE "Tom%"; -- 命中
-- 全文搜索用 FULLTEXT 索引

-- 4. 联合索引不满足最左前缀
-- 索引 idx(a,b,c)
SELECT * FROM t WHERE b = 1; -- 失效，缺少最左列 a
SELECT * FROM t WHERE a = 1 AND c = 3; -- 只用到 a
SELECT * FROM t WHERE a = 1 AND b = 2; -- 用到 a,b

-- 5. OR 条件中有一列无索引
SELECT * FROM t WHERE a = 1 OR d = 2; -- d 无索引，整体失效

-- 6. NOT IN / NOT EXISTS / != / <>
SELECT * FROM t WHERE status != 1; -- 大部分场景失效
-- 改为 IN：WHERE status IN (0, 2, 3)

-- 7. 索引列参与计算
SELECT * FROM t WHERE id + 1 = 10; -- 失效
SELECT * FROM t WHERE id = 9; -- 命中

-- 8. IS NOT NULL（部分优化器可走索引）
-- 9. ORDER BY 列方向不一致（ASC/DESC 混用）
-- 10. 优化器认为全表扫描成本更低（小表或数据分布不均）
-- 11. 使用了子查询
-- 12. GROUP BY 无索引支持
```

补充说明：
- EXPLAIN FORMAT=JSON 提供更详细的成本分析
- EXPLAIN ANALYZE（MySQL 8.0）会实际执行并返回真实耗时
- 索引不是越多越好，写操作需要维护索引，联合索引优于多个单列索引
- 规则：上线前必须 EXPLAIN 验证所有查询的执行计划