场景 用户说「后台很卡」--从一句抱怨到一条被治好的 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;

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

场景 没有 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 是常见起步档，按业务调。

场景 慢查询是「偶发」的：事后手动 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 版本。

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

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

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

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

场景 深分页优化的第一步不是改分页，是先把 select * 扫掉。

讲解 ① 传输：列表页要 20 行 8 列，select * 把整行几十列全传；② 回表：所有列都要回堆取，Index Only Scan 的机会被掐死；③ 扩展性：表加一列，这个接口的流量悄悄变大。只取需要的列是成本最低的优化。

-- 反面
select * from orders order by created_at desc offset 100000 limit 20;
-- 正面
select id, user_id, status, total_amount, created_at
from orders order by created_at desc offset 100000 limit 20;

易错 select * 还会破坏 prepare 语句的缓存效果（结构一变计划全重算）。

场景 「先 join 宽表再过滤」和「先过滤再 join」结果一样、速度差十倍。

讲解 数据越早变小，后面每一步越便宜：过滤条件尽量推到扫描层（走索引），聚合前先收窄范围。深分页的「延迟关联」就是它的应用：第一步只在索引里拿 20 个 id（窄而快），第二步再拿 id 回表取全部列。

-- 延迟关联：先 id 后详情
select o.*
from (
  select id from orders
  where user_id = '...'
  order by created_at desc
  offset 100000 limit 20        -- 只在索引层翻页
) page
join orders o on o.id = page.id;   -- 20 次回表

易错 外层的排序和过滤条件别丢：内层不排序，外层顺序就是乱的。

场景 第 1 页 5ms，第 5000 页 8 秒--OFFSET 的工作方式决定了它。

讲解 offset 100000 limit 20 的执行 = 扫过前 100000 行、全部丢掉，再取 20 行。翻得越深，丢弃越多，耗时线性增长。OFFSET 的本意是「跳过 N 行」，而跳过也是要一行行数过去的。

-- 耗时曲线实验
\timing on
select id from orders order by id offset 0       limit 20;
select id from orders order by id offset 10000   limit 20;
select id from orders order by id offset 500000  limit 20;
-- 记录三档耗时，画出来就是一条上升直线

易错 愿意翻到 5000 页的多半是爬虫不是用户--限页深 + 返回条数上限，本身就是防爬设计。

场景 治本方案：让「下一页」的代价和「第一页」一样。

讲解 记住上一页最后一行的排序键，下一页从它接着取：where (排序键) < :last_value order by 排序键 desc limit 20。无论第几页，都只扫 20 行。排序键必须唯一且有序（复合排序加 id 兜底），否则边界行会漏或重。

-- 下一页：拿着上一页最后的 (created_at, id) 来
select id, created_at, total_amount
from orders
where (created_at, id) < (:last_created_at, :last_id)   -- 行比较语法
order by created_at desc, id desc
limit 20;

易错 限制要主动说：不能随机跳页（只支持上一页 / 下一页）、排序键中途不能变。产品要跳页就和「延迟关联」组合用。

场景 列表页要显示「共 1,234,567 条」--这个数字本身就要扫几秒。

讲解 PG 的 count(*) 要真数（MVCC 下不能用索引元数据偷懒），百万级秒起。替代：① pg_class.reltuples 估算（毫秒级，误差百分之几）；② 计数表 + 触发器维护（精确、有写放大）；③ 前端展示「约 123 万条」。绝大多数列表页，用户根本不在乎精确总数。

-- 估算版（毫秒）
select reltuples::bigint from pg_class where relname = 'orders';
-- 精确版（慢）
select count(*) from orders;

易错 reltuples 依赖统计新鲜度（D41）；号称「精确」的功能页（对账、结算）别用估算。

场景 两年 24 个月的订单挤在一张表里--按月切开后，每月一张「小表」。

讲解 PG 10+ 的声明式分区：父表带 partition by range/list/hash，子表带 for values ... 定义归属。range 按值区间（时间分区本命）；list 按枚举值（按城市、按租户）；hash 散列均衡（无明显查询边界时摊数据）。对查询和 DML 而言父子表是一张表，写入自动路由到子分区。

create table orders_p (
  id uuid not null,
  created_at timestamp not null,
  ...
  primary key (id, created_at)          -- 分区键必须进主键
) partition by range (created_at);

create table orders_p_2026_08 partition of orders_p
  for values from ('2026-08-01') to ('2026-09-01');

易错 分区键必须是主键 / 唯一键的一部分--「按月分区但主键只有 id」建不起来，这是第一个坑。

场景 分区表凭什么快：查询条件包含分区键时，别的分区碰都不碰。

讲解 「分区裁剪」= 优化器按 WHERE 里的分区键排除无关分区。常量条件在计划期裁剪（explain 直接只列一个分区）；「参数化条件」要到执行期裁剪（计划里显示全部分区但实际只扫一个，看 analyze 的 actual）。验证永远用 explain analyze 的 actual 行数。

explain select count(*) from orders_p
where created_at >= '2026-08-01' and created_at < '2026-09-01';
-- Subplans Only / 只出现一个分区 = 裁剪生效

-- 条件不含分区键 = 全分区扫（24 个分区全摸一遍）
explain select count(*) from orders_p where total_amount > 1000;

易错 对分区键套函数（date_trunc(created_at)）会让裁剪失效--和索引失效同一个道理。

场景 「该不该分区、按什么分」的决策框架。

讲解 选键原则：高频查询条件里出现的那一列（时间是最常见的）。代价照单全收：① 跨分区查询 / 不含分区键的查询反而变慢；② 全局唯一约束做不了（唯一键必须含分区键）；③ 分区键定了一般改不了；④ 运维复杂度（几百个子表的管理）。索引能解决的性能问题不要用分区解决。

-- 分区数量也要克制：几百个分区后规划开销开始可感知
select count(*) from pg_inherits
where inhparent = 'orders_p'::regclass;

易错 为了「显得专业」上分区、按 hash 分 16 区但查询从不带分区键 = 白白放大了复杂度。

场景 大促前要在百万行表上补索引--普通建索引期间全表写入被锁。

讲解 普通 create index 拿排他锁，建完才放行写入；concurrently 分两趟扫表、不阻塞 DML，代价是耗时更长、不能在事务里跑。生产大表加索引的默认姿势。

-- 普通版：锁写直到完成
create index idx_created on orders_p (created_at);

-- 并发版：写入不受阻（另开窗口验证）
create index concurrently idx_created on orders_p (created_at);
-- 别忘了：不能包在 begin...commit 里

易错 concurrently 中途失败会留下 invalid 索引（占空间不可用），要 drop 掉重建：查 pg_index 的 indisvalid 列。

场景 一条 UPDATE 把 200 万行历史数据打标--事务日志爆炸、复制延迟、长锁。

讲解 单个巨型事务的三重罪：WAL 洪峰（磁盘和复制跟不上）、锁持有全程（别人排队）、失败回滚代价同样是巨型。分批：每批 1 万行、循环提交，每批影响可控，失败从断点重跑。注意批间会有别的事务插进来，过滤条件要幂等（每批重新筛选还没处理的行）。

do $$
declare n int := 1;
begin
  while n > 0 loop
    with 批 as (
      select id from orders_p
      where status is null
      limit 10000
      for update skip locked
    )
    update orders_p o
    set status = 0
    from 批 where 批.id = o.id;
    get diagnostics n = row_count;   -- 没得改了就停
  end loop;
end $$;

易错 「一次更新所有行」的失败重试等于再来一遍全量；分批 + 幂等筛选才有断点续跑能力。

场景 老板的仪表盘每次打开都实时聚合百万行--先把「视图」和「物化视图」分清楚。

讲解 视图 = 存起来的一条查询，每次 select 都重新执行（不省任何计算，价值在封装和安全）；物化视图 = 查询结果落盘，查它就是查表，毫秒级。代价：数据是快照，必须定期 REFRESH 才更新。仪表盘这种「容忍分钟级延迟、查询巨重」的场景是物化视图的本命。

create view v_monthly as
select date_trunc('month', created_at) as 月份, count(*), sum(total_amount)
from orders group by 1;                    -- 每次都实时算

create materialized view mv_monthly as
select date_trunc('month', created_at) as 月份, count(*), sum(total_amount)
from orders group by 1;                    -- 算一次存下来

refresh materialized view mv_monthly;      -- 手动更新快照

易错 视图改名/换定义不影响底表；但「以为视图能提速」是最常见误解--它是查询的别名，不是缓存。

场景 普通 REFRESH 期间仪表盘直接查询报错/排队--大促时这不能忍。

讲解 普通 refresh 拿排他锁：刷新期间读也被挡。concurrently 版对比新旧结果、增量替换，刷新期间可读。代价和前提各一：刷新本身更慢；物化视图上必须有唯一索引。

create unique index on mv_monthly (月份);   -- 前提

refresh materialized view concurrently mv_monthly;
-- 另一个窗口此刻查询 mv_monthly 不受阻塞

易错 没有唯一索引时用 concurrently 直接报错--这是「为什么刷新锁表」排查时最先查的一项。

场景 刷新多勤？这是个业务问题不是技术问题。

讲解 刷新 = 全量（或增量）重算：表越大刷新越贵，还占双倍空间（新旧快照切换）。决策框架：业务能容忍多旧的数据？分钟级延迟换十倍查询提速，绝大多数报表都愿意。定时刷新（pg_cron / crontab）+ 查询时标注「数据截至 HH:MM」是标准交付形态。

-- 看物化视图多大、上次刷新何时（自己维护一列刷新时间也行）
select relname, pg_size_pretty(pg_relation_size(relname::regclass))
from pg_class where relname = 'mv_monthly';

易错 对「必须实时」的数据上物化视图是方向错误--那该做的是优化查询本身或上缓存层。

场景 运营要查数据，总不能把超级用户密码给他。

讲解 create role readonly nologin 建角色，grant select on 表 to readonly 授权，再把登录账号（role login + 密码）加进角色。最小权限原则：每个账号的权限刚好够干自己的活、多一点都不给--运营只读，分析只读几张表，写入只走应用账号。

create role readonly nologin;
grant usage on schema public to readonly;
grant select on all tables in schema public to readonly;

create role ops_report login password '...';
grant readonly to ops_report;    -- 运营账号继承只读权限

-- 验证越权：用 ops_report 连上后
-- update orders set status = 1;   -- ERROR: permission denied

易错 新表不会自动继承 grant（除非 alter default privileges）--「加了张表运营看不到」先查这个。

场景 「每个用户只能看自己的订单」--不用改任何查询，数据库层强制。

讲解 RLS 给表挂策略：alter table ... enable row level security 开关 + create policy ... using (...) 定义哪些行可见。开启后，普通角色查这张表自动被策略过滤（比如 user_id = current_setting(...)）。多租户 SaaS 的标配防线。

alter table orders enable row level security;

create policy own_orders on orders
  using (user_id = current_setting('app.user_id')::uuid);
-- 会话里 set app.user_id = '...' 后，select 自动只见自己的单

易错 表的 owner 默认绕过 RLS--要真拦住自己得 force row level security；应用连接千万别用 owner 账号。

场景 后端代码评审，四大常客一个不落。

讲解 ① N+1：取 100 个订单再循环逐个查用户 = 101 次往返，改成 join 或 where id = any(数组) 一次取回；② select *（D44 讲过三宗罪）；③ 大事务：事务里夹外部 HTTP 调用，锁陪你等超时；④ 无限制 IN 列表：塞一万个 id 进 in (...)，解析慢、计划烂，改 values join / 临时表 / 数组。

-- N+1 的解药：一次取回
select o.*, u.name from orders o join users u on u.id = o.user_id
where o.id = any($1::uuid[]);    -- 数组参数版，替代 in (一万个字面量)

易错 N+1 的识别：应用日志里短时间海量相同模板语句；ORM 的懒加载是重灾区，列表场景要显式预取。

场景 另外两个慢性病：一个杀索引，一个悄悄膨胀。

讲解 ① 隐式转换：varchar 列 = 整数，索引失效（D41）；② 软删除：全表 update 置 is_deleted，看似温柔实则：死元组暴涨（vacuum 压力）、表和索引双膨胀、每条查询都要带过滤条件（漏写就是事故）。历史数据定期归档硬删（D45 的分区 detach）才是正解。

-- 软删除的膨胀体检
select n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 1) as 死活比
from pg_stat_user_tables where relname = 'orders';

易错 软删除不是免费开关：它的账单（膨胀 + 遗忘过滤条件 + 索引全量变大）往往在半年后才寄到。

场景 「为什么 PG 的 count 这么慢，MySQL 不是挺快？」--考点。

讲解 PG 的 count 必须逐行检查可见性（MVCC：每行版本对不同事务可见性不同，索引里没有这个信息），所以再好的索引也只能加速定位、不能免检。MySQL 的 MyISAM 引擎表头存了行数所以快，但那是「不支持事务的快」，InnoDB 同样要数。方案：reltuples 估算 / 计数表 / 业务上接受「约 N 条」（D44 详述）。

explain analyze select count(*) from orders;
-- 计划再优也是全量可见性检查：这就是它的成本下限

易错 「MySQL count 快 PG 慢所以 PG 不行」是拿古董引擎（MyISAM）比的错觉，面试要能拆穿。

场景 评审里最「聪明」的两个设计，往往最贵。

讲解 ① EAV（实体-属性-值三列表）：想存任意自定义属性。代价：查一个完整对象要 pivot 出几十个 join、类型约束丢失（value 列只能用 text）、无法建外键。真有半结构化需求，PG 的 jsonb 是更体面的答案。② 拿表当队列（状态列 + 轮询）：并发抢占有竞态，必须 for update skip locked（D35 的方案）；撑得住小规模，量大还是上消息中间件。

-- EAV 的日常：想拿「颜色」就得 pivot
select e.id,
       max(case when a.attr = '颜色' then a.value end) as 颜色,
       max(case when a.attr = '尺寸' then a.value end) as 尺寸
from entities e join eav a on a.entity_id = e.id
group by e.id;   -- 属性一多就是灾难现场

易错 两个模式的共同话术是「灵活」；共同账单是「查询地狱 + 约束真空」。评审时听到「灵活」先警惕。

场景 同一用户连点五次「抢购」按钮，只能成交一件--第一道防线。

讲解 把「同一件事」定义成唯一键（用户 + 活动 / 商品），建唯一约束 + on conflict do nothing。重复请求在数据库层被无声吞掉，第二次起的插入根本不生效。这是所有幂等设计里最便宜、最可靠的一档：不依赖任何应用层逻辑。

create unique index uq_seckill on seckill_orders (user_id, activity_id);

insert into seckill_orders(user_id, activity_id, ...)
values (:uid, :aid, ...)
on conflict (user_id, activity_id) do nothing
returning id;   -- 有返回 = 抢到，无返回 = 重复请求

易错 幂等键的颗粒度是业务定义的：用户+活动？用户+商品+秒？定义错颗粒度，要么吞单要么重复。

场景 「读-改-写」三步之间数据被人改了怎么办--提交时校验，冲突就重试。

讲解 update ... set x = 新值, version = version + 1 where id = ? and version = 旧version：影响行数为 0 说明被抢先，应用层重读再试。没有锁等待、吞吐高，适合冲突少的场景；冲突率高时重试风暴反而拖垮吞吐。

-- 先读：select stock, version from products where id = 1;  -- version=5
update products
set stock = stock - 1, version = version + 1
where id = 1 and version = 5;      -- CAS
-- 返回 0 行 = 被别人抢先改了：重读、重算、重试（应用层循环）

易错 CAS 的 where 条件是「旧值」不是「目标值」--写成 stock >= 0 就变成了下一条的原子扣减，语义混了。

场景 复杂的多步「读-算-写」，一步锁住，别人排队。

讲解 事务里先 select ... for update 把行锁住，读到的值保证不被别人动，算完再 update、commit 放锁。绝对正确、逻辑最直白；代价是串行化：后到的全部排队，吞吐由事务长度决定。

begin;
select stock from products where id = 1 for update;   -- 锁住
-- 应用判断 stock > 0
update products set stock = stock - 1 where id = 1;
commit;   -- 放锁，下一个排队者进来

易错 锁持有时间 = 整个事务长度：锁住行之后再调外部接口、发短信，就是把全队列按住陪等。

场景 其实大多数「防超卖」只需要这一条语句。

讲解 把「判断 + 扣减」压进一条 UPDATE：set stock = stock - 1 where id = ? and stock >= 1。单语句的原子性由数据库保证，不为 0 就扣不成负。影响行数 0 = 没抢到，应用直接处理失败分支。没有读-改-写间隙、没有竞态窗口。

update products
set stock = stock - 1
where id = 1 and stock >= 1
returning stock;    -- 返回新库存 = 成功；无返回 = 售罄

易错 条件是 stock >= 1 而不是应用层先判断--判断放应用层，间隙里库存就被别人扣了（这就是当初超卖 37 件的机制）。

场景 三把武器都造好了，压测台上见真章。

讲解 ① 原子扣减：单字段扣减的最优解，无重试无等待，吞吐最高；② 悲观锁：多步业务逻辑（扣库存 + 建订单 + 记流水）必须串行时的正确解，吞吐受事务长度限制；③ 乐观锁：冲突稀疏时近于无锁，冲突密集时重试风暴。选型口诀：简单扣减用原子，复杂流转用悲观，冲突少才乐观。

-- 压测脚本骨架：pgbench 或多会话并发
-- 每个会话循环执行：
update products set stock = stock - 1
where id = 1 and stock >= 1;
-- 记录三种方案下：成功数 / 失败数 / 平均耗时 / 超卖数（必须 = 0）

易错 别为「显得高级」上乐观锁重试框架--简单场景一条原子 UPDATE 完胜，工程判断力恰恰体现在克制。