上一篇【第32篇】postgresql特色功能拾遗——数组、并行查询与fdw 下一篇【第34篇】硬件与操作系统优化——CPU、内存、存储与文件系统
性能优化是 DBA 和后端开发者的"必修课"。但很多人一上来就改 SQL、加索引，却忽略了更上层的优化空间。本文将建立一套完整的优化方法论，从硬件到 SQL，帮你系统地定位和解决性能问题。
一、优化的层次结构
数据库性能优化不是单一维度的，而是一个从上到下的金字塔：
数据库优化金字塔：
┌───────────┐
│ 应用层 │ ← SQL写法、ORM配置、批量操作
┌┴───────────┴┐
│ 数据库层 │ ← 索引、参数配置、表结构
┌┴─────────────┴┐
│ 操作系统层 │ ← 文件系统、I/O调度、内存分配
┌┴───────────────┴┐
│ 硬件层 │ ← CPU、内存、磁盘、网络
└──────────────────┘
优化原则：自下而上，先确保底层没问题，再优化上层。
但投入产出比通常上层 > 下层（改一行SQL可能快10倍）。
二、优化方法论——四步法
第1步：确定瓶颈在哪
瓶颈定位决策树：
查询慢？
│
┌───────────┼───────────┐
│ │ │
所有查询都慢 特定查询慢 高并发时慢
│ │ │
系统级瓶颈 SQL/索引问题 连接/锁问题
│ │ │
┌──────┴──────┐ EXPLAIN pg_stat_activity
│ │ ANALYZE pg_locks
CPU满？ I/O满？ 优化SQL 优化连接池
│ │
加CPU 换SSD
加内存 优化I/O
第2步：80/20 法则——关注最重要的
— 找出最慢的 SQL（消耗最多时间的 20% 查询）
— 使用 pg_stat_statements 扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
left(query, 80) AS query,
calls,
total_exec_time / 1000 AS total_time_ms,
mean_exec_time / 1000 AS avg_time_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
— 优化这 20% 的查询，通常能解决 80% 的性能问题
第3步：先量化，再优化
— ❌ 错误做法："我觉得这个查询慢，加个索引试试"
— ✅ 正确做法：先测量，再优化，再测量
— 优化前
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
— 记录：执行时间 500ms，Buffer hits=100, reads=50000
— 加索引后
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
— 优化后
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
— 记录：执行时间 2ms，Buffer hits=5, reads=3
— 优化效果：250 倍提升！有数据支撑
第4步：一次只改一个变量
优化纪律：
┌────────────────────────────────────────────────────────────┐
│ 1. 每次只改一个参数/索引/SQL │
│ 2. 每次修改前后都要测量对比 │
│ 3. 如果没有改善，立即回滚 │
│ 4. 记录每次优化的效果（建立优化日志） │
│ 5. 避免同时改多个东西（无法判断哪个生效了） │
└────────────────────────────────────────────────────────────┘
三、各层优化要点
3.1 硬件层优化
硬件优化优先级（投入产出比从高到低）：
┌────────────────────────────────────────────────────────────┐
│ 1. 内存（性价比最高） │
│ – 增加 shared_buffers │
│ – 增加 effective_cache_size │
│ – 启用大页内存 │
│ │
│ 2. 磁盘（I/O 瓶颈最明显） │
│ – HDD → SSD → NVMe（性能差异巨大） │
│ – WAL 放在单独磁盘 │
│ – 使用 RAID 10 │
│ │
│ 3. CPU（通常不是瓶颈） │
│ – 更多核心 → 更多并行查询 Worker │
│ – 更高主频 → 单查询更快 │
│ │
│ 4. 网络（分布式场景） │
│ – 主备复制带宽 │
│ – 应用连接延迟 │
└────────────────────────────────────────────────────────────┘
3.2 操作系统层优化
# Linux 内核参数优化
# /etc/sysctl.conf
# 共享内存（必须大于 shared_buffers）
kernel.shmmax = 68719476736 # 64GB
kernel.shmall = 16777216
# 内存管理
vm.swappiness = 1 # 最小化 swap 使用
vm.dirty_ratio = 10 # 脏页比例
vm.dirty_background_ratio = 5
# 文件描述符
fs.file-max = 1048576
# 网络优化
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
3.3 数据库层优化
— PostgreSQL 核心参数优化清单
— 内存参数
shared_buffers = '4GB' — 总内存的 25%
effective_cache_size = '12GB' — 总内存的 75%
work_mem = '64MB' — 排序/哈希操作的内存
maintenance_work_mem = '512MB' — 维护操作的内存
— WAL 参数
wal_buffers = '64MB'
checkpoint_completion_target = 0.9
max_wal_size = '2GB'
— 并行查询
max_parallel_workers_per_gather = 4
— 连接
max_connections = 200
3.4 应用层优化
应用层优化要点：
┌────────────────────────────────────────────────────────────┐
│ 1. 使用连接池（PgBouncer）减少连接开销 │
│ 2. 避免 N+1 查询（批量获取代替循环查询） │
│ 3. 使用 PREPARED STATEMENT 减少解析开销 │
│ 4. 合理使用事务（短事务，减少锁持有时间） │
│ 5. 避免 SELECT *（只查需要的列） │
│ 6. 分页查询使用游标分页（keyset pagination）代替 OFFSET │
│ 7. 读写分离（查询走备库） │
└────────────────────────────────────────────────────────────┘
四、慢查询诊断流程
4.1 发现慢查询
— 方式1：pg_stat_statements（推荐）
SELECT
dbid, query, calls,
total_exec_time / 1000 AS total_ms,
mean_exec_time / 1000 AS avg_ms,
max_exec_time / 1000 AS max_ms,
rows,
shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE mean_exec_time > 100 — 平均执行时间超过 100ms
ORDER BY total_exec_time DESC;
— 方式2：pg_stat_activity（实时监控）
SELECT pid, usename, state,
now() – query_start AS duration,
left(query, 100) AS query
FROM pg_stat_activity
WHERE state = 'active'
AND now() – query_start > INTERVAL '1 second'
ORDER BY duration DESC;
— 方式3：log_min_duration_statement（日志记录慢查询）
ALTER SYSTEM SET log_min_duration_statement = '200'; — 记录超过 200ms 的查询
SELECT pg_reload_conf();
— 慢查询会记录到 postgresql 日志中
4.2 分析执行计划
— 使用 EXPLAIN ANALYZE 分析慢查询
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT)
SELECT o.id, o.customer_id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.amount DESC
LIMIT 50;
— 关键指标：
— actual time vs estimated time（差距大 → 统计信息过期）
— rows vs estimated rows（差距大 → ANALYZE）
— shared hit vs read（read 多 → 需要更多内存或更好的索引）
— 执行计划中是否有 Seq Scan（全表扫描，考虑加索引）
— 是否有 Nested Loop + 大量循环（考虑 Hash Join）
4.3 常见性能反模式
— 反模式1：SELECT *（获取不需要的列）
— ❌
SELECT * FROM orders WHERE customer_id = 42;
— ✅ 只查需要的列
SELECT id, amount, status FROM orders WHERE customer_id = 42;
— 反模式2：OFFSET 分页（深分页极慢）
— ❌ 越往后越慢
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 100000;
— ✅ keyset 分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 50;
— 反模式3：N+1 查询
— ❌ 循环查询（100次查询）
FOR customer IN customers:
SELECT * FROM orders WHERE customer_id = customer.id
— ✅ 批量查询（1次查询）
SELECT * FROM orders WHERE customer_id IN (...);
— 反模式4：在索引列上使用函数
— ❌ 索引失效
SELECT * FROM orders WHERE LOWER(email) = 'test@example.com';
— ✅ 使用表达式索引或 ILIKE
CREATE INDEX idx_email_lower ON users ((lower(email)));
SELECT * FROM users WHERE lower(email) = 'test@example.com';
— 反模式5：过度使用 COUNT(*)
— ❌ 实时 COUNT(*) 大表很慢
SELECT count(*) FROM orders; — 可能需要全表扫描
— ✅ 使用 estimate 或缓存
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders'; — 近似值
五、优化 Checklist
性能优化 Checklist：
┌────────────────────────────────────────────────────────────┐
│ □ 是否有缺失的索引？ │
│ □ 是否有过期或缺失的统计信息？(ANALYZE) │
│ □ 是否有不合理的查询？（全表扫描、N+1、SELECT *） │
│ □ 连接池是否配置正确？ │
│ □ 内存参数是否合理？（shared_buffers, work_mem） │
│ □ 是否有表膨胀？（VACUUM/pg_repack） │
│ □ 是否有长事务？（阻塞 VACUUM） │
│ □ 是否有锁等待？（pg_locks） │
│ □ 磁盘 I/O 是否是瓶颈？（iostat） │
│ □ 是否可以读写分离？（备库承担读） │
│ □ 是否有慢查询日志？(log_min_duration_statement) │
│ □ 是否启用了 pg_stat_statements？ │
│ □ 并行查询是否开启？(max_parallel_workers_per_gather) │
│ □ WAL 日志是否放在高速磁盘上？ │
│ □ autovacuum 是否正常运行？ │
└────────────────────────────────────────────────────────────┘
六、总结
数据库优化是一个系统工程，关键要点：
下一篇，我们深入硬件与操作系统优化，了解 CPU、内存、磁盘和文件系统对 PostgreSQL 性能的影响。
标签：PostgreSQL、性能优化、方法论、慢查询、EXPLAIN、优化Checklist
上一篇【第32篇】postgresql特色功能拾遗——数组、并行查询与fdw 下一篇【第34篇】硬件与操作系统优化——CPU、内存、存储与文件系统
