42、PostgreSQL性能调优：EXPLAIN ANALYZE、索引优化、VACUUM、ANALYZE、配置参数调优
PostgreSQL 性能调优，说白了就是让数据库跑得更快、更稳。我做了这么多年嵌入式数据库，见过太多「SQL 写出来能跑就行」的项目，结果一到数据量上来就崩。今天咱们就聊聊 PG 调优的几个核心手段：EXPLAIN ANALYZE、索引优化、VACUUM、ANALYZE、配置参数调优。这些招数我在项目中反复用过，效果立竿见影。
核心观点：调优不是玄学，是科学。先诊断（EXPLAIN），再开药（索引、VACUUM、参数），最后复查（再次 EXPLAIN）。
一、EXPLAIN ANALYZE：先看执行计划
你想想看，SQL 写得再漂亮，数据库怎么执行才是关键。EXPLAIN ANALYZE 就是 PG 的「体检报告」。我习惯在调优前先跑一遍，看看哪里是瓶颈。
-- 基础用法
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2024-01-01';
-- 更详细的输出
EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM orders WHERE order_date > '2024-01-01';输出里你会看到几个关键指标：
- Seq Scan（顺序扫描）—— 如果出现这个，说明没走索引，数据量大时很慢。
- Index Scan（索引扫描）—— 走索引了，但还要回表查数据。
- Index Only Scan（仅索引扫描）—— 最理想的情况，索引里就有你要的数据。
- Rows 和 Actual Rows —— 预估行数和实际行数差距大，说明统计信息过时了。
我的习惯：每次上线新 SQL 之前，必跑 EXPLAIN ANALYZE。曾经有一次我发现预估行数差了 100 倍，一查发现 ANALYZE 没跑过，统计信息还是上周的。
二、索引优化：选对索引，事半功倍
索引不是越多越好。我见过有人一张表建了 20 个索引，结果写入慢得像蜗牛。索引优化的核心是：用最少的索引覆盖最多的查询。
2.1 B-Tree 索引（最常用）
-- 单列索引
CREATE INDEX idx_order_date ON orders(order_date);
-- 复合索引（注意列顺序）
CREATE INDEX idx_order_customer_date ON orders(customer_id, order_date);
-- 部分索引（只索引需要的行）
CREATE INDEX idx_active_orders ON orders(order_status) WHERE order_status = 'ACTIVE';复合索引的列顺序很重要。我建议把等值查询的列放前面，范围查询的列放后面。比如 WHERE customer_id = 123 AND order_date > '2024-01-01'，就应该先建 customer_id，再建 order_date。
2.2 其他索引类型
| 索引类型 | 适用场景 | 我踩过的坑 |
|---|---|---|
| GIN | 全文搜索、数组、JSONB | 曾经对 JSONB 字段建了 GIN 索引，但查询条件写错了，索引没生效 |
| GiST | 空间数据、范围类型 | 地理坐标查询时，GiST 比 B-Tree 快 10 倍 |
| BRIN | 超大表、数据天然有序 | 日志表用 BRIN 索引，存储空间只有 B-Tree 的 1/50 |
注意：索引不是银弹。如果表很小（几百行），全表扫描可能比走索引还快。另外，写频繁的表索引太多，INSERT/UPDATE 会变慢。
三、VACUUM：清理垃圾，回收空间
PostgreSQL 的 MVCC 机制有个特点：更新和删除不会立即清理旧数据，而是标记为「死元组」。时间长了，这些死元组会占用大量空间，查询也会变慢。VACUUM 就是干这个的。
-- 标准 VACUUM（不锁表）
VACUUM orders;
-- VACUUM FULL（锁表，但回收空间更彻底）
VACUUM FULL orders;
-- 自动 VACUUM 参数查看
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;我个人建议：开启 autovacuum，不要手动频繁跑 VACUUM FULL。VACUUM FULL 会锁表，生产环境慎用。曾经有一次我在白天跑了 VACUUM FULL，结果业务停了 5 分钟，被老板骂了一顿。
避坑指南：如果发现表膨胀严重，先检查 autovacuum 是否正常工作。可以用 SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup > 1000; 查看死元组数量。
四、ANALYZE：更新统计信息
优化器做决策靠的是统计信息。如果统计信息过时，优化器可能选错执行计划。ANALYZE 就是更新这些统计信息的命令。
-- 分析单表
ANALYZE orders;
-- 分析整个数据库
ANALYZE;
-- 查看统计信息
SELECT * FROM pg_stats WHERE tablename = 'orders';为什么统计信息会过时？因为数据在变。你想想看，昨天表里只有 1000 行，今天变成了 100 万行，优化器还按 1000 行做计划，能准吗？
我习惯在批量导入数据后手动跑一次 ANALYZE。autovacuum 也会自动触发 ANALYZE，但阈值可能不够敏感。
五、配置参数调优：让 PG 跑得更顺
PostgreSQL 的默认配置偏保守，适合小内存机器。如果你有 16GB 内存，还用默认配置，那就是浪费。下面是我常用的调优参数：
| 参数名 | 默认值 | 建议值（16GB 内存） | 说明 |
|---|---|---|---|
| shared_buffers | 128MB | 4GB | PG 的缓存区，建议设为内存的 25% |
| effective_cache_size | 4GB | 12GB | 告诉优化器系统有多少缓存可用 |
| work_mem | 4MB | 32MB | 排序、哈希操作的内存，注意是每个操作 |
| maintenance_work_mem | 64MB | 1GB | VACUUM、CREATE INDEX 等维护操作 |
| wal_buffers | 16MB | 64MB | WAL 日志缓冲区，写频繁时调大 |
| random_page_cost | 4.0 | 1.1（SSD） | SSD 比机械盘快，调低让优化器更倾向索引扫描 |
-- 修改配置（需要重启）
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET random_page_cost = 1.1;
-- 查看当前配置
SHOW shared_buffers;
SHOW work_mem;注意：work_mem 是每个操作、每个连接都独立分配的。如果连接数很多，work_mem 设太大可能导致 OOM。我建议先设 32MB，观察一段时间再调。
六、调优实战流程
说了这么多，实际调优怎么下手？我总结了一个流程：
- 先跑 EXPLAIN ANALYZE —— 找到慢查询。
- 检查索引 —— 有没有走索引？索引类型对不对？
- 检查统计信息 —— 跑 ANALYZE，再看执行计划有没有变化。
- 检查表膨胀 —— 跑 VACUUM，清理死元组。
- 调整配置参数 —— 根据硬件资源调 shared_buffers、work_mem 等。
- 再次 EXPLAIN ANALYZE —— 确认效果。
核心原则：一次只改一个变量，改完就测。不要同时改索引、VACUUM、参数，否则你根本不知道哪个改动起了作用。
七、SVG 流程图：调优决策树
八、总结
PostgreSQL 性能调优，说白了就是先诊断、再开药、后复查。EXPLAIN ANALYZE 是诊断工具，索引优化是精准打击，VACUUM 和 ANALYZE 是日常保养，配置参数是硬件适配。这五招组合起来，大部分性能问题都能解决。
我记得有一次帮客户调优一个电商系统，订单表 5000 万行，查询要 30 秒。我一看执行计划，全表扫描，统计信息还是三个月前的。跑完 ANALYZE，再建个复合索引，查询降到 200 毫秒。客户当场就愣住了。
嗯，调优就是这么回事——找到问题，对症下药。别怕，多练几次就熟了。
公众号:蓝海资料掘金营，微信deep3321