Marvin's Blog【程式人生】

Ability will never catch up with the demand for it

25 Jun 2020

SQL Antipatterns学习心得

学习Bill Karwin的SQL Antipatterns Strike Back的心得体会。Slides也可以在SlideShare查看。

体会

SQL是用来查询关系数据库(DBMS)的语言。DBMS的存储单元是数据表。每个表可以有多个栏,每个栏代表不同类型的数据。为了增加效率,数据表中的栏最好存储的是共生性的数据,也就是说,这些数据增删改查的特性相近。表中的数据是按行增长的,如果把共生性不强的数据放在一个表的不同栏中,导致有的栏经常增加数据,有的栏经常删除数据,整个表变得有很多空洞的栏,变得不紧实,影响查询效率。如果不把共生性的数据放在相同的表中,还有可能导致同样的数据在不同表的中出现,导致重复。如何最大程度增加共生性,是SQL设计的时候要考虑的一个大问题。

对于一个数据表来说,对表栏的操作属于Metadata操作,对于表行的操作属于Data操作。

SQL Antipatterns中以问题入手,探讨解决方案。针对每个问题,下面以章节的形式展开。

表中数据太大怎么办?

一个倒行逆施的做法是将表中的数据分拆到多个表中(按时间分,或者按用户分,等等),每个表的结构相同,只是名字不同。根据共生性原则,这些数据应该在一个表中。拆分成多个表。这些表之间的主键可能是相同的值,导致麻烦;而且不仅查询起来需要连接多个表,而且如果某个表的结构被修改,会影响数据一致性。

解决办法一:有的数据库比如MySQL提供对表的分区功能,可以把表分成多个区,但是逻辑上还是一体的,维持了共生性。

解决办法二:如果观察到一部分字段经常为空,可以把这些字段单独成表,并不破坏共生性。这些表之间可以采用一一对应关系。

一一对应,指一个表的行最多与第二个表中的一个行有关联,但是这不排除空行的情况。

解决办法三:如果需要一对多,可以引入一个中间表。这样每个表中的数据都有比较强的共生性,避免出现空洞。

CREATE TABLE Bugs (id PRIMARY KEY, ...);
CREATE TABLE Products (id PRIMARY KEY, ...);
CREATE TABLE BugsProducts(
  bug_id REFERENCES Bugs,
  product_id REFERENCES Products,
  PRIMARY KEY (bug_id, product_id)
)

如果存储实体属性值?

如果一个实体有很多属性,比如一个Bug可以有优先级、汇报者、严重度等属性值。如何将这些值很好地在SQL中保存。

一个倒行逆施地做法是采用抽象地属性表:

CREATE TABLE BugAttrs (
  bug_id ..., # Bugs表的bug_id
  attr_name ...,
  attr_value ...
);

这种做法的问题是一个表栏可以对应不同的属性,比如该栏第一行可能对应优先级,第二栏就对应严重度了。该表栏本身的类型变得无所谓了,也很难在该表栏上附加约束。表中同一栏的每一行数据应该具有相同的数据类型,以及具有相同的约束,否则无法保证共生性。

解决办法一:老老实实地把每个属性对应到不同的表栏中,即便这样会出现大量的空洞

解决办法二:把表切分成多个表,每个表中的前几个栏是相同的,增加每个表的共生性。这个做法有点像面向对象中的继承关系,公共的几个栏可以看成是父类中的字段。

解决办法三:把表切分成多个表,但是只保持各个表的主键栏表示的是相同数据。这个做法有点像面对对象中的组合关系。每个分表可以看成是构成对象的一个组件。

多对一的关系中,如果这个一是两个表构成的怎么办?

SQL Antipatterns中举的例子是Comments中有一个issues_id,必须多对一到Bugs或者Features中的issue_id。但是Comments的外键约束只能对应到Bugs或者Features的issue_id。

一个倒行逆施的办法是issue_id之外增加一个issue_type,用这个issue_type来表明其issue_id对应的是Bugs还是Features。这导致issue_type这一栏的数据共生性问题:用一个栏来表示两种数据。

解决办法一:在Comments中把issues_id拆分成bug_id和product_id,用于对应Bugs 表以及Products,即便对于某一行,其中一个id为空,也比用同一栏表示两种数据要好。

解决办法二:增加专门的中间表,用来表示多对一关系。比如增加BugsComments表,用来管理Comments和Bugs的关系;增加ProductsComments表,用来管理Products和Comments的关系。这样一来,其实关系就反转过来了。从多对一变成了一对多。之前多个Comment对应一个Bugs或者Products变成了一个Bugs或者Products对应多个BugsComments或者ProductComments,后者跟Comments一一对应。

解决办法三:提取出一个Issues表,让其与Bugs和Products一一对应。然后每个Issue对应着多个Comments。

如何存储层级数据?

一个倒行逆施的做法是使用行来模拟链表,也就是使用一个表栏来引用到其上一层级的数据所在行的主键值。

解决办法一:在一个表栏中使用字符串数据来模拟层级关系,例如: 1/4/,其中1代表主键为1的数据行,4代表主键为4的数据行。4的层级在1之下。

解决办法二:使用二分树的形式来组织层级关系,新增两个表栏,分别用来表示左极值和右极值。左右极值范围内的数据行在该数据行的层级之下。这个解决办法的问题在于二分树的结构不容易维护。

解决办法三:使用一个额外的闭环表来存储层级数据。通过让这个闭环表中的某一数据行引用到同一个表中的其他数据行,以用来表示层级关系。非常灵活,缺点是数据比较重复。比如层级1下面有3个直接子层级,每个子层级在闭环表中都需要一行来表示,每行的父层级的都指向1。

如何表示enum?

一个不太好的办法是用表栏的值来表示enum,比如设置一个字符串类型的表栏,取值"bugs"或者“features”来表示不同的enum值。这相当于把一栏的值限制在某些范围内。

一个改进的办法是单独用一个表来表示枚举值,这个表可以只有一栏,用来表示枚举类型,然后这一栏的每一行用来表示枚举值。如果想废弃某些值,可以增加一个新的表栏active,把那些废弃的值对应的active设成0。

对NULL的误用

简单一句话,NULL不应该作为值来使用,NULL只适合用来表示空。

Ambiguous Groups

一个栏中的数据的自相关性比较差,以下面的bug_id为例

  • product_name, bug_id
  • Open RoundFile, 1234
  • Open RoundFile, 5678
  • Visual TurboBuilder, 3456
  • Visual TurboBuilder, 4077

上面的例子中,bug_id其实跟product_name强相关,跟自己的关联性没有那么大。

其他

SQLAntipatterns还介绍了其他许多反模式,这里就不列举了。

(本篇完)

comments powered by Disqus