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

​关键词​索引失效隐式转换最左前缀范围查询or条件函数索引大家好我是小耶。上次讲了5种索引失效场景评论区说“够用了但还有更坑的吗” 有的。今天分享6个我在工作中遇到的更难发现的失效案例有些甚至出现在已经建了索引的字段上。1 问题背景为什么索引建了却依然失效索引失效的本质是MySQL认为走索引的成本高于全表扫描或者索引列被迫进行了变换函数、类型转换等。在高阶场景中字符集不一致、排序规则差异、隐式的NULL处理等都会让优化器放弃索引。2 六个高阶案例2.1 字符集不一致导致关联索引失效两张表关联时若关联字段字符集不同例如utf8vsutf8mb4MySQL会对字段进行隐式转换导致索引失效。​示例​-- t1 utf8t2 utf8mb4 SELECT * FROM t1 JOIN t2 ON t1.name t2.name;​验证​执行EXPLAIN观察key列为NULL。​优化​统一字符集为utf8mb4因为它是utf8的超集。2.2 排序规则collation不同即使字符集相同若排序规则不同如utf8_general_civsutf8_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 总结与建议索引失效排查可遵循以下步骤先看EXPLAIN的key列是否为NULL若为NULL检查possible_keys是否有索引若有索引但不使用检查是否触发了隐式转换类型、字符集、排序规则检查WHERE条件中是否有、NOT IN、IS NOT NULL检查多列索引的顺序是否匹配查询条件最后考虑优化器统计信息是否过旧ANALYZE TABLE。索引失效的坑往往藏在不经意的细节里。统一开发规范、定期审计慢查询、使用EXPLAIN验证每个新上线SQL是成本最低的防范手段。小耶在手SQL 不愁。还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~参考文献[1] MySQL官方文档Index Optimization[2] 《高性能MySQL》第4版第5章“索引优化”[3] 阿里云数据库索引失效案例解析