查询优化入门：用 EXPLAIN 看懂查询计划

在数据库应用开发中，我们经常会遇到一些执行缓慢的SQL查询。当面对一个慢查询时，如何快速定位性能瓶颈？答案就是分析数据库的查询执行计划。PostgreSQL提供了强大的EXPLAIN和EXPLAIN ANALYZE命令，让我们能够深入了解查询的执行细节。

什么是查询执行计划？

简单来说，查询执行计划是数据库优化器为执行SQL查询而制定的一系列步骤。就像旅行前规划路线一样，数据库需要决定：

使用哪个索引
采用哪种连接方式
表的扫描顺序
是否使用临时排序等

理解执行计划是数据库性能优化的基本功。

EXPLAIN 基础用法

基本语法

EXPLAIN [ ( option [, ...] ) ] sql_statement;

其中option可以是：

ANALYZE：实际执行查询并显示真实运行数据
VERBOSE：显示更详细的信息
COSTS：显示成本估算（默认开启）
BUFFERS：显示缓冲区使用情况（需与ANALYZE一起使用）
FORMAT：指定输出格式（TEXT, JSON, XML等）

一个简单的例子

让我们从一个简单的查询开始：

EXPLAIN SELECT * FROM users WHERE age > 30;

输出可能类似于：

Seq Scan on users  (cost=0.00..155.00 rows=5000 width=64)
  Filter: (age > 30)

这个简单的执行计划告诉我们：

Seq Scan：进行了全表扫描
cost=0.00..155.00：启动成本0.00，总成本155.00
rows=5000：预计返回5000行
width=64：每行平均64字节

深入理解 EXPLAIN 输出

成本估算（Cost）

成本是PostgreSQL中的抽象概念，由几个部分组成：

启动成本：获取第一行数据前的成本
总成本：获取所有数据的总成本
成本单位基于seq_page_cost、random_page_cost、cpu_tuple_cost等参数

常见的节点类型

Seq Scan：顺序扫描（全表扫描）
Index Scan：索引扫描
Index Only Scan：仅索引扫描
Bitmap Heap Scan：位图堆扫描
Nested Loop：嵌套循环连接
Hash Join：哈希连接
Merge Join：合并连接
Sort：排序操作
Aggregate：聚合操作

EXPLAIN ANALYZE：获取真实执行数据

EXPLAIN只显示优化器的估算，而EXPLAIN ANALYZE会实际执行查询并提供真实数据：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

输出：

Seq Scan on users  (cost=0.00..155.00 rows=5000 width=64) (actual time=0.012..12.345 rows=4800 loops=1)
  Filter: (age > 30)
  Rows Removed by Filter: 200
Planning Time: 0.123 ms
Execution Time: 12.567 ms

这里我们看到：

actual time=0.012..12.345：实际执行时间（启动..总时间，单位ms）
rows=4800：实际返回行数
loops=1：执行次数
Rows Removed by Filter：被过滤掉的行数
Planning Time：查询规划时间
Execution Time：查询执行时间

实战案例解析

案例1：索引的重要性

假设我们有一个用户表：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INTEGER,
    created_at TIMESTAMP
);

-- 插入10万条测试数据
INSERT INTO users (name, email, age, created_at)
SELECT 
    'User_' || i,
    'user_' || i || '@example.com',
    (random() * 80)::integer + 18,
    now() - (random() * 365)::integer * '1 day'::interval
FROM generate_series(1, 100000) i;

无索引查询：

EXPLAIN ANALYZE SELECT * FROM users WHERE age = 30;

输出：

Seq Scan on users  (cost=0.00..1943.00 rows=1 width=64) (actual time=12.345..15.678 rows=1234 loops=1)
  Filter: (age = 30)
  Rows Removed by Filter: 98766
Planning Time: 0.123 ms
Execution Time: 15.789 ms

添加索引后：

CREATE INDEX idx_users_age ON users(age);

EXPLAIN ANALYZE SELECT * FROM users WHERE age = 30;

输出：

Index Scan using idx_users_age on users  (cost=0.29..16.31 rows=1 width=64) (actual time=0.045..0.567 rows=1234 loops=1)
  Index Cond: (age = 30)
Planning Time: 0.234 ms
Execution Time: 0.678 ms

性能提升明显：从15.789ms降到0.678ms！

案例2：连接查询分析

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- 插入50万条订单数据
INSERT INTO orders (user_id, amount, status)
SELECT 
    (random() * 100000)::integer + 1,
    (random() * 1000)::decimal,
    CASE WHEN random() < 0.8 THEN 'completed' ELSE 'pending' END
FROM generate_series(1, 500000);

分析连接查询：

EXPLAIN ANALYZE 
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
  AND o.status = 'completed'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5;

这个复杂的查询可能产生包含多个节点的执行计划：

对users表的索引扫描（使用年龄条件）
对orders表的索引扫描（使用状态条件）
Hash Join或Nested Loop连接
HashAggregate分组聚合

案例3：使用BUFFERS分析IO

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

输出会包含缓冲区信息：

Seq Scan on users  (cost=0.00..1943.00 rows=10000 width=64) (actual time=0.012..15.678 rows=9876 loops=1)
  Filter: (age > 60)
  Rows Removed by Filter: 90124
  Buffers: shared hit=834 read=166
Planning:
  Buffers: shared hit=25
Planning Time: 0.234 ms
Execution Time: 16.123 ms

缓冲区信息解读：

shared hit：从缓存命中的块数
shared read：从磁盘读取的块数
高的shared read可能意味着需要更多内存或更好的索引

解读执行计划的实用技巧

1. 关注高成本操作

寻找成本最高的节点
检查全表扫描（Seq Scan）是否必要
注意排序（Sort）和聚合（Aggregate）操作

2. 比较估算与实际值

如果rows估算与实际相差很大，可能需要更新统计信息：ANALYZE table_name;
大的差异可能导致优化器选择错误的执行计划

3. 识别性能瓶颈

高actual time的节点是优化重点
注意loops值，嵌套循环中的高循环次数可能有问题
关注Rows Removed by Filter，可能提示需要更好的索引

4. 使用可视化工具

对于复杂的执行计划，可以使用：

explain.depesz.com
tatiyants.com/pev 这些工具能提供更直观的可视化分析

常见性能问题及解决方案

问题1：全表扫描

症状：执行计划中出现Seq Scan 解决方案：

添加合适的索引
重写查询条件

问题2：错误的连接顺序

症状：连接顺序导致中间结果集过大 解决方案：

使用SET enable_nestloop = off;等参数调整连接策略
确保连接条件上有索引

问题3：内存不足

症状：大量磁盘读写（高shared read） 解决方案：

增加work_mem参数
优化查询减少内存使用

最佳实践

定期分析表：使用ANALYZE更新统计信息
监控参数：关注work_mem、shared_buffers等关键参数
渐进优化：一次只做一个改动，测试效果
生产环境测试：使用EXPLAIN ANALYZE时要小心，它会在生产环境实际执行查询

总结

掌握EXPLAIN和EXPLAIN ANALYZE是数据库性能优化的关键技能。通过本文的讲解和实战案例，你应该已经能够：

理解执行计划的基本结构和关键指标
识别常见的性能问题模式
使用适当的工具和技术进行查询优化
制定有效的优化策略

记住，查询优化是一个持续的过程。随着数据量的增长和业务需求的变化，需要定期重新评估和优化查询性能。现在就开始使用EXPLAIN分析你的慢查询吧！

进一步学习资源：

PostgreSQL官方文档 - EXPLAIN
Use The Index, Luke
《数据库索引设计与优化》

文档信息

本文作者：JiliangLee
本文链接：https://leejiliang.cn/2025/11/24/%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96%E5%85%A5%E9%97%A8%E7%94%A8-EXPLAIN-%E7%9C%8B%E6%87%82%E6%9F%A5%E8%AF%A2%E8%AE%A1%E5%88%92-EXPLAINEXPLAIN-ANALYZE-%E7%9A%84%E5%AE%9E%E6%93%8D%E8%A7%A3/
版权声明：自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）

CODE

查询优化入门：用 EXPLAIN 看懂查询计划 — EXPLAIN/EXPLAIN ANALYZE 的实操解读