索引失效高阶案例：从隐蔽场景到精准优化

​关键词​：索引失效；隐式转换；最左前缀；范围查询；or条件；函数索引

大家好，我是小耶。上次讲了5种索引失效场景，评论区说“够用了，但还有更坑的吗？” 有的。今天分享6个我在工作中遇到的更难发现的失效案例，有些甚至出现在已经建了索引的字段上。

1 问题背景：为什么索引建了却依然失效？

索引失效的本质是：MySQL认为走索引的成本高于全表扫描，或者索引列被迫进行了变换（函数、类型转换等）。在高阶场景中，字符集不一致、排序规则差异、隐式的NULL处理等都会让优化器放弃索引。

2 六个高阶案例

2.1 字符集不一致导致关联索引失效

两张表关联时，若关联字段字符集不同（例如utf8 vs utf8mb4），MySQL会对字段进行隐式转换，导致索引失效。

​示例​：

-- t1 utf8，t2 utf8mb4
SELECT * FROM t1 JOIN t2 ON t1.name = t2.name;

​验证​：执行EXPLAIN，观察key列为NULL。
​优化​：统一字符集为utf8mb4，因为它是utf8的超集。

2.2 排序规则（collation）不同

即使字符集相同，若排序规则不同（如utf8_general_ci vs utf8_unicode_ci），同样会引发隐式转换。

​优化​：使用ALTER TABLE t MODIFY col VARCHAR(50) COLLATE utf8mb4_unicode_ci;统一。

2.3 IS NULL 和 IS NOT NULL 的索引使用

对于大部分索引，col IS NULL可以走索引，但col IS NOT NULL通常不会（除非索引统计信息中NULL值占比极低）。若col允许NULL且查询大量出现IS NOT NULL，可考虑将该列设为NOT NULL或使用覆盖索引。

2.4 不等于（<>, !=）和 NOT IN 导致索引失效

范围查询中，>、<、BETWEEN 可以用索引，但<>、NOT IN一般不会。对于需要排除少量值的场景，可改写为IN包含需要的值（如果可选值很少），或使用UNION ALL分别处理正反条件。

2.5 多列条件顺序与索引匹配

即使索引是(a, b, c)，若查询条件为WHERE a = 1 AND c = 3，只能用到a，不能用到c（因为跳过b）。这不是索引失效，而是使用不完整。解决办法是将索引改为(a, c)或者把b也加入查询条件（即使不用，也要占位？不，只能重建索引或改写查询）。

更隐蔽的是：查询条件中a是范围查询时，其后的b即便在索引中也无法使用。所以范围列必须放在索引末尾。

2.6 MySQL 8.0 函数索引的误用

MySQL 8.0支持函数索引，如CREATE INDEX idx ON t ((LOWER(name)))。但如果在查询中写的函数与索引定义不完全一致（例如UPPER(name)），索引不会生效。另外，函数索引会增加存储成本，且优化器对函数索引的代价估算不一定准确。

​建议​：优先考虑改写成普通索引可支持的形式（例如使用生成列），避免滥用函数索引。

3 实战案例：一个字符集导致的线上事故

某电商系统订单表和用户表关联查询突然变慢，EXPLAIN显示关联字段索引失效。排查发现用户表是utf8，订单表是utf8mb4（因为订单表存储了表情符号）。统一订单表字符集后，索引恢复，查询从3秒降到0.05秒。

4 总结与建议

索引失效排查可遵循以下步骤：

1. 先看EXPLAIN的key列是否为NULL；
2. 若为NULL，检查possible_keys是否有索引；
3. 若有索引但不使用，检查是否触发了隐式转换（类型、字符集、排序规则）；
4. 检查WHERE条件中是否有<>、NOT IN、IS NOT NULL；
5. 检查多列索引的顺序是否匹配查询条件；
6. 最后，考虑优化器统计信息是否过旧（ANALYZE TABLE）。

索引失效的坑往往藏在不经意的细节里。统一开发规范、定期审计慢查询、使用EXPLAIN验证每个新上线SQL，是成本最低的防范手段。

小耶在手，SQL 不愁。

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~