《SQL 反模式》 学习笔记

第一章 引言

GoF 所著的的《设计模式》，在软件领域引入了“设计模式”（design pattern）的概念。

而后，Andrew Koenig 在 1995 年造了 反模式（anti-pattern） （又称反面模式）这个词，灵感来自于 GoF 所著的的《设计模式》。

反模式指的是在实践中经常出现但又低效或是有待优化的设计模式，是用来解决问题的带有共同性的不良方法。它们已经经过研究并分类，以防止日后重蹈覆辙，并能在研发尚未投产的系统时辨认出来。

所以，反模式是特殊的设计模式，而这种设计模式是欠妥的，起到了反效果。

但有的时候，出于权衡考量，也会使用反模式。

例如数据库的结构中使用的反规范化设计。

下面的每一章，都会列举一种特定场景下的反模式，然后再给出避免使用反模式的建议。

有个别章节，我略去了反模式，直接写解决方案了。

第二章 乱穿马路

假设有 Product 和 Account 两个实体。

1、一对一关系

假设：Product 只有一个 Account（即 Account 也只有一个 Product）。

方案：只用一张表，用两个字段（Product + Account）关联即可。

如无必要，就别用多个表，这会增加复杂度（除非考虑未来的拓展性等其他情况）。

2、一对多关系

假设：Product 可以有多个 Account。

方案1：两张表，一个 Product 表，一个 ProductAccount 表，此表存 ProductId + AccountName。

ProductAccount 表称之为从属表。

方案2：只用一张表，即 Product 表，然后此表有个 Account 字段，存以逗号分隔的 AccountName。

此为反模式，不推荐使用。

方案3：还有一种拓展性更好的、也是本人工作中更常用的做法，直接用下面 ”3、多对多关系“ 的方案 。

3、多对多关系

假设：Product 可以有多个 Account，Account 也可以有多个 Product。

方案：用三张表，一个 Product 表，一个 Account 表，一个 ProductAccount 表，此表存 ProductId + AccountId。

ProductAccount 表称之为交叉表。

第三章 单纯的树

1、需求

建立一张表，存放（帖子的）评论（可嵌套回复评论）。

2、方案1：邻接表

添加 parent_id 列，指向同一张表的id。

这样的设计叫做邻接表。这可能是程序员们用来存储分层结构数据中最普通的方案了。

缺点：

  • 查询一个节点的所有后代很复杂
  • 从一棵树中删除一个节点会变得比较复杂。如果需要删除一棵子树，你不得不执行多次查询来找到所有的后代节点（其实这点跟上一个点实质一样），然后逐个从最低级别开始删除这些节点以满足外键完整性。

[拓展]

某些品牌的数据库管理系统提供扩展的 SQL 语句，来支持在邻接表中存储分层数据结构。

  • SQL-99 标准定义了递归查询的表达式规范，使用 WITH 关键字加上公共表表达式。
  • oracle 可以使用层次化查询 connect by 遍历表数据。
  • postgreSQL 数据库中，我们使用 RECURSIVE 参数配合 with 查询来实现遍历。如果安装了 tablefunc 扩展，也可以使用 PG 版本的 connectby 函数。这个没有Oracle那么强大，但是可以满足基本要求。
  • mysql 暂不支持。

这里的递归查询暂时不深究，待写。

3、方案2：路径枚举

建立一个 path 字段，存路径，如1/4/6/7/

缺点：

  • 数据库不能确保路径的格式总是正确或者路径中的节点确实存在。依赖于应用程序的逻辑代码来维护路径的字符串，并且验证字符串的正确性的开销很大。
  • 无论将 VARCHAR 的长度设定为多大，依旧存在长度限制，因而并不能够支持树结构的无限扩展。

    可以用 PG 的 text 类型，最高支持存储 1G 的字符串，应该是够了。

4、方案3：嵌套集

建立 nsleft 和 nsright 字段，存储子孙节点的相关信息，而不是节点的直接祖先.

每个节点通过如下的方式确定 nsleft 和nsright 的值：nsleft 的数值小于该节点所有后代的ID，同时 nsright 的值大于该节点所有后代的ID。这些数字和 comment_id 的值并没有任何关联。

确定这三个值（nsleft，comment_id，nsrigh）的简单方法是对树进行一次深度优先遍历，在逐层深入的过程中依次递增地分配 nsleft 的值，并在返回时依次递增地分配 nsright 的值。

最后结果形如：

2021-04-06-23-58-46

缺点：
如果简单快速地查询是整个程序中最重要的部分，嵌套集是最佳选择——比操作单独的节点要方便快捷很多。然而，嵌套集的插入和移动节点是比较复杂的，因为需要重新分配左右值，如果你的应用程序需要频繁的插入、删除节点，那么嵌套集可能并不适合。

5、方案4：闭包表（推荐）

闭包表是解决分级存储的一个简单而优雅的解决方案，它记录了树中所有节点间的关系，而不仅仅只有那些直接的父子关系。

在设计评论系统时，我们额外创建了一张叫做 TreePaths 的表，它包含两列：ancestor 和 descendant，每一列都是一个指向评论表的id的外键。

TreePaths 表结构如下：

2021-04-06-23-58-54

6、总结

​

设计 表 查询子 查询树 插入 删除 引用完整性
邻接表 1 简单 困难 简单 简单 是
递归查询 1 简单 简单 简单 简单 是
枚举路径 1 简单 简单 简单 简单 否
嵌套集 1 困难 简单 困难 困难 否
闭包表 2 简单 简单 简单 简单 是
​
​
​邻接表是最方便的设计，并且很多软件开发者都了解它。

如果你使用的数据库支持W ITH 或者 CONNECT BY PRIOR 的递归查询，那能使得邻接表的查询更为高效。

闭包表是最通用的设计，并且本章所描述的设计中只有它能允许一个节点属于多棵树。它要求一张额外的表来存储关系，使用空间换时间的方案减少操作过程中由冗余的计算所造成的消耗。

我之前做过的评论功能，需求都会尽量简化，例如弄成扁平化，只能回复评论一次，即不能评论评论的评论。如果下次鄙人真的要实现这个复杂的评论功能了，关于闭包表的具体设计及操作实现，准备回头再看原书。

第四章 需要 ID

1、什么是伪主键

在这样的表中，需要引入一个对于表的域模型无意义的新列来存储一个伪值。这一列被用作这张表的主键，从而通过它来确定表中的一条记录。这种类型的主键列我们通常称其为伪主键或者代理键。

可以把 伪主键 理解成 伪键 或者 主键。

2、伪主键的作用

  • 确保一张表中的数据不会出现重复行；

    按照关系型数据库的定义，表里是不可以出现重复行的，但是实际中确实会出现，怎么办，引入伪键就不会重复了。

  • 在查询中引用单独的一行记录；
  • 支持外键。

3、各家数据库产品中的伪主键

伪主键直到 SQL:2003 才成为一个标准，因而每个数据库都使用自己特有的 SQL 扩展来实现伪主键，甚至不同数据库中对于伪主键都有不同的名称（不同的表述），如下表：

名称 数据库
AUTO_INCREMENT MySQL
GENERATOR Firebird, InterBase
IDENTITY DB2 Derby, Microsoft SQL Server, Sybase
ROWID SQLite
SEQUENCE DB2 Firebird, Informix, Ingres, Oracle, PostgreSQL
SERIAL MySQL, PostgreSQL

虽然各家数据库产品的伪主键叫法不同，但是给伪主键指派的列名，确是出奇的一致，那就是 id。

第五章 不用钥匙的入口

1、反模式 —— 不用外键

有时你被迫使用不支持外键约束的数据库产品（比如 MySQL 的 MyISAM 存储引擎，或者比 SQLite 3.6.19 早的版本）。

如果是这种情况，那你不得不使用别的方法来弥补。

2、推荐：使用外键

外键的好处：

  • 自动维持引用完整性（否则需要自己写监控脚本）
  • 级联更新/删除（否则需要自己写逻辑代码）

总结来看就是：避免编写不必要的代码，节省了大量开发、调试以及维护时间。

软件行业中每千行代码的平均缺陷数约为 15~50 个。在其他条件相同的情况下，越少的代码，意味着越少的缺陷。

外键的缺点：

  • 需要多一点额外的系统开销。

但这是值得的。

第六章 实体-属性-值

1、需求

表支持可变（可拓展）属性（列）。

例如：你有一个 Prodcut 表，记录了两种类型的产品：

  • 产品1：product_type = "电影"，此外还有 product_name、total_duration（总时长） 属性。
  • 产品2：product_type = "图书"，此外还有 product_name、total_page（总页数） 属性。

2、反模式

对于某些程序员来说，当他们需要支持可变属性时，第一反应便是创建另一张表，将属性当成行来存储。

这样的设计称为实体—属性—值，简称EAV。有时也称之为：开放架构、无模式或者名—值对。

例如：

  • Prodcut 表：id
  • ProdcutAttr 表：id、product_id、attr_name、attr_value

    ProdcutAttr 表数据形如：

    • (1，1, "product_type", "电影")
    • (2，1, "product_name", "阿甘正传")
    • (3，1, "total_duration", 120)
    • (4，2, "product_type", "图书")
    • (5，2, "product_name", "简爱")
    • (6，2, "total_page", 300)

3、推荐

（1）单表继承

最简单的设计是将所有相关的类型都存在一张表中，为所有类型的所有属性都保留一列。同时，使用一个属性来定义每一行表示的子类型。在这个例子中，这个属性称作issue_type。

对于所有的子类型来说，既有一些公共属性，但同时又有一些子类型特有属性。这些子类型特有属性列必须支持空值，因为根据子类型的不同，有些属性并不需要填写，从而对于一条记录来说，那些非空的项会变得比较零散。

例如：

  • Prodcut 表：id、product_type、product_name、total_duration、total_page

    Prodcut 表数据形如：

    • (1，"电影", "阿甘正传", 120, NULL)
    • (2，"图书", "简爱", NULL, 300)

缺点：

  • 没有任何的元信息来记录哪个属性属于哪个子类型

适用场景：

  • 当数据的子类型很少，以及子类型特殊属性很少
  • 使用 Active Record 模式来访问单表数据库时
（2）实体表继承

为每个子类型创建一张独立的表。每个表包含那些属于基类的共有属性，同时也包含子类型特殊化的属性。

例如：

  • ProdcutMovie 表：id、product_name、total_duration
  • ProdcutBook 表：id、product_name、total_page

缺点：

  • 很难将通用属性和子类特有的属性区分开来

    可以创建一个视图联合这些表，仅选择公共的列。

  • 如果将一个新的属性增加到通用属性中，必须为每个子类表都加一遍。
（3）类表继承

此种方法模拟了继承，把表当成面向对象里的类。创建一张基类表，包含所有子类型的公共属性。对于每个子类型，创建一个独立的表，通过外键和基类表相连。

这里需要用到数据库产品自带的表继承功能。

例如：

  • Prodcut 表：id、product_name
  • ProdcutMovie 表：id、total_duration
  • ProdcutBook 表：id、total_page
（4）半结构化数据模型

使用一个BLOB 列来存储数据，用 XML 或者 JSON 格式——同时包含了属性的名字和值。Martin Fowler 称这个模式为：序列化大对象块（Serialized BLOB）。

优点：优异的扩展性

缺点：就是在这样的一个结构中，SQL 基本上没有办法获取某个指定的属性。你不能在一行blob 字段中简单地选择一个独立的属性，并对其进行限制、聚合运算、排序等其他操作。你必须获取整个blob 字段结构并通过程序去解码并且解释这些属性。

但现在的数据库，例如 PG，可以直接支持使用 JSON(B) or XML 的数据类型。所以不会存在必须整个获取再解析的麻烦了。

第七章 多态关联

1、需求

怎么声明一个指向多张表的外键？

例如，Comments 表的外键（issue_id）要引用 Bugs 表 or FeatureRequests 表。形如（这种写法是无效的）：

FOREIGN KEY  (issue id)
REFERENCES Bugs (issue_id) OR FeatureRequests (issue_id)

2、反模式

有一个解决方案已经流行到足以正式命名了，那就是：多态关联。有时候也叫做杂乱关联。

例如：
除了 Comments 表 issue_id 这个外键之外，你必须再添加一列：issue_type，这个额外的列记录了当前行所引用的表名，取值范围是 "Bugs" / "FeatureRequests"。

缺点：没有任何保障数据完整性的手段来确保 Comments.issue_id 中的值在其父表中存在。

当你使用一个面向对象的框架（诸如Hibernate）时，多态关联似乎是不可避免的。这种类型的框架通过良好的逻辑封装来减少使用多态关联的风险(即依赖上层程序代码而不是数据库的元数据)。如果你选择了一个成熟、有信誉的框架，那可以相信框架的作者已经完整地实现了相关的逻辑代码，不会造成错误。

3、推荐

（1）交叉表

把 Comments 表向下拆分，分出两个多的交叉表，即 BugsComments 和 FeatureRequestsComments

2021-04-06-23-58-59

（2）共用的超级表

基于 Bugs 表和 FeatureRequests 表，创建共用的超级表：Issues。

2021-04-06-23-59-03

第八章 多列属性

1、反模式 —— 可拓展的列

例如： 有一个 Bug 表，每个 Bug 自身可能会有多个 tag。

CREATE TABLE Bug
bug_id SERIAL PRIMARY KEY
description VARCHAR (1000)
tagl VARCHAR (20)
tag2 VARCHAR (20)
tag3 VARCHAR (20)

每次要修改 Bug 自身的最大 tag 数，会动表结构，可拓展性很差。

2、推荐

在原有 Bug 表的基础上，再创建一个 BugTag 表。包含下面几列：

  • bug_id
  • tag

第九章 元数据分裂

1、反模式

用形如 Crevenue2002、Crevenue2003、Crevenue2004 的多列，来记录销售额。

这里的问题在于部分数据存在于列名中，即混淆了元数据和数据。

还有一种常见的反模式是，将数据（年份）追加在基本表名之后。

2、推荐

如果是因为同一张表数据量太多导致这种反模式，建议：

（1）水平分区（or 分片）

你仅需要定义一些规则来拆分一张逻辑表，数据库会为你管理余下的所有事情。物理上来说，表的确是被拆分了，但你依旧可以像查询单一表那样执行SQL 查询语句。

分区在 SQL 标准中并没有定义，因此每个不同的数据库实现这一功能的方式都是非标准的。

（2）垂直分区（or 分片）

鉴于水平分区是根据行来对表进行拆分的，垂直分区就是根据列来对表进行拆分

比如说，会在Products 表中为每个单独的产品存储一份安装文件。这种文件通常都很大，但BLOB 类型的列可以存储庞大的二进制数据。如果你有使用通配符“*”进行查询的习惯，那么将如此大的文件存储在Products 表中，而且又不经常使用，很容易就会在查询时遗漏这一点，从而造成不必要的性能问题。

正确的做法是将BLOB 列存在另一张表中，和Products 表分离但又与其相关联。

（3）创建关联表

把列转为行。

第十章 取整错误

1、为什么

关于计算机二进制浮点数表示法导致的精度丢失和取整错误，可以看我这一篇：《关于 JavaScript 的 精度丢失 与 近似舍入》，原理是一样的。

2、怎么办

解决方案：使用 SQL 中的 NUMERIC 或 DECIMAL 类型来代替 FLOAT 及与其类似的数据类型进行固定精度的小数存储。

哪怕不是存小数而是存整数，也不要用 FLOAT！同样会存在错误隐患。

第十一章 每日新花样

需求：限定列的有效值。

1、反模式

1、CHECK 约束

缺点：

  • 添删有效值不方便，需要重新 drop 并 create 约束。
  • 取列的有效值的 list 很麻烦，且不可复用。

2、域

缺点：属于数据库高级操作，杀鸡焉用牛刀。不赘述了。


3、用户自定义类型（UDT）

缺点：属于数据库高级操作，杀鸡焉用牛刀。不赘述了。

2、推荐

1、使用枚举类型 ENUM

优点：

  • 添删有效值很方便
  • 可复用。可以把有效值写在应用代码中，结合 ORM，即可以 for 数据库，也可以 for 前端显示（例如 展示在 select 组件）

2、创建一个单独的表，存列的有效值，其他表使用外键引用

优点：

  • 上面 ENUM 的优点都有。
  • 更加灵活、拓展性更强。

第十二章 幽灵文件

原始图片文件可以以二进制格式存储在 BLOB 类型中，就像之前我们存储超长字段那样。

然而，很多人选择将图片存储在文件系统中，然后在数据库里用 VARCHAR 类型来记录对应的路径。这其实是一种反模式。

具体要不要用这种反模式，见仁见智，要按照具体使用场景来判断。

现在普遍还是流行这种反模式，例如我司，因为静态资源都是上传到 OSS 托管，有 CDN 加成。

第十三章 乱用索引

略

第十四章 对未知的恐惧

1、需求：如何筛选出两个列值不相等的行?

假设：我们有 test 表：

id left right
1 111 222
2 333 333
3 444 NULL
4 NULL 555
5 NULL NULL

正确结果是 id 为 1、3、4 的行。

2、反模式

错误方法：直接使用 where "left" != "right" ，但 != 对 NULL 无效。

正如下面这个例子：

select 1 != 1; #f
select 1 != 2; #t
select 1 != NULL; #null（不是我们想要的结果，应该返回 t)
select NULL != NULL; #null（不是我们想要的结果，应该返回 f)

后两种情况结果为 NULL，是因为 sql 是三值逻辑而不是二值逻辑，具体可以看我之前的一篇：《SQL基础教程》+《SQL进阶教程》学习笔记，里面有详细介绍。

3、推荐

（1）将 NULL 视为特殊值

将 NULL 视为特殊值，额外用 IS ( NOT ) NULL 判断：where "left" != "right" or ( "left" is null and "right" is not null ) or ( "left" is not null and "right" is null )

这种写法很累赘。

（2）IS ( NOT ) DISTINCT FROM

直接用 IS DISTINCT FROM，即：where "left" IS DISTINCT FROM "right" ，不需要额外对 NULL 判断。


IS ( NOT ) DISTINCT FROM 的支持情况：

每个数据库对 IS ( NOT ) DISTINCT FROM 的支持是不同的。PostgreSQL、IBM DB2 和 Firebird 直接支持它，Oracle 和 Microsoft SQL Server 暂时还不支持。MySQL 提供了一个专有的表达式 <=>，它的工作逻辑和 IS NOT DISTINCT FROM 一致。

第十五章 模棱两可的分组

1、反模式

例如，有 test 表：

id type name join_time
1 老师 赵老师 2020-01-01
2 老师 钱老师 2020-01-02
3 同学 张三 2020-01-03
4 同学 李四 2020-01-04
5 同学 王五 2020-01-05

需求：我们需要在 老师 or 同学 分别里找出 join_time 最早的一条记录。

执行 SELECT "type", MIN("join_time"), "name" FROM "test" GROUP BY "type"

name 列就是有歧义的列，可能包含不可预测的和不可靠的数据：

  • 在 MySQL 中，返回的值是这一组结果中的第一条记录。
  • 在 Postgres 中，会报错。

2、推荐

解决方案：无歧义地使用列。

（1）只查询功能依赖的列

最直接的解决方案就是将有歧义的列排除出查询。

执行 SELECT "type" FROM "test" GROUP BY "type"

但这满足不了我们的需求，pass。

（2）对额外的列使用聚合函数

执行 SELECT "type", MIN("join_time"), MIN("name") FROM "test" GROUP BY "type"

如果不能保证 MIN("join_time") 和 MIN("name") 是指向同一行，那这个写法就是错的。有风险，pass。

（3）使用关联子查询
SELECT * FROM test as t1
WHERE NOT EXISTS 
(
	SELECT * FROM test as t2
	WHERE t1."type" = t2."type" and t1.join_time > t2.join_time 
)

缺点：性能不好。


[拓展] 用关联子查询写出来的思路：

涉及 SQL 基础的全程量化和存在量化的知识点，详细可参考我的旧文：《SQL基础教程》+《SQL进阶教程》学习笔记

2021-04-07-08-45-51

如果需求变成：我们需要在 老师 or 同学 分别里找出 join_time 最晚的一条记录，那只需要把 t1.join_time > t2.join_time 变成 t1.join_time < t2.join_time 即可：

SELECT * FROM test as t1
WHERE NOT EXISTS 
(
	SELECT * FROM test as t2
	WHERE t1."type" = t2."type" and t1.join_time > t2.join_time 
)
（4）使用衍生表 JOIN
SELECT * FROM test as t1 
INNER JOIN
(
	SELECT "type", MIN("join_time") as "join_time" FROM test  
	GROUP BY "type" 
) as t2 
ON t1.join_time = t2.join_time

缺点：性能不好。


如果需求变成：我们需要在 老师 or 同学 分别里找出 join_time 最晚的一条记录，那只需要把 MIN("join_time") 变成 MAX("join_time") 即可：

SELECT * FROM test as t1 
INNER JOIN
(
	SELECT "type", MAX("join_time") as "join_time" FROM test  
	GROUP BY "type" 
) as t2 
ON t1.join_time = t2.join_time
（5）直接使用 LEFT JOIN
SELECT * FROM test as t1 
LEFT JOIN test as t2
ON t1."type" = t2."type" 
AND  
(
		t1.join_time > t2.join_time 
)
WHERE t2."id" IS NULL

解释：t1.join_time > t2.join_time 搭配 WHERE t2."id" IS NULL 是利用 LEFT JOIN 的特性，即如果找到匹配行则可以生成多行，但若找不到匹配行，则另一边置 NUll。

缺点：性能稍好，但是较难维护。


如果需求变成：我们需要在 老师 or 同学 分别里找出 join_time 最晚的一条记录，那只需要把 t1.join_time > t2.join_time 变成 t1.join_time < t2.join_time 即可：

SELECT * FROM test as t1 
LEFT JOIN test as t2
ON t1."type" = t2."type" 
AND  
(
		t1.join_time < t2.join_time 
)
WHERE t2."id" IS NULL
（6）窗口函数
SELECT
	* 
FROM
	(
	SELECT
		*,
		RANK() OVER ( PARTITION BY "type" ORDER BY "join_time" ASC ) AS "rank" 
	FROM
	  test  
	) as t1
WHERE
	"t1"."rank" = 1

关于更多窗口函数的介绍，可看我的旧文：《SQL基础教程》+《SQL进阶教程》学习笔记


如果需求变成：我们需要在 老师 or 同学 分别里找出 join_time 最晚的一条记录，那只需要把 ASC 变成 DESC 即可：

SELECT
	* 
FROM
	(
	SELECT
		*,
		RANK() OVER ( PARTITION BY "type" ORDER BY "join_time" DESC ) AS "rank" 
	FROM
	  test  
	) as t1
WHERE
	"t1"."rank" = 1

第十六章 随机选择

相比于将整个数据集读入程序中再取出样例数据集，直接通过数据库查询拿出这些样例数据集会更好。

本章的目标就是要写出一个仅返回随机数据样本的高效 SQL 查询。

1、传统方法、random()

SELECT * FROM test ORDER BY random() limit 1

缺点：

  • 整个排序过程无法利用索引
  • 性能不好。好不容易对整个数据集完成排序，但绝大多数的结果都浪费了，因为除了返回第一行之外，其他结果都立刻被丢弃了。

2、推荐方法1、从 1 到最大值之间随机选择

一种避免对所有数据进行排序的方法，就是在 1 到最大的主键值之间随机选择一个。

但要考虑 1 到最大值之间有缝隙的情况。

利用 JOIN：

SELECT
	t1.* 
FROM
	test AS t1
	JOIN ( SELECT CEIL( random() * ( SELECT MAX ( "id" ) FROM test ) ) AS "id" ) AS t2
ON
	t1."id" >= t2."id"
ORDER BY
	t1."id" 
LIMIT 1

3、推荐方法2、使用偏移量选择随机行

计算总的数据行数，随机选择0 到总行数之间的一个值，然后用这个值作为位移来获取随机行。

利用 OFFSET：

SELECT
	* 
FROM
	test 
	LIMIT 1 OFFSET ( 
		SELECT CEIL( 
			random() * ( SELECT COUNT ( * ) FROM test )
		) - 1 
	)

4、推荐方法3、专有解决方案

每种数据库都可能针对这个需求提供独有的解决方案：

-- Microsoft SQL Server 2005 增加了一个 TABLE-SAMPLE 子句。
-- Oracle 使用了一个类似的 SAMPLE 子句，比如返回表中1%的记录。
-- Postgres 也有类似的叫 TABLESAMPLE。

但是这种采样的方法返回结果的行数很不稳定，感觉还是不推荐了。

第十七章 可怜人的搜索引擎

1、需求

全文搜索。

2、反模式

使用 LIKE 或者正则表达式进行模式匹配搜索。

缺点：使用模式匹配操作符的最大缺点就在于性能问题。它们无法从传统的索引上受益，因此必须进行全表遍历。

3、推荐

解决方案：使用正确的工具。

（1）数据库扩展

每个大品牌的数据库都有对全文搜索这个需求的解决方案。

例如，PostgreSQL 8.3 提供了一个复杂的可大量配置的方式，来将文本转化为可搜索的词汇集合，并且让这些文档能够进行模式匹配搜索。即，为了最大地提升性能，你需要将内容存两份：一份为原始文本格式，另一份为特殊的 TSVECTOR 类型的可搜索格式。

空间换时间。

① 步骤：

建表时创建 TSVECTOR 数据类型的列。

② 步骤：

你需要确保 TSVECTOR 列的内容和你所想要搜索的列的内容同步。PostgreSQL 提供了一个内置的触发器来简化这一操作。

触发器写法略，可看原书。

③ 步骤：

你也应该同时在 TSVECTOR 列上创建一个反向索引（GIN）。

写法略，可看原书。

④ 步骤：

在做完这一切之后，就可以在全文索引的帮助下使用PostgreSQL 的文本搜索操作符@@来高效地执行搜索查询。

写法略，可看原书。

（2）自己实现 反向索引

太复杂，略。

（3）第三方搜索引擎

你不必使用 SQL 来解决所有问题。

两个产品：Sphinx Search 和 Apache Lucene。

使用略，可看原书。

第十八章 意大利面条式查询

1、反模式

一条精心设计的复杂 SQL 查询，相比于那些直接简单的查询来说，不得不使用很多的JOIN、关联子查询和其他让 SQL 引擎难以优化和快速执行的操作符。而程序员直觉地认为越少的SQL 执行次数性能越好。

2、推荐

目标：减少 SQL 查询数量。

解决方案：

  • 分而治之，一步一个脚印
  • 你可以将几个查询的结果进行 UNION 操作，从而最终得到一个结果集

好处：

  • 性能更好
  • 便于开发、维护

第十九章 隐式的列

1、反模式

我所遇到的程序员使用SQL通配符时问得最多的问题是：“有没有选择除了几个我不想要的列之外所有列的方法？

答案是“没有”。

其实我还是希望数据库厂商能加上，现在网上有很多 hack 的方法，需求毕竟是在的。诶。

2、推荐

解决方案：明确列出列名，而不是使用通配符或者隐式列的列表。

第二十章 明文密码

可以参考我之前的文章：《数据库里账号的密码，需要怎样安全的存放？—— 密码哈希（Password Hash）》

第二十一章 SQL 注入

1、需求

防止 SQL 注入。

2、反模式

（1）转义

比如，在PHP 的 PDO 扩展中，可以使用一个 quote()函数来定义一个包含引号的字符串或者还原一个字符串中的引号字符。

3、推荐

解决方案：不信任任何人。

（1）check 数据
  • 过滤输入内容.比如在 PHP 中，可以使用filter 扩展

    Node.js 的 joi 库。

  • 正则表达式来匹配安全的子串

    用上一条的过滤库也可以实现。

  • 用类型转换函数
（2）参数化动态内容

你应该使用查询参数将其和 SQL 表达式分离。

没有哪种 SQL 注入的攻击能够改变一个参数化了的查询的语法结构。

缺点：

① 会影响优化器的效果，最终影响性能。

比如说，假设在 Accounts 表中有一个 is_active 列。这一列中99%的记录都是真实值。对 is_active = false 的查询会得益于这一列上的索引，但对于 is_active = true 的查询却会在读取索引的过程中浪费很多时间。然而，如果你用了一个参数 is_active = ? 来构造这个表达式，优化器不知道在预处理这条语句的时候你最终会传入哪个值，因此很有可能就选择了错误的优化方案。

要规避这样的问题，直接将变量内容插入到SQL 语句中会是更好的方法，不要去理会查询参数。一旦你决定这么做了，就一定要小心地引用字符串。

可以结合下面的 ”（3）将用户与代码隔离“ 一起使用。


② 这还不是一个通用的解决方案，因为查询参数总被视为是一个字面值。

例如：

  • 多个值的列表不可以当成单一参数： 例如 in（x，x，x）

    解决方案：使用了一些 PHP 内置的数组函数来生成一个占位符数组。

  • 表名、列名、SQL 关键字 无法作为参数。

    解决方案：可以用存储过程；或者通过事先在应用逻辑代码里，先用字符串拼接的方式生成好 sql 代码。


③ 不好调试

这意味着，如果你获取到一个预先准备好的SQL 查询语句，它里面是不会包含任何实际的参数值的。当你调试或者记录查询时，很方便就能看到带有参数值的SQL 语句，但这些值永远不会以可读的SQL 形式整合到查询中去。

解决方案：调试动态化SQL 语句的最好方法，就是将准备阶段的带有占位符的查询语句和执行阶段传入的参数都记录下来。（自己动手，丰衣足食。）

（3）数据访问框架

你可能看过数据访问框架的拥护者声称他们的库能够抵御所有SQL 注入的攻击。对于所有允许你使用字符串方式传入SQL 语句的框架来说，这都是扯淡。

没有任何框架能强制你写出安全的 SQL 代码。一个框架可能会提供一系列简单的函数来帮助你，但很容易就能绕开这些函数，然后使用通常的修改字符串的办法来编写不安全的SQL语句。

就是你得保证自己写的是符合框架规范的写法，不然人为因素还是会导致出错。

（4）存储过程

略

（5）将用户与代码隔离

将请求的参数作为索引值去查找预先定义好的值，然后用这些预先定义好的值来组织 SQL 查询语句。

例如把请求的参数经过 if else ，来分配进预先定义好的 SQL 查询语句。

（6）找个可靠的人来帮你审查代码

找到瑕疵的最好方法就是再找一双眼睛一起来盯着看。

有条件可以结对编程。

第二十二章 伪键洁癖

1、反模式

不能忍受主键中间出现不连续的缺位。

重用主键并不是一个好主意，因为断档往往是由于一些合理的删除或者回滚数据所造成的。

2、推荐

（1）克服心里障碍

它们不一定非得是连续值才能用来标记行。

将伪键当做行的唯一性标识，但它们不是行号。别把主键值和行号混为一谈。


问：怎么抵挡一个希望清理数据库中伪键断档的老板的请求？
答：这是一个沟通方面的问题，而不是技术问题。

（2）使用GUID

GUID （Globally Unique Identifier 全局唯一标识符）是一个128 位的伪随机数（通常使用32 个十六进制字符表示）。

GUID 也称 UUID (Universally unique identifier 通用唯一标识符)。

GUID 相比传统的伪键生成方法来说，至少有如下两个优势：

  • 可以在多个数据库服务器上并发地生成伪键，而不用担心生成同样的值。
  • 没有人会再抱怨有断档——他们会忙于抱怨输入32 个十六进制字符做主键。

第二十三章 非礼勿视

1、反模式 —— 忽略错误处理

“我不会让错误处理弄乱了我的代码结构的。”

导致问题：

  • 代码健壮性不好
  • 出现错误不好回溯
  • 用户体验差（用户看不见代码，他们只能看见输出。当一个致命错误没有被处理时，用户就只能看到一
    个白屏，或者是一个不完整的异常信息。）

2、推荐 —— 优雅地从错误中恢复

一些计算机科学家推测在一个稳固的程序中，至少有50%的代码是用来进行错误处理的。

所有喜欢跳舞的人都知道，跳错舞步是不可避免的。优雅的秘诀就是弄明白怎么挽回。给自己一个了解错误产生原因的机会，然后就可以快速响应，在任何人注意到你出丑之前，神不知鬼不觉地回到应有的节奏上。

第二十四章 外交豁免权

1、反模式

技术债务（technical debt），是程序设计及软件工程中的一个比喻。指开发人员为了加速软件开发，在应该采用最佳方案时进行了妥协，改用了短期内能加速软件开发的方案，从而在未来给自己带来的额外开发负担。这种技术上的选择，就像一笔债务一样，虽然眼前看起来可以得到好处，但必须在未来偿还。软件工程师必须付出额外的时间和精力持续修复之前的妥协所造成的问题及副作用，或是进行重构，把架构改善为最佳实现方式。

2、推荐

  • 画实体关系图（ER 图）,更复杂一点的ER 图包含了列、主键、索引和其他数据库对象。

    还有些工具能够通过SQL 脚本或者运行中的数据库直接通过反向工程得到 ER 图。

  • 写文档
  • 源代码管理
  • 测试

[拓展] 版本管理 之 管理数据库：

版本管理工具管理了代码，但并没有管理数据库。Ruby on Rails 提供了一种技术叫做“迁移”，用来将版本控制应用到数据库实例的升级管理上。

大多数其他的网站开发框架，包括PHP 的Doctrine、Python 的Django 以及微软的 ASP.NET，都支持类似于Rails 的“迁移”这样的特性。

我目前 Node.js 用的 sequelize 就包含了这种数据库的迁移脚本。

缺点：但它们还不是完美的，只能处理一些简单类型的结构变更。而且从根本上说，它们在原有版本控制服务之外又建立了一个版本系统。

第二十五章 魔豆

略

好词好句

所谓专家，就是在一个很小的领域里把所有错误都犯过了的人。 —— 尼尔斯·玻尔

规范仅仅在它有帮助时才是好的。

Mitch Ratcliffe 说：“计算机是人类历史中最容易让你犯更多错误的发明……除了手枪和龙舌兰之外。”

posted @ 2020-04-19 22:46  小蒋不素小蒋  阅读(3123)  评论(4)  编辑  收藏  举报

ICP证：沪ICP备20014317号