DAY 47 W7 · 大促备战：优化实战与工程化 高频

大促前的代码评审：反模式清点

大促前的代码评审，你把这一年里见过的反模式整理成清单，替后端把了关。

学 45 min
练 50 min
盘 25 min
共 120 分钟

学 · 45 min

  1. 01N+1 查询、SELECT *、大事务、无限制 IN 列表

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

    ① 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 的懒加载是重灾区，列表场景要显式预取。

  2. 02隐式类型转换、软删除导致的表膨胀

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

    ① 隐式转换：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';

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

  3. 03count(*) 慢的成因与近似方案

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

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

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

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

  4. 04EAV 模型的代价、把数据库当消息队列的取舍

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

    ① 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;   -- 属性一多就是灾难现场

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

练 · 50 min

  1. 写出 N+1 的具体现象，并给出 JOIN 或批量查询的改法
    参考答案

    识别特征比改法更重要：应用日志里同一模板的语句海量出现，基本就是 N+1。ORM 懒加载是重灾区，列表场景必须显式预取。

    -- 现象：后端先取 100 个订单，再循环逐个查用户 = 101 次往返
-- 日志特征：同一语句模板几毫秒内刷屏 100 次

-- 改法一：join 一次取回
select o.id, o.total_amount, u.name as 用户名
from orders o
join users u on u.id = o.user_id
where o.status = 2
order by o.created_at desc
limit 100;

-- 改法二：数组参数批量取（ORM 里就是预取）
select id, name from users
where id = any((select array_agg(user_id)
                from (select user_id from orders
                      where status = 2
                      order by created_at desc limit 100) t));
  2. 实测三种 count 方案（精确 / reltuples 估算 / 计数表）的耗时
    参考答案

    预期：②③毫秒级，①慢两三个量级。计数表要靠触发器或应用双写维护增量--用查询提速换写入侧的代价和复杂度。

    \timing on
-- ① 精确：百万行逐行可见性检查
select count(*) from orders;

-- ② 估算：读统计信息
select reltuples::bigint from pg_class where relname = 'orders';

-- ③ 计数表：预聚合好，查询只扫几十行
create table order_counts (status int, cnt bigint);
insert into order_counts
select status, count(*) from orders group by status;
select sum(cnt) from order_counts;
  3. 找出自己这个库里可能存在的 EAV 或软删除膨胀
    参考答案

    本库按课程设计没有 EAV 和软删除列，这题的产出是排查动作本身：两条体检 SQL + \d 过一遍表结构确认。真实项目里死活比超过 20% 就要警惕（先 vacuum，再查是不是长事务挡住了它）。

    -- 软删除膨胀体检：死活比持续偏高 = vacuum 跟不上
select relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 1) as 死活比
from pg_stat_user_tables
order by n_dead_tup desc;

-- EAV 排查：看有没有「一张表一个 value 列」的设计
select table_name, column_name, data_type
from information_schema.columns
where column_name in ('attr', 'attribute', 'value', 'entity_id');
  4. IN 列表塞 1 万个 id，观察计划与耗时，改成 VALUES join 或临时表
    参考答案

    预期：三者行数一致，但①要先把一万个字面量解析成语法树，语句越长解析和计划开销越大（这部分耗时 explain analyze 里看不到，用 \timing 测端到端）。②③把 id 当数据传：语句恒短、计划稳定，顺便也是防注入的正道。

    -- ① 复现：拼一条带一万个字面量的 in 查询（\gexec 把查询结果当语句执行）
select format(
  'explain analyze select count(*) from orders where id in (%s)',
  (select string_agg(quote_literal(id::text), ',')
   from (select id from orders limit 10000) t)
)\gexec

-- ② 正面：数组参数
\timing on
select count(*) from orders
where id = any((select array_agg(id)
                from (select id from orders limit 10000) t));

-- ③ 正面：临时表 join
create temp table t_ids (id uuid primary key);
insert into t_ids select id from orders limit 10000;
select count(*) from orders o join t_ids on t_ids.id = o.id;
  5. 整理成 antipatterns.md，每条含「为什么坏 + 怎么改」
    参考答案

    antipatterns.md 每条三段式：反模式名 + 为什么坏（讲机制，不讲感觉）+ 怎么改（带改法示例或 SQL）。本天可覆盖：N+1、select *、大事务、无限制 IN、隐式类型转换、软删除膨胀、count(*) 滥用、EAV、拿表当队列--凑满 8 条，每条都能对照自己库里的证据。

过关标准 反模式清单至少 8 条，每条都能说出改法。