当查询性能突然下降，最可能的原因之一就是索引失效。索引失效意味着数据库优化器选择了全表扫描而非索引扫描，导致查询耗时剧增。要解决这个问题，不能盲目地加索引或重写SQL，而应遵循一条清晰的判断路径：先确认索引是否真的失效，再定位失效的类型，最后对症下药。本文将沿着这条路径，深入分析索引失效的常见原因，并给出系统性的排查方法。
第一步：确认索引是否真的失效
在讨论失效原因之前，首先要确认索引是否确实没有被使用。最直接的工具是EXPLAIN命令（MySQL、PostgreSQL等均支持）。执行EXPLAIN SELECT ...，观察type字段和key字段：如果type为ALL（全表扫描）且key为NULL，则索引未生效。但需注意，type为index（全索引扫描）也可能不是最优，需结合rows和filtered评估。
另外，索引失效不等于索引不存在。有时优化器会基于统计信息判断全表扫描比索引扫描更快（例如查询返回的行数超过表的20%），此时即使索引存在，优化器也会放弃。这并非失效，而是优化器的合理选择。因此，在排查时，应先排除这类情况，再关注真正的失效原因。
常见索引失效原因分类
索引失效的原因可以归纳为三类：SQL写法问题、数据类型问题和优化器误判。
1. 隐式类型转换
当查询条件中的字段类型与索引列类型不一致时，数据库会进行隐式转换，导致索引列上发生函数操作，从而失效。例如，WHERE phone = 13800138000，如果phone是VARCHAR类型，MySQL会将字符串转为数字进行比较，相当于CAST(phone AS SIGNED)，索引失效。排查方法：检查表结构，确保查询条件与列类型一致，或显式使用CAST。
2. 对索引列使用函数或表达式
在WHERE子句中对索引列使用函数（如DATE()、UPPER()、LENGTH()）或算术运算（如price + 10 > 100），会使索引失效，因为数据库无法直接使用列值定位。正确做法是重写为对常量应用函数，例如WHERE create_time >= '2023-01-01'而非WHERE DATE(create_time) = '2023-01-01'。
3. 前导模糊查询
使用LIKE '%keyword'时，由于通配符在开头，索引无法匹配。但LIKE 'keyword%'仍可使用索引。若必须使用前导模糊，可考虑全文索引或搜索引擎。对于复合索引，如果查询条件未包含最左前缀列，索引也会失效。例如索引(a, b)，仅查询b时无法使用该索引。
4. OR条件连接
当OR连接的多个条件中，只要有一个列没有索引，整个查询都可能全表扫描。例如WHERE id = 1 OR name = 'x'，若name无索引，则索引失效。解决方法是确保所有条件列都有索引，或改用UNION拆分查询。
5. 数据分布与统计信息
如果索引列的数据分布极不均匀（如大量重复值），优化器可能认为全表扫描更高效。此时即使索引存在，也可能被放弃。此外，统计信息过期也会导致优化器误判。定期执行ANALYZE TABLE（MySQL）或VACUUM ANALYZE（PostgreSQL）可更新统计信息。
系统性排查方法
排查索引失效问题，应遵循以下步骤：
- 获取慢查询日志：开启慢查询日志，捕获超过阈值的查询，作为排查起点。
- 使用EXPLAIN分析执行计划：对可疑查询执行
EXPLAIN，检查type、key、rows字段。 - 检查表结构和索引定义：使用
SHOW INDEX FROM table（MySQL）查看索引列和顺序，确认查询条件是否匹配。 - 对照上述失效原因逐项验证：检查类型、函数、模糊查询、OR条件等。
- 更新统计信息并重试：若怀疑统计信息问题，更新后重新执行
EXPLAIN。 - 使用优化器提示：在确认索引有效但优化器未选用时，可临时使用
FORCE INDEX（MySQL）测试，但需谨慎。
案例：一个典型的索引失效场景
假设有订单表orders，索引为idx_user_created (user_id, created_at)。一条查询：SELECT * FROM orders WHERE user_id = 123 AND DATE(created_at) = '2023-01-01'。由于对created_at使用了DATE()函数，复合索引中该列失效，但仍可用user_id部分。若将条件改为created_at >= '2023-01-01' AND created_at < '2023-01-02'，则索引完全生效。这个案例说明，通过重写查询，可以避免函数操作，从而利用索引。
常见误区与注意事项
- 误区一：索引越多越好。索引会占用存储并降低写入性能，应只为高频查询建立索引。
- 误区二：索引失效只与SQL有关。实际上，数据分布和统计信息也会影响优化器决策。
- 误区三：EXPLAIN显示使用索引就万事大吉。还需要关注
rows和filtered，评估实际扫描行数。 - 注意事项：在排查时，应结合业务场景，避免过度优化。索引失效问题往往涉及多个因素，需综合判断。
总结与行动指南
索引失效的排查并非无迹可寻。通过EXPLAIN确认失效，再对照失效原因分类，通常能快速定位问题。记住关键点：保持查询条件与索引列类型一致，避免对索引列使用函数，使用前导模糊时考虑替代方案，确保OR条件中所有列有索引，并定期更新统计信息。这些措施能大幅减少索引失效的发生。
若需进一步了解索引原理与优化，可参考MySQL InnoDB存储引擎索引原理与B+树慢查询优化实战，以及索引与查询优化专题。
参考资料
延伸阅读