Trafodion优化器简述,第二部分

在前一篇博文中，笔者着重介绍了Dynamic Programming。今天，将简单介绍Cascades，也就是Trafodion的优化器框架。

回顾Dynamic Programming

Dynamic Programming利用两个限制条件（Heuristics）将本来庞大的搜索空间减小到合理范围。单词“heuristics”在讨论优化器的文章中被经常用到，这个单词本来就怪，在不同的上下文中还有不同的含义，十分讨厌，需要特别留意。在这里，Heuristics指的是一些‘靠谱’的经验法则，用来减少搜索空间。

比如人们买彩票，买6个数字，全对就中奖，一个比较靠谱的heuristics是：不买6个相同的数字。这个heuristics还是比较有道理的，应该可以用概率论来证明；如果一个彩民说他喜欢用自己的生日作为数字来买彩票，这种heuristics就是不靠谱的。

在优化器的实现中，有靠谱的Heuristics，它们不会将最优解漏掉；也有很多不靠谱的Heuristics，会将最优解漏掉，即最优解在Heuristics剪掉的搜索空间中。但是这类不靠谱的Heuristics却会极大地加快优化器的执行效率，因此也是很有用的。

很多研究在于找到靠谱的heuristics，Trafodion的优化器在20年的漫长岁月中，有很多天才的程序员奉献了青春，并且在无数的应用场景下进行了验证，终于找到了一些重要的Heuristics，可惜笔者也所知不多，尚无法成文。

Dynamic Programming自底向上地搜索问题空间，非常的优雅。不过它却有两个缺点：扩展性差；多表join还是效率不高。导致其应用很有局限。

为了克服DP的缺点，人们展开了很多的研究，最终诞生了两个主要的技术：Cascades和starBurst。StarBurst在IBM的产品线中应用，Cascades在微软和HP应用。不过正如笔者多次强调，其他的数据库厂商比如Oracle，或者IBM自己，很可能拥有某种更加先进的优化器算法，但是笔者尚未听说有公开的资料描述。

本文主要关注Cascades。

1. Cascades 基本思想

本质上Cascades的基本原理和Dynamic Programming也差不多。对于给定的一条SQL，首先需要枚举所有的等价方案，形成一个搜索空间；然后高效地在这个搜索空间中找到最优解。一条方案是否更优，是通过比较两个方案的成本来决定的。

并且，Cascades优化器也同样设定了Pat的两个Heuristics：

·         只考虑Linear型的join变换；（这个并不是Cascades的限制，而是Trafodion的限制）

·         基于Principle of Optimality。

读者应该对以上两点有所了解，不再赘述。因此，严格说来Cascades也是Dynamic Programming的一种变形，并没有本质的突破。

Cascades和Dynamic Programming的最大区别在于搜索空间的枚举算法和搜索的顺序不同。其中搜索顺序是关键，更好的枚举算法在starBurst中也已经解决；cascades作为同类中的最先进算法还在于其不同的空间搜索顺序。

1.1  空间搜索顺序

Cascades搜索的顺序是自顶向下。这个自顶向下是一个关键，在第一部分的结尾，我特意强调大家留意DP的自底向上那个关键词。

先讨论自顶向下搜索顺序。这个顺序的主要优点在于，可以应用bound and branch算法：在搜索的过程中，始终维护当前已经找到的执行计划成本的upper bound，就是已知的最优成本，如果在搜索的过程中遇到一个分支，其成本高于upper bound，那么就直接丢弃。叫做prune，剪枝。这样就可以减少需要搜索的次数。这个Heuristics是靠谱的，不会把"最优解"剪掉。

比如上例，Cascades在搜索的过程中，先遇到Hash join，假设其成本为100。在遇到Merge join的时候，如果T1的成本为101，则不用再深入右边的孩子节点，prune it！这样，相对于Dynamic Programming，就有机会进一步减少搜索空间。这是Cascades或者自顶向下算法的主要优点。

我想这里还是需要举例，认真的读者一定还不满足，要追根究底。我觉得还是举个实例，那些和笔者类似的、最爱钻牛角尖的读者或许会需要。其他的读者可以略过1.1.1。

1.1.1 一个例子

问题描述

我们的问题是T1 join T2。

假设T1有2种访问方法：T1-Scan；T1-Index。T2有2种访问方法T2-Scan，T2-Index。

join的方法有3种：NLJ、SMJ、HJ。

利用DP

自底向上，第一层需要考虑4种不同方案。

对于单个Table访问，由于两种访问方法的physical property，即排序不同，因此最优方案必须保留4个[（T1-Scan, no-order），(T1-Index，order)，(T2-Scan, no-order)，(T2-index, order)]。

进入第二层

先考虑NLJ：T1 NLJ T2和T2 NLJ T1。每个T1，T2的访问各有2种，所以需要比较的方案是8个：(T1-scan NLJ T2-scan)，(T1-scan NLJ T2-Index), (T1-index NLJ T2-index), (T1-index NLJ T2-index), (T2-scan NLJ T1-scan), (T2-scan NLJ T2-index), (T2-index NLJ T2-scan), (T2-index NLJ T2-index)

休息一下，现在DP一共访问了8+4=12个不同的方案了哦。

还在第二层，开始考虑SMJ，对于SMJ，no-order的方案不需要考虑，因此这里只需要访问4个不同方案。

一共访问的不同方案是16个了哦。

还在第二层，开始考虑HJ，需要比较8个不同方案。

结束，一共需要访问24个不同的方案。

利用Cascades

现在看Cascades，自顶向下，深度优先遍历。

root是 T1 join T2，产生6个方案：(T1 NJ T2),(T2 NJ T1),(T1 SMJ T2),(T2 SMJ T1),(T1 HJ T2),(T2 HJ T1)。

先看（T1 NJ T2），为了确定其成本，需要先看T1的访问方案。Cascades枚举出两个新子方案（T1-Scan）,(T1-Index)。还是无法决定(T1 NJ T2)的成本，还需要看T2的访问方案，又枚举两个(T2-Scan),(T2-Index)。需要进行成本比较4个方案，找到T1/T2的最佳方案。

此时，深度优先遍历回到(T1 NJ T2)。这个时候可以决定这个节点的最优成本了，假设是(T1-Scan NJ T2-Scan)，成本为100。再假设(T1-Index) 的成本为101.

深度优先，接下来看(T2 NJ T1)，比较成本，因为T1,T2的成本已经知道，不需要再深入子节点访问了。这里大家需要注意：cascades自顶向下的策略要求一个节点可能会被访问多次；而Dynamic Programming，任何一个节点只会被访问一次。Cascades采用MEMO数据结构来解决这个问题，它能够“记忆住”曾经访问过的节点，第二次访问相同节点的时候，直接使用已经“记忆住”的结果。因此到目前为止，Cascades访问了6个不同方案，其中T1/T2被重复访问，但归功于MEMO的记忆能力，多次访问的代价和一次访问是相同的，所以我们将目前访问的不同方案个数记为6。

接下来，深度优先的过程来到了(T1 SMJ T2)。本来，需要访问1个子方案，(T1-index SMJ T2-Index)，但是我们发现(T1-Index)的成本已经是101，高于目前的upper bound:100。所以这个分支被直接prune了。(T1-Scan) 因为不满足排序要求，所以不予考虑。

接下来，(T2 SMJ T1)，同样被prune。接下来(T1 HJ T2)，同样，凡是包含(T1-Scan)的方案都被prune。只需要考虑2个不同方案 (T1-Index HJ T2-Index), (T1-Index HJ T2-Scan) 。到目前为止，Cascades访问的方案个数为6+4=10。

最后(T2 HJ T1)，这里会prune两个，访问2个。最终Cascades需要访问的不同方案数目为12。比DP的24个减少了很多。意味着搜索空间极大减少。

这里笔者需要进一步说明什么叫做“访问一个方案”，访问一个方案就是计算该方案的成本，并进行成本比较。”比较”可以用一条机器指令完成，然而成本计算是一个代价极高的操作。笔者在这里无法详细描述，cost需要综合考虑cardinality，CPU，I/O，Network。是一个CPU-bound的计算过程，不是几条机器指令即可解决的问题。所以减少访问一个方案，就可以减少很多的CPU消耗，而优化器的时间都花费在CPU计算上，减少访问量，就可以减少整体的计算时间。我们希望在ms级别完成优化器的工作，所以减少搜索空间是最重要的。

而自底向上是不可能应用这个bound and branch理念的，因为必须从最底层开始遍历所有子问题，没有办法prune分支。所以采用自顶向下，在理论上能够比自底向上的Dynamic Programming更优秀，因为需要进行成本比较的方案数目要少些，而这是优化器自身最大的代价衡量标准。

Cascades这个搜索算法使得其相对于Dynamic Programming更加高效，有可能进一步缩小问题空间。不过实际上，单纯地利用这个自顶向下的搜索算法，在实践中其实还是很难有效缩小问题空间。最明显的问题在于，如果初始upper bound太高，会导致没有多少机会prune分支。因此经验和用户实践对优化器的发展至关重要，团队在漫长的岁月中逐步积累在用户真实环境下的一些问题，不断调整，才一步一步地得到了一个比较完善的优化器。目前优化器采用2阶段优化，第一个阶段利用一些重要的Heuritics将搜索空间限定在很小很小却很可能包含最优解的范围内，极快地得到一个初始upper bound。第二阶段再进行完整的Cascade空间枚举和搜索过程，以便找到最优解。

不过和前辈相比，Trafodion将数据存储在HBase中，和之前基于磁盘的存储大有不同，因此过去的Heuristics很多都失效了，还需要在未来实践中不断地调整。

有人说，optimizer更多的是一门艺术而不是科学，也有一定道理，一些Heuristic是很艺术的，即一般人都看不懂，也没有什么道理。这不就是艺术么？

1.2 枚举算法

Cascades采用transform rules来指导优化器枚举等价计划，而不是将变换都hardcode到代码中。开发人员定义转换规则，比如join的交换律是一条规则；结合律是一条规则；view merge是一条规则，等等，等等。

枚举的时候，Cascades遍历语法树，对于访问到的节点，执行用户定义的规则，产生新的等价方案，并添加进搜索空间，同时在这个过程中进行搜索和成本比较，执行bound and branch。

Dynamic Programming基本上是hardcode，即代码写好了：对于Table访问方法，我要先考虑full table scan，然后考虑index scan；对于join，我要先考虑nest loop join，再考虑merge join，每次我要交换join的顺序。这样的话，修改起来，比如加入了hash join，就很麻烦。

笔者在网上找到一篇PostgreSQL的优化器代码描述^[3]，摘录如下：

planner(){――第（1）层   standard_planner(){――第（2）层       subquery_planner(){――第（3）层  ...           grouping_planner(){――第（4）层 //生成查询计划               query_planner(){――第（5）层                     make_one_rel(){――第（6）层//生成新关系                        set_base_rel_pathlist(): //找出所有访问基表的方法                         make_rel_from_joinlist (){――第（7）层                         //决定使用什么查询优化的算法（遗传算法还是动态规划算法）                            生成一棵查询树的所有访问路径，得到最优的路径                          standard_join_search (){――第（8）层 //实现动态规划算法                                                                             为一个查询找出所有可能的连接路径                             join_search_one_level () {――第（9）层                                                 //第8、9层是实现动态规划算法                         make_join_rel () {――第（10）层                                  //查找或创建一个RelOptInfo节点，                                    用于表示2个关系的连接结果，                                    把2个关系的连接路径加入这个RelOptInfo节点                                  build_join_rel() {――第（11）层                                  Find or build the join RelOptInfo                                  Consider paths using each rel as both outer and inner                                 //尝试把每个关系作为外关系、作为内关系 。。。

作者也是数据库开发人员，其博文都值得一读。

可以看到DP在枚举搜索空间的时候通过层层的函数调用，hardcode了如何枚举不同的join顺序；先做什么优化，后做什么优化，等等。因此如果想扩展，加入一个新的优化策略，那么就很难，要对上述代码进行大的手术。

Cascades对此进行了改进，优化器的编写人员可以定义transform rule，让优化器在运行过程中依据这些rule对query进行等价变换。每一个transform rule都是一个C++类，扩展的时候定义一个新的C++类即可。 其实也是面向对象思想的一个应用，Cascades在90年代中期出现，好像正是OO开始流行的时期。因此技术之间互相影响，很多新技术都是必须具备天时地利，才顺理成章地出现了。

比如我们认为inner join符合交换律，因此可以定义一个transform rule：

当Cascades遇到inner join时，就可以调用这个规则。

1.3 MEMO

Cascades的另一个核心概念是MEMO，它解决的问题是：等价方案组成的搜索空间究竟应该如何存储在内存中？

Dynamic Programming不需要考虑这个问题，因为它不需要保存”搜索空间”，只需要维护当前已经找到的、下一层的所有最佳方案列表。之前搜索过的方案可以立即丢弃。

而Cascades的深度优先遍历却意味着一个节点可能会被访问多次，整个问题空间必须被完整地保存在内存中，否则下次遍历到的时候就找不到了。而且，应用transform rule会改变执行计划树的形状，导致遍历路径改变，因此还必须适应随时的变换。

存储完整的搜索空间带来一个大问题：如果等价方案有成千上万，如何有效的存储？Cascades的MEMO技术解决了这个问题。

 MEMO的全称是MEMOization。它可以有效地保存存储空间，并支持记忆功能，是非常聪明的一个设计。

MEMO由一组Group组成，每个Group代表了一个Expression，表达式，比如 A Join B，就是一个表达式。用Group(A Join B)代表，在这个Group中，保存了A Join B的所有等价方案，比如( A NJ B) (A SMJ B) (B NJ A)等等等等。表达式分为两种，逻辑表达式，比如 A join B；物理表达式，比如 A NestLoopJoin B。可见物理表达式是一个真正的SQL操作符实现。如果一个表达式完全由物理表达式组成，我们就称之为Plan。

比如A join B这个问题。初始的MEMO非常简单，如下：

一共有3个Group，分别保存三个初始SQL语法树的三个节点。后续，当Cascade对Group 1运用一个交换律的转换规则时，就生成了一个新的方案(B join A)，也称为一个Expression。则将Expression  (Join 3,2)加入Group1。当运用物理转换规则，比如join采用Nest Loop Join时，也产生一个新的Expression，即NJ(3,2)，加入Group1。因此一个Group就是一个等价的Expression的集合。当需要引用其他group时，比如Join A, B需要引用访问A的方案，就直接用Group 3来表示A的访问方案。每个Group有一个32bit的groupID，因此在Group1中，引用Group 2只需要32bit的ID，而不需要在Group 3中保存完整的Group 2，节约了空间。

2. Cascade的运行机制描述

用户定义transform rule，用户对MEMO进行初始化，Cascades开始启动，对初始MEMO进行遍历，遍历到的每个节点递归地进行优化操作，即找到该节点的最优方案。Transform有可能改变MEMO的形状，即添加新的Group；Transform也可以不改变MEMO的形状，但是会向Group内添加一个新的Expression。此外MEMO能够记住自己曾经被优化过的记录，一个Group如果被优化过，再次被访问时，就可以直接返回优化结果。

Cascades的运行过程类似一个堆栈式的虚拟机。有一个task堆栈，调度器不断地从堆栈顶端读取task，运行它。Task的运行过程中，可能会向堆栈中添加新的task。初始的时候task堆栈中只有一个task，然后知道堆栈为空，执行结束。

Casacdes有5种task。我打算在下一节用一个例子来说明各种task的细节，不过笔者实力不够，只能神似，绝不是Cascades真实的运行过程，仅为了给读者一个简单的印象。

3. 一个例子

最好还是举个例子，Cascades非常复杂，一个简单的包含join的例子实在太耗费篇幅。因此在这里，我们只考虑一个最简单的query:

  SELECT * FROM T1 WHERE C1 > 10;

初始的时候，MEMO只有一个Group：

Group 1中只有一个逻辑表达式”Scan A”。在task堆栈中，只有一个初始task，OptimizeGroup(group1)。

从task堆栈pop出一个任务，即OptimizeGroup，执行这个task，该任务对保存在Group中的每一个Expression生成一个新的OptmizeExpression任务。在Group 1中，只有一个Expression。所以OptimizeGroup向task堆栈push一个新任务OptimizeExpression(ScanA)

前面的OptimizeGroup任务执行完毕，调度器从task堆栈pop出下一个任务,OptimizeExpression(Scan A)。

回顾OptimizeExpression任务的逻辑，它的任务是对Expression执行transform rule，即对其进行变换，这是通过调用ApplyRule任务完成的，同样，将ApplyRule push到任务堆栈。该任务结束。

现在执行ApplyRule任务，这个任务对Expression执行所有可行的等价变换，对于Scan A，我们假设有两个Rule： Full-Table-Scan A和Index-Scan A。那么就会生成两个新的Expression，将他们加入Group 1。MEMO有了变化。

ApplyRule对于每个新生成的Expression进行判断，如果是一个逻辑Expression，则继续调用OptimizeExpression任务；如果是物理Expression，就调用CreatePlan。在本例中，ApplyRule任务对Expression SCAN A发现了两个物理转换规则，因此将两个CreatePlan任务压入task堆栈。图就不上了。

CreatePlan任务接着被执行，这里是真正应用Principle Of Optimality的地方。它对Expression的每个child执行OptmizeGroup任务，因此如果是Join，的话，这里就会生成深度遍历孩子节点的OptimizeGroup任务。在本例中，Scan操作符没有孩子节点了，所以就结束了。如果是join，那么就开始一个递归的深度优先遍历的过程。CreatePlan还需要将每个孩子节点的最优方案组合起来，作为本Group的最优方案，保存在MEMO中。

两个CreatePlan任务都执行完后，又各自不产生新的task，整个优化过程结束。最优方案保存在唯一的Group 1中。如果有多个Group，则最终的执行方案就是各个Group的最优解的组合。

以上例子不太合适，不能看出Cascades如何进行bound and branch，用join会比较好。可是一旦用一个join的例子，篇幅又不够，笔者已经力不从心了。

本文仅仅希望给大家一个简单的概念。

3. 小结

Cascades克服了Dynamic Programming的缺点，但Cascades自身也有一些缺点：第一是实现难度太大；第二，Cascades的MEMO数据结构对内存的要求比较高，需要配置较大的内存。由于这些缺点，目前只有微软和Tandem（现在的HP）采用了这个算法。

但是笔者辛辛苦苦写了这篇博文，可并不是想说Cascades的缺点，而是想强调：在笔者眼中，Cascades代表了SQL优化器的最先进技术，至少是开源界的最先进技术。开源的数据库代表就两个：PostgreSQL和MySQL。我想大家普遍都应该认同PG的优化器比MySQL先进。我已经反复说过，PG使用的还是Dynamic Programming。Cascades优于Dynamic Programming是本文的中心思想；况且，当join的表数目大于12时，PG就放弃使用DP，而是采用了一个基因算法。笔者没有认真研究过基因算法，不过在PG的dev maillist中，PG开发人员自己都对基因算法颇有微词，正如其名所示，搜索的过程类似基因突变，可能变好，也可能变坏。。。因此笔者尚没有计划去认真学习基因算法。

MySQL默认采用greedy search算法(optimizer_prune_level=1)。如果不用Greedy Search，MySQL就采用穷举法。窃以为还是非常不靠谱的。

笔者把Greedy Search叫做爬山算法：如果把山顶比喻为执行计划的最优解，爬山的过程就是优化器找最优解的过程，那么优秀的登山者应该是以爬到最顶为目标。

上山的路可不是爬楼梯，并不是始终向上的，来看看笔者的涂鸦：

贪婪算法从山脚出发，走到第一个小山坡，发现两条路，一条向下（红色），一条向上（绿色）。它毫不犹豫地选择绿色，因为它的原则就是不肯往下走，贪婪嘛。而真正的山顶却需要向下走一下，是曲折的。贪婪的登山者只肯往上走，不肯临时往下走看看。结果只能爬到小山峰，而最高峰就错过了。如果大家爬过山，那么就能明白，这种策略应该是很大的几率爬不到山顶。因此，任何一个严肃的数据库产品都不会采用Greedy Search算法。

单从优化器的角度来看，在笔者眼中：如果Oracle，SQL Server是莫扎特、贝多芬，如雷贯耳；那么Trafodion可能是马勒，不那么有名气，但其实也非常不错；PostgreSQL可能是柴可夫斯基；那么MySQL就是Taylor Swift了。。。流行的很，粉丝很多，但是很业余。

这种比喻非常刺耳，MySQL的拥趸一定有点儿不舒服，笔者在此道歉，为了彰显Trafodion优化器的领先，不得不故作惊人之语。J

笔者承认，MySQL有非常优秀的设计，比如多线程模型，Trafodion应该学习，这里仅仅比较一下它的Optimizer。

以上的爬山比喻毫无科学的严谨精神，MySQL只有在optimizer_search_depth=1的情况下才会那么做，不过optimizer_search_depth再大一些就变成穷举法，小一点的话，也就是多看几步，比如optimizer_search_depth=2，就看两步，即比较下面两步的综合成本，然后还是‘贪婪’地选最好的一个走下去，所以本质是一样的。笔者实在没有力气描述了，在这里copy一张图^【2】吧。该图作者对MySQL优化器写的非常到位，是MySQL的专家，读者可以进一步研究参考资料2，验证笔者是否公允：

*   *  *  *  *

结束语

这篇写的有点儿长，因为笔者打算结束关于Optimizer的介绍，不想再写另外一篇了。Cascades是SQL的核心难点。。。之一。。。但其实能够做的工作并不多。

大部分真正有价值的工作在于开发各种靠谱的Heuristics和Transform Rule。此外如何设计直方图，如何进行精确的成本分析，也是优化器开发的重要内容，可惜笔者所知甚少，也不是兴趣所在，恐怕难以成文。

又是夜深人静，有了大把的自由时间，便又舍不得睡觉了。回头看看，居然已经是第五篇博客，从来没有写过这么多东西。手头还有好几个bug要修，不过还是先放一边不管吧，看会儿网易的回帖轻松一下，再来根红双喜，心中一沉，又涨了5毛。2015，股市又一次疯狂，张江办公楼旁边的木槿开了又谢，生命就是在不断的轮回。。。我还是放一首Taylor Swift的歌吧。

参考资料

[1] The Cascades Framework for Query Optimization

[2] How does the MySQL Optimizer work，by Timour, SergeyP, Igor

[3] PostgreSQL查询优化器源码分析--整体流程 http://blog.163.com/li_hx/blog/static/183991413201322982935548/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30206145/viewspace-1656908/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30206145/viewspace-1656908/