第二章 SQL 语法与高级查询
第三节 查询优化技巧与执行计划解读
目标：掌握 PostgreSQL 中的查询执行计划分析方法，理解常见的执行计划类型，并能够通过 EXPLAIN 命令和实际性能指标发现并优化慢查询，提升数据库整体性能。
SQL 查询是应用访问数据库的核心方式，但即使是最简单的语句，在面对海量数据时也可能变得异常缓慢。因此，掌握如何分析查询执行计划、识别性能瓶颈，并进行有效优化，是每个 PostgreSQL 开发者和 DBA 必须具备的能力。
本节将涵盖以下内容：
- 如何使用
EXPLAIN 和 EXPLAIN ANALYZE 查看执行计划 - 理解执行计划中的关键字段（如
type, rows, cost, actual time） - 常见执行计划类型（Seq Scan, Index Scan, Bitmap Heap Scan 等）
- 实用查询优化技巧（索引选择、JOIN 顺序、子查询改写等）
🔍 一、执行计划基础：使用 EXPLAIN 分析查询
1. 基本命令格式
1
| EXPLAIN SELECT * FROM customers WHERE country = 'USA';
|
输出示例：
1
2
| Seq Scan on customers (cost=0.00..18.10 rows=10 width=372)
Filter: ((country)::text = 'USA'::text)
|
2. 包含实际执行时间（推荐）
1
| EXPLAIN ANALYZE SELECT * FROM customers WHERE country = 'USA';
|
输出示例：
1
2
3
4
5
| Seq Scan on customers (cost=0.00..18.10 rows=10 width=372) (actual time=0.012..0.025 rows=10 loops=1)
Filter: ((country)::text = 'USA'::text)
Rows Removed by Filter: 5
Planning Time: 0.123 ms
Execution Time: 0.042 ms
|
📌 关键字段说明：
| 字段 | 含义 |
|---|
cost | 预估成本（单位为磁盘页读取次数） |
rows | 预估返回行数 |
width | 每行平均字节数 |
actual time | 实际执行时间（毫秒） |
loops | 循环次数（适用于嵌套循环 JOIN） |
Planning Time | 查询规划耗时 |
Execution Time | 实际执行耗时 |
🧭 二、常见执行计划类型解析
PostgreSQL 支持多种执行计划类型，每种都有其适用场景和性能特点。
1. Sequential Scan（顺序扫描）
- 含义：全表扫描每一行
- 适用场景：
- 优化建议：通常应避免在大表上使用
1
| Seq Scan on orders (cost=0.00..18.10 rows=1000 width=60)
|
2. Index Scan（索引扫描）
- 含义：使用 B-tree、Hash 等索引逐条查找记录
- 适用场景：
- 精确匹配或小范围查询
- 聚簇索引（CLUSTERED INDEX）效果最佳
- 缺点：可能频繁访问堆表（Heap Table），产生额外 I/O
1
| Index Scan using idx_orders_customer_id on orders (cost=0.29..8.31 rows=1 width=60)
|
3. Index Only Scan（仅索引扫描）
- 含义：查询所需字段全部来自索引，无需回表
- 优点：显著减少 I/O，提升性能
- 要求：索引必须包含所有查询字段
1
| Index Only Scan using idx_orders_customer_date on orders (cost=0.29..8.31 rows=1 width=60)
|
4. Bitmap Heap Scan + Bitmap Index Scan（位图扫描）
- 含义：先通过索引收集匹配行的 TID（物理地址），然后批量回表
- 适用场景：
- 优点：减少随机 I/O，提高效率
1
2
3
4
| Bitmap Heap Scan on orders (cost=4.29..12.31 rows=10 width=60)
Recheck Cond: (customer_id = 'VINET'::bpchar)
-> Bitmap Index Scan on idx_orders_customer_id (cost=0.00..4.29 rows=10 width=0)
Index Cond: (customer_id = 'VINET'::bpchar)
|
5. Nested Loop / Hash Join / Merge Join（连接类型）
| 类型 | 描述 | 适用场景 |
|---|
| Nested Loop | 对每一条左表记录遍历右表 | 小结果集、有索引 |
| Hash Join | 构建哈希表进行快速匹配 | 大表连接 |
| Merge Join | 排序后合并两个有序结果集 | 已排序或索引支持排序 |
⚙️ 三、实用查询优化技巧
1. 使用合适的索引
- 根据查询条件选择合适的索引类型（B-tree、BRIN、GIN/GiST、Bloom）
- 创建复合索引时注意列顺序（前导列优先）
- 使用
INCLUDE 添加覆盖字段以实现 Index Only Scan
1
2
| -- 创建带 INCLUDE 的索引
CREATE INDEX idx_orders_customer_date_include ON orders (customer_id, order_date) INCLUDE (total_amount);
|
2. 避免 SELECT *
- 明确指定需要的字段，避免不必要的 I/O 和内存消耗
1
2
3
4
5
| -- 不推荐
SELECT * FROM orders;
-- 推荐
SELECT order_id, customer_id, order_date FROM orders;
|
3. 控制返回行数（LIMIT）
- 使用
LIMIT 减少数据传输量 - 在分页查询中结合
OFFSET 使用
1
| SELECT * FROM orders ORDER BY order_date DESC LIMIT 10 OFFSET 20;
|
4. 优化 JOIN 顺序
- 尽量让驱动表（主表）是较小的结果集
- 使用
ANALYZE 更新统计信息帮助优化器判断
1
2
3
4
5
| SET LOCAL statement_timeout = '30s';
EXPLAIN ANALYZE
SELECT *
FROM large_table l
JOIN small_table s ON l.id = s.ref_id;
|
5. 子查询 vs CTE vs JOIN
- 子查询：适合简单过滤逻辑
- CTE：增强可读性，适合递归结构
- JOIN：性能最佳，适合多表关联
1
2
3
4
5
6
7
8
9
10
| -- JOIN 更高效
SELECT o.order_id, c.company_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- CTE 更清晰
WITH us_customers AS (
SELECT customer_id FROM customers WHERE country = 'USA'
)
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM us_customers);
|
🧪 四、实战演练：优化 Northwind 中的慢查询
场景描述：
你发现一个查询非常慢，用于找出“客户 ‘VINET’ 在 1996 年下的所有订单及其产品详情”。
原始查询：
1
2
3
4
5
6
| SELECT o.order_id, p.product_name, od.quantity, od.unit_price
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE o.customer_id = 'VINET'
AND o.order_date BETWEEN '1996-01-01' AND '1996-12-31';
|
步骤 1：查看执行计划
1
2
| EXPLAIN ANALYZE
SELECT ... -- 上述查询
|
观察是否出现 Seq Scan 或 Nested Loop 效率低下。
步骤 2：添加索引
1
2
3
4
5
| -- 为客户 ID 和订单日期创建复合索引
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
-- 为订单详情表建立外键索引
CREATE INDEX idx_order_details_order_id ON order_details (order_id);
|
步骤 3：再次执行并对比性能
1
2
| EXPLAIN ANALYZE
SELECT ... -- 再次执行相同查询
|
你应该能看到明显的时间下降，执行路径也从 Seq Scan 变为 Index Scan 或 Bitmap Scan。
📌 小结
| 技术 | 功能 | 推荐使用场景 |
|---|
EXPLAIN | 查看执行计划 | 所有 SQL 查询 |
EXPLAIN ANALYZE | 查看实际执行时间 | 性能调优 |
Index Only Scan | 避免回表 | 查询字段都在索引中 |
Bitmap Scan | 多条件组合查询 | 中等规模数据 |
JOIN 优化 | 提高多表查询效率 | 复杂业务逻辑 |
通过本节的学习，你应该已经掌握了 PostgreSQL 中查询执行计划的基本结构和关键指标，并能够根据执行计划识别性能瓶颈，采取合适的优化策略。下一节我们将进入“事务控制与并发机制”章节，深入讲解 PostgreSQL 的 ACID 特性和 MVCC 实现原理。
