让你的数据库飞起来！Greenplum查询优化解析

2022-06-08 14:39:53

本文将主要包括两大部分，部分是查询优化器的介绍，第二部分是查询优化的具体处理过程，整个过程将被分为四个阶段：查询树的预处理，扫描/连接优化，扫描/连接之外的优化，计划树的后处理，我们将在本文中一一展开。

首先我们来介绍一下查询优化器是做什么的。对于给定的查询语句，查询优化器会找到“代价”小的查询计划。这里的“代价”在不同的应用场景会有不同的维度或者是定义，比如可能是找到一个执行时间短，或者找到一个执行占用资源少的查询计划。

对于同一个查询语句，一般可以由多种方式去执行，因此查询优化器尽可能去遍历每一种可能的执行方式，找到“代价”小的执行方式，并把它转换成可执行的计划树。

下图是一个例子，A JOIN B on a.i = b.i，对于这个Query，我们有几种执行方式，这里我们来介绍三种执行方式。种执行方式中，我们可以先对b做一个索引扫描，再对a做一个顺序扫描，再去执行一个Nested Loop。第二种执行方式中，我们可以对a做一个顺序扫描，把扫描的结果建一个哈希表，再对b做一个顺序扫描，后用Hash Join得到一个结果。第三种方式是对a做一个顺序扫描，根据a.i为序的键值做一个sort排序，再对b做一个索引扫描，后做一个Merge Join，得到后的结果。

大家可以看到这三种执行方式，后面的“代价”（cost）信息是不同的，优化器会根据cost信息选出一个“代价”小的方式，形成后的执行方案。

前面介绍了查询优化器，这里我们再来介绍一下查询计划。一个查询计划就是一个由计划节点组成的树，每个计划节点代表了一个特定类型的处理操作，计划节点中包含了执行器执行所需的全部信息。在执行时，计划节点产生输出元组，一般来说，扫描节点从数据表中获取输入元组，大部分其他节点从它们的子计划节点中获取输入元组，并处理产生输出元组。

计划节点可以分为三个类型，

扫描节点

包括顺序扫描，索引扫描，位图扫描等；

连接节点

包括Nestloop，hash，merge等；

非SPJ节点

包括Sort，aggregate，set operations（UNION等）

下面我们再来看一个例子。对于例子中的Query，查询计划就如下图所示，其中Seq Scan on a 和Seq Scan on b都是扫描节点，扫描的结果去做Hash Join，是一个连接节点。Hash Join之上，做了一个HashAggregate，这是一个聚集节点，后再对聚集的节点做排序，这是一个排序节点。

在讲完查询优化和查询计划，我们再来看一下Greenplum查询优化的具体处理过程。Greenplum查询优化主要包括四个阶段，具体过程请看下图。

接下来我们来逐一解析这四个阶段分别做了什么。

1. 简化常量表达式

在表达式中，有一些常量表达式，在做计划时，可以对其做一些简化，例如对于函数表达式，可以对下图的两种类型做简化：

函数本身是严格的

如果一个表达式的输入是NULL，输出也是NULL或者是FALSE，在这种情况下，我们就可以说这个函数就是“严格”的。我们可以通过CATELOG表中的属性，来查看某个函数是否是严格的，如果函数本身是严格的，并且输入的参数中包含一个NULL值，在做计划时，就可以用NULL来代替这个函数表达式。

函数本身是“IMMUTABLE”

函数是”IMMUTABLE”是指如果输入是固定的，则输出也是不变的。我们也可以通过CATELOG表中的属性查看这个函数是否是“IMMUTABLE”。如果函数本身是“IMMUTABLE”的，并且输入参数全都是常量，则可以先求值，并用常量去代替函数表达式。

对于布尔表达式，在一些情况下也可以坐简化，比如下图的两种情况。

对于CASE表达式，如果某个分支条件是一个常量的话，也可以做简化。比如下图的这个例子中的2+2=4，可以把整个CASE表达式简化为x+1。虽然在else里与1/0，但是由于我们做了简化，因此表达式就不会报这样的除0的错误。

我们为什么要在做计划这个阶段去简化常量表达式呢？通过简化常量表达式可以带来以下好处：

仅需做一次计算，而不是为每行元组都做一次计算
视图展开和函数内联都可能会带来新的常量表达式简化的机会
简化常量表达式也为统计信息类的函数减少了计算量

2. 内联简单的SQL函数

查询树的预处理在早期做的第二件事是内联简单的SQL函数，比如下图的例子中，SELECT incr4(a) FROM foo可以内联成SELECT a+4 FROM foo。而这里不止做了个内联，还做了个常量表达式的简化。

通过内联简单的SQL函数，可以

避免SQL函数调用的代价
为简化常量表达式提供新的机会

3. 提升子链接

子链接是指出现在表达式中的子查询，通常出现在WHERE或者JOIN/ON子句中。

比如下面这个例子中，子查询出现在了一个WHERE子句中，因此就被归类为一个子链接，在这种情况下，我们就可以把一个EXISTS的子链接变成一个SEMI JOIN。

SELECT * FROM foo WHERE EXISTS (SELECT 1 FROM bar WHERE foo.a = bar.c);
复制代码

下图是转换之前的查询树，从图中我们可以看到，EXISTS_SUBLINK在转换之前出现在WHERE的约束条件中。

在转换后，这个子链接就变成了一个SEMI JOIN，这就是一个子链接的提升过程。

4. 提升子查询

子查询一般是以范围表的方式存在，通常出现在FROM子句中。在例子中的子查询出现在了FROM后面，在提升后，这个查询语句是对三个表做内连接，我们可以以任何顺序对这三个表做内连接，同时，我们观察到foo和bar之间有个连接条件，因此我们想先对foo和bar做内连接，再对baz做连接，从而得到一个更好的查询计划。而在提升之前，我们就不得不对bar和baz做连接。

SELECT * FROM foo JOIN (SELECT bar.c FROM bar JOIN baz ON TRUE) AS sub ON foo.a = sub.c;
复制代码

下图是提升之前的查询树的内部结构图。我们可以看到SUBSELECT是属于父查询的一个范围表中的一项。

而提升之后，我们可以看到三个表就变成了三个内连接的关系，这样我们就可以对三个表任意的交换顺序，从而找到一个代价低的查询计划。

通过把子查询提升到父查询之中，就可以使子查询参与整个计划搜索空间，从而找到更好的执行计划。否则，我们不得不为子查询单独做计划，然后在为父查询做计划时把子查询当做是一个“黑盒子”。

5. 消除外连接

消除外连接也是在预处理过程中做的一项很重要的事情。首先我们来看一下下图的两个表：学生表和选课表。学生表中我们有两个学生，选课表中我们有一个学号为1的学生，选了编号为100的课，得了60分。如果我们希望找出哪个学生选了哪些课，得了多少分，我们会对这两个表做连接。如果我们用INNER JOIN来做连接，则得到个表格的结果，只有一名选了课的学生的信息。如果对LEFT JOIN，我们发现不仅有已经选了课的1号学生，2号学生虽然没有选课，但是也出现在了结果中，只不过他的选课我们用NULL来填充。如果在这个左连接后面加一个WHERE条件，结果和INNER JOIN一样。在这种情况下，我们就可以把LEFT JOIN变成一个INNER JOIN。

总结一下：外连接的上层有“严格”的约束条件，且该约束条件限定了来自nullable side的某一变量为非NULL值，则外连接可以转换成内连接。

SELECT ... FROM foo LEFT JOIN bar ON (...) WHERE bar.d = 42;
复制代码

现在我们来看消除外连接的第二种情况。首先我们做了个LEFT JOIN，则出来两个结果，其中没有选课的学生的选课信息为NULL。接着我们又做了个LEFT JOIN，并加了个WHERE条件，限定enrolled.sid is null，此时就把前面的结果中选课信息不为NULL的结果过滤了。此时这个LEFT JOIN做的就是ANTI JOIN的事情。

当我们查看语句的计划，我们会发现Greenplum已经把它变成了一个ANTI JOIN。这里就是消除外连接的第二种情况。

外连接本身有“严格”的连接条件，且该连接条件引用了来自nullable side的某一变量，且该变量被上层的约束条件限定为NULL值，则外连接可以转换成反半连接(Anti Join)。

SELECT * FROM foo LEFT JOIN bar ON foo.a = bar.c WHERE bar.c IS NULL;
复制代码

阶段：查询树预处理（后期）

在早期，我们对查询树本身做了一些转换，在查询树预处理的后期，我们会做以下的事情：

分发WHERE和JOIN/ON约束条件
收集关于连接顺序限制的信息
消除无用连接
etc.

1. 分发约束条件

我们来看下分发约束条件，一般来说，我们期望可以尽可能的下推约束条件。如果只有内连接，我们可以把一个约束条件下推到它的“自然语义”位置。如果存在外连接，那么约束条件的下推可能会受到阻碍，从而无法下推到它的“自然语义”位置。对于被外连接阻碍的约束条件，我们通过让它的“required_relids”包含进外连接所需要的所有基表，从而避免该约束条件被下推到外连接之下。

首先，我们来看一下哪些约束条件会被外连接阻碍。先看种情况，左边的例子中，这个约束条件只引用了一个student表，那我们是否可以把它下推到对这个student表的扫描上呢。在Greenplum的计划中，student.sage = 18这个约束条件并没有被下推到对student的扫描上，而保留在了student和enrolled的外连接做JOIN的这一层，这就是被外连接阻碍的一个例子。

在右边的Query中，我们做了一些改动，用了一个子查询的形式，通过查看计划，我们成功的将sage=18下推到了student这张表上，用这个计划进行执行，我们发现生成结果和左边的结果不一样。

如果外连接本身的连接条件引用了non-nullable side的表，那么该连接条件不能下推到外连接之下，否则我们可能会丢失一些null-extended元组。

我们再来看一下会被外连接阻碍的第二种情况。首先我们看一下左边，我们可以看到这个约束条件只引用了enrolled这个表。那我们是否可以把这个约束条件下推到对enrolled表的扫描上呢。当我们看查询计划时，我们发现，它并没有这么做，而是保留到了连接这一层。执行后，我们发现它的结果只有一行结果。

当我们如上图右边所示，变换一下SQL语句，希望把约束条件下推，我们发现在plan中，成功的把约束条件下推到对enrolled表的扫描上，但是执行结果和左边不一样，多了一行结果。

如果外连接上层的约束条件引用了nullable side的变量，那么该约束条件不能下推到外连接之下，否则可能会多出一下null-extended元组。

2. 连接顺序限制

当有外连接存在时，就不可以随意的交换连接顺序，因为外连接会在一定程度上限制连接顺序的交换。非FULL-JOIN可以和一个外连接的左端(LHS)自由结合，通常非FULL-JOIN不可以和外连接的右端(RHS)结合。大家可以看一下下图的两个例子。在左边的例子中，大家可以从查询计划中看到，a和c先做了个inner join，然后在和b做了个left join。而右边的例子中，大家可以看到查询计划中，虽然a和b并没有连接条件，依然对a和b做了left join，再和c做了个INNER JOIN。

3. 消除无用连接

在预处理的后期，做的另一个事情就是消除无用连接。如果满足下图中的三个条件，无用连接可以被消除。下图的这个例子中，这个LEFT JOIN就可以被消除。

第二阶段：扫描/连接优化

在这一阶段中，主要处理查询语句中FROM和WHERE部分，同时也会考虑到ORDER BY信息。这一部分都是由“代价”来驱动的。

扫描/连接优化是这样做的：

首先为基表确定扫描路径，估计扫描路径的代价和大小
利用动态规划算法，搜索整个连接顺序空间，生成连接路径
在搜索连接顺序空间时，需要考虑到由外连接带来的连接顺序的限制

在动态规划的过程中，

首先为每一个基表生成扫描路径
为所有可能的两个表的连接生成连接路径
为所有可能的三个表的连接生成连接路径
为所有可能的四个表的连接生成连接路径
...
直到所有基表都连接在了一起

大家可以发现，这个动态规划是level by level来完成的。下图是SELECT * FROM A JOIN (B JOIN C ON B.j =C.j) ON A.i = B.i 的动态规划过程。

其实这个过程，“代价”是非常高的，n个表的连接，理论上有 n! 个不同的连接顺序，遍历所有可能的连接顺序是不可行的。我们使用一些启发式办法，减少搜索空间。对于不存在连接条件的两个表，尽量不做连接，把一个大的问题，分解成多个子问题。比如下图中的这个例子中，我们将10个表的连接变成了多个小问题。再对每个子问题做动态规划的过程，降低了复杂度。

第三阶段：扫描/连接之外的优化

在这个阶段，我们会做以下的处理：