我刚工作的时候设计了一张用户表把所有信息都塞进去了用户 ID、姓名、年龄、邮箱、地址、订单 ID、订单金额、订单状态……结果表有 50 多个字段查询慢得要命还经常冗余数据不一致。后来 DBA 帮我重新设计表结构遵循了数据库三大范式性能直接提升了 10 倍。今天咱们就来聊聊 MySQL 的三大范式与反范式看完这篇你就能设计出高性能的表结构了。为什么要有范式范式Normal FormNF是数据库设计的一套规范目的是减少数据冗余同样的数据不存多份避免数据异常插入异常、删除异常、更新异常提高数据一致性数据只存一份不会出现不一致但是过度遵循范式会导致表太多、JOIN 太多性能反而下降。所以有时候要反范式故意冗余数据。第一范式1NF列不可再分定义表中的每一列都是原子性的不可再分。违反 1NF 的例子-- 违反 1NFphone 列存了多个值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-- 违反 3NFdepartment_name 依赖于 department_iddepartment_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_idu.user_id;反范式设计冗余usernameCREATETABLEorders(order_idINTPRIMARYKEY,user_idINT,usernameVARCHAR(50),-- 冗余字段amountDECIMAL(10,2),created_atDATETIME);-- 查询订单时不需要 JOINSELECTorder_id,username,amountFROMorders;好处查询快了不需要 JOIN。代价如果用户改了用户名要更新所有相关订单可以用触发器或者定时任务冗余字段占存储空间例子 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_idc.category_id;反范式设计冗余category_nameCREATETABLEproducts(product_idINTPRIMARYKEY,product_nameVARCHAR(50),category_idINT,category_nameVARCHAR(50)-- 冗余字段);-- 查询商品时不需要 JOINSELECTproduct_name,category_nameFROMproducts;实战建议1. 先遵循 3NF再按需反范式建议流程先按 3NF 设计表结构减少冗余保证一致性上线后如果发现某些查询性能差JOIN 太多再考虑反范式冗余字段2. 反范式时要考虑数据同步问题如果冗余字段会更新要设计好同步机制方案 1用触发器自动同步– 当 users 表的 username 更新时自动更新 orders 表的 usernameDELIMITER $$CREATE TRIGGER update_username AFTER UPDATE ON usersFOR EACH ROWBEGINUPDATE orders SET username NEW.username WHERE user_id NEW.user_id;END$$DELIMITER ;方案 2用定时任务定期同步– 每天凌晨同步用户名UPDATE orders oJOIN users u ON o.user_id u.user_idSET o.username u.usernameWHERE 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再按需反范式反范式时要考虑数据同步问题用「宽表」做报表查询 如果你能把三大范式和反范式讲清楚并且能设计合理的表结构面试官绝对觉得你是高级开发。---**实战代码都在我本地跑过你可以放心复制。**如果有问题欢迎评论区交流
