SQL 中索引失效的 20 种常见场景及排查方法

索引失效主因是查询条件与索引结构不匹配。WHERE中对索引列用函数（如YEAR(create_time)）导致无法使用索引而全表扫描；应改写为范围查询：create_time >= ‘2023-01-01’ AND create_time

索引不是写了就生效的——绝大多数索引失效问题，根源不在 sql 写得“错”，而在查询条件与索引结构的匹配被悄悄破坏了。

WHERE 条件中对索引列使用函数或表达式

mysql 无法直接用索引定位经过计算的值，会退化为全表扫描。

• select * FROM users WHERE YEAR(create_time) = 2023 → create_time 索引失效；应改写为 create_time >= '2023-01-01' AND create_time
• WHERE UPPER(name) = 'ALICE' → name 索引失效；可建函数索引（MySQL 8.0+）：CREATE INDEX idx_name_upper ON users ((UPPER(name)))，或应用层统一大小写
• 隐式类型转换也属此类：如 WHERE user_id = '123'（user_id 是 int），MySQL 会把索引列转为字符串比对，导致索引失效

LIKE 查询以通配符开头

前导 % 让 B+ 树无法从左到右匹配前缀，索引只能用于跳过扫描（range）或完全失效。

• WHERE name LIKE '%abc' → 索引完全失效
• WHERE name LIKE 'abc%' → 可走索引（最左前缀匹配）
• WHERE name LIKE '%abc%' → 失效；若必须模糊搜，考虑 FULLTEXT 索引或 elasticsearch
• 注意：即使有复合索引 (a, b, c)，WHERE a = 1 AND b LIKE '%x' 中 c 也无法被索引覆盖

OR 条件中部分字段无索引

只要 OR 中任一条件列没索引，整个 WHERE 子句可能放弃使用索引（尤其 MySQL 5.6 之前）。

• WHERE status = 1 OR phone = '138...'，若 phone 无索引 → 全表扫描
• 解决方式：确保 OR 所有分支字段都有独立索引，或改用 union 拆分（每个子查询可独立走索引）
• MySQL 5.7+ 优化器有所改进，但仍建议用 EXPLaiN 验证：看 type 是否为 index_merge，且 key 显示多个索引名

隐式字符集/排序规则不一致

当关联字段或比较字段的 collation 不同（如 utf8mb4_0900_as_cs vs utf8mb4_general_ci），索引无法复用。

• 常见于 JOIN：左表 user.name 是 utf8mb4_unicode_ci，右表 log.user_name 是 utf8mb4_bin → 即使都建了索引，JOIN 时也可能不走索引
• 排查命令：SHOW FULL COLUMNS FROM table_name 查 Collation 列
• 修复：统一字符集和排序规则，或显式加 COLLATE 强制对齐（但会抑制索引）

真正难排查的，往往是多个小因素叠加：比如 OR + 函数 + 隐式转换 同时出现，EXPLAIN 显示 type=ALL 却看不出哪一环断了。建议从执行计划入手，逐条核对 key、possible_keys、rows 和 Extra（尤其是 using filesort 或 Using temporary）——这些才是索引是否实际生效的铁证。