1. 先把“数据库规范化”这件事讲透1.1 Normalization 不是机器学习里的正则化第一次看到“正则化Normalization”这个词的人十有八九会跑偏到机器学习那边去想到的是给损失函数加 L1/L2 惩罚项、抑制过拟合的那套东西。但数据库语境下的 Normalization中文更准确的译法是规范化或者范式化也有资料译作“正规化”个别地方直接写成“正则化”于是就有了这个标题。它跟机器学习里的正则化没有半点数学关系唯一相似的地方大概是——两者都是在跟“冗余”作斗争都是为了别让系统在规模变大之后自我崩坏。数据库规范化Normalization讲的事情用一句话概括就是把一张字段塞得满满当当的大宽表按照函数依赖的关系一层一层拆成若干张职责单一的小表最终目标是让同一条事实在库里只存一份消除插入异常、更新异常和删除异常。这套拆解过程被 Codd 在 1970 年代形式化成了一系列“范式”Normal Form标准依次是 1NF、2NF、3NF、BCNF、4NF、5NF。这篇文章要拆的是最基础也最关键的四档UNF非第一范式、1NF、2NF、3NF。适合谁看如果你写过建表语句但从来没认真想过“这个字段到底该放订单表还是客户表”如果你接手过一张三十多个字段、谁都不敢动的祖传大表如果你面试被问到“2NF 和 3NF 的区别”只能背定义说不清原理那这篇内容基本就是给你准备的。我尽量不用教科书的腔调而是按实际建库时的思考顺序来讲把每一步“为什么这么拆”讲明白看完你至少能自己把一张脏表推演到 3NF并且知道哪些地方可以故意不拆。1.2 一张“什么都能干”的大表是怎么把系统拖垮的先不讲定义。我拿一张很典型的订单宽表开场这张表在不少早期系统里真实存在过订单号客户编号客户姓名客户电话下单时间商品编号商品名品类品类描述单价数量金额收货地址D001C01张三138xxxx1111,139xxxx22222024-03-01P01,P02键盘,鼠标外设电脑周边配件199,591,2317江苏省南京市玄武区xx路1号D002C02李四137xxxx33332024-03-02P03显示器显示设备屏幕类产品8991899浙江省杭州市西湖区yy路2号这张表看上去“信息很全”一个查询就能把订单详情拉出来早期报表需求确实爽。但只要业务开始跑起来问题会一个接一个冒出来插入异常新签了一个品类但还没有任何商品录入这个品类信息就无处安放除非硬造一条商品为空的假记录新招了一个客户还没下单客户信息也存不进去因为没有订单号这个主键。更新异常客户张三改了手机号如果他有 20 个订单你得更新 20 行漏掉一行库里就同时存在两个版本的手机号谁也说不清哪个是真的。品类描述改成“电脑外设及配件”得把该品类下所有商品的每一行都刷一遍。删除异常某个商品卖完了要从系统里下架删掉它最后一条订单记录连带这个商品的所有信息、甚至这个品类的信息一起消失而这些信息本来跟订单没关系。这三种异常是同一根因的三种表现非键字段之间存在不该有的依赖关系。规范化的整套方法论本质上就是在系统性地找这些依赖、然后把它们切开。所以学范式不能只背“1NF 是原子性”得把“函数依赖”这个概念焊在脑子里后面每一步判断都靠它。1.3 范式的适用边界3NF 是事实标准但不是教条我见过两个极端。一个极端是“范式原教旨主义”任何一张表只要出现一点点冗余就如临大敌硬要拆到 BCNF最后业务查一次订单列表要 JOIN 七张表QPS 直接腰斩。另一个极端是“能跑就行主义”所有数据往一张宽表里塞用逗号分隔的字符串存标签用 JSON 存明细等到要做数据统计的时候天天写正则表达式拆字符串。我的实际立场是3NF 是绝大多数 OLTP 业务库的默认落点它已经把三种异常基本消灭干净同时 JOIN 深度还在可控范围内。BCNF 在少数场景下需要比如一个表存在多个候选键且主属性之间互相依赖的时候。而到了数据仓库、报表集市、缓存宽表这类读多写少、以查询性能为第一优先级的地方故意反规范化是常规操作把维度表的字段冗余进事实表用空间换时间这不叫设计失误叫设计选择。所以这篇文章讲拆分但会在每个阶段说清楚“这一步拆完得到了什么、代价是什么”最后还会专门讲反规范化的取舍。你拿到任何一张表最终要回答的问题不是“它符不符合 3NF”而是“它承担了哪些业务事实这些事实拆开后各自的更新频率和一致性要求是什么”。2. UNF 到 1NF把格子里的东西拆干净2.1 UNF 到底长什么样为什么它连表都算不上UNFUnnormalized Form非第一范式不是一个“及格线以下”的正式状态它描述的是一种还带有重复组和多值字段的表格形态。最典型的特征就是上面那张订单表里出现的东西客户电话格子塞了“138xxxx1111,139xxxx2222”两个值商品编号塞了“P01,P02”商品名塞了“键盘,鼠标”收货地址一整串连着省市区和详细门牌。这种形态在 Excel 里是完全合理的表达方式——人眼一看就懂一个人有两个电话就写两行字。但关系数据库的存储模型建立在关系之上关系要求每一行每一列的交点是一个不可再分的值原子值因为只有这样数据库才能对某一列建立索引、做等值比较、做聚合统计。你存了一串“138xxxx1111,139xxxx2222”数据库对它就是一个字符串你想按手机号查某个客户只能LIKE %139xxxx2222%索引直接失效还容易误命中。判断一张表是不是 UNF最快的办法是问自己三个问题有没有哪个格子里的值需要“再分”才能用比如地址要不要拆成省市区、金额要不要拆成数值和币种有没有哪个字段名带序号后缀比如商品1、商品2、电话1、电话2只要出现这种命名基本就是重复组有没有哪一列存的是列表逗号分隔、分号分隔、JSON 数组、竖线分隔都算。2.2 1NF 的三条硬性要求1NF 的要求说白了就三条每条背后都对应一个数据库的实现约束第一条每一列都是原子值。一个格子里只能放一个不可再分的值。这条的判定标准取决于业务粒度不是绝对意义上的“不能再分”——地址存成“江苏省南京市玄武区xx路1号”算不算原子如果你只用来打印快递单它就是原子的但你要做“按城市统计订单量”它就违反了 1NF 的精神得拆成省、市、区、详细地址四个字段。所以原子性是相对于你需要的查询粒度而言的这个概念后面还会反复用到。第二条每一列的类型和语义一致。同一列不能这一行存电话、下一行存邮箱也不能这一行存“199 元”、下一行存“价格待定”。数据库对列有数据类型约束但像VARCHAR这种宽容类型拦不住语义混乱得靠设计者自己把关。我见过最离谱的一张表备注字段里混着客服留言、退货原因、内部标记码后来做退货分析时只能靠关键词匹配痛苦程度难以描述。第三条行没有顺序也不能有完全重复的行。关系是集合集合元素无序且不重复。这条在物理层面由主键来保证——1NF 的表必须能确定一个主键无论是单列的自然键还是多列组成的复合键。这一点经常被忽略一张表如果找不出任何能唯一标识一行的字段组合它连 1NF 都进不去只能加一个自增代理键。注意很多人把“把多值列拆成多列”当成 1NF 化的操作比如把电话拆成电话1、电话2、电话3。这不算 1NF这叫变相保留重复组只是把横着的重复组改成了竖着的。真正的 1NF 是拆成多行或者拆到独立的子表里去。2.3 拆解实操三种拆法的取舍面对那张订单宽表UNF 到 1NF 有两条主流路径选哪条取决于你的业务语义。路径一拆成行让复合键成立。一行订单里买了两个商品就拆成两行(订单号, 商品编号)作为复合主键。电话有两个怎么办电话是客户属性、不是订单明细属性硬拆成行会把订单行数放大所以电话应该走另一条路——拆到客户子表。这条路径的产物是这样订单号商品编号客户编号客户姓名下单时间商品名品类单价数量金额D001P01C01张三2024-03-01键盘外设1991199D001P02C01张三2024-03-01鼠标外设592118D002P03C02李四2024-03-02显示器显示设备8991899路径二拆成子表。保留一行订单把明细整体挪到订单明细表主表只管订单头和客户信息。这是实际工程里更常见的做法因为它天然贴合“订单头 订单行”的业务模型前端展示也方便。这两种路径在 1NF 层面都合格区别在于复合键的设计。路径一的主键是(订单号, 商品编号)路径二的主键是订单号明细表另立门户。我个人的偏好是路径二因为订单头和订单行在业务上的生命周期完全不同订单头有状态流转待支付、已支付、已发货、已签收订单行只是商品快照混在一张表里会让状态字段在每一行重复白占空间还容易改漏。2.4 1NF 阶段最容易踩的三个坑坑一把逗号分隔的字符串当成“灵活设计”。一开始图省事标签存成新品,热销,包邮后来要查“所有包邮商品”只能写LIKE %包邮%再后来标签改名成“免运费”几百万条数据要靠 UPDATE REPLACE 刷一遍中途还可能误伤“包邮险”这种包含子串的标签。这类问题的修复成本随时间指数上升能在 1NF 阶段解决就绝不要往后拖。坑二原子性判定过粗或过细。判太粗把手机号、邮箱、传真号全塞进一个联系方式字段后续没法按类型查询判太细把“姓名”拆成“姓”和“名”结果遇到少数民族姓名、外文姓名、复姓就全乱套还得写一堆兼容逻辑。判断标准始终是查询需求不是理论上的“能不能再分”。坑三忽略 NULL 的语义。1NF 不禁止 NULL但 NULL 有三种截然不同的含义值不存在客户没有传真号、值未知还没填、值不适用这个商品没有折扣。这三种混在同一个 NULL 里统计的时候永远对不上账。实际做法是能用默认值表达的用默认值比如折扣率默认 0实在要多态语义就加一个状态标记字段别指望靠 NULL 区分。3. 1NF 到 2NF消灭部分依赖3.1 函数依赖整篇文章的地基要讲 2NF绕不开函数依赖Functional Dependency简称 FD。定义很朴素在关系 R 中如果对于任意两行只要 X 的值相同Y 的值就一定相同就说Y 函数依赖于 X记作X → YX 叫决定因素。翻成人话知道 X 就能唯一确定 Y。举例在订单明细表里商品编号 → 商品名成立因为一个商品编号只对应一个商品名商品名 → 商品编号一般不成立除非你保证商品名全局唯一实际业务里经常不保证同名不同规格的商品一堆。(订单号, 商品编号) → 数量成立因为要确定“买了几件”必须同时知道是哪张订单里的哪个商品。还有两个衍生概念必须分清完全函数依赖X → Y且 X 的任何真子集都不能决定 Y。也就是说 Y 必须依赖 X 的全部属性。部分函数依赖X → Y但 X 的某个真子集就能决定 Y。比如(订单号, 商品编号) → 客户姓名实际上订单号单独就能确定客户姓名商品编号是多余的。这就是部分依赖。2NF 的定义就一句话在 1NF 的基础上每个非主属性都完全函数依赖于候选键。注意这里说的是候选键不是主键——这个区别很多人背定义时忽略了但恰恰是判断 2NF 时最容易出错的地方后面 3.4 节会专门讲一个反例。顺便把主属性和非主属性也说清楚包含在任何一个候选键里的属性都叫主属性剩下的全是非主属性。所以判断 2NF 的完整流程是先求出所有候选键标出主属性再检查每个非主属性是否完全依赖于每一个候选键。3.2 候选键怎么求属性闭包手算法求候选键的方法叫属性闭包Attribute Closure。给定属性集 X求 X 能推导出的所有属性集合记作 X⁺。算法很机械初始时 X⁺ X扫描所有 FD如果某个 FD 的左部全部落在 X⁺ 里就把右部也加进 X⁺重复第 2 步直到 X⁺ 不再变化如果 X⁺ 包含了全部属性X 就是超键如果 X 的任何真子集都不是超键X 就是候选键拿 3.1 节的订单明细表练一遍。假设属性集R(订单号, 商品编号, 客户编号, 客户姓名, 商品名, 数量)已知 FD订单号 → 客户编号客户编号 → 客户姓名商品编号 → 商品名先试(订单号, 商品编号)⁺初始是{订单号, 商品编号}扫到订单号 → 客户编号加进来再扫到客户编号 → 客户姓名加进来扫到商品编号 → 商品名加进来。结果{订单号, 商品编号, 客户编号, 客户姓名, 商品名}加上本身隐含的数量等于全体属性所以(订单号, 商品编号)是超键。再验证它的真子集订单号⁺ {订单号, 客户编号, 客户姓名}不含商品编号和数量不是超键商品编号⁺ {商品编号, 商品名}也不是。所以(订单号, 商品编号)是唯一的候选键主属性就是这两列非主属性是客户编号、客户姓名、商品名、数量。现在检查部分依赖订单号 → 客户编号 → 客户姓名说明客户姓名只依赖订单号这一半不满足完全依赖商品编号 → 商品名同理。这张表不是 2NF。3.3 拆分实操按依赖关系一分为三2NF 的拆分原则非常直接把部分依赖的那部分抽出去独立成表。按上面的分析订单明细表要拆成三张订单表(订单号, 客户编号, 客户姓名, ...)主键订单号商品表(商品编号, 商品名, ...)主键商品编号订单明细表(订单号, 商品编号, 数量)主键(订单号, 商品编号)两个外键分别指向前两张表拆完的好处是立刻可见的客户张三改手机号只改订单表里属于他的那一行如果客户信息再往下拆到客户表连订单表都不用动商品改名只改商品表一行所有引用它的订单明细自动“同步”商品下架删商品表记录不会影响历史订单明细里的数量。建表的 SQL 大致长这样以 MySQL 为例字段做了精简CREATE TABLE product ( product_id VARCHAR(16) NOT NULL, product_name VARCHAR(64) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order_detail ( order_id VARCHAR(16) NOT NULL, product_id VARCHAR(16) NOT NULL, qty INT NOT NULL DEFAULT 1, PRIMARY KEY (order_id, product_id), KEY idx_product (product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意order_detail上的索引主键(order_id, product_id)天然能支撑“按订单查明细”的查询但“按商品查销量”走不了这个前缀所以要单独给product_id建一个辅助索引。这是拆分之后必然要付的代价——表变多了索引也得补不能指望拆完之后什么都不管性能还好。3.4 常见误判什么时候“看起来像”部分依赖其实不是这里有个经典反例专门用来打“凭主键判断 2NF”的脸。假设有一张授课关系表授课(学生, 课程, 教师)业务规则是每位教师只教一门课同时一名学生在同一门课上只跟一位教师。由此得到两个 FD(学生, 课程) → 教师和教师 → 课程。求候选键。(学生, 课程)⁺ {学生, 课程, 教师}是超键。(学生, 教师)⁺由教师 → 课程推出{学生, 教师, 课程}也是超键。所以这张表有两个候选键(学生, 课程)和(学生, 教师)主属性是学生、课程、教师三个没有非主属性。按 2NF 的定义“每个非主属性都完全依赖于候选键”因为没有非主属性条件空成立这张表是 2NF。但换个角度如果按主键(学生, 课程)检查教师 → 课程这个依赖里教师是(学生, 教师)的真子集却决定了主属性课程这在直觉上明显是冗余的——教师和课程的对应关系在每一行学生记录里重复。它其实违反的是 3NF 的等价定义后面 4.2 节会讲而不是 2NF。这个例子告诉我们两件事第一判断 2NF 必须用候选键而不是主键选哪个候选键当主键只是物理实现问题不影响范式判定第二教科书的范式定义有它的严格边界实际工作中遇到多候选键的情况更省事的做法是直接看哪些 FD 的决定因素不是超键把这个当作通用判据比逐级背定义快得多。4. 2NF 到 3NF斩断传递依赖4.1 传递依赖的本质是“链条太长”传递函数依赖的定义如果X → Y、Y → Z且Y ↛ XY 不能反推 XZ ⊄ Y那么称 Z 传递函数依赖于 X。用人话说就是A 决定 BB 决定 C而 B 本身不唯一那么 A 通过 B 间接决定了 C中间这层 B 就是冗余的来源。3NF 的定义在 2NF 的基础上每个非主属性都不传递依赖于任何候选键。还有个更实用的等价定义我个人更推荐用它来判断对于 R 中的每一个非平凡函数依赖X → A要么 X 是超键要么 A 是主属性。所谓“非平凡”就是 A 不属于 X。这个定义的妙处在于它不需要你先算出候选键再逐层检查直接一条条 FD 过一遍就行。而 BCNF 的定义只把这个定义里的“或 A 是主属性”去掉——也就是说 BCNF 要求每个决定因素都必须是超键。3NF 比 BCNF 松的那一点点全在“A 是主属性”这个例外上。举个最经典的例子员工(工号, 姓名, 部门编号, 部门名称, 部门负责人)FD 是工号 → 姓名、工号 → 部门编号、部门编号 → 部门名称、部门编号 → 部门负责人。候选键只有工号非主属性是姓名、部门编号、部门名称、部门负责人。看工号 → 部门编号 → 部门名称这条链上部门编号 ↛ 工号所以部门名称传递依赖于工号。问题很直观一个部门有 50 个人部门名称和负责人就在 50 行里重复了 50 次部门换负责人时要更新 50 行漏一行就出现两个负责人。拆分方案毫无悬念员工(工号, 姓名, 部门编号)部门(部门编号, 部门名称, 部门负责人)。这个“主表 字典表”的结构几乎所有业务系统里都能见到它其实就是 3NF 的直接产物。4.2 3NF 体检的实操清单拿到一张已经满足 1NF 和 2NF 的表做 3NF 检查我一般按这个顺序走比死抠定义快第一步列出所有 FD。不要凭字段名猜要去翻业务文档、问产品、看代码里的校验逻辑。比如“订单总额 各明细金额之和”看起来像 FD但它是聚合计算不是表内 FD别混进来。第二步找出所有决定因素。把每个 FD 的左部单独拎出来看它是不是候选键或超键。如果某个左部既不是超键又不是候选键的真超集那它就有问题。第三步看每个决定因素的右部是不是主属性。如果右部全是主属性恭喜可能是 3NF 的例外情况如果右部有非主属性那基本可以确定存在传递依赖该拆了。第四步追问“这个属性是不是只被一个决定因素唯一确定”。这句话有点绕举例说明商品编号 → 品类编号 → 品类名称品类名称只被品类编号确定那就该跟着品类编号走不该留在商品表里。反过来商品编号 → 成交价成交价虽然也是被商品编号加上订单号确定的但它有历史快照语义那就得留在明细表里这是后面要讲的反规范化例外。4.3 真实案例一个商品表是怎么从 1NF 一路拆到 3NF 的我把前面订单表的商品部分完整走一遍。1NF 之后的商品信息大概是商品(商品编号, 商品名, 品类编号, 品类名称, 品类描述, 供应商编号, 供应商名称, 供应商联系人, 单价)FD 链条商品编号 → 商品名, 品类编号, 供应商编号, 单价品类编号 → 品类名称, 品类描述供应商编号 → 供应商名称, 供应商联系人候选键是商品编号其余全是非主属性。一眼看去两条传递依赖链商品编号 → 品类编号 → 品类名称/品类描述以及商品编号 → 供应商编号 → 供应商名称/供应商联系人。3NF 化之后变成三张表表名主键主要字段表承担的业务事实product商品编号商品名、品类编号、供应商编号、单价单个商品的基础属性category品类编号品类名称、品类描述品类的公共属性supplier供应商编号供应商名称、联系人、联系方式供应商档案拆完的效果是什么品类改名改一行供应商换联系人改一行商品调价改一行但历史订单里的成交价不受影响因为订单明细存了快照价。这就是 3NF 的核心收益每一条业务事实只有一个权威存储位置single source of truth任何变更都只有一处需要维护。对应的建表语句重点看外键和约束CREATE TABLE category ( category_id VARCHAR(16) NOT NULL, category_name VARCHAR(64) NOT NULL, description VARCHAR(255) DEFAULT NULL, PRIMARY KEY (category_id), UNIQUE KEY uk_category_name (category_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE product ( product_id VARCHAR(16) NOT NULL, product_name VARCHAR(64) NOT NULL, category_id VARCHAR(16) NOT NULL, supplier_id VARCHAR(16) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (product_id), KEY idx_category (category_id), KEY idx_supplier (supplier_id), CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES category (category_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意uk_category_name这个唯一约束别省。3NF 只保证“品类名称跟着品类编号走”但不保证“一个品类名称只对应一个品类编号”。如果业务上品类名不允许重名必须靠唯一索引兜住否则同一个名字能在品类表里建出两条记录往下挂商品的时候数据就分裂了。范式负责结构约束负责语义两者缺一不可。4.4 拆过头的代价什么时候该停手规范化不是免费的。每拆一次写操作要多一次 INSERT读操作要多一次 JOIN事务边界变长缓存失效点变多。我见过一个极端案例某团队把一张订单表按范式拆成了 11 张只为一个订单详情页的展示接口要写 12 次 JOIN主库 CPU 常年 70% 以上后来做了一次“订单快照表”的冗余接口耗时从 800ms 降到 60ms。哪些地方值得故意保留冗余我总结了几类历史快照类。订单明细里的成交单价、成交时的商品名、收货地址。这些字段看似是商品表、客户表的冗余但它们的语义是“下单那一刻的值”属于业务事实本身不是派生数据。商品后来涨价、客户后来搬家历史订单必须保持原样所以这些冗余是必须存在的不叫反规范化叫正确的建模。计算派生类。订单总额 各明细金额之和。理论上可以每次查询时聚合但订单列表页要分页、要排序、要筛选金额区间实时聚合基本不可能。做法是存一个total_amount同时在应用层用事务保证它和明细一致定期跑对账任务校验。这类冗余的代价是一致性风险收益是查询性能值不值取决于读写比。高频关联类。用户表、部门表这类变动极少、查询极频繁的小字典表可以在主表里冗余一两个展示字段比如部门名称减少 JOIN。但前提是接受偶发不一致或者有机制保证改名时同步刷新。判断标准其实很简单问自己这个冗余字段的更新频率和主表的更新频率差多少。商品名一个月改一次订单明细一天写十万行冗余商品名就是划算的客户手机号一天改几十次你把它冗余进二十张表那就是灾难。5. 手把手走一遍完整案例从一张脏表到 3NF5.1 原始需求和那张“祖传大表”假设某公司接手了一个早期的销售管理系统核心就是一张表所有数据都在里面订单号订单日期客户编号客户名联系电话收货地址商品列表总金额业务员业务员电话备注D10012024-05-01C01张三138xxxx1111,139xxxx2222江苏南京玄武区xx路1号P01 键盘 x1;P02 鼠标 x2317王五136xxxx9999加急D10022024-05-03C02李四137xxxx3333浙江杭州西湖区yy路2号P03 显示器 x1899王五136xxxx9999无这张表的问题一眼能数出一堆联系电话多值商品列表多商品混在一个格子里还带数量收货地址没有结构化备注里混着优先级和自由文本。这就是典型的UNF。5.2 逐级推导每一步到底动了什么UNF → 1NF。三处非原子字段要处理联系电话拆到独立的客户联系方式表收货地址拆成省、市、区、详细地址商品列表拆成明细行一行一个商品带上数量。拆完之后主键是(订单号, 商品编号)业务员信息还挂在订单上。这一步做对了的标志是你能用一个主键唯一确定一行且每个格子里只有一个值。1NF → 2NF。列出 FD订单号 → 订单日期, 客户编号, 客户名, 业务员(订单号, 商品编号) → 数量, 成交单价商品编号 → 商品名。候选键是(订单号, 商品编号)。部分依赖很清楚客户名、业务员只依赖订单号这一半商品名只依赖商品编号这一半。拆成订单表、商品表、订单明细表。2NF → 3NF。订单表里继续查传递依赖订单号 → 客户编号 → 客户名、客户电话订单号 → 业务员编号 → 业务员姓名、业务员电话。拆出客户表、业务员表。商品表里商品编号 → 品类编号 → 品类名称再拆出品类表。最终的库结构是六张表表名主键说明customer客户编号客户主档姓名、等级等customer_phone(客户编号, 电话类型)一个客户多个电话拆行存储salesperson业务员编号业务员档案姓名、电话、所属区域orders订单号订单头客户编号、业务员编号、下单时间、状态、总金额order_item(订单号, 商品编号)明细数量、成交单价、成交时商品名product商品编号商品主档名称、品类编号、当前售价5.3 迁移脚本怎么写才不出事从一张大表迁到六张表最怕的是中途数据错乱。我的做法是分批、幂等、可回滚具体步骤第一步先建新表不动老表。老表改名成order_raw保留原始数据新表按上面的结构建好加好主键、外键、唯一约束。这一步只做结构不搬数据风险为零。第二步先迁主体字典再迁事实表。顺序必须是customer→customer_phone→salesperson→product→orders→order_item因为有外键依赖先迁被引用的表。迁客户表时用SELECT DISTINCT从老表抽客户遇到同一客户编号对应多个客户名的情况先不覆盖单独拉出来人工核对-- 找出客户编号与客户名不一致的脏数据 SELECT customer_id, COUNT(DISTINCT customer_name) AS name_cnt FROM order_raw GROUP BY customer_id HAVING COUNT(DISTINCT customer_name) 1;这个查询非常关键。它查出来的每一条都是老表里的实际矛盾说明同一个客户编号下存过不同的名字。这类数据你没法自动决定哪个是对的必须走人工或业务规则裁决。我遇到过几百条这种情况八成是客户改过名两成是录入错误最后靠“取最新订单的客户名”这条规则统一掉了但规则本身是业务方拍板的技术不能替他们决定。第三步迁移明细时拆分多值字段。商品列表是P01 键盘 x1;P02 鼠标 x2这种格式得用脚本按分号拆、再按空格拆出编号和数量。这一步一定要写校验 SQL迁移完比对总行数和总金额-- 迁移前后总金额对账 SELECT (SELECT SUM(total_amount) FROM order_raw) AS old_total, (SELECT SUM(qty * unit_price) FROM order_item) AS new_total;两个数必须相等。如果不等八成是拆分时漏了某个格式比如有人用了中文分号、用了逗号、数量写成中文“两件”把这些异常值捞出来单独补。第四步加外键和唯一约束跑一遍全量校验。建外键前先确认没有孤儿记录建唯一约束前先确认没有重复值否则约束会直接建失败——这其实是好事失败一次就暴露一批脏数据。第五步保留老表只读一段时间。至少保留一个业务周期比如一个月前端切到新表之后观察出问题能对照老表排查。确认无误后再归档不要一上来就删。5.4 拆完之后怎么验证“真的规范了”验证不能靠肉眼得写检查查询。几个我常备的“范式体检”语句-- 检查 1同一客户编号是否对应多个姓名看客户表是否被破坏 SELECT customer_id, COUNT(DISTINCT customer_name) FROM customer GROUP BY customer_id HAVING COUNT(DISTINCT customer_name) 1; -- 检查 2同一商品编号是否对应多个品类看商品表 3NF 是否被破坏 SELECT product_id, COUNT(DISTINCT category_id) FROM product GROUP BY product_id HAVING COUNT(DISTINCT category_id) 1; -- 检查 3订单总金额与明细汇总是否一致看反规范化字段是否漂移 SELECT o.order_id, o.total_amount, SUM(i.qty * i.unit_price) AS real_total FROM orders o JOIN order_item i ON o.order_id i.order_id GROUP BY o.order_id, o.total_amount HAVING ABS(o.total_amount - SUM(i.qty * i.unit_price)) 0.01;第一条和第二条正常情况下应该返回 0 行返回非空就说明有人绕过了约束直接改库或者某个写入路径有 bug。第三条在存了冗余总金额的情况下必然要定期跑它是反规范化的代价得用对账任务来偿还。6. 踩坑实录与问题速查6.1 几个我亲身踩过的坑坑一只拆了表忘了搬约束。把客户信息从订单表拆出去之后订单表的customer_id没建外键应用层也没校验。结果某个批处理任务写了一批customer_id不存在的订单跑了半年才发现订单详情页一片空白。外键不是装饰品它是拆表之后唯一能防止数据分裂的机械保障。如果你的团队因为性能原因禁用外键那就必须在应用层有一层统一的校验拦截不能指望每个开发者都记得。坑二用业务字段当主键后来要改。早期拿手机号当客户主键觉得天然唯一又能查。后来客户换号、一个手机号被两个人用、海外客户没有国内手机号格式主键直接失控改一次牵动十几张表。经验是凡是可能变化的业务字段一律不做主键用无意义的代理键自增 ID 或 UUID业务唯一性靠唯一索引保证。这样改业务字段只影响一行数据不影响任何引用关系。坑三拆分之后 JOIN 爆炸接口超时。一个列表页本来查一张表拆完要 JOIN 五张。优化路径我一般是三条把高频访问的字典字段冗余回主表给 JOIN 字段建好索引这条最容易被忽略很多人拆完表不加索引性能直接崩把展示层做成宽表视图让读路径和写路径分离。坑四视图兼容老代码结果视图里写了业务逻辑。拆表之后为了不动老代码建了个视图把表 JOIN 回原样。这个思路本身没问题但后来有人在视图里加了 WHERE 过滤和计算字段随着视图越改越复杂查询计划彻底失控最后视图比原表还慢三倍。视图只做字段映射别塞逻辑。坑五过度拆分导致事务变长。一个订单写入要落四张表事务里四个 INSERT 串行高并发下锁等待明显。解决办法不是不拆而是把能异步的拆出去——比如客户档案的更新走异步补写核心的订单和明细保持在一个事务内。事务边界要跟业务一致性边界对齐跟表的数量无关。6.2 范式判定速查表范式核心要求一句判断典型违规特征UNF无要求格子里能再分、字段名带序号逗号分隔列表、电话1/电话21NF每列原子、无重复组、有主键一行能否被主键唯一确定多值字段、复合字段、无主键2NF非主属性完全依赖于候选键有没有字段只依赖复合键的一半复合主键表里混入主表属性3NF非主属性不传递依赖于候选键A→B→CC 该不该跟着 B 走主表里塞字典表的描述字段BCNF每个决定因素都是超键所有 FD 的左部能否唯一确定整行多候选键且主属性互相依赖6.3 最后分享一点实操心得做规范化改造最难的从来不是理论是搞清楚业务语义。同样一个状态字段放在订单头是对的放在订单明细就可能是错的同样一个收货地址放在订单上是历史快照必须保留放在客户档案就是可变的当前值。判断这些靠的不是背范式定义是去问“这条数据什么时候会变、变了之后谁应该跟着变、变了之后历史记录要不要跟着变”。我现在的习惯是拿到一张新表先不急着画 ER 图而是把每个字段的变更频率和责任方列出来。变更频率高、责任方明确的字段就该独立成表变更频率低、纯展示用的字段该冗余就冗余。这个土办法比对着范式定义逐条抠要快得多也更贴近真实系统的运行方式。范式是给你一把尺子量完了怎么裁还得看这块布料要做什么衣服。如果后面继续往下走BCNF 里多候选键的坑、4NF 的多值依赖、还有数仓里星型模型的反规范化都是很值得展开的话题尤其是当你发现生产环境里“完全规范化”和“完全跑得动”经常打架的时候那些取舍经验才是真正值钱的部分。