持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第21天，点击查看活动详情

计划执行

我们已经知道了数据库较为底层的结构是怎样的，现在我们来查询运算符在数据库中具体的执行是怎样的。关于这方面的内容我们可以分为三个方面来学习：

• 运算符算法：SQL语句中已定义的算子（聚集、并表等）是怎样执行的
• 查询处理模型：查询是用怎样的方案来执行的
• 执行架构：查询的执行在内存中是怎样架构的

首先我们来看执行计划，顾名思义，执行计划就是描述SQL语句在数据库中是如何扫描表、如何使用索引的过程。

我们研究面向硬盘的数据库，因此不能假设操作的中间结果能够全部装入内存中。所以我们要依赖缓存池和硬盘的配合来执行运算符算法。

算子执行

我们以JOIN为例，看看算子执行一般流程是怎样的。

排序算子

因为表中的元组在关系模型下没有特定的顺序。而排序（可能）用于ORDER BY、GROUP BY、JOIN和DISTINCT运算符。如果SQL语句里面显式的指名了要使用这些运算符，就要进行排序。

如果需要排序的数据适合内存，那么DBMS可以使用标准的排序算法（例如快速排序）。如果数据不合适，那么DBMS需要使用能够根据需要溢出到磁盘的外部排序，并且更适合顺序而不是随机I/O。

用于大型数据的排序算法是外部归并排序，它是一种分而治之的排序算法，将数据集拆分为单独的块，然后对它们进行单独排序。外部归并排序的排序数据是Key-Value对，其中key是要排序的数据，value是要排序数据所在tuple的其它数据。关于value，有两种呈现方式，称为早物化和晚物化，早物化表示value就是元组数据，随key一起排序，晚物化表示value是元组数据的标识，之后再转化为元组数据。

我们以最基本的二路归并排序来讲解外部归并排序的具体流程，该算法在排序阶段读取每个页面，对其进行排序，然后将排序后的版本写回磁盘。它从磁盘读取两个排序的页面，并将它们合并到第三个缓冲页面中。每当第三页填满时，它就会被写回磁盘，并替换为一个空页。

但是实际上内存中不可能只能容下3个块，所以会有N路外部归并排序，其过程如下图所示

外部合并排序的一个优化是在后台预取下一次运行，并在系统处理当前运行时将其存储在第二个缓冲区中。这通过持续利用磁盘减少了每一步I/O请求的等待时间。这种优化需要使用多个线程，因为预取应该在当前运行的计算进行时进行。

假如系统本身B+树是聚簇的，那么就可以直接用B+树进行排序，但是如果不是聚簇的，那么B+树索引的代价将会远远大于排序的开销。

聚集算子

查询计划中的聚集运算符将一个或多个元组的值折叠为单个标量值。实现聚集有两种方法：排序、散列。

利用排序的原理很好理解，排序天然将重复值聚合到一起，之后的操作就很简单了，在执行排序聚合时，重要的是对查询操作进行排序，以最大限度地提高效率。例如，如果查询需要筛选，最好先执行筛选，然后对筛选后的数据进行排序，以减少需要排序的数据量。

利用哈希的原因是有一些聚集函数是本身不需要排序的，只是排序天然将数据聚集起来了。所以针对这类函数可以用哈希的方法，具体流程为：

0. 先给要聚集的key做一个哈希表，将相同元素放入同一个块里面，并且将冲突元素也放入冲突的块中。如果哈希表比较大，需要将块先存入磁盘中，假如是去重操作，可以提前进行去重，可以缩小块的大小。
1. 之后我们将块中的元素进行重新哈希，因为块中有很多冲突的元素，如果块比较大的话，还可以设置多个哈希函数，确保最后哈希表中相同块元素相同

有一些聚集操作不只需要操作key，例如AVG，聚集起来算另一列的值。这样可以在第二阶段的时候将值也计算到哈希表里，最后也能得到结果。

连表算子

有时为了增加信息的可读性（方便理解），一段信息需要拆分成多个表来存储，当读取的时候需要将多个表JOIN起来。

连接的时候尽量将更小的表放在左侧，左侧的表称为outer table外表或者驱动表

连接操作符生成的输出元组的内容是不同的。它取决于DBMS的查询处理模型、存储模型和查询本身，也就是说JOIN有很多种不同的实现，我们通过对其原理和性能的分析，可以将它们分为三类

• Nested Loop Join
• Sort-Merge Join
• Hash Join

输出数据或输出ID

JOIN输出的结果有两种，一种是直接输出数据，将外部表和内部表中属性的值复制到元组中，然后放入一个中间结果表中，不需要再次查找数据。一种是输出ID，只复制连接键和匹配元组的记录ID。这种方法非常适合列存储，因为DBMS不会查询不需要的数据。如下图所示

输出数据和输出ID，也就是之前提到的物化和非物化的概念

为了衡量JOIN算法的效率，我们用一个模型来描述JOIN效率。如下图所示

总的来说，在某些情况下，会有许多算法/优化可以降低连接成本，但没有一种算法在每种情况下都能很好地工作。

Nested Loop Join

Simple Nested Loop Join

这种类型的连接算法由两个嵌套的for循环组成，循环遍历两个表中的元组，并比较每个唯一的元组。如果元组与连接谓词匹配，则输出它们。

对于数据库来说，表的大小是按所占文件页来说的，并不是看 tuple数量，这就是为什么小表做外表，因为小表的磁盘索引次数少

这种方法的问题在于，缓存池完全用不上，在R表中两个相邻的tuple会用到完全不同的两个缓存。

IO开销：M + (m ×N )

Block Nested Loop Join

事实上，我们完全可以按块来比较索引，而不是按tuple，对于外部表中的每个块，从内部表中获取每个块，并比较这两个块中的所有元组。这种算法执行的磁盘访问更少，因为DBMS会扫描内部表中的每个外部表块，而不是每个元组。

IO开销：M + (M × N) 或者，当缓存池大小为B时 M + ([M/(B-2)] x N)

Index Nested Loop Join

如果数据库已经在join键上为其中一个表建立了索引，那么它可以使用该索引来加速比较。DBMS可以使用现有索引，假设按照索引查询条目的开销为C，则

IO开销：M + (m ×C)

Sort-Merge Join

排序合并联接会根据联接键对两个表进行排序，排序完之后就可以对两个表进行游走匹配，如下图所示

如果一个或两个表已经按照联接属性排序（如使用聚集索引），或者输出需要按照联接键排序，则此算法非常有用。

但是这个算法有可能退化，当两个表的索引列都一样的时候，合并的成本为M*N。这一点的详细信息可以在视频里找到。

IO开销=排序开销+匹配开销

Hash Join

Basic Hash Join

回忆索引循环连接的理念，我们何不用哈希表来构建这样一种索引呢？基础的哈希连接很简单，用外表R表构建哈希表，然后用内表S表来索引匹配R表构建出的哈希表，如下图所示

哈希连接只能用于完整联接键上的相等连接。上面的value也有提前物化和推迟物化的权衡

当S表在匹配哈希表的时候 ，常常出现匹配不上的情况，这时候我们提前用R表构建布隆过滤器，用布隆过滤器来提前过滤掉匹配不上的索引，如下图所示

布隆过滤器的详情可参考布隆过滤器，这一篇给你讲的明明白白-阿里云开发者社区 (aliyun.com)

Grace Hash Join

如果外表比较大的时候，构建的哈希表可能非常大，这时我们需要把部分的哈希表从内存移动到磁盘上，对于移动那部分的哈希表，我们可以参考Grace的方案。

简单来说就是做两套哈希表，都存在硬盘里，对每个哈希冲突分桶。当需要join的时候，能够匹配的key一定在同一个桶里，如下图所示

如果同一个元素过多，那一个桶可能太大，这时候我们对该桶内元素进行rehash，哈希过程

IO开销，构建哈希表需要2(M + N)，而输出结果需要M + N，总的需要3(M + N)

下图是各个方法的开销比较，可以看不同情况(输入)下需要用不同的方法，但总体上sort和hash的方法是比较好的，也是用的比较多的。

计划执行

DBMS将SQL语句转换为查询计划。查询计划中的运算符排列在树中。数据从这棵树的叶子流向根部。树中根节点的输出是查询的结果。通常，运算符是二进制的（1-2个子查询）。同一查询计划可以以多种方式执行。

处理模型

DBMS处理模型定义了系统如何执行查询计划。它指定了查询计划的计算方向，以及在此过程中运算符之间传递的数据类型。有不同的处理模型，对于不同的工作负载（OLAP、OLTP） 有不同的权衡。

这些模型还可以实现为从上到下或从下到上调用操作符。尽管自上而下的方法更常见，但自下而上的方法可以更严格地控制管道中的缓存/寄存器。

如果将不同的处理模型分类，可以分为三类

• lterator Model
• Materialization Model
• Vectorized / Batch Model

lterator Model

迭代模型，也叫火山模型，因为就像火山喷发一样，数据从底部算子一直向上传递到上级算子

如图，对父对象调用Next会调用其子对象的Next。作为响应，子节点将返回父节点必须处理的下一个元组。

这种方法有以下几个特征

• 最大的开销在于函数调用，对于一个大数据表的操作可能会有上千万的函数调用，开销不可忽视

• 几乎用在所有DBMS中，因为它允许数据流式处理，开发很方便

  

• 某些算子会在它的子算子执行返回数据之前一直阻塞，如Join、Subqueries、Order By

• 在这种模型上进行输出控制很简单，因为流式处理支持对单个元组的处理，例如Limit算子的实现

Materialization Model

物化模型是更符合我们对数据库工作流程的直觉印象，每个算子每次处理时都返回其处理的所有元组。最后把所有结果整个输出，即每一个算子只调用一次，一次将结果全部输出。（中间结果由抽象数组存储）

一些OLTP专门的数据库会用这种模型，因为每次查询的检索数据量很小，因此可以省去函数调用的开销。而不适用于具有大量中间结果的OLAP查询，因为DBMS可能必须在操作员之间将这些结果溢出到磁盘。

Vectorization Model

由于两种模型各有优缺点，因此有人将它们两个结合起来，即有函数调用但是也是批处理，具体的，每个算子都实现了下一个函数。然而，每个算子都会发出一批（即向量）数据，而不是一个元组。

• 矢量化模型方法非常适合必须扫描大量元组的OLAP查询，因为对下一个函数的调用较少。
• 矢量化模型允许操作员更容易地使用矢量化（SIMD）指令处理成批的元组。（Intel AVX指令集）

数据读取方法

access method是DBMS访问存储在表中的数据的方式。一般来说，访问模型有两种方法；数据可以从表中读取，也可以通过顺序扫描从索引中读取。一般来讲，数据访问方法可以分为两种

• Sequential Scan
• Index Scan或者Multi-Index Scan

Sequential Scan

sequential scan让算子遍历表中的每一页，并从缓冲池中检索它。当扫描遍历每个页面上的所有元组时，它会对谓词求值，以决定是否将元组发送给下一个算子，DBMS维护一个内部指针，跟踪它检查的最后一页/插槽。

顺序表扫描几乎总是DBMS执行查询时效率最低的方法。有许多优化可以帮助加快顺序扫描：

• Prefetching：提前获取接下来的几页，这样DBMS在访问每一页时就不必阻塞存储I/O

• Buffer Pool Bypass：扫描算子将从磁盘获取的页面存储在其本地内存中（直接读取），而不是缓冲池中。

• Parallelization：使用多个线程/进程并行执行扫描。

• Zone Maps：为页面中的每个元组属性提前计算聚合值（统计信息），当谓词中涉及这些信息是可以提前结束扫描。

  • 

• Late Materialization：延迟拼接元组，直到查询计划的上部。这允许每个算子将所需的最小信息量传递给下一个算子（例如记录ID、列中记录的偏移量）。这只在列存储系统中有用

• Heap Clustering：详情看B+树中相关的部分

Index Scan

在索引扫描中，DBMS选择一个索引来查找查询所需的元组（走索引），DBMS的索引选择过程涉及很多因素，包括：

• 索引包含哪些属性
• 查询引用哪些属性
• 属性的值域
• 谓词组合
• 索引是否具有唯一键或非唯一键

基本索引选择原则是，走完这个索引后，剩下的元素越少，就选哪个索引。

Multi-Index Scan

更高级的DBMS支持多索引扫描。当对一个查询使用多个索引时，DBMS使用每个匹配索引计算记录ID集，根据查询的谓词组合这些集，检索记录并应用可能保留的任何谓词。DBMS可以使用位图、哈希表或Bloom过滤器通过集合交集计算记录ID

数据修改

修改数据库（插入、更新、删除）的操作员负责检查约束和更新索引。对于更新/删除，子运算符传递目标元组的记录ID，并且必须跟踪之前看到的元组（不然可能对同一个数据重复操作）

关于如何处理插入运算符，有两种选择

• 在算子内部物化元组
• 算子插入从子算子插入的任何元组

万圣节问题是一种异常情况，更新操作会更改元组的物理位置，导致扫描操作员多次访问元组。这可能发生在集群表或索引扫描上，这一现象最初是由IBM研究人员在1976年万圣节建造System R时发现的。如上图所示，修改后的Andy被放在了后面，如果不记录，有可能被同一个语句再次修改。

表达式估值

DBMS将WHERE子句（谓词表达式）表示为表达式树（参见图6中的示例）。树中的节点代表不同的表达式类型，

可以存储在树节点中的表达式类型的一些示例：

• Comparisons (=, <, >, !=)
• Conjunction (AND), Disjunction (OR)
• Arithmetic Operators (+, -, *, /, %)
• Constant and Parameter Values（参数）
• Tuple Attribute References（元组引用，如ID）

具体的例子如下图所示：

上图中S.value = ? + 1被当作是一种函数计算，每个元组都要执行一遍这个算子，从表达式树的底层开始直到返回=号的结果。这样有一个效率问题，这只是一个简单的计算成百上千次的重复执行，没必要用表达式树来重复的计算，而是直接计算谓词的结果，我们用一个例子来看：

WHERE 1=1是一个很常见的谓词表达式，因为在优化后的DBMS中1=1检测到重复计算后，其结果被记录下来，每次执行WHERE 1=1到直接结果为ture返回

这就是java高级技术中JIT（just in time）的思想，在java中，重复执行的代码不会进入虚拟机执行，而是直接转化为二进制码执行，提高效率。

并发执行

之前关于查询执行的讨论假设查询由一个用户执行。然而，在实践中，通常有多个用户并行查询。数据库的query plan一定要很好的支持并发操作。

并发执行计划有很多好处

• 提高吞吐（一段时间内输入的查询、算出的结果）、减少延迟（单个查询语句的耗时）
• 提高了对外部请求的响应性和可用性（很好理解）
• 减少了硬件成本，单位时间处理更多请求表示硬件被更好的利用了（相关概念查询TCO）

弄懂数据库并发执行，无非就是弄懂两个方面的架构

• 计划之间的并发执行
• 计划内的并发执行

并发数据库与分布式数据库

在并发和分布式系统中，数据库分布在多个“资源”中，并发数据库和分布式数据库的区别就在于这些资源的分布不同，单个节点DBMS执行的SQL查在并行或分布式DBMS会生成相同的结果

• 在并发数据库中，资源之间彼此接近，通信不仅快速，而且开销小、可靠性高
• 在分布式数据库中，资源可能彼此远离，节点之间的通信成本更高，故障也不容忽视。

处理模型

在DBMS中，一个worker指在数据库中执行操作的单位（想想worker的意思），并发执行从实现的结果来看就是多个worker同时在数据库中运行。worker和资源的调度关系称为处理模型，不同的数据库采用不同的处理模型。一般来说，有三种处理模型比较常见

• process per worker
• process pool
• thread per worker

Process per Worker

每一个worker分配一个进程，这种处理模型非常依赖操作系统的协调，当应用程序请求数据库时，调度器指派一个worker负责这个连接，知道请求完成。这种处理模型的好处是如果一个进程崩溃，不会影响到其他进程，但是性能不高，因此只在比较老的数据库上使用，如DB2、ORACLE、PostgreSQL

Process pool

进程很多的时候非常吃资源，因此自然而然的出现了基于进程池的处理模型，Worker被保存在一个池中，并在查询请求到达时由调度器选择执行。由于这些进程同时存在于一个池中，因此进程可以在它们之间共享查询，这种模型有效的控制了进程的数量

thread per worker

之后各个操作系统实现了一个统一的接口Pthread，可以方便的新建线程，因此催生了现在最常见的处理模型，一个worker一个线程。每个数据库系统只有一个进程和多个工作线程，而多个线程由DBMS完全调度，这种多线程结构有两大好处，一是上下文切换的开销更小，二是不用维护共享数据，因为线程之间天然是数据共享的。（此时不一定需要dispatcher）

在具体的调度问题上，因此 DBMS总是比操作系统知道的更多，所以最好让 DBMS接管调度，包括执行哪一个任务、在哪个 CPU核心上执行、结果存在哪儿等等。

计划间并发

如果并发查询是只读模式的话，那么并发冲突很少，如果并发查询有增删改的话，那么冲突会很多，后面再细讲

计划内并发

成熟的数据库，几乎每一个算子都有并发的版本，并发算子的实现有两大思路

• 将执行任务分配给多个worker，同时执行
• 将任务数据分成多块，再分给多个worker，最后合并结果

具体的说，有三种并发执行方法：intra-operator, inter-operator, and bushy，DBMS负责结合这些方法来优化查询执行

intra-operator

查询计划的运算符被分为独立的片段，这些片段对不同的数据子集执行相同的操作。

在DBMS中，一些并发算子可以做到这一点，例如Gathe、Exchange、Repartition、Distribute等

以一个实际的例子来看

inter-operator

将worker分配到算子上，而不是数据上，这样逻辑上同一层的算子是并发执行的，这种方法被广泛应用于流处理系统中，流处理系统是在输入元组流上连续执行查询的系统。问题在于如果下一层的算子执行太慢，上一层的算子就需要等待数据，有可能会浪费许多时间。

bushy

bushy是inter-operator和intra-operator的结合，具体来看一个例子

在上图中，四表join由四个线程1，2，3，4来执行，其中：

线程1、2：做intra-operator，分别负责两个join算子的执行

线程3、4：做inter-operator，3负责一部分JOIN结果数据的总JOIN，4负责一部分JOIN结果数据的总JOIN，最后合并所有结果

IO并发

面向磁盘的执行瓶颈都在磁盘上，假如磁盘太慢的话并行再优化速度也就那样，所以我们也要重视在磁盘上IO的并发性。

假如一个数据库一整个都在一个磁盘上，那不管有多少并发优化机制，对这个数据库进行操作还是得一步步来。因此，IO并发性的关键在于将逻辑上一起的数据分开到不同的磁盘上，有两种方法，分别是多磁盘并行和数据库分区

多磁盘并行

在多磁盘并行中，OS/硬件被配置为自动将DBMS文件存储在多个磁盘中，也就是RAID系统，这种优化与DBMS本身关系不大，因为文件的操作是对DBMS透明的，DBMS并不知道OS优化的怎么样。

数据库分块

因为数据库文件是保存在文件系统文件夹里面的，所有数据库分块实现比较简单（只要文件分块就行），但是要注意整个数据库的log file要在多个磁盘之间共享。一些DBMS允许指定每个数据库的磁盘位置。

但是将一整个数据库分在多个磁盘则不同，主要有两种方法，vertical和horizontal

vertical

字面意思，将表的一部分属性分到另一个盘上

注意这里的分法非常经典，数据表中经常有一些又大又冷门的数据，将这些数据分开对查询速度提升非常大。

horizontal

比如有100万条数据，将50万条数据分别分开，这种分表要设置partition key，每次查询根据partition key找到不同磁盘上的表。

分表就是分布式数据库的前身，既然本地能够在不同磁盘上分表，那将partition key换成网关，就可以实现在不同主机上分表。

\