前置知识

相关文章

仓库 · 209 字 · 1 分钟阅读

范式:1NF → BCNF,配例子

Codd 的建表规则——每种异常长什么样、下一个范式怎么修它、以及什么时候为了性能故意打破规则。

目录
  1. TL;DR
  2. 范式要防的那些异常
  3. 第一范式(1NF)
  4. 第二范式(2NF)
  5. 第三范式(3NF)
  6. Boyce-Codd 范式(BCNF)
  7. 4NF:多值依赖
  8. 5NF 和 6NF
  9. 故意的反规范化
  10. 为什么范式有序号
  11. 范式真正给你什么

#TL;DR

范式是一组逐级递进的规则,用于组织关系表,避免冗余和更新异常。Codd 在他 1971 年给 1970 年奠基论文的后续论文里定义了前三个(1NF、2NF、3NF)。1974 年 Ray Boyce 指出了 3NF 漏掉的一种情形,随后 Boyce-Codd 范式(BCNF) 出现。4NF 和 5NF 存在,但很少被明确拿来说事。大多数生产模式瞄准 3NF 或 BCNF。低于此的反规范化——为了性能故意做——是一次经过深思的工程权衡,不是错误。懂规则的意义在于知道自己正在打破哪条规则。

#范式要防的那些异常

在走完各范式之前,值得先看一眼它们要解决的问题。所有这些问题都来自冗余——同一个事实被存到多个位置,以至于更新、插入或删除都可能把数据库留在不一致的状态。

看一个把客户和订单放进同一张表的设计:

┌────┬─────────────┬──────────┬─────────┬──────────┐
│ id │ customer    │ city     │ order_id│ amount   │
├────┼─────────────┼──────────┼─────────┼──────────┤
│  1 │ Ada         │ London   │    1001 │     250  │
│  2 │ Ada         │ London   │    1002 │     430  │
│  3 │ Turing      │ London   │    1003 │      80  │
└────┴─────────────┴──────────┴─────────┴──────────┘

三种异常:

  • 更新异常。 如果 Ada 搬到巴黎,我们要改两行。漏掉一行,数据库就会说 Ada 同时住在两座城市。
  • 插入异常。 如果一位客户目前还没有订单,我们没地方给他开一行。
  • 删除异常。 如果我们删掉 Turing 唯一的订单(1003),也就一并删掉了 Turing 存在这个事实。

范式就是把数据重新组织得让上述异常无法发生的形式化规则。

#第一范式(1NF)

每一列存储单个原子值。没有重复组,也没有嵌套结构。

在 1NF 之前——像这样的表不在 1NF:

┌────┬──────────┬──────────────────────────────┐
│ id │ customer │ phones                       │
├────┼──────────┼──────────────────────────────┤
│  1 │ Ada      │ "555-1234, 555-5678"         │
│  2 │ Turing   │ "555-9999"                   │
└────┴──────────┴──────────────────────────────┘

phones 列存了一个逗号分隔的列表。这让查询变得很糟糕(“找所有使用某区号的客户”要子串匹配),让更新危险(分隔符不一致),并且违反了 1NF。

1NF 的修法:把多值属性拆成独立的表。

customers               phones
┌────┬──────────┐      ┌────┬──────────┬──────────┐
│ id │ customer │      │ id │ cust_id  │ number   │
├────┼──────────┤      ├────┼──────────┼──────────┤
│  1 │ Ada      │      │  1 │        1 │ 555-1234 │
│  2 │ Turing   │      │  2 │        1 │ 555-5678 │
└────┴──────────┘      │  3 │        2 │ 555-9999 │
                       └────┴──────────┴──────────┘

在现代 SQL 里,“原子”这个要求很有意思。Postgres 允许你在单列里存数组和 JSON 文档,严格来说这违反了经典的 1NF。实际上,如果你对那些列只做整体读取、不做分片查询,就完全没问题。如果你发现自己在查询数组里的内容,那通常意味着你应该把它规范化出去。

#第二范式(2NF)

1NF,再加上:每个非主键属性都依赖整个主键,而不是主键的一部分。

这条规则只对复合主键(由多列组成的主键)的表有意义。只有单列主键的表自动就在 2NF。

看一张订单明细表,主键是 (order_id, product_id):

┌──────────┬────────────┬───────────┬──────────────┬────────┐
│ order_id │ product_id │ product_name│ unit_price   │ quantity│
├──────────┼────────────┼───────────┼──────────────┼────────┤
│     1001 │         42 │ Teapot    │         9.99 │      2 │
│     1001 │         43 │ Mug       │         4.99 │      4 │
│     1002 │         42 │ Teapot    │         9.99 │      1 │
└──────────┴────────────┴───────────┴──────────────┴────────┘

问题:product_name 和 unit_price 只依赖 product_id,并不依赖完整的 (order_id, product_id) 主键。每次茶壶出现在订单里,它的名字和价格都会被重复存一遍。

2NF 修法:把商品数据拿出来成独立表。

order_items                          products
┌──────────┬────────────┬────────┐  ┌─────┬───────────┬────────┐
│ order_id │ product_id │quantity│  │ id  │ name      │ price  │
├──────────┼────────────┼────────┤  ├─────┼───────────┼────────┤
│     1001 │         42 │      2 │  │  42 │ Teapot    │   9.99 │
│     1001 │         43 │      4 │  │  43 │ Mug       │   4.99 │
│     1002 │         42 │      1 │  └─────┴───────────┴────────┘
└──────────┴────────────┴────────┘

现在每个商品的名字和价格只存在一处。在一个地方改茶壶的价格;所有用到它的订单都自动反映——当然,这里假设你不同时保留历史价格,那是另一个话题。

#第三范式(3NF)

2NF,再加上:没有非主键属性依赖于另一个非主键属性(没有传递依赖)。

看订单表:

┌────┬─────────┬──────────────┬──────────┐
│ id │ cust_id │ cust_name    │ amount   │
├────┼─────────┼──────────────┼──────────┤
│ 1001│    101 │ Ada          │    250   │
│ 1002│    101 │ Ada          │    430   │
│ 1003│    102 │ Turing       │     80   │
└────┴─────────┴──────────────┴──────────┘

cust_name 依赖 cust_id,而不是直接依赖主键 id。这就是传递依赖:id → cust_id → cust_name。如果 Ada 改名,我们得更新每一行订单。

3NF 修法:把客户名字搬到 customers 表,通过订单的 cust_id 引用:

orders                            customers
┌────┬─────────┬────────┐        ┌─────┬──────────┐
│ id │ cust_id │ amount │        │ id  │ name     │
├────┼─────────┼────────┤        ├─────┼──────────┤
│1001│    101  │   250  │        │ 101 │ Ada      │
│1002│    101  │   430  │        │ 102 │ Turing   │
│1003│    102  │    80  │        └─────┴──────────┘
└────┴─────────┴────────┘

三表结构:customers、orders,通过 cust_id 做连接。多数生产模式就停在这里。3NF 是多数 OLTP 系统的目标,达到它之后,大部分更新异常也就避开了。

#Boyce-Codd 范式(BCNF)

3NF,再加上:每一条函数依赖的左边都是超键。

BCNF 是 3NF 的更严格版本,覆盖 3NF 技术上允许但有问题的一种情形。想象一张表,记录哪位教授教哪门课,约束是每位教授只教一门课,但同一门课可以有多位教授:

┌───────────┬────────────┬───────────────┐
│ student   │ course     │ professor     │
├───────────┼────────────┼───────────────┤
│ Alice     │ 6.001      │ Sussman       │
│ Bob       │ 6.001      │ Sussman       │
│ Alice     │ 6.004      │ Knight        │
│ Carol     │ 6.001      │ Abelson       │
└───────────┴────────────┴───────────────┘

主键:(student, course)。由于 professor 依赖完整主键(具体是哪位教授取决于学生是谁、课是什么),它在 3NF。

但有一个隐藏依赖:professor → course。Sussman 教 6.001,这是关于 Sussman 的事实,与任何特定学生-课程对无关。如果我们想记录 Abelson 下学期将开始教 6.001,我们没有地方放这条事实,除非再拉一个学生上去。

BCNF 修法:拆成两张表。

enrollments                       teaches
┌───────────┬────────────┐       ┌───────────┬────────────┐
│ student   │ professor  │       │ professor │ course     │
├───────────┼────────────┤       ├───────────┼────────────┤
│ Alice     │ Sussman    │       │ Sussman   │ 6.001      │
│ Bob       │ Sussman    │       │ Knight    │ 6.004      │
│ Alice     │ Knight     │       │ Abelson   │ 6.001      │
│ Carol     │ Abelson    │       └───────────┴────────────┘
└───────────┴────────────┘

两张表里的函数依赖,其左边都是超键。

BCNF 由 Codd 和 Boyce 在 1974 年引入,作为对 3NF 漏掉情形的补丁。实际上,多数 3NF 的模式同时也在 BCNF——两者有差别的情形比较罕见,多出现在建模拥有多个独立候选键的业务规则的表上。

#4NF:多值依赖

BCNF,再加上:不含非平凡的多值依赖。

看一张员工、多技能、多语言的表,且这两个属性完全独立:

┌────────────┬─────────┬──────────┐
│ employee   │ skill   │ language │
├────────────┼─────────┼──────────┤
│ Dylan      │ Python  │ English  │
│ Dylan      │ Python  │ Spanish  │
│ Dylan      │ SQL     │ English  │
│ Dylan      │ SQL     │ Spanish  │
└────────────┴─────────┴──────────┘

技能和语言的笛卡尔积被存了进来。如果 Dylan 学一门新语言,我们要加两行(每条已有的技能加一条)。这是一种多值依赖——employee →→ skill 和 employee →→ language——4NF 说:把它们拆开。

employee_skills                   employee_languages
┌────────────┬─────────┐         ┌────────────┬──────────┐
│ employee   │ skill   │         │ employee   │ language │
├────────────┼─────────┤         ├────────────┼──────────┤
│ Dylan      │ Python  │         │ Dylan      │ English  │
│ Dylan      │ SQL     │         │ Dylan      │ Spanish  │
└────────────┴─────────┘         └────────────┴──────────┘

4NF 在生产模式设计中很少被点名使用,因为它要预防的情形相对罕见,多数设计者本能上就会把独立属性分开,想都不会想”4NF”。

#5NF 和 6NF

5NF(投影-连接范式) 处理那些可以分解成三张或更多较小关系、但不能分解成两张的情形。基本上不会被明确引用——情形很少见,多数从业者也不会显式朝它设计。

6NF 是研究界的一种范式,主要用于时态数据库,其中每个事实都带时间区间存储。在专门语境之外,6NF 更偏学术对象。

如果你在问自己的模式是不是 5NF,那你更像是在写研究论文,而不是在做生产数据库。

#故意的反规范化

规范化是默认,不是法律。有合理的理由故意违反规则。

读多写少的工作负载能从反规范化获益。一个数据仓库在存 transactions 时,可能把客户位置、商品分类、货币反规范化到事实表里,形成星形模式,使得多数查询不用连接就能回答。Snowflake、BigQuery、Redshift——按设计其模式看起来完全不像 3NF。

写多、小规模的系统则更适合规范化。一个每秒处理几千次写、业务规则复杂的 OLTP 数据库,保持 3NF 要好推理得多。

缓存和物化视图是一种反规范化。你并不改变真理之源的模式;你为了性能创建派生的、反规范化的副本。如果能容忍些许陈旧,这可能是两全其美。

负责任地反规范化的几条规矩:

  • 保留规范化的真理之源。 反规范化是缓存,不是主数据。
  • 把反规范化显式化。 不要”以防万一”地复制某个字段;因为某条具体查询需要它快,才复制。
  • 记录下更新路径。 源数据变化时,得有某个东西去更新反规范化的副本。你要是不知道是谁更新,副本就会漂。
  • 反规范化之前先做性能测试。 多数性能问题有其他解(索引、更好的查询、缓存层),不需要违背规范化。

#为什么范式有序号

范式有一个值得注意的性质:它们是嵌套的。BCNF 蕴含 3NF,3NF 蕴含 2NF,2NF 蕴含 1NF。你如果达到了 BCNF,就同时满足了之前每一级。

这不是巧合。每一级范式都定义了一种新的冗余——这种冗余在前面的范式下是被允许的。在没有先确定”有一组属性可以推理”(1NF)之前,你谈不了传递依赖(3NF)。没有复合键,就不会有部分依赖(2NF)——而复合键的存在需要先有主键(1NF)这个概念。

范式按时间先后引入,是因为它们解决的冗余越来越细微。1970 年代的数据库还在跟 1NF 搏斗(人们还在用长度可变、内嵌分隔符的列)。到 1980 年代末,3NF 已经是默认设计范式;到 1990 年代,大家基本不再刻意推导 BCNF,因为标准设计实践天然就会产生它。

#范式真正给你什么

关系模型那篇把规范化总结为”最小化冗余、防止不一致的建表规则”。这就字面意思而言没错。范式给得更深的,是一套设计词汇——一组你可以用来说明某个模式为什么不对劲的概念。

“这是一个更新异常”是精确的批评,不是模糊的。“这不是 2NF”是一个具体、可修复的问题。有这个语言,你就能快速诊断模式、对为什么某处痛苦开展有结构的讨论。

生产中:默认瞄 3NF 或 BCNF。反规范化要有意为之、要有证据、要针对具体负载。把范式当成思考数据的工具,不是过关用的合规清单。

Codd 1971 年引入范式的论文,基本上是对他 1970 年提案的一次清理。五十年后,它仍然是模式设计的工作语言。对 1970 年代的计算机科学论文来说,这是一种罕见的性质。它经得起时间,是因为它谈的与其说是数据库,不如说是当你冗余地表达数据时,数据拒绝规矩地行事的方式——无论你在用哪种存储引擎、哪种查询语言、哪种行业,这个真相都继续成立。