SQL查询优化器浅析

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

青训营将主要从分析引擎的计算展开，还会涉及存储系统（存储分布式协议）的介绍：
为什么把SQL的优化器放在第一节课？答：One SQL rules big data all

课程内容：

大数据体系和SQL
常见的查询优化器
社区开源实践
前沿趋势

01. 大数据体系和SQL

介绍大数据体系和SQL处理流程，重点介绍SQL在分布式环境下的处理

分布式系统：建立在网络之上的软件系统，具有高度内聚性和透明性。一个著名的分布式系统的例子是万维网，在万维网中，所有的一切看起来就好像是一个文档（Web页面）一样。多数分布式系统是建立在计算机网络上的，所以分布式系统与计算机网络在物理结构上是基本相同的。分布式系统和计算机网络系统的根本区别在于：分布式操作系统是以全局方式管理系统资源的，可以为用户任意调度网络资源，并且调度过程是透明的，即用户不可知的。

① 内聚性：每一个数据库分布节点高度自治，有本地的数据库管理系统。② 透明性：每一个数据库分布节点对用户的应用来说都是透明的，看不出是本地还是远程。在分布式数据库系统中，用户感觉不到数据是分布的，即用户不须知道关系是否分割、有无副本、数据存于哪个站点以及事务在哪个站点上执行等。

1. SQL的处理流程

1.1 Parser

输入->输出：String->AST(abstract syntax tree)

处理过程：①词法分析（拆分成token）②语法分析（将token组成AST node）

处理过程的实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），JavaCC（Flink），Antlr(Presto,Spark)

1.2 Analyzer

输入->输出：AST->Logical Plan

处理过程：

①检查并绑定元信息（Database，table Column等） ②SQL的合法性检查（例min/max/avg的输入是数值） ③AST到Logical Plan的转化（可能会做，有些系统在其他组件实现）

Logical Plan：逻辑计划，逻辑地描述SQL对应的分步骤计算操作。树中的每个结点都是一个算子（operator），即每一个计算操作。

例子：

转化成：

1.3 Optimizor

为什么SQL有查询优化：因为SQL是以一种声明式的语言，用户只描述做什么，没有告诉数据库怎么做，数据库就有了很大的选择空间。

查询优化的目标：找到一个正确且执行代价最小的物理执行计划，将逻辑计划拆分成多个物理计划片段（需要感知数据分布，充分利用数据的亲和性）

查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的。一般SQL越复杂，Join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍。

输入->输出：Logical Plan->Physical Plan

1.4 Executor

输入->输出：Physical Plan->客户端显示容器

Physical Plan也表示为一棵树，由优化过的逻辑计划转化而来

处理过程：对优化后的逻辑计划进行拆分，每个节点是完整的执行计划的一部分，拆分出来的部分称作（Plan Fragment——“执行计划子树”），目的是最小化节点之间的网络数据传输，需要利用数据的物理分布（数据的亲和性），拆分过后的节点通过Shuffle算子连接。

处理方式：

①单机并行（充分利用cache，pipeline，SIMD）

②多机并行（一个fragment对应多个实例）

02.常见的查询优化器

介绍查询优化器的分类，重点介绍RBO和CBO的原理

2.1. 查询优化器的分类

两种分类方法：

1)根据遍历树的顺序不同划分为两种优化器：

①Top-down Optimizer（从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划）例子：SQLServer，Volcano/Cascade

②Bottom-up Optimizer（从0开始，由下往上遍历计划树，找到完整的执行计划）例子：System R(最早的优化器)，PostgreSQL，IBM DB2

2）根据优化的方法不同划分为两种优化器：

①Rule-based Optimizer(RBO)：主要是根据关系代数等价语义，重写查询；基于经验的启发式规则

②Cost-based Optimizer(CBO)：使用一个模型估算执行计划的代价，选择代价最小的执行计划

现在系统使用的查询优化器可能上述方法都会用到

2.2 RBO

RBO是基于关系代数的等价变换

RBO的优化原则：

1）读更少的数据、使访问速度更快（对I/O进行优化）

2）传输更少的数据、使传输速度更快（对Network进行优化）

3）处理更少的数据、处理速度更快（对CPU & Memory进行优化）

RBO的优化规则：

1）列裁剪（减少列扫描的数目） b9a866421bfb4c80b809d73dc8d42aca_tplv-k3u1fbpfcp-watermark.png 2）谓词下推（在不影响最终结果的情况下可以把where的选择条件下移） c57b64b0cb65417a85090ac67c945f99_tplv-k3u1fbpfcp-watermark.png 3）传递闭包 4）Runtime Filter（运行时产生的filter）有三种常用的Runtime Filter：

① min-max filter：范围一定要是紧密的才有过滤意义

② in-list filter：用集合来表示范围，是min-max的优化，但数据量过大在网络传输过程会有速度限制

③ bloom filter：它的大小不会随着集合大小的变化而改变，把在bloom filter的数据扫描出来

小结

主流RBO实现一般都有几百条基于经验归纳得到的优化规则（可到开源数据库查看它们的优化规则）

优点：实现简单，优化速度快

缺点：不能保证得到最优的执行计划（①全表扫描可能比索引扫描更好(查询数据分布十分不均匀时，索引扫描可能导致随机I/O) ②Join（连接）的物理实现无法选择是Hash join或SortMerge Join ③ 无法识别小表（Hash join需要用小表来构建哈希表）④多表连接）

随机I/O 与顺序I/O：指磁盘访问的I/O在磁盘中的顺序。随机I/O就是读写的内容分散在磁盘的不同位置，需要来回查找所以效率低。顺序I/O就是读写的内容集中存储在磁盘的一块，从前到后依次读取，免去了查找的过程，所以效率高。

2.3 CBO

使用一个模型估算执行计划的代价，选择代价最小的执行计划

CBO使用一个模型估算执行计划的代价，执行计划的代价等于所有算子执行代价之和

算子代价：CPU，内存，磁盘I/O，网络I/O等代价（叶子算子可以通过统计原始表数据得到；中间算子需要根据一定的推导规则，从下层算子的统计信息推导得到）

1）统计信息

原始表的统计信息：①表或者分区级别（行数、行平均大小、表在磁盘中占了多少字节等）；②列级别（列的min，max，num null，num not null，num distinct value，histogram等）
推导统计信息：①选择率（对于某一个过滤条件，查询表会从表中返回多大比例的数据） ②基数（在查询计划中常指算子需要处理的行数）

/准确地统计信息远比代价模型本身重要/

2）统计信息的收集方式

DDL中定义的数据实时更新可能会导致插入数据时的处理速度过慢；手动执行可能会导致数据太旧；动态采样通常是通过query来实现的。