DAY 42 W6 · 两年之后：索引与执行计划实战

优化实战：救活三条慢查询

交付日：从 pg_stat_statements 里挑出最慢的三条，把它们救活，写成可以进简历的案例。

学 10 min

练 85 min

盘 25 min

共 120 分钟

完成这一天

学 · 10 min

01优化前先记录基线：耗时、执行计划、返回行数--没有基线，优化后说不出「快了多少倍」

练 · 85 min

从 pg_stat_statements 挑出 3 条秒级查询作为目标

参考答案

优先挑「总耗时高 + 平均耗时秒级」的：总耗时高说明它真的在吃数据库，优化它收益最大。没跑过几条查询榜单是空的，先把本周的练习各跑几遍。

-- 前提：D41 已装好 pg_stat_statements（preload + 重启 + create extension）
select calls,
       round(total_exec_time::numeric, 0) as 总ms,
       round(mean_exec_time::numeric, 1)  as 平均ms,
       rows,
       left(query, 70) as query
from pg_stat_statements
where mean_exec_time >= 1000        -- 平均耗时 ≥1 秒才算「秒级」
order by total_exec_time desc
limit 10;

逐条记录优化前的 EXPLAIN ANALYZE 和耗时
参考答案

基线三件套一个都不能少：耗时（\timing / Execution Time）、执行计划、返回行数--没有基线，优化完说不出「快了多少倍」，这是本日铁律，也是过纲里「简历素材」的数字来源。
```
\timing on

-- 例：近 30 天日报（换成你自己挑的目标查询）
explain (analyze, buffers)
select created_at::date as 日期, count(*) as 单数, sum(total_amount) as gmv
from orders
where created_at >= current_date - 30
group by 1;
```

提出假设 -> 加索引或改写 SQL -> 验证

参考答案

方法论：一次只改一个变量（只加一个索引或只改一处写法），改完立刻重新 explain--同时动两处就说不清是哪个手段起的作用。假设不成立就回滚（drop index / 还原 SQL），再提下一个假设。

-- 假设示例 A：「某用户的某时间段」没索引 -> 建复合索引（等值在前、范围在后）
create index idx_orders_uid_created on orders (user_id, created_at);

-- 假设示例 B：where date(created_at) = ... 函数包列失效 -> 改左闭右开范围

-- 验证：同一个查询重跑
explain (analyze, buffers)
select * from orders
where user_id = '00000000-0000-0000-0000-000000000042'
  and created_at >= current_date - 30;

记录优化后的计划与耗时，算出提升倍数
参考答案

提升倍数 = 优化前耗时 ÷ 优化后耗时，用 Execution Time 算，别用 cost（它不是秒）。注意缓存影响：第一次可能带冷读，多跑两三次取稳定值再记录。
```
\timing on

-- 与基线一模一样的查询再跑一次
explain (analyze, buffers)
select created_at::date as 日期, count(*) as 单数, sum(total_amount) as gmv
from orders
where created_at >= current_date - 30
group by 1;
```
整理成对比表格：查询 | 优化前 | 优化后 | 手段 | 提升倍数

参考答案

表格列就用题面那五列：查询 | 优化前 | 优化后 | 手段 | 提升倍数。示例一行：「近 30 天日报 | ≈1s | ≈100ms | created_at 加索引 | ≈10×」。每行都要能附上优化前后的执行计划--面试官追问「为什么快了」，拿计划说话，不拿感觉说话。

过关标准 三条查询都有明确提升倍数，且能解释每一条为什么快了。这是简历上可以直接写的东西。

今日复盘

D+1 默写 D41 加了索引却没被用：失效场景与统计信息

逐个复现 6 种失效场景，每种都保存失效前后的执行计划

参考答案

psql 里 \o 文件名可以把每份计划存档，失效 / 命中各存一份。六类里只有①②④是「写法病」要治，⑤是优化器的正确判断，治了反而慢。

-- ① 函数包列：失效 vs 命中
explain select * from orders where date(created_at) = current_date - 1;
explain select * from orders
where created_at >= current_date - 1 and created_at < current_date;
-- ② 列被套 cast（PG 里「隐式转换失效」的实际形态）
explain select * from orders
where user_id::text = '00000000-0000-0000-0000-000000000042';
-- ③ 前导 %
explain select * from products where name like '%42';
-- ④ OR 的两半不都有索引（total_amount 没索引）
explain select * from orders
where user_id = '00000000-0000-0000-0000-000000000042' or total_amount > 1900;
-- ⑤ 低选择性：命中 ≈80% 行，Seq 是正确决策
explain select * from orders where status = 2;
-- ⑥ 排序键不在索引里：多出 Sort 节点
explain select * from orders
where user_id = '00000000-0000-0000-0000-000000000042'
order by total_amount;

D+3 重做 D39 读懂体检报告：EXPLAIN ANALYZE

挑 5 条查询做 EXPLAIN (ANALYZE, BUFFERS)，逐行写注释

参考答案

注释从缩进最深的节点往上写：叶子扫描走没走索引 -> 连接怎么组织 -> 顶层排序聚合。每个节点记三样：actual time、rows= vs actual rows、buffers。瓶颈 = actual time 最大的一枝。

explain (analyze, buffers)
select * from orders where user_id = '00000000-0000-0000-0000-000000000042';

explain (analyze, buffers)
select count(*) from orders where status = 2;

explain (analyze, buffers)
select * from orders order by created_at desc limit 10;

explain (analyze, buffers)
select u.city, count(*), sum(o.total_amount)
from orders o join users u on u.id = o.user_id
group by u.city;

explain (analyze, buffers)
select * from orders o join payments p on p.order_id = o.id
where o.total_amount > 1900;

D+7 口头解释 D35 秒杀预演：锁与死锁 + 周复盘

行锁与表锁的模式与兼容矩阵

易错点

PG 的行锁信息在内存里、不落盘，崩溃恢复不需要修补行锁--跟「锁存在数据页里」的数据库不同，冷知识但面试官爱听。