如何让SQL速度飞起来 入门YashanDB优化器

　　如何让SQL速度飞起来 入门YashanDB优化器

　　优化器，SQL引擎的核心组成部分，是数据库中用于把关系表达式转换成最优执行计划的核心组件，影响数据库系统执行性能的关键组件之一。

　　进入优化器之前，我们先来看一下数据库是如何处理一条用户输入SQL语句的。如下图所示，从接收到客户端发送的SQL语句，一般需要经历SQL解析、SQL校验、查询重写、计划优化、SQL执行等过程，最终服务端将SQL的计算结果返回给客户端。其中查询重写与计划优化的步骤，便是优化器所承担的关键职责。

                                　　图1 SQL语句处理过程

　　优化器的职责

　　我们来看一个简单的例子，比如存在10个数字：请求一：找到所有的数字6

　　·

　　A：1 3 8 5 6 4 2 7 10 9

　　数字是随机存储的，需要将所有的数据都查找一遍，访问效率为O(n)。

　　B：1 2 3 4 5 6 7 8 9 10

　　数字是按大小顺序存储的，可以使用二分法查找，访问效率为O(Log2(n))。请求二：查看数字中是否存在6，意味着只要找到6，我们就可以返回一个True，反之返回False。我们再来看一下两种不同的存储方式：

　　A1：6 3 8 5 1 4 2 7 10 9A2：9 3 8 5 1 4 2 7 10 6

　　数字是随机存储的，如果6刚好在第一个位置，则最佳O(1)的访问就可以，如果在最后一个位置，则需要O(n)访问全部10个数字。

　　B：1 2 3 4 5 6 7 8 9 10

　　数字是按大小顺序存储的，依然需要使用二分法查找，访问效率为O(Log2(n))。其中，请求一和请求二，我们可以理解为不同的客户访问请求，而数据是随机存储还是顺序存储，则是数据不同的存储特征。通过例子我们可以看出，当用户请求或者数据特征不同时，实现数据处理的最佳算法都是不同的。而优化器的职责就是通过以下几个步骤，找到最佳的算法。

　　• 识别不同的客户请求(SQL语句决定)。

　　• 理解数据特征(主要由存储和统计信息承载)。

　　• 探索在当前数据特征下，实现客户请求的所有可能路径(通过重写变换和实现丰富的物理算子的方式)。

　　• 选择最佳的执行路径(Cost模型计算决定)。

　　优化器的发展

　　正所谓条条大路通罗马，当用户输入一条SQL语句时，在数据库中，存在非常多可能的执行方式，而优化器负责找到并且选择最合适的执行方式。我们可以通过经验规则的方式来实现，比如在某个数据特征A满足的时候，就选择算法A，在特征B满足的时候，就选择对应的算法B，这就是早期典型的RBO(Rule-Based Optimization)的实现方式。RBO通过丰富的优化经验规则，来产生语句的执行计划，在数据特征与算法相对较少的情况下，RBO可以通过枚举各种可能性，来实现最佳路径的选择，当数据特征与算法都较多的时候，通过程序分支枚举来实现的复杂度非常之高，以至于几乎无法实现所有可能性的枚举，CBO(Cost-Based Optimization)优化器应运而生。CBO是目前主流数据库优化器，通过抽象用户请求、数据特征、实现一种通用的评分机制，评估每个执行计划的总代价，从而选择代价最低的计划作为最优计划，CBO的方式不再依赖经验规则的分支判断，很好地解决了RBO的规则编码复杂度。但是CBO优化器依赖数据特征的准确性，当数据变化较频繁的时候，数据特征的维护与实时更新很难做到，经过多层复杂运算后，其数据特征通过传统的统计计算也很难准确地评估出来，这些都影响了CBO中计划准确性，这也是过去几十年中，CBO优化器中非常难解决的问题。是否存在更好的方式来描述数据特征和评估数据计算结果?是否存在更优的方式来选择最优计划?优化器能否像DBA一样，不通过枚举，而利用经验来产生最优计划?随着机器学习的发展，其在解决经验学习问题上展现出了强大的能力，基于机器学习的方法也在优化器中各个方面进行着尝试，比如：基于ML的Join order优化，基于ML的统计信息与Cost模型，基于ML的最优计划选择等。

　　YashanDB优化器

　　YashanDB优化流程分为静态重写优化与动态优化过程。首先是静态优化重写阶段，主要包括内容如下：

　　1. 冗余操作的简化，比如Filter的相关的优化，Filter的合并，a > 1 and a > 5，将优化为a > 5。

　　2. 实现了简单且有益的改写规则，改写不会丢失任何更优的执行路径，产生更加优化的执行路径。

　　比如：Outer join到Inner join的优化，比如：

　　a left outer join b on a.id = b.id where b.name = const

　　可以直接将Outer join改写为Inner join。

　　随后将重写后的Query表示输入到动态优化阶段，包括如下几个模块：

                                　　图2 YashanDB优化器模块

　　1转换模块

　　主要实现逻辑动态重写和逻辑计划到物理计划的转换，根据原始语句的特点，生成更多的扩展访问路径。比如子查询的改写，将子查询的执行方式，扩展出子查询和父查询Join的执行方式。

　　根据Join表的个数，通过动态规划或者贪心算法，扩展生成各不同等价Join Order等。

　　而在逻辑到物理阶段的转换中，实现一个逻辑算子对应的多个等价物理算子的扩展，比如Join将生成Nestedloop Join，Merge Sorted Join，Hash Join等。如上图中的Exploring阶段和Dynamic Transform阶段，实现该部分的逻辑。

　　2统计信息模块

　　如上图的Statistics Estimator阶段，实现了算子统计信息的计算。主要包括Filter的过滤评估，Group By/Grouping Set的分组评估等。

　　3Cost计算模块

　　如上图的Cost Model部分，实现了各算子基于统计信息和配置参数相关的Cost计算模型。

　　4搜索最优计划

　　在上图的Optimize Engine中，嵌入了计划的遍历和和最优计划的搜索能力。

　　YashanDB动态优化部分基于Cascades框架设计构建，该框架可以较好的实现器各模块解耦，例如路径搜索与统计信息和Cost模型的解耦、查询重写规则与逻辑算子和物理算子的解耦等，路径扩展与最佳路径搜索的解耦等，通过各个模块的解耦，实现在任何模块都可以快速的增加开发一些新的功能的能力，比如可以快速增加一个物理算子，可以快速的增加一个查询重写的规则。

　　下面我们用一条简单的sql语句示例，来说明一下优化器的实现过程。

　　select count(*) from t1 join t2 on t1.id = t2.id group by t1.id;

　　1扩展可选计划

　　优化器的第一步便是把所有可能的计划找出来，分为以下3个步骤：

　　1. SQL语句经过Parse后生成抽象表达树，在进入到CBO后，根据SQL的语义，生成一个初始的逻辑执行树。从而初始化搜索空间，语句在CBO框架中的表示如图一。接着进行逻辑转换，根据重写规则，扩展出更多的等价形式。该语句group by t1.id命中Group By下推的重写条件，所以根据动态重写规则，会生成一个额外的路径，将Group By下推到Join下面，t1表的Scan之后，表示如图二。

　　2. 在该语句中，由于存在Join条件t1.id = t2.id, 所以Group By t1.id也可以等价表示为Group By t2.id，所以还会额外的产生一个Group By下推到join下面t2的Scan之后的计划。所以就有了Group By下推到t1表，Group By下推到t2表下面的额外路径产生。

　　3. 逻辑转换阶段结束后，进入物理转化阶段，根据逻辑算子，产生相关的物理执行算子。在YashanDB数据库中，Group By的物理算子实现了Hash Group，Sorted Group和Sdt group，这样在每个阶段的Group操作，都会产生3个物理Group的路径，表示如图三。Scan会根据表和表上的索引，产生相关的Table Scan，Index Scan等物理执行计划。

　　2 选择最佳计划

　　在生成多个可选执行路径后，优化器需要选出最优的方式。优化器会遍历所有可用计划路径，根据Cost计算，选出最优计划，在计划表示中，总访问路径的个数是每层节点之间的可选计划的乘积，比如在Group By没有下推的路径中，Group By有三种执行方式，Join也有三种执行方式，如果表t1有两种扫描方式，t2有两种扫描方式，那么将生成36个不同的执行路径(332*2的组合方式)，在任何一个节点上增加一个算子，整个搜索计划数目会乘以2。所以，随着路径的增加和算子实现的增加，语句的搜索空间以指数的方式增长。当计划空间增大太多时，会通过适当的路径裁剪来减小搜索空间。

　　结语

　　优化器的实现对数据库的性能影响十分关键，一个好的优化器体现在优化规则与优化细节上，如何最大限度的利用所有可用的信息，来帮助生成更丰富的执行计划。而实际的客户场景可以帮助提炼更多优化规则，所以优化器是随着客户场景而需要不断发展和进步的。随着应用场景的不断丰富和复杂化，YashanDB优化器的能力也将不断完善和优化，以满足更多的用户需求。