SQL性能调优：从慢查询到索引决策

简介

慢 SQL 查询在生产环境中是家常便饭。一个今天只需 93 毫秒的查询，明天可能突然退化到 15 秒——不是因为 SQL 变了，而是数据分布发生了偏移，或者优化器选择了错误的计划。根本原因几乎总是相同的：数据库读取的行数远远超过实际需要。

本文带你走完一个完整的性能调优工作流：识别慢查询、解读执行计划、诊断缺失的索引、设计正确的组合索引、并验证改进效果。你将学到适用于任何关系型数据库的原则，并附有可直接借鉴的具体示例。

查询为何变慢

当数据库必须扫描表的大部分数据才能返回一个很小的结果集时，查询就会变慢。典型症状：应用程序只要 20 行，数据库却扫描了 120 万行来找到它们。这种不匹配发生在以下情况：

• WHERE 和 ORDER BY 子句没有合适的索引。
• 优化器认为全表扫描比使用索引更便宜（但估算错误）。
• 数据倾斜导致优化器的行数估算严重失准。

第一种情况的修复方法是添加索引。第二种情况可能需要更新统计信息或使用查询提示。第三种情况可能需要重写查询或改用不同的连接策略。

执行计划：你的第一诊断工具

在添加任何索引之前，你必须理解数据库实际在做什么。执行计划显示了每个步骤的访问方法、连接顺序和行数估算。

EXPLAIN 关键列（MySQL）

示例计划（PostgreSQL）

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM benefit_cert_task 
WHERE region_code = '330101' 
  AND biz_status = 'pending' 
  AND risk_level = 'high' 
  AND insurance_type = 'pension' 
  AND cert_deadline BETWEEN '2026-06-01' AND '2026-06-08' 
ORDER BY cert_deadline ASC, updated_at DESC 
LIMIT 20;

优化前输出：

Limit  (cost=0.00..12345.67 rows=20 width=200)
  ->  Gather Merge  (cost=0.00..12345.67 rows=20 width=200)
        Workers Planned: 2
        ->  Sort  (cost=... rows=... width=...)
              Sort Key: cert_deadline, updated_at DESC
              ->  Parallel Seq Scan on benefit_cert_task  (cost=0.00..12000.00 rows=1200000 width=200)
                    Filter: ((region_code = '330101') AND ...)
                    Rows Removed by Filter: 399333

注意 Parallel Seq Scan 扫描了 120 万行的表，以及 Rows Removed by Filter——数据库扫描了近 40 万行只是为了丢弃它们。执行时间为 93 毫秒。

索引设计原则

一个好的索引可以将全表扫描变成几次索引查找。遵循以下规则：

1. 最左前缀：对于组合索引 (a, b, c)，查询必须首先引用 a 才能使用索引。
2. 等值在前，范围在后：将 = 条件的列放在范围条件（>、<、BETWEEN、LIKE 'abc%'）的列之前。
3. 覆盖排序：将 ORDER BY 的列放在索引末尾，以避免额外的排序（Using filesort）。
4. 覆盖查询：使用 INCLUDE（PostgreSQL）或将所有选择的列包含在覆盖索引中，以避免回表。

实战示例：从 93ms 到 8ms

场景

一个待遇认证系统的任务表有 120 万行。应用程序需要某个地区和险种的前 20 条高风险待处理任务，按截止日期排序。

优化前

现有索引：仅在 task_id 上有主键索引。

执行时间：93 ms（并行全表扫描）。

诊断

执行计划显示全表扫描。WHERE 子句有四个等值条件（region_code、biz_status、risk_level、insurance_type）和一个范围条件（cert_deadline）。ORDER BY 使用了 cert_deadline ASC, updated_at DESC。

推荐索引

CREATE INDEX idx_filter_sort ON benefit_cert_task 
  (region_code, biz_status, risk_level, insurance_type, cert_deadline, updated_at DESC)
INCLUDE (other_returned_columns);

要点：

• 等值列放在前面。
• 范围列（cert_deadline）紧随其后。
• 排序列（updated_at）以 DESC 包含进来以匹配查询。
• INCLUDE 列避免了单独的回表。

优化后

执行计划：

Limit  (cost=0.00..10.00 rows=20 width=200)
  ->  Index Scan using idx_filter_sort on benefit_cert_task  (cost=0.00..10.00 rows=20 width=200)
        Index Cond: ((region_code = '330101') AND ...)

执行时间：8 ms（索引扫描）。

性能提升 11.4 倍，缓冲区读取从 27,112 降到 2,031，确认访问路径确实改变了，而不仅仅是缓存命中。

常见陷阱

• 添加过多单列索引：组合索引几乎总是优于多个单列索引。每个表每查询最多只能使用一个索引（索引合并很少见）。
• 忽略数据倾斜：如果某个值（例如一个高频用户）的行数远多于其他值，优化器即使有索引也可能选择全表扫描。考虑分区或重写查询。
• 忘记更新统计信息：添加索引后，运行 ANALYZE（PostgreSQL）或 OPTIMIZE TABLE（MySQL）以给优化器提供准确信息。
• 在索引列上使用函数：WHERE YEAR(create_time) = 2026 永远不会使用 create_time 上的索引。应改写为 WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'。
• 未用生产数据验证：使用真实数据量和分布测试索引。1000 行的测试表永远不会暴露糟糕的计划。

常见问题

如何找到生产中的慢查询？

启用慢查询日志（MySQL 的 slow_query_log，PostgreSQL 的 log_min_duration_statement）。设置一个阈值（例如 1 秒）并定期审查日志。pt-query-digest（Percona Toolkit）等工具可以汇总重复的慢查询。

查询慢时是否总是应该添加索引？

不一定。首先检查查询是否可以重写（例如避免函数、用 EXISTS 代替 IN）。其次，通过检查执行计划确认索引会被使用。第三，考虑写入开销——每个新索引都会拖慢 INSERT/UPDATE/DELETE。对于读多写少的表，索引成本低；对于写多读少的表，要谨慎选择。

什么是覆盖索引？

覆盖索引包含查询所需的所有列，因此数据库永远不需要查找实际的表行。在 MySQL 中，你会在 Extra 中看到 Using index。在 PostgreSQL 中，使用 INCLUDE 添加非键列而不膨胀 B-tree。

如何处理 Using filesort？

Using filesort 意味着 MySQL 在获取行后在内存或磁盘上排序。要消除它，将排序列以与 ORDER BY 相同的顺序添加到索引中。如果排序方向不同（ASC vs DESC），某些数据库（PostgreSQL、MySQL 8.0+）支持混合方向索引。

如果添加索引后优化器仍然选择糟糕的计划怎么办？

更新表统计信息（ANALYZE）。如果仍然不行，可以使用提示强制索引，例如 MySQL 的 FORCE INDEX 或 PostgreSQL 的 SET enable_seqscan = off（用于测试）。真正的修复通常是重写查询或调整索引以更好地匹配查询条件。

试试我们的 SQL 格式化工具 来保持查询的可读性和可维护性。