数据库调试 - PostgreSQL 性能分析与优化

100 天认知提升计划 | Day 6

---

目录

• 第一部分：PostgreSQL 性能分析基础
  • pg_stat_statements：SQL 性能统计
  • EXPLAIN：执行计划分析
  • pg_stat_activity：实时会话监控
• 第二部分：性能优化实战
  • 索引优化策略
  • SQL 查询优化
  • 配置参数调优
• 第三部分：工具与实践
  • pgcli：现代化命令行工具
  • pgFormatter：SQL 格式化工具
  • 实践与思考

---

第一部分：PostgreSQL 性能分析基础

pg_stat_statements：SQL 性能统计

什么是 pg_stat_statements？

pg_stat_statements 是 PostgreSQL 官方提供的核心性能监控扩展模块，用于跟踪所有 SQL 语句的执行统计信息。它就像是数据库的"黑匣子"，记录每条 SQL 的运行痕迹，帮助定位性能瓶颈。

核心功能

功能	说明
执行统计	记录 SQL 的执行次数、总耗时、平均耗时
资源消耗	统计 I/O 开销（缓存命中/磁盘读取）、内存使用
查询归一化	自动将常量替换为参数，聚合相同结构的查询

安装与配置

# 1. 修改 postgresql.conf 配置文件
shared_preload_libraries = 'pg_stat_statements'  # 必须重启生效
pg_stat_statements.max = 10000                    # 最多跟踪10000条不同SQL
pg_stat_statements.track = all                    # 跟踪所有语句（含函数内SQL）
track_io_timing = on                              # 跟踪I/O时间

# 2. 重启 PostgreSQL 服务
sudo systemctl restart postgresql

# 3. 在数据库中创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

常用查询示例

-- 查询总执行时间最长的前10条SQL
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查询缓存命中率最低的SQL
SELECT
    query,
    calls,
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY hit_percent ASC;

-- 重置统计信息
SELECT pg_stat_statements_reset();

关键字段说明

字段	说明
queryid	SQL 的唯一哈希ID（相同结构的SQL哈希值相同）
query	SQL 文本（常量会被替换为 $1、$2）
calls	执行次数
total_exec_time	总执行时间（毫秒）
mean_exec_time	平均执行时间（毫秒）
shared_blks_hit	共享缓存命中次数
shared_blks_read	共享缓存未命中次数（需要读磁盘）

---

EXPLAIN：执行计划分析

什么是执行计划？

执行计划是 PostgreSQL 执行 SQL 语句时所采取的操作步骤和执行顺序。通过查看执行计划，我们可以了解查询是否使用了索引、是否存在全表扫描等性能问题。

基本用法

-- 查看执行计划（不执行查询）
EXPLAIN SELECT * FROM users WHERE age > 18;

-- 查看执行计划并实际执行（获取真实耗时）
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

-- 详细分析模式
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

执行计划解读重点

扫描类型	说明	性能
Seq Scan	顺序扫描（全表扫描）	慢，需警惕
Index Scan	索引扫描	较快
Bitmap Scan	位图扫描（批量读取索引）	适合大范围查询
Tid Scan	按行ID扫描	最快

执行时间解读

Planning Time: 0.1 ms    -- 查询规划时间
Execution Time: 120.5 ms  -- 实际执行时间（重点关注）

Buffers: shared hit=15 read=230  -- 缓存命中15次，磁盘读取230次（read高需优化）

优化决策流程

发现问题SQL (pg_stat_statements)
         ↓
EXPLAIN ANALYZE 分析执行计划
         ↓
识别瓶颈类型
    ├─ 全表扫描 → 添加索引
    ├─ 索引未使用 → 检查统计信息/查询条件
    ├─ 排序开销大 → 调整 work_mem
    └─ 连接效率低 → 优化 JOIN 条件
         ↓
实施优化并验证效果

---

pg_stat_activity：实时会话监控

核心功能

pg_stat_activity 视图可以查看当前所有数据库会话的状态，包括：

• 正在执行的 SQL 语句
• 会话的等待状态
• 连接的客户端信息
• 事务状态

常用查询

-- 查看当前活跃会话
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    state_change
FROM pg_stat_activity
WHERE state != 'idle';

-- 查找长时间运行的查询
SELECT
    pid,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- 查找被阻塞的会话
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
WHERE NOT blocked_locks.GRANTED;

终止会话

-- 终止指定会话（需谨慎使用）
SELECT pg_terminate_backend(pid);

-- 取消指定会话的当前查询
SELECT pg_cancel_backend(pid);

---

第二部分：性能优化实战

索引优化策略

索引类型选择

索引类型	适用场景	示例
B-tree	等值查询、范围查询	WHERE id = 1 或 WHERE date > '2024-01-01'
GIN	多值类型、全文检索	JSONB、数组、全文搜索
GiST	空间数据、范围查询	PostGIS 地理数据
Hash	等值查询	简单的 = 比较（较少使用）

复合索引设计原则

-- 遵循最左前缀原则
-- 如果经常有 WHERE user_id = ? AND order_date > ? 的查询
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 优先将选择性高的列放在前面
-- 部分索引（只索引部分数据，更小更高效）
CREATE INDEX idx_active_orders ON orders(order_id)
WHERE status = 'active';

-- 覆盖索引（包含查询所需所有列，避免回表）
CREATE INDEX idx_orders_user_status
ON orders(user_id, status) INCLUDE (total, created_at);

索引管理

-- 查看表的索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

-- 查找未使用的索引
SELECT
    schemaname,
    tablename,
    indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey';

---

SQL 查询优化

常见问题与优化

问题类型	优化方案
避免使用 SELECT *	只查询需要的列，减少数据传输
避免在 WHERE 中对字段做函数操作	会导致索引失效
减少嵌套子查询	改写为 JOIN 通常更高效
使用 CTE 优化复杂查询	提高可读性，某些情况下可提升性能
避免 SELECT DISTINCT	考虑使用 GROUP BY

查询重写示例

-- 原始（低效）：子查询
SELECT * FROM t1
WHERE id IN (SELECT id FROM t2 WHERE condition);

-- 优化后（高效）：改写为 JOIN
SELECT t1.* FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t2.condition;

-- 原始：索引列上使用函数（索引失效）
SELECT * FROM users
WHERE TO_CHAR(created_at, 'YYYY-MM') = '2024-01';

-- 优化后：改为范围查询（可使用索引）
SELECT * FROM users
WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01';

---

配置参数调优

核心内存参数

参数	作用	推荐值（SSD环境）
shared_buffers	共享缓冲区大小	物理内存的 25%
work_mem	单查询工作内存	64-256MB
effective_cache_size	可用缓存估算	物理内存的 70%
maintenance_work_mem	维护操作内存	1-2GB

配置示例

# postgresql.conf
shared_buffers = 4GB              # 16GB内存的服务器
work_mem = 64MB                   # 避免设置过大导致OOM
effective_cache_size = 12GB
maintenance_work_mem = 1GB

# 连接配置
max_connections = 100             # 根据应用需求调整

# WAL 配置
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# 查询规划器相关
random_page_cost = 1.1            # SSD环境可降低此值

---

第三部分：工具与实践

pgcli：现代化命令行工具

什么是 pgcli？

pgcli 是一个支持自动补全和语法高亮的现代化 PostgreSQL 命令行客户端，相比原生的 psql 工具，提供了更好的用户体验。

安装与使用

# 安装
pip install pgcli

# 连接数据库
pgcli -h localhost -p 5432 -U postgres -d mydb

# 常用快捷键
Ctrl+R     # 搜索历史命令
Ctrl+Space # 触发自动补全
Ctrl+D     # 退出

核心特性

特性	说明
智能自动补全	补全表名、列名、关键字、函数
语法高亮	SQL 关键字、字符串、注释高亮显示
多行编辑	支持复杂SQL的编辑
表格输出	结果以表格形式展示，更易读
主题支持	多种配色主题可选

---

pgFormatter：SQL 格式化工具

什么是 pgFormatter？

pgFormatter 是专为 PostgreSQL 设计的 SQL 语法美化工具，能够将杂乱的 SQL 代码转换为规范、易读的格式。

安装与使用

# 安装（需要 Perl 环境）
git clone https://github.com/darold/pgFormatter.git
cd pgFormatter
perl Makefile.PL
make && make install

# 格式化单个文件
pg_format example.sql

# 从标准输入格式化
echo "SELECT * FROM table;" | pg_format

# 保存格式化结果
pg_format input.sql -o output.sql

# 格式化并匿名化敏感数据
pg_format sensitive.sql -a -o anonymized.sql

格式化效果对比

-- 格式化前
SELECT a,b,c FROM table1 WHERE a=1 AND b=2 ORDER BY c;

-- 格式化后
SELECT
    a,
    b,
    c
FROM
    table1
WHERE
    a = 1
AND b = 2
ORDER BY
    c;

---

实践与思考

实践记录

• [ ] 任务 1：在本机或虚拟机安装 PostgreSQL，启用 pg_stat_statements 扩展
• [ ] 任务 2：创建测试表和索引，使用 EXPLAIN ANALYZE 对比不同查询的执行计划
• [ ] 任务 3：安装并配置 pgcli，体验自动补全和语法高亮功能
• [ ] 任务 4：使用 pgFormatter 格式化一段复杂的 SQL 查询
• [ ] 任务 5：编写 SQL 找出当前数据库中执行时间最长的 5 条查询
• [ ] 任务 6：为一个经常慢的查询创建合适的索引，验证优化效果
• [ ] 任务 7：调整 shared_buffers 和 work_mem 参数，观察性能变化
• [ ] 任务 8：使用 pg_stat_activity 监控数据库，识别并终止长时间运行的查询

疑问与思考

已解答

1. ✅ PostgreSQL 性能分析的核心工具是什么？（pg_stat_statements、EXPLAIN、pg_stat_activity）
2. ✅ 如何识别慢查询？（通过 pg_stat_statements 按 total_exec_time 排序）
3. ✅ 索引类型如何选择？（B-tree 用于等值/范围查询，GIN 用于多值/全文）

待探索

1. ❓ 如何在高并发场景下避免锁争用？
2. ❓ 什么情况下应该使用分区表？
3. ❓ VACUUM 和 AUTOVACUUM 的工作原理是什么？
4. ❓ 如何设置合理的 slow query log 阈值？
5. ❓ PostgreSQL 的查询规划器如何选择执行计划？

---

参考资料

• PostgreSQL 官方文档 - pg_stat_statements
• PostgreSQL 官方文档 - EXPLAIN
• pgcli GitHub 仓库
• pgFormatter 项目主页
• PostgreSQL 性能调优最佳实践

---

更新日期：2026-02-20