📚 课程目录第2章业务负载画像

2. 业务负载画像

性能优化最忌讳凭直觉猜瓶颈。业务负载画像的目标，是用数据回答三个问题：生产库上到底在跑什么 SQL？哪些 SQL 出现得最频繁？哪些 SQL 单次或累计消耗的时间最多？只有把"高频"和"高耗时"这两类查询区分开，后续优化才有明确靶点。本节围绕 PostgreSQL 自带的 pg_stat_statements 扩展与日志分析两条主线展开。

一、pg_stat_statements：SQL 级统计的核心工具

pg_stat_statements 是 PostgreSQL 官方 contrib 扩展，它按"归一化后的 SQL 语句"聚合执行次数、耗时、行数、缓存命中等指标。所谓归一化，是把语句中的常量替换为占位符，例如 SELECT * FROM orders WHERE user_id = 123 与 ... user_id = 456 会被归并为同一条 SELECT * FROM orders WHERE user_id = $1，从而避免统计被海量常量值打散。

启用分两步。先在 postgresql.conf 中加载扩展并设置采样上限：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000          # 最多跟踪的归一化语句数
pg_stat_statements.track = top          # top 只跟踪顶层语句，all 含嵌套
pg_stat_statements.track_utility = on   # 是否统计 DDL/DCL 等工具类语句

修改后需重启实例，再在目标库中执行 CREATE EXTENSION pg_stat_statements;。注意 pg_stat_statements.max 是硬上限，超出后新语句会被忽略，因此生产环境建议按业务复杂度设为 5000～20000。

采集时最常用的查询如下，它按总耗时排序，直接暴露"累计成本最高"的语句：

SELECT
  calls,
  round(total_exec_time)::bigint        AS total_ms,
  round(mean_exec_time, 2)              AS mean_ms,
  rows,
  round(100.0 * shared_blks_hit
        / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
  left(query, 80)                       AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

这里要区分几个关键字段：calls 是执行次数，代表"高频"；total_exec_time 是累计执行时间，代表"总成本"；mean_exec_time 是平均单次耗时，代表"单次高耗时"；shared_blks_hit/read 反映缓存命中情况。一条语句可能调用百万次、单次仅 0.1ms，累计却占满 CPU；另一条只调用几十次、单次却要 3 秒。两者优化手段完全不同，必须分开看。

统计自上次 pg_stat_statements_reset() 或实例启动以来累积。做负载画像前先重置，让数据覆盖一个完整业务周期（如 24 小时或一个结算日），结论才可靠。

二、从统计到画像：四象限分类

把 calls 与 mean_exec_time 作为两个维度，可以把 SQL 分成四类，这是负载画像最实用的判断框架。下图展示了这一分类逻辑与对应的处理方向。

实际判断时，可先按 total_exec_time 降序取前 20，再对每条语句看 calls 与 mean_exec_time 落在哪个象限。右上象限（高频且高耗时）应最先处理；左上象限（低频高耗时）虽不频繁，但可能造成偶发超时，需结合业务时段判断；右下象限（高频低耗时）单条不慢，但调用量放大后可能吃满 CPU，优化方向是减少调用次数或引入缓存，而非改 SQL 本身。

三、日志分析：捕捉统计之外的细节

pg_stat_statements 给出的是聚合视图，看不到具体某次执行的参数值、执行时刻和客户端来源。日志正好补上这块。开启慢查询日志：

log_min_duration_statement = 500ms   # 超过 500ms 的语句记入日志
log_line_prefix = '%m [%p] %u@%d '   # 时间、PID、用户、库名
log_duration = off                   # 避免与上面重复记录
log_statement = 'none'               # 生产环境不要设 all，日志量过大

日志中每条慢 SQL 会带时间戳、PID、用户和库名，配合 auto_explain 还能自动记录执行计划：

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1s
auto_explain.log_analyze = on
auto_explain.log_buffers = on

这样超过 1 秒的语句会连同实际执行计划、各节点耗时和缓冲区读写一起落盘，非常适合定位"为什么这条 SQL 慢"。日志分析常用手段是用 pgbadger 汇总，或直接 grep 统计：

# 统计出现次数最多的慢 SQL 模板（示例）
grep 'duration:' postgresql-*.log \
  | sed 's/[0-9]\+/N/g' \
  | sort | uniq -c | sort -rn | head -20

日志与 pg_stat_statements 是互补关系：前者提供带时间、来源、执行计划的明细，后者提供稳定的聚合排序。生产环境通常两者同时开启，用统计表定优先级，用日志查具体案例。

四、采集流程与注意事项

一个可复用的采集流程是：先 SELECT pg_stat_statements_reset(); 清零，让统计覆盖一个完整业务周期；周期结束后导出按 total_exec_time 排序的 Top 50，同时收集该时段的慢查询日志；把两者按归一化 SQL 对齐，标注每条语句的象限归属和业务来源；最后形成一份"待优化 SQL 清单"，包含语句样本、调用次数、平均耗时、累计耗时、命中率和初步判断。

采集本身有成本。pg_stat_statements.track = all 会跟踪嵌套语句，开销明显上升；log_min_duration_statement 设得过低（如 0）会写爆磁盘。生产环境建议先设 500ms～1s，观察日志增长速度后再调整。另外统计信息在实例重启后清零，跨重启对比需借助外部采集或监控系统持久化。

完成负载画像后，你会得到一张按成本排序的 SQL 地图。下一步才是针对具体语句分析执行计划、调整索引或改写查询。没有画像就动手优化，往往是在解决一个并不存在的瓶颈。