PostgreSQL 6 亿行大表慢查询复盘:索引 + 分区 + 参数调优实战

2024 年我们的核心业务库:PostgreSQL 15,主库 64 核 / 256GB / 8TB NVMe,单表最大 6 亿行,某次促销活动开始,数据库 CPU 持续 95%+,慢查询日志暴涨,连接池打满,业务接口大面积超时。投了三周做数据库治理,CPU 回落到 40%,P99 查询从 5s 降到 80ms,连接稳定。本文复盘 PostgreSQL 慢查询 + 索引 + 参数 + 分区治理的完整实战,覆盖执行计划、索引设计、参数调优、分区表、连接池、监控。

故障现场

数据库:PostgreSQL 15.4
主库:64 核 / 256GB / 8TB NVMe
从库:2 个(流复制)
核心表:
- t_order:6 亿行,300GB
- t_order_item:18 亿行,900GB
- t_user:8000w 行

故障表现:
- CPU 持续 95%+
- 慢查询(> 1s)从每天 100 条 → 每分钟 500 条
- 连接数打满(max_connections=500)
- error: FATAL: sorry, too many clients already
- error: canceling statement due to statement timeout
- 业务接口大面积 504

排查工具:
1. pg_stat_statements:看 Top SQL
2. pg_stat_activity:看当前活跃查询
3. EXPLAIN ANALYZE:看执行计划
4. pg_stat_user_tables:看表扫描方式

发现:
- 一条订单查询 Seq Scan 6 亿行(没用索引)
- order_item JOIN 用了 Nested Loop(应该 Hash Join)
- 大量 idle in transaction 连接(事务没提交)
- autovacuum 跟不上,表膨胀严重(dead tuple 40%)

修复 1:执行计划分析

-- 1. 开启 pg_stat_statements(找 Top SQL)
-- postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

-- 查最耗时的 SQL
SELECT
    substring(query, 1, 80) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 2. EXPLAIN ANALYZE 看真实执行
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.name
FROM t_order o
JOIN t_user u ON o.user_id = u.id
WHERE o.created_at >= '2024-05-01'
  AND o.status = 'PAID'
ORDER BY o.created_at DESC
LIMIT 50;

-- 坏的计划(优化前):
-- Seq Scan on t_order  (cost=0..8500000 rows=600000000)
--   Filter: (created_at >= ... AND status = 'PAID')
--   Rows Removed by Filter: 599000000     ← 扫了 6 亿删 5.99 亿
-- Execution Time: 5234.567 ms

-- 好的计划(优化后):
-- Index Scan using idx_order_status_created on t_order
--   Index Cond: (status = 'PAID' AND created_at >= ...)
-- Execution Time: 23.456 ms

-- 3. 查当前阻塞 / 慢查询
SELECT
    pid,
    now() - query_start AS duration,
    state,
    substring(query, 1, 60) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > interval '1 second'
ORDER BY duration DESC;

-- 4. 查锁等待
SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

修复 2:索引设计

-- 1. 复合索引(最左前缀 + 选择性高的列在前)
-- 查询:WHERE status = ? AND created_at >= ? ORDER BY created_at
CREATE INDEX CONCURRENTLY idx_order_status_created
ON t_order (status, created_at DESC);
-- CONCURRENTLY:不锁表建索引(生产必用)

-- 2. 部分索引(只索引热数据,体积小)
-- 99% 查询只查未完成订单
CREATE INDEX CONCURRENTLY idx_order_pending
ON t_order (user_id, created_at)
WHERE status IN ('PENDING', 'PAID', 'SHIPPING');

-- 3. 覆盖索引(INCLUDE,避免回表)
CREATE INDEX CONCURRENTLY idx_order_cover
ON t_order (user_id, status)
INCLUDE (order_no, amount, created_at);
-- 查询只取这几列时,Index Only Scan,不回表

-- 4. 表达式索引
-- 查询:WHERE lower(email) = ?
CREATE INDEX CONCURRENTLY idx_user_email_lower
ON t_user (lower(email));

-- 5. GIN 索引(JSONB / 数组 / 全文)
CREATE INDEX CONCURRENTLY idx_order_ext_gin
ON t_order USING gin (ext_data jsonb_path_ops);
-- 查询:WHERE ext_data @> '{"channel": "app"}'

-- 6. BRIN 索引(时序大表,体积极小)
-- created_at 天然有序的大表
CREATE INDEX CONCURRENTLY idx_order_created_brin
ON t_order USING brin (created_at);
-- 6 亿行表,B-tree 索引 12GB,BRIN 只要 5MB

-- 7. 找无用索引(删掉省空间 + 加速写入)
SELECT
    schemaname, relname, indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 50            -- 几乎没用过
ORDER BY pg_relation_size(indexrelid) DESC;

-- 8. 找重复索引
SELECT indrelid::regclass, array_agg(indexrelid::regclass)
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

修复 3:参数调优

# postgresql.conf(64 核 / 256GB 机器)

# === 内存 ===
shared_buffers = 64GB              # 物理内存 25%
effective_cache_size = 192GB       # 物理内存 75%(优化器估算用)
work_mem = 256MB                   # 单次排序/hash 内存(注意:并发会乘)
maintenance_work_mem = 4GB         # VACUUM/CREATE INDEX 用
huge_pages = try                   # 大页内存

# === WAL / checkpoint ===
wal_buffers = 64MB
max_wal_size = 32GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9 # checkpoint 平滑写入
checkpoint_timeout = 15min

# === 并发 ===
max_connections = 300              # 不要太大,用连接池
max_worker_processes = 64
max_parallel_workers = 32
max_parallel_workers_per_gather = 8 # 单查询最多 8 并行
max_parallel_maintenance_workers = 8

# === 查询规划 ===
random_page_cost = 1.1             # SSD 设接近 1(默认 4 是机械盘)
effective_io_concurrency = 256     # SSD 高并发 IO
default_statistics_target = 200    # 统计信息精度(默认 100)

# === autovacuum(关键,防表膨胀)===
autovacuum = on
autovacuum_max_workers = 8
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05   # 5% dead tuple 就触发(默认 20%)
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 4000     # 提高 vacuum 速度

# === 大表单独设 autovacuum ===
# ALTER TABLE t_order SET (
#   autovacuum_vacuum_scale_factor = 0.01,
#   autovacuum_vacuum_cost_delay = 0
# );

# === 日志 ===
log_min_duration_statement = 1000  # 记录 > 1s 的慢查询
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0                 # 记录所有临时文件(work_mem 不够的信号)

# === 超时保护 ===
statement_timeout = 30s            # 查询最长 30s
idle_in_transaction_session_timeout = 60s  # 事务空闲 60s 杀掉
lock_timeout = 10s

修复 4:分区表(超大表)

-- t_order 6 亿行,按月分区
-- 1. 创建分区主表
CREATE TABLE t_order (
    id          bigint NOT NULL,
    order_no    varchar(32) NOT NULL,
    user_id     bigint NOT NULL,
    status      varchar(16) NOT NULL,
    amount      numeric(12,2),
    created_at  timestamptz NOT NULL,
    PRIMARY KEY (id, created_at)        -- 分区键必须在主键里
) PARTITION BY RANGE (created_at);

-- 2. 创建月分区
CREATE TABLE t_order_2024_05 PARTITION OF t_order
    FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');
CREATE TABLE t_order_2024_06 PARTITION OF t_order
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

-- 3. 每个分区单独建索引
CREATE INDEX ON t_order_2024_05 (status, created_at);
CREATE INDEX ON t_order_2024_05 (user_id);

-- 4. 自动创建分区(pg_partman 扩展)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table => 'public.t_order',
    p_control => 'created_at',
    p_interval => '1 month',
    p_premake => 3                     -- 提前建 3 个月分区
);

-- 5. 分区裁剪验证(查询只扫相关分区)
EXPLAIN
SELECT * FROM t_order
WHERE created_at >= '2024-05-15' AND created_at < '2024-05-20';
-- 计划只出现 t_order_2024_05,其他分区被裁剪

-- 6. 老数据归档(直接 detach 分区,秒级)
ALTER TABLE t_order DETACH PARTITION t_order_2024_01;
-- 归档到冷库或直接 DROP TABLE t_order_2024_01;

-- 效果:
-- - 查询自动分区裁剪,只扫 1 个月数据
-- - 老数据归档秒级完成(不用 DELETE 6 亿行)
-- - VACUUM 按分区进行,不锁全表
-- - 单分区索引小,维护快

修复 5:连接池(PgBouncer)

# 问题:每个业务实例连 PG,500 个连接全占满
# PG 连接很重(每连接 ~10MB + fork 进程)
# 解决:PgBouncer 连接池

# pgbouncer.ini
[databases]
orderdb = host=10.0.1.1 port=5432 dbname=orderdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

# 池模式:transaction(事务级复用,最高效)
pool_mode = transaction

# 连接池大小
max_client_conn = 10000            # 接受客户端连接 1w
default_pool_size = 50             # 每 db 到 PG 只用 50 连接
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3

# 超时
server_idle_timeout = 600
query_timeout = 30
client_idle_timeout = 0

# server 连接生命周期
server_lifetime = 3600
server_connect_timeout = 5

# 效果:
# - 业务侧 1w 连接 → PG 实际只 50 连接
# - PG 内存压力骤降
# - 连接建立从 ~5ms 降到 < 0.1ms(池复用)

# 业务侧连接串改成连 PgBouncer
# jdbc:postgresql://pgbouncer-host:6432/orderdb

# 注意:transaction 模式下不能用
# - 会话级特性(SET / prepared statement 跨事务)
# - 用 prepared statement 的话改 statement 模式或应用层禁用

修复 6:VACUUM + 表膨胀治理

-- 1. 查表膨胀(dead tuple 比例)
SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC;

-- 2. 手动 VACUUM(高 dead tuple 表)
VACUUM (VERBOSE, ANALYZE) t_order;

-- 3. 表/索引膨胀严重时:重建(在线)
-- pg_repack 扩展(不锁表重建)
-- $ pg_repack -d orderdb -t t_order --jobs 4

-- 不用 VACUUM FULL(锁全表,生产禁用)

-- 4. 长事务监控(autovacuum 杀手)
SELECT
    pid,
    now() - xact_start AS xact_age,
    state,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_age DESC;
-- 长事务会让 VACUUM 无法回收 dead tuple

-- 5. 索引膨胀重建
REINDEX INDEX CONCURRENTLY idx_order_status_created;

-- 6. 更新统计信息(执行计划变差时)
ANALYZE t_order;
-- 大促数据分布突变后,统计信息过时会选错计划

-- 7. 监控 XID 回卷(autovacuum 没跟上的极端情况)
SELECT
    datname,
    age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
-- xid_age 接近 2 亿要警惕,接近 20 亿数据库会停摆

优化效果

指标                  优化前         优化后
=========================================================
主库 CPU              95%+           40%
慢查询(> 1s)        500/min        < 5/min
订单查询 P99          5s             80ms
JOIN 查询             Nested Loop 8s Hash Join 120ms
连接数                500(打满)     50(PgBouncer 后端)
                     业务侧 1w 客户端连接
表膨胀(dead tuple)  40%            < 5%
最大表扫描            Seq Scan 6 亿  分区裁剪 + Index Scan
归档 1 个月数据        DELETE 数小时  DETACH 秒级

成本:
- 主库未扩容,通过治理释放性能
- 原计划升配 128 核(+30w/年)取消
- 从库压力同步下降

业务影响:
- 促销活动数据库零事故
- 接口超时消失,SLO 达标
- 财务报表查询(大范围)可用
- DBA 不再被慢查询告警淹没

避坑清单

1. pg_stat_statements 必开,先找 Top SQL 再优化
2. EXPLAIN ANALYZE 看真实执行,关注 Seq Scan / Rows Removed
3. 建索引用 CONCURRENTLY,不锁表;复合索引注意列顺序
4. SSD 必须 random_page_cost=1.1(默认 4 会让优化器拒绝索引)
5. work_mem 注意并发会乘,不要设太大撑爆内存
6. autovacuum_vacuum_scale_factor 大表调到 0.01-0.05
7. 超大表用分区表,分区裁剪 + DETACH 秒级归档
8. PgBouncer transaction 模式,业务千连接压成几十个
9. statement_timeout + idle_in_transaction_session_timeout 必设
10. 禁用 VACUUM FULL(锁表),用 pg_repack 在线重建

总结

PostgreSQL 慢查询治理是个"测量—索引—参数—结构"的递进过程。最大的认知改变:random_page_cost 这个参数太关键了,默认值 4 是为机械硬盘设计的,SSD 时代不改成 1.1,优化器会系统性地"嫌弃"索引、偏爱全表扫描,一个参数就能让全库查询计划恶化。最被低估的是分区表,6 亿行的大表不分区,VACUUM 锁全表、归档要 DELETE 几小时、查询扫全表;按月分区后,查询自动裁剪只扫一个月,归档变成秒级 DETACH,运维难度断崖式下降。最容易踩的坑是 idle in transaction 长事务,业务代码忘了提交事务,这个连接会一直占着,更要命的是它让 autovacuum 无法回收 dead tuple,表膨胀越来越严重 —— idle_in_transaction_session_timeout 必须设。最后,PgBouncer 是 PG 高并发的标配,PG 的连接是重量级的(独立进程 + 10MB 内存),业务侧动辄上千连接直接打爆数据库,transaction 模式的连接池能把它压缩到几十个,这一步几乎零成本却收益巨大。

—— 别看了 · 2026