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

存储多值属性

---

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

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

引起的问题：

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

合理使用反模式：

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

反模式：创建多个列

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

引起的问题：

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

合理使用反模式：

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

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

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

树形结构的存储与查询

---

反模式：总是依赖父节点

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

引起的问题：

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

合理使用反模式：

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

解决方案：路径枚举

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

优点

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

缺点

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

解决方案：嵌套集

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

优点

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

缺点

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

解决方案：闭包表

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

优点：

• 可以通过 ancestor 和 descendant 快速高效的查询一个节点所有的祖先节点和子节点
• 结合 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 的情况下（如使用了数据库代理，分库分表等）

解决方案：反向引用

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

缺点：

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

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

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

缺点：

• 额外的性能开销

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

反模式：克隆表

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

引起的问题：

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

合理使用反模式：

• 归档数据

解决方案：垂直拆分

根据业务情况将不常用的字段，大字段（如 text，blob）拆分到单独的表中

缺点：

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

解决方案：水平拆分

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

缺点：

• 切分规则相对复杂