SQL Optimizer--RBO&CBO | 青训营笔记

这是我参与「第四届青训营 」笔记创作活动的第1天

简介

在大数据系统中，SQL的处理流程为：SQL传入Parser，进行词法分析和语法分析，生成的AST进入Analyzer，由Analyzer输出逻辑执行计划，而改逻辑执行计划到转变为物理执行计划并且真正为Executer所运行的过程中，为了提高运算的速度，减少内存的浪费，设计运用合适的Optimizer为必不可少的一环。 在该笔记中，我基于字节跳动第四届青训营的课程 SQL Optimizer，总结了常见的两个查询优化器，RBO与CBO的运作原理及其基本背景知识。

graph TD
Parser --> Analyzer --> Optimizer --> Executor

目录

1. Relational Algebra 关系代数
2. Rule-Based Optimizer (RBO)
3. Cost-Based Optimizer (CBO)
4. 总结

Relational Algebra 关系代数

Relational Algebra 是很多数据库数据处理方式的底层逻辑，比如SQL是对Relation（可以直观想象为把数据以表格形式存储）的处理，其中的常见操作如Join，都是基于Relational Algebra的思想。此篇笔记所关注的RBO的底层思想，也是基于Relational Algebra。

Select

Relational Algebra最基本的操作之一。从Relation表格上理解即是根据给出的一定条件，选择相应的行。比如有一个关于学生绩点的Relation，每一行对应一个学生的ID，姓名，绩点，那么如果我们给出绩点>3.7的条件，即会选择出该Relation中符合该要求的行。

Project

与Select类似，区别在于Project是选出相应符合条件的类，如在上文提到的Relation中选出学生ID一列。

Cross Product

将两个Relation结合在一起（Cartesian product），得到结果的内容是两个Relation全部内容排列组合的结果，Attribute的数量，如果没有做额外规定，为两个Relation Attribute数量之和。

Natural Join

在合并两个Relation的时候，会识别相同的Attribute，不会让其重复出现，Attribute的数量不一定等于两个Relation Attribute数量之和。比如一个Relation为学生ID，学生绩点，另一个为学生ID，学生专业，用Natural Join合并时，会自动将两个的学生ID相匹配。

Theta Join

同样是合并两个Relation，但是是基于自定义的条件（即称为Theta），是基本的DBMS中的操作，一般提到join即指的是Theta Join。

Union, Difference and Intersection Operator

这三个Operator与集合中所学习的相同。因此不做额外赘述。

因为在对Relation进行操作的时候，有以上的关系代数作为支持，我们可以进行一些等价变换，如利用select和join时的结合律、交换律，和传递性，对Analyzer给出的逻辑执行计划进行优化，减小运行成本，这便是下一个部分RBO的基本思想。

Rule-Based Optimizer (RBO)

是第一个Oracle Optimizer,Oracle的网站上有很多详细介绍。

优化原则

1. Read data less and faster （读更少的数据，更快地读数据）
2. Transfer data less and faster （传更少的数据，更快地传数据）
3. Process data less and faster （处理更少的数据，更快地处理数据）

RBO方法1--列剪裁

如图为一个简单的执行计划。 列裁剪的方法是从后到前，即在图中从上到下倒推，看需要的是哪些列，这样在最开始的SCAN的步骤的时候，可以只扫描需要的列，以达到减少工作量的目的。 比如，在该图中我们只需要的是pv.siteID，user.name，user.siteID，pv.userID，user.ID，故只需要SCAN这些即可，Relation中的其他列都可以摒弃。

RBO方法2--谓词下推

谓词下推指在整个流程中尽早地过滤数据，这样后面被操作的数据量就会变小，可以提高整个过程的效率。

比如在上图中，filter的条件是user.ID>123，这只与user相关，不与user和pv合并后的结果相关，这样我们就可以先对user经过SCAN后的相关列进行Filter筛选，再与pv中的相关列join。由此，不论是Filter这一步还是join这一步，涉及的数据量都会减少。

RBO方法3--传递闭包

传递闭包更常见的或许是在离散数学上的概念，指在某个集合上求包含某个关系的最小传递关系，主要应用的是传递性。即如果有i-->k,k-->j,那么我们可以直接有一条边将i和j相连。

在RBO中，传递闭包同样是对于传递性的应用。比如在上图的例子里，join的条件是pv.siteID=user.siteID，而filter的条件是user.siteID>123，那么根据传递性，我们知道这一步的要求等效于pv.siteID>123，那么可以做的优化便是，在SCAN之后，分别根据pv.siteID>123和user.siteID>123，在两边进行筛选，再对两边的数据join，又减少了一些数据量。

RBO方法4--Runtime Filter

Runtime Filter的运行原理是，在执行中，join两个（会经过Filter的）Relation之前，对于其中一边Filter之后的结果通过建哈希表等方式，总结其特点，比如key的取值范围等，这个特点便形成一个Runtime Filter，传递给另一侧还没有被Filter筛选的Relation，那么另一侧的Relation在被筛选之前，就可以依据该Runtime Filter再进行一次筛选，以减少之后到达Filter的数据量。

常见的Runtime Filter包括min-max，in-list和bloom filter。

Cost-Based Optimizer (CBO)

虽然RBO提出了一些高效可行的优化方法，但是RBO并不能保证最终得到的是最优的执行计划，因此提出了另一种方法，CBO。如其名，CBO意在选择代价（Cost）最小的执行计划，总代价即是所有算子代价之和。在得到代价最小的执行计划之后，可以再利用RBO得到所有可能的等价执行计划。

算子代价

算子代价包括CPU、内存、磁盘、网络等代价。根据算子在整个流程中扮演的位置，又可以分为叶子算子（SCAN，统计原始数据信息）和中间算子。

统计原始表数据

该步骤对应叶子算子的代价。

从两种级别谈：第一种是表或者分区级别，计算这部分的代价考虑的是行数，行的平均大小，表在磁盘中占多少字节等；第二种是列级别，计算这部分代价主要考虑最小值最大值，有多少是null值，多少非null，有多少不同的（distinct）的值等。

收集该信息的方式包括在DDL里预先指定好需要收集那些信息，根据什么方式归bucket；手动执行explain analyze statement；和动态采样。

推导统计信息

该步骤对应中间算子的代价。

推导统计信息包括两部分：选择率--即一个filter会从表中筛选出多大比例的数据；第二是基数，即数据的行数有多少。

具体统计信息的推导规则与我们常见的集合论以及概率统计中的规则相同。比如对于一个AND关系的Filter Selectivity返回的数据比例，fs（a AND b）= fs(a)·fs(b)。

总结

查询优化器之前的关系并非互斥关系，可以根据具体使用场景、对于速度内存的要求等，进行选择和结合，就像由CBO找到代价最小的执行方法之后由RBO找到等价的执行方法。

该部分的内容在概念、原理的初步理解上难度不大，但是真正付诸应用是一个较为复杂的过程。