【数据库内核】基于代价的优化器引擎之Join Order

目录

概述

Tidb Join Order 算法简介

贪心算法缺点

PostgreSQL Join Order 算法简介

算法对比情况

结论

---

概述

执行计划优化中有一个非常重要的问题，就是连接顺序（Join Order）。简单来说，当用户执行一个查询需要Join多张表时，优化器需要决定按照什么样的顺序将这些表连接在一起，而这个“顺序”我们称为Join Order。在很多时候，Join Order对于执行效率的影响是决定性的，在最佳的Join Order中，哪怕改变其中一部分，也有可能引起数量级的执行时间变化，可谓失之毫厘谬以千里。因此，不管数据库如何发展，Join Order始终是一个非常重要的话题。

那么多表连接算法(Join Order)需要解决两个问题:

• 多表连接的顺序: 表的不同的连接顺序，会产生许多不同的连接路径;不同的连接路径有不同的效率。
• 多表连接的搜索空间:因为多表连接的顺序不同，产生的连接组合会有多种，如果这个组合的数目巨大，连接次数会达到一个很高的数量级，最大可能的连接次数 是 N !(N 的阶乘)。比如，N=5，连接次数是 120 ; N=10，连接次数是 3 628 800 ; N=20，连接次数是 2 432 902 008 176 640 000。所有的连接可能构成一个巨大的“搜 索空间”。如何将搜索空间限制在一个可接受的时间范围内，并高效地生成查询执行 计划将成为一个难点。

那针对于这些挑战，我们看看一些开源数据库是如何实现的来扩展一下我们的眼界。

 

Tidb Join Order 算法简介

目前 TiDB 中使用的算法是 Join Reorder 算法，又称贪心算法。简单描述下贪心算法的过程。

贪心算法的前提是确定源点，只找当前步骤的最优解，是一种深度优先的解法，算法复杂度是O（n²）找到后继续深入下一层，直至达到终点。

比如上图从A到G，使用贪心算法的路径是A->B->D->G算法，代价是1+2+6=9，很明显这并不是最优解，最优解我们肉眼可以看出来是A->C->F->G，代价是2+3+1=6。所以我们看贪心算法并不是全局最优的，但是优点是算法复杂度低，不会将时间都浪费在计算代价上了，因为如果关联的表特别多，那么代价的计算是指数级增长，所以贪心算法虽然不是最优解，但是在连接表的数量很大的情况下具有一定优势。

举一个实际的SQL案例说明如下所示

SELECT  * FROM  A,  B,  C WHERE  A.a = B.a   AND C.a = B.a

以三个表 A、B、C 的 Join 为例。

第一步、首先获取所有参与 Join 的节点，将所有节点按照行数多少，从少到多进行排序。

 

第二步、选定其中最小的表，将其与其他两个表分别做一次 Join，观察输出的结果集大小，选择其中结果更小的一对。

 

第三步、进入下一轮的选择，如果这时是四个表，那么就继续比较输出结果集的大小，进行选择。这里只有三个表，因此就直接得到了最终的 Join 结果。

 

以上就是当前 TiDB 中使用的 Join Reorder 算法。Mysql数据库对于多表关联也采用的是贪心算法。

 

贪心算法缺点

贪心算法只能保证局部最优，但是没有办法保证全局最优。例如下面的案例

A JOIN B JOIN C

 

原始表数据如下所示:

• A有1000条数据
• B有100条
• C有10条数据

 

利用贪心算法只查找Join结果行数最少的方式，未必是结果最优的。例如

三个表之间存在一定的Join谓词使得

• A JOIN B返回10000条数据
• B JOIN C返回200条数据

如果采用最朴素的NestLoop Join算法，

• 第一个执行计划需要处理100 * 10 + 200 * 1000 = 200100次循环
• 第二个执行计划需要处理1000 * 100 + 10000 * 10 = 200000次循环

因此第二个执行计划会更优一点。贪心算法没有找到合适的解。

 

PostgreSQL Join Order 算法简介

从底向上进行的，即从叶子(单个表)开始算作一层，然后由底层开始对每层的关系做两两连接 (如果满足内连接则两两连接，不满足内连接则不可对全部表进行两两连接操作)，构造出上层，逐次递推到树根。

下面介绍具体步骤。

• 第一步、初始状态。构造第一层关系，即叶子结点，每个叶子对应一个单表。
• 第二步、归纳。当层数从第1到n-1，假设已经生成，则如何求解第n层的关系? 方法为:将第n-1层的关系(有多个关系)与第一层中的每个关系连接，生成新的关系，放于第n层，且每一个新关系，均求解其最优路径。

以上虽然分为两步，但实际上步骤2多次执行，每一次执行后生成的结果被下一次使用，即每层路径的生成都是基于上层生成的最优路径的，这满足最优化原理的要求。

动态规划算法与System R算法相比，增加了中间关系的大小估算。还有的改进算法，在生成第n层的时候，除了通过第 n-1 层和第一层连接外，还可以通过第n-2层和第2层连接，通过第n-3层和第3层连接......

传统多表连接树有如下形态

 

举一个实际的SQL案例说明如下所示

SELECT  * FROM  A,  B,  C,  D WHERE  A.col = B.col   AND A.col = C.col   AND A.col = D.col

 

上面的查询语句生成最优查询计划的过程如下:

第一步、构建第一层树叶，初始化层。

 

第二步、构建第二层数据；使用第一层和第一层连接得到。

 

第三步、构建第三层数据；使用第二层和第一层连接得到。

 

第四步、

<1> 构建第四层数据；使用第三层和第一层连接得到。

<2> 构建第四层数据；使用第二层和第二层连接得到(紧密树)。

 

使用图表表示，动态规划运行过程表

层级	说明	产生的结果
4	第四层通过第三层与第一层关联和第二层与第二层关联得到	{A, B, C, D}, {A, B, D, C} {B, A, C, D}, {B, A, D, C} {A, C, B, D}, {A, C, D, B} {C, A, B, D}, {C, A, D, B} {A, D, B, C}, {A, D, C, B} {D, A, B, C}, {D, A, C, B} {B, C, A, D}, {B, C, D, A} {C, B, A, D}, {C, B, D, A} {B, D, A, C}, {B, D, C, A} {D, B, A, C}, {D, B, C, A} {C, D, B, A}, {C, D, A, B} {D, C, B, A}, {D, C, A, B}, {A, B} | {C, D}, {A, B} | {D, C} {B, A} | {C, D}, {B, A} | {D, C} {C, D} | {A, B}, {C, D} | {B, A} {D, C} | {A, B}, {D, C} | {B, A}
3	第三层通过第二层与第一层关联得到	{A, B, C}, {A, B, D} {B, A, C}, {B, A, D} {A, C, B}, {A, C, D} {C, A, B}, {C, A, D} {A, D, B}, {A, D, C} {D, A, B}, {D, A, C} {B, C, A}, {B, C, D} {C, B, A}, {C, B, D} {B, D, A}, {B, D, C} {D, B, A}, {D, B, C} {C, D, B}, {C, D, A} {D, C, B}, {D, C, A}
2	第二层通过第一层关联得到	{A, B}, {B, A}, {A, C}, {C, A}, {A, D}, {D, A}, {B, C}, {C, B}, {B, D}, {D, B}, {C, D}, {D, C}
1	树叶，初始层	{A}, {B}, {C}, {D}

然后通过代价估算找出最优的查询计划。

综上，tidb或者mysql使用贪心算法只能得到局部最优执行计划，但是计算最优解所消耗的代价较小，而postgreSQL使用动态规划能够得到最优执行计划，但是计算最优解算法复杂度较高，代价较大。

 

算法对比情况

除了上述的贪心算法和动态规划算法，还有其他的一些算法，都可以用于查询优化多表连接的生成，如爬山法、分支界定枚 举法、随机算法、遗传算法、模拟退火算法或多种算法相结合等。

多表连接算法整理如下表所示:

算法名称	特点与适用范围	缺点
启发式算法	适用于任何范围，与其他算法结合，能有效提高整 体效率	不知道得到的解是否最优
贪婪算法	非穷举类型的算法。适合解决较多关系的搜索	得到局部最优解
爬山法	适合查询中包含较多关系的搜索，基于贪婪算法	随机性强，得到局部最优解
遗传算法	非穷举类型的算法。适合解决较多关系的搜索	得到局部最优解
动态规划算法	穷举类型的算法。适合查询中包含较少关系的搜索， 可得到全局最优解	搜索空间随关系个数增长呈指数增长
System R 优化	基于自底向上的动态规划算法，为上层提供更多可 能的备选路径，可得到全局最优解	搜索空间可能比动态规划算法更大一些

 

 

结论

可以说Join是关系数据库的核心功能，Join Order又是提升Join执行性能的一个重要手段，Join Order的好坏一定程度上影响的优化器的好坏。

Join Order的算法是有多种的，每一种都有自己适用的场景和优势。所以好的优化器一般都会衡量目前处于哪一种场景下，针对于这种场景，选择更加适用的算法。

 

参考资料

• https://www.youtube.com/watch?v=t2R0-xcKw44
• https://www.cockroachlabs.com/blog/join-ordering-pt1/
• https://docs.pingcap.com/zh/tidb/dev/join-reorder
• 《数据库查询优化器的艺术: 原理解析与SQL性能优化》

分享大数据行业的一些前沿技术和手撕一些开源库的源代码
微信公众号名称：技术茶馆
微信公众号ID    :    Night_ZW