SQL 性能调优：执行计划分析指南

By | 2026年7月9日
0 Comment

为什么需要分析执行计划？

在数据库开发中，我们经常遇到这样的场景：一个 SQL 查询在数据量小时运行飞快，但数据量增长后变得异常缓慢。这时候，仅仅看 SQL 语句本身往往找不到问题所在。执行计划（Execution Plan）是数据库优化器根据 SQL 语句生成的执行步骤，它揭示了数据库如何访问数据、使用索引以及连接表的方式。通过分析执行计划，我们可以精准定位性能瓶颈，从而进行针对性优化。

本文将使用 PostgreSQL 数据库为例，但原理同样适用于 MySQL、Oracle 等其他数据库。

获取执行计划

使用 EXPLAIN

PostgreSQL 中，使用 EXPLAIN 命令可以查看执行计划：


EXPLAIN SELECT * FROM users WHERE age > 30;

输出示例：


Seq Scan on users  (cost=0.00..35.00 rows=10 width=100)
  Filter: (age > 30)

使用 EXPLAIN ANALYZE

EXPLAIN ANALYZE 会实际执行 SQL，并返回真实的执行时间和行数，更准确：


EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

输出示例：


Seq Scan on users  (cost=0.00..35.00 rows=10 width=100) (actual time=0.015..0.020 rows=5 loops=1)
  Filter: (age > 30)
  Rows Removed by Filter: 995

💡 注意：EXPLAIN ANALYZE 会实际修改数据（如 INSERT/UPDATE/DELETE），建议在事务中执行并回滚。

解读执行计划的关键指标

1. 节点类型

  • Seq Scan：全表扫描，通常效率低，应避免。
  • Index Scan：索引扫描，快速定位数据。
  • Index Only Scan：仅索引扫描，无需回表，更快。
  • Nested Loop：嵌套循环连接，适合小表驱动大表。
  • Hash Join：哈希连接，适合大表等值连接。
  • Merge Join：归并连接，适合排序后的数据。

2. 成本估算

cost=0.00..35.00 表示启动成本到总成本。单位是任意单位，但相对大小有意义。

  • 启动成本：返回第一行前的成本。
  • 总成本：返回所有行的成本。

3. 实际时间与行数

actual time=0.015..0.020 rows=5 loops=1 表示实际执行时间（毫秒）和返回行数。如果与估算相差很大，说明统计信息可能过时。

4. 缓冲区信息

使用 BUFFERS 选项可以查看缓存命中情况：


EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 30;

输出示例：


Seq Scan on users  (cost=0.00..35.00 rows=10 width=100) (actual time=0.015..0.020 rows=5 loops=1)
  Buffers: shared hit=100 read=0
  • shared hit：从缓存读取的块数。
  • shared read：从磁盘读取的块数。

实战调优案例

案例1：全表扫描优化

慢查询：


SELECT * FROM orders WHERE order_date >= '2023-01-01';

执行计划：


Seq Scan on orders  (cost=0.00..5000.00 rows=1000 width=200) (actual time=0.100..50.000 rows=5000 loops=1)
  Filter: (order_date >= '2023-01-01'::date)

问题：全表扫描，数据量大时慢。

优化：创建索引


CREATE INDEX idx_order_date ON orders(order_date);

再次执行计划：


Index Scan using idx_order_date on orders  (cost=0.29..150.00 rows=1000 width=200) (actual time=0.020..5.000 rows=5000 loops=1)
  Index Cond: (order_date >= '2023-01-01'::date)

成本从 5000 降到 150，时间从 50ms 降到 5ms。

案例2：连接查询优化

慢查询：


SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 30;

执行计划：


Hash Join  (cost=100.00..5000.00 rows=1000 width=300) (actual time=10.000..100.000 rows=5000 loops=1)
  Hash Cond: (o.user_id = u.id)
  -> Seq Scan on orders o  (cost=0.00..4000.00 rows=100000 width=200) (actual time=0.100..80.000 rows=100000 loops=1)
  -> Hash  (cost=50.00..50.00 rows=1000 width=100) (actual time=0.500..0.500 rows=1000 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 40kB
        -> Seq Scan on users u  (cost=0.00..50.00 rows=1000 width=100) (actual time=0.010..0.200 rows=1000 loops=1)
              Filter: (age > 30)

问题：orders 表全表扫描，且 Hash Join 构建哈希表消耗内存。

优化：

  1. 为 orders.user_id 创建索引：

CREATE INDEX idx_orders_user_id ON orders(user_id);
  1. 为 users.age 创建索引（已存在）。

优化后执行计划：


Nested Loop  (cost=0.29..2000.00 rows=1000 width=300) (actual time=0.010..20.000 rows=5000 loops=1)
  -> Index Scan using idx_users_age on users u  (cost=0.29..50.00 rows=1000 width=100) (actual time=0.010..5.000 rows=1000 loops=1)
        Index Cond: (age > 30)
  -> Index Scan using idx_orders_user_id on orders o  (cost=0.00..1.50 rows=10 width=200) (actual time=0.010..0.015 rows=5 loops=1000)
        Index Cond: (user_id = u.id)

成本从 5000 降到 2000，时间从 100ms 降到 20ms。

案例3：排序优化

慢查询：


SELECT * FROM products ORDER BY price DESC LIMIT 10;

执行计划：


Limit  (cost=5000.00..5000.10 rows=10 width=100) (actual time=100.000..100.010 rows=10 loops=1)
  -> Sort  (cost=5000.00..6000.00 rows=100000 width=100) (actual time=100.000..100.005 rows=10 loops=1)
        Sort Key: price DESC
        Sort Method: external merge  Disk: 2048kB
        -> Seq Scan on products  (cost=0.00..4000.00 rows=100000 width=100) (actual time=0.100..80.000 rows=100000 loops=1)

问题：全表扫描后排序，使用了磁盘排序（external merge），非常慢。

优化：创建索引


CREATE INDEX idx_products_price ON products(price DESC);

优化后：


Limit  (cost=0.29..1.00 rows=10 width=100) (actual time=0.010..0.020 rows=10 loops=1)
  -> Index Scan Backward using idx_products_price on products  (cost=0.29..7000.00 rows=100000 width=100) (actual time=0.010..0.015 rows=10 loops=1)

成本从 5000 降到 1，时间从 100ms 降到 0.02ms。

常见陷阱与最佳实践

1. 统计信息过时

如果执行计划中估算行数与实际行数差异很大，可能是统计信息未更新：


ANALYZE table_name;

2. 索引未被使用

可能原因：

  • 查询条件使用了函数或类型转换（如 WHERE DATE(column) = '2023-01-01' 应改为 WHERE column >= '2023-01-01' AND column < '2023-01-02'）
  • 使用了 LIKE '%keyword' 无法使用索引
  • 数据分布导致优化器认为全表扫描更优（如大部分数据满足条件）

3. 嵌套循环 vs 哈希连接

  • 当驱动表小且内表有索引时，嵌套循环快。
  • 当两表都大且无索引时，哈希连接更优。

可以通过 SET enable_nestloop = off; 临时禁用某种连接方式测试。

4. 使用 pgstatstatements 监控

安装扩展后，可以查看高频慢查询：


CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

总结

通过执行计划分析，我们可以：

  1. 识别全表扫描、排序等低效操作。
  2. 验证索引是否被有效使用。
  3. 发现统计信息问题。
  4. 对比不同查询写法或索引方案的效果。

建议在日常开发中，对每个可能产生性能问题的 SQL 都执行 EXPLAIN ANALYZE，确保其执行计划合理。

延伸阅读：

  • PostgreSQL 官方文档：EXPLAIN
  • 索引类型：B-tree、Hash、GiST、GIN 等适用场景
  • 查询重写技巧：子查询 vs JOIN，使用 WITH 等