★ FEATURED ARTICLE 前阵子帮朋友看一个小库存系统的表结构打开数据库我人差点没坐住一张库存表里一个字段叫“标签”存的是“红,大,棉,男款”另一个字段叫“供应商”把负责人电话、地址、折扣比例全怼在一个字符串里。查询全靠 like 和字符串截取每次报错都得从头捋。这问题根子不在于 SQL 写得不够好而在于建表时压根没按数据库范式来设计。这不是个别现象。很多项目一开始都能跑等数据量上来、需求一改各种脏数据、重复数据、更新异常就开始大面积爆发。根子往往不在 SQL而在建表的时候没按“规矩”来。数据库圈子里的这套规矩就是范式——从第一范式到第三范式最常用往上是 BCNF、第四第五范式普通人能用到的基本就是前三个。这篇文章我想用管冰箱、写账本这类日常事做类比把范式这层窗户纸捅破然后给你一套真正能用、能照着改的实战方法。不管你是刚转行的数据分析师、在校学生还是被历史表结构折磨多年的老开发这套逻辑搞明白了后面所有的表设计都会顺很多。1. 为什么会有范式这套规矩1.1 没有范式的时候表到底有多乱想理解范式先看一张反例表。假设我们要管公司员工和部门最直觉的表长这样员工ID员工姓名部门部门经理办公地点办公电话001张三研发部王老师A栋3018001002李四研发部王老师A栋3018001003王五市场部赵老师B栋5018002这表看起来挺正常对吧但懂行的人一眼就能看出问题部门经理、办公地点、办公电话其实不依赖员工 ID它们只依赖“部门”。只要研发部来一个新员工这三列信息就被复制一遍。一千个研发部员工就有一千份一模一样的“王老师、A栋301、8001”。复制本身不可怕可怕的是要改的时候。王老师升职了你要把研发部的经理改成“刘老师”那就得 update 这张表里所有研发部的行。万一漏掉几行你会看到同一个部门有两种经理报表一发出去领导立刻发现不对劲。这就是更新异常。反过来如果把某部门的最后一个员工删掉部门本身的信息也跟着没了——这叫删除异常。要是新成立一个部门还没有分员工你甚至没法把部门先录进去——这叫插入异常。三大异常全是这种“一个表啥都装”装出来的。范式就是针对性地回答一个问题你表里的每一列到底应该由谁来决定。1.2 范式不是教条是设计目标很多人把范式背成几条定义考试能过做项目还是乱来。我建议你把范式理解成一系列“分家”的步骤每个范式都在回答“如果这张表里有多余的依赖关系怎么拆开才不丢信息”。1NF不许一个单元格里塞数组和菜篮子拆到每行每列都是原子值。2NF复合主键的表里非主键列不能只依赖主键的一部分拆掉部分依赖。3NF非主键列不能依赖另一个非主键列拆掉传递依赖。BCNF把函数依赖的左边都变成候选键算是 3NF 的加强版。4NF/5NF处理多值依赖、连接依赖属于学术和极端复杂场景。展开讲之前先说个原则范式不是越高越好。项目里绝大多数表到 3NF 就够了有时候还要故意往后退一退回到第二甚至第一范式来换性能。这点我放到后面细说先把三层基础范式吃透。2. 第一范式别在单元格里塞一篮子菜2.1 原子性的生活化理解第一范式1NF最简单的说法每一行、每一列交叉的那个格子里只能存一个值不能存列表、数组也不能存那种“用逗号分隔的一堆东西”。打个比方。你管一个冰箱冰箱的每个抽屉就是一个字段。按 1NF 的要求一个抽屉只放一类东西而且你贴了标签说“鸡蛋”里面就只能放鸡蛋不能放半抽屉鸡蛋加半抽屉葱。放在数据库里意思就是“兴趣爱好”这个字段不能存成“篮球, 阅读, 钢琴”而应该把兴趣爱好拆成多行或者拆到另一张专门存“用户-兴趣”关系的表里。有人会说那我用逗号分隔查询的时候用 find_in_set 不也能查吗能查但你会遇到一系列副作用统计兴趣人数要写奇怪的函数给用户加一个兴趣得读出整段字符串再拼接删一个兴趣更是碰运气。数据量上万以后这种字段就是长期技术债。2.2 违反 1NF 的三种典型场景实际业务里违反 1NF 最常见的就三类逗号分隔字符串像前面说的“标签”“兴趣”字段。看起来省了一张表实际把查询、统计、更新的复杂度全堆在 SQL 里。JSON 当万能字段很多团队图省事把不确定的字段全塞进一个 JSON 列。小规模原型没问题但一旦要对 JSON 里面的某个值做条件查询索引用不上性能瞬间垮掉。一个字段存多种含义比如“联系方式”字段里既有手机号又有座机号或者“地址”字段里塞省市区街道全拼在一起。虽然从数据库角度看它是个字符串但里面混了多种业务含义。2.3 怎么改成符合 1NF处理 1NF 的实操套路分两种能拆字段就拆字段拆不了就拆行。场景一字段含义混杂。比如“地址”拆成“省”“市”“区”“街道”。别怕字段多数据库多几个字段的成本远低于后面写一串 substring_index 解析的成本。场景二一个属性有多个值。比如用户有多个兴趣。要么在“用户”表里建“兴趣1、兴趣2、兴趣3”这种垃圾设计千万别学要么建一张子表CREATE TABLE user_interest ( user_id INT NOT NULL, interest VARCHAR(32) NOT NULL, PRIMARY KEY (user_id, interest) );这样每一个兴趣单独一行主键是 (user_id, interest)想统计“有多少人对篮球感兴趣”就是一句简单的 count想给用户加兴趣就插入一行。很多范式问题到最后都是靠这种“把一个字段的多值拆成多行”解决的。2.4 1NF 的一个争议这年头要不要严格 1NF有时候我也会用 JSON 字段但要区分场景。比如埋点事件里的自定义参数上千种业务自定义字段你不可能给每种参数建一张表这时候 JSON 是合理的。关键是对需要检索、计算、关联的属性必须拆出来成独立字段而对只做展示、存档、不参与核心逻辑的属性JSON 可以容忍。1NF 不是让你把所有 JSON 都干掉而是让你别把 JSON 当成所有设计问题的遮羞布。注意如果你决定用 JSON一定要把其中会被频繁过滤的字段用虚拟列索引的方式补出来否则上线半年后 DBA 会找你谈心。3. 第二范式半张表依赖半把钥匙当然要拆3.1 先看什么是“部分依赖”第二范式2NF是在 1NF 基础上往前走的。它只适用于“复合主键”的表——也就是主键是两列或更多列组成的情况。如果一张表的主键本身就是单列那它天然满足 2NF不需要额外操作。很多人设计表时主键都喜欢用自增 id于是 2NF 在他们那儿几乎没存在感。但复合主键的场景其实比想象中多选课表学号课程号、订单明细表订单号商品序号、角色权限表角色ID权限ID这些都逃不掉。举具体例子。有一张“学生选课表”学号课程号课程名称学分成绩S001C01数据库490S002C01数据库485S001C02算法395主键是 (学号, 课程号)。但“课程名称”和“学分”只依赖课程号和学号没关系。这意味着同一个课程被 100 个学生选课程名称和学分就重复 100 遍。改学分的时候要 update 100 行万一漏几行同一个课程在系统里就有两个学分后面算绩点直接乱套。这就是部分依赖有些列只依赖复合主键的一部分。有部分依赖存在就不满足 2NF。3.2 拆解方法去掉那个多余的依赖处理方式很直接把真正依赖于“课程号”的列抽出去单独建“课程表”。课程表课程号课程名称学分C01数据库4C02算法3选课表去掉了课程名称和学分学号课程号成绩S001C0190S002C0185S001C0295选课表里保留课程号的外键需要课程名称时通过 join 把课程表带出来就行。这样课程信息只存一份改学分只需要改一行删除学生也不会误删课程课程未开课也能先录入课程表。2NF 直接解决了这组插入、删除、更新异常。3.3 实操中识别部分依赖的三个判断法我在实际评审表结构时识别部分依赖基本靠三个问题这张表的主键是不是复合的不是那 2NF 直接跳过。每个非主键列能不能用主键的全部列来解释能继续。不能说明它只依赖其中一部分这就违规了。这列会不会在主键的一部分相同时出现大量重复如果会基本可以判定为部分依赖。第三个问题特好用。比如订单明细表 (订单号, 商品序号) 为主键“商品名称”“商品单价”是不是只跟商品 ID 有关是那就拆出去。很多外表的设计其实就是从这种局部重复里拆出来的。3.4 关于“先放一起再拆”的顺序有些人会有疑问为什么一开始不直接设计成三张表非要先放一张里然后再拆这个问题问得特别对。现实里你如果是一个人从零设计完全可以直接设计好。但范式这套操作真正的用武之地在于“已经有一张跑了几年的线上老表”的场景你不知道怎么把一张混杂了所有信息的表安全地拆开范式给了你明确的拆法和依据。拆的时候不是随便拆是根据“谁的依赖关系更紧密”来切这样才不会丢数据、不会破坏关系。所以学 2NF重点不是背定义是掌握“识别多余依赖”的眼睛。眼睛练出来了面对历史遗留脏表你就有了一把精密手术刀。4. 第三范式别让列像传话游戏那样拐弯4.1 传递依赖A 决定 BB 决定 C于是 A 绕远路决定了 C第三范式3NF针对的是传递依赖。它的定义是非主键列不能依赖于另一个非主键列。还是回到开头那个员工表。主键是员工 ID员工 ID 决定部门部门决定部门经理。于是员工 ID 绕了个弯也“决定”了部门经理。但部门经理显然不是员工本人的属性它属于部门。这种“非主键列之间还有依赖关系”的结构就是传递依赖。它造成的后果和 2NF 类似部门信息跟着员工复制。但和 2NF 不同的是2NF 的连接点是复合主键的一部分3NF 的连接点是另一个非主键列诊断起来更隐蔽。我见过最典型的反例是一张“订单表”订单号客户ID客户姓名客户等级客户所在城市O001C100老李金卡上海O002C100老李金卡上海O003C200小张银卡北京订单号是主键但客户姓名、客户等级、客户所在城市这些信息明明只由客户 ID 决定。客户信息跟着订单复制一遍又一遍客户改地址要更新其所有历史订单想想就头大。4.2 拆法剥掉中间那一环建依赖层的“专职表”处理 3NF 的办法同样是拆把依赖链条拆直客户表客户ID客户姓名客户等级客户所在城市C100老李金卡上海C200小张银卡北京订单表精简版订单号客户ID订单金额下单时间O001C1002992024-01-01O003C200882024-01-02客户改地址改客户表的某一列订单表一行不用动。查订单详情需要客户信息join 一下客户表逻辑清清楚楚。4.3 什么时候 3NF 会自动满足有个偷懒但好用的判断法如果一张表的主键是单列自增 id而且这张表里根本没有“另一个业务主键”出现比如“用户表”里的 id、手机号、昵称、注册时间那它基本天然满足 2NF 和 3NF因为手机号、昵称、注册时间全部直接依赖于 id非主键列之间也不存在相互依赖。真正需要你动手做 3NF 的往往是那些“把别的表的字段搬进来”的表订单表搬了客户字段商品表搬了分类字段报销单表搬了员工字段。遇到这种表多想一句我搬进来的这列是不是应该由这张表的主键直接决定如果答案是否就要警惕传递依赖。4.4 3NF 与 2NF 的实战联动实际项目里2NF 和 3NF 经常一起出现。我们看一个稍微复杂的例子一个“仓库调拨明细”表主键是调拨单号, 商品ID字段包括商品名称、商品规格、调出仓库ID、仓库名称、调拨数量。老规矩第一刀先解决部分依赖商品名称、商品规格都和商品 ID 有关和调拨单号没关拆出商品表仓库名称和仓库 ID 有关拆出仓库表。第二刀看看还有没有传递依赖调拨单号决定调出仓库 ID仓库 ID 决定仓库名称虽然拆完单表后仓库名称已经进仓库表了但如果当初只拆了商品把仓库名称留在明细表里依然是 3NF 违规。所以很多时候你要先按 2NF 的思路拆再按 3NF 的思路复查一遍两把刀轮流上。这套“先拆部分依赖再拆传递依赖”的操作本质上是在给数据做血缘梳理。表拆清楚了后面做数据仓库、做权限分级、做接口设计都会省一大堆心。5. 范式实战从零设计一个在线书店的数据库光讲概念不够尽兴咱们拿一个具体业务走一遍全流程。假设我现在要设计一个在线书店系统先丢一张最原始的“大表”出来——很多新手第一次设计系统最直觉的想法就是这样订单ID顾客姓名顾客城市商品名称商品作者商品价格购买数量O001张三上海数据库讲义刘老师591O001张三上海算法导论李老师791O001张三上海数据库讲义刘老师592O002李四北京数学之美王老师4915.1 第一刀处理重复购买的商品先看这条数据O001 订单里“数据库讲义”出现了两次分别是 1 本和 2 本。这说明什么不是数据录重复了而是我们把“买了几种书”和“每种书买了几本”的关系描述不清。真正的结构是订单里有“明细行”每一行是一次购买的“某一种商品”的明细同一种商品在一个订单里只会有一行数量放在明细行里。这一刀做下去其实已经暗合 1NF 原子性的精神一行记录一个不可再分的购买动作。你不可能让同一种商品拆成两行再分别写数量那样查询、统计都扭曲。5.2 第二刀按 2NF 拆出商品表这张原始表的主键如果硬定得是订单ID, 商品名称因为同一订单可以买多种商品。可是“商品名称”“商品作者”明显都只依赖“商品”这个实体和订单没关系算是典型的部分依赖。按 2NF 拆商品名称、商品作者、商品价格抽出去成一张“商品表”商品 ID 作为主键。于是订单明细行不再存“商品名称文字”这么一坨而是存“商品ID”通过外键引用商品表。为什么商品价格也放商品表因为现在这个业务里价格是商品目录价。如果你做促销活动、每个订单的真实成交价可能不同那“成交价”就必须留在明细行里而不是跟着商品表走。这种“同一字段在不同上下文里该放哪”的取舍是表设计最容易翻车的地方后面我会专门做一张对照表。5.3 第三刀按 3NF 拆出顾客表再看“顾客姓名”“顾客城市”。它们明显由“顾客实体”决定而不是由订单决定。如果只依赖订单 ID那么每个订单都要复制一遍顾客姓名和城市顾客搬家时历史上几十张订单全要更新这就是传递依赖的另一个变种。按 3NF 拆顾客信息进“顾客表”订单表里只留顾客 ID。于是订单表变成订单ID顾客ID下单时间O001C12024-03-01O002C22024-03-02订单明细表变成订单ID商品ID购买数量成交价O001B1159O001B2179O001B1259O002B3149看这版是不是清爽多了但细看O001 里 B1 又出现了两行。这说明订单明细的主键设计还有问题同一订单同一商品只能有一行应该把订单 ID 和商品 ID 组成联合主键或者至少加唯一索引。在真正的订单系统里通常还会加入“下单批次号”或“行号”让每一明细行拥有唯一性。这里的要点是范式只告诉你拆分原则具体到唯一性约束、索引设计还得自己补。5.4 拆完之后的全景最终的四张表顾客表顾客ID顾客姓名顾客城市C1张三上海C2李四北京商品表商品ID商品名称商品作者目录价B1数据库讲义刘老师59B2算法导论李老师79B3数学之美王老师49订单表订单ID顾客ID下单时间O001C12024-03-01O002C22024-03-02订单明细表订单ID商品ID购买数量成交价O001B1159O001B2179O001B1259O002B3149这样从顾客视角查订单顾客表→订单表→订单明细表→商品表链路清晰从订单视角查顾客反过来 join 也一样容易。要统计某个作者的书卖了多少本join 两三次就能算出来还不用考虑重复复制信息带来的维护痛苦。5.5 一套完整的建表 SQL 建议把上面的设计落成库表一套干净的建表语句大致长这样CREATE TABLE customer ( customer_id INT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) NOT NULL, city VARCHAR(50) NOT NULL ); CREATE TABLE product ( product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, author VARCHAR(50) NOT NULL, price DECIMAL(10, 2) NOT NULL ); CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, order_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ); CREATE TABLE order_item ( order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, deal_price DECIMAL(10, 2) NOT NULL, PRIMARY KEY (order_id, product_id), CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(order_id), CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product(product_id) );建表的 SQL 表面上一看都是 create table但每一行的约束都在兑现前面拆表时下的判断外键保证引用完整联合主键保证同订单同商品唯一价格字段用 decimal 而不是 float避免金额精度出鬼。6. 范式的边界哪些场景要科学地“造反”6.1 性能优先时反范式不是妥协是另一种严谨把范式讲到五张表六张表之后你一定会遇到一个问题查询的 join 太多了。一个列表页要联五张表数据库压力大接口响应慢这时候就需要“反范式”——刻意把某些字段复制回来用空间换时间。最常见的反范式场景有三类冗余计数字段比如订单表里加一列“商品种类数”每次下单选完商品就顺手 update 一下查询列表页时直接展示不用再 count 明细表。冗余热门展示字段比如商品表里存“分类名称”而不是 join 分类表。分类改名时批量 update 一次商品表就行。大数据分析场景数据仓库里的宽表经常把几十张表 join 好然后落成一张大宽表供报表直接查。这跟 OLTP 事务系统追求的低冗余完全不是一个逻辑。关键区别在于事务系统讲究“写入时的一致性”冗余数据一旦多了更新就麻烦分析系统讲究“查询时的速度”冗余数据能省掉每次报表跑批的 join 成本。前者反范式要极其克制后者反范式是常规操作。6.2 反范式怎么反才不翻车我见过太多“反范式反过头”的案例最经典的坑是冗余了字段但没人负责同步。比如你在订单表里冗余了“顾客姓名”顾客改名后忘了同步订单表报表里顾客名字就对不上了。正确的反范式必须配套三件事明确冗余字段的同步时机是在业务写入时同步还是定时任务批量同步还是通过消息队列异步同步。明确冗余字段的归属方必须有一个“源表”其他都是副本。源表改了副本必须跟着改谁改的、什么时候改的要有记录。明确可容忍的延迟有些报表场景第二天才同步也能接受有些交易场景一秒都不能忍。还有一种更精细的做法用触发器或存储过程自动维护冗余字段。比如 MySQL 里可以建触发器在订单明细插入后自动更新订单表的“商品种类数”。触发器好处是同步逻辑内聚坏处是调试困难、对性能有一定影响小表没问题大表要慎重。我更推荐在业务代码里显式维护至少出问题时能根据日志定位。6.3 范式等级选择一个可以参考的决策表业务场景推荐范式等级理由OLTP 核心交易表订单、支付3NF 优先一致性最关键写入多、更新多OLTP 配置表商品、分类3NF冗余少维护成本低统计报表宽表1NF 或反范式查询性能优先允许冗余日志、事件流水1NF 即可写入量巨大字段以 JSON 兜底多级分类树3NF特殊优化加上闭包表或路径枚举避免深 join这张表不是标准答案而是个思考方向。核心思路是先想清楚这个表的读多还是写多一致性要求多高再决定范式和冗余的平衡点。7. 从范式中真正学到的东西7.1 第一课识别冗余范式给我的核心训练不是记住定义而是看到一张表就能条件反射地问哪一列是被别的列决定的哪一列会在很多行里重复出现这种直觉在代码评审和库表评审时特别有用。别人拿一张表过来我扫一眼就能看出“这个部门经理不应该在员工表里”“这个客户城市放在订单表是个隐患”。冗余有时候藏得很深但范式给了你一套寻找它的系统方法一切从主键出发非主键列要么完全依赖主键要么就该去别的表。7.2 第二课拆分的胆量很多人不敢拆表怕业务代码要改太多。但我的经验是越晚拆越难拆。表结构一上生产就有数据、有线上代码、有各种报表依赖再想动就像给飞驰的汽车换轮胎。所以新表设计时多花十分钟拆干净比上线后加班一个月救火划算得多。如果确实要改老表原则也是先加新结构、同步数据、验证无误后再废弃旧结构别想着一步到位。7.3 第三课当规范与性能打架时范式不是枷锁。用不用反范式取决于业务对一致性和性能的权衡。只要同步机制可靠、延迟可控、边界清晰反范式是完全科学的设计。最怕的是那种“因为赶进度随手冗余事后没人维护”的脏设计。我自己每设计完一张新表都会用三个问题自审主键是什么其他列都直接依赖主键吗有没有哪一列其实根本不归这张表管三个问题问完问题基本就暴露了。范式定义可以忘这三个问题建议焊在脑子里。
阅读完成 · 觉得有帮助？
