本章核心数据库表不是随便建的需要通过规范化理论消除冗余和异常让数据结构既正确又高效。一、函数依赖定义1.1 是什么函数依赖Functional DependencyFD 描述属性之间决定关系的约束是数据库规范化理论的基石。形式化定义设 R(U) 是一个关系模式U 是 R 的属性集合X 和 Y 是 U 的子集。如果对于 R 的任意一个关系 rr 中不可能存在两个元组在 X 上的属性值相等而在 Y 上的属性值不等则称 X 函数确定 Y或 Y 函数依赖于 X记作 X → Y。关键术语术语是什么例子假设学号→姓名决定因素Determinant箭头左边的属性集 X学号被决定因素Dependent箭头右边的属性集 Y姓名平凡函数依赖Y ⊆ X即 X→Y 但 Y 是 X 的一部分{学号, 姓名} → 姓名非平凡函数依赖Y ⊄ X即 Y 不是 X 的子集学号 → 姓名完全函数依赖Y 依赖于 X 整体不依赖于 X 的任何真子集(学号, 课程号) → 成绩成绩不单独依赖学号或课程号部分函数依赖Y 依赖于 X 的某个真子集(学号, 课程号) → 姓名姓名只依赖学号不依赖课程号传递函数依赖X → YY → Z且 Y ↛ X则 X → Z 是传递依赖学号 → 系名系名 → 系主任则学号 → 系主任是传递依赖函数依赖的现实意义学号 → 姓名 一个学号对应唯一姓名 学号 → 系名 一个学生只属于一个系 (学号, 课程号) → 成绩 一个学生一门课只有一个成绩 系名 → 系主任 一个系只有一个系主任1.2 为什么要有函数依赖函数依赖是发现数据冗余和分析数据结构的数学工具。没有函数依赖的困境函数依赖解决后凭感觉建表不知道哪里冗余用 FD 精确计算属性间的决定关系数据冗余凭经验找FD 推导能系统性地暴露所有冗余不知道一张表拆成几张合适FD 指导分解到哪种范式最合适改一处数据要改多处消除不良 FD让每张表只表达一个独立主题核心价值数学化描述数据结构—— 不再凭感觉而是用符号精确表达属性关系。冗余的探测器—— 不良的函数依赖部分依赖、传递依赖就是冗余的根源。规范化的起点—— 没有 FD就没有范式和分解。1.3 怎么用如何分析函数依赖给一张表找出其中所有的 FD学生成绩表(学号, 姓名, 系名, 系主任, 课程号, 课程名, 成绩)分析步骤单属性决定什么学号 → 姓名, 系名, 系主任一个学生只有一个姓名、一个系课程号 → 课程名一门课只有一个名字系名 → 系主任一个系只有一个系主任联合属性决定什么(学号, 课程号) → 成绩一个学生一门课只有一个成绩判断完全/部分依赖(学号, 课程号) → 姓名部分依赖姓名只依赖学号(学号, 课程号) → 成绩完全依赖必须两个一起才能决定成绩判断传递依赖学号 → 系名 → 系主任所以学号 → 系主任是传递依赖函数依赖集 F 的表示F { 学号 → 姓名, 学号 → 系名, 学号 → 系主任, 课程号 → 课程名, 系名 → 系主任, (学号, 课程号) → 成绩, (学号, 课程号) → 姓名, (学号, 课程号) → 系名 }二、范式Normal Form2.1 是什么范式 关系模式满足某种规范化程度的级别级别越高冗余和异常越少。范式的层级关系1NF ⊃ 2NF ⊃ 3NF ⊃ BCNF ⊃ 4NF ⊃ 5NF 集合越来越小要求越来越严格范式全称核心要求解决什么问题1NF第一范式所有属性值不可分原子性消除表中的表嵌套关系、重复组2NF第二范式满足 1NF且不存在非主属性对主码的部分依赖消除部分依赖导致的冗余3NF第三范式满足 2NF且不存在非主属性对主码的传递依赖消除传递依赖导致的冗余BCNF巴斯-科德范式满足 3NF且每一个决定因素都是候选码消除主属性对候选码的部分/传递依赖4NF第四范式满足 BCNF且不存在非平凡多值依赖消除多值依赖导致的冗余5NF第五范式满足 4NF且不存在连接依赖引起的冗余消除连接依赖导致的冗余各级范式的本质对比学生成绩表(学号, 姓名, 系名, 系主任, 课程号, 课程名, 成绩) ​ 1NF✓ 每个单元格都是原子值 ✗ 但有大量冗余同一学生的姓名、系名、系主任重复存多次 ​ 2NF消除部分依赖 ✗ (学号, 课程号) 是主码但姓名只依赖学号 → 拆成学生(学号, 姓名, 系名, 系主任) 选课(学号, 课程号, 课程名, 成绩) ​ 3NF消除传递依赖 ✗ 学号 → 系名 → 系主任系主任传递依赖于学号 → 再拆成学生(学号, 姓名, 系名) 系(系名, 系主任) 选课(...) ​ BCNF消除所有不良依赖 检查每个 FD 的决定因素是否都是候选码2.2 为什么要有范式没有规范化的表就是垃圾堆问题表现根源数据冗余同一信息存多次部分依赖、传递依赖更新异常改一处漏改别处数据不一致冗余导致多处存储插入异常想插入数据但缺少主码值插不进去一张表承载多个主题删除异常删了一条记录连带丢失其他信息一张表承载多个主题范式的价值分级治理—— 不需要一步到最完美按需求逐步提升到合适级别。权衡的艺术—— 不是越高级越好BCNF 以上可能带来过多的表连接开销。工业标准—— 大多数业务系统做到 3NF 或 BCNF 就足够了。2.3 怎么用判断一张表属于第几范式步骤 1找出所有候选码表(学号, 姓名, 系名, 系主任, 课程号, 课程名, 成绩) 候选码(学号, 课程号)步骤 2检查 1NF所有属性值是否原子→ 是 →满足 1NF步骤 3检查 2NF非主属性姓名、系名、系主任、课程名、成绩是否都完全依赖于候选码姓名、系名、系主任只依赖学号 →部分依赖→不满足 2NF步骤 4检查 3NF假设已满足 2NF非主属性之间有没有传递依赖学号 → 系名 → 系主任 →传递依赖→ 如果不拆不满足 3NF步骤 5检查 BCNF每个 FD 的决定因素是否都是候选码系名 → 系主任决定因素是系名但系名不是候选码 →不满足 BCNF从低范式向高范式提升分解1NF → 2NF消除部分依赖原表(学号, 课程号, 姓名, 系名, 系主任, 课程名, 成绩) ​ 拆分为 R1(学号, 姓名, 系名, 系主任) -- 学号为主码描述学生信息 R2(课程号, 课程名) -- 课程号为主码描述课程信息 R3(学号, 课程号, 成绩) -- (学号, 课程号)为主码描述选课关系2NF → 3NF消除传递依赖R1(学号, 姓名, 系名, 系主任) 中学号 → 系名 → 系主任传递依赖 ​ 拆分为 R1a(学号, 姓名, 系名) -- 学号为主码 R4(系名, 系主任) -- 系名为主码最终达到 3NF 的结构学生(学号, 姓名, 系名) -- 2NF3NF 系(系名, 系主任) -- 3NF 课程(课程号, 课程名) -- 3NF 选课(学号, 课程号, 成绩) -- 3NF范式的工业权衡场景推荐范式理由一般 OLTP 业务系统3NF ~ BCNF冗余少、更新简单读多写少的报表/分析系统2NF 甚至反规范化减少连接提升查询速度数据仓库星型/雪花型特殊设计为分析优化有意保留部分冗余三、函数依赖理论3.1 是什么函数依赖理论 一套关于函数依赖的推理规则和公理系统用于从已知的 FD 推导出所有隐含的 FD。下辖知识点知识点是什么Armstrong 公理系统一套完备且有效的公理用于推导 FD自反律Reflexivity若 Y ⊆ X则 X → Y增广律Augmentation若 X → Y则 XZ → YZ传递律Transitivity若 X → Y 且 Y → Z则 X → Z合并规则若 X → Y 且 X → Z则 X → YZ伪传递规则若 X → Y 且 WY → Z则 WX → Z分解规则若 X → YZ则 X → Y 且 X → Z属性集闭包 X⁺由 X 出发能推导出的所有属性的集合最小覆盖最小依赖集与原始 FD 集等价但无冗余、无多余属性候选码的求解利用闭包判断某属性集是否为候选码Armstrong 公理详解公理含义直观理解自反律自己决定自己的一部分知道身份证号当然能推出身份证号姓名里的身份证号增广律左边加属性结论也加学号能决定姓名那(学号, 课程号)能决定(姓名, 课程号)传递律像数学中的传递性学号→系名系名→系主任则学号→系主任属性集闭包 X⁺定义由属性集 X 出发利用 F 中的函数依赖能够推导出的所有属性的集合。算法闭包计算初始化X⁺ X 循环 对 F 中每个 FD (A → B) 如果 A ⊆ X⁺ 且 B ⊄ X⁺ X⁺ X⁺ ∪ B 直到 X⁺ 不再变化例子F { A → B, B → C, C → D } 求 A⁺ A⁺ {A} A → B 触发A⁺ {A, B} B → C 触发A⁺ {A, B, C} C → D 触发A⁺ {A, B, C, D} 无更多触发 → A⁺ {A, B, C, D}3.2 为什么要有函数依赖理论手动列举所有 FD 不现实问题函数依赖理论解决F 中只写了显式 FD隐含的 FD 成百上千Armstrong 公理系统能完备地推导出所有隐含 FD不知道某属性集是不是候选码计算闭包如果 X⁺ 全部属性则 X 是超码再去掉冗余属性得候选码F 中有冗余规则影响效率求最小覆盖去掉冗余 FD 和 FD 左边多余的属性判断分解是否保持依赖检查分解后的 FD 集是否等价于原 FD 集核心价值完备性—— Armstrong 公理是完备的所有逻辑蕴含的 FD 都能被推出来。有效性—— 推导出的 FD 一定是正确的不会推错。算法基础—— 候选码求解、最小覆盖、模式分解算法都建立在闭包计算之上。3.3 怎么用用闭包求候选码R(A, B, C, D, E) F { AB → C, C → D, D → E } 分析 - A、B 只在左边出现从未在右边出现→ 它们必须出现在候选码中 - E 只在右边出现 → 它不可能在候选码中 - C、D 两边都出现 → 待定 尝试 (AB)⁺ AB → C得 {A, B, C} C → D得 {A, B, C, D} D → E得 {A, B, C, D, E} 全部属性 所以 AB 是超码。检查是否有真子集也是超码 A⁺ {A}B⁺ {B} → 都不是超码 所以 AB 是候选码求最小函数依赖集最小覆盖三个步骤步骤 1右边单一化X → YZ 拆成 X → Y, X → Z步骤 2去掉左边多余属性对 X → Y检查 X 的每个属性 A 若 (X - {A})⁺ 包含 Y则 A 是多余的去掉步骤 3去掉冗余的 FD对每条 FD X → Y 暂时从 F 中去掉它得到 F 若 X⁺(基于 F) 仍包含 Y则该 FD 冗余 permanently 去掉例子F { A → B, B → C, A → C, AB → C } 步骤 1右边已单一化 步骤 2AB → C检查 A 是否多余 A⁺ {A, B, C}基于 F包含 C → A 多余 所以 AB → C 简化为 B → C但 B → C 已存在这条冗余 步骤 3去掉 A → C F { A → B, B → C, AB → C } A⁺ {A, B, C} 包含 C → A → C 冗余去掉 去掉 AB → C因为等价于 B → C 已存在 最小覆盖{ A → B, B → C }四、模式分解算法4.1 是什么模式分解 将一个关系模式拆分成多个子关系模式目的是消除不良依赖提升范式级别。下辖知识点知识点是什么无损连接分解Lossless Join分解后可以通过自然连接完全恢复原始关系不丢失信息保持依赖分解Dependency Preservation分解后的子模式上的 FD 并集逻辑蕴含原模式上的所有 FD3NF 分解算法保证分解既是无损连接又保持依赖BCNF 分解算法保证无损连接但不一定保持依赖判定无损连接的表格法用 chase 过程判断一个分解是否无损判定保持依赖的方法检查每个原 FD 是否都能被分解后的 FD 集逻辑蕴含无损连接 vs 保持依赖性质含义重要性无损连接分解后能拼回原来的样子数据没丢必须满足否则分解就是破坏性的保持依赖分解后还能检查原来的所有约束尽量满足否则约束检查需要跨表连接效率低BCNF 分解算法核心思想找到违反 BCNF 的 FD决定因素不是候选码用这个 FD 进行分解。算法步骤输入关系模式 R函数依赖集 F 1. 计算 F 在 R 上的覆盖 2. 若 R 已满足 BCNF所有 FD 的决定因素都是候选码返回 {R} 3. 找到违反 BCNF 的 FDX → A其中 X 不是 R 的候选码 4. 将 R 分解为 R1 X ∪ A 包含决定因素和被决定的属性 R2 R - A 去掉被决定属性 5. 对 R1 和 R2 递归应用本算法 6. 返回所有分解结果例子R(学号, 系名, 系主任) F { 学号 → 系名, 系名 → 系主任 } 候选码学号 检查 FD 学号 → 系名决定因素学号是候选码 → OK 系名 → 系主任决定因素系名不是候选码 → 违反 BCNF 分解用 系名 → 系主任 R1 {系名, 系主任} -- 主码系名 R2 {学号, 系名} -- 主码学号 检查 R1系名 → 系主任系名是候选码 → 满足 BCNF ✓ 检查 R2学号 → 系名学号是候选码 → 满足 BCNF ✓ 分解完成且是无损连接。 但保持依赖吗检查原 FD 学号 → 系名在 R2 中 ✓ 系名 → 系主任在 R1 中 ✓ → 本例中恰好也保持了依赖。3NF 分解算法核心思想先求最小覆盖然后为每个 FD 建一张表最后检查是否有候选码被遗漏。算法步骤输入关系模式 R函数依赖集 F 1. 求 F 的最小覆盖 G 2. 对 G 中每个 FD X → A 创建一个子模式 Ri X ∪ A 3. 如果没有任何一个 Ri 包含 R 的候选码 增加一个子模式 R 的某个候选码 4. 如果有子模式被另一个包含去掉被包含的 5. 返回分解结果特点一定保持依赖每个原 FD 都落在某张子表里一定无损连接因为有候选码作为桥梁表4.2 为什么要有模式分解算法分解不是随意拆而是有约束条件的随意分解的问题模式分解算法解决拆完发现有些数据拼不回去了保证无损连接拆完发现原来的约束没法检查了保证保持依赖不知道拆到哪一级该停给出明确的终止条件满足目标范式分解方案不唯一不知道哪个好给出确定性的算法步骤核心原则无损连接是底线—— 宁可不拆也不能拆了拼不回来。保持依赖是追求—— 约束最好在单表内就能检查不要每次都要跨表连接。4.3 怎么用实际工作中做分解的步骤1. 收集函数依赖 F ↓ 2. 分析候选码 ↓ 3. 判断当前属于第几范式 ↓ 4. 确定目标范式通常 3NF 或 BCNF ↓ 5. 应用分解算法 ↓ 6. 验证无损连接和保持依赖 ↓ 7. 评估查询性能连接开销 ↓ 8. 必要时适度反规范化BCNF vs 3NF 的选择条件选择追求无冗余允许少量跨表约束检查BCNF必须保持所有依赖约束检查要高效3NF原模式有多个候选码互相重叠通常只能到3NFBCNF 可能无法保持依赖经典例子无法做到 BCNF 且保持依赖的情况R(城市, 街道, 邮编) F { (城市, 街道) → 邮编, 邮编 → 城市 } 候选码(城市, 街道) 和 (街道, 邮编) 检查 BCNF 邮编 → 城市决定因素邮编不是候选码 → 违反 BCNF 如果用 BCNF 算法分解用 邮编 → 城市 R1(邮编, 城市) -- 满足 BCNF R2(街道, 邮编) -- 满足 BCNF 保持依赖吗 (城市, 街道) → 邮编需要城市街道才能推出邮编 但城市在 R1街道在 R2跨表了 → 分解后无法直接检查 (城市, 街道) → 邮编 → **没有保持依赖** 结论此例中只能接受 3NF它有多个重叠候选码不能强求 BCNF。五、数据库模式求精5.1 是什么数据库模式求精 在规范化基础上结合实际应用需求对表结构进行微调不是教条地追求最高范式。下辖知识点知识点是什么规范化 vs 反规范化规范化是消除冗余反规范化是有意引入冗余以提升性能水平拆分按行拆分如按时间分区、按地区分表垂直拆分按列拆分如常用字段放一张表大文本放另一张表冗余字段设计在查询频繁的表中冗余存储关联表的字段派生列/计算列存储可以由其他列计算得出的值分区表将大表按规则分成多个物理存储单元物化视图预先计算并存储查询结果相当于有冗余的缓存表常见的反规范化手段手段是什么场景冗余字段在订单表中冗余存客户姓名客户表中也有查询订单时几乎都要显示客户名减少连接派生列在订单表中存订单总金额可由明细行计算报表查询频繁避免实时计算合并表把 1:1 的两张表合并成一张总是一起查询没必要分开拆分表把一张大表拆成多张冷热数据分离热数据放 SSD5.2 为什么要有模式求精规范化不是银弹过度规范化的代价模式求精解决表拆得太碎查询要连接七八张表适度合并减少连接报表查询要实时计算聚合慢得离谱加派生列或物化视图高频查询的字段分布在不同表冗余存储到查询入口表单表数据量太大查询慢水平/垂直拆分核心思想规范化保证数据正确—— 写入时不出错。反规范化优化查询性能—— 读取时更快。在正确性和性能之间找平衡—— 这就是求精的含义。5.3 怎么用反规范化的决策流程1. 先按 3NF/BCNF 设计规范化的模式 ↓ 2. 识别性能瓶颈慢查询、高并发读 ↓ 3. 分析瓶颈原因连接太多计算太多单表太大 ↓ 4. 选择反规范化手段 ↓ 5. 建立数据同步机制冗余字段如何保持同步 ↓ 6. 监控数据一致性风险冗余字段的同步策略-- 方案1触发器自动同步 CREATE TRIGGER sync_customer_name AFTER UPDATE ON 客户 FOR EACH ROW BEGIN UPDATE 订单 SET 客户姓名 NEW.姓名 WHERE 客户ID NEW.客户ID; END; -- 方案2应用层双写写客户时同时更新订单 -- 方案3定时同步/批处理T1 更新适合报表场景垂直拆分例子原表文章(文章ID, 标题, 作者, 摘要, 正文, 发布时间, 阅读数) 问题正文很大几MB但列表查询只需要标题、作者、摘要 垂直拆分 文章概要(文章ID, 标题, 作者, 摘要, 发布时间, 阅读数) -- 常查 文章内容(文章ID, 正文) -- 只在详情页查水平拆分例子原表订单(订单ID, 用户ID, 金额, 时间, 状态) 问题每年几千万单单表撑不住 水平拆分 订单_2024(订单ID, ...) -- 历史归档 订单_2025(订单ID, ...) -- 当前活跃 订单_2026(订单ID, ...) -- 未来预留物化视图例子-- 原始查询很慢要连接聚合 SELECT 系名, AVG(成绩) AS 平均分 FROM 学生 JOIN 选课 ON ... GROUP BY 系名; -- 建物化视图有冗余但查起来飞快 CREATE MATERIALIZED VIEW 系成绩统计 AS SELECT 系名, AVG(成绩) AS 平均分 FROM 学生 JOIN 选课 ON ... GROUP BY 系名; -- 定期刷新 REFRESH MATERIALIZED VIEW 系成绩统计;六、知识脉络图关系数据理论与模式求精 │ ├── 函数依赖定义 │ ├── 是什么属性之间的决定关系X → Y │ ├── 为什么数学化描述数据结构发现冗余根源 │ └── 怎么用分析表中的FD区分完全/部分/传递依赖 │ ├── 范式 │ ├── 是什么规范化级别1NF/2NF/3NF/BCNF/4NF/5NF │ ├── 为什么消除冗余、避免更新/插入/删除异常 │ └── 怎么用判断当前范式级别逐步分解提升 │ ├── 函数依赖理论 │ ├── 是什么Armstrong公理、闭包、最小覆盖 │ ├── 为什么完备推导所有FD系统化求候选码和最小依赖集 │ └── 怎么用公理推导、闭包算法求候选码、三步法求最小覆盖 │ ├── 模式分解算法 │ ├── 是什么无损连接分解、保持依赖分解 │ ├── 为什么分解不能乱拆必须保证拼得回来、约束能检查 │ └── 怎么用BCNF算法可能不保持依赖/ 3NF算法保持依赖无损 │ └── 数据库模式求精 ├── 是什么规范化基础上的性能优化调整 ├── 为什么过度规范化导致查询慢需要反规范化平衡 └── 怎么用冗余字段、派生列、水平/垂直拆分、物化视图、分区七、一句话记忆概念一句话函数依赖 X→YX 定了Y 就跟着定了完全依赖必须 X 整体才能定 Y少一个都不行部分依赖X 的一部分就能定 Y是冗余的信号传递依赖A→B→CA 间接定 C也是冗余的信号1NF每个格子只放一个值不能是一组2NF消除部分依赖非主属性必须完全依赖主码3NF消除传递依赖非主属性不间接依赖主码BCNF任何决定因素都必须是候选码更严格Armstrong 公理自反、增广、传递三条推所有 FD闭包 X⁺X 能推出的所有属性算它无损连接拆了能拼回来信息没丢保持依赖拆了之后原来的约束还能检查BCNF 算法找个违反 BCNF 的 FD用它劈成两半递归3NF 算法最小覆盖 → 每条 FD 一张表 → 补候选码反规范化明知有冗余但为了查得快故意留着总结基于《数据库系统概论》第4/5章知识体系整理