Join优化规则及应用层BI系统实践_system-r框架和cascade框架优缺点-CSDN博客

在数据分析场景下，多个表的 Join 语句是很常见的。其中之一的原因为，在数据仓库的建设中，维度建模是一种常用的建设方法，因此数据仓库通常分为事实表和维表，但用户查询时需要完整的维度信息，因此会构建事实表和维表的join。同时，OLAP分析通常不会局限于一个独立的业务分析面，而是需要将业务相关的所有数据整合起来进行分析，此时Join功能起到了一个数据整合的能力。

应用BI系统也是数据分析的其中一个入口，虽然我们可以借助众多开源OLAP引擎的所提供的Join能力来实现多表Join，但以下两个场景仍然要求应用BI系统自身的引擎具备Join功能：

灵活自助分析中，用户可以选择任意选择感兴趣的数据进行整合分析，可能也包括用户本地上传的数据。这些数据可能不会存在同一个OLAP引擎中，甚至可能归属于不同类型的OLAP引擎。
OLAP引擎提供了秒级的查询性能，但受限于硬件能力，它所存储的数据不可能无限制增加。在实际业务场景中，OLAP数据库分库和拆分集群的情况是很常见的，这种情况下就无法依赖于OLAP引擎自身所提供的Join功能。

本文从应用BI系统的Join需求角度出发，首先对优化器的类型进行概要介绍，接着介绍了查询优化器中和Join操作具有关联关系的优化规则，最后从应用层的实际情况介绍应用层BI系统在实践过程中遇到的一些问题和思考。

需要说明的是，应用层BI系统的查询引擎选型不在本文的讨论范围，同时Join相关的优化并不仅仅指对Join操作进行处理的规则，还包含了可以跨越Join节点执行以提高Join效率的相关规则。同时优化规则是可以不断扩展的，本文列举的优化规则并不是理论上的全集，仅仅只是其中一部分。

二、查询优化器概述

查询优化器在数据库中一个重要且复杂的组件，它再一定程度上决定了数据库的性能。如上图，优化器接收一个被parser解析的逻辑算子的代数表达式树，优化器由一些从逻辑表达式树到最优等价物理表达式树的映射组成。换言之，优化器对算子重排序并选择实现算法。

数据库查询优化技术主要包括查询重用技术、查询重写规则、查询算法优化技术、并行查询优化技术、分布式查询优化技术及其他方面（如框架结构）的优化技术，这6项技术构成了一个“广义的数据库查询优化”的概念。一个优秀的查询优化器并不只会使用其中一种技术，而是需要从性能角度出发进行结合。但不同的查询优化器框架也具有不同的解决思路。这里我们不对具体的查询优化技术进行介绍，而是对于业界较为通用的三种查询优化器框架简要介绍其思路，以帮助我们更好地理解后续的Join相关规则。

2.1 System R Optimizer

System R 是一个具有开创性的项目，该项目是SQL 语言的第一次实现，从此SQL便成为标准关系型数据库查询语言。System R 也是第一个论证了关系型数据库管理系统能够提供良好的事务处理性能。System R 中的设计决策以及一些基本的选择算法（例如查询优化中的动态规划算法）对后来的关系型系统有巨大的影响。

System R将查询优化分为逻辑优化和物理优化两个阶段，逻辑优化根据规则对执行计划做等价变形，物理优化则根据统计信息和代价计算将逻辑执行计划转化为能更快执行的物理计划。同时System R的大牛Pat Selinger提出了Dynamic Programming和Interesting Orders这两个重要算法和思想，影响了所有后续的数据库优化器设计和实现。

System R 优化器的目标是在给定可用的数据结构和访问路径的情况下，找到一种低成本的方法来执行SQL语句。

System R在使用Dynamic Programming构建最低执行代价的物理执行计划时使用的是自底向上的算法，这一点和Volcano和Cascade不一样。自底向上的算法会先计算基表的访问路径（Access Path），通常来说存在几种：顺序扫描、索引扫描、组合索引等，而存在多个索引时，每个索引都视作一个访问路径。接着，枚举两表Join，这里同时还需要对Join的物理实现进行枚举，所以第二层的状态会比第一层多许多。一层层往上搜索，即可得到多表Join的执行计划。

在搜索过程中，每一层不需要保留所有的组合，而是保留代价最低的即可。但需要考虑到一个问题，两表Join的最优解，未必能得到三表Join的最优解，例如两表用了HashJoin，那么输出的结果会是无序的；相比之下，如果用MergeJoin，两表Join可能不是代价最小的，但是在三表Join时，就可以利用其有序性，对上层的Join进行优化。

为了解决这个问题，引入了Interesting Order，即上层对下层的输出结果的顺序感兴趣。因此自底向上枚举时，A JOIN B不仅仅是保留代价最小的，还需要对每种Interesting Order的最小代价的Join进行保留。如果元组顺序是由查询指定的，那么它是Interesting的。一些查询可能没有指定感兴趣的顺序(即返回无序的结果)。为了优化连接成本，需要在产生Interesting order的最优方法和产生无序结果加上排序成本的最优方法之间进行选择。注意，可以将多路连接流水线化。

虽然 System R 优化器框架大大提升了数据库处理复杂 SQL 的能力，但也存在一定缺陷，比如：

扩展性不好。每次添加优化规则都需要考虑新的规则和老的规则之间的关系，需要对优化器非常了解的同学才能准确判断出新的优化规则应该处在什么位置比较好。另外每个优化规则都需要完整的遍历整个逻辑执行计划，添加优化规则的心智负担和知识门槛非常高。
搜索空间有限。搜索空间一方面因为优化规则难以添加导致比较狭小，另一方面，逻辑优化要求该优化规则一定在各个场景下都有收益才行，但在数据库面临的各种场景中，总有一些优化规则在某种数据分布下有收益，在另一种数据分布下没有收益，需要根据数据的分布估算代价来判断是否启用这些优化规则，因为这个原因，进一步导致一些优化规则不能添加到这个搜索框架中，或者添加后需要人工的通过开关来开启或关闭该优化规则。

2.2 Volcano Optimizer

Volcano/Cascades Optimizer 是经典的优化器框架，作者都是 Goetz Graefe。Volcano和Cascades Optimizer是当前业界最常用的两种框架，开源SQL引擎Calcite就实现了Volcano Optimizer。其中Cascades Optimizer是基于Volcano Optimizer基础之上的进一步优化，我们将会在后一章进行介绍。

Volcano Optimizer遵循了以下5个设计原则：

查询处理都是基于关系代数。需要定义代数算子、代数等价定义以及合适的实现算法。选择合适的算法就是查询优化。因此Volcano optimizer定义了两个类别的代数：逻辑代数和物理代数，将逻辑代数映射到物理代数的过程就是优化器，过程中使用了逻辑代数的转换和从逻辑到物理代数的基于代价的映射。
使用规则和模式来处理等价转换。规则被定义为关于模式的特定知识表达。查询优化中等价变换所需要的代数定律知识可以很容易地用模式和规则来表示。另外，我们需要关注规则的独立性，它保证了规则的模块化，优化过程中它被搜索引擎组合起来并独立地执行转换。查询优化是数据库系统中最复杂的一部分，模块化有利于后续的维护和扩展。</