索引失效是查询条件、表结构、数据分布和执行计划共同作用的结果，需通过EXPLAIN逐层验证索引是否命中、被选中及有效利用；常见原因包括对索引列使用函数操作等。
SQL 索引失效不是“突然发生的bug”，而是查询条件、表结构、数据分布和执行计划共同作用的结果。真正有效的排查，要从 执行计划（EXPLAIN）出发，结合索引定义与实际查询逻辑，逐层验证是否命中、是否被选中、是否被有效利用。
一、常见导致索引失效的写法
这些写法会让优化器放弃使用索引，即使字段上有索引：
-
对索引列做函数操作：如
WHERE YEAR(create_time) = 2023→ 改为WHERE create_time >= '2023-01-01' AND create_time -
隐式类型转换：如
WHERE user_id = '123'（user_id 是 INT），字符串与数字比较会触发全表扫描；应确保类型一致 -
使用不匹配的通配符前缀：如
WHERE name LIKE '%abc'无法使用 B+Tree 索引的最左前缀；可考虑全文索引或倒序存储优化 -
OR 条件中部分字段无索引：如
WHERE a = 1 OR b = 2，若只有 a 有索引，b 没索引，整体可能走全表；拆成 UNION 或补全索引 -
IS NULL / IS NOT NULL 在非空字段上误用：如果字段定义为
NOT NULL，WHERE col IS NULL必然不走索引（结果为空），但优化器仍可能误判；检查约束与查询语义是否匹配
二、索引本身设计不合理
索引存在，不代表能被当前查询用上：
-
复合索引未满足最左前缀原则：索引是
(a, b, c)，但查询只用了WHERE b = ? AND c = ?→ 无法命中；需调整顺序或单独建索引 -
选择性低的字段放在索引前面：如
(status, user_id)，status 只有 0/1 两个值，会导致大量索引项重复，优化器倾向全表扫描；应把高区分度字段前置 - 索引列包含太多 NULL 值：尤其在 MySQL 中，B+Tree 索引对 NULL 的处理较特殊，大量 NULL 可能降低索引效率；可考虑设为默认值或单独过滤
-
过度索引或冗余索引：如已有
(a, b)，又建了(a, b, c)和(a)，不仅浪费空间，还拖慢写入，并干扰优化器选择
三、执行计划分析是核心手段
不看 EXPLAIN，一切判断都是猜测：
-
重点关注 type 字段：从
const、ref、range（健康）→index（全索引扫描）、ALL（全表扫描）说明性能退化 - key 字段是否为 NULL：表示没走任何索引；若非 NULL 但 rows 很大，说明索引选择不佳或数据倾斜
-
Extra 字段提示关键线索：如
Using filesort（排序未走索引）、Using temporary（用了临时表）、Using index condition（ICP 优化生效）等 -
对比实际执行时间与预估 rows：若
rows明显低估（如统计信息过期），运行ANALYZE TABLE table_name更新统计信息
四、其他易忽略的影响因素
有些问题不在 SQL 本身，而在环境与配置：
- 小表未走索引是正常现象：MySQL 优化器认为全表扫描比索引回表更快（尤其当数据页少于 10–20 页时），不必强行加索引
-
查询返回大量字段 + 覆盖索引缺失：若
SELECT *且索引不包含所有需要字段，就会回表；可建立覆盖索引，或明确只查必要字段 -
隔离级别与锁竞争干扰执行计划：高并发下，某些查询因锁等待超时被优化器降级；可通过
SHOW ENGINE INNODB STATUS查看锁信息 -
字符集/排序规则不一致：如关联字段一个是
utf8mb4_0900_as_cs，另一个是utf8mb4_general_ci，可能导致无法使用索引；统一 collation 是前提
索引优化不是堆砌索引，而是理解数据访问模式后做的精准匹配。每次修改前先 EXPLAIN，每次上线后观察慢日志，长期积累才能形成稳定的索引治理习惯。
