Greenplum查询优化解析

发布于 2022-5-18 17:56
浏览
0收藏

 

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


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


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


下图是一个例子,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信息选出一个“代价”最小的方式,形成最后的执行方案。

Greenplum查询优化解析-开源基础软件社区

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

 

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

 

  • 扫描节点
    包括顺序扫描,索引扫描,位图扫描等;
  • 连接节点
     包括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查询优化的具体处理过程。Greenplum查询优化主要包括四个阶段,具体过程请看下图。

Greenplum查询优化解析-开源基础软件社区

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

Greenplum查询优化解析-开源基础软件社区

1.

简化常量表达式

 

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

 

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

 

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

Greenplum查询优化解析-开源基础软件社区

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

Greenplum查询优化解析-开源基础软件社区

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

Greenplum查询优化解析-开源基础软件社区
我们为什么要在做计划这个阶段去简化常量表达式呢?通过简化常量表达式可以带来以下好处:

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

 

2.

内联简单的SQL函数

 

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

Greenplum查询优化解析-开源基础软件社区

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

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


3.

提升子链接

 

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

 

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

SELECT * FROM foo WHERE EXISTS (SELECT 1 FROM bar WHERE foo.a = bar.c);
=>
SELECT * FROM foo *SEMI JOIN* bar ON foo.a = bar.c;

 

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

Greenplum查询优化解析-开源基础软件社区

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

Greenplum查询优化解析-开源基础软件社区

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;
=>
SELECT * FROM foo JOIN (bar JOIN baz ON TRUE) ON foo.a = bar.c;

 

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

Greenplum查询优化解析-开源基础软件社区

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

 

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

 

5.

消除外连接

 

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

Greenplum查询优化解析-开源基础软件社区

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

SELECT ... FROM foo LEFT JOIN bar ON (...) WHERE bar.d = 42;
=>
SELECT ... FROM foo INNER 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。这里就是消除外连接的第二种情况。

Greenplum查询优化解析-开源基础软件社区

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

SELECT * FROM foo LEFT JOIN bar ON foo.a = bar.c WHERE bar.c IS NULL;
=>
SELECT * FROM foo *ANTI JOIN* bar on foo.a = bar.c;

 

第一阶段:查询树预处理(后期)

 

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

 

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

 

1.

分发约束条件

 

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


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

Greenplum查询优化解析-开源基础软件社区

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

 

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

 

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

Greenplum查询优化解析-开源基础软件社区

当我们如上图右边所示,变换一下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。

Greenplum查询优化解析-开源基础软件社区

3.

消除无用连接

 

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

Greenplum查询优化解析-开源基础软件社区

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

 

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

Greenplum查询优化解析-开源基础软件社区

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

 

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


在动态规划的过程中,

 

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


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

Greenplum查询优化解析-开源基础软件社区

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

 

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

 

在这个阶段,我们会做以下的处理:

 

  • 处理GROUP BY, aggregation, window functions, DISTINCT
  • 处理集合操作,UNION/INTERSECT/EXCEPT
  • 如果ORDER BY需要,添加最后的SORT节点
  • 添加LockRows, Limit, ModifyTable节点

 

做完这一步,我们几乎得到了一个完整的计划树。但我们还需要做一些后处理。

 

第四阶段 计划树的后处理

 

在这一阶段,我们需要把代价最小的路径转换成计划树,并且调整计划树中的一些细节:

 

  • 展平子查询的范围表
  • 把上层计划节点中的变量变成OUTER_VAR或是INNER_VAR的形式,来指向子计划的输出
  • 删除不必要的SubqueryScan, Append等节点

 

做完这一步,我们就得到了完整的计划树,并可以将该计划树交予执行器去执行。

 

这就是查询优化的所有内容,欢迎大家继续关注《深入浅出Greenplum内核》系列后面的直播内容。

 

文章转自公众号:Greenplum中文社区

分类
标签
已于2022-5-18 17:56:52修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐