SQL反模式
本文为《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
)拆分到单独的表中
缺点:
- 不是所有业务表都能拆分
- 不能完全解决数据量不断增长的问题
解决方案:水平拆分
垂直拆分无法满足性能需求时,考虑进行水平拆分
缺点:
- 切分规则相对复杂