SQL 查询优化器浅析| 青训营笔记

yuanyuan123
198 阅读7分钟

这是我参与「第四届青训营 -大数据场」笔记创作活动的的第1篇笔记

本文已参与「新人创作礼」活动， 一起开启掘金创作之路。

第一节：SQL 查询优化器浅析

大数据体系和SQL

image-20220724095358007.png

SQL的处理流程

image-20220724095552758.png

Parser

将字符串转化为AST

词法分析与语法分析

(待补充)

AST(抽象语法树)

抽象语法树（abstract syntax code，AST）是源代码的抽象语法结构的树状表示，树上的每个节点都表示源代码中的一种结构，这所以说是抽象的，是因为抽象语法树并不会表示出真实语法出现的每一个细节，比如说，嵌套括号被隐含在树的结构中，并没有以节点的形式呈现。抽象语法树并不依赖于源语言的语法，也就是说语法分析阶段所采用的上下文无文文法，因为在写文法时，经常会对文法进行等价的转换（消除左递归，回溯，二义性等），这样会给文法分析引入一些多余的成分，对后续阶段造成不利影响，甚至会使合个阶段变得混乱。因些，很多编译器经常要独立地构造语法分析树，为前端，后端建立一个清晰的接口。

抽象语法树在很多领域有广泛的应用，比如浏览器，智能编辑器，编译器。

实现

递归下降(ClickHouse)、Flex和Bison(PostgreSQL)、javaCC(Flink)、Antlr(Presto,Spark)

Analyzer和Logical Plan

Analyzer：

  1. 检查并绑定Database,Table,Column等元信息
  2. SQL的合法性检查，比如min/max/avg的输入是数值
  3. AST转化为Logical Plan

Logical Plan：

  1. 逻辑地描述SQL对应的分步骤计算操作
  2. 计算操作：算子(operator)

举个例子：

image-20220724103108681.png

image-20220724103055596.png 以上是实际SQL语句执行过程

Optimizer

Executor

Physical Plan：

Plan Fragment:(执行计划子树)

  1. 目标：最小化网络数据传输
  2. 利用上数据的物理分布(数据亲和性)
  3. 增加Shuffle算子

Executor：

  1. 单机并行：cache，pipeline，SIMD
  2. 多级并行：一个fragment对应多个实例

拆分执行计划时，每个节点之间通过增加Sheffle算子连接。

拆分执行计划成多个Plan Fragment然后发送给节点执行，每个节点增加一个Executor。

批量计算与流式计算

大数据的计算模式主要分为批量计算(batch computing)、流式计算(stream computing)、交互计算(interactive computing)、图计算(graph computing)等。其中，流式计算和批量计算是两种主要的大数据计算模式，分别适用于不同的大数据应用场景。

流数据（或数据流）是指在时间分布和数量上无限的一系列动态数据集合体，数据的价值随着时间的流逝而降低，因此必须实时计算给出秒级响应。流式计算，顾名思义，就是对数据流进行处理，是实时计算。批量计算则统一收集数据，存储到数据库中，然后对数据进行批量处理的数据计算方式。主要体现在以下几个方面：

1、数据时效性不同：流式计算实时、低延迟， 批量计算非实时、高延迟。

2、数据特征不同：流式计算的数据一般是动态的、没有边界的，而批处理的数据一般则是静态数据。

3、应用场景不同：流式计算应用在实时场景，时效性要求比较高的场景，如实时推荐、业务监控...批量计算一般说批处理，应用在实时性要求不高、离线计算的场景下，数据分析、离线报表等。

4、运行方式不同，流式计算的任务持续进行的，批量计算的任务则一次性完成。

SQL执行计划

SQL 执行的基本流程

一般业务：客户端(也就是我们的业务代码)发送了一段SQL文本，服务端接收到了一段SQL文本然后进行解析处理，最终返回一段文本(执行结果)。

image-20220723234241927.png

  1. 连接处理：

    该模块主要是管理客户端的连接，客户端可以通过TCP/IP、命名管道、共享内存、套接字等方式与服务端进行连接，服务端接收到连接后，会专门生成一个线程去处理客户端的请求。当完成客户端的请求后，该线程不会被销毁，而是放入线程池中，从而减少了频繁创建和删除线程的消耗，大大节省了系统资源和提高了效率。

    客户端每次发起连接请求时,都会携带用户名、密码等验证信息,如果服务器验证不通过,则会拒绝连接,同时,如果很多客户端同时请求连接,为了避免服务端程序崩溃和提高效率,可以限制最大的连接数量。

  2. 解析和优化：

    1. 查询缓存：为了提高相应效率，MYSQL服务端程序会根据客户端请求的信息生成对应的缓存，如果请求的信息符合缓存中的，则直接返回，无需再去与底层进行更多的交互
    2. 语法解析
    3. 查询优化

  3. 存储引擎

常见查询优化器

2种分类方法：

  1. 第一种，按照遍历计划树划分：

    1. Top-down Optimizer：从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
    2. Bottom-up Optimizer：从0开始，由下往上遍历计划树，找到完整的执行计划

  2. 第二种，按照优化规则划分：

    1. Rule-based Optimizer(RBO)：根据关系代数等价语义，重写查询；基于启发式规则；会访问表的元信息(catalog)，不会涉及具体的表数据(data)
    2. Cost-based Optimizer(CBO)：使用一个模型估算执行计划的代价，选择代价最小的执行计划

RBO

关系代数

image-20220724110541323.png

优化规则

  1. 优化I/O
  2. 优化网络
  3. 优化CPU&Memeory

列裁剪

 select pv.siteId,user.name
 from pv join user
 on pv.siteId = user.siteId and pv.userId = user.id
 where user.siteId > 123

image-20220724111406878.png 优化部分：

只需要SCAN查询所需要的列即可，而不是整张表

谓词下推

 select pv.siteId,user.name
 from pv join user
 on pv.siteId = user.siteId and pv.userId = user.id
 where user.siteId > 123

image-20220724111900830.png

优化部分：

尽早地过滤条件，FILTER user.siteId > 123 早于 JION pv.siteId = user.siteId and pv.userId = user.id

传递闭包

 select pv.siteId,user.name
 from pv join user
 on pv.siteId = user.siteId and pv.userId = user.id
 where user.siteId > 123

image-20220724113959439.png

优化部分：

FILTER pv.siteId > 123 被推导出来，创建一个新的FILTER

Runtime Filter

 select pv.siteId,user.name
 from pv join user
 on pv.siteId = user.siteId and pv.userId = user.id
 where user.siteId > 123

image-20220724114357835.png

优化部分：

  1. min-max:
  2. in-list:
  3. bloom filter:

CBO

也即“基于代价的优化器”，该优化器通过根据优化规则对关系表达式进行转换，生成多个执行计划，然后CBO会通过根据统计信息(Statistics)和代价模型(Cost Model)计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案。

  1. 执行计划地代价等于所有算子地执行代价之和
  2. 通过RBO得到(所有)的等价执行计划

算子代价：CPU、内存、磁盘I/O、网络I/O等代价

统计信息

  1. 原始统计信息：

    1. 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
    2. 列级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等

  2. 推导统计信息：

    1. 选择率：(selectivity)，对于某一个过滤条件，查询会从表中返回多大比例的数据
    2. 基数：(cardinality)，在查询计划中常指算子需要处理的行数

统计信息的收集方式

  1. 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息

     create table test(
 ...
 )
 properties("stats_columns" = "r_name");

  2. 手动执行explain analzye statement，触发数据库收集或者更新统计信息

     analzye table test compute statistics for columns col1,col2,...

  3. 动态采样

     select count(*) from test

统计信息推导规则

假设列和列之间是独立的，列的信息均匀分布

Filter Selectivity：AND、OR、 NOT、 =、 <

执行计划枚举

通常使用贪心算法或者动态规划选出最优的执行计划

  1. 动态规划

(待完成)

Apache Calcite

支持异构数据模型：

  1. 关系型
  2. 半结构化
  3. 流式
  4. 地理空间数据

内置RBO和CBO

image-20220724173814045.png

HepPlanner

优化规则

模式：匹配表达式子树

等价变换：得到新的表达式

内置有100+优化规则

匹配规则

  1. ARBIRARY/DEPTH_FIRST：深度优先
  2. TOP_DOWN：拓扑排序
  3. BOTTOM_UP：与TOP_DOWN相反

VolcanoPlanner

基于Volcano/Cascade框架

Memo：存储候选执行计划

avatar
文章
阅读
粉丝