数据库原理

今天转载一篇文章，是关于数据库原理的文章，写的非常好，自己从中摘抄一些对自己有用的东西。点击打开链接

我会从一些计算机科学方面的知识谈起，比如时间复杂度。我知道有些人讨厌这个概念，但是没有它你就不能理解数据库内部的巧妙之处。由于这是个很大的话题， 我将集中探讨 我认为必要的内容： 数据库处理SQL查询的方式 。我仅仅介绍数据库背后的基本概念，以便在读完本文后你会 对底层到底发生了什么有个很好的了解 。

这篇文章大约分为3个部分：

• 底层和上层数据库组件概况
• 查询优化过程概况
• 事务和缓冲池管理概况

很久很久以前（在一个遥远而又遥远的星系……)，开发者必须确切地知道他们的代码需要多少次运算。他们把算法和数据结构牢记于心，因为他们的计算机运行缓慢，无法承受对CPU和内存的浪费。

重要的不是数据量，而是 当数据量增加时运算如何增加 。时间复杂度不会给出确切的运算次数，但是给出的是一种理念。

数据量低时，O(1) 和 O(n^2)的区别可以忽略不计。比如，你有个算法要处理2000条元素。

• O(1) 算法会消耗 1 次运算
• O(log(n)) 算法会消耗 7 次运算
• O(n) 算法会消耗 2000 次运算
• O(n*log(n)) 算法会消耗 14,000 次运算
• O(n^2) 算法会消耗 4,000,000 次运算

O(1) 和 O(n^2) 的区别似乎很大（4百万）,但你最多损失 2 毫秒，只是一眨眼的功夫。确实，当今处理器每秒可处理上亿次的运算。这就是为什么性能和优化在很多IT项目中不是问题。

这里我只探讨时间复杂度，但复杂度还包括：

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

当你要对一个集合排序时你怎么做？什么？调用 sort() 函数……好吧，算你对了……但是对于数据库，你需要理解这个 sort() 函数的工作原理。

优秀的排序算法有好几个，我侧重于最重要的一种： 合并排序 。你现在可能还不了解数据排序有什么用，但看完查询优化部分后你就会知道了。再者，合并排序有助于我们以后理解数据库常见的联接操作，即 合并联接  。

合并排序的强大之处

为什么这个算法如此强大？

因为：

• 你可以更改算法，以便于节省内存空间，方法是不创建新的序列而是直接修改输入序列。

注：这种算法叫『原地算法』(in-place algorithm)

• 你可以更改算法，以便于同时使用磁盘空间和少量内存而避免巨量磁盘 I/O。方法是只向内存中加载当前处理的部分。在仅仅100MB的内存缓冲区内排序一个几个GB的表时，这是个很重要的技巧。

注：这种算法叫『外部排序』(external sorting)。

• 你可以更改算法，以便于在 多处理器/多线程/多服务器 上运行。

比如，分布式合并排序是Hadoop（那个著名的大数据框架）的关键组件之一。

• 这个算法可以点石成金（事实如此！）

这个排序算法在大多数（如果不是全部的话）数据库中使用，但是它并不是唯一算法。如果你想多了解一些，你可以看看，探讨的是数据库中常用排序算法的优势和劣势。

阵列，树和哈希表

既然我们已经了解了时间复杂度和排序背后的理念，我必须要向你介绍3种数据结构了。这个很重要，因为它们是现代数据库的支柱。我还会介绍数据库索引的概念。

阵列

二维阵列是最简单的数据结构。一个表可以看作是个阵列，比如：

虽然用这个方法保存和视觉化数据很棒，但是当你要查找特定的值它就很糟糕了。 举个例子， 如果你要找到所有在 UK 工作的人 ，你必须查看每一行以判断该行是否属于 UK 。 这会造成 N 次运算的成本 （N 等于行数），还不赖嘛，但是有没有更快的方法呢？这时候树就可以登场了（或开始起作用了）。

树和数据库索引

二叉查找树是带有特殊属性的二叉树，每个节点的关键字必须：

• 比保存在左子树的任何键值都要大
• 比保存在右子树的任何键值都要小

现在比方说我要找40

• 我从键值为136的根开始，因为 136>40，所以我去找节点136的左子树。
• 80>40，所以我去找节点 80 的左子树
• 40=40，节点存在。我抽取出节点内部行的ID（图中没有画）再去表中查找对应的 ROW ID。
• 知道 ROW ID我就知道了数据在表中对精确位置，就可以立即获取数据。

最后，两次查询的成本就是树内部的层数。如果你仔细阅读了合并排序的部分，你就应该明白一共有 log(N)层。所以这个查询的成本是 log(N)，不错啊！

上文说的很抽象，我们回来看看我们的问题。这次不用傻傻的数字了，想象一下前表中代表某人的国家的字符串。假设你有个树包含表中的列『country』：

• 如果你想知道谁在 UK 工作
• 你在树中查找代表 UK 的节点
• 在『UK 节点』你会找到 UK 员工那些行的位置

这次搜索只需 log(N) 次运算，而如果你直接使用阵列则需要 N 次运算。你刚刚想象的就是一个数据库索引。

B+树索引

查找一个特定值这个树挺好用，但是当你需要查找两个值之间的多个元素时 ，就会有 大 麻烦了。你的成本将是 O(N)，因为你必须查找树的每一个节点，以判断它是否处于那 2 个值之间（例如，对树使用中序遍历）。而且这个操作不是磁盘I/O有利的，因为你必须读取整个树。我们需要找到高效的 范围查询 方法。为了解决这个问题，现代数据库使用了一种修订版的树，叫做B+树。在一个B+树里

• 只有最底层的节点（叶子节点）才保存信息（相关表的行位置）
• 其它节点只是在搜索中用来指引到正确节点的。

你可以看到，节点更多了（多了两倍）。确实，你有了额外的节点，它们就是帮助你找到正确节点的『决策节点』（正确节点保存着相关表中行的位置）。但是搜索复杂度还是在 O(log(N))（只多了一层）。一个重要的不同点是，最底层的节点是跟后续节点相连接的。

用这个 B+树，假设你要找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,000），那结果就是天壤之别了。

然而还有新的问题（又来了！）。如果你在数据库中增加或删除一行（从而在相关的 B+树索引里）：

• 你必须在B+树中的节点之间保持顺序，否则节点会变得一团糟，你无法从中找到想要的节点。
• 你必须尽可能降低B+树的层数，否则 O(log(N)) 复杂度会变成 O(N)。

换句话说，B+树需要自我整理和自我平衡。谢天谢地，我们有智能删除和插入。但是这样也带来了成本：在B+树中，插入和删除操作是 O(log(N)) 复杂度。 所以有些人听到过使用太多索引不是个好主意这类说法。没错，你减慢了快速插入/更新/删除表中的一个行的操作，因为数据库需要以代价高昂的每索引 O(log(N)) 运算来更新表的索引。再者，增加索引意味着给 事务管理器带来更多的工作负荷（在本文结尾我们会探讨这个管理器）。

哈希

我们最后一个重要的数据结构是哈希表。当你想快速查找值时，哈希表是非常有用的。而且，理解哈希表会帮助我们接下来理解一个数据库常见的联接操作，叫做『哈希联接』。这个数据结构也被数据库用来保存一些内部的东西（比如 锁表或者缓冲池 ，我们在下文会研究这两个概念）。

比方说你要找元素 78：

• 哈希表计算 78 的哈希码，等于 8。
• 查找哈希桶 8，找到的第一个元素是 78。
• 返回元素 78。
• 查询仅耗费了 2 次运算（1次计算哈希值，另一次在哈希桶中查找元素）。

现在，比方说你要找元素 59：

• 哈希表计算 59 的哈希码，等于9。
• 查找哈希桶 9，第一个找到的元素是 99。因为 99 不等于 59， 那么 99 不是正确的元素。
• 用同样的逻辑，查找第二个元素(9)，第三个(79)，……，最后一个(29)。
• 元素不存在。
• 搜索耗费了 7 次运算。

为什么不用阵列呢？

嗯，你问得好。

• 一个哈希表可以只装载一半到内存，剩下的哈希桶可以留在硬盘上。
• 用阵列的话，你需要一个连续内存空间。如果你加载一个大表，很难分配足够的连续内存空间。
• 用哈希表的话，你可以选择你要的关键字（比如，一个人的国家和姓氏）。

数据库是一个易于访问和修改的信息集合。不过简单的一堆文件也能达到这个效果。事实上，像SQLite这样最简单的数据库也只是一堆文件而已，但SQLite是精心设计的一堆文件，因为它允许你：

• 使用事务来确保数据的安全和一致性
• 快速处理百万条以上的数据

为了理解成本优化器的原理，我觉得最好用个例子来『感受』一下这个任务背后的复杂性。这里我将给出联接 2 个表的 3 个方法，我们很快就能看到即便一个简单的联接查询对于优化器来说都是个噩梦。之后，我们会了解真正的优化器是怎么做的。

对于这些联接操作，我会专注于它们的时间复杂度，但是，数据库优化器计算的是它们的 CPU 成本、磁盘 I/O 成本、和内存需求。时间复杂度和 CPU 成本的区别是，时间成本是个近似值（给我这样的懒家伙准备的）。而 CPU 成本，我这里包括了所有的运算，比如：加法、条件判断、乘法、迭代……还有呢：

• 每一个高级代码运算都要特定数量的低级 CPU 运算。
• 对于 Intel Core i7、Intel Pentium 4、AMD Opteron…等，（就 CPU 周期而言）CPU 的运算成本是不同的，也就是说它取决于 CPU 的架构。

使用时间复杂度就容易多了（至少对我来说），用它我也能了解到 CBO 的概念。由于磁盘 I/O 是个重要的概念，我偶尔也会提到它。请牢记， 大多数时候瓶颈在于磁盘 I/O 而不是 CPU 使用。

缓存管理器

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

查询执行器不会直接从文件系统拿数据，而是向缓存管理器要。缓存管理器有一个内存缓存区，叫做 缓冲池 ， 从内存读取数据显著地提升数据库性能 。对此很难给出一个数量级，因为这取决于你需要的是哪种操作：

缓存管理器在缓冲池里保存所有的这些数据。为了确定一条数据是否有用，缓存管理器给缓存的数据添加了额外的信息（叫闩锁）。

有时查询执行器不知道它需要什么数据，有的数据库也不提供这个功能。相反，它们使用一种推测预读法（比如：如果查询执行器想要数据1、3、5，它不久后很可能会要 7、9、11），或者顺序预读法（这时候缓存管理器只是读取一批数据后简单地从磁盘加载下一批连续数据）。

为了监控预读的工作状况，现代数据库引入了一个度量叫缓冲/缓存命中率，用来显示请求的数据在缓存中找到而不是从磁盘读取的频率。

LRU 代表最近最少使用（ L east  R ecently  U sed）算法，背后的原理是：在缓存里保留的数据是最近使用的，所以更有可能再次使用。

事务管理器

最后但同样重要的，是事务管理器，我们将看到这个进程是如何保证每个查询在自己的事务内执行的。但开始之前，我们需要理解ACID事务的概念。

“I’m on acid”

一个ACID事务是一个工作单元，它要保证4个属性：

• 原子性（Atomicity）: 事务『要么全部完成，要么全部取消』，即使它持续运行10个小时。如果事务崩溃，状态回到事务之前（事务回滚）。
• 隔离性（Isolation）: 如果2个事务 A 和 B 同时运行，事务 A 和 B 最终的结果是相同的，不管 A 是结束于 B 之前/之后/运行期间。
• 持久性（Durability）: 一旦事务提交（也就是成功执行）,不管发生什么（崩溃或者出错），数据要保存在数据库中。
• 一致性（Consistency）: 只有合法的数据（依照关系约束和函数约束）能写入数据库，一致性与原子性和隔离性有关。

在同一个事务内，你可以运行多个SQL查询来读取、创建、更新和删除数据。当两个事务使用相同的数据，麻烦就来了。经典的例子是从账户A到账户B的汇款。假设有2个事务：

• 事务1（T1）从账户A取出100美元给账户B
• 事务2（T2）从账户A取出50美元给账户B

我们回来看看ACID属性：

• 原子性确保不管 T1 期间发生什么（服务器崩溃、网络中断…），你不能出现账户A 取走了100美元但没有给账户B 的现象（这就是数据不一致状态）。
• 隔离性确保如果 T1 和 T2 同时发生，最终A将减少150美元，B将得到150美元，而不是其他结果，比如因为 T2 部分抹除了 T1 的行为，A减少150美元而B只得到50美元（这也是不一致状态）。
• 持久性确保如果 T1 刚刚提交，数据库就发生崩溃，T1 不会消失得无影无踪。
• 一致性确保钱不会在系统内生成或灭失。

悲观锁

原理是：

• 如果一个事务需要一条数据
• 它就把数据锁住
• 如果另一个事务也需要这条数据
• 它就必须要等第一个事务释放这条数据
  这个锁叫排他锁。

但是对一个仅仅读取数据的事务使用排他锁非常昂贵，因为这会迫使其它只需要读取相同数据的事务等待。因此就有了另一种锁，共享锁。

共享锁是这样的：

• 如果一个事务只需要读取数据A
• 它会给数据A加上『共享锁』并读取
• 如果第二个事务也需要仅仅读取数据A
• 它会给数据A加上『共享锁』并读取
• 如果第三个事务需要修改数据A
• 它会给数据A加上『排他锁』，但是必须等待另外两个事务释放它们的共享锁。

同样的，如果一块数据被加上排他锁，一个只需要读取该数据的事务必须等待排他锁释放才能给该数据加上共享锁。

锁管理器是添加和释放锁的进程，在内部用一个哈希表保存锁信息（关键字是被锁的数据），并且了解每一块数据是：

• 被哪个事务加的锁
• 哪个事务在等待数据解锁

死锁

但是使用锁会导致一种情况，2个事务永远在等待一块数据

在本图中：

• 事务A 给 数据1 加上排他锁并且等待获取数据2
• 事务B 给 数据2 加上排他锁并且等待获取数据1

这叫死锁。

在死锁发生时，锁管理器要选择取消（回滚）一个事务，以便消除死锁。这可是个艰难的决定：

• 杀死数据修改量最少的事务（这样能减少回滚的成本）？
• 杀死持续时间最短的事务，因为其它事务的用户等的时间更长？
• 杀死能用更少时间结束的事务（避免可能的资源饥荒）？
• 一旦发生回滚，有多少事务会受到回滚的影响？

在作出选择之前，锁管理器需要检查是否有死锁存在。

哈希表可以看作是个图表（见上文图），图中出现循环就说明有死锁。由于检查循环是昂贵的（所有锁组成的图表是很庞大的），经常会通过简单的途径解决：使用 超时设定 。如果一个锁在超时时间内没有加上，那事务就进入死锁状态。