关系数据库如何工作

 

 

 

当涉及到关系数据库时，我不禁会以为有些东西丢失了。它们无处不在。有许多不同的数据库：从小型且有用的SQLite到功能强大的Teradata。但是，只有少数几篇文章解释了数据库的工作方式。您可以自己在Google上搜索“关系数据库的工作原理”，以查看结果很少。而且，这些文章简短。现在，如果您正在寻找最新的技术（大数据，NoSQL或JavaScript），则会发现更多深入的文章来解释它们的工作原理。

关系数据库是否太老太无聊，无法在大学课程，研究论文和书籍之外进行解释？

 

 

作为开发人员，我讨厌使用一些我不了解的东西。而且，如果数据库已经使用了40年，则一定有原因。多年来，我花了数百个小时来真正了解我每天使用的这些奇怪的黑匣子。 关系型数据库 是非常有趣的，因为他们是基于有用的和可重复使用的概念。如果了解数据库很感兴趣，但是您却没有时间或意愿去研究这个广泛的主题，那么您应该喜欢这篇文章。

 

尽管本文的标题是明确的，但本文的目的不是要了解如何使用数据库。因此，您应该已经知道如何编写一个简单的联接查询和基本的CRUD查询。否则，您可能不理解本文。这是您唯一需要了解的内容，我将解释其他所有内容。

我将从一些计算机科学方面的东西开始，例如时间复杂度。我知道有些人讨厌这个概念，但是没有它，您将无法理解数据库内部的聪明之处。由于这是一个巨大的主题，因此我将重点介绍我认为必不可少的内容：数据库处理SQL查询的方式。我将仅介绍数据库背后的基本概念，以便在本文结尾时，您将对幕后发生的事情有一个很好的了解。

 

由于这是一篇冗长而技术性的文章，涉及许多算法和数据结构，因此请花一些时间阅读它。有些概念更难以理解。您可以跳过它们，但仍然可以了解总体思路。

对于您来说，知识更多些，本文大致分为3部分：

• 低级和高级数据库组件概述
• 查询优化过程概述
• 事务和缓冲池管理概述

内容

 

回归本源

很久以前（在一个遥远的星系中……），开发人员必须确切地知道他们正在编码的操作数。他们完全知道自己的算法和数据结构，因为他们负担不起浪费速度较慢的计算机的CPU和内存。

在这一部分中，我将提醒您一些这样的概念，因为它们对于理解数据库是必不可少的。我还将介绍数据库索引的概念。

 

O（1）对O（n 2）

如今，许多开发人员都不在乎时间的复杂性……他们是对的！

但是，当您处理大量数据（我不是在谈论数千个数据）时，或者如果您要争夺毫秒级的时间，那么了解此概念就变得至关重要。猜猜是什么，数据库必须处理两种情况！我不会为您烦得很久，只是让您明白这个主意的时间。这将有助于我们以后理解基于成本的优化的概念 。

 

这个概念

的时间复杂度是用来看看的算法需要多长时间为给定的数据量。为了描述这种复杂性，计算机科学家使用了数学上的大O符号。该符号与描述了算法针对给定数量的输入数据需要进行多少次操作的函数一起使用。

例如，当我说“此算法在O（some_function（））中”时，它意味着对于一定数量的数据，该算法需要some_function（a_certain_amount_of_data）操作才能完成其工作。

重要的不是数据量，而是当数据量增加时操作数增加的方式。时间复杂度并不能给出确切的操作数量，而是一个好主意。

在此图中，您可以看到不同类型的复杂性的演变。我用对数标度绘制它。换句话说，数据数量正迅速从1亿增加到10亿。我们可以看到：

• 在O（1）或恒定的复杂性保持不变（否则就不叫常复杂）。
• 即使有数十亿个数据，O（log（n））仍保持较低水平。
• 复杂度最差的是O（n 2），其中操作数量迅速激增。
• 这两个OT ^ h铒共米p乐喜牛逼我ES正在迅速增加。

 

例子

数据量少时，O（1）和O（n 2）之差可以忽略不计。例如，假设您有一个需要处理2000个元素的算法。

• O（1）算法将花费您1次操作
• O（log（n））算法将花费您7次操作
• O（n）算法将花费您2000次操作
• O（n * log（n））算法将花费您14000次操作
• O（n 2）算法将花费您4000000次操作

O（1）和O（n 2）之间的差异似乎很大（400万），但是您最多会在2毫秒内迷失方向，只是眨眼的时间。实际上，当前的处理器每秒可以处理数亿个操作。这就是为什么性能和优化在许多IT项目中都不是问题的原因。

 

正如我所说，面对大量数据时，了解这一概念仍然很重要。如果这一次该算法需要处理1 000 000个元素（对于数据库来说这不是那么大）：

• O（1）算法将花费您1次操作
• O（log（n））算法将花费您14次操作
• O（n）算法将花费您1000000次操作
• O（n * log（n））算法将花费您14000000次操作
• O（n 2）算法将花费您1 000000000000000次操作

我没有做数学运算，但是我想用O（n 2）算法，您有时间喝咖啡（甚至可以喝一杯！）。如果您在数据量上再加上0，那么您将有时间进行小睡。

 

更深入

给您一个想法：

• 在良好的哈希表中进行搜索可得出O（1）中的一个元素
• 在平衡良好的树中进行搜索得到的结果为O（log（n））
• 数组中的搜索结果为O（n）
• 最佳排序算法的复杂度为O（n * log（n））。
• 不良的排序算法具有O（n 2）复杂度

注意：在下一部分中，我们将看到这些算法和数据结构。

 

时间复杂度有多种类型：

• 平均情况
• 最好的情况
• 最坏的情况

时间复杂度通常是最坏的情况。

我只谈论时间复杂性，但是复杂性也适用于：

• 算法的内存消耗
• 算法的磁盘I / O消耗

 

当然，复杂度比n 2还差，例如：

• n 4：太烂了！我将提到的某些算法具有这种复杂性。
• 3 n：那更糟！我们将在本文中看到的一种算法具有这种复杂性（并且它确实在许多数据库中得到了使用）。
• 阶乘n：即使数据量很少，也永远不会得到结果。
• n n：如果您最终遇到这种复杂性，您应该问问自己，IT是否真的是您的领域……

 

注意：我没有给您大O表示法的真实定义，而是给您一个想法。您可以在Wikipedia上阅读此文章的真实（渐近）定义。

 

合并排序

当您需要对集合进行排序时，您会怎么做？什么？您可以调用sort（）函数…好的，很好的答案…但是对于数据库，您必须了解sort（）函数的工作方式。

有几种不错的排序算法，因此我将重点介绍最重要的一种：合并排序。您可能现在不明白为什么排序数据很有用，但是您应该在查询优化部分之后进行。此外，了解合并排序将有助于我们稍后理解称为合并联接的常见数据库联接操作。

 

合并

像许多有用的算法一样，合并排序基于一个技巧：将2个大小为N / 2的排序数组合并为N个元素的排序数组仅需要N次操作。此操作称为合并。

让我们看一个简单的例子，这意味着什么：

您可以在该图上看到，要构造最终的8个元素的排序数组，只需要在2个4元素数组中进行一次迭代即可。由于两个4元素数组均已排序：

• 1）比较两个数组中的两个当前元素（current =第一次为第一次）
• 2）然后将最低的一个放入8个元素的数组中
• 3）并转到数组中的下一个元素，您选择了最低的元素
• 并重复1,2,3，直到到达数组之一的最后一个元素。
• 然后，将另一个数组的其余元素放入8元素数组中。

这是可行的，因为两个4元素数组都已排序，因此您无需在这些数组中“返回”。

 

现在我们已经了解了这个技巧，这是我的归类排序的伪代码。

<span style="color:#333333">array mergeSort(array a)
   if(length(a)==1)
      return a[0];
   end if

   //recursive calls
   [left_array right_array] := split_into_2_equally_sized_arrays(a);
   array new_left_array := mergeSort(left_array);
   array new_right_array := mergeSort(right_array);

   //merging the 2 small ordered arrays into a big one
   array result := merge(new_left_array,new_right_array);
   return result;
</span>

合并排序将问题分解为较小的问题，然后找到较小问题的结果以得到初始问题的结果（注意：这种算法称为分治法）。如果您不了解此算法，请不要担心。我第一次看到它是不明白的。如果可以帮到您，我将此算法视为两阶段算法：

• 分割阶段，将阵列分为较小的阵列
• 将小数组放在一起（使用合并）以形成更大数组的排序阶段。

 

分割阶段

在除法阶段，使用3个步骤将阵列分为单一阵列。正式的步骤数为log（N）（因为N = 8，log（N）= 3）。

我怎么知道

我是天才！一言以蔽之：数学。这个想法是，每个步骤都将初始数组的大小除以2。步骤数是可以将初始数组除以2的次数。这是对数的精确定义（以2为底）。

 

分选阶段

在排序阶段，从单一数组开始。在每个步骤中，您将应用多个合并，并且总成本为N = 8次操作：

• 第一步，您有4个合并，每个合并需要2个操作
• 在第二步中，您有2个合并，每个合并花费4个操作
• 在第三步中，您有1个合并需要8次操作

由于存在log（N）个步骤，因此总成本为N * log（N）个操作。

 

合并排序的力量

为什么此算法如此强大？

因为：

• 您可以修改它，以减少内存占用，但不创建新数组，而是直接修改输入数组。

注意：这种算法称为就地。

• 您可以对其进行修改，以便同时使用磁盘空间和少量内存，而不会产生巨大的磁盘I / O损失。这个想法是只将当前正在处理的零件加载到内存中。当您需要对仅具有100 MB内存缓冲区的多GB表进行排序时，这一点很重要。

注意：这种算法称为外部排序。

• 您可以修改它以在多个进程/线程/服务器上运行。

例如，分布式合并排序是Hadoop（这是大数据中的THE框架）的关键组件之一。

• 该算法可以将铅变成金（真实的事实！）。

 

这种排序算法已在大多数（如果不是全部）数据库中使用，但并不是唯一的一种。如果您想了解更多信息，可以阅读这份研究论文，其中讨论了数据库中常见排序算法的优缺点。

 

数组，树和哈希表

既然我们了解了时间复杂度和排序背后的思想，那么我必须向您介绍3种数据结构。这很重要，因为它们是现代数据库的骨干。我还将介绍数据库索引的概念。

 

大批

二维数组是最简单的数据结构。一个表可以看作是一个数组。例如：

此二维数组是具有行和列的表：

• 每行代表一个主题
• 这些列描述主题的功能。
• 每列存储某种类型的数据（整数，字符串，日期…）。

尽管存储和可视化数据很棒，但是当您需要查找特定值时，它很糟糕。

例如，如果要查找在英国工作的所有人员，则必须查看每一行以查找该行是否属于英国。这将花费您N次操作（N是行数），这不错，但是有没有更快的方法呢？这是树木发挥作用的地方。

 

注意：大多数现代数据库都提供高级阵列来有效地存储表，例如堆组织表或索引组织表。但这并没有改变在一组列上快速搜索特定条件的问题。

 

树和数据库索引

二进制搜索树是具有特殊属性的二进制树，每个节点中的关键字必须为：

• 大于存储在左侧子树中的所有键
• 小于存储在右侧子树中的所有键

 

让我们看一下视觉上的含义

这个主意

 

这棵树有N = 15个元素。假设我要寻找208：

• 我从键为136的根开始。由于136 <208，所以我看节点136的右子树。
• 398> 208所以，我看节点398的左子树
• 250> 208因此，我看一下节点250的左子树
• 200 <208因此，我看节点200的右子树。但是200没有右子树，该值不存在（因为如果确实存在，它将在200的右子树中）

现在假设我正在寻找40

• 我从键为136的根开始。由于136> 40，所以我看节点136的左子树。
• 80> 40所以，我看节点80的左子树
• 40 = 40，该节点存在。我提取节点内行的ID（图中未显示），并查看表中给定的行ID。
• 知道行ID后，我便知道数据在表中的确切位置，因此我可以立即获取它。

最后，两次搜索使我损失了树内的层数。如果您仔细阅读合并排序中的部分，您应该会看到存在log（N）级别。因此搜索的成本为log（N），还不错！

 

回到我们的问题

但是，这些内容非常抽象，让我们回到我们的问题上来。想象一个代表上表中某人所在国家的字符串，而不是一个愚蠢的整数。假设您有一棵包含表的“国家”列的树：

• 如果您想知道谁在英国工作
• 你看树得到代表英国的节点
• 在“英国节点”内，您将找到英国工人行的位置。

如果您直接使用数组，则此搜索仅花费您log（N）个操作，而不是N个操作。您刚刚想象的是一个数据库索引。

您可以为任何一组列（一个字符串，一个整数，2个字符串，一个整数和一个字符串，一个日期……）构建树索引，只要您具有比较键（即列组）的功能即可，您可以在键之间建立顺序 （数据库中的任何基本类型都是这种情况）。

 

B +树索引

尽管此树可以很好地获取特定值，但是当您需要在两个值之间获取多个元素 时，仍然存在BIG问题。这将花费O（N），因为您必须查看树中的每个节点，并检查它是否在这两个值之间（例如，按顺序遍历树）。此外，此操作不是磁盘I / O友好的，因为您必须阅读完整的树。我们需要找到一种有效地进行范围查询的方法。为了解决此问题，现代数据库使用了以前的树（称为B + Tree）的修改版本。在B +树中：

• 只有最低的节点（叶子）存储信息（相关表中行的位置）
• 其他节点只是在搜索过程中路由到正确的节点。

如您所见，有更多的节点（更多的节点）。实际上，您还有其他节点，即“决策节点”，可以帮助您找到合适的节点（该节点将行的位置存储在关联表中）。但是搜索的复杂度仍然在O（log（N））中（只有一个级别）。最大的区别是最低的节点链接到其后继节点。

使用此B + Tree，如果要查找40到100之间的值：

• 您只需要像前一棵树一样查找40（如果不存在40，则查找40之后的最接近值）。
• 然后使用到后继者的直接链接收集40个后继者，直到达到100。

假设您找到了M个后继节点，并且树上有N个节点。像上一棵树一样，搜索特定节点的成本为log（N）。但是，一旦有了该节点，就可以在M个操作中获得M个后继者，并带有指向其后继者的链接。该搜索仅花费M + log（N）个操作，而前一个树则花费N个操作。此外，您不需要读取完整的树（仅需M + log（N）个节点），这意味着磁盘使用量更少。如果M低（例如200行）而N大（1 000万行），则会产生很大的差异。

 

但是又有新的问题！如果您在数据库中（因此在关联的B + Tree索引中）添加或删除行：

• 您必须保持B + Tree内部节点之间的顺序，否则您将无法在混乱中找到节点。
• 您必须在B + Tree中保持尽可能少的级别数，否则O（log（N））中的时间复杂度将变为O（N）。

换句话说，B +树需要自我排序和自我平衡。幸运的是，这可以通过智能删除和插入操作实现。但这要付出代价：B +树中的插入和删除在O（log（N））中。这就是为什么有些人听说使用太多索引不是一个好主意的原因。确实，您正在减慢表中行的快速插入/更新/删除的速度，因为数据库需要使用每个索引的昂贵O（log（N））操作来更新表的索引。而且，添加索引意味着事务管理器会增加工作量（我们将在本文结尾看到该管理器）。

有关更多详细信息，您可以查看有关B + Tree的Wikipedia文章。如果要在数据库中实现B + Tree实现的示例，请参阅MySQL的核心开发人员提供的这篇文章和这篇文章。他们俩都专注于innoDB（MySQL的引擎）如何处理索引。

注意：一位读者告诉我，由于底层优化，B +树需要完全平衡。

 

哈希表

我们最后一个重要的数据结构是哈希表。当您想快速寻找价值时，这非常有用。此外，了解哈希表将有助于我们稍后理解称为哈希联接的常见数据库联接操作。数据库还使用此数据结构来存储一些内部内容（例如锁表或缓冲池，稍后将介绍这两个概念）

哈希表是一种数据结构，可快速找到具有其键的元素。要构建哈希表，您需要定义：

• 您元素的关键
• 键的哈希函数。计算得出的键的哈希值给出了元素（称为buckets）的位置。
• 比较键的功能。找到正确的存储桶后，您必须使用此比较在存储桶中查找要查找的元素。

 

一个简单的例子

让我们看一个直观的例子：

该哈希表有10个存储桶。由于我很懒，所以我只提了5个水桶，但我知道你很聪明，所以我让你想象另外5个水桶。我使用的哈希函数是密钥的模10。换句话说，我只保留元素键的最后一位来查找其存储桶：

• 如果最后一位数字为0，则该元素最终出现在存储区0中，
• 如果最后一位数字为1，则该元素最终出现在存储桶1中，
• 如果最后一位数字为2，则该元素最终出现在存储桶2中，
• …

我使用的比较函数只是2个整数之间的相等。

假设您要获取元素78：

• 哈希表计算78的哈希码，即8。
• 它在存储桶8中查找，找到的第一个元素是78。
• 它给您元素78
• 的 搜索成本只有2个操作（1计算散列值，而另一个用于求出铲斗内的元件）。

现在，假设您要获取元素59：

• 哈希表计算59的哈希码，即9。
• 它在存储桶9中查找，找到的第一个元素是99。由于99！= 59，因此元素99不是正确的元素。
• 使用相同的逻辑，它查看第二个元素（9），第三个元素（79），…和最后一个元素（29）。
• 元素不存在。
• 搜索需要进行7次操作。

 

良好的哈希函数

如您所见，根据您所寻找的价值，成本是不一样的！

如果现在我用键的模数1 000 000来更改哈希函数（即取最后6位数字），则第二次搜索仅花费1次操作，因为存储桶000059中没有元素。真正的挑战是找到一个好的散列函数，该函数将创建包含少量元素的存储桶。

在我的示例中，找到一个好的哈希函数很容易。但这是一个简单的示例，当键为：

• 字符串（例如某个人的姓氏）
• 2个字符串（例如，一个人的姓氏和名字）
• 2个字符串和一个日期（例如，一个人的姓，名和出生日期）
• …

有了良好的哈希函数， 哈希表中的搜索就在O（1）中。

 

数组与哈希表

为什么不使用数组？

哼，你问的是一个好问题。

• 哈希表可以一半加载到内存中，而其他存储桶可以保留在磁盘上。
• 对于数组，您必须在内存中使用连续的空间。如果要加载大表，则很难有足够的连续空间。
• 使用哈希表，您可以选择所需的键（例如国家/地区和一个人的姓氏）。

有关更多信息，您可以阅读我有关Java HashMap的文章，它是一种有效的哈希表实现；请参见参考资料。您无需了解Java就可以了解本文中的概念。

 

全球概况

我们刚刚看到了数据库内部的基本组件。现在，我们需要退后一步以查看全局。

数据库是可以轻松访问和修改的信息的集合。但是一堆简单的文件也可以做到这一点。实际上，最简单的数据库（如SQLite）仅是一堆文件。但是SQLite是一堆精心设计的文件，因为它允许您执行以下操作：

• 使用确保数据安全和连贯的交易
• 即使您正在处理数百万个数据，也可以快速处理数据

 

更一般地，数据库可以如下图所示：

在撰写本部分之前，我已经阅读了多本书/论文，每种资料都有代表数据库的方法。因此，不要过多地关注如何组织此数据库或如何命名流程，因为我做出了一些选择以适合本文的计划。重要的是不同的组成部分；总体思路是将数据库分为相互交互的多个组件。

核心组件：

• 进程管理器：许多数据库都有一个需要管理的进程/线程池。而且，为了获得纳秒级的性能，某些现代数据库使用其自己的线程而不是操作系统线程。
• 网络管理员：网络I / O是一个大问题，尤其是对于分布式数据库。这就是为什么某些数据库拥有自己的管理器的原因。
• 文件系统管理器：磁盘I / O是数据库的第一个瓶颈。重要的是拥有一个能够完美处理操作系统文件系统甚至替换它的管理器。
• 内存管理器：为避免磁盘I / O损失，需要大量的内存。但是，如果您处理大量内存，则需要高效的内存管理器。尤其是当您有多个查询同时使用内存时。
• 安全管理器：用于管理用户的身份验证和授权
• 客户经理：用于管理客户连接
• …

工具：

• 备份管理器：用于保存和还原数据库。
• Recovery Manager：用于在崩溃后以一致的状态重新启动数据库
• 监视管理器：用于记录数据库的活动并提供监视数据库的工具
• 管理管理器：用于存储元数据（如表的名称和结构），并提供工具来管理数据库，模式，表空间，…
• …

查询管理器：

• 查询解析器：检查查询是否有效
• 查询重写器：预优化查询
• 查询优化器：优化查询
• 查询执行器：编译并执行查询

数据管理器：

• 交易经理：处理交易
• 缓存管理器：在使用数据之前先将其放入内存中，然后在将其写入磁盘之前先将其放入内存中
• 数据访问管理器：访问磁盘上的数据

 

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

• 客户经理
• 查询管理器
• 数据管理器（在这一部分中，我还将包括恢复管理器）

 

客户经理

客户经理是处理与客户通信的部分。客户端可以是（Web）服务器或最终用户/最终应用程序。客户端管理器提供了通过一组著名的API访问数据库的不同方法：JDBC，ODBC，OLE-DB…

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

 

当您连接到数据库时：

• 管理员首先检查您的身份验证（您的登录名和密码），然后检查您是否具有使用该数据库的授权。这些访问权限由您的DBA设置。
• 然后，它检查是否有一个进程（或线程）可用于管理您的查询。
• 它还会检查数据库是否不处于高负荷状态。
• 它可以等待片刻以获取所需的资源。如果此等待达到超时，它将关闭连接并给出可读的错误消息。
• 然后它将您的查询发送到查询管理器并处理您的查询
• 由于查询处理不是“全有或全无”的事情，因此，一旦它从查询管理器获取数据，它将部分结果存储 在缓冲区中并开始将其发送给您。
• 出现问题时，它将停止连接，为您提供易于阅读的解释并释放资源。

 

查询管理器

 

这是数据库功能所在的地方。在这一部分中，将写得不好的查询转换为快速的可执行代码。然后执行代码，并将结果返回给客户管理器。这是一个多步骤操作：

• 首先解析查询以查看其是否有效
• 然后将其重写以删除无用的操作并添加一些预优化
• 然后对其进行优化以提高性能，并将其转变为执行和数据访问计划。
• 然后编制计划
• 最后执行了

在这一部分中，我不会谈论最后两点，因为它们不太重要。

 

阅读此部分后，如果您想更好地理解，我建议阅读：

• 有关基于成本的优化的初步研究论文（1979年）：关系数据库管理系统中的访问路径选择。本文只有12页，可以理解，具有计算机科学的平均水平。
• 关于DB2 9.X如何优化查询的非常出色且深入的演示，请参见此处。
• PostgreSQL的如何优化一个很好的演示查询这里。这是最易于访问的文档，因为它比“让我们看看PostgreSQL使用的算法”更多地是关于“让我们看看PostgreSQL在这些情况下的查询计划”。
• 有关优化的官方SQLite文档。它很容易阅读，因为SQLite使用简单的规则。而且，它是唯一真正说明其工作原理的官方文档。
• 如何SQL Server 2005中优化查询好介绍这里
• 此处有关Oracle 12c中优化的白皮书
• 从书“的作者查询优化2门理论课程数据库系统概念”这里和T在这里。重点放在磁盘I / O成本方面的读物不错，但在CS方面也要有一个良好的水平。
• 我发现另一门理论课程更容易访问，但仅侧重于联接运算符和磁盘I / O。

 

查询解析器

每个SQL语句都发送到解析器，在该处检查语法是否正确。如果您在查询中输入错误，则解析器将拒绝该查询。例如，如果您写的是“ SLECT…”而不是“ SELECT…”，那么故事到此结束。

但这更深了。它还会检查关键字的使用顺序是否正确。例如，SELECT之前的WHERE将被拒绝。

然后，分析查询中的表和字段。解析器使用数据库的元数据来检查：

• 如果这些表存在
• 如果表的字段存在
• 如果可以对字段类型进行操作（例如，您不能将整数与字符串进行比较，则不能对整数使用substring（）函数）

 

然后，它检查您是否具有读取（或写入）查询中的表的权限。同样，这些对表的访问权限由您的DBA设置。

在此解析期间，SQL查询将转换为内部表示形式（通常为树）

如果一切正常，则将内部表示形式发送到查询重写器。

 

查询重写器

在此步骤中，我们具有查询的内部表示。重写器的目的是：

• 预优化查询
• 避免不必要的操作
• 帮助优化器找到最佳解决方案

 

重写器在查询中执行已知规则的列表。如果查询符合规则的模式，则将应用规则并重写查询。以下是（可选）规则的详尽列表：

• 视图合并：如果您在查询中使用视图，则将使用该视图的SQL代码转换该视图。
• 子查询展平：很难优化子查询，因此重写器将尝试使用子查询修改查询以删除子查询。

例如

<span style="color:#333333">SELECT PERSON.*
FROM PERSON
WHERE PERSON.person_key IN
(SELECT MAILS.person_key
FROM MAILS
WHERE MAILS.mail LIKE 'christophe%');
</span>

将被替换

<span style="color:#333333">SELECT PERSON.*
FROM PERSON, MAILS
WHERE PERSON.person_key = MAILS.person_key
and MAILS.mail LIKE 'christophe%';
</span>

• 删除不必要的运算符：例如，如果使用DISTINCT却有一个UNIQUE约束来防止数据不唯一，则DISTINCT关键字将被删除。
• 冗余联接消除：如果由于一个联接条件被隐藏在视图中而具有两次相同的联接条件，或者由于传递性而存在无用的联接，则将其删除。
• 恒定算术评估：如果编写需要演算的内容，则在重写过程中将对其进行一次计算。例如，将WHERE AGE> 10 + 2转换为WHERE AGE> 12，然后将TODATE（“ some date”）转换为datetime格式的日期
• （高级）分区修剪：如果您使用的是分区表，重写器将能够找到要使用的分区。
• （高级）实例化视图重写：如果您具有与查询中的谓词子集匹配的实例化视图，则重写器将检查该视图是否为最新视图，并修改查询以使用实例化视图而不是原始表。
• （高级）自定义规则：如果您具有用于修改查询的自定义规则（例如Oracle策略），则重写器将执行这些规则
• （高级）Olap转换：分析/窗口函数，星型连接，汇总……也都进行了转换（但是我不确定是由重写器还是优化器完成的，因为这两个过程都非常接近，它必须取决于数据库）。

 

然后，这个重写的查询将发送到查询优化器，从那里开始乐趣！

 

统计数据

在我们看到数据库如何优化查询之前，我们需要先谈谈统计信息，因为没有 统计信息，数据库是愚蠢的。如果您不告诉数据库分析自己的数据，它将不会这样做，并且会做出（非常）错误的假设。

但是数据库需要什么样的信息？

我不得不（简短地）谈论数据库和操作系统如何存储数据。他们利用所谓的最小单位一个 页面或块（4或8千字节默认情况下）。这意味着，如果您只需要1 KB，则将花费您一页。如果页面占用8 KB，那么您将浪费7 KB。

 

回到统计！当您要求数据库收集统计信息时，它计算的值如下：

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

这些统计信息将帮助优化器估计查询的 磁盘I / O，CPU和内存使用率。

每列的统计信息非常重要。例如，如果需要在2列上联接表PERSON：LAST_NAME，FIRST_NAME。根据统计信息，数据库知道FIRST_NAME上只有1000个不同的值，而LAST_NAME上只有1000万个不同的值。因此，数据库将联接LAST_NAME，FIRST_NAME而不是FIRST_NAME，LAST_NAME上的数据，因为它产生的方式比较少，因为LAST_NAME不太可能相同，因此大多数情况下，比较数据库的第2个（或3个）首字符LAST_NAME就足够了。

 

但是这些是基本统计数据。您可以要求数据库计算称为直方图的高级统计信息。直方图是统计信息，用于通知列中值的分布。例如

• 最频繁的值
• 分位数
• …

这些额外的统计信息将帮助数据库找到更好的查询计划。特别是对于相等谓词（例如：WHERE AGE = 18）或范围谓词（例如：WHERE AGE> 10和AGE <40），因为数据库会对这些谓词所涉及的行数有更好的了解（请注意：这个概念就是选择性）。

 

统计信息存储在数据库的元数据中。例如，您可以查看（未分区的）表的统计信息：

• 适用于Oracle的USER / ALL / DBA_TABLES和USER / ALL / DBA_TAB_COLUMNS中
• 在SYSCAT中。用于DB2的表和SYSCAT.COLUMNS。

 

该统计数据必须是最新的。没有什么比数据库认为表只有500行而表有1 000 000行更糟糕的了。统计信息的唯一缺点是计算它们需要时间。这就是为什么在大多数数据库中默认情况下不会自动计算它们的原因。数以百万计的数据很难对其进行计算。在这种情况下，您可以选择仅计算基本统计信息或计算数据库样本的统计信息。

例如，当我在一个项目中处理每个表中的亿万行时，我选择只计算10％的统计信息，从而节省了大量时间。对于这个故事，事实证明这是一个错误的决定，因为Oracle 10G偶尔为特定表的特定列选择的10％与总的100％完全不同（对于具有100M行的表，这种情况不太可能发生） 。错误的统计信息导致查询有时需要8个小时而不是30秒；寻找根本原因的噩梦。此示例说明了统计数据的重要性。

 

注意：当然，每个数据库都有特定的高级统计信息。如果您想了解更多信息，请阅读数据库的文档。话虽如此，我试图理解如何使用统计数据，而我发现的最佳官方文档是PostgreSQL的官方文档。

 

查询优化器

所有现代数据库都使用基于成本的优化（CBO）来优化查询。这样做的目的是为每次操作增加成本，并通过使用最便宜的操作链来获取结果，从而找到降低查询成本的最佳方法。

 

为了理解成本优化器的工作原理，我认为最好有一个示例来“感觉”该任务背后的复杂性。在这一部分中，我将向您介绍联接2个表的3种常见方法，我们将很快看到，即使是简单的联接查询也是要优化的噩梦。之后，我们将看到真正的优化器如何完成这项工作。

 

对于这些连接，我会专注于自己的时间复杂度，但一个 数据库优化计算的CPU成本，磁盘I / O成本和存储空间需求。时间复杂度和CPU成本之间的区别在于，时间成本非常近似（这适用于像我这样的懒人）。对于CPU成本，我应该将每个操作都算作加法，“ if语句”，乘法，迭代…等等。此外：

• 每个高级代码操作都有特定数量的低级CPU操作。
• 无论您使用的是Intel Core i7，Intel Pentium 4，AMD Opteron…，CPU操作的成本（在CPU周期方面）都不相同。换句话说，它取决于CPU体系结构。

 

使用时间复杂度更容易（至少对我而言），并且有了它，我们仍然可以得到CBO的概念。有时我会谈论磁盘I / O，因为它是一个重要的概念。请记住，瓶颈通常是磁盘I / O而不是CPU使用率。

 

指标

当我们看到B + Trees时，我们谈到了索引。请记住，这些索引已经排序。

仅供参考，还有其他类型的索引，例如位图索引。在CPU，磁盘I / O和内存方面，它们提供的成本与B + Tree索引所提供的成本不一样。

此外，如果可以提高执行计划的成本，许多现代数据库可以仅为当前查询动态创建临时索引。

 

存取路径

在应用联接运算符之前，您首先需要获取数据。这是获取数据的方法。

注意：由于所有访问路径的真正问题是磁盘I / O，因此我不会谈论太多时间复杂性。

 

全面扫描

如果您曾经阅读过执行计划，那么您肯定已经看到“完全扫描”（或仅扫描）一词。完全扫描只是数据库完全读取一个表或一个索引。就磁盘I / O而言，表完全扫描显然比索引完全扫描更昂贵。

 

范围扫描

还有其他类型的扫描，例如索引范围扫描。例如，当您使用“ WHERE AGE> 20 AND AGE <40”这样的谓词时，将使用它。

当然，您需要在AGE字段上有一个索引才能使用此索引范围扫描。

我们已经在第一部分中看到范围查询的时间成本类似于log（N）+ M，其中N是此索引中的数据数，而M是对该范围内的行数的估计。由于统计信息，N和M值都已知（注意：M是谓词AGE> 20 AND AGE <40的选择性）。而且，对于范围扫描，您不需要读取完整索引，因此就磁盘I / O而言，它比完整扫描便宜。

 

独特扫描

如果您只需要索引中的一个值，则可以使用唯一扫描。

 

按行ID访问

在大多数情况下，如果数据库使用索引，则它将不得不查找与索引关联的行。为此，它将使用按行ID进行的访问。

 

例如，如果您执行类似

<span style="color:#333333">SELECT LASTNAME, FIRSTNAME from PERSON WHERE AGE = 28
</span>

如果您有一个列年龄的人的索引，那么优化器将使用该索引查找所有28岁的人，然后它会询问表中的关联行，因为索引仅包含有关年龄的信息，并且您想知道姓氏和名字。

 

但是，如果现在你做类似的事情

<span style="color:#333333">SELECT TYPE_PERSON.CATEGORY from PERSON ,TYPE_PERSON
WHERE PERSON.AGE = TYPE_PERSON.AGE
</span>

PERSON上的索引将用于与TYPE_PERSON联接，但是行ID将无法访问表PERSON，因为您没有在该表上询问信息。

尽管它对于某些访问非常有效，但此操作的真正问题是磁盘I / O。如果您需要通过行ID进行太多访问，则数据库可能会选择完全扫描。

 

其他路径

我没有介绍所有的访问路径。如果您想了解更多信息，可以阅读Oracle文档。其他数据库的名称可能不同，但是后面的概念是相同的。

 

加盟运营商

因此，我们知道如何获取我们的数据，让我们加入他们！

我将介绍3个常见的联接运算符：合并联接，哈希联接和嵌套循环联接。但是在此之前，我需要介绍新的词汇表：内部关系和外部关系。关系可以是：

• 一张桌子
• 索引
• 先前操作的中间结果（例如先前联接的结果）

当您连接两个关系时，连接算法对两个关系的管理方式不同。在本文的其余部分，我将假定：

• 外部关系是左数据集
• 内部关系是正确的数据集

例如，A JOIN B是A和B之间的联接，其中A是外部关系，B是内部关系。

在大多数情况下，A JOIN B的成本与B JOIN A的成本不同。

在这一部分中，我还将假设外部关系具有N个元素 ，内部关系具有M个元素。请记住，真正的优化程序可以通过统计信息了解N和M的值。

注意：N和M是关系的基数。

 

嵌套循环联接

嵌套循环联接是最简单的一种。

 

这是个主意：

• 对于外部关系中的每一行
• 您查看内部关系中的所有行以查看是否存在匹配的行

这是一个伪代码：

<span style="color:#333333">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
</span>

由于是两次迭代，因此时间复杂度为O（N * M）

 

就磁盘I / O而言，对于外部关系中的N行中的每行，内部循环都需要从内部关系中读取M行。该算法需要从磁盘读取N + N * M行。但是，如果内部关系足够小，则可以将该关系存储在内存中，并且只需进行M + N次读取即可。通过这种修改，内部关系必须是最小的，因为它有更多的机会适合内存。

就时间复杂度而言，这没有什么区别，但就磁盘I / O而言，最好只读取一次两个关系。      

当然，内部关系可以用索引代替，这对于磁盘I / O会更好。

 

由于此算法非常简单，因此如果内部关系太大而无法容纳在内存中，则这是另一个对磁盘I / O更友好的版本。这是个主意：

• 而不是逐行阅读两个关系，
• 您一堆又一堆地读取它们，并在内存中保留2束行（来自每个关系），
• 您比较两个束中的行，并保持匹配的行，
• 然后从磁盘加载新的束并进行比较
• 以此类推，直到没有束要加载为止。

这是一个可能的算法：

<span style="color:#333333">// 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
</span>

 

使用此版本，时间复杂度保持不变，但是磁盘访问数量减少了：

• 在以前的版本中，该算法需要N + N * M次访问（每个访问获得一行）。
• 在此新版本中，磁盘访问次数变为number_of_bunches_for（外部）+ number_of_ bundlees_for（外部）* number_of_ bundlees_for（内部）。
• 如果增加束的大小，则会减少磁盘访问次数。

注意：每个磁盘访问都比以前的算法收集更多的数据，但这没关系，因为它们是顺序访问（机械磁盘的真正问题是获取第一个数据的时间）。

 

哈希联接

在许多情况下，哈希联接比嵌套循环联接更复杂，但成本更高。

 

哈希联接的想法是：

• 1）从内部关系中获取所有元素
• 2）建立一个内存中的哈希表
• 3）一对一地获得外部关系的所有元素
• 4）计算每个元素的哈希值（使用哈希表的哈希函数）以找到内部关系的关联存储区
• 5）查找存储桶中的元素与外部表的元素之间是否存在匹配项

在时间复杂度方面，我需要做一些假设来简化问题：

• 内部关系分为X个值区
• 散列函数为这两种关系几乎均匀地分布散列值。换句话说，铲斗尺寸相同。
• 外部关系的元素与存储桶中的所有元素之间的匹配会花费存储桶中的元素数。

时间复杂度为（M / X）* N + cost_to_create_hash_table（M）+ cost_of_hash_function * N

如果哈希函数创建了足够多的小型存储桶，则时间复杂度为O（M + N）

 

这是哈希联接的另一个版本，它对内存更友好，但对磁盘I / O的友好性更低。这次：

• 1）您计算内部和外部关系的哈希表
• 2）然后将它们放在磁盘上
• 3）然后按桶比较2个关系桶（其中一个加载在内存中，另一个逐行读取）

 

合并加入

合并联接是唯一产生排序结果的联接。

注意：在此简化的合并联接中，没有内部表或外部表。他们都扮演着相同的角色。但是实际的实现方式会有所不同，例如，在处理重复项时。

合并联接可以分为两个步骤：

1. （可选）对联接操作进行排序：两个输入都对联接键进行了排序。
2. 合并联接操作：将排序的输入合并在一起。

 

种类

我们已经讲过合并排序，在这种情况下，是一种好的算法中的合并排序（但如果内存不是问题，则不是最好的）。

但是有时数据集已经被排序，例如：

• 如果表是本地排序的，例如，在联接条件下为索引组织的表
• 如果关系是联接条件上的索引
• 如果此联接应用于查询过程中已经排序的中间结果

 

合并加入

这部分与我们看到的合并排序的合并操作非常相似。但是这一次，我们没有从两个关系中选择每个元素，而是仅从两个关系中选择了相等的元素。这是个主意：

• 1）您比较2个关系中的两个当前元素（第一次时current = first）
• 2）如果它们相等，则将两个元素都放入结果中，然后转到两个关系的下一个元素
• 3）如果不是，则转到具有最低元素的关系的下一个元素（因为下一个元素可能匹配）
• 4）并重复1,2,3，直到到达其中一个关系的最后一个元素。

之所以可行，是因为两个关系都已排序，因此您无需在这些关系中“返回”。

此算法是简化版本，因为它无法处理相同数据在两个数组中多次出现（换句话说，多个匹配项）的情况。对于这种情况，实际版本“更复杂”。这就是为什么我选择了简化版本。

 

如果两个关系都已排序，则时间复杂度为O（N + M）

如果两个关系都需要排序，那么时间复杂度就是两个关系的排序成本：O（N * Log（N）+ M * Log（M））

 

对于CS极客，这是一种可以处理多个匹配项的算法（请注意：我对自己的算法不是100％肯定）：

<span style="color:#333333">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
</span>

 

哪一个是最好的？

如果有最佳的联接类型，就不会有多种类型。这个问题非常困难，因为有许多因素起作用，例如：

• 在可用内存量：没有足够的内存，你可以说再见了强大的散列连接（至少满内存哈希联接）
• 2个数据集的大小。例如，如果您有一个很大的表而一个很小的表，则嵌套循环联接将比哈希联接要快，因为哈希联接会产生大量的哈希。如果您有2个非常大的表，则嵌套循环联接将占用大量CPU。
• 该存在 的 指标。使用2个B + Tree索引，明智的选择似乎是合并联接
• 如果需要对结果进行排序：即使您正在使用未排序的数据集，您也可能希望使用代价高昂的合并联接（带有排序），因为最后将对结果进行排序并且可以进行链接另一个合并联接的结果（或者可能是因为查询通过ORDER BY / GROUP BY / DISTINCT操作隐式/显式地要求排序结果）
• 如果关系已经排序：在这种情况下，合并联接是最佳候选者
• 该类型的连接，你正在做的：这是一个等值连接（即：tableA.col1 = tableB.col2）？它是一个内部联接，一个外连接，一个笛卡尔积或自连接？某些联接在某些情况下不起作用。
• 该数据的分布。如果在数据连接条件是倾斜的（例如你在自己的姓氏加入的人，但很多人都有相同的），使用散列连接将是一场灾难，因为哈希函数将产生不良分布式桶。
• 如果要由多个线程/进程执行联接

 

有关更多信息，您可以阅读DB2，ORACLE或SQL Server文档。

 

简化的例子

我们刚刚看过3种类型的联接操作。

现在，我们需要联接5个表才能完整地看到一个人。一个人可以有：

• 多个移动
• 多个邮件
• 多个地址
• 多个BANK_ACCOUNTS

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

<span style="color:#333333">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
</span>

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

• 我应该为每种联接使用哪种联接？

我有3种可能的联接（哈希联接，合并联接，嵌套联接），可以使用0,1或2个索引（更不用说存在不同类型的索引）。

• 我应该选择什么顺序来计算联接？

例如，下图显示了4个表上仅3个联接的不同可能计划

 

所以这是我的可能性：

• 1）我使用蛮力方法

使用数据库统计信息，我计算出每种可能的计划的成本，并保持最佳计划。但是有很多可能性。对于给定的联接顺序，每个联接都有3种可能性：HashJoin，MergeJoin，NestedJoin。因此，对于给定的连接顺序，存在3 4种可能性。连接排序是二叉树上的一个置换问题，有（2 * 4）！/（4 + 1）!！可能的订单。对于这个非常简化的问题，我最终得到3 4 *（2 * 4）！/（4 + 1）！可能性。

用非极客术语来说，这意味着27 216个可能的计划。如果现在添加使合并联接采用0,1或2个B + Tree索引的可能性，则可能的计划数变为21万个。我是否忘了提到此查询非常简单？

• 2）我哭了，辞掉了这份工作

这很诱人，但您不会得到结果，我需要钱来支付账单。

• 3）我只尝试一些计划，并采取成本最低的计划。

由于我不是超人，所以无法计算每个计划的成本。相反，我可以任意选择所有可能计划的子集，计算其成本，然后为您提供此子集的最佳计划。

• 4）我运用精明的规则来减少可能的计划数量。

有两种类型的规则：

我可以使用“逻辑”规则来消除无用的可能性，但是它们不会过滤很多可能的计划。例如：“嵌套循环连接的内部关系必须是最小的数据集”

我接受没有找到最佳解决方案，而是采用更具侵略性的规则来减少大量可能性的想法。例如：“如果关系很小，请使用嵌套循环联接，切勿使用合并联接或哈希联接”

 

在这个简单的示例中，我最终有很多可能性。但是实际查询可以具有其他关系运算符，例如OUTER JOIN，CROSS JOIN，GROUP BY，ORDER BY，PROJECTION，UNION，INTERSECT，DISTINCT……这意味着更多的可能性。

那么，数据库是如何做到的呢？

 

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

关系数据库尝试了我刚刚说过的多种方法。优化器的真正工作是在有限的时间内找到一个好的解决方案。

大多数情况下，优化器找不到最佳解决方案，而是找到“好的”解决方案。

对于小的查询，可以使用蛮力方法。但是有一种方法可以避免不必要的计算，因此即使是中等查询也可以使用蛮力方法。这称为动态编程。

 

动态编程

这两个词背后的想法是，许多执行计划非常相似。如果您查看以下计划：

它们共享相同的（A JOIN B）子树。因此，与其在每个计划中都不计算此子树的成本，我们可以对其进行一次计算，保存计算的成本，并在再次看到此子树时重新使用它。更正式地说，我们正面临一个重叠的问题。为了避免对部分结果进行额外的计算，我们使用了记忆。

使用这种技术，而不是（2 * N）！/（N + 1）！时间复杂度，我们“只”有3 ñ。在我们前面的具有4个联接的示例中，这意味着从336的顺序传递到81。如果您通过8个联接（不大）进行较大的查询，则意味着从57 657 600传递到6561。

 

对于CS极客，这是我在给您的正式课程中找到的算法。我不会解释此算法，因此只有在您已经了解动态编程或对算法精通的情况下（如果被警告，请仔细阅读）：

<span style="color:#333333">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]
</span>

 

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

• 如果仅分析某种类型的计划（例如，左深树），则最终得到n * 2 n而不是3 n

• 如果我们添加逻辑规则来避免针对某些模式的计划（例如“如果表作为给定谓词的索引，则不要尝试对表进行合并联接，而仅对索引进行尝试”），它将减少可能性的数量而无需损害尽可能最好的解决方案。
• 如果我们在流上添加规则（例如“在所有其他关系操作之前执行联接操作”），那么它也会减少很多可能性。
• …

 

贪婪算法

但是对于非常大的查询或具有非常快的答案（但不是非常快的查询）的情况，则使用另一种算法，即贪婪算法。

这个想法是遵循规则（或启发式）以增量方式构建查询计划。使用此规则，贪婪算法一次找到一个问题的最佳解决方案。该算法从一个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都应用相同的算法。然后，我们以最低的成本保留计划。

顺便说一句，该算法有一个名称：它称为Nearest neighbor algorithm。

我不会详细介绍，但是通过良好的建模和N * log（N）的排序，可以轻松解决此问题。对于完整的动态编程版本，该算法的成本为O（N * log（N））对O（3 N）。如果您有20个联接的大型查询，则意味着26 vs 3 486 784 401，这是很大的不同！

 

该算法的问题在于，如果我们保持此联接并添加新联接，则假定在2个表之间找到最佳联接将为我们带来最佳成本。但：

• 即使A JOIN B给出了A，B和C之间的最佳成本
• （A JOIN C）JOIN B可能比（A JOIN B）JOIN C更好的结果。

为了改善结果，您可以使用不同的规则运行多种贪婪算法，并保持最佳计划。

 

其他算法

[如果您已经厌倦了算法，请跳到下一部分，对于其余的文章，我要说的内容并不重要。]

对于许多CS研究人员而言，寻找最佳可行方案的问题是一个活跃的研究主题。他们经常尝试为更精确的问题/模式找到更好的解决方案。例如，

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

 

还研究了其他算法来代替大型查询的动态编程。贪婪算法属于较大的家族，称为启发式算法。贪心算法遵循规则（或启发式），保留在上一步中找到的解决方案，然后“追加”以找到当前步骤的解决方案。一些算法遵循规则并逐步应用它，但并不总是保持上一步中找到的最佳解决方案。它们被称为启发式算法。

例如，遗传算法遵循规则，但通常不会保留最后一步的最佳解决方案：

• 解决方案代表可能的完整查询计划
• 在每个步骤中都保留了P个解决方案（即计划），而不是一个解决方案（即计划）。
• 0）随机创建P个查询计划
• 1）仅保留成本最高的计划
• 2）将这些最佳计划混合在一起以生成P个新闻计划
• 3）P个新计划中的一些是随机修改的
• 4）步骤1,2,3重复T次
• 5）然后，从最后一个循环的P计划中保留最佳计划。

您执行的循环越多，计划就会越好。

这是魔术吗？不，这是自然法则：只有适者生存！

仅供参考，遗传算法是在PostgreSQL中实现的，但是我无法确定默认情况下是否使用了它们。

数据库中还有其他启发式算法，例如“模拟退火”，“迭代改进”，“两阶段优化”……但是我不知道它们是否目前在企业数据库中使用，或者仅在研究数据库中使用。

有关更多信息，您可以阅读下面的研究文章，该文章提出了更多可能的算法： 数据库查询优化中的联接排序问题的算法综述

 

真正的优化器

[您可以跳到下一部分，我要说的并不重要]

但是，这一切都是非常理论性的。由于我是开发人员而不是研究人员，所以我喜欢具体的例子。

让我们看看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。默认情况下，优化器使用以下特征：

• 使用所有可用的统计信息，包括常值和分位数统计信息。
• 应用所有查询重写规则（包括具体化查询表路由），但仅在极少数情况下适用的计算密集型规则除外。
• 使用动态编程联接枚举，其中：
  • 复合内部关系的有限使用
  • 涉及查找表的星型模式对笛卡尔乘积的使用有限
• 考虑了各种各样的访问方法，包括列表预取（注意：将看到含义），索引ANDing（注意：具有索引的特殊操作）和具体化查询表路由。

缺省情况下，DB2使用受试探法限制的动态编程进行联接排序。

其他条件（GROUP BY，DISTINCT…）由简单规则处理。

 

查询计划缓存

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

 

查询执行器

在这一阶段，我们有一个优化的执行计划。将该计划编译为可执行代码。然后，如果有足够的资源（内存，CPU），则由查询执行器执行。计划中的运算符（JOIN，SORT BY…）可以按顺序或并行方式执行；由执行者决定。为了获取和写入数据，查询执行器与数据管理器进行交互，这是本文的下一部分。

 

资料管理员

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

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

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

 

缓存管理器

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

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

• 顺序访问（例如：全面扫描）与随机访问（例如：按行ID进行访问），
• 读与写

以及数据库使用的磁盘类型：

• 7.2k / 10k / 15k rpm硬盘
• 固态硬盘
• RAID 1/5 /…

但是我要说的内存比磁盘快100到10万倍。

但是，这会导致另一个问题（与数据库一样……）。高速缓存管理器需要在查询执行程序使用它们之前获取内存中的数据。否则查询管理器必须等待慢速磁盘中的数据。

 

预取

此问题称为预取。查询执行器知道它需要的数据，因为它知道查询的全部流程，并且知道具有统计信息的磁盘上的数据。这是个主意：

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

CM将所有这些数据存储在其缓冲池中。为了知道是否仍然需要数据，缓存管理器添加了有关缓存数据的额外信息（称为闩锁）。

 

有时查询执行器不知道需要什么数据，而某些数据库不提供此功能。取而代之的是，他们使用推测性预取（例如：如果查询执行程序要求数据1,3,5，在不久的将来很可能会要求7,9,11）或顺序预取（在这种情况下，CM只是从磁盘中加载询问后的下一个连续数据）。

 

为了监视预取的工作状况，现代数据库提供了一个称为“缓冲区/缓存命中率”的指标。命中率显示在不要求磁盘访问的情况下在缓冲区高速缓存中找到请求数据的频率。

注意：较差的缓存命中率并不总是意味着缓存工作不正常。有关更多信息，您可以阅读Oracle文档。

 

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

 

缓冲区替换策略

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

 

LRU

LRU代表大号东- [R ecently ü sed的。该算法的思想是将最近使用过的数据保存在缓存中，因此更有可能再次使用。

这是一个视觉示例：

 

为了理解，我假设缓冲区中的数据没有被闩锁锁定（因此可以删除）。在这个简单的示例中，缓冲区可以存储3个元素：

• 1：缓存管理器使用数据1并将数据放入空缓冲区
• 2：CM使用数据4并将数据放入半装入的缓冲区
• 3：CM使用数据3并将数据放入半载缓冲区
• 4：CM使用数据9。缓冲区已满，因此数据1被删除， 因为它是最近使用的数据。数据9被添加到缓冲区中
• 5：CM使用数据4。数据4已在缓冲区中，因此它再次成为最近使用的第一个数据。
• 6：CM使用数据1。缓冲区已满，因此数据9被删除， 因为它是最近使用的数据。数据1被添加到缓冲区
• …

该算法效果很好，但存在一些局限性。如果在一张大桌子上进行全面扫描怎么办？换句话说，当表/索引的大小大于缓冲区的大小时会发生什么？使用此算法将删除高速缓存中的所有先前值，而完全扫描中的数据可能仅使用一次。

 

改进之处

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

“对于非常大的表，数据库通常使用直接路径读取，该路径直接[...]加载块，以避免填充缓冲区高速缓存。对于中等大小的表，数据库可以使用直接读取或高速缓存读取。如果决定使用读取的缓存，则数据库会将这些块放在LRU列表的末尾，以防止扫描有效清除缓冲区缓存。”

还有其他可能性，例如使用称为LRU-K的LRU的高级版本。例如，SQL Server使用LRU-K表示K = 2。

该算法背后的想法是考虑更多历史。使用简单的LRU（对于K = 1也是LRU-K），该算法仅考虑最后一次使用数据的时间。使用LRU-K：

• 它考虑到了上次使用的时间数据K。
• 权重是使用数据的次数
• 如果将一堆新数据加载到缓存中，则不会删除旧的但经常使用的数据（因为它们的权重更高）。
• 但是，如果不再使用旧数据，该算法便无法将其保留在缓存中。
• 因此，如果不使用数据，权重会 随着时间的推移而降低。

权重的计算成本很高，这就是为什么SQL Server仅使用K = 2的原因。该值在可接受的开销下表现良好。

要更深入地了解LRU-K，可以阅读原始研究论文（1993年）：用于数据库磁盘缓冲的LRU-K页面替换算法。

 

其他算法

当然，还有其他算法可以管理缓存，例如

• 2Q（类似于LRU-K的算法）
• 时钟（类似于LRU-K的算法）
• MRU（最近使用，使用与LRU相同的逻辑，但使用另一条规则）
• LRFU（最近和经常使用的最少）
• …

一些数据库允许使用默认算法以外的其他算法。

 

写缓冲区

我只说过在使用数据之前加载数据的读取缓冲区。但是在数据库中，您还拥有写缓冲区，用于存储数据并将数据按束刷新到磁盘上，而不是一一写入数据并产生许多单个磁盘访问权限。

 

请记住，缓冲区存储的是页面（数据的最小单位）而不是行（这是查看数据的逻辑/人为方式）。如果页面已被修改且未写入磁盘，则缓冲池中的页面是脏的。有多种算法可以决定在磁盘上写入脏页的最佳时间，但是它与事务的概念高度相关，这是本文的下一部分。

 

交易经理

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

 

我在酸

ACID事务是确保以下四件事的工作单元：

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

 

在同一事务中，您可以运行多个SQL查询来读取，创建，更新和删除数据。当两个事务使用相同的数据时，混乱就开始了。经典示例是从帐户A到帐户B的资金转帐。假设您有2笔交易：

• 交易1从帐户A收取100 $，并将其转入帐户B
• 交易2从帐户A收取50 $，并将其转入帐户B

如果我们回到ACID属性：

• 原子性确保无论在T1期间发生什么情况（服务器崩溃，网络故障...），您都不会遇到从A提取100 $而不将其分配给B的情况（这种情况是不一致的状态） 。

• I solation确保如果T1和T2同时发生，最终A将被收取150 $，而B被给予150 $，而不是，例如，A被收取150 $，而B仅被给予$ 50，因为T2已部分删除了操作T1的状态（这种情况也是不一致的状态）。

• 持久性可确保如果T1提交后数据库崩溃，T1将不会消失。

• 一致性确保不会在系统中创建或销毁任何资金。

 

[如果需要，您可以跳到下一部分，对于本文的其余部分，我要说的内容并不重要]

许多现代数据库不使用纯隔离作为默认行为，因为它带来巨大的性能开销。SQL规范定义了4个隔离级别：

• 可序列化（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，然后该数据D被事务B修改（未提交但仍在运行），如果A再次读取数据D，它将看到修改后的值。如果回滚了事务B，则第二次读取A的数据D毫无意义，因为它已经被从未发生过的事务B修改过（因为它已被回滚）。

这称为脏读。

 

大多数数据库都添加了自己的自定义隔离级别（例如PostgreSQL，Oracle和SQL Server使用的快照隔离）。而且，大多数数据库并没有实现SQL规范的所有级别（尤其是读取未提交的级别）。

用户/开发人员可以在连接开始时覆盖默认的隔离级别（这是添加的非常简单的代码行）。

 

并发控制

确保隔离，一致性和原子性的真正问题是对同一数据的写操作（添加，更新和删除）：

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

此问题称为并发控制。

解决此问题的最简单方法是一个接一个地（即顺序地）运行每个事务。但这根本不是可扩展的，并且只有一个内核在多处理器/内核服务器上工作，效率不是很高……

解决此问题的理想方法是每次创建或取消交易时：

• 监控所有交易的所有操作
• 检查两个（或多个）交易的部分是否存在冲突，因为它们正在读取/修改相同的数据。
• 对冲突事务中的操作进行重新排序以减小冲突部分的大小
• 以一定顺序执行冲突部分（非冲突事务仍在并发运行）。
• 考虑到可以取消交易。

更正式地说，这是时间表冲突的时间表问题。更具体地说，这是一个非常困难且占用大量CPU的优化问题。企业数据库无力等待数小时来为每个新交易事件找到最佳时间表。因此，他们使用不太理想的方法，导致冲突事务之间浪费更多时间。

 

锁管理器

为了解决此问题，大多数数据库都使用锁和/或数据版本控制。由于这是一个大话题，因此我将重点介绍锁定部分，然后再介绍一些数据版本控制。

 

悲观锁定

锁定背后的想法是：

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

这称为排他锁。

但是，对于仅需要读取数据的事务使用排他锁非常昂贵，因为这会迫使仅希望读取相同数据的其他事务等待。这就是为什么还有另一种类型的锁，即共享锁。

使用共享锁：

• 如果交易只需要读取数据A，
• 它“共享锁定”数据并读取数据
• 如果第二笔交易也只需要读取数据A，
• 它“共享锁定”数据并读取数据
• 如果第三笔交易需要修改数据A，
• 它“独占锁定”数据，但是它必须等到其他两个事务释放它们的共享锁后才能将独占锁定应用于数据A。

但是，如果将数据作为排他锁，则仅需要读取数据的事务将不得不等待排他锁的结尾才能在数据上放置共享锁。

锁管理器是提供和释放锁的过程。在内部，它将锁存储在哈希表中（其中的键是要锁定的数据），并且知道每个数据：

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

 

僵局

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

在此图中：

• 事务A拥有对data1的排他锁，正在等待获取data2
• 事务B在data2上具有排他锁，并且正在等待获取data1

这称为死锁。

在死锁期间，锁管理器选择要取消（回滚）的事务以删除死锁。这个决定并不容易：

• 是否最好杀死修改了最少数据量的事务（因此将产生最便宜的回滚）？
• 最好是因为另一笔交易的用户等待了更长的时间而取消了最老的交易呢？
• 是否最好取消将花费较少时间完成的交易（并避免可能的饥饿）？
• 如果发生回滚，此回滚将影响多少交易？

 

但是在做出选择之前，它需要检查是否存在死锁。

哈希表可以看作是一个图形（就像前面的图中一样）。如果图中有一个循环，则存在死锁。由于检查周期非常昂贵（因为带有所有锁的图形很大），因此通常使用一种更简单的方法：使用timeout。如果在此超时时间内未给出锁定，则事务将进入死锁状态。

 

锁管理器还可以在提供锁之前检查该锁是否会产生死锁。但是，完美地做到这一点在计算上又很昂贵。因此，这些预检查通常是一组基本规则。

 

两相锁定

确保纯隔离的最简单方法是在事务开始时获取锁，然后在事务结束时释放锁。这意味着事务在开始之前必须等待其所有锁，并且在事务结束时释放由事务持有的锁。它可以工作，但 会浪费大量时间来等待所有锁。

更快的方法是两阶段锁定协议（DB2和SQL Server使用），该协议将事务分为两个阶段：

• 事务可以获取锁，但不能释放任何锁的增长阶段。
• 在收缩阶段，事务可以释放锁（针对已经处理并且不会再次处理的数据），但是无法获得新的锁。

 

这两个简单规则的思想是：

• 释放不再使用的锁，以减少等待这些锁的其他事务的等待时间
• 以防止发生这样的情况：在事务开始后，该事务会修改数据，因此与该事务所获取的第一个数据不一致。

 

该协议运行良好，除非修改（取消）了修改数据并释放关联锁的事务。您可能最终遇到另一个事务读取修改后的值而该值将被回滚的情况。为避免此问题，必须在事务结束时释放所有排他锁。

 

几句话

当然，实际的数据库使用的是更复杂的系统，其中涉及更多类型的锁（例如意图锁）和更多的粒度（行，页面，分区，表，表空间上的锁），但是这种想法仍然存在。相同的。

我仅介绍了基于纯锁的方法。数据版本控制是解决此问题的另一种方法。

版本控制的思想是：

• 每个交易都可以同时修改相同的数据
• 每笔交易都有其自己的数据副本（或版本）
• 如果2个事务修改了相同的数据，则仅接受一个修改，而另一个则被拒绝，并且关联的事务将回滚（并可能重新运行）。

由于以下原因，它提高了性能：

• 读者交易不会阻止作家交易
• 作家交易不会阻止读者交易
• “胖而慢”的锁管理器没有任何开销

一切都比锁好，除非两个事务写入相同的数据。此外，您可能很快就会面临巨大的磁盘空间开销。

 

数据版本控制和锁定是两个不同的愿景：乐观锁定与悲观锁定。他们都有优点和缺点；这实际上取决于用例（更多的读取还是更多的写入）。对于数据版本控制的演示，我推荐这个关于PostgreSQL如何实现多版本并发控制的很好的演示。

某些数据库，例如DB2（直到DB2 9.7）和SQL Server（快照隔离除外）仅使用锁。其他类似PostgreSQL，MySQL和Oracle的混合方法涉及锁和数据版本控制。我不知道仅使用数据版本控制的数据库（如果您知道基于纯数据版本控制的数据库，请随时告诉我）。

[2015年8月20日更新]一位读者告诉我：

Firebird和Interbase使用版本控制而没有记录锁定。
版本控制会对索引产生有趣的影响：有时，唯一索引包含重复项，索引所包含的条目可能比表中具有行的条目多，等等。

 

如果阅读有关隔离级别不同的部分，则增加隔离级别时会增加锁的数量，因此事务等待其锁所浪费的时间。这就是为什么大多数数据库默认情况下不使用最高隔离级别（可序列化）的原因。

与往常一样，您可以自己查看主要数据库的文档（例如MySQL，PostgreSQL或Oracle）。

 

日志管理器

我们已经看到，为了提高性能，数据库将数据存储在内存缓冲区中。但是，如果在提交事务时服务器崩溃，则崩溃期间您仍然会丢失仍在内存中的数据，这将破坏事务的持久性。

您可以将所有内容写在磁盘上，但是如果服务器崩溃，最终将一半的数据写在磁盘上，这将破坏事务的原子性。

事务编写的任何修改必须撤消或完成。

要解决此问题，有两种方法：

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

 

沃尔

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

大多数数据库（至少是Oracle，SQL Server，DB2，PostgreSQL，MySQL和 SQLite）都使用预写日志记录协议（WAL）处理事务日志。WAL协议是3条规则的集合：

• 1）对数据库的每次修改都会产生一个日志记录，并且在将数据写入磁盘之前，必须将日志记录写入事务日志中。
• 2）日志记录必须按顺序写入；在必须在日志记录B之前但在B之前写入的日志记录A
• 3）提交事务后，必须在事务成功结束之前将提交顺序写在事务日志上。

 

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

 

错误的答案！在完成所有工作之后，您应该知道与数据库相关的所有内容都受到“数据库效应”的诅咒。更严重的是，问题是找到一种在保持良好性能的同时写入日志的方法。如果事务日志上的写入太慢，它们将减慢所有操作。

 

白羊座

1992年，IBM研究人员“发明”了WAL的增强版本ARIES。大多数现代数据库都或多或少地使用ARIES。逻辑可能不尽相同，但ARIES背后的概念无处不在。我引用发明报价的原因是，按照MIT的这一课程，IBM研究人员所做的“仅是编写事务恢复的良好实践”。自从ARIES论文发表时我才5岁，我不在乎那些苦涩的研究人员的八卦。实际上，在我们开始这最后一个技术部分之前，我仅将此信息用于让您休息一下。我已经阅读了有关ARIES的研究论文的大部分内容，并且发现它非常有趣！在这一部分中，我将仅向您简要介绍ARIES，但是如果您需要真正的知识，我强烈建议您阅读本文。

 

ARIES代表一个lgorithms为[R ecovery和我染料溶液Ë xploiting小号emantics。

该技术的目的是双重的：

• 1）写日志时表现良好
• 2）具有快速可靠的恢复

 

数据库必须回滚事务有多种原因：

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

 

有时（例如，在网络故障的情况下），数据库可以恢复事务。

那怎么可能？要回答这个问题，我们需要了解存储在日志记录中的信息。

 

日志

事务期间的每个操作（添加/删除/修改）都会生成一个日志。该日志记录由以下内容组成：

• LSN：一个独特的大号OG小号层序Ñ棕土。该LSN按时间顺序给出*。这意味着，如果操作A在操作B之前发生，则日志A的LSN将低于日志B的LSN。
• TransID：产生该操作的事务的ID。
• PageID：修改后的数据在磁盘上的位置。磁盘上的最小数据量是一个页面，因此数据的位置就是包含该数据的页面的位置。
• PrevLSN：指向同一事务产生的先前日志记录的链接。
• 撤消操作：消除操作影响的一种方法

例如，如果操作是更新，则UNDO将存储更新前已更新元素的值/状态（物理UNDO），或者存储反向操作以返回到先前状态（逻辑UNDO）**。

• REDO：重播操作的一种方式

同样，有两种方法可以做到这一点。您可以在操作之后存储元素的值/状态，或者在操作本身中存储元素以重播它。

• …：（仅供参考，ARIES日志还有另外两个字段：UndoNxtLSN和Type）。

 

此外，磁盘上的每个页面（用于存储数据，而不是日志）具有修改数据的最后操作的日志记录（LSN）的ID。

*给出LSN的方式更加复杂，因为它与日志的存储方式相关。但是这个想法仍然是一样的。

** ARIES仅使用逻辑UNDO，因为处理物理UNDO真是一团糟。

注意：据我所知，只有PostgreSQL没有使用UNDO。相反，它使用垃圾收集器守护程序来删除旧版本的数据。这与PostgreSQL中数据版本控制的实现有关。

 

为了给您带来更好的想法，这是查询“ UPDATE FROM PERSON SET AGE = 18;”产生的日志记录的直观简化示例。假设此查询在事务18中执行。

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

 

日志缓冲区

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

当查询执行者要求修改时：

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

 

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

 

窃取和强制政策

出于性能原因，可能在提交后执行步骤5，因为如果发生崩溃，仍然可以使用REDO日志恢复事务。这就是所谓的NO-FORCE政策。

数据库可以选择一个FORCE策略（即，必须在提交之前执行步骤5），以降低恢复过程中的工作量。

另一个问题是选择将数据逐步写入磁盘（STEAL策略）还是缓冲区管理器是否需要等到提交顺序一次写入所有内容（NO-STEAL）。在STEAL和NO-STEAL之间进行选择取决于您想要的：使用UNDO日志进行长时间恢复的快速写入还是快速恢复？

 

以下是这些策略对恢复的影响的摘要：

• STEAL / NO-FORCE 需要UNDO和REDO：最高的性能，但提供更复杂的日志和恢复过程（如ARIES）。这是大多数数据库所做的选择。注意：我在多个研究论文和课程中都读过这个事实，但在官方文档中却找不到（明确地）。
• STEAL / FORCE仅需要撤消。
• 无需窃取/无需强制只需要重做。
• 无窃取/强制不需要任何东西：最差的性能和大量的夯是必需的。

 

恢复部分

好的，所以我们有不错的日志，让我们使用它们吧！

假设新的实习生使数据库崩溃（规则n°1：这始终是实习生的错）。您重新启动数据库，恢复过程开始。

 

ARIES通过以下三步从崩溃中恢复：

• 1）分析阶段：恢复过程读取完整的事务日志*，以重新创建崩溃期间发生的事情的时间表。它确定要回滚的事务（所有没有提交订单的事务都将回滚）以及崩溃时需要将哪些数据写入磁盘。
• 2）重做过程：此过程从分析期间确定的日志记录开始，并使用REDO将数据库更新为崩溃前的状态。

在重做阶段，将按时间顺序（使用LSN）处理REDO日志。

对于每个日志，恢复过程都会读取磁盘上包含要修改的数据的页面的LSN。

如果LSN（page_on_disk）> = LSN（log_record），则意味着数据已在崩溃之前被写入磁盘（但是该值已被日志之后和崩溃之前发生的操作所覆盖），因此什么也不做。

如果LSN（page_on_disk）<LSN（log_record），则更新磁盘上的页面。

即使对于将要回滚的事务，重做也已完成，因为它简化了恢复过程（但我敢肯定，现代数据库不会这样做）。

• 3）撤消密码：此密码会回退崩溃时所有未完成的事务。回滚从每个事务的最后一个日志开始，并以反时间顺序（使用日志记录的PrevLSN）处理UNDO日志。

 

在恢复期间，必须警告事务日志有关恢复过程所执行的操作，以便磁盘上写入的数据与事务日志中写入的数据同步。一种解决方案是删除正在撤消的事务的日志记录，但这非常困难。而是，ARIES将补偿日志写入事务日志中，该日志将逻辑上删除要删除的事务的日志记录。

当“手动”取消交易或由锁定管理器取消交易（以停止死锁）或仅由于网络故障而取消交易时，则不需要分析通过。确实，有关REDO和UNDO内容的信息可在2个内存表中找到：

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

这些表由缓存管理器和事务管理器针对每个新事务事件进行更新。由于它们在内存中，因此在数据库崩溃时会被销毁。

分析阶段的工作是在崩溃后使用事务日志中的信息重新创建两个表。*为了加快分析过程，ARIES提供了checkpoint的概念。想法是不时在磁盘上写入事务表，脏页表的内容以及该写入时的最后LSN的内容，以便在分析过程中仅分析此LSN之后的日志。

 

总结一下

在写这篇文章之前，我知道这个主题有多大，而且我知道写一篇深入的文章还需要时间。原来，我非常乐观，花了比预期多两倍的时间，但学到了很多东西。

如果您想对数据库有一个很好的了解，我建议阅读研究论文“数据库系统的体系结构”。这是对数据库（110页）的很好的介绍，并且对于非CS专家来说是一次可读性。本文为我找到本文的计划提供了很多帮助，它不像我的文章那样关注数据结构和算法，而是更多地关注体系结构概念。

 

如果仔细阅读本文，您现在应该了解数据库的功能。由于这是一篇很长的文章，所以让我提醒您我们所看到的：

• B + Tree索引概述
• 数据库的全局概述
• 基于成本的优化概述，重点放在联接运算符上
• 缓冲池管理概述
• 交易管理概述

但是数据库包含了更多的聪明之处。例如，我没有谈论一些棘手的问题，例如：

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

 

因此，当您不得不在错误的NoSQL数据库和坚如磐石的关系数据库之间进行选择时，请三思而后行。别误会，有些NoSQL数据库很棒。但是他们还很年轻，正在回答涉及一些应用程序的特定问题。