348 lines
14 KiB
Markdown
348 lines
14 KiB
Markdown
---
|
||
tags: [MySQL, 范式, 表设计, 反范式]
|
||
create time: 2026-05-16 12:56
|
||
---
|
||
|
||
# 表结构设计三范式
|
||
|
||
## 概述
|
||
|
||
> [!abstract] 本文内容
|
||
> 1. **三大范式**：从 1NF 到 3NF，理解为什么需要拆分表
|
||
> 2. **BCNF 简介**：何时需要更进一步
|
||
> 3. **范式化 vs 反范式化**：理论与实践的权衡
|
||
> 4. **设计流程**：一套可操作的建表步骤
|
||
|
||
数据库设计的规范化理论是避免数据冗余和更新异常的基石。但教科书里的完美范式落到工程实际中往往要打折——过度规范化会导致七表 JOIN、查询慢如蜗牛；完全抛弃规范又会让数据变成一盘散沙。
|
||
|
||
本节的目标不是背定义，而是建立一套**可操作的设计直觉**：什么时候该拆，什么时候该合。
|
||
|
||
> [!QUESTION] 思考：如果有一张订单表，字段包括 `order_id`, `user_name`, `user_dept`, `product_name`, `product_category`, `quantity`, `total`——这张表有几种范式违规？分别是什么？
|
||
> （带着这个问题读完本文，你会找到答案。）
|
||
|
||
## 三大范式
|
||
|
||
```mermaid
|
||
flowchart LR
|
||
F1["1NF: 原子性"] --> F2["2NF: 消除部分依赖"]
|
||
F2 --> F3["3NF: 消除传递依赖"]
|
||
|
||
style F1 fill:#00B6BC,color:#fff
|
||
style F2 fill:#FF9F43,color:#000
|
||
style F3 fill:#C44569,color:#fff
|
||
```
|
||
|
||
### 第一范式（1NF）—— 列不可再分
|
||
|
||
```sql
|
||
-- ✅ 满足 1NF：每个单元格只有一个值
|
||
CREATE TABLE employees (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(100),
|
||
phone VARCHAR(20), -- 单一手机号
|
||
skills JSON -- 数组存在 JSON 字段内（如 ["Go","Python"]）
|
||
);
|
||
|
||
-- ❌ 违反 1NF：同一列存多个值（用逗号分隔）
|
||
CREATE TABLE bad_employees (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(100),
|
||
skills VARCHAR(255) -- 'Go,Python,Docker' — 不行！无法单独查询某个技能
|
||
);
|
||
```
|
||
|
||
> [!TIP] 现代变通方案
|
||
>
|
||
> MySQL 5.7+ 支持 JSON 类型。虽然从严格范式角度看 JSON 列内部仍有复合结构，但数据库引擎提供了高效的索引和查询能力（如 `JSON_EXTRACT`），所以在工程实践中被广泛接受。**核心原则不变：不要把非结构化数据当字符串拼接处理。**
|
||
|
||
1NF 是最基本的要求——每一列都是原子值，不能再拆分。现代关系型数据库默认强制执行 1NF。
|
||
|
||
### 第二范式（2NF）—— 消除部分函数依赖
|
||
|
||
> **前提**：先满足 1NF。2NF 主要针对复合主键的情况。
|
||
|
||
```sql
|
||
-- ❌ 违反 2NF：订单明细表中，商品名只依赖 commodity_id，不完全依赖 (order_id, commodity_id)
|
||
CREATE TABLE order_items_bad (
|
||
order_id BIGINT,
|
||
commodity_id BIGINT,
|
||
commodity_name VARCHAR(200), -- 只依赖 commodity_id
|
||
quantity INT, -- 依赖 (order_id, commodity_id)
|
||
PRIMARY KEY (order_id, commodity_id)
|
||
);
|
||
|
||
-- ✅ 修正：拆分出去
|
||
CREATE TABLE commodities (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(200) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE order_items (
|
||
order_id BIGINT,
|
||
commodity_id BIGINT,
|
||
quantity INT,
|
||
PRIMARY KEY (order_id, commodity_id),
|
||
FOREIGN KEY (commodity_id) REFERENCES commodities(id)
|
||
);
|
||
```
|
||
|
||
> [!TIP] 实战建议
|
||
> 如果你用的主键都是单列自增 ID（而非复合主键），那么 2NF 的要求自动满足——因为没有"部分依赖"的问题。现代设计中几乎不用复合主键，所以 2NF 在实践中很少成为约束。
|
||
|
||
### 第三范式（3NF）—— 消除传递依赖
|
||
|
||
> **核心原则**：非主键列之间不能有依赖关系。即"非主属性不依赖于其他非主属性"。
|
||
|
||
```sql
|
||
-- ❌ 违反 3NF：在商品表中，city 通过 area_code 间接依赖于 id
|
||
-- id → area_code → city（传递依赖）
|
||
CREATE TABLE products_bad (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(200),
|
||
area_code INT, -- 区号
|
||
city VARCHAR(50) -- 城市：由 area_code 决定，不是直接由 id 决定
|
||
);
|
||
|
||
-- ✅ 修正：拆成两张表
|
||
CREATE TABLE areas (
|
||
area_code INT PRIMARY KEY,
|
||
city VARCHAR(50) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE products (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(200),
|
||
area_code INT,
|
||
FOREIGN KEY (area_code) REFERENCES areas(area_code)
|
||
);
|
||
```
|
||
|
||
> [!TIP] 直觉判断法
|
||
> 读一下这些字段："商品的**城市**是通过什么决定的？"如果你发现是"通过**区号**决定的"，而不是直接通过商品本身决定——那就是传递依赖，需要拆分。
|
||
>
|
||
> 对比 2NF：2NF 问的是"这列依赖主键的**全部**吗？"；3NF 问的是"这列有没有**绕道**经过另一列？"
|
||
|
||
#### BCNF（修正的第三范式）
|
||
|
||
BCNF 比 3NF 更严格：**任何非平凡函数依赖的决定因素必须是超键**。
|
||
|
||
```sql
|
||
-- 一个典型的 BCNF 违规场景
|
||
-- 学生选课表：(学生, 课程) → 成绩；教师 → 课程
|
||
CREATE TABLE student_course_teachers (
|
||
student_id BIGINT,
|
||
course_id BIGINT,
|
||
teacher_id BIGINT, -- 教师只依赖课程，不完全依赖复合主键
|
||
grade DECIMAL(5, 2),
|
||
PRIMARY KEY (student_id, course_id)
|
||
);
|
||
```
|
||
|
||
```sql
|
||
-- ✅ 修正为 BCNF：拆成两张表，让每个决定因素都是超键
|
||
CREATE TABLE courses_teachers (
|
||
course_id BIGINT PRIMARY KEY,
|
||
teacher_id BIGINT NOT NULL,
|
||
UNIQUE KEY uk_course (course_id) -- 一门课只有一位老师
|
||
);
|
||
|
||
CREATE TABLE student_grades (
|
||
student_id BIGINT,
|
||
course_id BIGINT,
|
||
grade DECIMAL(5, 2),
|
||
PRIMARY KEY (student_id, course_id),
|
||
FOREIGN KEY (course_id) REFERENCES courses_teachers(course_id)
|
||
);
|
||
```
|
||
|
||
> [!QUESTION] 3NF 已经够了，为什么还需要 BCNF？
|
||
>
|
||
> 大多数工程场景 3NF 完全够用。BCNF 解决的问题通常涉及**多重候选键重叠**的复杂建模。如果你遇到 "一门课只能由一位老师教" 这样的约束，且该约束与你的自然主键冲突，才需要考虑 BCNF。实践中建议先做到 3NF，遇到更新异常再往上走。
|
||
|
||
> [!WARNING] 关于 MySQL 自增主键的陷阱
|
||
> 给所有表加上 `id BIGINT AUTO_INCREMENT` 后，从理论上讲每个表的函数依赖都变成了「所有列直接依赖主键」——**自增 ID 会掩盖范式设计缺陷**。
|
||
>
|
||
> 但这恰恰是现代工程实践的真相：我们用自增代理主键保证查询性能，用外键语义保证设计合理。**范式检查应该在业务层（逻辑模型）上做，而不是在物理表结构上硬抠。**
|
||
|
||
## 三范式速查总结
|
||
|
||
读完上面的详细内容，回头用这张表做最后的对比和记忆强化：
|
||
|
||
| 范式 | 核心问题 | 违规症状 | 一句话修复 |
|
||
|------|---------|---------|-----------|
|
||
| **1NF** | 列是不是原子值？ | 一格里塞了多个值 | 拆成多行或多列 |
|
||
| **2NF** | 这列依赖主键的**全部**吗？（仅复合 PK 场景） | 某些列只依赖主键的一部分 | 把只依赖部分的列拆到新表 |
|
||
| **3NF** | 这列有没有**绕道**经过其他非主键列？ | B 列的值由 C 列决定，而不是直接由主键决定 | 把 C 列及它决定的所有列拆出去 |
|
||
| **BCNF** | 每个决定因素都是超键吗？ | 候选键之间有重叠，约束冲突 | 拆到没有交叉候选键为止 |
|
||
|
||
---
|
||
|
||
## 表设计实操流程
|
||
|
||
知道范式定义是一回事，拿到需求画出一张合理的 ER 图是另一回事。这里给一个**四步工作流**：
|
||
|
||
```mermaid
|
||
flowchart TD
|
||
A["1. 列清单<br/>把所有需要的字段写下来"] --> B["2. 定主键<br/>自然 PK or 代理 PK?"]
|
||
B --> C["3. 检查依赖<br/>每列是否直接依赖主键?"]
|
||
C -->|"否"| D["拆表 + FK 关联"]
|
||
D --> E["4. 审视 JOIN<br/>有没有过度拆分?"]
|
||
E --> F["✅ 完成"]
|
||
E -->|"JOIN 过多"| G["考虑反范式化"]
|
||
G --> F
|
||
```
|
||
|
||
### Step 1：列出所有需要的字段
|
||
|
||
不要一上来就想"分几张表"。先像产品经理一样列出这张实体需要的所有属性。
|
||
|
||
```
|
||
订单：下单时间、用户ID、收货地址、商品名称、商品数量、总价、优惠券、实际支付金额...
|
||
```
|
||
|
||
### Step 2：确定主键
|
||
|
||
| 选择 | 适用场景 | 例子 |
|
||
|------|---------|------|
|
||
| **自然主键**（业务唯一标识） | 有天然且不变的唯一码 | 身份证号、ISO 国家代码 |
|
||
| **代理主键**（推荐默认选项） | 大多数业务场景 | `BIGINT AUTO_INCREMENT` / `UUID` |
|
||
|
||
> [!TIP] 默认选代理主键（自增 BIGINT），除非你有明确的理由不用它。这是 95% 项目的最佳起点。
|
||
|
||
### Step 3：逐个检查函数依赖
|
||
|
||
对每一列问两个问题：
|
||
1. **它依赖主键的全部吗？** → 不依赖 = 违反 2NF → 拆出去
|
||
2. **它绕道经过其他非主键列吗？** → 有传递依赖 = 违反 3NF → 拆出去
|
||
|
||
### Step 4：审视 JOIN 成本
|
||
|
||
把表都拆完后，回到你最常用的那条查询，估算需要几个 JOIN。超过 3~4 个的话，认真考虑**局部反范式化**——在目标表中冗余关键信息，而非为了一条查询拆回去。
|
||
|
||
## 范式化的代价
|
||
|
||
> [!QUOTE] 范式设计的目的不是追求完美，而是找到合适的平衡点。
|
||
|
||
过度范式化的直接后果就是 **JOIN 爆炸**。每多一张表就多一次磁盘随机读、一个锁竞争点、一条复杂执行计划：
|
||
|
||
```sql
|
||
-- 7 张表 JOIN 才能拿到订单完整信息...
|
||
SELECT o.id, u.name, u.dept, c.cat_name, p.brand, sh.city, pg.payment_type
|
||
FROM orders o
|
||
JOIN users u ON o.user_id = u.id
|
||
JOIN departments d ON u.dept_id = d.id
|
||
JOIN categories c ON o.category_id = c.id
|
||
JOIN products p ON o.product_id = p.id
|
||
JOIN shipments sh ON o.shipment_id = sh.id
|
||
JOIN payments pg ON o.payment_id = pg.id
|
||
WHERE o.id = 1;
|
||
```
|
||
|
||
> [!TIP] 经验法则
|
||
> - **单个查询 ≤ 3 个 JOIN**：没问题，规范化设计是合理的
|
||
> - **4~6 个 JOIN**：开始警惕，检查是否所有关联都是必要的
|
||
> - **> 6 个 JOIN**：几乎可以确定需要局部反范式化
|
||
>
|
||
> 用 `EXPLAIN` 看看实际执行计划——`Using temporary` + `Using filesort` 是性能杀手。
|
||
|
||
## 反范式设计
|
||
|
||
### 核心权衡
|
||
|
||
反范式的核心思想很简单：**用空间换时间，用冗余换性能**。
|
||
|
||
```mermaid
|
||
flowchart LR
|
||
NORM["规范化设计<br/>少冗余 · 多 JOIN · 易维护"] --> TradeOff{"读 vs 写"}
|
||
ANTI["反范式设计<br/>适度冗余 · 少 JOIN · 高性能"]
|
||
|
||
TradeOff -->|"读 >> 写"| ANTI
|
||
TradeOff -->|"写频繁 \| 强一致性"| NORM
|
||
|
||
style ANTI fill:#00D866,color:#fff
|
||
style NORM fill:#4FC08D,color:#fff
|
||
```
|
||
|
||
| 特征 | 高度规范化 | 反范式化 |
|
||
|------|-----------|---------|
|
||
| 写入速度 | ✅ 快（单表写入） | ❌ 慢（需要同步多表） |
|
||
| 读取速度 | ❌ 慢（多表 JOIN） | ✅ 快（单表即可） |
|
||
| 数据一致性 | ✅ 天然保证 | ⚠️ 需要额外机制 |
|
||
| 存储开销 | ✅ 小 | ❌ 大 |
|
||
| 适用场景 | OLTP 高频写入 | OLAP 报表 / 读多写少 |
|
||
|
||
### 常见反范式手法
|
||
|
||
| 手法 | 示例 | 好处 |
|
||
|------|------|------|
|
||
| **冗余字段** | `orders` 表冗余 `username` | 避免每次都 JOIN `users` 表 |
|
||
| **预计算列** | 冗余 `order_total`（来自 `line_items` 汇总） | 避免运行时 SUM |
|
||
| **宽表** | 将用户基本信息平铺到日志表中 | 查询零 JOIN |
|
||
| **定时刷新** | 定时跑批生成汇总表 | 代替复杂的实时聚合 |
|
||
|
||
```sql
|
||
-- 实战：冗余计数字段
|
||
-- 帖子被点赞次数存在帖子表本身，而不是每次统计 likes 表的行数
|
||
CREATE TABLE posts (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
author_id BIGINT NOT NULL,
|
||
title VARCHAR(200),
|
||
content TEXT,
|
||
like_count INT DEFAULT 0, -- 冗余计数，由触发器或应用层维护
|
||
comment_count INT DEFAULT 0, -- 同上
|
||
INDEX idx_like_count (like_count)
|
||
);
|
||
|
||
-- 当用户点赞时，原子操作即可：
|
||
-- UPDATE posts SET like_count = like_count + 1 WHERE id = ?;
|
||
-- 比 SELECT COUNT(*) FROM likes WHERE post_id = ? 快几个数量级
|
||
```
|
||
|
||
> [!QUESTION] 冗余数据的同步问题怎么解决？
|
||
>
|
||
> 这是反范式最大的挑战，也是工程中最容易出 bug 的地方。以下是从简单到复杂的方案：
|
||
|
||
| 方案 | 实现难度 | 一致性 | 适合场景 |
|
||
|------|---------|--------|---------|
|
||
| **事务内同步更新** | ⭐ 简单 | 强一致 | 主表和冗余表在同一 DB |
|
||
| **异步最终一致** | ⭐⭐⭐ 复杂 | 最终一致 | 跨服务 / MQ 架构 |
|
||
| **应用层兜底** | ⭐⭐ 中等 | 周期性一致 | 定期修复工具巡检 |
|
||
| **触发器** | ⭐⭐ 中等 | 强一致 | 简单场景，但调试困难 |
|
||
|
||
> [!WARNING] 常见坑
|
||
>
|
||
> 1. **用户名变更**：用户改了名字，订单表里的历史订单 `username` 不同步——要么用事件溯源记录"下单时的快照"，要么在列表展示时用当前值覆盖
|
||
> 2. **并发写入冲突**：两个请求同时 `UPDATE like_count = like_count + 1`，在高并发下可能丢递增。**解决方案**：用 `INCR BY 1` 这类原子操作，或引入 Redis 累加再异步落库
|
||
> 3. **忘记同步**：新加的代码漏了冗余字段的更新逻辑。**预防手段**：把冗余字段的更新放在同一个 Service Method 内做单元测试
|
||
|
||
### 什么时候该反范式？
|
||
|
||
> [!NOTE] 反范式决策清单
|
||
>
|
||
> 满足以下任一条件时，考虑反范式化：
|
||
> - 某条查询路径上的 JOIN ≥ 4 且 P99 延迟 > 200ms
|
||
> - 报表/统计类接口需要实时聚合大量明细数据
|
||
> - 业务允许最终一致性（如阅读量、点赞数）
|
||
> - 历史数据只读不修改（冗余不影响历史正确性）
|
||
>
|
||
> 以下情况坚持规范化：
|
||
> - 核心交易链路对数据一致性要求极高
|
||
> - 写入 QPS > 1000，每次写入需要更新多个冗余表成为瓶颈
|
||
> - 团队规模小，缺乏数据一致性保障的基础设施（MQ、任务调度等）
|
||
|
||
## 回到开头：那道思考题的答案
|
||
|
||
> `order_id`, `user_name`, `user_dept`, `product_name`, `product_category`, `quantity`, `total`
|
||
|
||
这张表有 **三种范式违规**：
|
||
1. **违反 2NF**：如果主键是 `(order_id, product_name)`（复合 PK），那么 `quantity` 依赖整个主键没问题，但 `user_name`、`user_dept`、`product_category`、`total` 都只部分依赖——它们不依赖 `product_name`，也不完全依赖 `order_id`
|
||
2. **违反 3NF**：`user_dept` 通过 `user_name`（可关联到用户表）间接决定；`product_category` 通过 `product_name`（可关联到商品表）间接决定
|
||
3. **违反 3NF**：`total` 理论上由 `quantity × unit_price` 决定，属于传递依赖（应该去商品表取单价后计算）
|
||
|
||
这也解释了为什么电商系统通常会有 `orders → order_items → products` 这样的三层拆分结构。
|
||
|
||
## 关联笔记
|
||
|
||
- [[hhs/GORM/02-模型定义]] — GORM Struct Tag 与表结构的映射关系
|
||
- [[hhs/DEV/Go-Database]] — Go 中如何用 GORM 建模符合范式的数据库结构
|