MySQL 表设计范式

前言

很多开发者在刚接触数据库时，建表全凭"感觉"：业务需要什么字段就一股脑塞进一张表，能用就行。结果表越来越大、字段越来越多，出现一堆重复数据，改一个地方要连带改十几条记录，删一条记录又把别的信息一起删没了。这些问题，本质上都是表设计不符合范式导致的。

范式（Normal Form）是一套关于"如何把一张表拆得合理、干净"的规则。它不是玄学，而是一套被反复验证过的工程经验，核心目标只有一个：减少数据冗余，避免增删改带来的各种异常。

本文会用生活化的类比，从第一范式讲到 BCNF，每个范式都配正反两面的建表 SQL 和问题分析，最后用一个电商案例带你完整走一遍"从单表到规范多表"的落地过程。

一、范式到底在解决什么问题

在进入具体范式之前，先想清楚一件事：为什么不把数据全放一张表里？

想象一个快递包裹：一个包裹上写着收件人、寄件人、物品、金额、物流轨迹、派件员、网点……全写在一张贴纸上，乍一看省事，但问题来了：

同一个寄件人寄 100 个包裹，他的姓名电话就要抄 100 遍（数据冗余）。
寄件人换了电话号码，得把 100 张单子全部改一遍（更新异常）。
想新增一个还没寄过东西的客户，没地方记，因为单子上必须有"物品"才能生成（插入异常）。
派件员离职了，把他名字从单子上删掉，结果连包裹信息也一起丢了（删除异常）。

数据库范式要解决的，正是这四类问题。范式从低到高依次是：

范式	核心要求	一句话记忆
第一范式（1NF）	字段不可再分	每个格子只能装一个值
第二范式（2NF）	消除部分依赖	非主键字段要完全依赖整个主键
第三范式（3NF）	消除传递依赖	非主键字段不能依赖另一个非主键字段
BCNF	消除主属性间的依赖	主键字段之间也不能互相依赖

越往后的范式约束越严格，表被拆得越细，冗余越少，但联表查询也越多。后面会专门讲"什么时候可以故意不遵守范式"。

二、第一范式（1NF）：字段不可再分

2.1 用生活类比理解 1NF

1NF 是所有范式的基础，要求很简单：表中每个字段都必须是"原子"的，一个格子里只能放一个值。

就像填快递单，姓名一栏只写一个名字，不能写"张三、李四"；电话一栏只写一个号码，不能写"138xxxx / 139xxxx"。如果一栏里塞了好几个信息，系统就没法单独定位到某一个值，查询、统计都会变得一团糟。

2.2 违反 1NF 的错误设计

下面是一张"学生信息表"，把多个联系电话、多门选修课程都塞进了同一个字段里：

sql

-- 错误示例：一个字段里存多个值，违反 1NF
CREATE TABLE student_bad (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20),            -- 学生姓名
    phone    VARCHAR(100),           -- 联系方式：'13811112222,13933334444' 存了多个电话
    courses  VARCHAR(200)            -- 选修课程：'数学,英语,物理' 存了多门课程
);

插入的数据长这样：

sql

INSERT INTO student_bad (name, phone, courses) VALUES
('张三', '13811112222,13933334444', '数学,英语,物理'),
('李四', '13755556666', '数学,化学');

2.3 这样设计会引发什么问题

无法按单个值查询：想查"所有选修了数学的学生"，WHERE courses LIKE '%数学%' 虽然能查，但既慢又容易误伤（"高等数学"也会被匹配出来）。
无法按单个值统计：想统计"每门课有多少人",得先把逗号拆开，MySQL 里只能用 FIND_IN_SET 或字符串函数硬算，又慢又绕。
数据冗余与不一致：同一个电话、同一门课在多个格子里反复出现，改起来容易漏。
无法对单个值建索引：courses 这种拼接字段即使建了索引，范围查询和精确匹配都用不上。

2.4 符合 1NF 的优化设计

核心思路：把"一对多"的信息拆成单独的表，一行只存一个值。

sql

-- 学生表：只保留学生本身的信息
CREATE TABLE student (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(20) NOT NULL
);

-- 学生电话表：一个学生多个电话，就拆成多行
CREATE TABLE student_phone (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    student_id  INT NOT NULL,
    phone       VARCHAR(20) NOT NULL,
    UNIQUE KEY uk_student_phone (student_id, phone),
    KEY idx_student (student_id)
);

-- 学生选课表：一个学生选多门课，就拆成多行
CREATE TABLE student_course (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    student_id  INT NOT NULL,
    course_name VARCHAR(50) NOT NULL,
    KEY idx_student (student_id)
);

对应数据：

sql

INSERT INTO student (name) VALUES ('张三'), ('李四');

INSERT INTO student_phone (student_id, phone) VALUES
(1, '13811112222'), (1, '13933334444'), (2, '13755556666');

INSERT INTO student_course (student_id, course_name) VALUES
(1, '数学'), (1, '英语'), (1, '物理'), (2, '数学'), (2, '化学');

2.5 优化解决了哪些问题

查询"选修数学的学生"：SELECT DISTINCT student_id FROM student_course WHERE course_name = '数学'，精确命中、可走索引。
统计每门课人数：SELECT course_name, COUNT(*) FROM student_course GROUP BY course_name，一句搞定。
每个电话、每门课都是独立一行，增删改只动一行，不再互相牵连。

一句话总结 1NF：每个字段只存一个不可再分的最小值。

三、第二范式（2NF）：消除部分依赖

3.1 用生活类比理解 2NF

2NF 解决的是**"复合主键"场景下的依赖混乱**。

打个比方，食堂打饭的"打饭记录"，需要同时用"学生"和"窗口"两个信息才能确定一条记录（因为同一个学生可以在不同窗口打饭）。此时如果把"学生的班级"也写进打饭记录里，问题就来了：班级只跟学生有关，跟窗口一点关系都没有——这就是部分依赖。

2NF 的要求是：在满足 1NF 的基础上，非主键字段必须完全依赖"整个"主键，不能只依赖主键的一部分。换句话说，跟"学生"有关的放学生表，跟"窗口"有关的放窗口表，只有同时跟"学生+窗口"都有关的（比如打了几两饭、付了多少钱）才留在打饭记录表里。

3.2 违反 2NF 的错误设计

下面是一张"学生选课表"，主键是 (学号, 课程号) 的联合主键：

sql

-- 错误示例：非主键字段只依赖主键的一部分，违反 2NF
CREATE TABLE score_bad (
    student_id   INT NOT NULL,        -- 学号（联合主键之一）
    course_id    INT NOT NULL,        -- 课程号（联合主键之一）
    student_name VARCHAR(20),         -- 学生姓名：只依赖学号
    course_name  VARCHAR(50),         -- 课程名称：只依赖课程号
    score        INT,                 -- 成绩：同时依赖学号+课程号
    PRIMARY KEY (student_id, course_id)
);

插入数据：

sql

INSERT INTO score_bad (student_id, course_id, student_name, course_name, score) VALUES
(1, 101, '张三', '数学', 95),
(1, 102, '张三', '英语', 88),
(2, 101, '李四', '数学', 76);

3.3 这样设计会引发什么问题

数据冗余：张三的姓名 "张三" 在他选的每门课记录里都重复一份；数学课的课程名也在每个选它的学生记录里重复。
更新异常：张三改名叫 "张小三"，要把他所有选课记录里的 student_name 全部改一遍，漏一条就出现"同一个人两种名字"。
插入异常：想新增一门还没人选的课程（比如新开的 "化学"），因为联合主键里必须有 student_id，导致这条课程信息根本插不进去。
删除异常：李四退学了，删除他的选课记录，结果 "李四" 这个学生信息也跟着消失了，因为姓名只存在这张表里。

3.4 符合 2NF 的优化设计

核心思路：把"只依赖部分主键"的字段拆出去，各自成表。

sql

-- 学生表：姓名只依赖学号，单独放
CREATE TABLE student (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(20) NOT NULL
);

-- 课程表：课程名只依赖课程号，单独放
CREATE TABLE course (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(50) NOT NULL
);

-- 选课成绩表：成绩同时依赖学号+课程号，保留联合主键
CREATE TABLE score (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    score      INT,
    PRIMARY KEY (student_id, course_id),
    KEY idx_course (course_id)
);

对应数据：

sql

INSERT INTO student (name) VALUES ('张三'), ('李四');
INSERT INTO course (name) VALUES ('数学'), ('英语'), ('化学');

INSERT INTO score (student_id, course_id, score) VALUES
(1, 1, 95), (1, 2, 88), (2, 1, 76);

3.5 优化解决了哪些问题

冗余消失：姓名只在学生表存一次，课程名只在课程表存一次。
更新只改一处：张三改名，只改学生表一行。
可以提前插入：新课程 "化学" 可以独立插入课程表，等有人选了再往成绩表里加记录。
删除不误伤：删掉李四的选课记录，学生表里的 "李四" 依然存在。

一句话总结 2NF：先满足 1NF；如果表有联合主键，非主键字段必须依赖整个联合主键，不能只依赖其中一部分。只有单列主键的表，天然满足 2NF。

四、第三范式（3NF）：消除传递依赖

4.1 用生活类比理解 3NF

3NF 解决的是**"字段通过另一个字段间接依赖主键"的问题**，也就是传递依赖。

比如班级花名册：学生学号能确定班级编号，班级编号又能确定班级名称和班主任。这样一来，班级名称和班主任其实是通过"班级编号"间接依赖学号的。如果把它们都写进学生表，一旦班主任换人，就要把整个班几十个学生的记录全部改一遍。

3NF 的要求是：在满足 2NF 的基础上，非主键字段不能依赖另一个非主键字段（即不能有传递依赖）。班级名称、班主任这种信息，应该跟着"班级编号"走，单独放班级表。

4.2 违反 3NF 的错误设计

sql

-- 错误示例：非主键字段之间存在传递依赖，违反 3NF
CREATE TABLE student_bad (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    name         VARCHAR(20),         -- 学生姓名
    class_no     VARCHAR(20),         -- 班级编号
    class_name   VARCHAR(50),         -- 班级名称：依赖 class_no
    head_teacher VARCHAR(20)          -- 班主任：依赖 class_no
);

这里的依赖链是：id → class_no → class_name / head_teacher。class_name 和 head_teacher 并不直接依赖主键 id，而是通过 class_no 间接依赖。

插入数据：

sql

INSERT INTO student_bad (name, class_no, class_name, head_teacher) VALUES
('张三', 'C01', '三年一班', '王老师'),
('李四', 'C01', '三年一班', '王老师'),
('王五', 'C01', '三年一班', '王老师');

4.3 这样设计会引发什么问题

数据冗余：同一个班级的名称和班主任，在每个学生记录里都重复一遍。一个班 50 人，"三年一班 / 王老师" 就要存 50 遍。
更新异常：班主任从 "王老师" 换成 "刘老师"，要更新 50 条记录，漏改就出现"同一个班两个班主任"。
插入异常：想新增一个还没招到学生的班级（如 "三年二班"），因为这张表以学生为主键，班级信息没地方放，插不进去。
删除异常：把三年一班的学生全部删光（比如毕业），结果这个班级的名称、班主任信息也彻底丢失了。

4.4 符合 3NF 的优化设计

核心思路：把依赖 class_no 的字段抽出来，组成独立的班级表。

sql

-- 学生表：只保留学生 + 班级编号（外键）
CREATE TABLE student (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20) NOT NULL,
    class_id INT NOT NULL,
    KEY idx_class (class_id)
);

-- 班级表：班级名称、班主任都跟着班级走
CREATE TABLE class (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    class_no     VARCHAR(20) NOT NULL,
    class_name   VARCHAR(50) NOT NULL,
    head_teacher VARCHAR(20) NOT NULL
);

对应数据：

sql

INSERT INTO class (class_no, class_name, head_teacher) VALUES
('C01', '三年一班', '王老师'),
('C02', '三年二班', '刘老师');

INSERT INTO student (name, class_id) VALUES
('张三', 1), ('李四', 1), ('王五', 1);

4.5 优化解决了哪些问题

冗余消失：班级名称、班主任只在班级表存一次。
更新只改一处：换班主任，只改班级表一行，全班学生自动"生效"。
可以提前插入：新班级可以先建在班级表里，学生后续再进。
删除不误伤：删光学生，班级信息依然保留。

一句话总结 3NF：先满足 2NF；非主键字段之间不能存在依赖，即不能通过某个非主键字段间接依赖主键。凡是能"被主键之外的字段确定"的信息，都应该拆到它自己的表里。

五、BCNF（巴斯-科德范式）：消除主属性间的依赖

5.1 用生活类比理解 BCNF

BCNF 是 3NF 的"加强版"，它盯上了一种 3NF 管不到的特殊情况：主键字段之间互相依赖。

经典场景是"仓库管理"：一个仓库只配一个管理员，一个管理员只负责一个仓库，而一个仓库可以存放多种物品。用 (仓库, 物品) 当主键的话，仓库 → 管理员 和 管理员 → 仓库 这两个依赖就藏在主键字段之间，3NF 的规则（只约束非主键字段）管不住它。

BCNF 的要求是：每一个决定其他字段的"决定因素"，本身都必须是候选键。说得直白点——能唯一确定别的信息的字段，它自己必须能当主键。

5.2 违反 BCNF 的错误设计

sql

-- 错误示例：主属性之间存在依赖，违反 BCNF（虽然满足 3NF）
CREATE TABLE warehouse_bad (
    warehouse VARCHAR(20),            -- 仓库名
    item      VARCHAR(20),            -- 物品名
    manager   VARCHAR(20),            -- 管理员
    quantity  INT,                    -- 数量
    PRIMARY KEY (warehouse, item)
);

假设业务规则是：

一个仓库只能有一个管理员：warehouse → manager。
一个管理员只负责一个仓库：manager → warehouse。
一个仓库可以放多种物品。

此时这张表其实有两个候选键：(warehouse, item) 和 (manager, item)。问题在于 warehouse 和 manager 这两个"主属性"之间存在互相依赖（warehouse ↔ manager），而它们本身并不是完整的候选键，这就违反了 BCNF。

插入数据：

sql

INSERT INTO warehouse_bad (warehouse, item, manager, quantity) VALUES
('北京仓', '手机', '张管理员', 100),
('北京仓', '电脑', '张管理员', 50),
('上海仓', '手机', '李管理员', 80);

5.3 这样设计会引发什么问题

数据冗余：管理员和仓库的对应关系，在每件物品的记录里都重复一遍。
更新异常：北京仓换管理员，要把他名下所有物品记录的管理员字段全部改一遍。
插入异常：想新增一个还没存放任何物品的新仓库，因为 (warehouse, item) 里必须有物品，插不进去。
删除异常：某仓库最后一件物品被删掉，仓库与管理员的关系也跟着丢失。

这些问题和前面几类异常一脉相承，根源都是："仓库↔管理员" 这种独立关系，被硬塞进了一张以"仓库+物品"为主的表里。

5.4 符合 BCNF 的优化设计

核心思路：把"仓库—管理员"的对应关系拆成独立表，物品库存拆成另一张表。

sql

-- 仓库管理员表：专门维护 仓库 ↔ 管理员 的关系
CREATE TABLE warehouse_manager (
    warehouse VARCHAR(20) NOT NULL,
    manager   VARCHAR(20) NOT NULL,
    PRIMARY KEY (warehouse)
);

-- 仓库库存表：专门维护 仓库 + 物品 的数量
CREATE TABLE warehouse_stock (
    warehouse VARCHAR(20) NOT NULL,
    item      VARCHAR(20) NOT NULL,
    quantity  INT,
    PRIMARY KEY (warehouse, item)
);

对应数据：

sql

INSERT INTO warehouse_manager (warehouse, manager) VALUES
('北京仓', '张管理员'), ('上海仓', '李管理员');

INSERT INTO warehouse_stock (warehouse, item, quantity) VALUES
('北京仓', '手机', 100), ('北京仓', '电脑', 50), ('上海仓', '手机', 80);

5.5 优化解决了哪些问题

管理员和仓库的关系只在 warehouse_manager 存一次，换管理员改一行即可。
新仓库可以先建在 warehouse_manager 表里，再往 warehouse_stock 里加物品。
删除库存记录不再影响仓库与管理员的对应关系。

一句话总结 BCNF：先满足 3NF；并且每个能决定其他字段的"决定因素"都必须是候选键。在大多数业务表里，满足 3NF 通常也就满足了 BCNF，只有像上面这种"主属性间互相依赖"的场景才需要额外注意。

六、反范式：什么时候"故意"不遵守范式

读到这里你可能会问：既然范式越高越好，那把表拆得越细越规范不就行了？

答案是：规范化和查询性能常常是矛盾的。表拆得越细，数据冗余越少，但查询时需要 JOIN 的表就越多。JOIN 一多，在数据量大的时候查询会明显变慢。所以工程上不是"范式越高越好"，而是"在冗余可控的前提下，换取更快的查询"。

6.1 场景一：高频联表查询

比如订单详情页，每次都要同时展示订单、下单用户、商品三个信息。如果严格按照 3NF，就要 orders JOIN users JOIN order_items JOIN products 连续 JOIN 好几次，高并发下压力很大。

常见的反范式做法：在订单表里冗余一份"下单时的用户名快照"（甚至商品名、单价快照），查询时少 JOIN 一到两张表。代价是用户名改了不会自动同步到历史订单——但这恰恰符合业务需求：历史订单本来就该记录"下单那一刻"的用户名和价格，而不是实时最新的。

6.2 场景二：读多写少的热点数据

比如商品的"销量""评分"这类统计值，如果每次都要 SELECT COUNT(*) FROM order_items GROUP BY product_id 现算，读得多时数据库压力巨大。反范式做法是：在商品表里直接冗余一个 sales_count 字段，下单时顺带 +1。用"写入时多花一点点成本"，换取"读取时极快的速度"。

6.3 常用反范式手段小结

反范式手段	典型做法	适用场景
冗余字段	把常用的关联字段（如用户名）冗余到主表	高频联表查询、读多写少
统计字段	把 `COUNT/SUM` 结果预存成普通字段	排行榜、销量、点赞数
冗余快照	记录下单时的价格、地址等历史快照	订单等需要保留历史状态的场景
大宽表	把多表数据合并成一张宽表	报表、数据仓库、OLAP 分析

核心原则：反范式是用"可控的冗余"换"查询性能"。做反范式时，一定要明确"冗余字段由谁负责维护、什么时候更新"，否则冗余数据会变成不一致的脏数据。

七、范式设计与性能的平衡：实操方案

范式和性能的平衡没有标准答案，但有成熟的实操思路：

先规范、后反范式。设计初期严格按照 3NF/BCNF 拆分，把数据结构做干净；等线上真实暴露出查询瓶颈，再针对热点做反范式优化。不要一上来就做反范式，否则冗余很快失控。
区分 OLTP 和 OLAP。面向用户交易的业务系统（OLTP）追求"少冗余、多事务、快写入"，优先规范化；面向分析报表的系统（OLAP）追求"快查询、少 JOIN"，可以大量使用宽表、反范式。
让 JOIN 有索引兜底。如果不想反范式，就必须保证所有被 JOIN 的外键列都有索引。一个有索引的 3 表 JOIN，往往比一张没索引的大宽表还快。
衡量成本再动手。只有数据量大、查询频繁、且 JOIN 确实拖慢性能的表才值得反范式；数据量小的表，规范化的好处远大于 JOIN 的开销。
控制冗余的更新入口。凡是冗余字段，尽量只由一条固定的代码路径（如"下单"这个事务）来维护，避免多个地方各写各的导致数据打架。

八、范式落地的配套技术要点

范式落地不是光把表拆开就完事，还需要配套的技术手段，才能真正保证数据的一致性和查询效率。

8.1 外键约束

拆表之后，表与表之间靠"外键列"关联（如 student.class_id 指向 class.id）。在 MySQL 里，可以用**外键约束（FOREIGN KEY）**来强制保证关联数据的一致性：

sql

CREATE TABLE student (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20) NOT NULL,
    class_id INT NOT NULL,
    CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class (id)
);

加了外键约束后：

插不进去"不存在"的班级（避免孤儿数据）。
删不掉的被引用班级（避免学生指向空班级）。
可配置 ON DELETE CASCADE 级联删除、ON UPDATE CASCADE 级联更新。

但要注意：生产环境里不少团队会故意不用物理外键，而是靠应用层代码来保证一致性。原因是外键约束在高并发、大数据量、分库分表场景下会带来性能损耗和维护成本。是否使用外键，需要结合团队规范权衡。无论用不用物理外键，关联列上的索引都一定要建。

8.2 索引设计

拆表之后，JOIN 会变多，索引就成了命脉。几个要点：

外键列必须建索引：WHERE class_id = 1 或 JOIN 时，没有索引会退化成全表扫描。
联合索引要符合最左前缀：联合主键 (student_id, course_id) 自带索引，能加速 student_id = ? 和 student_id = ? AND course_id = ? 的查询，但无法单独加速 course_id = ?，所以上面 score 表里额外给 course_id 建了索引。
唯一约束防重复：如 student_phone 里的 UNIQUE KEY (student_id, phone)，从数据库层面杜绝重复数据。

8.3 建表时的几点工程习惯

主键尽量用自增 BIGINT，避免用有业务含义的字段当主键（业务值会变，变了主键代价极大）。
字符串优先 VARCHAR，长度按需设置，不要无脑 VARCHAR(255)。
金额用 DECIMAL(10,2) 而不是 FLOAT/DOUBLE，避免浮点精度丢失。
时间字段用 DATETIME，需要时区或跨时区再考虑 TIMESTAMP。
给所有表都加上 created_at / updated_at，方便排查和审计。

九、实战案例：电商平台的用户、订单、商品

下面用一个完整的电商场景，带你从"一张大表"出发，逐步按范式拆分，最终落地成规范的多表结构。

9.1 需求梳理

一个最简化的电商系统，核心业务如下：

用户：可以下单，一个用户有多个订单。
商品：有名称、价格，一个商品会出现在多个订单里。
订单：属于某个用户，一个订单可以包含多个商品，每种商品有购买数量和下单时的单价。

梳理出的实体关系：

用户对订单：一对多。
订单对商品：多对多（一个订单多个商品，一个商品出现在多个订单），需要中间表来承载"数量、单价"这些关系属性。

9.2 第一步：初始的不合理单表设计

新手最容易犯的错，就是把所有东西塞进一张"订单大表"：

sql

-- 初始错误设计：一张表装下所有信息
CREATE TABLE orders_bad (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    order_no      VARCHAR(32),        -- 订单号
    user_name     VARCHAR(20),        -- 用户名
    user_phone    VARCHAR(20),        -- 用户电话
    product_names VARCHAR(500),       -- 商品名称：'iPhone,充电器'
    product_prices VARCHAR(200),      -- 商品价格：'6999.00,99.00'
    quantities    VARCHAR(200),       -- 购买数量：'1,2'
    total_amount  DECIMAL(10,2),      -- 总金额
    address       VARCHAR(200)        -- 收货地址
);

这张表几乎同时违反了所有范式：

product_names、product_prices、quantities 用逗号拼接多个值 → 违反 1NF。
user_name、user_phone 只依赖用户，与订单无关 → 存在冗余和传递依赖 → 违反 2NF/3NF。
商品信息在每个订单里重复 → 数据冗余严重。

9.3 第二步：拆成用户表、商品表、订单表、订单明细表

按照范式逐步拆分，最终落地为四张表：

sql

-- 用户表
CREATE TABLE `user` (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    username    VARCHAR(50) NOT NULL COMMENT '用户名',
    phone       VARCHAR(20) NOT NULL COMMENT '手机号',
    address     VARCHAR(200) COMMENT '默认收货地址',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_phone (phone)
) COMMENT '用户表';

-- 商品表
CREATE TABLE product (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL COMMENT '商品名称',
    price       DECIMAL(10,2) NOT NULL COMMENT '商品价格',
    stock       INT NOT NULL DEFAULT 0 COMMENT '库存',
    sales_count INT NOT NULL DEFAULT 0 COMMENT '销量(反范式冗余统计字段)',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_name (name)
) COMMENT '商品表';

-- 订单表
CREATE TABLE `order` (
    id           BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no     VARCHAR(32) NOT NULL COMMENT '订单号',
    user_id      BIGINT NOT NULL COMMENT '下单用户ID',
    user_name    VARCHAR(50) NOT NULL COMMENT '下单时用户名快照(反范式冗余)',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
    status       TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态',
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user (user_id)
) COMMENT '订单表';

-- 订单明细表：承载"订单-商品"多对多关系
CREATE TABLE order_item (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id      BIGINT NOT NULL COMMENT '订单ID',
    product_id    BIGINT NOT NULL COMMENT '商品ID',
    product_name  VARCHAR(100) NOT NULL COMMENT '下单时商品名快照(反范式冗余)',
    product_price DECIMAL(10,2) NOT NULL COMMENT '下单时单价快照(反范式冗余)',
    quantity      INT NOT NULL COMMENT '购买数量',
    KEY idx_order (order_id),
    KEY idx_product (product_id)
) COMMENT '订单明细表';

9.4 逐条说明拆分逻辑与解决问题

user 表：用户信息独立存储，一个用户多个订单只存一份姓名电话，彻底消除冗余和更新异常。
product 表：商品信息独立，价格、库存各存一份。sales_count 是反范式冗余，下单时 +1，避免高频统计联表。
order 表：只存订单本身 + user_id 外键关联用户。user_name 是下单时快照，既减少展示订单列表时的 JOIN，又满足"历史订单保留当时用户名"的业务诉求。
order_item 表：拆掉了违反 1NF 的拼接字段，一个商品一行记录，通过 order_id + product_id 表达多对多关系。product_name、product_price 是下单时快照，保证商品后续改名或调价不影响历史订单。

9.5 联表查询示例

查询某个用户的全部订单：

sql

SELECT o.order_no, o.total_amount, o.status, o.created_at
FROM `order` o
JOIN `user` u ON o.user_id = u.id
WHERE u.username = '张三'
ORDER BY o.created_at DESC;

查询某个订单的完整明细（含商品）：

sql

SELECT oi.product_name, oi.product_price, oi.quantity,
       (oi.product_price * oi.quantity) AS subtotal
FROM order_item oi
JOIN `order` o ON oi.order_id = o.id
WHERE o.order_no = '202409240001';

统计每个商品的销量（用反范式字段，无需 GROUP BY 大表）：

sql

SELECT name, sales_count FROM product ORDER BY sales_count DESC LIMIT 10;

下单时的事务（演示冗余字段如何维护）：

sql

START TRANSACTION;

-- 1. 插入订单
INSERT INTO `order` (order_no, user_id, user_name, total_amount, status)
SELECT '202409240002', id, username, 7197.00, 0 FROM `user` WHERE id = 1;

-- 2. 插入订单明细（含快照）
INSERT INTO order_item (order_id, product_id, product_name, product_price, quantity)
VALUES (LAST_INSERT_ID(), 1, 'iPhone', 6999.00, 1),
       (LAST_INSERT_ID(), 2, '充电器', 99.00, 2);

-- 3. 反范式维护：销量 +1（或 +购买数量）
UPDATE product SET sales_count = sales_count + 1 WHERE id = 1;
UPDATE product SET sales_count = sales_count + 2 WHERE id = 2;

COMMIT;

通过这个案例可以看到：核心实体（用户、商品、订单）严格规范化，只有"快照"和"统计"这类明确可控的字段才做反范式冗余，既保证了数据规范，又兼顾了查询性能。

十、总结：MySQL 表设计范式的核心原则

回顾全文，范式的核心思想可以浓缩成一句话：让每张表只描述"一件事"，让每个字段只依赖它该依赖的主键。

1NF：字段原子化，一个格子一个值。
2NF：消除部分依赖，联合主键下非主键字段要依赖整个主键。
3NF：消除传递依赖，非主键字段之间不能互相依赖。
BCNF：消除主属性间依赖，任何"决定因素"都必须是候选键。

在实际工作中，记住这几条核心原则：

先规范后反范式：设计初期严格按 3NF/BCNF 拆分，把数据结构做干净，别怕拆表。
一张表只做一件事：用户是用户、订单是订单、明细是明细，职责单一。
能唯一确定某信息的字段，就让它独立成表：这是拆分表的万能判断标准。
外键列必建索引：拆表必然带来 JOIN，索引是查询性能的底线。
反范式要有边界：只对"快照、统计值"这类明确可控的字段做冗余，且必须指定唯一的更新入口。
先满足业务正确性，再谈性能：数据一致、无异常比"少写几个 JOIN"重要得多。

掌握范式，不是让你机械地把表拆得七零八落，而是让你在做每一次"拆不拆、冗余不冗余"的决策时，心里有清晰的标准和理由。真正好的表设计，是在规范与性能之间找到最贴合业务的那条平衡线。

MySQL 表设计范式 ​

前言 ​

一、范式到底在解决什么问题 ​

二、第一范式（1NF）：字段不可再分 ​

2.1 用生活类比理解 1NF ​

2.2 违反 1NF 的错误设计 ​

2.3 这样设计会引发什么问题 ​

2.4 符合 1NF 的优化设计 ​

2.5 优化解决了哪些问题 ​

三、第二范式（2NF）：消除部分依赖 ​

3.1 用生活类比理解 2NF ​

3.2 违反 2NF 的错误设计 ​

3.3 这样设计会引发什么问题 ​

3.4 符合 2NF 的优化设计 ​

3.5 优化解决了哪些问题 ​

四、第三范式（3NF）：消除传递依赖 ​

4.1 用生活类比理解 3NF ​

4.2 违反 3NF 的错误设计 ​

4.3 这样设计会引发什么问题 ​

4.4 符合 3NF 的优化设计 ​

4.5 优化解决了哪些问题 ​

五、BCNF（巴斯-科德范式）：消除主属性间的依赖 ​

5.1 用生活类比理解 BCNF ​

5.2 违反 BCNF 的错误设计 ​

5.3 这样设计会引发什么问题 ​

5.4 符合 BCNF 的优化设计 ​

5.5 优化解决了哪些问题 ​

六、反范式：什么时候"故意"不遵守范式 ​

6.1 场景一：高频联表查询 ​

6.2 场景二：读多写少的热点数据 ​

6.3 常用反范式手段小结 ​

七、范式设计与性能的平衡：实操方案 ​

八、范式落地的配套技术要点 ​

8.1 外键约束 ​

8.2 索引设计 ​

8.3 建表时的几点工程习惯 ​

九、实战案例：电商平台的用户、订单、商品 ​

9.1 需求梳理 ​

9.2 第一步：初始的不合理单表设计 ​

9.3 第二步：拆成用户表、商品表、订单表、订单明细表 ​

9.4 逐条说明拆分逻辑与解决问题 ​

9.5 联表查询示例 ​

十、总结：MySQL 表设计范式的核心原则 ​

MySQL 表设计范式

前言

一、范式到底在解决什么问题

二、第一范式（1NF）：字段不可再分

2.1 用生活类比理解 1NF

2.2 违反 1NF 的错误设计

2.3 这样设计会引发什么问题

2.4 符合 1NF 的优化设计

2.5 优化解决了哪些问题

三、第二范式（2NF）：消除部分依赖

3.1 用生活类比理解 2NF

3.2 违反 2NF 的错误设计

3.3 这样设计会引发什么问题

3.4 符合 2NF 的优化设计

3.5 优化解决了哪些问题

四、第三范式（3NF）：消除传递依赖

4.1 用生活类比理解 3NF

4.2 违反 3NF 的错误设计

4.3 这样设计会引发什么问题

4.4 符合 3NF 的优化设计

4.5 优化解决了哪些问题

五、BCNF（巴斯-科德范式）：消除主属性间的依赖

5.1 用生活类比理解 BCNF

5.2 违反 BCNF 的错误设计

5.3 这样设计会引发什么问题

5.4 符合 BCNF 的优化设计

5.5 优化解决了哪些问题

六、反范式：什么时候"故意"不遵守范式

6.1 场景一：高频联表查询

6.2 场景二：读多写少的热点数据

6.3 常用反范式手段小结

七、范式设计与性能的平衡：实操方案

八、范式落地的配套技术要点

8.1 外键约束

8.2 索引设计

8.3 建表时的几点工程习惯

九、实战案例：电商平台的用户、订单、商品

9.1 需求梳理

9.2 第一步：初始的不合理单表设计

9.3 第二步：拆成用户表、商品表、订单表、订单明细表

9.4 逐条说明拆分逻辑与解决问题

9.5 联表查询示例

十、总结：MySQL 表设计范式的核心原则