《SQL反模式》读后记

这几天读了《SQL Antipatterns Avoiding the Pitfalls of Database Programming》

原著者为Bill Karwin，翻译为谭震林，Push Chen。

读了一段时间之后才弄清楚“反模式”的含义，看出作者对其所持的态度。直观理解，反模式应该是违反数据库设计中的一些规则的模式，然而实际上并非书中所有的设计都是与常规设计所悖的（比如第三章，树的设计，并不违反数据库范式）。这些设计可能有利于数据库的实施，提高查询的性能，但是会造成一定的风险(主要的风险在于不利于数据库功能的扩展)，而作者则主要介绍了这些设计模式的优点以及如何避免其中存在的问题。在系统设计中，如果对数据库的设计进行了一些思考，很容易就能在本书中找到一些共鸣。经验越多，反而能从本书中学到很多（好比我在抽取数据库样本的时候所写的博文，在本书中就有所涉及，读过之后确实收获不少）。书的槽点在于翻译（亦可能原著便是如此）：章节的名字太过抽象了，你能从“不用钥匙的入口”中看到第五章到底是讲什么的么？

下面回归一下书中内容，结合书的纲目(注：纲目数据粘贴自豆瓣)，并做相应笔记。

第1 章引言　　1
第一部分逻辑型数据库设计反模式
第2 章乱穿马路　　10

2.1 目标：存储多值属性　　11
2.2 反模式：格式化的逗号分隔列表　　11

对于存储1-n型的数据(book,keywords)，一般的设计模式是建立两张表。一张表存放1，一张表存放1-n的对应关系。这样的结构符合关系数据库范式，但是经常要做连接查询会降低性能，所以可能把两张表放在一张表在中。

但存在的问题有：分隔符选择，列表长度限制。

这章的要点在于，如果想使得功能比较容易扩展（规范的设计模式），可能会降低性能。
第3 章单纯的树　　18
树也是经常要用到的数据结构，在数据库中我以前常用到的是(childId,childName,parentId)，这是大部分程序员很快会想到的问题的，但是当做实际查询与展示时，必须经过多次表连接操作，降低性能，作者给出了三种方法（反而是觉得这些方法更像是所谓的“反模式”）：路径枚举，嵌套集，闭包表。

路径枚举：(1, 1/, name1), (3, 1/2, name3)，如此形式；方便找到祖先，找到同级所有节点

嵌套集：(node, left_node, right_node, content)，及存储子节点，如此形式；没看出有多少好的地方

闭包表：即将(祖先,后代)关系放在单独的一张表中存储，这样在查询某个节点的后代的时候十分方便不过在插入的时候比较麻烦，当不需要修改已有的树节点结构的时候，这种方式性能是比较高的。

读这一章的主要想法是：我们在执行数据库update操作的时候，一个事务只是更新一张表记录，实际上为了方便以后的查询，在update的时候可以更新多张表记录，而往往忘了这一点。

第4 章需要ID　　 34

与主键有关。我反思了一下自己，确实是已经习惯了每张表有一个自增的id作为主键，但是并不一定必须如此。在关系数据库理论中主键是可以由多个字段共同组成的，而实际中由于这样为数据库造成了麻烦而而我们弃用。（原因也在于，实际中遇到的数据远没有理论中的那般规范）
第5 章不用钥匙的入口　　43

主要指出不用外键（参照完整性所造成的问题）
第6 章实体—属性—值　　50
第7 章多态关联　　64
第6章与第7章讲的同一个问题：如果将元数据视作值对待。
第8 章多列属性　　 75
第9 章元数据分裂　　　82
第8章与第9章讲的同一个问题：如何将值视作元数据对待。

对于8，不是很严格，如果转化后的多个字段的值都变成1与0，就严格了。
第二部分物理数据库设计反模式
第10 章取整错误　　 94
要在数据库中存储小数，有两类选择：float与NUMERIC。

在使用float的时候可能会出问题，因为float根据IEEE 754标准，使用二进制表示；所以对于数据59.95，系统只能使用与59.95相近的浮点型数据59.950000762939来表示，故59.95不一定等于59.95。所以推荐使用NUMERIC来存储，对于同样的精度（比如10位有效是数字），59.95就是59.95。

那么在程序设计语言中，似乎并没有考虑很多这个问题，为什么呢？因为在编程中大多使用的是离散型数值，都是精确的；而即使使用了浮点值，也通常不对他们作等值判断的操作。而在数据库中，比如财务数据库，等值判断操作则是一项常规的操作了。

第11 章每日新花样　　100
关于用户自定义完整性。比如DBMS用户定义了字符串集合{“AA”,”BB”}，那么这种限制如何体现呢？如果这种限制存储在数据库模式中，那么当限制增加到{“AA”,”BB”,”CC”}的时候，则需要停止DBMS，然后修改DB模式，在启动；在设计网页表单的时候，需要从DBMS的系统表中查询用户自定义的数据范围，这样才能构建下拉列表。总之，如果使用关系数据库中的用户自定义完整性，在修改表模式的时候是比较麻烦的，所以通常是在数据库中设计一张定义完整性的表，在构筑网页表单的时候，只需要查询这一张表就可以了；而且这样还支持自定义集合的变更操作。

抽象来看，其实这也是一种将元数据存储在表中的方式；关系数据库理论本身，似乎更适合设计不变的数据库结构；而当数据库结构经常变动时，遵循关系数据库理论反而不是一种很好的方法。
第12 章幽灵文件　　107
关于二进制文件的数据库存取。

如果文件存储在数据库之外，则程序代码必须考虑文件系统；当对数据库中的记录处理的时候，可能也需要处理文件系统中的二进制文件，容易出错；文件不支持delete，不支持事务隔离，不支持回滚操作，不支持数据库备份，不支持SQL的访问权限设置。

存在的好处如下：数据库在没有图片的时候能精益（InAsh注：就是“文件很小”的意思）很多；备份数据库文件更快；图片存在数据库之外可以使用预览功能。

当然也可以将二进制文件单独放在一张表中，当需要的时候，使用表连接操作进行查询。
第13 章乱用索引　　114
这一章补充了我不清楚的知识。索引并不一定能够加速表查询，需要清楚索引的原理才能正确使用。主键不需要手动创建索引；索引只不过是将值按照顺序排列，并支持使用二分法等算法进行查找。
第三部分查询反模式
第14 章对未知的恐惧　　126
关于NULL，将其认为是一种特殊的值就可以了。

NULL+一个值=NULL；NULL进行数值比较操作=NULL；NULL字符串拼接一个值=NULL。

如果想避免NULL引起纠纷，将列设置为Not Null。
第15 章模棱两可的分组　　135
在使用group操作时可能出现的问题。对于表(student, grade)，我使用max操作来寻找最高的分数，但仅仅使用group并不能得到与最高分相对应的学生，我还必须使用嵌套查询才能获得。
第16 章随机选择　　144
这个作为博文【数据库记录中随机抽取样本】的补充。

1.使用rand()然后排序，效率会比较低，适合从数据库，随机选择多个记录作为数据样本；

2.根据记录总数，使用概率确定需要定位的id集合，构造查询语句并执行，适合需要选择的样本记录并不是很高的情况；需要数据库中有伪键或者平滑的字段(1，3，5…)

3.获取所有的键值，随机选择一个。与方法2相似，适合没有伪键的情况；

4.使用偏移量选择随机行。如果样本数量为1，当然合适；如果样本数量>1，那么就不是严格随机的了；并且，这种方法需要用到非标准的LIMIT子句，MySQL，PostgreSQL和SQLite支持这一子句。

5.使用专有解决方法，在MS SQL Server 2005中，增加了一个TABLE-SAMPLE子句：

SELECT * FROM Bugs TABLESAMPLE (1 ROWS)

在Oracle中有一个类似SAMPLE子句，比如返回表中1% 的记录：

SELECT * FROM (SELECT * FROM Bugs SAMPLE(1) ORDER BY dbms_random.value) WHERE ROWNUM=1;

第17 章可怜人的搜索引擎　　150
通常使用模式匹配来搜索文本，但是无法完成比较高级的匹配；解决方案：

1.使用数据库扩展，MySql中可以针对字符串类型的字段定义全文索引，并使用布尔运算、正则等来匹配

其他数据库中也有类似技术，不提；

2.使用第三方搜索引擎：Sphinx Search, Apache Lucene.
第18 章意大利面条式查询　　162
略。
第19 章隐式的列　　 170
对于表的查询，SQL不支持“除了我不想要的字段，其他都要”的语法。
第四部分应用程序开发反模式
第20 章明文密码　　　178
略。
第21 章 SQL 注入　　188
略。
第22 章伪键洁癖　　202
这个比较有趣。除了使用int来作为伪键之外，还可以使用GUID。有些数据库提供全局唯一标识符（GUID）来随机生成伪键值。GUID是一个128位的伪随机数，GUID相比于传统的伪键生成方法，至少有两个优势之处：

1.可以在数据库中并发的生成伪键，而不用担心生成同样的值

2.没有人会再抱怨有断档（破除强迫之妙计啊）。

第23 章非礼勿视　　209
主要是说在用程序连接数据库的时候，需要注意执行数据库查询之后的返回值。
第24 章外交豁免权　　215

与团队合作有关。软件开发中的代码是有版本控制的，而在数据库的不断进化中却没有相应的版本控制方案，这为版本回溯造成了麻烦，除了数据库模式没有版本控制，开发文档也没有比较好的版本控制。也可能我并没有深入到一项大型的项目开发中所以对此方面了解不多，应该有些专门的书籍讨论这些问题了，不提。
第25 章魔豆　　225
MVC。矛盾平衡问题。
第五部分附录
附录A 规范化规则　　240
附录B 参考书目　　252

转载请注明出处：https://blog.exbye.com/2013/12/sql_antipatterns_reading_note/

《SQL反模式》读后记

发表评论 取消回复

发表评论取消回复