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)层

                                                //89层是实现动态规划算法

                        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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值