(翻译)关系型数据库工作原理(二)

总览

  我们刚刚看了数据库中的基本组件。 现在我们需要回退一步看看大局。

  数据库是可以轻松访问和修改的信息集合。但是一堆简单的文件也可以做同样的事情。 事实上，像SQLite这样最简单的数据库也只不过是一堆文件。但SQLite是一组精心设计的文件，因为它允许你：

• 使用事务确保数据安全和连贯
• 即使在百万级数据量时，也能快速处理数据

  更一般地说，数据库可以看作如下图：

  在写这部分之前，我读了很多书/论文甚至是数据库的源码。不要太在意我如何组织数据库、如何命名流程。我专门为这篇文章做了精心的挑选。重要的是这些不同的组成部分，总体思路是将数据库划分为多个相互交互的组件。

核心组件：
The process manager: 许多数据库都有一个需要管理的进程/线程池。另外，现代数据库为了获得纳秒级别的性能提升，会使用自己的线程去取代操作系统的线程。
The network manager: 网络I/0是个大问题，尤其是对分布式数据库来说，这就是为什么一些数据库会自己进行网络处理。
File system manager:磁盘I/O是数据库的第一个瓶颈。拥有一个能够完美处理操作系统文件系统甚至替换它的管理器非常重要。
The memory manager: 我们需要大量的RAM来减少磁盘I/0带来的性能损失。但是如果你处理大量内存，你需要一个高效的内存管理器。尤其是当你有许多查询同时使用内存时。
Security Manager: 用于管理用户的身份验证和授权
Client manager: 用于管理客户端连接
…

工具：
Backup manager: 用于保存和恢复数据库
Recovery manager:用于数据库崩溃后以完整的状态重启
Monitor manager: 用于记录数据库的活动并提供监控数据库的工具
Administration manager:用于存储元数据（如表的名称和结构）并提供管理数据库，模式，表空间的工具，…

查询管理器：
Query parser:校验查询是否有效
Query rewriter: 对查询预先优化
Query optimizer：查询优化
Query executor： 编译并执行查询

在本文的其余部分，我将重点介绍数据库是如何通过以下过程管理SQL查询的：

• the client manager
• the query manager
• the data manager (这一部分也会介绍recovery manager )

client manager

  客户端管理器处理与客户端的通信。客户端可以是一台（web）服务器，也可以是一个终端用户或者终端应用。客户端管理器提供了通过一组众所周知的API访问数据库：JDBC，ODBC，OLE-DB …

  它还可以提供专用的数据库访问API。

  当你连接到数据库时：

• 管理器首先检查你的凭据（你的登录名和密码），然后看你有没有使用数据库的权限。这些权限是由你的DBA设置的。
• 然后检查是否有空闲的进程（线程）去管理你的查询 同时也会检查数据库是否超负荷
• 它会等待一段时间直到它获取到所需要的资源，如果等待超时，就会关闭连接，并返回可读的错误信息。
• 他会将你的查询发送给查询管理器，并处理你的查询
• 由于查询处理不是一件“全有或全无”的事情，因此只要它从查询管理器获取数据，它就会将部分结果存储在缓冲区中并开始将它们发送给你。
• 如果出现问题，它会停止连接，并返回可读的错误解释，并释放资源。

Query manager

  这部分是数据库的力量所在。在这部分执行期间，那些写得不好的查询会被转换为快速可执行代码。代码接着被执行，并向client manager返回结果。分好几步操作：

• 查询首先被解析是否有效
• 然后对查询重写，并移除一些非必须的操作，添加一些预优化。
• 然后进行优化提高查询性能，将优化后的查询语句转换为一个可执行的数据访问计划
• 再然后，访问计划被编译
• 最后，被执行.

  这里最后两点不是那么重要，所以就不详细讲了。

  看完这部分后，如果你还想要进一步了解，我推荐阅读这些：

• 基于成本优化初步研究论文（1979）《 Access Path Selection in a Relational Database Management System》。这篇文章只有仅仅只有12页，只需要计算机平均水平就能读懂。
  https://people.eecs.berkeley.edu/~brewer/cs262/3-selinger79.pdf

• 一篇写得非常好并且深入介绍DB2 9.x优化查询文章
  http://infolab.stanford.edu/~hyunjung/cs346/db2-talk.pdf

• 一篇介绍关于PostgreSQL如何优化查询的文章。这篇文章比较容易理解，因为不是一篇讲述“PostgreSQL使用哪些算法”的文章，更像是讲述“让我们看看PostgreSQL在不同场景下的查询计划”的文章。
  http://momjian.us/main/writings/pgsql/optimizer.pdf

• SQLite 关于优化的官方文档。因为SQLite使用简单的规则，所以这篇文章比较好理解。还有就是，这是唯一解释其工作原理的官方文档。
  https://www.sqlite.org/optoverview.html

• 关于SQL Server 2005优化查询的介绍。
  https://blogs.msdn.com/cfs-filesystemfile.ashx/__key/communityserver-components-postattachments/00-08-50-84-93/QPTalk.pdf

• 关于Oracle 12c优化的白皮书。
  https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf

• 两门关于查询优化的理论课程。-来自《DATABASE SYSTEM CONCEPTS》一书的作者。偏向磁盘I/O消耗，但是需要良好的计算机科学（Computing Science）基础。
  http://codex.cs.yale.edu/avi/db-book/db6/slide-dir/PPT-dir/ch12.ppt
  http://codex.cs.yale.edu/avi/db-book/db6/slide-dir/PPT-dir/ch13.ppt

• 我找了另外一门比较好懂的课程，但是只讲了join操作和磁盘I/O
  https://www.informatik.hu-berlin.de/de/forschung/gebiete/wbi/teaching/archive/sose05/dbs2/slides/09_joins.pdf

Query parser

  解析器会对sql语句进行语法检查。如果你的查询语句有错误，解析器会拒绝执行查询。比如你将“SELECT …”错写成“SLECT …” ，好，故事结束。

  但是，再深入一点，它还会检查关键字的顺序。比如关键字WHERE 在SELECT 之前，查询将会被拒绝。
  然后分析查询语句中的表和字段。解析器通过存储的元数据去检查：

• 表是否存在
• 表的字段是否存在

  再看看对字段的操作是否允许。（比如你不能将string和integer 类型的放一起比较，也不能对integer类型的字段使用substring()函数）
  然后再检查看你有没有读写查询中表的权限。当然，这些权限也是你DBA设置的。
  在解析过程中，SQL查询将转换为内部表示（通常是一棵树）
  如果所有检查都通过，内部表示将会被发送给query rewriter.

Query rewriter

  在这一步中，我们有一个查询的内部表示，重写的主要目的在于：
  对查询预优化
  避免不必要的操作
  帮助优化器找到最好的优化路径
  重写器参照已知的若干规则，如果查询符合其中一条规则，则对应规则被应用，查询被重写。以下是部分可选规则：

• 视图合并：如果你在查询中使用视图，这视图会转换成SQL代码
• 平面化子查询：子查询很难优化，所以，重写器会尝试修改查询以删除子查询

  例如

	SELECT PERSON.*
	FROM PERSON
	WHERE PERSON.person_key IN
	(SELECT MAILS.person_key
	FROM MAILS
	WHERE MAILS.mail LIKE 'christophe%');

  将会被替换成

	SELECT PERSON.*
	FROM PERSON, MAILS
	WHERE PERSON.person_key = MAILS.person_key
	and MAILS.mail LIKE 'christophe%';

• 删除不必要的操作。例如，你使用关键字DISTINCT的地方刚好有唯一约束（UNIQUE constraint），那么关键字DISTINCT会被移除
• 消除多余的连接。如果你因为视图的隐藏连接条件造成你有两次相同的连接条件，或者通过传递性存在无用连接，那么会将这些多余的连接删掉。
• 常量表达式检测。如果你写了需要计算的式子，那么在重写期间会被计算一次。例如 WHERE AGE > 10+2会被重写成WHERE AGE > 12 ，TODATE(“some date”)会被转换成具有日期时间格式的日期。
• （高级）分区裁剪：如果你使用分区表，那么重写器会帮你找到目标分区。
• （高级）实体化视图：如果你的实体化视图和查询中的谓词集相匹配，重写器会检测视图是否是最新的，并修改查询使用实体化视图而不是原表。
• （高级）自定义规则：如果你有自定义规则来修改查询（如Oracle策略），则重写器将执行这些规则
• （高级）Olap转换：分析/窗口函数，星形连接，rollup …也被转换（但我不确定它是由重写器还是优化器完成的，因为两个进程非常贴近，所以还是取决于具体的数据库）。

重写后的查询会发送到查询优化器，接下来部分会很有趣。

统计

  在我们讲数据库查询优化之前，我们先聊一下统计。离开统计，数据库就是个傻蛋。如果你不告诉数据库分析它自己的数据，它就不会这样做，它会做出（非常）糟糕的假设。
  数据库到底需要哪种信息呢？
  我必须（简要地）谈谈数据库和操作系统如何存储数据。 他们使用的是一个称为页面或块的最小单位（默认为4或8千字节）。 这意味着即使你只需要1 KB，也要花费1页的代价。 如果页面有8KB，那么你将浪费7KB。
回到统计！ 当你要求数据库收集统计信息时，它会计算以下值：

• 表中的行数/页数
• 对表中的每一列：
  – 不同的数据值
  – 数据长度（最小值，最大值，平均值）
  – 数据范围信息（最小值，最大值，平均值）
• 表索引的相关信息。

  这些统计信息将帮助优化器评估查询的磁盘I / O，CPU和内存使用情况。
  每一列的数据统计都非常重要。例如，如果一张PERSON 表有两列：LAST_NAME, FIRST_NAME. 被连接，经过统计，我们知道，FIRST_NAME字段上有1000个不同的值，LAST_NAME字段上有1 000 000个不同的统计值。因此，数据库以 LAST_NAME, FIRST_NAME的顺序连接而不是按照FIRST_NAME,LAST_NAME顺序。因为字段LAST_NAME 上很少有重复的，所以只会产生少量的比较。而且大多数比较的时候，只需要比较2～3个字符就够了

  但这些是基本的统计数据。 你可以让数据库计算直方图高级统计信息。
  直方图是列内值分布的统计信息。 例如：

• 频率最高的值
• 频数
• …
    这些额外的统计信息能帮助数据库找到更好的查找计划。尤其是对谓词“=”（ex: WHERE AGE = 18 ）或者范围谓词（ex: WHERE AGE > 10 and AGE <40）。因为有助于数据库对这些谓词涉及到的列会有更好的了解。
    注意：这个概念的专业名词叫选择性（selectivity）
  统计信息存储在数据库的元数据中。 例如，你可以在下面这些（非分区）表看到统计信息：
• in USER/ALL/DBA_TABLES and USER/ALL/DBA_TAB_COLUMNS for Oracle
• in SYSCAT.TABLES and SYSCAT.COLUMNS for DB2.

  统计结果必须实时更新，如果数据库认为一张表只有500行记录，但实际上却有1 000 000行记录，这就彻底崩溃了。统计数据的唯一缺点是需要时间去计算它们。这就是为什么大多数数据库默认不去自动计算的原因。当数据量达到上百万时，计算就会变得很困难。基于上述原因，你可以选择只计算基本的统计信息或者计算数据库样本的统计信息。

  例如，我的工程需要处理包含上亿数据的表。我只对其中10%进行统计计算，这就获得了大量的时间收益。在这个例子中，将是一个糟糕的决定，因为在Oracle 在个别表个别列中偶尔选择10%的数据和选择全部数据（10G）是不一样的。（对于有100M行的表，这种情况不太可能发生）
  注意：当然，每个数据库都有更多高级统计信息。 如果你想了解更多信息，请阅读数据库的文档。 话虽这么说，我在试图了解统计数据是如何使用期间发现最好的官方文档来自PostgreSQL。

Query optimizer

  所有现代数据库都使用基于成本优化（or CBO）来优化查询。原理是:为每一步操作设定一个成本，找出一个总成本值最低的执行路径来获取结果，从而找到降低查询成本的最佳方法。
  要了解成本优化器的工作原理，我认为有一个例子可以“感受”这项任务背后的复杂性。 在这一部分中，我将向你介绍多表连接的3种常用方法，我们很快就会发现即使是简单的连接查询优化也是一个噩梦。 在那之后，我们将看到真正的优化器是如何完成这项工作的。
  对这些连接方式，我主要分析他们的时间复杂度，无非是数据库优化器计算他们的cpu成本，磁盘I/O成本，和内存需求。时间复杂度和CPU成本之间的区别是时间成本是非常近似的（对像我这么懒的人就是这样）。
  对于CPU成本，我需要计算每个操作，如添加，“if语句”，乘法，迭代…此外：

• 每个高级代码操作都对应一定数量的低级CPU操作。
• 无论你使用的是英特尔酷睿i7，英特尔奔腾4，还是AMD皓龙等，CPU操作的成本都不相同（就CPU周期而言）。 换句话说，它取决于CPU架构。

  使用时间复杂度更容易（至少对我来说），通过它我们仍然可以得到CBO的概念。 我有时会谈论磁盘I / O，因为它是很重要的概念。 请记住，大部分时间性能瓶颈是磁盘I / O而不是CPU使用率。

索引Indexes

  我们在讲述B+树的时候就说过索引，你只需要记住那些索引是有序的就行。
  仅供参考，还有其他类型的索引，如位图索引。 与B + Tree索引相比，它们在CPU，磁盘I / O和内存方面的成本不同。
  此外，如果可以减少执行计划的成本，许多现代数据库可以为当前查询动态创建临时索引。

访问路径Access Path

  在连接操作之前，你首先需要获取到数据，下面是你获取数据的具体方式
  注意：由于所有访问路径的真正问题是磁盘I / O，所以我会很少提及时间复杂度。

全表扫描Full scan

  如果你曾经阅读过执行计划，那你肯定看到过full scan（或仅 scan）一词。全表扫描就是数据库完整读取表或者索引的全部记录。 就磁盘I / O而言，全表扫描显然比全索引扫描更昂贵

范围查找Range Scan

  还有其他类型的扫描方式，比如说范围扫描。当你使用像“WHERE AGE > 20 AND AGE <40”的谓词时，范围扫描就启用.
  当然，想要使用范围查询的前提是对应字段建立了索引。
  我们在前面一部分已经看过了，像这样的范围查询的时间成本为log(N) +M, N是索引里面包含的数据总量，M是在这个范围内符合条件的行数。因为统计，我们知晓M,N的值（M就是谓词AGE >20 AND AGE<40的选择性）。另外，对于范围扫描，你不需要读取整个索引，因此在磁盘I / O方面的成本比全表扫描更便宜。

唯一性扫描Unique scan

  如果你只需要从索引获取一个值，你可以使用唯一性扫描。

按行id访问 Access by row id

  大多数情况下，如果数据库使用索引，则必须查找与索引关联的行。 为此，数据库使用行id访问。
  例如：

	SELECT LASTNAME, FIRSTNAME from PERSON WHERE AGE = 28

  如果你的PERSON 表的AGE列上建有索引，优化器会使用索引查找表中所有AGE为28的人，然后在表中找到所有的关联行，因为你想要查询姓名而索引里面只包含AGE信息。
  但是，如果你这么写：

	SELECT TYPE_PERSON.CATEGORY from PERSON ,TYPE_PERSON
	WHERE PERSON.AGE = TYPE_PERSON.AGE

  PERSON上的索引将用于与TYPE_PERSON连接，但由于你没有询问PERSON 表的信息，因此不会通过行ID访问表PERSON。
  这适合少量的访问，这个操作的真正问题是磁盘I/O。如果你有大量的按行id操作，数据库可能会选择全表扫描。

其他路径

  在这里，我不会介绍所有的访问路径，如果你还想进一步了解，你可以阅读Oracle文档，也许名称会有所差异，但背后的原理都是一样的。
https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#TGSQL228

连接操作

  好了，我们已经搞清楚怎么获取我们的数据，来，继续！
  我会介绍3个常见的连接运算符：Merge Join, Hash Join 和Nested Loop Join. 在此之前，我需要引入新的词汇：内部关系和外部关系。关系可以是：

• 一张表
• 一个索引
• 上一步操作的临时结果（比如上一步连接的结果）

  当你连接这两个关系时，连接算法会以不同的方式去管理这两个关系。在本文，我假设：

• 外部关系是左侧的数据集
• 内部关系是右侧的数据集

  例如，A JOIN B指在A和B两者之间的连接。A是外部关系，B是内部关系。
  大多数情况下， A JOIN B 和 B JOIN A的成本是不一样的。
  另外，我还假设外部关系有N个元素，内部关系有M个元素。记住，真正的优化器是知道N和M这些统计信息的。
注意：N和M是这些关系的基数。

Nested loop join

  nested loop join是最简单的一种。

  原理：
  对外部关系中的每一行
  都会去内部关系查找出全部的行看是否匹配
  伪代码如下：

	nested_loop_join(array outer, array inner)
	  for each row a in outer
	    for each row b in inner
	      if (match_join_condition(a,b))
	        write_result_in_output(a,b)
	      end if
	    end for
	   end for

  因为是双重循环，时间复杂度为 O(NM)
  就磁盘I/O而言，对外部关系N行中的每一行，内部关系都要读M行。这种算法需要从磁盘读N+NM行记录，但是当内部关系足够小时，你可以把内部关系存放在内存中，这种修改要求内部关系必须是最小的，这样才能有更多机会在内存中适配。
  两种关系就时间复杂度而言没有区别，但是对磁盘I/O而言，最好的方式是只读一次
  当然，内部关系如果能被索引代替，这对磁盘I/O更好。
  因为这种算法很简单，当内部关系数据太多不能全部放进内存的时候，下面这个版本就对磁盘I/O更友好了。下面是原理：

• 替代原来逐行读取两个关系的方式
• 你通过逐块的读取数据，并将这两块的数据保存在内存中。
• 你比较这两堆中的行，并保存符合条件的行。
• 然后你从磁盘加载新的块，继续比较。
• 一直到没有块加载

下面是一个可能的算法：

	// improved version to reduce the disk I/O.
	nested_loop_join_v2(file outer, file inner)
	  for each bunch ba in outer
	  // ba is now in memory
	    for each bunch bb in inner
	        // bb is now in memory
	        for each row a in ba
	          for each row b in bb
	            if (match_join_condition(a,b))
	              write_result_in_output(a,b)
	            end if
	          end for
	       end for
	    end for
	   end for

  这个版本，时间复杂度还是一样，但是磁盘访问的次数减少了。

• 第一个版本，算法需要N+N*M次访问（每次访问获取一条）
• 新版本，磁盘访问的次数变成了 number_of_bunches_for(outer)+ number_of_ bunches_for(outer)* number_of_ bunches_for(inner).
• 增加块的大小会减少磁盘访问次数。
  注意：每次磁盘访问都会比以前算法收集更多的数据，但这没关系，因为它们是顺序访问（机械磁盘的真正问题是获取第一个数据的时间）。

Hash join

  hash join更复杂，但是在很多场景中，它比nested loop join成本更低。

  hash join原理：

• 获取内部关系的所有元素
• 构建一个内存哈希表
• 一个一个的获取外部关系的全部元素
• 计算每个元素的哈希值（通过哈希表的哈希函数）去找到内部关系相关联的哈希桶
• 查看桶中的元素与外部关系的元素是否匹配

  论时间复杂度，我需要作一些假设来简化这个问题：

• 内部关系被拆分到X个哈希桶里。
• 哈希函数将两个关系的值几乎均匀的散列到各个桶里，换句话说，哈希桶大小相同
• 在内部关系的哈希桶里匹配一个外部关系的元素，成本为哈希桶里面元素的个数。
  时间复杂度为： (M/X) * N + cost_to_create_hash_table(M) + cost_of_hash_function*N

  如果哈希函数创建的哈希桶大小足够的小，这时间复杂度变为 O(M+N)
  下面是hash join更好的内存友好型版本，磁盘I/O友好度降低：

• 你同时计算内部关系和外部关系的哈希表
• 然后将它们放到磁盘上
• 然后你一个桶一个桶的比较这两个关系（其中一个加载在内存中，另一个逐行读取）

Merge join

  merge join是唯一生产有序结果的连接。
  注意：在这个简化的merge join中，没有内部或外部表; 他们都扮演同样的角色。 但实际的实现会有区别，例如，在处理重复项时。
merge join分为两步：

• （可选）排序连接操作：两个输入都在连接键（多个）上已经排好序。
• 合并连接操作：将有序的输入合并起来。

排序

  我们已经讲过了归并排序，这个例子中，归并排序是一个好的算法（如果内存不是问题则不是最好的）。
  但是，有些时候，数据集是已经排好序的。比如：

• 如果表是天然有序的，比如表在连接的条件是是索引组织的。
• 如果关系的连接条件就是索引
• 如果此连接应用于查询产生的有序的中间结果上。

merge join

这部分和我们先前看过的归并排序很类似，但是这一次，我们不再选取两个关系中的所有元素，我们只选那些相等的，原理如下：

1. 你比较两个关系中的当前元素，（第一次时，current为第一个元素）
2. 如果相同，你把两个元素都放进结果中，然后找到两个元素中的下一个继续比较
3. 如果不相同，寻找最低元素所在关系的下一个元素（因为下一个也许就能匹配上）
4. 重复1、2、3直到其中某个关系所有元素遍历结束

  因为两个关系都是有序的，所以你根本不需要回溯
  这个算法是个基础版，因为他没有处理相同数据出现多次的情况（换句话说，多值匹配）。基于这个情况，现实版本更复杂，这是我为啥选这个基础版。
  如果两个关系都是有序的，那么时间复杂度巍为 O(N+M)
  如果两个关系都需要排序，那么排序的成本为：O(NLog(N) + MLog(M))
  对于CS极客，这里有一个可能的算法来处理多个匹配（注意：我不是100％确定我的算法）：

mergeJoin(relation a, relation b)
  relation output
  integer a_key:=0;
  integer b_key:=0;
  
  while (a[a_key]!=null or b[b_key]!=null)
    if (a[a_key] < b[b_key])
      a_key++;
    else if (a[a_key] > b[b_key])
      b_key++;
    else //Join predicate satisfied
    //i.e. a[a_key] == b[b_key]
 
      //count the number of duplicates in relation a
      integer nb_dup_in_a = 1:
      while (a[a_key]==a[a_key+nb_dup_in_a])
        nb_dup_in_a++;
         
      //count the number of duplicates in relation b
      integer dup_in_b = 1:
      while (b[b_key]==b[b_key+nb_dup_in_b])
        nb_dup_in_b++;
         
      //write the duplicates in output
       for (int i = 0 ; i< nb_dup_in_a ; i++)
         for (int j = 0 ; i< nb_dup_in_b ; i++)     
           write_result_in_output(a[a_key+i],b[b_key+j])
            
      a_key=a_key + nb_dup_in_a-1;
      b_key=b_key + nb_dup_in_b-1;
 
    end if
  end while

哪个最好？

• 如果存在最好的连接类型。那么就不会出现这么多类型了。这个问题不好回答，因为有很多因素限制。
• 可用内存量：如果没有有足够的内存，那么就可以对hash join说拜拜了（至少对全内存的hash join）。
• 两个数据集的大小，比如，你有一张大表和一张非常小的表，nested loop join 比hash join快，因为hash join创建哈希的代价非常昂贵。
• 存在索引：当存在2个B+树索引时，选择merge join是非常明智的。
• 如果结果需要排序：即使你使用未排序的数据集，你会选择昂贵的 merge join（排序），因为在最后，结果将被排序并且你可以将排好序的结果与其他merge join的结果链接起来（或者是因为查询使用ORDER BY / GROUP BY / DISTINCT操作隐式/显式地询问排序结果）。
• 如果两个关系已经有序：这种情况下，merge join 是最好的选择。
• 你正在使用的连接类型，它是等值查询（tableA.col1 = tableB.col2） ， inner join， outer join，笛卡尔积还是self-join?某些连接在某些情况下无法工作。
• 数据分布：如果数据连接的条件是倾斜的（比如你通过人的姓氏查找人，很多人都有相同的姓氏），使用hash join将会是一个灾难，因为，哈希函数将会创建分布不均匀的桶。
• 如果你想要连接被多线程/进程执行
  想要进一步了解，请阅读 DB2, ORACLE or SQL Server 文档.
  https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005311.html
  https://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm#i76330
  https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191426(v=sql.105)

简化示例

我们已经讨论了连接操作的三种类型
现在约定，我们需要连接5张表去得到一个人的全部视图，一个人可以有：
多个手机号
多个邮箱
多个地址
多个银行卡账号

换句话说，我们需要快速回答以下查询：

SELECT * from PERSON, MOBILES, MAILS,ADRESSES, BANK_ACCOUNTS
WHERE
PERSON.PERSON_ID = MOBILES.PERSON_ID
AND PERSON.PERSON_ID = MAILS.PERSON_ID
AND PERSON.PERSON_ID = ADRESSES.PERSON_ID
AND PERSON.PERSON_ID = BANK_ACCOUNTS.PERSON_ID

作为一个查询优化器，我必须找到最佳的方式去处理数据。但是有两个问题。

• 对每种连接来说，我应该选择哪种连接方式？
  我有3个可选择的连接方式（Hash Join, Merge Join, Nested Join），可以使用0～2个索引（这里不关心索引的类型）
• 我应该按照什么样的顺序来计算连接？

比如，下图展示了通过3个连接查询4张表可能的顺序

下面是我可选的方案：

1. 使用直接暴力的方式
   通过数据库统计，我计算每个可行方案花费的代价从而找出最优的一个。但是有很多种可行的方案，对给定顺序的连接，每个连接都有3种可能性： HashJoin, MergeJoin, NestedJoin，所以，总共有34种可能性。连接排序就是二叉树的排序，有（2 * 4）!/（4 + 1）! 可能的订单。
   对非极客来说，可能有27 216种计划。如果我现在考虑到合并连接上建立的0-2个B+树索引，肯能的计划数量将变为 210 000。我刚才就不该说查询非常简单！
2. 我哭着退出任务。这很诱人，但你又得不到结果，我需要钱支付账单。
3. 我只会尝试很少量的计划，采取其中成本最低的计划。
   我又不是超人，我不可能把每一种计划的成本都计算出来。相反，我随意选择所有可能计划的子集。计算他们各自成本并给你一个最优的计划。
4. 我运用智能规则去减少可能计划的数量。
   有两种规则：
   我可以使用“逻辑”规则去除无用的可能计划，它可以过滤少量可能的计划。例如：
   nested loop join中的内部关系一定是最小的那个数据集

  我认可，不去找最优解而是通过运用更多有效规则去减少可能计划的数量。比如：如果关系很小，采用nested loop join，绝不使用任何merge join 或者 a hash join。

  在这个简单的例子中，我最终有很多种可能计划。但是对真正的查询来讲，他还可以有更多的关系运算符如OUTER JOIN，CROSS JOIN，GROUP BY，ORDER BY，PROJECTION，UNION，INTERSECT，DISTINCT …这意味着更多的可能计划。
  那么，数据库是怎么处理的呢？

动态编程，贪心算法和启发式算法

  关系数据库会尝试我刚才说过的多种方法。 优化器的真正工作是在有限的时间内找到一个好的解决方案。
  大多数时间，优化器会找一个好的方案，而不是找最好的方案。
  对于小型查询，可以采用暴力方法。 但是有一种方法可以避免不必要的计算，因此即使是中等查询也可以使用暴力方法。 这就是动态编程。

动态编程

  这个字面背后的原理是很多执行计划都非常相似，看看下面这些执行计划。

  他们有相同的子树（A join B）.所以，我们不需要计算每个计划中这种子树的成本，算一次就够了，保存计算的成本，当下一次遇到时重新使用就好了。更一般的，我们面临着一个重叠问题。为了避免部分结果的额外计算，我们使用memoization技术。
  使用这种技术，我们将时间复杂度从 (2*N)!/(N+1)!降到 3^N。在我们之前有4个连接的例子中，意味着从336种排列降到81种。如果一个带8个连接（不算大）的查询，意味着从 57 657 600排列减少到 6561.
  对于CS极客来说，这是我给你的正式课程中找到的算法。我不会解释这个算法，所以在你了解动态编程或者算法很好时再来阅读它（提醒）：

procedure findbestplan(S)
if (bestplan[S].cost infinite)
   return bestplan[S]
// else bestplan[S] has not been computed earlier, compute it now
if (S contains only 1 relation)
         set bestplan[S].plan and bestplan[S].cost based on the best way
         of accessing S  /* Using selections on S and indices on S */
     else for each non-empty subset S1 of S such that S1 != S
   P1= findbestplan(S1)
   P2= findbestplan(S - S1)
   A = best algorithm for joining results of P1 and P2
   cost = P1.cost + P2.cost + cost of A
   if cost < bestplan[S].cost
       bestplan[S].cost = cost
      bestplan[S].plan = “execute P1.plan; execute P2.plan;
                 join results of P1 and P2 using A”
return bestplan[S]

  对于更大的查询，你仍然可以采用动态编程方法，其使用额外的规则（或启发式）来消除可能性：

• 如果我们只分析明确的一种计划（比如：the left-deep trees），时间复杂度由3ⁿ减少为n*2ⁿ;
  
• 如果我们添加逻辑规则去避免某些模式的计划（比如，如果一个表在给定的谓词上有索引，那么就不要尝试在表上合并连接，而只允许在索引上），那么可以在保证不伤害到太多最优方式的解决方案前提下减少可能性的数量。
• 如果我们在流上添加规则（比如，在所有关系操作之前执行连接操作），也会减少很多可能性。
• …

贪心算法

  但是对于一个非常大的查询或者得到一个非常快速的答案（但不是一个非常快速的查询），使用另一种类型的算法，即贪心的算法。
  我们的想法是遵循规则（或启发式）以增量方式构建查询计划。通过这种规则，贪心算法寻找每一步的最优解。贪心算法启动查询计划是以一个join开始，然后，在每一步，算法按照相同规则将新的join添加至查询计划。
  我们举一个简单的例子。 假设我们在5个表（A，B，C，D和E）上有4个连接的查询。 为简化问题，我们将嵌套连接作为可能的连接。 让我们使用规则“使用最低成本的连接”

• 我们从5个表中任意一个开始（让我们选择A）
• 我们用A计算每个连接的成本（A是内部或外部关系）。
• 我们发现A JOIN B的成本最低。
• 然后，我们用A JOIN B（A JOIN B是内部或外部关系）的结果来计算每个连接的成本。
• 我们发现（A JOIN B）JOIN C成本最低。
• 然后我们用（A JOIN B）JOIN C的结果计算每个连接的成本…
• …
• 最后我们找到了计划（（（A JOIN B）JOIN C）JOIN D）JOIN E）

  由于我们随意以A开头，我们可以为B应用相同的算法，然后是C然后是D然后是E.然后我们保持计划一直是最低成本。
  顺便说一下，这个算法有个名字，叫最近邻算法。
  我不会详细介绍，但是通过良好的建模和N * log（N）的排序，这个问题很容易解决。 该算法的成本为O（N * log（N））VS 完整的动态编程版本为O（3^N）。 如果你有20个连接的大查询，则意味着26对3 486 784 401，这是一个巨大的差距！
  该算法的问题在于，我们假设，在保持连接不变的前提下，添加新的连接时，寻找两个表之间的最佳连接会给出最佳的成本。但是：

• 即使 A JOIN B在A, B 和 C中成本最优
• (A JOIN C) JOIN B 也许会比 (A JOIN B) JOIN C成本更优.

  为了改善结果，你可以使用不同规则运行多个贪心算法保持最优计划。

其他算法

  [如果你已经厌倦了算法，请跳到下一部分，我要说的对于文章的其余部分并不重要]
  寻找最佳可能计划的问题是许多CS研究人员积极研究课题。 他们经常试图为更精确的问题/模式的找到更好的解决方案。 例如，

• 如果查询是星型连接（它是某种类型的多连接查询），则某些数据库将使用特定算法。
• 如果查询是并行查询，则某些数据库将使用特定算法
• …

  研究人员还研究了其他算法来代替大型查询的动态编程。 贪婪算法属于称为启发式算法的较大家族。 贪心算法遵循规则（或启发式），保留在上一步找到的解决方案并将当前步骤找到的解决方案进行追加。 某些算法遵循规则并逐步应用，但并不总是保留上一步中找到的最佳解决方案。 它们被称为启发式算法。
  例如，遗传算法遵循规则，但最后一步的最佳解决方案通常不会保留：

• 解决方案可能代表完整的查询计划
• 每个步骤保持P个解决方案（即计划）而不是单独一个。
  1. P个查询计划是随机创建的
  2. 只保留最佳成本的计划
  3. 这些最佳计划混合起来生成P个新的计划
  4. P新计划中一些是随机修改的
  5. 步骤1,2,3重复T次
  6. 然后你保留最后一个循环的P计划中的最佳计划。

  循环的次数越多，计划会越好。
  这很神奇吗？ 不，这是自然法则：只有最适合的人才能生存！
  仅供参考，遗传算法在PostgreSQL中实现，但我无法找到它们是否默认被使用。
  在数据库中使用了其他启发式算法，如模拟退火，迭代改进，两阶段优化…但我不知道它们目前是否在企业数据库中使用，或者它们是否仅用于研究数据库。

有关更多信息，您可以阅读以下研究文章，该文章介绍了更多可能的算法：数据库查询优化中连接排序问题的算法综述
http://www.acad.bg/rismim/itc/sub/archiv/Paper6_1_2009.PDF

Real optimizers

  [你可以跳到下一部分，这部分我要说的不重要]
  但是，所有这些blabla都是非常理论化的。 由于我是开发人员而不是研究员，我喜欢具体的例子。
  让我们看看SQLite优化器的工作原理。 它是一个轻量级数据库，因此它使用基于贪心算法的简单优化，并使用额外规则来限制可能性的数量：

• SQLite不会对CROSS JOIN 操作中的表重排序
• 连接实现为嵌套连接
• 外连接始终按其出现的顺序进行评估
• …
• 在3.8.0版之前，SQLite在搜索最佳查询计划时使用“最近邻”贪心算法
  等一下…我们已经看过这个算法了！ 真是好巧呀！
• 从版本3.8.0（2015年发布）开始，SQLite在搜索最佳查询计划时使用“N Nearest Neighbors”贪心算法

  让我们看看另一个优化器如何完成他的工作。 IBM DB2就像所有企业数据库一样，我会说这个是因为它是我在切换到大数据之前真正使用的最后一个。
  如果我们查看官方文档，我们了解到DB2优化器允许你使用7种不同级别的优化：

• 对连接使用贪心算法：
  0 - 最低程度优化，使用索引扫描和嵌套循环连接，避免一些查询重写
  1 - 低优化
  2 - 完全优化
• 对连接使用动态编程
  3 - 适度优化和粗略近似
  5 - 完全优化，使用所有技术和启发式
  7 - 完全优化类似于5，没有启发式
  9 - 最大优化备用省力/费用考虑所有可能的连接顺序，包括笛卡尔积

  我们可以看到DB2使用贪心算法和动态编程。 当然，由于查询优化器是数据库的主要功能，因此它们不共享它们使用的启发式方法。
  仅供参考，默认级别为5.默认情况下，优化器使用以下特性：

• 使用所有可用的统计数据，包括频繁值和分位数统计。
• 除了仅在极少数情况下适用的计算密集规则外，所有查询重写规则（包括物化查询表路由）都会被应用。
• 使用动态编程连接枚举，包括：
• 有限使用组合内部关系
• 当涉及到表查找的星型模式时，有限使用笛卡尔积
• 考虑了各种访问方法，包括列表预取（注意：将看看是什么意思），索引AND（注意：带索引的特殊操作）和物化查询表路由。

  默认情况下，DB2使用启发式限制的动态编程来进行连接排序。
  其他条件（GROUP BY，DISTINCT …）由简单规则处理。

Query Plan Cache

  由于计划的创建需要时间，因此大多数数据库将计划存储到查询计划缓存中，以避免对同一查询计划进行无用的重复计算。 这是一个很大的话题，因为数据库需要知道何时更新过时的计划。方法是设置阈值，如果表的统计信息已经更改高于此阈值，则从缓存中清除涉及此表的查询计划。

Query executor

  在这个阶段，我们有一个优化的执行计划。 该计划被编译成可执行代码。 然后，如果有足够的资源（内存，CPU），它将由查询执行程序执行。 计划中的操作（JOIN，SORT BY …）可以按串行或并行方式执行，这取决于执行器。为了获取和写入其数据，查询执行器与数据管理器交互，下一部分会说到。

Data manager

  在此步骤中，查询管理器正在执行查询并需要表和索引中的数据。它向数据管理器获取数据，但有两个问题：

• 关系数据库使用事务模型。 因此，您无法随时获取任何数据，因为其他人可能同时使用/修改数据。
• 数据检索是数据库中最慢的操作，因此数据管理器需要足够智能以获取并将数据保存在内存缓冲区中。

  在这一部分中，我们将看到关系数据库如何处理这两个问题。 我不会谈论数据管理器获取数据的方式，因为它不是最重要的（这篇文章足够长了！）。

Cache manager

  正如我已经说过的，数据库的主要瓶颈是磁盘I / O. 为了提高性能，现代数据库使用缓存管理器。

  查询执行器不是直接从文件系统获取数据，而是向缓存管理器请求数据。 缓存管理器有一个称为缓冲池的内存缓存。 从内存中获取数据可以大大加快数据库的速度。给出一个数量级是很困难的，因为它取决于你需要做的操作：

• 顺序访问（例如：完全扫描）与随机访问（例如：按行ID访问），
• 读与写
  以及数据库使用的磁盘类型：
• 7.2k / 10k / 15k rpm硬盘
• SSD
• RAID 1/5 / …
    但要我说，内存比磁盘快100到100k倍。
    但是，这会导致另一个问题（与数据库一样…）。 缓存管理器需要在查询执行程序使用之前获取内存中的数据; 否则查询管理器必须等待来自慢速磁盘的数据。

预读取

  这个问题被称为预读取。查询执行器知道他需要的数据，因为他知道查询的整个流程并且知道磁盘上的数据和统计信息，下面是思路：

• 当查询执行器正在处理其第一组数据时
• 它要求缓存管理器预加载第二组数据
• 当它开始处理第二组数据时
• 它要求CM（缓存管理器）预先加载第三组并通知CM第一组可以从缓存中清除。
• …

  CM将所有这些数据存储在其缓冲池中。 为了知道数据是否仍被需要，缓存管理器会添加有关缓存数据的额外信息（称为锁存器）。
  有时查询执行器不知道它需要什么数据，而某些数据库不提供此功能。 他们使用推测预取（例如：如果查询执行者需要数据1,3,5，它可能会在不久的将来需要7,9,11）或顺序预取（在这种情况下，CM只需要从磁盘加载所需数据的下一个连续数据）来代替。
  为了监视预读取的工作情况，现代数据库提供了一个称为缓冲区/缓存命中率的度量。 命中率反应数据请求未经磁盘操作直接在缓冲区找到所需数据的频率。

注意：缓存命中率不高并不总是意味着缓存不正常。 有关更多信息，请阅读Oracle文档。
https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA294

  但是，缓冲区是有限的内存量。 因此，它需要删除一些数据才能加载新数据。 加载和清除缓存在磁盘和网络I / O方面有成本。 如果您有经常执行的查询，则始终加载然后清除此查询使用的数据方式将不会有效。 为了解决这个问题，现代数据库使用缓冲区替换策略。

缓冲替换策略

  大多数现代数据库（至少SQL Server，MySQL，Oracle和DB2）使用LRU算法。

LRU

  LRU代表最近最少使用。该算法背后的思路是在缓存中保留最近使用的数据，它们很可能再次被使用。
  下面是一个可视化示例：

  为了便于理解，我假设缓冲区中的数据没有被锁存器锁定（因此可以被删除）。 在这个简单的例子中，缓冲区可以存储3个元素：
1：缓存管理器使用数据1并将数据放入空缓冲区
2：CM使用数据4并将数据放入半载缓冲区
3：CM使用数据3并将数据放入半载缓冲器中
4：CM使用数据9.缓冲区已满，因此数据1被删除，因为它是最近最少使用的数据。 将数据9添加到缓冲区中
5：CM使用数据4.数据4已经在缓冲区中，因此它再次成为第一个最近使用的数据。
6：CM使用数据1.缓冲区已满，因此数据9被删除，因为它是最近使用的最后数据。 将数据1添加到缓冲区中
…
  该算法运行良好，但还是有一些局限性。假使在大表上进行全表扫描怎么办？换句话说，当表和索引的大小超过了缓冲区大小怎么办？使用此算法将删除缓存中的所有先前值，而来自完整扫描的数据可能仅使用一次。

改进

  为防止这种情况发生，某些数据库会添加特定规则。 例如，根据Oracle文档：

For very large tables, the database typically uses a direct path read, which loads blocks directly […], to avoid populating the buffer cache. For medium size tables, the database may use a direct read or a cache read. If it decides to use a cache read, then the database places the blocks at the end of the LRU list to prevent the scan from effectively cleaning out the buffer cache.

  还有其他可能性，例如使用称为LRU-K的高级版LRU。 例如，SQL Server使用LRU-2，K = 2。
  这种算法背后的想法是考虑更多的历史。 使用简单的LRU（K = 1，也是LRU-K），算法仅考虑上次使用数据的时间。使用LRU-K：
  它考虑了K数据使用的最后时间。
  权重是使用数据的次数
  如果将一堆新数据加载到缓存中，则不会删除旧的且经常使用的数据（因为它们的权重更高）。
  但是如果旧数据不再被使用，该算法无法将其保存在缓存中
  因此，不使用数据的数据，权重会随着时间的推移而降低。
  权重的计算成本很高，这就是SQL Server仅使用K = 2的原因。 该值对于可接受的性能开销范围内表现良好。
  有关LRU-K的更深入的知识，你可以阅读原始研究论文（1993）：用于数据库磁盘缓冲的LRU-K页面替换算法。

其他算法

  当然还有其他算法来管理缓存

• 2Q（类似LRU-K的算法）
• CLOCK（类似LRU-K的算法）
• MRU（最近使用的，使用与LRU相同的逻辑，但使用其他规则）
• LRFU（最近最少和经常使用）
• …

  有些数据库允许使用另一种算法而不是默认算法。

Write buffer

  我只谈到了使用数据之前加载数据的读缓冲区。但是在数据库中，您还有写入缓冲区，用于存储数据并成簇将它们刷新到磁盘上，而不是逐个写入数据并产生许多单个磁盘访问。
  请记住，缓冲区存储页（最小的数据单元）而不是行（这是逻辑/人为查看数据的方式）。 如果页面已被修改且未写入磁盘，则缓冲池中的页面将变脏。 有多种算法可以决定在磁盘上写脏页的最佳时间，但它与事务概念高度相关，是本文的下一部分。

Transaction manager

  最后但同样重要的，这部分是关于事务管理器的。 我们将看到此过程如何确保每个查询在其自己的事务中执行。 但在此之前，我们需要了解ACID事务的概念。

Acid

  ACID事务是确保4件事情的工作单元：

• 原子性：事务是“全有或全无”，即使持续10个小时。 如果事务崩溃，则状态返回到事务之前（事务被回滚）。
• 隔离：如果2个事务A和B同时运行，则事务A和B的结果必须相同，无论A在事务B之前/之后/期间结束。
• 持久性：一旦事务被提交（即成功结束），无论发生什么（崩溃或错误），数据都会保留在数据库中。
• 一致性：仅将有效数据（关系约束和功能约束）写入数据库。 一致性与原子性和隔离性有关。
  

  在同一事务期间，您可以运行多个SQL查询来读取，创建，更新和删除数据。 当两个事务使用相同的数据时，混乱开始。 典型的例子是从账户A到账户B的汇款。想象一下，你有2个事务：

• 事务1从账户A获得100美元并将其提供给账户B.
• 事务2从账户A获得50美元并将其提供给账户B.

  如果我们回到ACID属性：
  原子性确保无论T1期间发生什么（服务器崩溃，网络故障…），只要是100美元从A取出而且B没有收到这100美元的这种情况下，事务都不能结束（这种情况是不一致的状态）。
  隔离确保如果T1和T2同时发生，最后A将取150美元，B收到150美元。而不是，例如，A取出150美元而B只收到50美元，这里T2已部分消除了 T1的行为（这种情况也是不一致的状态）。
  如果数据库在T1提交后崩溃，则持久性可确保T1不会消失在空气中。
一致性确保系统中不会创建或销毁任何资金。

[如果你愿意，你可以跳到下一部分，我要说的对于文章的其余部分并不重要]
  许多现代数据库不使用纯隔离作为默认行为，因为它带来了巨大的性能开销。 SQL规范定义了4个级别的隔离：

• Serializable（SQLite中的默认级别）：最高级别的隔离。同时发生的两个事务是100％孤立的。每个事务都有自己的“世界”。
• 可重复读取（MySQL中的默认级别）：除了一种情况外，每个事务都有自己的“世界”。如果事务成功结束并添加新数据，则这些数据将在另一个仍在运行的事务中可见。但是，如果A修改数据并成功结束，则在仍在运行的事务中将不会显示修改。因此，事务之间的这种隔离中断只涉及新数据，而不是现有数据。
  例如，如果事务A执行“TABLE_X中的SELECT count（1）”，然后由事务B在TABLE_X中添加并提交新数据，如果事务A再次执行count（1），则该值将不是相同。
  这称为幻读。
• 读取已提交（Oracle，PostgreSQL和SQL Server中的默认行为）：这是一个可重复的读取+一个新的隔离中断。 如果事务A读取数据D然后由事务B修改（或删除）并提交该数据，则如果A再次读取数据D，则将看到B对数据进行的修改（或删除）。
  这称为不可重复读取。
• 读取未提交：最低级别的隔离。 它是一个读取提交+一个新的隔离中断。 如果事务A读取数据D然后该事务B（未提交但仍在运行）修改该数据D，则如果A再次读取数据D，则它将看到修改的值。 如果事务B被回滚，那么第二次由A读取的数据D没有任何意义，因为它已经被事件B修改过，从未发生过（因为它被回滚）。
  这称为脏读。

  大多数数据库都添加了自己的自定义隔离级别（如PostgreSQL，Oracle和SQL Server使用的快照隔离）。 而且，大多数数据库都没有实现SQL规范的所有级别（尤其是读取未提交级别）
  用户/开发人员可以在连接开始时覆盖默认的隔离级别（只需要添加一行简单的代码）。

并发控制

  真正的问题是对相同数据进行写操作时，如何确保隔离性，一致性和原子性。

• 如果所有事务只是读取数据，则它们可以不修改另一个事务的行为而同时工作。
• 如果（至少）其中一个事务正在修改其他事务读取的数据，则数据库需要找到一种方法来隐藏其他事务的修改。 此外，还需要确保此修改不会被另一个未看到修改数据的事务擦除。

  这个问题叫做并发控制。
  解决此问题的最简单方法是逐个运行每个事务（即串行）。 但这无法扩展，多处理器/核心服务器却只有一个核心处理，效率不高…
  解决这个问题的方法是，每次创建或者取消事务时：

• 监控事务的所有操作
• 检查两个（或更多）事务之间是否因读取/修改相同数据存在冲突
• 对起冲突的事务的内部操作重排序以减少冲突的大小
• 按照某种顺序去执行起冲突的部分（非冲突的事务仍然可以并发执行）
• 考虑到事务可能会被取消

  一般来说，这是一个冲突计划的调度问题。更具体点，这是一个非常困难、对昂贵cpu优化的问题。企业数据库不可能为每个新事物找到最佳调度计划而去等待数小时。因此，他们使用不太理想的方法去解决冲突事务，这会花费更多的时间。

锁管理器

  为解决此问题，大多数数据库都使用锁和（或）数据版本控制。 由于这是一个很大的话题，我将专注于锁定部分，然后我会谈谈数据版本控制。

悲观锁

  这种锁的思想是：

• 如果事务需要数据，
• 它锁定数据
• 如果另一个事务也需要这些数据，
• 它必须等到第一个事务释放数据。

  这称为独占锁
  但是，当事务仅读取数据时，使用独占锁的代价是非常昂贵的。因为它会强制其他读取相同数据的事务等待。这就是为什么还有另外一种类型的锁，即共享锁。
  使用共享锁时：

• 如果一个事务仅需要读取数据A
• 它“共享锁定”数据并读取数据
• 如果第二个事务也需要读取数据A
• 它也“共享锁定”数据并读取数据
• 如果第三个事务需要修改数据A
• 它“独占锁”数据。但它必须等待其他2个事务释放其共享锁然后对数据添加独占锁。

  于此相对的，如果数据加了独占锁，那么其他需要读取数据的事务就必须等待独占锁被释放后才能对数据加共享锁。

  锁管理器是提供和释放锁的进程。在内部，它将锁存在hash表里面（键即为要加锁的数据）并且知道每个数据：

• 哪些事务锁定数据
• 哪些事务正在等待数据

死锁

  但是，使用锁会导致2个事务永远等待数据的情况。

  在这个图中：
  事务A对data1有一个独占锁，正在等待获取data2
  事务B对data2有一个独占锁，正在等待获取data1
  这称为死锁。
  在死锁期间，锁管理器选择要取消（回滚）的事务以删除死锁。 这个决定并不容易：

• 是否杀掉最少数据修改量的事务（因此会产生最小代价的回滚）？
• 是否杀掉最年轻的事务？因为另一个事务的用户已经等待较长时间了。
• 是否杀掉需要更少时间完成的事务(避免进程饥饿)?
• 在回滚的情况下，此次回滚会影响多少个事务？

  在作出选择之前，需要检查是否存在死锁
  哈希表可以看作图形（如前面的图中所示）。 如果图中有一个闭环，则会出现死锁。 由于检查周期很昂贵（因为所有锁的图相当的大），因此经常使用更简单的方法：使用超时。 如果在此超时范围内未加锁成功，则事务将进入死锁状态。
  锁管理器也可以在加锁之前进行检查是否会导致死锁。但同样的，想完美的做到其计算成本很高。因此，这些预检查通常是一组基本规则。

两阶段锁

  确保纯隔离的最简单方式是在事务开始时获取锁并在事务结束时释放锁。 这意味着事务必须在启动之前等待其所有锁，并且在事务结束时释放事务持有的锁。 它可以工作，但它会浪费很多时间来等待所有锁。
   更快的方法是两阶段锁协议（由DB2和SQL Server使用），其中事务分为两个阶段：

• 扩展阶段，事务可以获取锁，但不能释放锁
• 收缩阶段，事务可以释放锁（对已经处理过的数据并不会再次处理），但不能获取锁。

  这两条简单规则背后的思想是：

• 释放不再使用的锁，以减少等待这些锁的其他事务的等待时间
• 防止事务开始后，事务获取的数据被修改，然后与事务最初获取的数据不一致。

  除非修改数据并释放锁的事务被取消（回滚），否则这个协议是有效的。你可能会遇到另外一个事务读取了修改后的值，而这个值被回滚。要避免这个问题，事务结束时需要释放所有的独占锁。

几句话

  当然，真正的数据库使用更复杂的系统，涉及更多类型的锁（如意向锁）和更多粒度（行上，页面上，分区上，表上，表空间上的锁）但是这个思想仍然是共通的。
  我只单纯介绍了基于锁的方法。 数据版本控制是解决此问题的另一种方法。
  版本控制背后的思想是：

• 每个事务可以同时修改相同的数据
• 每个事务都有自己数据的副本（版本）
• 如果两个事务修改相同的数据，只接受其中一个修改，另一个相关的事务会被回滚（并且可能重新运行）

  它提高了性能，因为：

• 读事务不会阻塞写事务
• 写事务不会阻塞读事务
• “胖又慢”的锁管理器没有任何开销

  除了两个事务写同一数据，否则一切都比锁好。而且，你可以快速获得巨大的磁盘空间开销
数据库版本和锁是两种不同的机制。乐观锁与悲观锁。他们都有利有弊，取决于实际情况（读多还是写多）
有关数据版本控制的演示，我推荐这个PostgreSQL如何实现多版本并发控制的。
http://momjian.us/main/writings/pgsql/mvcc.pdf
  一些数据库如DB2（直到DB2 9.7）和SQL Server（快照隔离除外）仅使用锁。 其他像PostgreSQL，MySQL和Oracle混合使用锁和数据版本控制。我不知道只使用数据版本控制的数据库（如果您知道基于纯数据版本的数据库，请随时告诉我）。
  [2015年8月20日更新]读者告诉我：
  Firebird和Interbase使用没有记录锁定的版本控制。
  版本控制对索引有一个有趣的影响：有时一个唯一索引包含重复项，索引可以有比表有行更多的条目，等等。
  如果你正在阅读隔离级别部分，提高隔离级别会增加锁的数量，从而事务等待锁的时间会增加。 这就是大多数数据库默认情况下不使用最高隔离级别（Serializable）的原因。
  通常，你可以亲自在这些主流数据库（例如MySQL，PostgreSQL或Oracle）的文档中确认下。
Mysql：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html
PostgreSQL：http://www.postgresql.org/docs/9.4/static/mvcc.html
Oracle：https://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#CNCPT1336

Log manager

  我们已经看到，为了提高性能，数据库将数据存在内存缓冲区。但是，当事务提交时，服务器崩溃了，崩溃期间，停留在内存中的数据将会丢失。这会破坏事务的持久性。
  你可以向磁盘写入所有内容，但如果服务器崩溃，数据只写了一半，这会破坏事务的原子性。
  事务的任何修改写入都必须是未完成状态或者完成状态。
  要解决这个问题，有两种方法：

• 卷影副本/页面：每个事务都创建自己的数据库副本（或只是数据库的一部分）并在此副本上工作。 如果出现错误，则删除副本。 如果成功，数据库会立即使用文件系统技巧切换副本中的数据，然后删除“旧”数据。
• 事务日志：事务日志是存储空间。 在每次磁盘写入之前，数据库会在事务日志中写入信息，以便在事务崩溃/取消的情况下，数据库知道如何删除（或完成）未完成的事务。

WAL

  卷影副本/页面在涉及许多事务的大型数据库上使用时，会产生巨大的磁盘开销。 这就是现代数据库使用事务日志的原因。 事务日志必须存储在稳定的存储介质中。 我不会深入地介绍存储技术，但必须使用（至少）RAID磁盘来防止磁盘故障。

  大多数数据库（至少Oracle，SQL Server，DB2，PostgreSQL，MySQL和SQLite）使用Write-Ahead Logging协议（WAL）处理事务日志。 WAL协议是一组3条规则：

1. 对数据库的每次修改都会生成一条日志记录，并且必须在将数据写入磁盘之前将日志记录写入事务日志。
2. 日志记录必须按顺序写入; 日志记录A发生在日志记录B之前，则日志记录A必须在日志记录B写入之前写入。
3. 提交事务时，在事务成功结束前，必须向事务日志中添加事务提交命令。

  这个工作由日志管理器完成。 一种简单的方法是在缓存管理器和数据访问管理器（在磁盘上写入数据）之间，日志管理器在将数据写入磁盘之前将每个更新/删除/创建/提交/回滚写入事务日志。 容易，对吗？

  错了！毕竟，在我们阅读了上文之后，你应该知道与数据库相关的所有内容都受到“数据库效应”的诅咒。更严重的是，问题是找到一种高效写入日志的方式。如果事务日志上的写入速度太慢，则会降低全局速度。

ARIES

  1992年，IBM研究人员“发明了”一种名为ARIES的WAL增强版。 大多数现代数据库或多或少都使用ARIES。 逻辑可能不一样，但ARIES背后的概念随处都有被用到。我给“发明”加上引号，是因为根据麻省理工学院的这门课程，IBM研究人员所做的“只不过是编写事务恢复的良好实践”。 ARIES论文发表时，我才5岁，我并不关心来自苦涩研究人员的陈旧八卦。 事实上，我只是在最后技术部分开始前，让你看看这些信息，轻松一下。我已经阅读了大量关于ARIES的研究论文，非常有趣！在这部分中，我将仅向您概述ARIES，但如果您需要真正的知识，我强烈建议您阅读本文。
https://people.eecs.berkeley.edu/~brewer/cs262/Aries.pdf
  ARIES代表算法恢复和隔离利用语义。
  这项技术的目标有两个：
  1）写日志时表现良好
  2）恢复快速可靠
  数据库回滚事务有多方面原因：

• 因为用户取消
• 由于服务器或网络故障
• 因为事务已经破坏了数据库的完整性（例如，事务对具有UNIQUE约束列添加了重复项）
• 因为死锁

  有时（例如，在网络出现故障的情况下），数据库可以恢复事务。
  怎么实现呢？ 要回答这个问题，我们需要了解存储在日志记录中的信息。

The logs

  事务期间的每个操作（添加/删除/修改）都会生成一个日志。 该日志记录包括：

• LSN：唯一的日志序列号。该LSN按时间顺序*给出。这意味着如果操作A发生在操作B之前，则日志A的LSN将低于日志B的LSN。
• TransID：产生操作的事务的id。
• PageID：修改数据的磁盘位置。 磁盘上的最小数据量是一个页面，因此数据的位置是包含数据的页面的位置。
• PrevLSN：指向同一事务生成的先前日志记录的链接。
• UNDO：一种消除操作效果的方法
  例如，如果操作是更新，则UNDO将在更新之前存储更新元素的值/状态（物理UNDO）或反向操作以返回先前状态（逻辑UNDO）**。
• REDO：一种重复操作的方式
  同样，有两种方法可以做到这一点。 你可以在操作或操作本身之后存储元素的值/状态以重放它。
• … :(仅供参考，ARIES日志还有另外两个字段：UndoNxtLSN和Type）。

  此外，磁盘上的每个页面（存储数据，而不是日志）存有修改数据的最后一个操作的日志记录（LSN）的id。
给出LSN的方式更复杂，因为它与日志的存储方式有关。 但这个思想是一致的。
  * ARIES仅使用逻辑UNDO，因为处理物理UNDO真是一团糟。
  注意：据我所知，只有PostgreSQL没有使用UNDO。 它使用垃圾收集器守护程序来删除旧版本的数据。 这与PostgreSQL中数据版本控制的实现有关。
  为了让您更好地了解，这里是查询“UPDATE FROM PERSON SET AGE = 18;”生成的日志记录的可视化和简化示例。 假设此查询在事务18中执行。

  每个日志都有一个唯一的LSN。 属于同一事务的日志链接成一组。 日志按时间顺序链接（链接列表的最后一个日志是最后一个操作的日志）。

Log Buffer

  为避免日志写入成为主要瓶颈，使用日志缓冲区。

  当查询执行器请求修改数据时：
  1）缓存管理器将修改存储在其缓冲区中。
  2）日志管理器将关联的日志存储在其缓冲区中。
  3）在此步骤中，查询执行器认为操作已完成（因此可以要求进行其他修改）
  4）然后（稍后）日志管理器将日志写入事务日志。 何时写日志的决定是由算法完成的。
  5）然后（稍后）缓存管理器将修改写入磁盘。 何时在磁盘上写入数据是由算法完成的。

  当提交事务时，这意味着对于事务中的每个操作，都完成了步骤1,2,3,4,5。 在事务日志中写入很快，因为它只是“在事务日志中的某处添加日志”，而在磁盘上写入数据则更复杂，因为它“以一种快速读取它们的方式写入数据”。

STEAL和FORCE策略

  出于性能原因，步骤5可能在提交后完成，因为在崩溃的情况下，仍然可以使用REDO日志恢复事务。 这称为NO-FORCE策略。
  数据库可以选择FORCE策略（即，必须在提交之前完成步骤5）以在恢复期间降低工作负载。
  另一个问题，是否选择在磁盘上逐步写入数据（STEAL策略），或者缓冲区管理器是否需要等到提交命令一次写入所有内容（NO-STEAL）。 STEAL和NO-STEAL之间的选择取决于您的需求：使用UNDO日志快速写入或快速恢复？
  以下是这些策略对恢复的影响总结：

• STEAL / NO-FORCE需要UNDO和REDO：最高性能，但提供更复杂的日志和恢复过程（如ARIES）。 这是大多数数据库的选择。 注意：我在多篇研究论文和课程中读到了这个结论，但我没有（明确地）在官方文件中找到它。
• STEAL / FORCE只需要UNDO。
• NO-STEAL / NO-FORCE只需要REDO。
• NO-STEAL / FORCE不需要任何东西：性能最差，需要大量的ram。

恢复部分

  好的，所以我们有很好的日志，让我们使用它们！
  假设新实习生已经崩溃了数据库（规则n°1：它始终是实习生的错误）。 您重新启动数据库并开始恢复过程。
  ARIES从崩溃中恢复过来需要三个关卡：

1. Analysis关卡：恢复过程读取完整的事务日志*以重新创建崩溃期间发生的事件的时间线。它确定要回滚的事务（没有提交命令的所有事务都被回滚）以及在崩溃时需要将哪些数据写入磁盘。
2. Redo 关卡：从分析期间确定的日志记录开始，并使用REDO将数据库更新为崩溃前的状态。在REDO阶段，REDO日志按时间顺序处理（使用LSN）。
   对于每个日志，恢复过程将读取包含要修改的数据的磁盘上页面的LSN。
   如果LSN（page_on_disk）> = LSN（log_record），则表示数据已经在崩溃之前写入磁盘（但是该值被日志之后和崩溃之前发生的操作覆盖）所以什么都不用做。
   如果LSN（page_on_disk）<LSN（log_record），则更新磁盘上的页面。
   即使对于要回滚的事务，也会完成重做，因为它简化了恢复过程（但我确信现代数据库不会这样做）。
3. Undo 关卡：回滚所有在崩溃时未完成的事务。回滚从每个事务的最后日志开始，并按时间顺序逆序处理UNDO日志（使用日志记录的PrevLSN）。

  在恢复期间，事务日志必须小心恢复过程所执行的操作，以便写入磁盘的数据和事务记录中的数据同步。一个解决方案可能是删除正在撤销的事务的日志记录，但这非常困难。
  当“手动”取消事务或由锁定管理器取消（停止死锁）或仅仅因为网络故障，则不需要分析关卡。 实际上，有关REDO和UNDO的信息可以在2个内存表中找到：

• 事务表（存储所有当前事务的状态）
• 脏页表（存储需要在磁盘上写入的数据）。

  高速缓存管理器和事务管理器为每个新事务事件更新这些表。 由于它们在内存中，因此在数据库崩溃时会被破坏。
  分析阶段的工作是使用事务日志中的信息在崩溃后重新创建两个表。 *为了加快分析过程，ARIES提供了检查点的概念。 我们的想法是不时地在磁盘上写入事务表和脏页表的内容以及写入时的最后一个LSN，以便在分析过程中，仅分析此LSN之后的日志。

总结

  在写这篇文章之前，我知道这个主题有多大，我知道要写一篇关于数据库的深入文章需要很多时间。 事实证明，有点太乐观，我花了比预期多两倍的时间，但我学到了很多东西。
  如果你想要了解数据库概述，推荐你阅读研究论文“Architecture of a Database System”，它很全面的介绍了数据库（110页），非CS极客也能读懂。这篇论文帮助我找到了本文的思路，它并没有像我的文章那样关注数据结构和算法，而是关注架构概念。
  如果您仔细阅读本文，您现在应该了解数据库的强大功能。 由于这是一篇很长的文章，让我帮你回顾：

• B + Tree索引的概述
• 数据库的全局概述
• 基于成本的优化概述，重点关注连接运算符
• 缓冲池管理概述
• 事务管理概述

但是数据库更智能。 例如，我没有谈到一些棘手的问题，例如：

• 如何管理集群数据库和全局事务
• 如何在数据库仍在运行时拍摄快照
• 如何有效地存储（和压缩）数据
• 如何管理内存

  因此，当你必须在容错低的NoSQL数据库和坚如磐石的关系数据库之间进行选择时，请三思而后行。 不要误会我的意思，一些NoSQL数据库很棒。 但他们还很年轻，并只回答了涉及少数应用的具体问题。
  总而言之，如果有人问你数据库是如何工作的，而不用逃跑，你现在可以回答：

  要么你给他看这篇文章。

---

转载请注明作者和出处