MySQL 索引失效的常见场景有哪些，怎么避免？

学完这篇，你能对照自己的慢 SQL 逐条排查索引为什么没被用上，并按文中的改写方式让它重新走索引。

第一步：先确认索引到底有没有失效

在做任何改写前，先拿到执行计划，确认 key 为空、type 是 ALL 还是 index，否则容易白改。打开 MySQL 8.0 命令行，在目标库执行：

EXPLAIN SELECT * FROM article WHERE user_id = 100 AND status = 1;
SHOW INDEX FROM article;

重点看三列：type（出现 ALL 基本是全表扫描）、key（实际用到的索引名，为 NULL 表示没用索引）、rows（预估扫描行数）。想看得更细，用 EXPLAIN FORMAT=JSON 或 8.0.18 以上版本的 EXPLAIN ANALYZE SELECT ...，后者会给出真实耗时。

注意：rows 是估算值，不是精确行数。判断前先跑一次 ANALYZE TABLE article; 更新统计信息，否则估算偏差会让你误判。

第二步：检查复合索引的最左前缀是否被跳过

复合索引 (a, b, c) 只能从最左列开始连续匹配，中间断掉，后面的列就白建了。比如索引是 (user_id, status, create_time)：

-- 用得上
SELECT * FROM article WHERE user_id = 100 AND status = 1;
-- 用不上（跳过了 user_id）
SELECT * FROM article WHERE status = 1 AND create_time > '2024-01-01';

处理办法是把高频、区分度高的列放左边，并按查询条件顺序补齐前缀列。

注意：WHERE user_id = 100 AND status > 1 AND create_time > '2024-01-01' 里，status 走了范围查询后，create_time 只能用于过滤、无法再走索引定位。

第三步：排查字段上的函数、运算和隐式类型转换

只要在索引列上套了函数或表达式，索引就失效。典型写法：

-- 失效
SELECT * FROM article WHERE DATE(create_time) = '2024-05-01';
SELECT * FROM article WHERE user_id + 1 = 101;
-- 改写为范围查询
SELECT * FROM article WHERE create_time >= '2024-05-01 00:00:00'
  AND create_time < '2024-05-02 00:00:00';

隐式转换更隐蔽：phone 是 varchar 却写成 WHERE phone = 13800001111，MySQL 会把列转成数字比较，索引直接废掉。改成 WHERE phone = '13800001111'。

注意：字符集不一致也会触发隐式转换。用 SHOW CREATE TABLE 核对关联两表的列字符集是否都是 utf8mb4。

第四步：处理 LIKE、OR、不等于这几类写法

LIKE '%关键词' 前导通配符必然全表扫，能改就改成 LIKE '关键词%'；确实要模糊搜索，用全文索引或外部搜索引擎。OR 连接的列若有一侧没索引，整体会退化成全表扫，可拆成两条 SQL 用 UNION ALL 合并。!=、NOT IN、IS NOT NULL 在数据分布倾斜时也常放弃索引。

注意：IN 列表元素过多（几百上千）同样会让优化器改走全表扫，尽量控制列表长度或改用临时表关联。

第五步：确认不是优化器主动放弃索引

当索引区分度太低（比如性别、状态只有两三个值），或者查询要回表取的列太多、命中比例超过约 20%~30%，优化器会判定全表扫更快，这是合理选择而非故障。这时优先考虑建覆盖索引，让查询不回表：

ALTER TABLE article ADD INDEX idx_cover (user_id, status, create_time, title);

第六步：把规则固化成日常习惯

建表时给每个高频查询配一条复合索引；写完 SQL 一律先 EXPLAIN 再上线；避免 SELECT *，只取需要的列以利于覆盖索引；定期 ANALYZE TABLE 保持统计信息新鲜。FORCE INDEX 只作为临时验证手段，不要长期写进业务代码。

小结

• 判断索引失效，先看 EXPLAIN 的 type、key、rows，并先更新统计信息。
• 复合索引守住最左前缀，范围查询之后的列不再参与定位。
• 索引列上不套函数、不做运算，字符串列传字符串值，避免隐式转换。
• %前缀、OR 混合无索引列、!=、超长 IN 都是高危写法。
• 区分度低或回表过多时，优化器放弃索引是正常行为，用覆盖索引解决。
• 每条 SQL 上线前 EXPLAIN 一遍，比事后救火省事得多。