优化器Optimizer可以说是Trafodion最值得骄傲的技术，是皇冠上最值钱的那颗明珠。其他的SQL on Hadoop方案还在试图完善SQL语法支持，发展的快些的，比如phoenix刚开始尝试CBO。而Trafodion的CBO优化器已经诞生了20年，笔者觉得应该是遥遥领先的。即便是在传统RDBMS领域，NonStop SQL/MX的优化器（即Trafodion的优化器）也是常被提及的一个优秀代表。
从本文开始，笔者打算挑战一下自己，尝试描述这个令人生畏的技术主题。究竟能写多少，我也不得而知。写一篇关于优化器的文章，这个想法已经有好几年了，但始终未写出一字，究其原因，首先是自己学艺不精，不想出来献丑；其次也没有动力，优化器的实现可以说是一个冷门专业，常年隐藏在舞台后面，无人关心。现在，Trafodion开源了，作为参与其中的小小一员，我也想拿出点儿开源精神来，因此有了动力，觉得应该大力介绍这个幕后英雄。不过笔者依然学艺不精，所以以下不免胡言乱语，各位读者还要做好准备。同时希望同行中的高手能不吝赐教。
言归正传。
optimizer是SQL数据库中最复杂，最难的一个技术专题。虽然整个数据库行业在过去的几十年中不断的探索，然而直到今天优化器依然有很多未能解决的问题。Oracle是数据库行业的翘楚，人人都爱Oracle。可是即便对于Oracle这样先进的数据库产品，在实践中，却常常遇到query的性能问题。在笔者印象中，Oracle DBA的主要价值之一还是SQL调优。帮助客户调优甚至成为了一个蛮有赚头的行业，过去曾有“DBA日记”等流行一时的书籍，笔者挑灯夜读，熬过了好多个不眠之夜。作者都是Oracle的大牛，专门为普通用户解决SQL性能难题，就像古代的武林高手行侠仗义、帮助弱小一样精彩。
这说明给定一个查询，并不是随便谁谁谁都可以找到最优的SQL方案，而是需要很多的知识和经验，所谓没有金刚钻揽不了瓷器活。按理说，人类的智商比机器要高很多，这个对人类来说都很困难的问题，交给计算机，让它找出最优的方案，可以想象究竟有多难啊。
然而，也有观点认为，SQL优化这件事还是交给机器更加合理，面对海量的可选方案，人类是不可能找到最优解的。因此PostgreSQL团队从来也不愿意支持hint功能，因为他们认为，连机器都找不到最优解，渺小的人类就更加不可能了。
究竟是完全交给数据库优化器调优，还是依赖DBA调优，孰是孰非，笔者对此也没有确切的回答，因为鄙人没有深厚的调优经验，对SQL优化器也所知有限。但却愿意跟大家分享一下自己对优化器的基本理解，各位读者也或许可以体会笔者为什么说话如此矛盾。。。
1.优化器基本概念
不知道是否科学，笔者斗胆将SQL优化器分为两大类：第一类是基于规则的优化 (Rule Based Optimization, RBO) ；第二类是基于成本的优化 (Cost Based Optimization) 。
1.1 基于规则的优化RBO
RBO就是根据固定的经验法则对SQL语法树进行等价变形，生成更加高效的等价语法树，完成基于经验规则的优化。
比如将查询条件尽量下推，在任何情况下都是有益的。举个例子【1】，给定一条SQL:
SELECT S.sname
FROM Reserves R, Sailors S
WHERE R.sid=S.sid AND
R.bid=100 AND S.rating>5
原始的执行计划如下：
假设左边的Reserves表查询得到10000条数据；Sailors查询到10000条数据，如果做Nested Loop Join，需要10000*10000次循环。
利用经验规则，将查询条件下推到Scan操作符：
假设符合条件bid=100的Reserves查询只返回1条数据，而符合Rating>5的Sailors查询也只返回1条数据，那么join只需循环一次。相比之前的1010次循环，执行效率提高了很多。
退一步来讲，这种等价变换至少不会带来坏处，经过条件过滤后返回数据永远也不可能变多。因此优化器只要发现这类pattern就可以毫不犹豫地执行变换，被称为基于规则的优化。RBO除了例子中提到的条件下推之外，还有很多，比如将子查询转换为join，view merge等几乎每一个商业版数据库都支持的经验型转换规则 。不过本文旨在介绍优化器最一般的概念，就不再深入。
发展到今天，笔者所知的所有关系型数据库的优化器都已经是CBO。但是RBO中所采用的变换规则也基本上都融入CBO中，并统一进行处理。比如在Trafodion中，RBO的变换都由Normalizer完成，经过这种‘规范化’的语法树再输入optimizer进行基于成本的优化。因此了解RBO也是有意义的。
1.2 基于成本的优化(CBO)
一条SQL可以有非常多的等价执行方案，究竟哪一条是最优的，单靠RBO的经验规则，在很多情况下是无法做出判断的。
最简单的例子就是多表的join顺序。比如3张表做inner join，A join B join C。其中A表有1,000条数据，B表有100,000条数据；C表有100,000,000条数据。
首先我们需要了解，inner join符合交换律，A join B等价于B join A；
inner join也符合结合律，A join (B join C)等价于 (A join B) join C。
为了简单起见，让我们假设A表的所有数据在B和C中都符合join条件，B中的所有数据在表C中也都符合join条件。此外，数据库只会Nest Loop Join，且没有索引。嗯，最坏不过如此了吧。
有了以上假设，那么
· A join B就会返回1000条数据；
· A jon C 也返回1000条数据；
· B join C 返回 100,000条数据；
现在让我们考察两个join顺序：( A join B) join C 和 A join (B join C)
首先A join B，采用Nest Loop Join，需要1000 * 1000,000 次循环，返回1000条数据。然后返回的1000条数据和表C进行 join，又需要1000 * 100,000,000次循环。一共需要100000000000+1000000000=1,001,000,000,000次循环。
第二种join顺序，首先B Join C，需要100,000* 100,000,000次循环；返回100,000条数据。然后用这100,000条数据和表A进行Join，需要100,000 * 1,000次循环，返回1000条数据。一共需要10000000000000+100000000=110,000,100,000,000次循环。
第二种join顺序需要的循环数是第一种join顺序的100倍。因此选择第一种join顺序比第二种要高效。
然而这种选择是无法靠RBO完成的，因为必须知道每张表的数据分布和个数才可以做出决定。此外join条件千奇百怪，无法预知给定条件下join的结果集的大小。因此如果不实时地进行成本分析，无法决定join的合理顺序。经验规则没有用。
为了解决这个问题，最直观的方法就是列举出所有等价的join顺序，然后逐一比较它们的成本，选择最优的一个方案。这就是CBO的基本思想，即对于给定SQL，首先利用等价变换的规则，枚举出所有的等价方案，形成一个搜索空间，然后利用算法在搜索空间中找出成本最低的一个方案。
然而，SQL的等价变换规则众多，最终形成的搜索空间十分巨大，因此如果采用简单的穷举法，对于复杂的query，其搜索过程将非常耗时，甚至根本无法做到：或者是因为没有那么大的内存存放完整的搜索空间，或者搜索耗时太久，比如1万年。。。因此，人们才不断地研究试图找到最好的搜索算法，然而直到今天，依据笔者的浅见，这个问题还是没有圆满的解决。
接下来介绍解决这个搜索问题的几个方法，笔者的见识有限，只能探讨自己略知的几个，学术界和工业界应该还有其他的优化器实现在我的知识范围之外。
笔者能够找到的，有公开描述和讨论的只有为数不多的几个：比如HP NonStop SQL的Cascades算法，也就是Trafodion采用的优化器实现框架(此外微软的SQL Server也采用了Cascades)；IBM的StarBurst算法，在DB2中应用； PostgreSQL的Dynamic Programming和基因算法；MySQL的贪婪搜索方法。
Oracle的：我不知道！！！
2. Dynamic Programming
IBM的System R是第一个成功的关系式数据库项目，虽然貌似没有商业化，但该项目开创了很多经典技术，其中之一就是优化器。System R的大牛Pat Selinger提出了Dynamic Programming和Interesting Orders这两个重要算法和思想，影响了所有后续的数据库优化器设计和实现。此后的几十年，坊间出现了多个不同的优化器实现方法，但严格说来都是Dynamic Programming的变形。
Dynamic Programming
让我们首先来了解一下Dynamic Programming。
Pat Selinger在设计优化器的时候，首先说明了一些限定条件。
第一个限定条件针对Join顺序变换的类型。很拗口，因为笔者真的不清楚这个概念的正确描述方法，找不到合适的文字，只能希望各位读了下面的例子会有所理解。
Join符合交换律和结合律，因此给定一个N-way join，可以有非常多的等价变换。这些不同的变换通常根据形状被分为两类，查看下图所示的4表join。
上图中的(a)叫做Linear型树，也有人叫left deep tree；(b)叫做Bushy型树。这两个不同类型，即笔者所说的“Join顺序变换的类型“。Pat将自己的任务限定在“只处理Linear型树的变换，不处理Bushy型”。原因如果考虑Bushy型变换，等价方案的个数将变得很多：(2n-2)!/(n-1)!。而Linear型的不同join顺序则为n!。具体细节可以进一步研究参考资料[3]。
对于数学不好的人，比如我，上面两个公式究竟有多大差别？可能没有什么特别的敏感，第一反应是好像也没有多大差别么，bushy公式中还有个除法呢。。。这里给出一张标明了计算结果的数字表格，您就会有些直观的概念了：
| join table个数 | Linear型 | Bushy型 |
| 1 | 1 | n/a |
| 2 | 2 | 2 |
| 3 | 6 | 12 |
| 4 | 24 | 120 |
| 5 | 120 | 1,680 |
| 6 | 720 | 30,240 |
| 7 | 5,040 | 665,280 |
| 8 | 40,320 | 17,297,280 |
| 9 | 362,880 | 518,918,400 |
| 10 | 3,628,800 | 17,643,225,600 |
| 11 | 39,916,800 | 670,442,572,800 |
| 12 | 479,001,600 | 28,158,588,057,600 |
| 13 | 6,227,020,800 | 1,295,295,050,649,600 |
| 14 | 87,178,291,200 | 64,764,752,532,480,000 |
| 15 | 1,307,674,368,000 | 3,497,296,636,753,920,000 |
请大家一定要看下15-way join那一行！假设我们的系统一秒钟可以处理1亿个这样的join顺序，那么对于15-way join，比较每一条bushy型join顺序，需要3497296636秒，即110年。。。
所以Pat那么聪明的人也仅仅将自己需要解决的问题限定为“仅考虑Linear型join”。而且多数关系型数据库都有类似的限制，据笔者所知，仅有PostgreSQL在搜索空间中包含Bushy join，但这也可能是PostgreSQL在12个表join的情况下就无法使用标准的Dynamic Programming优化器，而需要使用基因算法的原因了吧。
15-way join鄙人是没有亲眼见过，但是10-way join在HP的内部BI项目中还是存在过的，而Trafodion的前身SeaQuest都能够轻松处理。
Pat的第二个限定条件是principle of optimality。意思是给定一个复杂问题，人们可以将问题分解为N个小一点儿的子问题，父问题的最优解可以等价为其所有子问题的最优解的组合。即一个问题有n个子问题组成，找到每个子问题的最优解，然后将这n个最优解组合，就是父问题的最优解。。。
上面这段话我自己看都觉得烦，唉，常年写程序，用的最多的只有if/else/for。以至于写文章的时候语言贫乏，下图给出了一个例子，应该能胜过我的千言万语：
这个思想简单又实用，但是它不是很科学。因为会漏过真正的最优解，因为各个子问题的最优解不一定就能组成上层问题的最优解，上层问题的最优解可能包含某个子问题的非最优解。因此Principle of Optimality减小了搜索空间，但却可能漏掉最优解，所以这也是一个限定条件。读到这里，追求完美的人恐怕要失望了，然而问题总是这样，理想是一回事儿，现实则面目全非。。。虽然看似不完美，Pat女士却因为这些贡献成为了IBM的Fellow，后来又成了高管，还在美国的什么ACM之类的学术机构尽享荣华富贵呢。
这两个限制条件将问题空间大大缩小，这样优化器搜索的时间才能够控制在合理的范围。优化器的设计目标是在尽可能少的时间内找到尽可能好的执行计划。
好吧，来看Dynamic Programming吧。其实她的思想和我们在日常生活中的基本思想相同：凡事先挑简单的干。
让优化器找到子问题的最优解相对简单。因此先从树的最底层，即最简单的子问题开始查找最优解，找到每个子问题的最优解。然后这些子问题的最优解组合在一起，就形成上一层问题的最优解，这样，自底向上的层层查找，最终找到根问题的最优解。
不知道读者对上面这段话的感想如何。每次想阐述复杂的思想，我的笔就不好用了。。。多年的经验表明，描述技术问题的最佳途径还是举例。
让我举例来看看Pat在其最早的论文中所描述的原汁原味的Dynamic Programming方法吧。
4个表的join问题中，最底层的问题是单个表的访问问题，比如T1的访问可以有多种选择，全表扫描，Range扫描，index扫描，单键值抽取(unique access)等等。先找到T1访问的最佳方案，这个比较简单，顺序比较各种access方案即可。同理，找到T2, T3, T4各自的最佳访问方法。记录下这些最佳子方案，然后向上一层，找两个表join的最佳方案，比如T1-T2两个表join本来有很多种可选方案：T1-index-scan join T2-index-scan；T1-table-scan join T2-table-scan；T2-index-scan join T1-table-scan。。。可是，采用Dynamic Programming，T1和T2最优的访问方法已经找到，所以在第二层，只需要考虑4种方案：采用nest loop join，T1 join T2还是T2 join T1；然后考虑采用merge join，T1 join T2还是T2 join T1；通过比较它们的cost，找到这4个方案的最佳方案。
用这种方法找到所有两个表join的最佳方案，保存下来。再进入第三层，如此重复一直到最顶层。duang的一下，找到了最优解。
这就是笔者理解中的Dynamic Programming。
读者可能会问：在考虑2表join的时候，为啥只考虑Nest Loop Join和Sort Merge Join?
这是因为在Pat女士设计System R优化器的时候，Hash join还没有发明。Hash Join 是Hansj?rg Zeller最先提出并在NonStop SQL中实现的(http://en.wikipedia.org/wiki/Hash_join 参考external link)。Hans目前仍然奋战在Trafodion开发团队第一线，coding，修bug。
这个Dynamic Programming将n-way join的不同顺序的排列组合个数从 n!数量级(仅linear join) 进一步降低到了n*2n-1数量级。应该是少了不少，理解这个需要非常好的数学头脑，笔者数学不行，算不出来，也没有心思去查找确切的参考资料，所以偷个懒，有劳读者自己思考了。。。反正就是搜索的空间大大减少了。
Interesting Ordering
Pat在System R中还提出了另一个非常有价值的思想，即“Interesting Ordering”。在前文所述的4表join的例子中，Dynamic Programming会首先搜索最底层问题的最优方案，比如表T1的最佳访问方案。T1的不同访问方法本身的代价有高有低，DP仅考虑这些方法本身的代价，找到成本最低的一个。
但是，不同方法产生的结果集存在一种特性，这种特性会影响后续执行的效率，这就是数据的ordering，即是否排序。如果用户query中有order by，那么比较两种scan代价的时候就不能简单地比较各自的成本，因为一个代价小的scan a产出了乱序的结果集；另一个代价高的方案b产出了排序的结果；那么最终孰是孰非还未可知。
更有甚者，假如数据被取出后，被送到Join操作符，一般有三种可能的join方案：nest loop join, sort merge join, hash join。其中SMJ的代价往往是最小的，但是它却要求输入的数据必须排序，因此如果底层返回的数据是乱序的，优化器便不能选择SMJ，而必须从hash join和NLJ中选择一个，而在一些情况下SMJ的代价是更小的，因此T1的两种访问方法a和b，虽然a本身的代价小些，但是b却可以产出排序的数据时，仅仅采用Dynamic Programming，就明显地错过了最佳方案，即SMJ。
为此，Pat提出了”Interesting Ordering”的概念，即每一个方案都保留一项属性，即ordering，如果数据是排序的，就将该属性设置为true；否则为false。而DP在比较两个方案时，如果拥有不同属性，则不能简单地认为a比b好，而是两个都作为最优解保留下来。这样，上层的优化过程就不会错过更优的方案了。
后来人们发现，不仅ordering很重要，还有其他一些特性也很重要，比如partition信息在分布式执行过程中也是很重要的参考指标。因此后来的人们将这个”Interesting Ordering”思想扩展为”Physical Property“，并在其他的优化器技术中得到了更加广泛的应用。
不过”Interesting Ordering”仅仅是对DP的一种修正，从整体上将，System R的优化器的核心就是Dynamic Programming。
Dynamic Programming小结
DP历史悠久，并得到了广泛应用。据笔者所知PostgreSQL还采用标准DP算法；IBM DB2在产品线中采用了DP，和DP的升级版本StarBurst；其他的商业数据库笔者便不了解了。
Dynamic Programming有一些严重的缺点 。
DP的问题主要在于扩展性。扩展性有两方面的含义：第一，虽然Dynamic Programming的编程比较容易，但是它在枚举等价执行计划的过程中采用了hardcode的方法，比如考虑join的不同方法，先遍历NLJ方案，再遍历SMJ方案，当需要加入新的join方法时，需要修改大量代码。如果增加新的operator，比如aggregation，那么修改就更加困难。
此外Dynamic Programming对Join顺序优化的问题非常适合，但是却不容易适用其他的优化方法，比如对GroupBy或者Union的优化。
所以采用DP框架的优化器很难应用新的优化技术，其主要的现实意义仅在于找到最优的join次序。然而实际上还有其他许多重要的优化技术和join次序无关。比如：join之后还需要执行GROUP BY，如果将GROUP BY移动到Join下面，往往也是等价的变换，而且GROUP BY往往会限制tuple的产出个数，因此这种变化有可能生成更好的执行计划，然而这类优化对DP来说就很困难了。
因此采用DP，很难或者无法实现类似的更多种类的优化策略，意味着数据库的优化器会遗漏最优的执行计划，而执行非最优的执行计划，比如没有把上图中的groupby下推，失去了找到这种方案的机会。
其次Dynamic Programming仍然需要搜素非常大的搜索空间，当join的表很多， DP的效率很低，因为它还需要O(n*2n-1)的空间复杂度，人们希望找到更加有效的方法来减少搜索空间。
尽管Dynamic programming有这些缺点，它还是为后续所有的优化器实现给出了标准的模板，虽然在后面的几十年间优化理论不断完善和进步，但窃以为并没有本质的突破。
DP出现后的10年间大家都在研究如何克服DP的扩展性问题，并出现了Cascades为代表的新优化器框架。Cascades很好地解决了DP的扩展性问题，并在一定程度上进一步缩小了搜索空间，改进了优化效率。在Cascades之后，又是10年，研究人员的重点转移到发现新的优化规则，和靠谱的heuristics。
再后来，据笔者的见识，貌似再没有太多的进展。近年来的研究重点在于人工智能方面，采用机器学习等理论来指导优化器，但貌似没有成熟的理论和技术出现。
Trafodion使用Cascades优化器框架，笔者计划将在下一篇博文中介绍Cascades。
这个第一部分写了不少，希望大家能够记住Dynamic Programming的搜索过程是自底向上的，这个很重要。
* * * * *
回到本文开始时的问题，sql调优是应该交给数据库还是DBA？我还是没有确切的答案，这个问题让我头疼，觉得自己脑子不够用。对于复杂的Query，比如15-way join，给定那么多的可选方案，我相信作为人类的DBA应该不大可能会找到最佳join顺序吧；而对于相对简单的query，人类的智慧应该是有用的，是不是这样呢？我有点儿迷茫，已经五月份了，上海的夜晚还有点儿冷，我仰望漆黑的天空，“嘒彼小星，三五在东。肃肃宵征，夙夜在公。实命不同”，下定决心不再熬夜了。今天就写到这里吧。
参考文献
【1】 https://courses.cs.washington.edu/courses/cse444/98au/optimization.ppt
【2】 An overview of query optimization in relational systems, Surajit Chaudhuir, Microsoft. （网上可以找到pdf下载，笔者着重推荐一读的文章）
【3】 Apers, P.M.G., Hevner, A.R., Yao, S.B. Optmization Algorithms for Distributed Queries, IEEE Transactions on Software Engineering, Vol 9:1, 1983
【4】 Pat Selinger, Access Path Selection in a Relational Database Management System
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30206145/viewspace-1651583/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30206145/viewspace-1651583/
221
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
