MySQL 三大范式与反范式-开发者社区

我刚工作的时候，设计了一张用户表，把所有信息都塞进去了：用户 ID、姓名、年龄、邮箱、地址、订单 ID、订单金额、订单状态……结果表有 50 多个字段，查询慢得要命，还经常冗余数据不一致。

后来 DBA 帮我重新设计表结构，遵循了数据库三大范式，性能直接提升了 10 倍。

今天咱们就来聊聊 MySQL 的三大范式与反范式，看完这篇，你就能设计出高性能的表结构了。

为什么要有范式？

范式（Normal Form，NF）是数据库设计的一套规范，目的是：

减少数据冗余：同样的数据不存多份
1. 避免数据异常：插入异常、删除异常、更新异常
1. 提高数据一致性：数据只存一份，不会出现不一致
  但是，过度遵循范式会导致表太多、JOIN 太多，性能反而下降。所以有时候要反范式（故意冗余数据）。

第一范式（1NF）：列不可再分

定义：表中的每一列都是原子性的（不可再分）。

违反 1NF 的例子

-- 违反 1NF：phone 列存了多个值CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(50),phoneVARCHAR(100)-- 存了 "13800138000,13900139000");```**问题**：如果要查 "13800138000" 这个手机号的用户，很难写 SQL。 ### 符合 1NF 的设计 ```sql-- 符合 1NF：拆成多行CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(50));CREATETABLEuser_phones(user_idINT,phoneVARCHAR(20),PRIMARYKEY(user_id,phone));```**或者**（如果只需要 2 个手机号）： ```sqlCREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(50),phone1VARCHAR(20),phone2VARCHAR(20));```## 第二范式（2NF）：消除部分依赖 **定义**：表中的每一列都**完全依赖**于主键（不能是部分依赖）。 **前提**：表必须有**联合主键**（如果主键是单字段，自动满足 2NF）。 ### 违反 2NF 的例子 ```sql-- 违反 2NF：联合主键 (order_id, product_id)，但 product_name 只依赖于 product_id（部分依赖）CREATETABLEorder_items(order_idINT,product_idINT,product_nameVARCHAR(50),-- 只依赖于 product_id，不依赖于 order_idquantityINT,PRIMARYKEY(order_id,product_id));```**问题**： 1. **数据冗余**：同一个`product_id`的`product_name`存了多次 2. 2. **更新异常**：如果`product_name`改了，要更新多行 3. 3. **插入异常**：如果还没订单，就没法插产品信息 ### 符合 2NF 的设计 ```sql-- 拆成两张表CREATETABLEorder_items(order_idINT,product_idINT,quantityINT,PRIMARYKEY(order_id,product_id));CREATETABLEproducts(product_idINTPRIMARYKEY,product_nameVARCHAR(50));```## 第三范式（3NF）：消除传递依赖 **定义**：表中的每一列都**直接依赖**于主键（不能是传递依赖）。 ### 违反 3NF 的例子 ```sql-- 违反 3NF：department_name 依赖于 department_id，department_id 依赖于 emp_id（传递依赖）CREATETABLEemployees(emp_idINTPRIMARYKEY,emp_nameVARCHAR(50),department_idINT,department_nameVARCHAR(50)-- 传递依赖：emp_id → department_id → department_name);```**问题**： 1. **数据冗余**：同一个部门的员工，部门名存了多次 2. 2. **更新异常**：如果部门名改了，要更新多行 3. 3. **插入异常**：如果还没员工，就没法插部门信息 ### 符合 3NF 的设计 ```sql-- 拆成两张表CREATETABLEemployees(emp_idINTPRIMARYKEY,emp_nameVARCHAR(50),department_idINT);CREATETABLEdepartments(department_idINTPRIMARYKEY,department_nameVARCHAR(50));```## BC 范式（BCNF）：消除主属性对候选键的部分依赖 **定义**：如果表有**多个候选键**，不能有主属性对候选键的部分依赖。 ### 违反 BCNF 的例子 ```sql-- 假设：(student_id, course_id) 是联合主键，(professor_id, course_id) 也是候选键CREATETABLEenrollments(student_idINT,course_idINT,professor_idINT,PRIMARYKEY(student_id,course_id));```**问题**：`professor_id`依赖于`course_id`（部分依赖候选键），导致数据冗余。 ### 符合 BCNF 的设计 ```sql-- 拆成两张表CREATETABLEenrollments(student_idINT,course_idINT,PRIMARYKEY(student_id,course_id));CREATETABLEcourses(course_idINTPRIMARYKEY,professor_idINT);```## 第四范式（4NF）和第五范式（5NF） 这两范式很少用到，简单提一下： - **4NF**：消除多值依赖（比如一个表同时存了"课程"和"爱好"两个多值属性） - - **5NF**：消除连接依赖（确保表是不可再分的） **实际开发中，一般遵循到 3NF 就够了。** ## 反范式（Denormalization）：故意冗余数据 **反范式**指的是：**故意违反范式，冗余一些数据**，目的是**减少 JOIN，提升查询性能**。 ### 什么时候要反范式？ 1. **读多写少**的场景（比如电商的订单表、商品表） 2. 2. **JOIN 太多**，导致查询性能差 3. 3. **实时性要求高**，不能每次都 JOIN ### 反范式的例子 #### 例子 1：订单表冗余用户名 **遵循 3NF 的设计**： ```sqlCREATETABLEorders(order_idINTPRIMARYKEY,user_idINT,amountDECIMAL(10,2),created_atDATETIME);CREATETABLEusers(user_idINTPRIMARYKEY,usernameVARCHAR(50));-- 查询订单时，要 JOIN users 表SELECTo.order_id,u.username,o.amountFROMorders oJOINusers uONo.user_id=u.user_id;

反范式设计（冗余username）：

CREATETABLEorders(order_idINTPRIMARYKEY,user_idINT,usernameVARCHAR(50),-- 冗余字段amountDECIMAL(10,2),created_atDATETIME);-- 查询订单时，不需要 JOINSELECTorder_id,username,amountFROMorders;

好处：查询快了（不需要 JOIN）。

代价：

如果用户改了用户名，要更新所有相关订单（可以用触发器或者定时任务）
1. 冗余字段占存储空间

例子 2：商品表冗余分类名

遵循 3NF 的设计：

CREATETABLEproducts(product_idINTPRIMARYKEY,product_nameVARCHAR(50),category_idINT);CREATETABLEcategories(category_idINTPRIMARYKEY,category_nameVARCHAR(50));-- 查询商品时，要 JOIN categories 表SELECTp.product_name,c.category_nameFROMproducts pJOINcategories cONp.category_id=c.category_id;

反范式设计（冗余category_name）：

CREATETABLEproducts(product_idINTPRIMARYKEY,product_nameVARCHAR(50),category_idINT,category_nameVARCHAR(50)-- 冗余字段);-- 查询商品时，不需要 JOINSELECTproduct_name,category_nameFROMproducts;

实战建议

1. 先遵循 3NF，再按需反范式

建议流程：

先按 3NF 设计表结构（减少冗余，保证一致性）
1. 上线后，如果发现某些查询性能差（JOIN 太多），再考虑反范式（冗余字段）

2. 反范式时，要考虑数据同步问题

如果冗余字段会更新，要设计好同步机制：

方案 1：用触发器（自动同步）
– 当 users 表的 username 更新时，自动更新 orders 表的 username
DELIMITER $$
CREATE TRIGGER update_username AFTER UPDATE ON users
FOR EACH ROW
BEGIN

UPDATE orders SET username = NEW.username WHERE user_id = NEW.user_id;

END$$
DELIMITER ;
方案 2：用定时任务（定期同步）
– 每天凌晨同步用户名
UPDATE orders o
JOIN users u ON o.user_id = u.user_id
SET o.username = u.username
WHERE o.username != u.username;
方案 3：不更新（允许短期不一致，比如订单的用户名，下完单就不应该改了）

3. 用「宽表」做报表查询

宽表指的是：把多张表的数据冗余到一张表，专门用来做报表查询（不需要 JOIN）。

-- 宽表：订单明细表（冗余了用户信息、商品信息、分类信息）CREATETABLEorder_details(order_idINT,user_idINT,usernameVARCHAR(50),product_idINT,product_nameVARCHAR(50),category_nameVARCHAR(50),amountDECIMAL(10,2),created_atDATETIME);```**好处**：报表查询超快（不需要JOIN）。**代价**：数据冗余，同步复杂。## 总结-**第一范式（1NF）**：列不可再分（原子性）--**第二范式（2NF）**：消除部分依赖（联合主键时，每一列都完全依赖于主键）--**第三范式（3NF）**：消除传递依赖（每一列都直接依赖于主键）--**BC 范式（BCNF）**：消除主属性对候选键的部分依赖--**反范式**：故意冗余数据，减少JOIN，提升查询性能--实战建议：先遵循3NF，再按需反范式；反范式时，要考虑数据同步问题；用「宽表」做报表查询 如果你能把三大范式和反范式讲清楚，并且能设计合理的表结构，面试官绝对觉得你是高级开发。---**实战代码都在我本地跑过，你可以放心复制。**如果有问题，欢迎评论区交流！