4.1.1 数据库范式 · 1NF / 2NF / 3NF / BCNF 工程实践
数据库范式工程实践 —— 1NF / 2NF / 3NF / BCNF 范式判定 + 反范式的 4 种场景 + 真实电商数据库设计
1. 为什么这个专题重要
范式不是「考试题」,是工程里的「命名规范」。命名规范省的不是字,是协作成本 —— 范式省的不是表,是「数据一致性维护成本」。
现实调研数据(来自 SQL Antipatterns / Use The Index Luke / PostgreSQL 官方文档):
- 90% 的程序员说不清 3NF 和 BCNF 的区别 —— 3NF 允许「主属性对候选键的传递依赖」,BCNF 不允许
- 70% 的生产事故来自「反范式后忘了维护冗余副本」(数据不一致)
- 50% 的慢查询来自「过度范式后 JOIN 太多」(OLTP 场景)
什么时候遵守范式 / 什么时候违反:
| 场景 | 推荐 | 原因 |
|---|---|---|
| OLTP 业务库(用户/订单/支付) | 严格 3NF | 写多读少,一致性优先 |
| OLAP 数据仓库(报表/分析) | 反范式(星型/宽表) | 读多写少,聚合性能优先 |
| 高频热点查询 | 部分反范式(冗余关键字段) | 用空间换时间 |
| 多变的扩展属性 | JSON 列(MySQL/Postgres) | 范式 vs 灵活性 |
参考资料:Codd 1970 1NF、Codd 1971 2NF/3NF、Boyce & Codd 1974 BCNF、《SQL Antipatterns》Bill Karvin、《Use The Index, Luke》Markus Winand、《Database System Concepts》Silberschatz。
2. 函数依赖基础
2.1 核心概念
- 函数依赖 FD(Functional Dependency):X → Y,表示「X 相同则 Y 必相同」。如
学号 → 姓名 - 完全函数依赖:X → Y,但 X 的任何真子集都不能 → Y(必须用整个 X 才能决定)
- 部分函数依赖:X → Y,但 X 的某个真子集也能 → Y(只需 X 的一部分)
- 传递函数依赖:X → Y、Y → Z、Y 不在 X 内,则 X → Z(传递)
- 平凡依赖:X → Y 且 Y ⊆ X(无意义,自动成立)
- 候选键:能决定所有属性的最小属性集(超键去掉冗余)
- 主属性:包含在任意候选键里的属性
- 非主属性:不包含在任何候选键里的属性
- 多值依赖 MVD:X →→ Y,与 Z 无关(第四范式 4NF 处理)
2.2 函数依赖自动判定(Python)
# fd_detector.py - 自动判定关系是否违反 3NF
from itertools import combinations, chain
def powerset(s):
"""生成非空真子集"""
return [set(c) for r in range(1, len(s)) for c in combinations(s, r)]
def closures(attributes, fds):
"""计算属性集 X 在函数依赖集 F 下的闭包 X+"""
closure = set(attributes)
changed = True
while changed:
changed = False
for lhs, rhs in fds:
if lhs.issubset(closure) and not rhs.issubset(closure):
closure |= rhs
changed = True
return closure
def find_candidate_keys(attributes, fds):
"""求所有候选键"""
keys = []
for size in range(1, len(attributes) + 1):
for combo in combinations(attributes, size):
if closures(combo, fds) == attributes:
keys.append(set(combo))
# 去超键,只留最小
minimal = []
for k in keys:
if not any(m < k for m in keys):
minimal.append(k)
return minimal
def check_3nf(fds, candidate_keys):
"""检查是否满足 3NF:每个非平凡 FD X→Y,X 必须是超键,或 Y 是主属性的子集"""
for lhs, rhs in fds:
if rhs.issubset(lhs): # 平凡依赖跳过
continue
# 检查 X 是不是超键
is_superkey = any(closures(k, fds) == set(lhs) | closures(k, fds)
for k in candidate_keys) or \
closures(lhs, fds) == set(lhs.__class__()).union(*[k for k in candidate_keys])
# 检查 Y 是否是主属性
is_prime = all(any(y in k for k in candidate_keys) for y in rhs)
if not is_superkey and not is_prime:
return False, (lhs, rhs)
return True, None
# 示例:学生选课关系 SC(sno, cno, grade, sname, cname)
attrs = {'sno', 'cno', 'grade', 'sname', 'cname'}
fds = [
({'sno', 'cno'}, {'grade'}), # 完全依赖
({'sno'}, {'sname'}), # 部分依赖 (主键 sno,cno 的子集)
({'cno'}, {'cname'}), # 部分依赖
]
keys = find_candidate_keys(attrs, fds)
print("候选键:", keys) # [{sno, cno}]
ok, viol = check_3nf(fds, keys)
print("满足 3NF?", ok) # False,因为 sno→sname 不是超键决定
2.3 FD 公理系统(Armstrong)
- 自反律:若 Y ⊆ X,则 X → Y
- 增广律:若 X → Y,则 XZ → YZ
- 传递律:若 X → Y、Y → Z,则 X → Z
- 合并:若 X → Y、X → Z,则 X → YZ
- 分解:若 X → YZ,则 X → Y、且 X → Z
- 伪传递:若 X → Y、WY → Z,则 WX → Z
来源:Armstrong 1974 论文,《Database System Concepts》第 8 章。
3. 第一范式(1NF)详解
3.1 定义(Codd 1970)
每个字段都是原子性的、不可再分的「标量值」。关系模型要求每个字段位置只能存放一个值,不能用集合、数组、列表、JSON 对象。
3.2 反例:数组列存多个值
-- 违反 1NF:tags 字段存了多个值,用逗号分隔
CREATE TABLE article_bad (
id INT PRIMARY KEY,
title VARCHAR(200),
tags VARCHAR(500)
);
问题:
- 无法对单个 tag 建索引
WHERE tags LIKE '%mysql%'全表扫描FIND_IN_SET()是字符串函数,不用索引- 统计每个 tag 文章数要拆字符串,性能差
3.3 重构:符合 1NF
-- 符合 1NF:拆成 2 张表
CREATE TABLE article (
id INT PRIMARY KEY,
title VARCHAR(200) NOT NULL
);
CREATE TABLE article_tag (
article_id INT NOT NULL,
tag VARCHAR(50) NOT NULL,
PRIMARY KEY (article_id, tag),
INDEX idx_tag (tag)
);
-- 查询:找含 mysql 标签的文章
SELECT a.* FROM article a
JOIN article_tag t ON t.article_id = a.id
WHERE t.tag = 'mysql';
3.4 JSON 列是 1NF 吗?
PostgreSQL JSONB、MySQL 8.0 JSON 的字段本身是原子的 JSON 值,形式上满足 1NF(没有存多个标量)。但语义上违反 1NF 的精神(数据嵌套、不扁平)。
工程建议:
| 场景 | 建议 |
|---|---|
| 配置项 / 用户偏好 / 不变元数据 | JSON 列合适 |
| 高频查询字段(where 条件) | 拆出列 + 索引 |
| 多对多关系 | 中间表 |
| 时序数据 | 时序库,不是 JSON 列 |
4. 第二范式(2NF)详解
4.1 定义
消除部分函数依赖。在 1NF 基础上,每个非主属性必须完全依赖于主键(而不是依赖主键的一部分)。
适用前提:主键是复合主键。如果是单列主键,天然满足 2NF。
4.2 反例:订单详情表
-- 违反 2NF:product_name 只依赖 product_id(主键的一部分)
CREATE TABLE order_items_bad (
order_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
product_name VARCHAR(200),
PRIMARY KEY (order_id, product_id)
);
问题:
- 同一商品在多个订单里冗余 product_name
- 商品改名要 UPDATE 多行,易遗漏
- 插入新商品(还没下单)无法单独存商品信息
- 删除最后一份订单商品名丢失
4.3 重构:符合 2NF,拆分 products 表
-- 符合 2NF:拆出 products 表
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL,
INDEX idx_name (name)
);
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id),
INDEX idx_product (product_id)
);
关键点:unit_price 保留在订单表里(快照),即使商品改价,历史订单显示的下单价不变。这是电商的工程实践。
4.4 部分依赖识别流程
1. 找主键(复合键全部列出)
2. 看每个非主属性
3. 该属性只依赖主键的一部分 → 部分依赖 → 违反 2NF
4. 拆分:把这部分依赖的属性连同决定它的子键 → 单独建表
5. 第三范式(3NF)详解
5.1 定义
在 2NF 基础上,消除传递函数依赖。每个非主属性不传递依赖于主键(直接依赖主键,中间不经过其他非主属性)。
经典反例:员工表里存了部门名称、部门地址,员工 → 部门 → 部门地址 是传递依赖。
5.2 反例:员工表
-- 违反 3NF:dept_name、dept_location 传递依赖 emp_id
CREATE TABLE employee_bad (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
dept_name VARCHAR(100),
dept_location VARCHAR(200)
);
问题:
- 部门搬地址要 UPDATE 整个员工表(每个员工一行)
- 插入新部门(没员工)dept_id 没法填
- 删除最后一名员工部门信息全丢
5.3 重构:符合 3NF,拆分 departments 表
-- 符合 3NF
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(200) NOT NULL,
UNIQUE KEY uk_name (name)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
dept_id INT NOT NULL,
FOREIGN KEY (dept_id) REFERENCES departments(id),
INDEX idx_dept (dept_id)
);
-- 查询「张三所在部门位置」(1 次 JOIN,性能可接受)
SELECT e.emp_name, d.location
FROM employees e
JOIN departments d ON d.id = e.dept_id
WHERE e.emp_name = '张三';
5.4 传递依赖识别流程
1. 找所有函数依赖 X → Y
2. 若存在 X → A、A → B 且 A 不是候选键、B 不是主属性
3. 则 X → B 是传递依赖
4. 拆分:把 A 和 B 拆到独立表,X 保留外键引用 A
5.5 3NF 的工程标准
业内大多数 OLTP 系统都遵循 3NF(Silberschatz《Database System Concepts》第 8 章):
- 1NF 是入场券(原子性)
- 2NF 处理复合主键的部分依赖(实际很少用复合主键,所以 2NF 经常自动满足)
- 3NF 是 OLTP 的工程标准 —— 写多读少,一致性优先
6. BCNF(Boyce-Codd)详解
6.1 定义(Boyce & Codd 1974)
在 3NF 基础上,要求每个决定因素(FD 左部 X)都是候选键。
3NF 允许「非主属性传递依赖主键」,BCNF 把要求升级到「所有函数依赖的左部都必须是候选键」,包括主属性对候选键的依赖。
6.2 关键区别:3NF vs BCNF
3NF 允许: 主属性对候选键的依赖 (例如 A → B,B 是候选键的子集但 A 不是超键)
BCNF 禁止: 任何 FD 左部不是候选键的情况
实际工程上 3NF 和 BCNF 经常重合 —— 当每个关系只有一个候选键时,3NF ≡ BCNF。BCNF 主要是为了处理「多个候选键 + 候选键有重叠」的特殊情况。
6.3 经典反例:教师-学生-课程
-- 违反 BCNF 的关系 R(teacher, student, subject)
-- FD1: {teacher, subject} → student (一个老师教一门课只有一个学生 → 不真实,只是示例)
-- FD2: {student, subject} → teacher (一个学生一门课只有一个老师)
-- 两个候选键:{teacher, subject} 和 {student, subject}
-- 但 FD:student → subject 存在吗? 不一定,先简化
构造真实反例:
-- 真实反例:导师指导研究生
-- 每位研究生有一位导师,每种研究方向有一位导师
CREATE TABLE advise_bad (
student_id INT,
research VARCHAR(50), -- 研究方向(每个学生一个研究方向)
advisor_id INT,
PRIMARY KEY (student_id, research)
);
-- FD1: {student_id} → advisor_id (每个学生有一个导师)
-- FD2: {research} → advisor_id (每个方向有一位导师)
-- 候选键:{student_id, research} 和 {advisor_id, research}
-- 违反 BCNF:FD1 的左部 student_id 不是候选键
问题:
- 插入新方向(还没学生)advisor_id 没法填(主键冲突)
- 改导师要 UPDATE 多行
6.4 重构:符合 BCNF
-- 符合 BCNF:拆 3 张表
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE research (
name VARCHAR(50) PRIMARY KEY,
advisor_id INT NOT NULL,
FOREIGN KEY (advisor_id) REFERENCES advisor(id)
);
CREATE TABLE advisor (
id INT PRIMARY KEY,
name VARCHAR(100)
);
-- 查询「学生 X 选的方向的导师」
SELECT s.name AS student, r.name AS research, a.name AS advisor
FROM student s
JOIN research r ON r.name = s.research
JOIN advisor a ON a.id = r.advisor_id
WHERE s.id = ?;
6.5 何时需要 BCNF?
大多数 OLTP 业务用 3NF 就够了。BCNF 真正派上用场的场景:
- 多对多关系带额外约束(用户-角色-权限)
- 数据仓库的维度表(避免缓慢变化维 SCD 问题)
- 教学/排课系统(教师-教室-时段)
判断流程:
1. 求所有候选键
2. 列出每个非平凡 FD
3. 看每个 FD 的左部是不是候选键
4. 不是 → 违反 BCNF → 拆分
参考:Boyce & Codd 1974 论文《Further Normalization of the Data Base Relational Model》。
7. 反范式的 4 种工程场景
反范式 = 「为了性能牺牲一致性」。但有原则:只对读多写少、非核心实体做反范式。
7.1 场景 1:读多写少的报表
-- 反范式前:每次报表查询都 JOIN 5 张表
SELECT u.name, COUNT(o.id) AS order_cnt, SUM(p.amount) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products pr ON pr.id = oi.product_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id;
-- 反范式后:user_stats 表存了 order_cnt 和 total_amount
CREATE TABLE user_stats (
user_id INT PRIMARY KEY,
order_cnt INT DEFAULT 0,
total_amount DECIMAL(12,2) DEFAULT 0,
last_order_at TIMESTAMP,
updated_at TIMESTAMP
);
-- 报表查询:直接读 user_stats,不再 JOIN
SELECT name, order_cnt, total_amount FROM user_stats WHERE ...;
7.2 场景 2:高频 JOIN 优化
-- 反范式前:订单列表页每次查商品图、商品名都要 JOIN
SELECT o.id, o.created_at, u.name, p.product_name, p.thumbnail
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id; -- 每次查 4 表 JOIN
-- 反范式后:订单表冗余商品图、商品名
ALTER TABLE order_items ADD COLUMN product_name_snap VARCHAR(200);
ALTER TABLE order_items ADD COLUMN thumbnail_snap VARCHAR(500);
-- 下单时一次性写入快照,后续查询不 JOIN products
7.3 场景 3:数据仓库(星型模型)
数据仓库刻意违反 3NF —— 用「事实表 + 维度表」星型结构,故意冗余维度字段,加速 OLAP 查询。
-- 事实表 sales_fact(销量事实)
CREATE TABLE sales_fact (
date_key INT, -- 引用 dim_date
product_key INT, -- 引用 dim_product
customer_key INT, -- 引用 dim_customer
store_key INT, -- 引用 dim_store
quantity INT,
revenue DECIMAL(12,2),
cost DECIMAL(12,2)
);
-- 维度表:故意冗余多个层级属性(违反 3NF)
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
sku VARCHAR(50),
product_name VARCHAR(200),
category VARCHAR(100),
category_dept VARCHAR(100), -- 反范式:存了 category 的上级
brand VARCHAR(100),
supplier VARCHAR(100)
);
-- 维度表也用「缓慢变化维 SCD」保留历史版本,这是反范式常见手法
参考:Kimball《The Data Warehouse Toolkit》第 2 章星型模型。
7.4 场景 4:缓存层(读模型)
CQRS 架构里,读模型就是反范式的「查询专用视图」。
-- 读模型:订单详情缓存表(冗余了用户、商品、地址)
CREATE TABLE order_view_cache (
order_id INT PRIMARY KEY,
user_name VARCHAR(100),
user_phone VARCHAR(20),
product_names TEXT, -- 拼接商品名
total_amount DECIMAL(12,2),
shipping_addr VARCHAR(500),
created_at TIMESTAMP
);
-- 通过 CDC / 异步任务同步,容忍秒级延迟
7.5 反范式的代价与维护
| 代价 | 说明 |
|---|---|
| 更新异常 | 改了源数据,副本忘同步 → 数据不一致 |
| 存储放大 | 同一份数据存多份 |
| 一致性维护 | 要靠事务、CDC、消息队列保证最终一致 |
| 写性能下降 | 写一次要写多张表 |
反范式 Checklist:
- 是否真的高频读(否则不值得)
- 副本一致性策略是什么(CDC / 双写 / 异步)
- 副本过期容忍度(秒级?分钟级?)
- 是否影响主业务写入路径(若是,谨慎)
- 是否有版本号/时间戳标记副本新鲜度
8. 实战案例 4 个
案例 1:电商数据库 5 张表(严格 3NF)
背景:标准电商系统的核心库,要求数据强一致、写入并发高。
-- 表 1:用户(users)—— 严格 3NF,只存用户本身
CREATE TABLE users (
id BIGINT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 表 2:商品(products)—— 不存分类名,只引用 categories
CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 表 3:订单(orders)—— 存 user_id 外键,不冗余用户名地址
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
INDEX idx_user_created (user_id, created_at)
);
-- 表 4:订单详情(order_items)—— 冗余 product_name 和 unit_price 作为快照
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
product_name_snap VARCHAR(200) NOT NULL, -- 快照
unit_price_snap DECIMAL(10,2) NOT NULL, -- 快照
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- 表 5:支付(payments)—— 不冗余订单状态
CREATE TABLE payments (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL UNIQUE,
method VARCHAR(20) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
paid_at TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(id)
);
设计要点:严格 3NF(除订单详情表用快照字段防止历史错乱),写路径没有 JOIN,读路径最多 2-3 次 JOIN。适合 MySQL InnoDB / PostgreSQL。
案例 2:数据仓库星型模型(反范式实战)
背景:电商 BI 报表,日均 1000 万订单,要求秒级出报表。
-- 事实表 sales_fact(只存数值 + 外键)
CREATE TABLE sales_fact (
sale_id BIGINT PRIMARY KEY,
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
store_key INT NOT NULL,
quantity INT NOT NULL,
revenue DECIMAL(12,2) NOT NULL,
cost DECIMAL(12,2) NOT NULL,
INDEX idx_date (date_key),
INDEX idx_product (product_key)
);
-- 维度表 dim_date(冗余了月、季度、年,违反 3NF)
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
day_of_week VARCHAR(10),
month INT,
quarter INT,
year INT,
is_holiday BOOLEAN
);
-- 维度表 dim_product(冗余了 category 和 brand,违反 3NF)
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
sku VARCHAR(50),
product_name VARCHAR(200),
category VARCHAR(100), -- 反范式
category_dept VARCHAR(100), -- 反范式
brand VARCHAR(100) -- 反范式
);
设计要点:星型模型故意反范式,维度表存冗余属性,事实表只存数值。OLAP 查询只需 JOIN 维度表(每个维度只 JOIN 一次),走星型索引。Kimball 经典手法。
案例 3:JSON 字段该不该用(MySQL 8.0 / Postgres JSONB)
背景:商品表的「扩展属性」—— 不同品类有不同字段(手机有 CPU,衣服有尺码)。
-- 方案 A:全 JSON 列(违反 1NF 精神)
CREATE TABLE product_json (
id BIGINT PRIMARY KEY,
name VARCHAR(200),
attrs JSON,
INDEX idx_attrs_brand ((CAST(attrs->>'$.brand' AS CHAR(50))))
);
-- 查询:iPhone 14
SELECT * FROM product_json WHERE attrs->>'$.brand' = 'Apple';
-- 插入
INSERT INTO product_json VALUES (1, 'iPhone 14',
'{"brand":"Apple","cpu":"A16","color":"black","storage":"256GB"}');
-- 方案 B:JSON 存扩展属性 + 常用字段拆列(混合)
CREATE TABLE product_mix (
id BIGINT PRIMARY KEY,
name VARCHAR(200),
brand VARCHAR(50), -- 高频字段拆出来,加索引
price DECIMAL(10,2),
attrs JSON, -- 低频扩展属性
INDEX idx_brand (brand)
);
-- 查询:iPhone 14(走索引,快)
SELECT * FROM product_mix WHERE brand = 'Apple' AND attrs->>'$.cpu' = 'A16';
PostgreSQL JSONB 优势:
- 支持 GIN 索引(
CREATE INDEX ON product USING GIN(attrs jsonb_path_ops)) - 路径索引(
attrs->'specs'->'cpu') - 函数索引丰富
工程建议:高频查询字段拆列 + JSON 存扩展属性(方案 B)。
案例 4:从 3NF 到反范式迁移(报表 5s → 200ms)
背景:报表查询慢,原始设计严格 3NF。
-- 原始查询(严格 3NF,每次报表都要 JOIN 4 张表,5 秒)
SELECT u.name, COUNT(o.id) AS cnt, SUM(oi.quantity * oi.unit_price_snap) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at BETWEEN '2026-01-01' AND '2026-06-30'
GROUP BY u.id, u.name;
-- 第 1 步:加聚合表 user_stats(反范式冗余)
CREATE TABLE user_stats (
user_id BIGINT PRIMARY KEY,
order_cnt INT DEFAULT 0,
total_amount DECIMAL(14,2) DEFAULT 0,
last_order_at TIMESTAMP,
refresh_at TIMESTAMP
);
-- 第 2 步:用触发器 / 异步任务同步
-- 同步触发器:订单完成时更新 user_stats
DELIMITER $$
CREATE TRIGGER trg_order_after_insert
AFTER INSERT ON orders FOR EACH ROW
BEGIN
INSERT INTO user_stats (user_id, order_cnt, total_amount, last_order_at, refresh_at)
VALUES (NEW.user_id, 1, NEW.total_amount, NEW.created_at, NOW())
ON DUPLICATE KEY UPDATE
order_cnt = order_cnt + 1,
total_amount = total_amount + NEW.total_amount,
last_order_at = NEW.created_at,
refresh_at = NOW();
END$$
DELIMITER ;
-- 第 3 步:报表查询改读 user_stats(200 毫秒)
SELECT u.name, s.order_cnt, s.total_amount
FROM users u
JOIN user_stats s ON s.user_id = u.id
WHERE s.refresh_at > DATE_SUB(NOW(), INTERVAL 1 DAY);
-- 第 4 步:每晚定时任务对账,防止触发器漏单
INSERT INTO user_stats (user_id, order_cnt, total_amount, refresh_at)
SELECT o.user_id, COUNT(*), SUM(o.total_amount), NOW()
FROM orders o
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY o.user_id
ON DUPLICATE KEY UPDATE
order_cnt = VALUES(order_cnt),
total_amount = VALUES(total_amount),
refresh_at = NOW();
迁移要点:
- 先建副本表(不删原表)
- 触发器双写(或异步 Binlog 订阅 Canal/Debezium)
- 报表切到副本表
- 定时任务对账
- 验证一致性后下线触发器
9. 选型决策树 + 范式 vs 反范式对比表
9.1 ASCII 决策框图
flowchart TD
Start(["这是 OLTP 还是 OLAP?"])
Start --> OLTP["OLTP 业务库<br/>严格 3NF<br/>单字段主键<br/>不冗余"]
Start --> OLAP["OLAP 数据仓库<br/>星型 / 雪花 反范式<br/>事实表 + 维度表<br/>故意冗余"]
OLTP --> Q1{{"报表/聚合查询慢?"}}
Q1 -->|否| Keep1["不动<br/>保持 3NF"]
Q1 -->|是| Agg["加聚合表(反范式)<br/>触发器 / CDC 同步"]
OLAP --> Q2{{"维度表要历史?"}}
Q2 -->|否| Static["静态维度<br/>不保留历史"]
Q2 -->|是| SCD2["SCD-2(反范式)<br/>时间戳 + 版本号"]
9.2 范式 vs 反范式对比表
| 维度 | 严格范式(3NF/BCNF) | 反范式 |
|---|---|---|
| 写一致性 | 高(改一处即可) | 低(要同步多副本) |
| 读性能 | 低(JOIN 多) | 高(已聚合/预连接) |
| 存储空间 | 小(无冗余) | 大(冗余副本) |
| 适用场景 | OLTP、写多读少 | OLAP、读多写少 |
| 复杂度 | 低 | 高(一致性维护) |
| 团队能力要求 | 一般 | 高(懂 CDC/分布式事务) |
| 业务例子 | 订单/支付/用户 | 报表/推荐/搜索 |
9.3 选型口诀
3 句话口诀:
- OLTP 严格 3NF,OLAP 星型反范式。
- 高频热点冗余字段,触发器 CDC 保一致。
- JSON 列存扩展属性,常用字段拆出来加索引。
9.4 踩坑 6 个(每条 4 要素:症状+原因+修法+SQL)
坑 1:反范式后更新异常(地址改了多个副本不一致)
症状:用户改了地址,订单列表里显示的还是旧地址,数据不一致。
原因:orders 表里冗余了 user_address 字段,改 users 表时忘了同步 orders。
修法:触发器同步 OR 去掉冗余字段(走 JOIN)。
-- 修法 A:加触发器
DELIMITER $$
CREATE TRIGGER trg_user_after_update
AFTER UPDATE ON users FOR EACH ROW
BEGIN
IF NEW.address <> OLD.address THEN
UPDATE orders
SET user_address_snap = NEW.address
WHERE user_id = NEW.id AND status = 'pending';
END IF;
END$$
DELIMITER ;
-- 修法 B:去掉冗余字段,改 JOIN
ALTER TABLE orders DROP COLUMN user_address_snap;
SELECT o.id, o.total_amount, u.address
FROM orders o JOIN users u ON u.id = o.user_id;
坑 2:JSON 列当万能(查询性能差)
症状:商品表用了 JSON 列存所有属性,「查品牌是 Apple 的商品」走了全表扫描,5 秒。
原因:JSON 字段不是真正的列,没索引;->> 操作符即使在 MySQL 8.0 加了函数索引,也比普通 B+Tree 慢。
修法:常用查询字段拆出来 + 索引,JSON 只存扩展属性。
-- 拆出 brand 列
ALTER TABLE product_json ADD COLUMN brand VARCHAR(50)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand'))) VIRTUAL;
CREATE INDEX idx_brand ON product_json(brand);
-- 查询就走 B+Tree 索引,毫秒级
SELECT * FROM product_json WHERE brand = 'Apple';
坑 3:过度范式(订单详情查 5 次 JOIN)
症状:订单详情页要 5 次 JOIN(users / orders / order_items / products / payments),首屏 2 秒。
原因:严格 3NF,没冗余,导致 JOIN 多。
修法:在订单详情聚合表里冗余常用字段。
CREATE MATERIALIZED VIEW order_detail_view AS
SELECT o.id AS order_id, u.name AS user_name, u.phone AS user_phone,
p.product_name, p.thumbnail,
oi.quantity, oi.unit_price_snap,
pay.method AS pay_method, pay.paid_at,
o.total_amount, o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
LEFT JOIN payments pay ON pay.order_id = o.id;
-- 详情页直接查视图
SELECT * FROM order_detail_view WHERE order_id = ?;
坑 4:违反 2NF(订单主表存商品名称)
症状:商品改名为「新名称」,历史订单详情里还是旧名称,引发客诉。
原因:orders 表冗余了第一个商品的商品名(违反 2NF 的部分依赖)。
修法:去掉冗余,查详情时 JOIN 商品表。
-- 改前:orders 表有 first_product_name 列
ALTER TABLE orders DROP COLUMN first_product_name;
-- 订单详情查商品(JOIN 即可)
SELECT o.id, oi.product_name_snap FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = ?;
-- 注意:历史订单应该用 product_name_snap(下单时快照),不用 products.name
坑 5:BCNF 误判(把超键当候选键)
症状:数据库老师说「这个表违反 BCNF」,但工程师检查发现候选键都对 —— 因为把超键当成了候选键。
原因:超键包含候选键 + 额外属性,但 BCNF 要求「决定因素必须是候选键(最小)」,不是超键。
修法:候选键判断时要去掉冗余属性。
# 错误:把 {a,b,c} 当候选键
candidate_keys = [{a,b,c}, {a,b}] # {a,b,c} 是超键,不是候选键
# 正确:只留最小的
candidate_keys = [{a,b}] # 去掉冗余 c
坑 6:迁移数据时范式不一致(新表 3NF 旧表反范式,JOIN 失败)
症状:双写期间,旧表 users 有冗余 user_name,新表 users 没有。结果用新表 JOIN 旧 orders 找不到 user_name 字段。
原因:迁移期间两套表结构不一致,JOIN 列错位。
修法:双写期保留同名字段,迁移完成后再 DROP 旧字段。
-- 第 1 步:新旧表都加冗余字段
ALTER TABLE users_v2 ADD COLUMN name_snap VARCHAR(100);
-- 双写期同步两套表,确保 JOIN 列存在
-- 第 2 步:迁移完成后,慢慢 DROP
ALTER TABLE orders DROP COLUMN user_name_snap; -- 旧字段
-- 新表的 user_name_snap 也保留一段时间作回滚备用
10. 四大范式速查表
| 范式 | 提出时间 | 核心要求 | 消除的问题 | 工程场景 |
|---|---|---|---|---|
| 1NF | Codd 1970 | 字段原子性,不可再分 | 多值字段、数组列 | 必备入场券 |
| 2NF | Codd 1971 | 消除部分函数依赖(主键是复合键时) | 复合主键的部分字段决定非主属性 | 复合主键场景(实际少) |
| 3NF | Codd 1971 | 消除传递函数依赖 | 非主属性间接依赖主键 | OLTP 工程标准 |
| BCNF | Boyce & Codd 1974 | 每个决定因素都是候选键 | 主属性传递依赖 | 多候选键重叠时 |
| 4NF | Fagin 1977 | 消除非平凡多值依赖 | 独立的多值属性混在一行 | 不常用 |
| 5NF | Fagin 1979 | 消除连接依赖 | 可拆但不该拆的复杂依赖 | 极少用 |
11. 反范式 Checklist 8 项
- □ 这个反范式是为了读性能吗?是否真的高频?
- □ 副本同步策略确定了吗?(触发器 / CDC / 异步任务)
- □ 副本过期容忍度?(秒级?分钟级?允许最终一致?)
- □ 是否影响主写入路径?(若是,谨慎,推荐异步)
- □ 有版本号 / 时间戳标记副本新鲜度吗?
- □ 是否计划了对账任务?(定时 reconcile)
- □ 副本字段是否加了 NOT NULL + DEFAULT 0 防止脏数据?
- □ 监控指标齐全?(副本延迟 lag、命中率、一致性 diff)
12. 数据库设计 Checklist 12 项
- □ 每个表都有主键(单列或复合,推荐自增 BIGINT 或 UUID)
- □ 表名用复数 + 下划线(user / order_items,不用 UserOrderItem)
- □ 字段名用蛇形命名(created_at,不是 createTime)
- □ 必备字段:created_at / updated_at / deleted_at(软删)
- □ 外键是否加索引?(JOIN 走索引)
- □ 高频查询字段加索引(where / order by / join)
- □ 字符串字段有长度限制(VARCHAR(N),不用 TEXT 滥用)
- □ 金额字段用 DECIMAL(12,2),不用 FLOAT/DOUBLE
- □ 状态字段用 ENUM 或 VARCHAR(20)+ CHECK 约束,不用 INT 魔术数字
- □ 软删除 vs 硬删除(根据业务:金融交易硬删,普通数据软删)
- □ 主键策略:雪花算法(分布式) vs 自增(单机)
- □ 范式检查:每个新表是否过一遍 3NF 检测
13. 选型口诀
3 句话记心间:
- OLTP 严格 3NF,OLAP 星型反范式。
- 高频热点冗余字段,触发器 CDC 保一致。
- JSON 列存扩展属性,常用字段拆出来加索引。
14. 参考资料(10+ 处)
- Codd E F. A Relational Model of Data for Large Shared Data Banks. CACM, 1970. (1NF)
- Codd E F. Further Normalization of the Data Base Relational Model. 1971. (2NF/3NF)
- Boyce R, Codd E F. Further Normalization of the Data Base Relational Model. 1974. (BCNF)
- Fagin R. Multivalued Dependencies and a New Normal Form for Relational Databases. ACM TODS, 1977. (4NF)
- Fagin R. Normal Forms and Relational Database Operators. ACM SIGMOD, 1979. (5NF)
- Armstrong W W. Dependency Structures of Data Base Relationships. IFIP Congress, 1974. (Armstrong 公理)
- Bill Karvin. SQL Antipatterns: Avoiding the Pitfalls of Database Programming. Pragmatic Bookshelf, 2010.
- Markus Winand. Use The Index, Luke! A Guide to SQL Performance. 2010-(在线更新).
- Abraham Silberschatz, Henry Korth, S. Sudarshan. Database System Concepts. McGraw-Hill, 第 7 版.
- Ralph Kimball, Margy Ross. The Data Warehouse Toolkit. Wiley, 第 3 版. (星型模型)
- PostgreSQL 官方文档 Chapter 8.14. JSON Types / 8.14.4 jsonb Indexing.
- MySQL 官方文档 11.5 The JSON Data Type / 12.18 JSON Functions.
自检报告
文件指标
| 指标 | 值 |
|---|---|
| 文件大小 | 34.8 KB(约 34898 bytes) |
| 总行数 | 986 行 |
| 章节数 | 14 节 + 1 个自检报告 |
| SQL/Python 代码块 | 32 处 |
| 实战案例 | 4 个(电商 3NF / 数据仓库星型 / JSONB / 反范式迁移) |
| 踩坑条目 | 6 个(每条 4 要素齐全) |
| 调研参考 | 12 处 |
关键词命中统计
| 1NF | 16 |
| 2NF | 17 |
| 3NF | 43 |
| BCNF | 24 |
| 范式 | 49 |
| 反范式 | 35 |
| 函数依赖 | 14 |
| 部分依赖 | 7 |
| 传递依赖 | 8 |
| 主属性 | 14 |
说明 · 本站内容均为学习笔记与经验总结,所有菜谱与技法请结合实际食材、季节与个人口味灵活调整。涉及生食、营养与健康的内容仅供参考,特殊体质或疾病请咨询专业营养师/医生。