06-数据库设计（一致性保障）

1. 数据库范式

1. 1NF：属性的原子性
2. 2NF：属性的主键完全依赖
3. 3NF：不存在传递函数依赖
   • 冗余最小化
4. BCNF、4NF、5NF（完美范式）

2. 数据库反范式

规范化的结果是一个结构上一致，且拥有最小冗余的结构，但未必是性能最优的设计。

• 反范式本质是——考虑引入可控制的冗余
  1. 实现更复杂，需要手动控制冗余
  2. 减低灵活性
  3. 加快读取，降低更新
• 主要动作：复制
• 核心目标：减低连接次数，提高查询效率

3. 数据库反范式模式

1. 合并1:1关系
2. 复制1:*关系的非Key、FK及值
3. 复制*:*关系的属性
4. 引入重复组
5. 创建提取临时表

3.1. 合并1:1关系

部分参与会大量引入空值，处理复杂

3.2. 复制1:*关系的非Key、FK及值

多代码表的连接基本上都是通过复制代码值，而不是外键id，来减少表连接

适用于，代码表的值比较固定，值的数量不多

3.3. 复制*:*关系的属性

3.4. 引入重复组

4NF要求

• 地址，电话
• 静态，数量小

e.g.：淘宝，选择收获地址，在customer主表里加一列常用地址。当用户点击更多地址时，才查询用户的全部地址。

3.5. 创建提取临时表

静态，时间切片，不是实时数据
实时计算，物化视图，代价极大
不推荐

4. 数据库设计

基本满足3NF，可能产生多种设计结果

4.1. 树状结构

对象类型相同，而对象的层次可变，其关系就应该被建模为树结构

树状结构复杂度在于：树的访问

• 实际实现：
  1. 邻接模型
  2. 物化路径模型
  3. 嵌套集合模型

4.1.1. 临接模型

满足单父节点

• T(id,pid,attr…)
  • id标记当前节点，pid标记父节点
• 问题：
  1. 会丢失子节点的顺序
  2. 不符合归一化原则（一事，一地，一次），无法通过数据库保证一致性
     • pid存在多处修改也需要多次
     • insert时数据库无法检查是否是有圈，无法检查是树而不是图，需要再应用系统检查
     • delete复杂
  3. 要找所有子节点很复杂

4.1.2. 物化路径模型

• T(m_path,attr…)把路径存成一个字段

1. 解决了子节点顺序问题
2. insert时更容易控制
3. 找所有子节点简单，只需要字符串匹配
4. 无法满足归一化，父节点路径存在多个子节点里，发生修改还是要多次修改
5. 不容易变成图
6. delete数据还是要手动处理

路径id是字符串，处理性能差
读写性能最平衡

4.1.3. 嵌套集合模型

T(left_num,right_num,attr…)，基于集合论，在[left,num]内的都是该节点的子节点

1. 可以保存子节点顺序
2. delete节点，可以自动缺省处理
3. insert要修改很多，所有右边的节点都要改
4. 是归一化模型

现实里使用往往更新比较少，而且保留range

4.1.4. 闭包表模型

• 整棵树在两张表里记录，节点表和节点关系表。
• NodeRelation(id, ancester, descendant, distance)，定义了所有节点之间的关系

闭包模型不仅可以处理树也可以处理图，可读性更差，通用性和扩展性强

场景：论坛回帖，典型的树状结构

4.2. 查询

4.2.1. 自顶向下

查询所有子节点，缩进排序

1. 邻接模型
   1. oracle的connect by,mysql递归查询
   2. 手动union，在一个查询中多次连接，前提是知道深度
   3. 单独设计一张关系表，T(id,pid,distance)，保存节点到所有父节点的距离
2. 物化路径模型
   1. 字符串前缀比较，深度就是.的个数
3. 嵌套集合模型
   1. 遍历整张表，只要在[left,right]内，一定是子节点。但是缩排很复杂。

效率指标： t/s，每秒做了几个事务，兼顾容量和时间

资源消耗：from表连接>字符串处理>数值比较

4.2.2. 自底向上查询

1. 邻接模型
   • connect by, 递归
2. 物化路径
3. 嵌套集合
   1. 动态计算深度仍然是问题，计算量大
   2. 可以按照left_num排序

4.2.3. 问题

select 投影，只做一次
where 选择，对查询时遍历的每一条记录都做一次，如果在where里写复杂函数代价很大

1. 物化路径不该是KEY，既是有唯一性
2. 物化路径和邻接模型等价使用时，不该按时任何兄弟父子节点的排序
3. 所选择的编码方式不需要完全中立

4.3. 效率比较

1. 邻接模型
   • 简单，成熟，深度是最大障碍
2. 物化路径
   • 读写平很，稳定的输出
3. 嵌套集合
   • 读取频率远高于修改频率，只在乎上下关系，不在乎层级
4. 闭包表模型
   • 额外表存储，维护细节和成本高，查询效率优秀

4.4. 其他查询要求

4.4.1. 对保存叶节点的值做聚合

邻接模型的connect by无法使用，只能递归
物化路径比较简单

4.4.2. 物料单问题

任何一个节点都可能包含多个子节点，也可能有多个父节点
递归难以避免

• 多叉树
  1. 复杂的层次模型
  2. 每个节点都有多父节点，多子节点
  3. 边列表结构库设计（Edge List），也是图结构的基础数据库设计
  4. 也是闭包模型的扩展，具体应用中需要增加一些实体

原子化实体和组合化实体要分开

• 例：计算分散在各层的百分比
  • A(aa 20%,bb,cc,B 30%)
  • B(aa 50%,bb,cc)
  • 计算A中aa含量：20% + 30% * 50%

树状结构的问题

要有条件地控制深度，否则都会很慢

5. 递归SQL语法

WITH RECURSIVE cte_name (column_list) AS (
//初始查询

//递归查询
)
//主查询
SELECT ...
FROM cte_name
WHERE ...

引入层次