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

BDacy
145 阅读5分钟

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

课程名称：SQL Optimizer解析

预习内容

关于预习：

主要涉及自己没有接触过的内容进行提前理解和简要学习

编译原理相关基础知识

  • 词法分析：简单来说就是将程序中的字符流进行解析，去读取筛选分析出：哪些是关键字，哪些是运算符，标识符，常量，分隔符等，结果会得到一个单词流
  • 语法分析：语法分析的任务就是在词法分析识别出正确的单词符号串(token串)是否符合语言的语法规则，分析并识别各种语法成分，同时进行语法检查和错误处理，为语义分析和代码生成做准备。输出为一个语法分析树
  • 抽象语法树：由语法分析分析token串得到；是源代码的抽象语法结构的树状表示，树上的每个节点都表示源代码中的一种结构，这所以说是抽象的。 简单了解可参考这篇文章：juejin.cn/post/703045…

sql执行流程

sql 执行流程为：sql语句 -> 查询缓存 -> 解析器 -> 优化器 -> 执行器。

sql语句来时，查询缓存，缓存存在则直接返回结果，没有则交给解析器进行词法分析和语法分析，将sql分解成数据结构，生成一个语法树，还会对其进行语法上的优化和重写，之后交给查询优化器进行执行计划的生成，最后交给查询执行器进行查询，再下面就是引擎层和存储层的结构，最终返回结果，写入缓存。

任务调度DAG 有向无环图
shuffle的实现方式

主要以spark和MapReduce两种系统为参考

课堂学习笔记

大数据体系和sql

现在的大数据体系中，许多计算框架都支持了sql语句的使用，体现sql与大数据体系的结合，主要原因是：sql比较简单，可以给一些数据分析师和数据挖掘师等上手使用，学习的成本相对较低。 因此许多计算分析引擎都提供了sql接口

  • 批式分析——spark，hive，MR
  • 实时分析——flink
  • 交互式分析——Presto，Doris，ClickHouse

sql的处理流程，经过四个组件的处理：

graph TD
a(SQL) --> Parser
Parser -->|AST| Analyzer
Analyzer -->|Logical Plan| Optimizer
Optimizer --> |Physical Plan| Executor
Parser

任务：String -> AST，进行词法分析和语法分析

词法分析：拆分字符串，获取不同类别的token 语法分析：将token组成AST node 输出AST 可以学习编译原理相关知识便于理解

实现：递归下降(ClickHouse)，Flex和Bison(PostgreSQL), JavaCC(Flink),Antlr(Presto,Spark)等等

Analyzer
  • 检查并绑定database，table等元元信息
  • sql合法性检查，如min、max、avg
  • AST -> logical plan

logical plan(left-deep tree)

逻辑的描述sql对应的分步骤计算操作

用一个left-deep tree来表示

optimizer

负责查询优化，sql知识描述做什么，但是却没有告诉数据库怎么做，因此存在优化的空间

目标：找到一个正确且执行代价最小的物理执行计划 一般sql越复杂，join的表越多，数据量越大，查询优化的意义就越大

physical plan 和 executor
  • plan fragment：执行计划子树
    • 最小化网络数据传输
    • 数据亲和性
    • 增加shuffle算子

将plan fragment分发到node上运行

  • Executor
    • 单机并行
    • 多机并行

常见的查询优化器

CBO 和 RBO

Top-down Optimizer 和 Bottom-up Optimizer

Top-down Optimizer：从输出开始，从上往下遍历计划树

Bottom-up Optimizer：从零开始，从下往上遍历计划树

RBO：基于启发式规则，重写查询，不涉及表数据吗，会访问元信息；基于规则

CBO：使用模型估算代价；基于代价

RBO
关系代数

选择，投影，连接等关系代数知识

优化规则

三个指标： io 网络 cpu和内存

  1. 列裁剪 将不会需要的列不进行scan，(从上往下进行扫描)
  2. 谓词下推
  3. 传递闭包 利用关系代数的一些等价关系，推导出新的过滤条件
  4. Runtime Filter

优点：实现简单，优化速度快 缺点：不保证得到最优的执行计划

CBO

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

  • 执行计划的代价等于所有算子的执行计划之和
  • 通过RBO得到(所有)可能的等价执行计划

算子代价：CPU，内存，IO，网络

  • 算子输入数据的统计信息：输入，输出行数，每行大小。。。
    • 叶子算子:scan
    • 中间算子:推导规则，由下层算子得到
  • 具体的算子类型，主要是算子的物理实现
  • 动态枚举执行计划

统计信息

  • 基表统计信息

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

  • 推导统计信息

选择率（selectivity） ：对于某一个过滤条件，查询会从表中返回多大比例的数据 基数（cardinality） ：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数

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

用三表连接的例子

社区开源实践和前沿趋势

主要以Apache Calcite作为例子 介绍其中的RBO和CBO

而前沿趋势一个比较热门的点：DATA+AI

个人感想

学习到了SQL运行时的执行流程，以及在查询优化方面的算法和解决方案，同时感受到如今大数据体系和sql正在紧密融合，所以如何优化关键点optimizer查询优化的算法是重中之重。

avatar
文章
阅读
粉丝