PostgreSQL实战指南：多领域数据库应用与实践

第六部分：PostgreSQL高级管理与优化

本部分聚焦于 PostgreSQL 的高级运维和管理，内容涵盖高可用架构、备份恢复、安全加固、性能诊断与调优，以及通过扩展开发来增强数据库功能。旨在帮助数据库管理员（DBA）和开发人员精通 PostgreSQL 的管理与维护，确保数据库系统的稳定、安全与高效。

第27章：性能监控与调优实践

数据库性能调优是一个系统性工程，它涉及从宏观的系统监控到微观的 SQL 查询分析。一个性能良好的数据库能够以最少的资源消耗，为应用程序提供最快的响应。本章将介绍 PostgreSQL 性能监控的核心指标、查询优化的关键工具 EXPLAIN，以及常见的调优策略。

27.1 性能监控：关键指标与视图

PostgreSQL 内部记录了大量的统计信息，通过查询这些系统视图，我们可以了解数据库的运行状况。

pg_stat_activity: 显示当前所有连接到数据库的会话信息。非常适合用于查找长时间运行的查询、空闲的事务（idle in transaction）或锁等待。
- state: 连接状态 (active, idle, idle in transaction)。
- wait_event_type, wait_event: 连接正在等待什么（如 Lock, IO）。
- query: 正在执行的查询文本。
pg_stat_statements: (需要创建扩展 CREATE EXTENSION pg_stat_statements;) 这是性能调优最有用的工具之一。它跟踪所有已执行查询的统计信息，如执行次数（calls）、总耗时（total_exec_time）、平均耗时（mean_exec_time）、读写的块数量等。通过查询这个视图，可以快速定位系统中开销最大的查询。
pg_stat_user_tables: 显示表的统计信息，如顺序扫描次数（seq_scan）、索引扫描次数（idx_scan）、表中的行数（n_live_tup）等。如果一个大表的 seq_scan 次数非常高，通常意味着查询缺少合适的索引。
pg_locks: 显示当前所有的锁信息，用于诊断死锁或锁竞争问题。

27.2 查询优化：精读 `EXPLAIN` 执行计划

EXPLAIN 是分析和优化 SQL 查询性能的最重要工具。它会显示 PostgreSQL 的查询优化器为一条 SQL 语句选择的执行计划，即数据库将如何一步步地获取数据。

EXPLAIN <QUERY>: 显示预估的执行计划和成本。
EXPLAIN ANALYZE <QUERY>: 实际执行查询，并显示真实的执行时间和行数。这是最准确的分析方式，但要小心，因为它会实际执行 INSERT, UPDATE, DELETE 等写操作。

如何解读执行计划:

执行计划是一个树形结构，从内到外、从下到上看。每一层节点代表一个操作。

扫描类型 (Scan Types):
- Seq Scan (Sequential Scan): 全表扫描。对于小表是正常的，但对于大表通常是性能瓶颈的标志。
- Index Scan: 通过索引查找数据，然后访问表获取行。非常高效。
- Index-Only Scan: 查询所需的数据完全包含在索引中，无需访问表本身。性能最高。
- Bitmap Heap Scan: 当 WHERE 条件涉及多个索引时，优化器可能会先在内存中通过位图（Bitmap）合并多个索引的结果，然后再统一访问表。
连接类型 (Join Types):
- Nested Loop Join: 嵌套循环。对于一个表很小的情况很高效。
- Hash Join: 将一个表（通常是较小的表）在内存中构建一个哈希表，然后扫描另一个表进行匹配。适合大数据量的等值连接。
- Merge Join: 如果两个表都已按连接键排好序，则可以高效地进行合并连接。
成本 (Cost): cost=startup_cost..total_cost
- startup_cost: 返回第一行数据之前的预估成本。
- total_cost: 返回所有数据所需的总预估成本。成本是一个抽象的单位，用于比较不同计划的优劣，不直接对应时间。
行数 (Rows): rows=N 是优化器预估会返回的行数。如果 EXPLAIN ANALYZE 显示的实际行数（actual rows=...）与预估行数差异巨大，通常意味着表的统计信息过时，需要运行 ANALYZE <table_name>; 来更新。

27.3 索引调优

索引是提升查询性能最立竿见影的方法。但索引并非越多越好，因为它们会占用存储空间，并拖慢写操作（INSERT, UPDATE, DELETE）的速度。

创建索引的原则:

在 WHERE 子句中频繁用于过滤的列上创建索引。
在 JOIN 操作的连接键上创建索引。
在 ORDER BY 子句中用于排序的列上创建索引，可以避免昂贵的排序操作。
考虑创建复合索引（多列索引）。列的顺序很重要，应将最常用于等值过滤的列放在前面。
使用 INCLUDE 子句创建覆盖索引，以支持更多的索引扫描。

27.4 场景实战：优化一个慢查询

业务场景描述:

一个报表查询用于查找特定产品类别在某段时间内的所有大额订单，执行非常缓慢。

慢查询 SQL:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id
JOIN
    products p ON oi.product_id = p.product_id
JOIN
    customers c ON o.customer_id = c.customer_id
WHERE
    p.category = 'Electronics'
    AND o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
    AND (oi.quantity * oi.unit_price) > 1000;

步骤1：使用 EXPLAIN ANALYZE 分析查询

1
EXPLAIN ANALYZE SELECT ...;

可能的执行计划输出 (简化版):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1.30..650.00 rows=50 width=104) (actual time=10.5..1500.2 ms rows=100 loops=1)
   ->  Hash Join  (cost=1.00..300.00 rows=100 width=80) (actual time=10.0..1200.1 ms rows=500 loops=1)
         Hash Cond: (o.customer_id = c.id)
         ->  Hash Join  (cost=0.50..200.00 rows=200 width=60) (actual time=5.0..800.5 ms rows=1000 loops=1)
               Hash Cond: (oi.product_id = p.id)
               ->  Seq Scan on order_items oi  (cost=0.00..100.00 rows=10000 width=16) (actual time=0.1..200.0 ms rows=10000 loops=1)
                     Filter: ((quantity * unit_price) > 1000)
               ->  Hash  (cost=0.40..0.40 rows=50 width=40) (actual time=0.5..0.5 ms rows=50 loops=1)
                     ->  Seq Scan on products p  (cost=0.00..0.40 rows=50 width=40) (actual time=0.1..0.2 ms rows=50 loops=1)
                           Filter: (category = 'Electronics')
         ->  Hash  (cost=0.20..0.20 rows=100 width=20) (actual time=0.2..0.2 ms rows=100 loops=1)
               ->  Seq Scan on customers c  (cost=0.00..0.20 rows=100 width=20) (actual time=0.1..0.1 ms rows=100 loops=1)
   ->  Index Scan using orders_pkey on orders o  (cost=0.30..3.50 rows=1 width=24) (actual time=0.1..0.1 ms rows=1 loops=500)
         Index Cond: (order_id = oi.order_id)
         Filter: (order_date BETWEEN '2024-01-01' AND '2024-03-31')

问题分析:

Seq Scan on products: 对 products 表进行了全表扫描，然后才用 category = 'Electronics' 进行过滤。
Seq Scan on order_items: 对 order_items 表进行了全表扫描，然后对每一行计算 (quantity * unit_price)。
Filter on orders: 在对 orders 表的索引扫描之后，还有一个 Filter 操作来过滤 order_date。这意味着 order_date 没有被包含在索引中。

步骤2：创建合适的索引

1
2
3
4
5
6
7
8
-- 为 products.category 创建索引
CREATE INDEX idx_products_category ON products (category);

-- 为 orders.order_date 创建索引
CREATE INDEX idx_orders_order_date ON orders (order_date);

-- 为 order_items 上的计算创建一个表达式索引
CREATE INDEX idx_order_items_total ON order_items ((quantity * unit_price));

步骤3：再次分析查询

在创建索引后，再次运行 EXPLAIN ANALYZE。新的执行计划可能会变成：

对 products 表使用 Index Scan。
对 order_items 表使用 Index Scan (基于表达式索引)。
对 orders 表的 JOIN 可能会因为更优的行数估算而选择不同的 JOIN 策略。
整体的 cost 和 actual time 将会显著降低。

27.5 总结

本章我们学习了 PostgreSQL 性能调优的基础知识。我们了解了如何通过 pg_stat_activity 和 pg_stat_statements 等系统视图来监控数据库的宏观性能，并重点深入了如何使用 EXPLAIN ANALYZE 来解读查询执行计划，从而定位微观的 SQL 性能瓶颈。通过一个慢查询优化的实例，我们实践了如何根据执行计划来创建合适的索引，并验证调优效果。性能调优是一个持续的过程，掌握这些工具是成为一名优秀 DBA 或开发人员的必经之路。

PostgreSQL实战指南：多领域数据库应用与实践 - 第27章：性能监控与调优实践

文章目录