DAY 31 W5 · 公司立规矩：建模、约束与事务 高频

后端提议做宽表：范式与反范式

后端提议把用户名、商品名直接冗余进订单表「查得快」。你想起了范式，决定跟他掰扯清楚。

学 45 min
练 55 min
盘 20 min
共 120 分钟

学 · 45 min

  1. 01函数依赖与候选键的概念

    场景掰扯范式之前，先学会说「谁决定谁」。

    函数依赖：知道 A 就能唯一确定 B，记作 A -> B（user_id -> 用户名）。候选键：能唯一确定整行的最小列集（orders 的 id；user_id + created_at 若唯一也是候选键）。范式理论的全部推导都建立在这两个概念上，不难，难的是把业务里「谁决定谁」说准。

    -- 检验 user_id -> city 是否成立：一个用户出现两个城市就是反例
select user_id, count(distinct city) as cities
from (select o.user_id, u.city from orders o join users u on u.id = o.user_id) t
group by user_id
having count(distinct city) > 1;

    易错函数依赖是业务事实不是 SQL 特性--它由「一个用户只有一个城市」这类现实规则决定，表设计只能遵守或违反它。

  2. 021NF -> 2NF -> 3NF -> BCNF 各自消除什么问题

    场景面试让你「讲讲三范式」--拿订单表一步步拆，比背定义有力得多。

    ① 1NF：字段原子化--别在一个列里塞「红色,XL」；② 2NF：消除部分依赖--非键列不能只依赖复合键的一部分（明细表里放商品名，商品名只依赖 product_id，不依赖整个 (order_id, product_id)）；③ 3NF：消除传递依赖--键 -> A -> B 的 B 不该存（存了 category_id 就别存 category 名）；④ BCNF：所有决定因素都得是候选键，是 3NF 的收紧版。

    -- 反例：一张「订单宽表」
-- orders_wide(order_id, product_id, 商品名, 类目名, 用户名, 城市, ...)
-- 2NF 违例：商品名只依赖 product_id
-- 3NF 违例：类目名依赖 类目id（不在键里）、城市依赖 user_id
-- 拆法：order_items 只留 (order_id, product_id, qty, unit_price)

    易错讲范式别背「确保数据一致性」这种空话--每级都配一个「会出什么异常」的例子：改商品名要改一万行、插入没有订单的新商品没地方放。

  3. 03反范式的三种常见形态：冗余字段、预聚合列、宽表

    场景范式全守住了，报表要连七张表才出数--该反范式出场了。

    三种形态按激进程度排：① 冗余字段（订单里存下单时的用户名）；② 预聚合列（orders 上加 item_count，免得每次 count 明细）；③ 宽表（为报表单独建一张大宽表，ETL 定期刷新）。共同点：用一致性维护成本换读取性能。

    -- ② 预聚合列的读取收益
select id, item_count from orders;              -- 免 join 明细
-- 维护成本：每次增删明细都要同步（见下一条）

    易错反范式不是「更先进」，是拿写复杂度换读性能的交易--读得少的表做反范式是纯亏本。

  4. 04反范式带来的一致性维护成本

    场景后端问：那冗余的用户名，用户改名了怎么办？--问到点子上了。

    冗余数据会漂移。两种处理：① 当快照（订单存的是下单当时的名字，故意不同步--历史就该是历史）；② 当缓存（要的就是当前值，必须同步：触发器 / 应用双写 / 定时校验，三选一）。冗余前先回答是哪种。order_items.unit_price 为什么冗余得理直气壮？因为它是价格快照：商品后来改价，当时的成交价不该变。

    -- 定时校验（兜底方案）：找出漂移的冗余行
select o.id
from orders_wide o
join users u on u.id = o.user_id
where o.用户名 <> u.name;   -- 有结果就该同步了

    易错说不清「快照还是缓存」的冗余列，上线半年后没人敢动它--这是最贵的技术债。

练 · 55 min

  1. 拿一张「订单宽表」（含用户名、商品名、类目名）逐步拆到 3NF，写出每一步消除了什么异常
    参考答案

    四步走：① 1NF--每列原子化（「红色,XL」塞一列的要拆），宽表本身已满足；② 2NF--商品名、类目名只依赖复合键 (order_id, product_id) 中的 product_id，是部分依赖，拆出 products：消除「改商品名要改一万行」「没有订单的新商品没地方插」两类异常；③ 3NF--用户名、城市传递依赖 user_id（键 到 user_id 再到 城市），类目名传递依赖 category_id，拆出 users 和 categories：消除「删掉最后一单连用户信息都没了」的删除异常；④ 终点就是练习库现状：orders / users / products / categories / order_items(order_id, product_id, qty, unit_price)。讲每级都配一个具体异常例子，别背「保证一致性」的空话。

  2. 论证 order_items.unit_price 的冗余是合理的（价格快照语义）
    参考答案

    它是价格快照不是缓存：商品改价后，历史订单的成交价不该跟着变；若只存 product_id、查询时 join products.price 拿现价，一次改价等于改写全部历史账本。判断冗余是否合理就问一句「快照还是缓存」--快照不需要同步，缓存必须同步（触发器 / 应用双写 / 定时校验三选一）。seed 给 unit_price 加 0.9~1.1 的抖动就是在模拟「当时成交价不等于现价」。

  3. 给 orders 加冗余列 item_count，写出保持它同步的两种方案
    参考答案

    触发器 = 强一致、但每次写明细都多一次 update orders；定时重算 = 便宜、有数据过期窗口。选哪个取决于读端能否容忍短暂不准--item_count 是当缓存用的冗余，所以必须有同步方案。

    -- 加列并回填存量
alter table orders add column item_count int;
update orders o
set item_count = s.c
from (select order_id, count(*) as c from order_items group by 1) s
where s.order_id = o.id;

-- 方案一：触发器强同步
create or replace function sync_item_count() returns trigger as $$
declare
  oid uuid := coalesce(new.order_id, old.order_id);
begin
  update orders o
  set item_count = (select count(*) from order_items i where i.order_id = oid)
  where o.id = oid;
  return null;
end $$ language plpgsql;

create trigger trg_sync_item_count
after insert or update or delete on order_items
for each row execute function sync_item_count();

-- 验证方案一：插一条明细，对应订单的 item_count 立刻 +1
insert into order_items(order_id, product_id, qty, unit_price)
values ((select id from orders order by created_at limit 1),
        (select id from products limit 1), 1, 1.00);
select id, item_count from orders order by created_at limit 1;
delete from order_items                    -- 清理，顺便验证 delete 也触发同步
where order_id = (select id from orders order by created_at limit 1)
  and qty = 1 and unit_price = 1.00;

-- 方案二：不做实时同步，定时重算（只更新有差异的行）
update orders o
set item_count = s.c
from (select order_id, count(*) as c from order_items group by 1) s
where s.order_id = o.id
  and o.item_count is distinct from s.c;
  4. 举一个你认为必须反范式的实际场景并说明理由
    参考答案

    例子：首页商品卡片的销量与好评率。特征：读 QPS 极高（每次打开首页都查）、实时聚合要扫全量明细、容忍分钟级延迟--在 products 上冗余 sales_count / good_rate，由异步任务定时刷新。判断三问：读得够多吗？现算够贵吗？能容忍旧数据吗？三个都答「是」才反范式，否则是白付维护成本。

  5. 画出练习库的完整 ER 图
    参考答案

    关系清单：users 1-n orders、orders 1-n order_items、products 1-n order_items、categories 1-n products、categories 1-n categories（parent_id 自引用）、orders 1-n payments（unique 限制同渠道一笔）、users 1-n user_logins。工具用 dbdiagram.io 或 mermaid 的 erDiagram 都行，画完对照上面的外键清单核对。

    -- 用外键清单当画图的底稿
select conrelid::regclass as 子表,
       confrelid::regclass as 父表,
       pg_get_constraintdef(oid) as 定义
from pg_constraint
where contype = 'f'
order by 1;
过关标准 能用订单表这一个例子，从头讲完 1NF 到 3NF 的演进。