★ FEATURED ARTICLE 1. 先说说我为什么非要讲三大范式刚接触数据库设计那会儿我犯过一个现在看来特别低级的错误拿到需求就建表凡是跟业务沾边的字段全塞进一张大表里。订单、商品、用户、地址、分类能想到的都往里放。当时觉得没什么问题查数据也不用JOIN多张表一条SQL就能拿到所有信息效率高得很。结果等业务量一上来问题全暴露了。数据大量冗余改一个商品分类名称要UPDATE几百行删除用户信息却因为关联关系报错更麻烦的是统计报表时发现同一张表里同样的数据有了多个版本对不上账。那时才意识到一个合格的表结构设计不是能存数据就行。三大范式1NF、2NF、3NF就是用来解决这类问题的。它是关系型数据库设计中最基础、也最容易被人忽略的一套规则。说白了它就是一套表结构设计规范告诉你怎么拆分字段、怎么设计主键、怎么排除列之间的隐性问题从而让数据库干净、稳定、好维护。这篇文章我会用实际例子把三个范式逐个讲透核心会放在2NF和3NF的判断方法上最后补一段关于反范式的实战经验。后面也会给出一套完整的订单表拆解演练把为什么这样拆的理由也讲清楚而不是只丢给你三条定义让你背。读这篇文章之前你最好已经会基本的建表语句用过MySQL之类的关系型数据库至少理解主键、外键、字段、表这些概念。如果这些还不熟悉建议先补一下基础再回来看。2. 范式到底是什么——把它想成表结构的分级体检标准说句实话范式这个概念一开始很难让人提起兴趣。教科书的说法是关系模式中各个属性之间必须满足的约束条件听起来很绕但其实可以换个角度理解。2.1 范式是一段关系数据库设计的红线想象你在装修房子。水电工告诉你电线不能裸露水管承重墙不能打穿排插不能放在潮湿区域。这些规则不是限制你而是保证你住进去之后不会三天两头出问题。范式就是数据库设计领域的水电规范——它在表结构层面替你划好了一条底线避免数据不一致、冗余爆炸和更新异常。1NF第一范式管的是字段原子性也就是最基本的数据存储形态2NF第二范式管的是跟主键的部分依赖主要发生在复合主键场景中3NF第三范式管的是非主键字段之间的隐性依赖也就是传递依赖。三条规则一层套一层范围越来越严格。满足3NF通常意味着表结构已经比较干净了。不过要注意很多现实项目并没有完全满足3NF因为你一旦把所有表都拆到极致查询性能往往会非常难看这个后文专门说。2.2 一个反直觉的事实设计越规范查询越复杂这是新手最容易想不通的地方。在小数据量的时候你根本看不出来违反范式有什么问题反而觉得拆表之后的SQL复杂、性能差。但数据量一大、并发一高、业务一复杂区别就出来了。不遵守范式早期写起来很爽后期改起来很疼。遵守范式前期麻烦一点后期维护起来省心很多。所以这不是会不会爆炸的问题而是什么时候爆炸的问题。我先给出一份快速对比表方便后面展开范式管什么一句话判断方法常见问题1NF字段原子性字段还有没有拆分的价值分隔符拼多个值JSON数组2NF与主键的部分依赖非主键字段是否依赖复合主键的一部分联合主键场景下的重复数据3NF非主键字段的传递依赖字段之间是否存在A决定BB决定C的关系冗余的关联属性、统计字段3. 第一范式原子性这条底线看着简单却经常被踩第一范式的定义读起来很短表中的每个字段都应具有原子性不能再拆分。但再拆分这个边界在实际工作中其实相当暧昧。3.1 什么叫不能再拆分假设你是某培训机构的课程顾问需要记录学员的报名信息。你做了一个字段叫备用联系方式里面存了两个电话13812345678 / 0571-88886666。这个字段能拆吗当然能它可以拆成手机号和座机号两列。违反了1NF。还有一种更隐蔽的情况——用分隔符一口气存多个值。比如做学生选课系统时把某同学选的课都存成一个字段语文,数学,英语。看似没问题可一旦你要统计选数学的有多少人就得在WHERE条件里用模糊匹配性能差还容易出错。再看一个更贴近现实的例子。某电商项目早期设计了这样一张表CREATE TABLE product_info ( id INT PRIMARY KEY, product_name VARCHAR(64), tags VARCHAR(255), -- 多个标签用逗号拼接比如手机,数码,全新 detail_images VARCHAR(1024) -- 多张图片地址用竖线分割 );明显不满足1NF。正常设计应该拆出一张product_tag关联表或者至少拆分字段图片地址同理。这也是很多项目里埋雷最多的地方。3.2 为什么空空如也的字段也可能违背1NF还有一种反模式同一个字段在不同行里存了不同类型的数据。比如某表有个字段叫remark一会在A行存已发货一会在B行存2024-03-12一会在C行存退款单号R20240312001。虽然每个行单独看都没问题但从整列数据类型的语义来看这违背了字段类型一致性。1NF不仅要求一列存一种信息还隐式要求一列在整个表里类型统一、语义统一。3.3 1NF的判据与常见修正方案判断一个字段是否违反1NF就问你三个问题这个字段里是否存在多个独立含义的数据这些数据是否在将来的查询中会被单独过滤、统计或连接如果被拆开原表结构是否还能保持业务语义只要三个问题里有一个答案是是那就拆。常见的处理方法把复合值拆成独立字段手机号、座机号分列把一对多关系拆成独立子表student_course选课表把JSON字段拆成关联行当明细查询频繁时JSON只适合低频场景4. 第二范式复合主键下的部分依赖才是真正的大坑如果1NF只是表面功夫那2NF就要真正理解主键的结构了。第二范式的定义是在满足1NF的基础上非主键字段必须完全依赖于主键而不能只依赖主键的一部分。很多文章直接抄了这一句就完了但新手不知道自己什么时候会踩到它——这个定义的核心场景是复合主键。4.1 部分依赖的典型场景订单明细表教学里最经典的分析例子之一就是订单明细表。场景如下某书店系统里用户会买书一个订单里可能包含多种书每种书会买若干数量。设计订单明细表时有两种主键选择方案A单独加一个唯一ID自增主键 方案B用order_idbook_id做联合主键如果你选了方案B那么问题就来了。假设表长这样CREATE TABLE order_item ( order_id INT, book_id INT, book_name VARCHAR(64), book_price DECIMAL(10,2), quantity INT, PRIMARY KEY (order_id, book_id) );这个表存在什么问题order_id和book_id的联合体已经能确定每本书的订购明细但book_name和book_price只依赖book_id真正的部分依赖。比如同一个书ID在多个订单中出现book_name就会被重复存储一旦书名变动所有引用它的订单行都得改。看书名重复可能觉得还好但如果是供应商电话商品重量这种大字段冗余量就非常可观了。更麻烦的是更新异常某一天书价调整你可能会漏更新其中的某几行导致同一个book_id出现两个价格——数据直接不一致。这就是2NF要解决的问题把只依赖部分主键的字段拆出去放进独立的主数据表。4.2 有单列主键就没2NF问题了吗很多读者到这可能会想那我不用联合主键给每张表都弄一个自增ID不就没有部分依赖问题了吗逻辑上没错2NF的确只在复合主键下有意义。但工程上的坑恰恰在这里——不少人为了省事给所有表都加一个自增主键把真正的业务唯一键只做成普通索引或干脆不设结果业务上完全可以由两个字段唯一确定的数据在表里出现了多行。给出一个我实际见到过的例子CREATE TABLE student_course ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, course_id INT, score INT, register_time DATETIME );逻辑上一个学生选同一门课只能有一条记录student_id和course_id是天然的业务唯一键。可因为主键是自增ID程序里又缺少对这两个字段的约束导致重复选课记录被插入。此时这个表不是满足2NF与否的问题而是根本失去了记录一条选课事实的定位。所以2NF的实际落地法是找准业务主键。能用业务唯一键做主键的就用不能的也要用UNIQUE约束把业务唯一性锁死而不是放任自增ID当万能挡箭牌。4.3 正确设计拆表让每张表只表达一种实体沿用上面的书籍订单例子正确做法是把完整信息落到两张表里-- 书籍主数据表只依赖 book_id CREATE TABLE book ( book_id INT PRIMARY KEY, book_name VARCHAR(64), book_price DECIMAL(10,2) ); -- 订单明细表只记录订单与书籍之间的关系 CREATE TABLE order_item ( order_id INT, book_id INT, quantity INT, PRIMARY KEY (order_id, book_id), FOREIGN KEY (book_id) REFERENCES book (book_id) );这样的结构里书价、书名只存在于book表中。修改一次所有订单引用处的价格信息全部生效本质上消除了部分依赖带来的更新异常。5. 第三范式传递依赖看着无害实则引发一堆隐性Bug通过了2NF的表已经满足了字段完全依赖主键但还不够。3NF约束的是非主键字段之间的关系。第三范式的定义是在满足2NF的基础上非主键字段之间不能存在传递依赖。所谓传递依赖就是字段A决定字段B字段B又决定字段C于是非主键字段C间接依赖了主键A。5.1 传递依赖的经典角色班级与班主任先说一下这个概念是怎么发生的如果没有3NF同一张表里可能混入中间实体的专属属性导致这些属性跟主键之外的其他字段挂钩。最常出现的多对一关系场景学生属于班级班级有班主任。错误示范CREATE TABLE student_info ( student_id INT PRIMARY KEY, student_name VARCHAR(32), class_id INT, class_name VARCHAR(32), class_teacher VARCHAR(32) );这个表里student_id决定class_idclass_id决定teacher_name而teacher_name是班主任的属性并不是学生本身的属性。它虽然完全依赖于主键但主键对它来说是绕路依赖。传递依赖带来的具体问题同一个班级100个学生class_teacher就会重复100次冗余明显班主任换了必须UPDATE这100行漏改一行同一个班级出现两个班主任班级还没有学生入驻时班主任信息完全无处可存还需要另搞一张空班级表来撑。5.2 如何识别这个字段该不该拆出去一个好的技巧是拿归属感来检测你写完一行记录后问自己一句——这个字段到底描述的是谁student_name描述的是学生class_teacher描述的是班级不是学生book_price描述的是书不是订单明细。一旦答案不是当前主键对应的实体本身就应该把它移动到它所描述的实体的表中去。这就是有些老手说的视图观——一张表最好只承载一种实体的事实。5.3 拆完之后的表结构长什么样按3NF修正学生表CREATE TABLE class ( class_id INT PRIMARY KEY, class_name VARCHAR(32), class_teacher VARCHAR(32) ); CREATE TABLE student ( student_id INT PRIMARY KEY, student_name VARCHAR(32), class_id INT, FOREIGN KEY (class_id) REFERENCES class (class_id) );这两个表各自都遵守3NF。需要查班主任时一条JOIN就搞定SELECT s.student_name, c.class_name, c.class_teacher FROM student s LEFT JOIN class c ON s.class_id c.class_id WHERE s.student_id 1001;从执行计划看可能多了一次连接但换来的是数据一致性、低冗余和清晰的领域边界。这也是所有规范设计的基本权衡。6. 一个订单场景的完整拆解从违反范式到大体合规理论讲完我拿一套实际的电商订单模型走一遍完整拆解。这块内容就是你最值得拿去参考的部分——从最初的大而全到拆完后的表组每一步改的理由都写清楚。6.1 最初的大而全表反例我刚开始做订单模块的时候经常产出这种表CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) UNIQUE, user_name VARCHAR(32), user_phone VARCHAR(20), user_address VARCHAR(128), product_ids VARCHAR(512), -- 多个商品ID逗号拼接 product_names VARCHAR(512), -- 对应多个商品名 product_prices VARCHAR(512), -- 对应多个商品价格 total_amount DECIMAL(10,2), pay_status TINYINT, pay_time DATETIME );一眼就能看出它同时违反三个范式product_ids / product_names / product_prices是典型的1NF反模式一个字段存了多个值如果强行拆成多行用户信息和订单信息又会大规模重复关联关系混乱商品名、商品价格如果跟订单绑在一起后续改动商品信息会非常痛苦。这种结构在功能Demo阶段跑起来很快但真要上线维护每一处都是暗雷。6.2 第一步先拆成实体表 关系表的主体结构我建议按这样几个步骤来做先列实体用户、商品、订单、订单明细一共四个核心概念为每个实体建立独立的主数据表用户表、商品表建立订单主表和订单明细表体现订单头与订单行的关系只保留必要的冗余比如冗余order_no方便查询其余依赖关系一律通过外键或索引建立。最终的建表脚本简化为核心字段-- 用户表1NF、2NF、3NF均可满足 CREATE TABLE user ( user_id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(32) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(128) NOT NULL ); -- 商品表 CREATE TABLE product ( product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(64) NOT NULL, price DECIMAL(10,2) NOT NULL ); -- 订单主表订单头信息 CREATE TABLE order ( order_id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL UNIQUE, user_id INT NOT NULL, total_amount DECIMAL(10,2) NOT NULL, pay_status TINYINT NOT NULL DEFAULT 0, pay_time DATETIME, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES user (user_id) ); -- 订单明细表反映订单和商品的多对多关系 CREATE TABLE order_item ( item_id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, -- 由于商品价格会变这里冗余保存下单时的快照价 FOREIGN KEY (order_id) REFERENCES order (order_id), FOREIGN KEY (product_id) REFERENCES product (product_id) );这里有一个细节需要单独说明有人会质疑order_item里的price是不是违背了3NF。商品表里已经有price了为什么这里还存一个价格答案是订单明细中的price必须保留它是下单当时的成交价快照。商品表的价格会变动可历史订单必须保留当时的价格事实。这是业务强约束也属于快照冗余。这类规范化与业务需求的冲突正是文章结尾要讲的反范式范畴。6.3 第二步为常用查询路径建立合适的索引规范拆表之后JOIN操作变多了。这时候索引的设计决定了查询体验。order_item.order_id查询一个订单的全部明细必须索引order_item.product_id按商品维度统计销量不能落下order.user_id查用户的历史订单列表业务上经常有按时间范围查订单的需求created_at也建议加入索引或跟pay_status组成联合索引。ALTER TABLE order_item ADD INDEX idx_order_id (order_id); ALTER TABLE order_item ADD INDEX idx_product_id (product_id); ALTER TABLE order ADD INDEX idx_user_id (user_id); ALTER TABLE order ADD INDEX idx_created_status (created_at, pay_status);有些人会说拆了表之后查询慢其实很多时候不是拆表的问题而是索引没补齐。规范化的代价在维护层面不在查询层面——这才是正确理解。6.4 第三步对比一下改前改后的收益这套表结构上线很久之后我回顾了一遍收益维度大而全反例规范化表组存储冗余商品名、价格每条订单重复一份只在订单明细冗余下单价格快照修改商品价格可能要UPDATE几百条订单记录只需改product表一行查询订单详情一条SQL拿全不行还要程序里按逗号拆分JOIN order_item即可按商品日报难以按分隔符聚合直接对order_item按product_id分组扩展性加字段就要动核心表可独立扩展用户画像、商品SKU更重要的是这张表的结构有清晰的领域语义。每个模块的负责人可以独立演进自己的表不需要理解一张大表里十几个字段的奇怪拼接这对团队协作的收益也很大。7. 过度规范化的代价什么时候该打破范式理论归理论现实中我见过不少团队因为过度追求范式把系统拆成了上百张表的精密仪器。查询一条页面数据要关联七八张表写一条复杂报表SQL跑了十分钟跑不出来。最后不得不加班做性能优化。范式的目标是消除冗余和不一致但它不是银弹。正常的工程实践中3NF是基准线但可以因为性能或业务需求选择性地打破它。7.1 打破范式最实用的三个场景可以总结为三类场景几乎覆盖了日常反范式设计的绝大多数理由场景一高频查询字段的快照冗余就像上面的order_item.price。用户下单成功后商品表价格改到十万八千里订单里还是显示你买的时候的价格。这类快照数据是合理的冗余不需要遵守3NF。场景二完全被包含的映射冗余比如在某个用户会员等级业务中你查出用户等级后经常还要展示等级名称、等级折扣。你把等级名称和折扣也一并放在用户表里省掉每次JOIN。更保险的做法是核心更新逻辑里把冗余字段一起更新用事务或者用定时任务兜底刷新。但这张表在写多读少的场景下要谨慎高频写会导致频繁同步。场景三内部汇总字段比如商品表里冗余一个monthly_sales字段。这个数值明显是订单明细表的统计结果理论上不符合3NF但它能让你免去高频SUM查询的消耗。配合定时任务按小时/天刷新收益非常明显。7.2 打破范式前的判断清单我自己在决定要不要反范式时会过一遍这个清单这个冗余字段是只读数据吗主要被用来展示冗余字段的更新频率有多大一旦更新是否失控读频率 写频率 吗统计一下实际执行的SQL分布再决定是否已有定时任务或消息机制能维持冗余数据的一致性如果以后数据量涨十倍这个设计方案是否还能撑住如果以上五个问题里有两个以上让你犹豫建议还是先按3NF拆表等真的出现严重的性能瓶颈再做冗余补偿。7.3 范式运用的度再补一条很重要的经验过程中的表更多地考虑消除冗余报表查询用的只读宽表可以大胆反范式。比如报表系统的底层很多团队干脆用一张极度违反范式的宽表把用户维度、订单维度、商品维度全部拉平。因为报表查询的特点是读多写少、只读不更新这时候宽表可以一次性给BI工具喂全量数据效率极高。对报表宽表讲范式是给自己找麻烦。8. 面试与工作中最常见的范式辨析盲区三大范式知识点本身不难但面试和实际协作中经常出现一些看起来会、一用就错的辨析题。我今天把几个高频问题一起理清楚。8.1 BCNF到底是什么和3NF有什么不同严格来说还有一个更高级的范式叫BCNFBoyce-Codd Normal Form。它的定义是对于表中的每一个函数依赖X→YX都必须是一个超键即能唯一标识整行的字段组合。3NF只约束非主键字段不得传递依赖主键但没限制主键字段之间的关系。某些特殊场景下一张满足3NF的表依然存在主键字段依赖于非主键字段的情况。举个经典例子CREATE TABLE course_teacher ( course_name VARCHAR(32), teacher_name VARCHAR(32), classroom VARCHAR(32), PRIMARY KEY (course_name, teacher_name) );假设规则是同一门课只能由一位老师上而同一教室也只能排一门课但同一老师可以教多门课。此时存在course_name → teacher_name和classroom → course_name依赖。表本身符合3NF可是当老师换人时你会被迫连classroom等其它信息一并调整非常别扭。BCNF会进一步禁止所有主键以外字段决定主键部分内容的情况。好在这个问题在现代工程中比较罕见日常设计一般满足3NF就可以打80分了。8.2 冗余不是罪恶但不一致的冗余才是很多人谈冗余色变实际上冗余是有层次的受控冗余快照、汇总、缓存复制可以极大提升查询性能失控冗余多表各自维护同一份事实一定会导致数据不一致。我在代码评审里最反感的不是有人加了冗余字段而是加了冗余字段之后没有更新链路。如果你要在一个表里额外放一个字段一定要在评审时讲清楚数据从哪来谁去更新同步失败怎么办答不出这三个问题说明你放的可能是失控冗余。8.3 反范式设计出现在设计评审里不等于水平低这是新人和经验丰富的人之间最大的观点差异。新人通常觉得应该完全遵守范式有经验的工程师则更关心该在哪一层、哪个字段适度违反范式。真正的设计能力是能在规范与效能之间做出有意识的取舍。如果你在评审时听到别人说不满足3NF正确的思考路径不是马上反驳而是先问这违反了哪条范式违反的理由是什么有没有同步机制保证数据一致9. 关于范式我的最终建议单独背三大范式是什么其实不难难的是在真实项目里做完选择后不后悔。我自己在经历过订单表拆解的完整过程以及后来为报表系统做的各种反范式设计后最大的体会是范式是一种思维方式不是教条。核心事务表、基础主数据表尽量往3NF靠报表查询、低频更新的统计场景可以用反范式换性能同时要永远给冗余字段配上同步链路否则规矩破得越爽事后修数据修得越痛。如果你现在找工作、准备面试三大范式的定义和例子必须能脱口而出。如果你已经在写业务代码我反而建议你把重心放在什么时候打破范式上——这个问题能体现的设计经验和思考深度恰恰是面试官和团队更看重的部分。
阅读完成 · 觉得有帮助？
