本文为《SQL反模式》 —— 逻辑型数据库设计反模式章节的归纳整理

存储多值属性


反模式:格式化的逗号分隔列表

单个字段内用逗号分隔多个属性

引起的问题:

  • 单个字段结构有长度限制
  • 无法正常使用关联查询
  • 无法使用索引
  • 无法使用聚合函数
  • 无法保证引用完整性

合理使用反模式:

  • 出于性能优化考虑
  • 只作为值使用,不需要对其进行修改,和建立连接关系

反模式:创建多个列

创建多个列来存储多值属性

引起的问题:

  • 查询时需要搜索所有相关的列
  • 更新和删除一个值前需要先查询出所有列进行分析
  • 需要处理不断增长的值集

合理使用反模式:

  • 一个属性只有固定数量的值集,且值的实际业务逻辑作用是不同的

解决方案:创建一个交叉表

创建一个交叉表,将多个值记录在多个行里而不是列里

树形结构的存储与查询


反模式:总是依赖父节点

添加 parent_id 字段,即使用邻接表

引起的问题:

  • 查询一个节点的所有后代时,要么贴加相应层级数的关联查询,要么查出所有的行,在应用中进行处理
  • 删除一个节点及其子树时,需要执行多次查询找到节点的后代进行删除
  • 删除一个非叶节点并移动后代时,需要先修改子节点的 parent_id,然后才能进行移动

合理使用反模式:

  • 业务需要快速获取给定节点的新节点,易于插入新节点,并且只有嵌套不深的有限层级(如2~3层)
  • 使用 PostgreSQLOracle 等支持递归查询的数据库

解决方案:路径枚举

使用 path 来代替 parent_id,类似 UNIX 文件系统的路径,在 path 中枚举所有的父节点如 /parent_id_1/parent_id_2/self_id

优点

  • 可以比较方便的使用 like 查出指定节点的所有祖先和子节点
  • 可以很方便的通过路径获取节点所在的层级

缺点

  • 单字段有最大长度限制,无法支持无限扩展
  • 无法保证引用完整性,需要依赖业务逻辑来维护路径格式和引用的正确

解决方案:嵌套集

使用两个数字来编码每个节点,例入 leftright
left 小于该节点所有后代节点的 idright 则大于所有后代节点的 id

优点

  • 可以通过查询哪些节点的 idleftright 之间来获取一个节点的所有子节点
  • 可以通过查询一个节点的 id 在哪些节点 leftright 之间来获取它的祖先节点
  • 删除一个非叶节点时,其子节点会代替删除的节点,成为其直接父节点的直接后代

缺点

  • 需要复杂的查询才能找到一个节点的直接父节点或直接子节点
  • 插入和移动一个节点时,需要相应的检查并更新其祖先和子孙节点 leftright,不适用需要频繁插入和移动节点的情况

解决方案:闭包表

创建一个额外的表来记录树中所有节点的关系,表包含 ancestordescendant 字段来记录祖先和子代关系,同时也可以加入一个 path_length 来表示祖先到子代的距离,如 path_length 等于 0 表示自我引用,等于 1 表示直接子节点,以此类推。

优点:

  • 可以通过 ancestordescendant 快速高效的查询一个节点所有的祖先节点和子节点
  • 结合 path_length 可以快速查出直接父节点和直接子节点

缺点:

  • 空间换时间,闭包表的条目数为实际数据的平方级

选择合适的主键


反模式:使用通用 ID 主键

每个表都有一个通用主键,且具有三个特性:

  • 主键名叫做 id
  • 数据类型是 32 位和 64 位
  • 主键的值是自动生成的以确保唯一性

引起的问题:

  • 需要数据插入完成后才有主键
  • 冗余键值,往往同时存在另一个逻辑上更自然的主键
  • 因为主键业务无关,所以需要额外声明一个 UNIQUE 约束
  • id 是一个意义不明的关键字,无法表达额外信息
  • 无法使用 USING 关键字

合理使用反模式:

  • 业务需求多变,使用业务无关的主键有时可以给自己留一条后路
  • InnoDB 数据是按照主键聚簇的,使用自增主键可以避免磁盘的随机 IO 提高写入性能
  • 二级索引存储了主键,使用联合索引或字符串作为主键会增大二级索引占用的空间,影响查询性能

解决方案:剪裁设计

  • 为主键选择更有意义的名称,如用 user_id 代替 id
  • 使用自然键和组合键作为主键

外键约束


反模式:无视外键约束

为了使数据库设计更加高效灵活,设计表时不添加外键约束

引起的问题:

  • 无法完全保证数据的引用完整性(误操作,代码Bug等)
  • 代码层面需要检查数据引用完整性,使得逻辑更加复杂(如更新,删除操作需要同时操作多表)

合理使用反模式:

  • 应用对性能要求更高,对数据完整性要求较低
  • 框架模型层已经能很好处理数据完整性问题
  • 业务逻辑多变导致表结构经常需要变更

解决方案:声明外键约束

通过使用外键约束来保证数据完整性

实体-属性-值 —— 存储可变属性


反模式:使用 EAV 模型

描述:将属性当做行来存储,使用 EAV 即 Entity-Attribute-Value 结构的表来支持可变属性,表中每条记录都包含实体(指向实体的外键)、属性、值三列

引起的问题:

  • 属性查询操作复杂化
  • 无法声明 NOT NULL
  • 无法使用正确的数据结构,属性值一般只能是一个单纯的字符串

合理使用反模式:

  • 绝大部分情况下不要考虑 EAV, 如果有大量非关系数据管理的需求,最好考虑使用非关系型数据库。

解决方案: 单表继承

当数据的子类型和子类型的非公共属性很少时,可以使用单表存储所有子类型和属性,将所有相关的类型存放到一张表中,为每个类型的每个属性保留一列,使用一个 type 属性来标识类型

缺点:

  • 非公共属性存在很多空项,无法使用 NOT NULL 限制
  • 缺乏元信息来标识一个属性属于哪个子类型

解决方案:实体表继承

当业务中很少需要一次性查询所有子类型时,可以使用将每个子类型分别存储到不同的实体表中,每个表包含该类型的所有属性

缺点:

  • 添加通用属性时,需要对每个子类型表进行操作

类表继承

使用一个基类表存储所有子类型的公共属性(同样需要一个 type 属性来区分子类型),对于每个子类型创建一个独立的表存储其特有属性,使用外键和基类表进行关联

缺点:

  • 需要额外的 join 来获取相应的子类型属性

半结构化数据模型

当子类型数量很多或者需要经常变动属性时,可以使用一列来存储 XML 或者 JSON 格式

缺点:

  • 当你使用的数据库不支持类似 JSON 这样的特殊格式存储时,你无法对其中的一个属性进行限制,聚合运算,排序,添加索引等操作,并且你需要获取整个结构并在代码中去解码相应的属性

多态关联 —— 单列属性引用多表

反模式:使用额外的列来标识所引用的表

添加一个额外的列来标识当前行所引用的表

引起的问题:

  • 无法声明外键约束
  • 无法进行关联查询,需要在代码中进行判断后再增加一次查询

合理使用反模式:

  • 不声明外键约束的情况下
  • 无法使用 join 的情况下(如使用了数据库代理,分库分表等)

解决方案:反向引用

为每个被引用表创建一个交叉表

缺点:

  • 无法保证一个引用在多张交叉表中出现
  • 额外的性能开销

解决方案:创建共用的超级表

使被引用的表继承一个共同的基类表,用基类表代替子类表建立关联关系

缺点:

  • 额外的性能开销

元数据分裂 —— 数据表的扩展

反模式:克隆表

将一个表拆分成多个结构相同的小表,使用表中一个特定数据字段来给拆分出来的表进行命名

引起的问题:

  • 不断产生新表
  • 更新数据时,可能需要在不同克隆表中同步数据
  • 跨表查询
  • 变更表结构

合理使用反模式:

  • 归档数据

解决方案:垂直拆分

根据业务情况将不常用的字段,大字段(如 textblob)拆分到单独的表中

缺点:

  • 不是所有业务表都能拆分
  • 不能完全解决数据量不断增长的问题

解决方案:水平拆分

垂直拆分无法满足性能需求时,考虑进行水平拆分

缺点:

  • 切分规则相对复杂