当一条SQL查询变慢时，最有效的排查手段就是分析它的SQL查询计划。查询计划是数据库优化器根据统计信息和索引情况生成的执行路径，它决定了查询是走索引还是全表扫描。本文将从查询计划入手，讲解如何通过执行计划判断索引使用情况，并给出索引设计的可操作步骤。
查询计划中的关键信息
在MySQL中，使用EXPLAIN SELECT ...可以查看查询计划。关键字段包括：
- type：访问类型，从好到差依次为
system>const>eq_ref>ref>range>index>ALL。其中ALL表示全表扫描，是性能瓶颈的主要信号。 - key：实际使用的索引。若为
NULL，说明没有使用索引。 - rows：预估扫描的行数，越小越好。
- Extra：常见值如
Using index（覆盖索引）、Using where（过滤）、Using filesort（额外排序）、Using temporary（临时表）等，这些信息直接影响查询性能。
在PostgreSQL中，使用EXPLAIN ANALYZE可以查看实际执行计划，包括每个节点的实际耗时和行数。注意，EXPLAIN只显示计划，ANALYZE会实际执行查询，因此对于写操作要谨慎。
从查询计划中识别索引问题
拿到查询计划后，主要看以下几点：
- type 为 ALL：说明发生了全表扫描，通常意味着没有合适的索引或索引未被使用。
- key 为 NULL：可能原因包括：查询条件列没有索引、索引列参与运算或函数、隐式类型转换导致索引失效等。
- rows 远大于实际行数：统计信息不准确，可能需要
ANALYZE TABLE更新统计信息。 - Extra 出现 Using filesort 或 Using temporary：说明排序或分组没有利用索引，可能需要调整索引顺序或添加覆盖索引。
例如，对于查询 SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC，如果执行计划显示Using filesort，则说明(user_id, created_at)复合索引缺失或顺序不对。
索引设计的基本原则
优化索引设计时，可以遵循以下原则：
- 选择性高的列前置：复合索引中，将区分度高的列放在前面，能更快缩小范围。
- 覆盖索引：让索引包含查询所需的所有列，避免回表。例如查询
SELECT id, name FROM users WHERE status = 1，可建立(status, id, name)索引。 - 避免冗余索引：如已有
(a, b)索引，再建(a)索引就是冗余，因为前者可以支持后者。 - 注意索引列上的操作：对索引列使用函数或计算会导致索引失效，应改写查询。
优化索引设计的步骤
1. 收集慢查询日志，找到最耗时的SQL。
2. 使用EXPLAIN分析这些SQL的执行计划，记录type、key、rows和Extra。
3. 根据计划中的问题，设计或调整索引。例如，若发现WHERE条件中的列没有索引，可添加单列索引；若排序字段导致filesort，可尝试建立包含排序字段的复合索引。
4. 修改后重新执行EXPLAIN，对比rows和Extra的变化，确认优化效果。
5. 在测试环境验证性能，注意索引也会增加写入开销，需权衡读写比例。
常见误区与失败条件
- 误区一：索引越多越好。实际上，每个索引都会占用存储空间并增加DML开销，应只为高频查询创建索引。
- 误区二：忽略联合索引的顺序。顺序错误可能导致索引无法完全利用。
- 误区三：认为索引一定能提升查询性能。当查询返回大部分行时，优化器可能放弃索引而选择全表扫描，此时索引反而无用。
失败条件还包括：统计信息不更新、数据分布倾斜（如大量NULL值）、隐式类型转换等。遇到这些情况，需要结合具体数据库的文档来调整。
数据库日志与监控辅助优化
索引优化不仅依赖查询计划，还需要日志和监控数据辅助。OWASP日志安全速查表建议应用程序应记录安全事件，同时也强调了日志的一致性和标准化，这有助于分析性能问题。OpenTelemetry日志规范指出，日志与追踪、指标的集成能提供更全面的可观测性，帮助定位慢查询的上下文。Python的logging模块提供了灵活的日志配置，能够在应用中记录SQL执行时间，辅助发现慢查询。
总结
分析SQL查询计划是索引优化的核心方法。通过EXPLAIN识别访问类型、索引使用情况和额外操作，可以针对性地调整索引设计。记住，优化是一个迭代过程，需要结合日志和监控持续改进。
参考资料
延伸阅读