外观
04 - 数据库范式不是教条:冗余字段到底该不该加
摘要
用户搬家后,历史订单地址也变了,是表没有拆够,还是拆错了?本文先解释“一个字段代表什么事实”,再用订单明细理解范式,最后讨论历史快照、派生总额和报表汇总的维护成本。重点不是少存几个字段,而是明确谁负责写、失败怎么办、结果如何校验。
前置阅读:03 表设计与字段约束。同事务维护部分需要 16 ACID 与转账事务和 19 SELECT 与锁。示例以 MySQL 8.0.16+ / 8.4、InnoDB 为基础;SQL 预期不代表实例实测。
阅读入口:先分清事实,不必先学并发锁
初读可以先看第 1—4 节,理解事实、范式和重复存储,再看第 8 节的工程取舍。第 5—7 节讨论事务维护、异步汇总和对账,需要事务与锁的基础。函数依赖先理解为“哪些字段能确定另一个字段的值”,多候选键下的严格判定可在第二遍阅读时深入。
1. 先区分当前资料和历史事实
订单只保存客户编号,每次展示时关联客户当前收货地址。客户修改地址后,三年前订单也显示新地址。查询没有写错,模型却表达错了业务。
- 客户当前地址:回答“现在默认送到哪里”,由客户资料维护。
- 订单收货地址:回答“这笔订单约定送到哪里”,由下单或订单变更流程维护。
两个地址起初相同,却不是同一个事实。订单应保存当时确认的地址;如果允许发货前改地址,要定义允许状态、操作权限和变更记录,不能让资料更新悄悄改写历史。
判断冗余前先问:字段的主体是谁、在哪个时刻成立、由谁修改。成交价与当前商品价也是这个区别。
2. 范式为什么能减少数据错误
范式是根据字段之间的依赖关系判断表结构是否合理的一组规则。按这些规则调整表结构的过程称为规范化。它要解决的是:同一个事实存了多份后,怎样避免漏改、无法单独登记或误删。先看这些问题怎样发生,再理解拆表规则。
2.1 从一张混合表看三类异常
假设订单明细保存这些字段:
| 订单号 | 行号 | 客户号 | 客户当前姓名 | 商品号 | 商品当前名称 | 数量 | 成交单价 |
|---|---|---|---|---|---|---|---|
| 1001 | 1 | 7 | 张三 | 81 | 杯子 | 2 | 20.00 |
| 1001 | 2 | 7 | 张三 | 82 | 碗 | 1 | 10.00 |
| 1002 | 1 | 7 | 张三 | 81 | 杯子 | 1 | 18.00 |
这里保存的是当前姓名、当前商品名称。它们应随客户或商品资料变化,而订单快照记录的是下单时确认的值。如果商品资料只存在这张混合表中,就会出现三类异常:
- 更新异常:客户改名需要同步改三行;漏改一行,同一个客户就出现两个当前姓名。
- 插入异常:还没有订单时,无法在这张表中独立登记商品,因为商品资料被迫依附于订单明细。
- 删除异常:删除某商品的最后一条订单明细时,唯一保存的商品资料也随之消失。
根因是订单、明细、客户、商品的事实混在一起,生命周期不同,却被迫一起维护。
2.2 “由谁决定”就是函数依赖
在业务规则稳定时,给定一组字段,就能确定另一字段的值,称为函数依赖。记号 X → A 表示:只要两行的 X 值相同,A 值就必须相同。例如,客户号相同的记录不能有两个不同的当前姓名;不同客户却可以同名,所以箭头不能随意反过来。下面约定一笔订单属于一个客户,并为每条明细分配订单内唯一的行号:
text
订单号 → 客户号、下单时间
客户号 → 客户当前姓名
商品号 → 商品当前名称
(订单号, 行号) → 商品号、数量、成交单价这里是业务约束,不是“当前样本恰好没有重复”。同一商品可以在订单中出现两行,例如不同促销或配送安排,所以不能未经约定就认定 (订单号, 商品号) 唯一决定一条明细。
候选键是能够唯一确定整行、且删去任何一个字段后都不再具备这个能力的字段组合。上例中,订单号不能区分同一订单的两条明细,行号也会在不同订单中重复;两者合在一起才能定位一条明细。
一个表可以有多个候选键,选中的候选键叫主键。讨论范式时,字段也称为属性:属于任意候选键的字段叫主属性,不属于任何候选键的字段叫非主属性。因此,非主属性不等于“不在选中主键里的字段”。
2.3 三种范式,按解决的问题理解
| 层次 | 直观要求 | 订单中的例子 |
|---|---|---|
| 第一范式,1NF | 一行是一条记录,一个字段保存模型认可的单一值 | 不用 81,82,83 字符串代替需要独立查询和约束的明细行 |
| 第二范式,2NF | 在 1NF 基础上,非主属性不能只依赖某个候选键的一部分 | 明细键是 (订单号, 行号),客户号却只由订单号决定,应放订单表 |
| 第三范式,3NF | 在 2NF 基础上,避免把本应由其他实体决定的当前事实附在本实体上 | 订单保存客户号,不重复维护客户当前姓名 |
按这个例子逐步拆分:先让每条明细占一行;再把只依赖订单号的客户号移到订单表,这消除了对明细候选键的部分依赖;最后把客户当前姓名放到客户表。否则,订单表中仍有 订单号 → 客户号 → 客户当前姓名 这条传递依赖,客户改名还是要修改多笔订单。
第一范式中的“单一值”要结合字段用途理解。JSON 是一种可以表示对象和数组的数据格式。若需要逐条约束、关联和修改商品明细,用独立明细行更合适;若只是保存一份完整的原始报文,建模边界则不同。因此,不能仅凭字段使用 JSON 就判定整个设计是否合理。
多候选键情形下,第三范式需要更严格的判定:在满足第一范式的关系中,对每个非平凡依赖 X → A,X 是超键,或者 A 是主属性。非平凡指 A 不属于 X;超键指能唯一确定整行的字段集合,但不要求字段组合最小。前面的“避免传递依赖”有助于理解这个订单例子,不能替代严格判定。
为明细增加自增 ID,这种不承载业务含义、只用于标识记录的键称为代理主键。它方便定位,却不会消除 订单号 → 客户号、客户号 → 客户当前姓名 等依赖,也不会自动修复事实重复维护。
3. 拆表之后,哪些字段仍要保留
把上例拆成客户、商品、订单和订单明细:
| 表 | 保存的事实 | 主要标识 |
|---|---|---|
| 客户 | 当前姓名、默认地址 | 客户号 |
| 商品 | 当前名称、当前售价 | 商品号 |
| 订单 | 客户号、下单时间、订单状态 | 订单号 |
| 订单明细 | 商品号、数量、成交价,必要时商品名快照 | 订单号与行号 |
| 订单配送信息 | 本订单确认的收件信息及变更记录 | 订单号 |
拆分后,客户当前姓名只在客户表维护,查询订单时通过客户号关联;商品当前名称同理。但订单确认的地址、成交价和商品名快照仍属于订单的历史事实。它们虽然可能与当前资料相同,也有独立的含义和变更规则。
涉及优惠、税费、运费时,订单实付不是简单的数量乘单价求和。后面的示例只计算明细金额之和,不把它当作完整的实付金额。
规范化并不保证业务全部正确。外键只能约束引用存在,不能保证“订单地址来自客户下单时确认的版本”。该规则仍需事务和业务流程落实。
4. 三类“重复存储”,维护方式不同
历史快照保存业务发生时确认的事实;派生字段则能从源数据重新计算,例如从明细计算总额;查询汇总把多条记录按统计规则合并,通常也属于派生数据,但维护范围和刷新频率不同。三者看起来都是“多存一份”,更新规则却不能混用:
| 类别 | 是否跟随源对象变化 | 失败处理 | 典型例子 |
|---|---|---|---|
| 历史快照 | 通常不跟随;按业务规则订正 | 留审计记录,不能随主资料刷新 | 订单成交价、订单地址 |
| 派生字段 | 必须按定义跟随明细变化 | 同事务回滚,或在约定延迟内补齐 | 订单明细总额 |
| 查询汇总 | 按统计口径和刷新频率变化 | 重放、对账、重建 | 每日已支付订单销售额 |
不能把三类都叫“缓存”然后定时覆盖。快照记录过去;派生值由源数据计算;缓存通常可以丢弃并重新取得。尤其历史事实不能用今天的主资料重建。
5. 简单验证:明细变化,总额必须一起变化
以下只讨论“明细金额之和”,不含优惠、税费和运费。kb04_item 保存源数据,kb04_order.total_amount 保存派生总额;维护目标是每次提交后两者一致。
开始准备前,主动提交或回滚已有事务。在独立测试库中创建本文专属表,不覆盖其他章节数据。DDL 指建表等结构变更语句;MySQL 普通 DDL 可能隐式提交,因此建表在事务外执行,不能用它替已有事务安全收尾。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET @kb04_saved_autocommit=@@session.autocommit;
SET autocommit=1;
CREATE TABLE kb04_order (
id BIGINT PRIMARY KEY,
total_amount DECIMAL(18, 2) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE kb04_item (
order_id BIGINT NOT NULL,
line_no INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
deal_price DECIMAL(18, 2) NOT NULL CHECK (deal_price >= 0),
PRIMARY KEY (order_id, line_no),
FOREIGN KEY (order_id) REFERENCES kb04_order(id)
) ENGINE = InnoDB;
INSERT INTO kb04_order VALUES (1001, 50.00);
INSERT INTO kb04_item VALUES (1001, 1, 2, 20.00), (1001, 2, 1, 10.00);
SELECT o.id,o.total_amount,
SUM(i.quantity*i.deal_price) AS calculated_total
FROM kb04_order o JOIN kb04_item i ON i.order_id=o.id
GROUP BY o.id,o.total_amount;初始化预期返回一行:订单 1001,存储与计算总额都是 50。先确认这一步,再把第 1 行商品的数量从 2 改为 3,使总额从 50 变为 70。初始值相同只能说明这组数据一致,还需要维护规则保证后续修改不会让两者分离。
约定所有修改这笔订单明细或总额的入口,先锁订单行,再读取和修改明细,并在同一事务提交。后台订正、删除明细、管理脚本也不能绕过协议。
事务使明细与总额一起提交或回滚;锁则协调同一订单的并发修改。如果两个事务各自读取旧明细再计算总额,后写入的结果可能覆盖先前的修改。先锁同一订单行,就让遵守协议的维护者依次处理这笔订单。下面的 FOR UPDATE 用于加锁读取,ROW_COUNT() 用于检查前一条更新语句影响的行数。
sql
START TRANSACTION;
SELECT id FROM kb04_order WHERE id = 1001 FOR UPDATE;
SELECT line_no, quantity, deal_price
FROM kb04_item WHERE order_id = 1001
ORDER BY line_no FOR UPDATE;
UPDATE kb04_item
SET quantity = 3
WHERE order_id = 1001 AND line_no = 1;
SELECT ROW_COUNT() AS item_rows;预期影响恰好 1 行。否则立即回滚并停止;若目标不存在、任一语句报错,也不能继续写总额。确认后在同一连接和事务继续:
sql
UPDATE kb04_order SET total_amount = 70.00 WHERE id = 1001;
SELECT ROW_COUNT() AS order_rows;
SELECT o.total_amount,
SUM(i.quantity * i.deal_price) AS calculated_total
FROM kb04_order o
JOIN kb04_item i ON i.order_id = o.id
WHERE o.id = 1001
GROUP BY o.id, o.total_amount;推导预期:旧总额是 2 × 20 + 1 × 10 = 50,新总额是 3 × 20 + 1 × 10 = 70,两个结果应都为 70。这里的 70 是为了演示算出的值,不是生产中硬编码金额的方案。应用应使用锁内读取的当前明细按同一金额口径计算,不混用先前快照读出的旧值。
确认 item_rows=1、order_rows=1 且两个金额都为 70 后,再单独执行提交。该检查以初始总额为 50、数量为 2 为前提;重复执行时,数值可能已经相同,不能继续套用“恰好修改 1 行”的预期:
sql
COMMIT;任何错误、目标不存在或校验不符都应 ROLLBACK,不能继续提交。手工示例不会自动检查并回滚;生产实现必须把这些检查写进事务控制,完整模式见 09 安全更新与删除。
先锁父行让同一订单的维护串行化,是协议,不是外键自动提供的汇总保护。不同订单仍可并发。若一个事务要处理多笔订单,统一父行加锁顺序,减少死锁;库存与跨订单约束还需另外设计。
验证中途回滚时,在新的事务内仍先按上述顺序加锁,修改明细后执行 ROLLBACK。随后检查总额和明细都保持上次已提交状态,不用制造复杂故障就能理解原子性。
重复练习应使用新表名,或在所有参与者结束事务后明确只清理本文表:先 DROP TABLE kb04_item,再 DROP TABLE kb04_order,因为明细引用订单。不要删除整个实验库。原连接在无未完成事务时恢复 SET autocommit=@kb04_saved_autocommit;;重连后重新准备,不执行丢失变量的恢复。
6. 异步汇总:省读取成本,不是省一致性设计
若每日销售汇总允许晚几秒更新,可以先记录订单变化,再由后台处理这些变化来更新汇总。描述一次业务变化的记录称为事件;读取并处理事件称为消费。
如果先提交订单、再单独发送事件,两步之间的失败可能使订单已变更、汇总却收不到通知。使用 **Outbox(待发送事件表)**时,业务修改和待发送事件记录在同一数据库事务提交,提交后由后台发送。**CDC(变更数据捕获)**则从数据库变更日志获取变化;它与 Outbox 的具体协作方式见后续专题。
发送与消费之间仍可能出错:
| 情况 | 如果只做“金额加一次” | 应明确的规则 |
|---|---|---|
| 同一事件重复发送 | 多算一次金额 | 用事件身份去重,并与汇总更新同事务完成 |
| 支付后退款 | 原金额没有撤回 | 按口径处理状态变化和退款金额 |
| 新版本先到、旧版本后到 | 旧值覆盖新值 | 检查业务版本或保证所需顺序 |
| 统计口径改了 | 新旧汇总混杂 | 标记口径版本,重建并校验后切换 |
Outbox 不保证消息只发送一次,CDC 也不会自动使业务处理具备幂等性,即同一事件重复处理时,不重复产生业务效果。例如,消费端不能因为重试就把同一笔销售额加两次。完整发布和消费故障窗口见 分布式 29 Outbox 与 CDC。
必须约定可以落后多久、积压如何告警、原事件保留多久、如何重放。读者看到的是“截至哪个时间或版本的结果”,不应把延迟汇总宣称为实时交易事实。
7. 对账发现差异后,先解释再修复
对账是在相同的金额口径和时间边界下,比较存储结果与可信依据。对于本文的派生总额,可以从订单明细重新计算,再筛出与存储值不同的订单:
sql
SELECT o.id, o.total_amount AS stored_total,
COALESCE(SUM(i.quantity * i.deal_price), 0) AS calculated_total
FROM kb04_order o
LEFT JOIN kb04_item i ON i.order_id = o.id
GROUP BY o.id, o.total_amount
HAVING stored_total <> calculated_total;上述事务成功提交后,推导预期是没有差异行。COALESCE 把没有明细的总额定义为 0;这里字段非空,如果业务允许未知金额,不能未经口径确认就把未知当 0。
线上对账要按订单区间分批、限速并记录进度。差异可能来自绕过写入协议、异步积压、旧统计口径或程序错误。不能把查询出的值直接回填:发现差异到更新之间,订单可能又变化。订正需重新锁定目标、复核版本和源数据,保存修复依据。
历史快照的对账应依赖当时订单记录和审计证据,不应关联客户当前地址“修正”过去。
8. 什么时候值得反范式
反范式是在明确读取需求后,有意增加重复字段或预计算结果,并承担相应的维护成本。例如,将客户当前姓名复制到订单查询表,可以减少关联,但客户改名时也要更新这些副本。它与保存订单历史姓名快照的目的不同。
决定是否增加副本前,先确认读取瓶颈。JOIN 是按关联条件组合多张表记录的操作,使用 JOIN 本身不说明查询慢:
- 按 14 执行计划分析确认扫描、回表、排序还是关联行数过多。
- 核对查询语义与索引,避免把错误查询固化进宽表。
- 用相同数据量和流量比较优化前后读取收益。
- 同时测写入成本、锁等待、补齐延迟和重建时长。
- 为新副本指定负责人、源数据、更新协议和退出方案。
把多个实体的查询字段集中到一张表,常称为宽表。它可能减少关联,却增加每行占用的字节数;复制字段还可能增加索引和回填成本,高频变化时尤其会增加写入量。同库同事务维护更直观,但会扩大锁范围;异步维护缩短同步路径,却可能让查询暂时读到旧值,因此需要明确允许的延迟和补偿方式。
CQRS(命令与查询职责分离)把修改状态的写模型与服务查询的读模型分开组织;两者可以共享同一个数据库,也可以采用独立存储。它不等于把 SELECT 路由到副本的数据库读写分离,也不要求所有表都反范式。前者解决模型职责,后者解决读取路由与复制负载,可能组合使用但边界不同。定义参见微软 CQRS 模式说明。
物化视图将查询结果存储下来,供后续查询使用。能否使用它、能否仅根据变化的数据进行增量刷新,取决于具体数据库、版本和刷新方式,不能仅凭“支持视图”就推断这些能力。
9. 阅读导航
- 前置:03解释身份与约束;16解释全事务成功或失败。
- 横向:05 JOIN用于确认关联粒度;07 聚合用于定义汇总口径。
- 排障:19 锁解释汇总维护等待;09提供受控订正流程。
- 后续:29 分库分表把同库维护变成跨资源协调;跨专题 Outbox 与 CDC展开异步可靠性。
10. 名词解释
| 名词 | 本文中的含义 |
|---|---|
| 函数依赖 | 按业务规则,一组字段能确定另一字段的值 |
| 候选键 / 超键 | 都能唯一确定整行;候选键还要求字段组合最小 |
| 主属性/非主属性 | 属于任一候选键/不属于任何候选键的属性,不只看选中的主键 |
| 规范化 / 反范式 | 前者减少事实重复维护;后者为明确读取目标增加可控副本 |
| 历史快照 | 业务发生或确认时保存的事实,不自动随当前资料变化 |
| 派生字段 | 能按明确规则从源数据计算的值 |
| 对账 | 在明确口径和时间边界下比较存储结果与可信依据 |
| Outbox / CDC | 同事务保存待发事件 / 从数据库变化中获取事件 |
| CQRS/数据库读写分离 | 分开组织写与读模型/把查询路由到合适实例,二者不是同义词 |