DAY 43 W7 · 大促备战：优化实战与工程化

不能靠用户投诉才知道慢：排查方法论

大促前第一件事：建立慢查询的发现与排查机制。

学 40 min
练 50 min
盘 30 min
共 120 分钟

学 · 40 min

  1. 01五步法：定位 -> 分析 -> 假设 -> 验证 -> 回归

    场景用户说「后台很卡」--从一句抱怨到一条被治好的 SQL，中间是可复现的流程。

    ① 定位：哪条慢？（pg_stat_statements / 慢日志，拿语句拿数字）；② 分析：EXPLAIN ANALYZE 找瓶颈节点（W6 的功夫）；③ 假设：缺索引？统计过期？写法失效？一次只验证一个；④ 验证：改一处、重测、对比；⑤ 回归：确认没伤到别的查询，把前后数据存档。优化是流程，不是灵感。

    -- 定位：TOP 10 慢语句
select calls, round(mean_exec_time::numeric,1) as 平均ms, left(query,70)
from pg_stat_statements order by mean_exec_time desc limit 10;

    易错跳过「分析」直接加索引是玄学优化：碰巧好了也不知道为什么，下次照样抓瞎。

  2. 02log_min_duration_statement 慢日志配置

    场景没有 pg_stat_statements 的环境（或要抓「原始完整语句」），慢日志是底线配置。

    设成 200（毫秒）：执行超过 200ms 的语句连原文带耗时自动进日志。改它不用重启：alter system + pg_reload_conf() 即可。日志里能拿到带字面量的完整 SQL（pg_stat_statements 是归一化后的模板），这是两者互补的点。

    alter system set log_min_duration_statement = 200;
select pg_reload_conf();          -- 不重启生效

show log_min_duration_statement;  -- 验证：200ms

    易错阈值太小日志爆炸（磁盘被拖垮），太大漏网；200ms~1s 是常见起步档，按业务调。

  3. 03auto_explain 模块自动记录慢查询计划

    场景慢查询是「偶发」的：事后手动 EXPLAIN，它偏偏又快了--你需要案发当时的计划。

    auto_explain 预载后，超过阈值的语句执行时自动把执行计划写进日志。它抓的是现场：当时的统计信息、当时的缓存状态、当时的计划。偶发慢查询（plan 抖动、冷缓存）只有它抓得住。

    -- 会话级试开（不用重启）：
set session_preload_libraries = 'auto_explain';
set auto_explain.log_min_duration = '500ms';
set auto_explain.log_analyze = on;      -- 真实执行数据（有开销，谨慎）

-- 跑一条慢查询，然后看日志里有完整计划

    易错log_analyze = on 会给被记录的语句加额外开销，生产常开要评估；先只开 log_min_duration 版本。

  4. 04优化前必须确认的三件事：数据量、频率、可接受延迟

    场景三个优化候选摆在面前，先做哪个？不是哪个慢做哪个。

    ① 数据量：多大的表、返回多少行；② 频率：一天一次的报表还是每秒 100 次的接口；③ 可接受延迟：报表 1 分钟无妨，交易接口 200ms 是红线。优先级 = 频率 × 收益空间。每天跑一次的 30 秒报表，优化的性价比是零。

    -- 频率证据：这条查询被调了多少次
select calls, round(total_exec_time::numeric) as 总耗时ms
from pg_stat_statements
order by calls desc limit 10;

    易错只按「绝对耗时」排优先级是错的：一条 5 秒但每天一次的查询，排不进前三优先级。

练 · 50 min

  1. 把慢日志阈值设为 200ms 并重载配置
    参考答案

    alter system 写的是 postgresql.auto.conf，重载即生效。日志默认打到容器 stdout，docker logs pg16 里就能翻到。

    alter system set log_min_duration_statement = 200;
select pg_reload_conf();           -- 不重启生效

show log_min_duration_statement;   -- 验证：200ms
  2. 故意跑几条慢查询，从日志里捞出来
    参考答案

    预期：日志里每条形如「duration: 800.123 ms statement: select ...」，带完整原文和真实耗时。这是慢日志相对 pg_stat_statements 的独特价值：拿到带字面量的现场原文。

    -- 故意慢的三条（在百万行库上都远超 200ms）
select count(*) from orders;
select count(distinct user_id) from orders;
select o.user_id, sum(i.qty * i.unit_price)
from orders o join order_items i on i.order_id = o.id
group by 1 order by 2 desc limit 10;
  3. 开启 auto_explain，看它记录的计划
    参考答案

    预期：日志里出现以 QUERY 开头的完整计划（带 actual time）。它抓的是「案发当时」的计划--偶发慢查询（冷缓存、计划抖动）只有这招能留现场。会话级设置断开连接即失效。

    set session_preload_libraries = 'auto_explain';
set auto_explain.log_min_duration = '100ms';
set auto_explain.log_analyze = on;      -- 有额外开销，练习环境随便开

-- 再跑一条慢查询，然后去日志里找执行计划
select count(*) from orders o
join order_items i on i.order_id = o.id;
  4. 画出属于你自己的排查流程图（一页纸）
    参考答案

    一页纸流程图：用户抱怨/监控告警 -> 定位（pg_stat_statements 按总耗时排 TOP、慢日志拿原文）-> 分析（explain analyze，圈出最贵节点）-> 假设（一次只立一个：缺索引/统计过期/写法失效/数据量变了）-> 验证（只改这一处，重测对比）-> 回归（确认别的查询没被伤到，前后数据存档进文档）。面试就照着这张图讲。

  5. 写下「优化前必须先确认的 3 个问题」
    参考答案

    三问：① 数据量--表多大、返回多少行（pg_class.reltuples 或 \dt+ 看体积）；② 频率--一天一次还是每秒上百次（pg_stat_statements 的 calls 列）；③ 可接受延迟--业务红线是多少（报表分钟级可忍，交易接口 200ms 封顶）。优先级 = 频率 × 收益空间：每天跑一次的 30 秒报表，优化性价比是零。

过关标准 有一张能对着面试官讲的排查流程图。