MySQL 数据库设计规范
从三大范式的定义出发，用进货单拆分案例讲清规范化设计的完整推导过程，并给出业务优先原则下的反范式权衡依据。
目录
| 章节 | 说明 |
|---|---|
| 三大范式 | 1NF/2NF/3NF 定义与推导案例 |
| 业务优先原则 | 范式≠终点，适度冗余的场景 |
| ER 模型 | 实体-联系建模方法 |
| 字段类型选择规范 | 各类型的选择原则与陷阱 |
| 主键设计原则 | 自然主键 vs 代理主键 |
| 表结构优化技巧 | 数据类型压缩、冗余字段、垂直拆表 |
三大范式
第一范式（1NF）：字段不可再拆分
要求：数据表中所有字段都是不可拆分的基本数据项。
反例：字段 "property" = "规格:500ml/单位:瓶"
正例：拆分为 specification="500ml" 和 unit="瓶" 两个字段
第二范式（2NF）：非主键字段完全依赖主键
要求：在满足 1NF 的基础上，所有非主键字段必须完全依赖全部主键字段（消除部分依赖）。
推导案例：进货单表的拆分
原始表（主键为 listnumber + barcode）：
| listnumber | barcode | supplierid | suppliername | goodsname | qty | price |
|---|---|---|---|---|---|---|
| 3478 | 0001 | S01 | 供应商A | 书 | 10 | 5.00 |
| 3478 | 0002 | S01 | 供应商A | 笔 | 20 | 1.50 |
问题：supplierid、suppliername 只依赖 listnumber（主键的一部分），goodsname 只依赖 barcode（主键的另一部分）→ 部分依赖 → 数据冗余。
拆分为三张表：
-- 商品信息表（barcode 为主键或自增 itemnumber）
CREATE TABLE goodsmaster (
itemnumber INT PRIMARY KEY AUTO_INCREMENT,
barcode TEXT NOT NULL,
goodsname TEXT NOT NULL,
spec TEXT NOT NULL,
unit TEXT NOT NULL
);
-- 进货单头表
CREATE TABLE importhead (
listnumber VARCHAR(20) PRIMARY KEY,
supplierid INT NOT NULL,
suppliername TEXT NOT NULL,
stock TEXT NOT NULL
);
-- 进货单明细表（联合主键）
CREATE TABLE importdetail (
listnumber VARCHAR(20) NOT NULL,
itemnumber INT NOT NULL,
quantity DECIMAL(10,3) NOT NULL,
importprice DECIMAL(10,2) NOT NULL,
importvalue DECIMAL(10,2) NOT NULL,
PRIMARY KEY (listnumber, itemnumber)
);
第三范式（3NF）：非主键字段不依赖其他非主键字段
要求：在满足 2NF 的基础上，不能存在依赖于非主键字段的字段（消除传递依赖）。
问题：进货单头表中 suppliername 依赖于 supplierid（非主键）→ 传递依赖。
再拆分：
-- 供货商表
CREATE TABLE supplier (
supplierid INT PRIMARY KEY AUTO_INCREMENT,
suppliername TEXT NOT NULL
);
-- 进货单头表（去掉 suppliername）
CREATE TABLE importhead (
listnumber VARCHAR(20) PRIMARY KEY,
supplierid INT NOT NULL, -- 外键引用 supplier
stock TEXT NOT NULL
);
三范式总结
flowchart TD
A["原始大表<br/>所有字段混在一起"] -->|"1NF<br/>字段不可再拆"| B["字段原子化"]
B -->|"2NF<br/>完全依赖主键"| C["拆出部分依赖的字段<br/>形成新表"]
C -->|"3NF<br/>无传递依赖"| D["拆出依赖非主键字段的字段<br/>形成新表"]
style D fill:#cfc,stroke:#060
| 范式 | 解决的问题 | 判断标准 |
|---|---|---|
| 1NF | 字段粒度太粗 | 每个字段是最小信息单元 |
| 2NF | 联合主键表中的部分依赖 | 所有非主键字段依赖全部主键 |
| 3NF | 非主键字段间的传递依赖 | 没有"字段A → 字段B → 字段C"的链条 |
业务优先原则
范式不是终点，业务需求优先。
适度冗余的场景
案例：进货单明细表中 qty × importprice = importvalue，看似 importvalue 是冗余字段，但：
- 供应商按金额促销时，只有
importvalue，importprice是反算出来的 - 反算因四舍五入会累计误差，日积月累影响财务结果
→ 保留三个字段，虽然违反 3NF，但满足业务可靠性要求。
冗余字段合理增加的两个条件
- 不需要经常修改（修改成本低）
- 查询时不可或缺（避免高频 join）
典型案例：高频查询的流水表 join 商品表获取商品名称
→ 在流水表冗余存储商品名称字段
→ 代价：商品改名时需同步更新流水表
ER 模型
三要素
| 要素 | 说明 | ER 图表示 |
|---|---|---|
| 实体（Entity） | 可以独立存在的事物 | 矩形 |
| 属性（Attribute） | 实体的特征 | 椭圆 |
| 联系（Relationship） | 实体间的关系 | 菱形 |
联系的三种类型
| 类型 | 说明 | 示例 |
|---|---|---|
| 1:1 | 一对一 | 员工 ↔ 员工档案 |
| 1:N | 一对多 | 部门 → 员工（一个部门有多名员工） |
| M:N | 多对多 | 学生 ↔ 课程（需要中间表） |
M:N 关系的数据库实现
-- 学生表
CREATE TABLE student (id INT PRIMARY KEY, name VARCHAR(50));
-- 课程表
CREATE TABLE course (id INT PRIMARY KEY, name VARCHAR(100));
-- 选课中间表（M:N 拆解为两个 1:N）
CREATE TABLE enrollment (
student_id INT NOT NULL,
course_id INT NOT NULL,
score DECIMAL(5,2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
ER 建模流程
1. 确定系统的核心实体（名词 → 表）
2. 确定实体的属性（字段）
3. 确定实体间的联系（1:1 / 1:N / M:N）
4. 将联系转化为外键或中间表
5. 应用三大范式精简表结构
6. 根据业务优先原则做适度冗余
字段类型选择规范
整数类型
| 类型 | 字节 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT | 4 | -21 亿 ~ 21 亿 | 0 ~ 42.9 亿 |
| BIGINT | 8 | ±9.2×10¹⁸ | 0 ~ 1.8×10¹⁹ |
选择原则：先保证不溢出，再考虑节省空间。数据量小时用 INT，数据量大（百万级以上）时评估用 MEDIUMINT / SMALLINT 能否节省显著空间。
浮点数 vs 定点数
-- ❌ 不精确：FLOAT / DOUBLE 存在精度问题
CREATE TABLE t1 (price DOUBLE);
INSERT INTO t1 VALUES (0.1), (0.2);
SELECT 0.1 + 0.2 FROM t1; -- 结果可能是 0.30000000000000004
-- ✅ 精确：DECIMAL（定点数）
CREATE TABLE t2 (price DECIMAL(10, 2));
-- DECIMAL(10, 2)：总共 10 位数字，小数点后 2 位
规则：金额、价格等精度敏感字段必须用 DECIMAL，禁止用 FLOAT/DOUBLE。
字符串类型
| 类型 | 特点 | 适用场景 |
|---|---|---|
CHAR(N) | 定长，不足补空格，查询速度快 | 固定长度（手机号、邮编） |
VARCHAR(N) | 变长，节省空间 | 不定长文本（姓名、标题） |
TEXT | 变长大文本 | 长文章、JSON 内容 |
TEXT字段无法建完整索引（只能前缀索引），且排序时使用临时表，尽量避免在高频查询字段上使用 TEXT。
时间类型
| 类型 | 范围 | 精度 | 存储空间 |
|---|---|---|---|
DATE | 1000-01-01 ~ 9999-12-31 | 日 | 3 字节 |
DATETIME | 1000-01-01 00:00:00 ~ 9999-12-31 | 秒 | 8 字节 |
TIMESTAMP | 1970-01-01 ~ 2038-01-19 | 秒 | 4 字节 |
TIMESTAMP 有 2038 年问题，新系统建议用
DATETIME。TIMESTAMP会自动转换时区，跨时区系统慎用。
主键设计原则
自然主键 vs 代理主键
| 类型 | 说明 | 优缺点 |
|---|---|---|
| 自然主键 | 用业务字段做主键（如身份证号） | 有实际含义，但业务变化时可能需要改主键 |
| 代理主键 | 新增自增 ID 字段 | 稳定，但无业务含义 |
推荐代理主键：业务主键可能变化（如手机号换号），代理主键永远稳定，也避免字符串比较开销。
InnoDB 主键选择的影响
InnoDB 的数据按主键顺序存储（聚簇索引）：
- 主键应尽量顺序递增（自增 INT/BIGINT），避免页分裂
- UUID 做主键：随机写入导致频繁页分裂，性能差，不推荐
-- 推荐：自增主键
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
-- 不推荐：UUID（字符串比较慢，随机写入导致页分裂）
id CHAR(36) DEFAULT (UUID()) PRIMARY KEY
表结构优化技巧
数据类型压缩
对于大数据量表，字段类型选择影响显著：
案例：400 万条流水数据
字段 itemnumber：INT(4字节) → MEDIUMINT(3字节)，节省 25% 空间
字段 transuniqueid：TEXT(20字节) → BIGINT(8字节)，节省 60% 空间
导入速度：3分23秒 → 3分2秒
查询速度：5.18s → 3.86s
垂直拆分（大宽表拆分）
将一张宽表中高频访问字段和低频访问字段分开存储：
流水单头表（原始）：
会员编号、收款机、折扣（高频）+ 微信金额、支付宝金额、现金金额（低频）
拆分为：
流水单头表1：会员编号、收款机、折扣（高频）
流水单头表2：微信金额、支付宝金额、现金金额（低频）+ 外键
好处：每次查询读取的行数据更小，Buffer Pool 命中率更高。
非空约束
尽量给字段加 NOT NULL：
- 节省判断 NULL 的开销
- 更容易创建索引（NULL 值会让索引统计不准确）
- 节省少量存储空间（每个 NULL 字段需要 1 bit 标记位）
-- 推荐
name VARCHAR(50) NOT NULL DEFAULT ''
-- 不推荐（除非业务上确实需要区分 NULL 和空字符串）
name VARCHAR(50) DEFAULT NULL
参考资料
- 《MySQL 必知必会》— 第 22 讲：范式：如何消除冗余，实现高效存取？
- 《MySQL 必知必会》— 第 23 讲：ER 模型：如何理清数据库设计思路？
- 《MySQL 必知必会》— 第 25 讲：表太大了，如何设计才能提高性能？
- 《MySQL 必知必会》— 第 02 讲：字段：这么多字段类型，该怎么定义？
- MySQL - Data Types
评论 (0)