SQL Optimizer 解析｜青训营笔记

天天开心lzz
110 阅读5分钟

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

大数据体系和 SQL

大数据体系全景图

SQL 的一生

SQL的处理流程-Parse

String -> AST (abstract syntax tree)

√词法分析:拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token

√语法分析:将token 组成AST node，最终得到一个AST

●实现:递归下降(ClickHouse)，Flex和Bison (PostgreSQL)， JavaCC(FIlink)，Antlr (Presto, Spark)

SQL的处理流程– Analyzer和 Logical Plan

Analyzer

√检查并绑定Database, Table, Column 等元信息

√ SQL的合法性检查，比如min/max/avg的输入是数值

√AST -> Logical Plan

Logical Plan

√逻辑地描述SQL对应的分步骤计算操作

√计算操作:算子(operator)

注意join 和scan 不能颠倒

所谓逻辑计划树，可以理解为逻辑地描述一个 SQL 如何一步步地执行查询和计算，最终得到执行结果的一个分步骤地计划。树中每个节点是是一个算子，定义了对数据集合的计算操作（过滤，排序，聚合，连接），边代表了数据的流向，从孩子节点流向父节点。之所以称它为逻辑的，是因为算子定义的是逻辑的计算操作，没有指定实际的算法，比如对于逻辑的排序算子，逻辑计划树里没有指定使用快排还是堆排。

SQL的处理流程–查询优化

  • SQL 是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做
  • 查询优化的目标是为 SQL 找到一个正确的且执行代价最小的执行计划
  • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP 的，(NP:Nondeterministic polynominal，非确定性多项式)能在多项式时间内验证得出一个正确解的问题
  • 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍
  1. 类比 gcc/g++ 编译程序时的编译级别（-O1, -O2, -O3），经过编译优化的程序运行效率更高

SQL的处理流程- Physical Plan和 Executor

Plan Fragment:执行计划子树

√目标:最小化网络数据传输

√利用上数据的物理分布（数据亲和性)

√增加Shuffle算子

Executor

√单机并行:cache, pipeline，SIMD

√多机并行:一个fragment 对应多个实例

  • 优化器的输出是一个分布式的物理执行计划。
  • 分布式物理执行计划的目标是在单机 Plan 的基础上最小化数据移动和最大化本地 Scan，生成 PlanFragment 树。
  • 一个 PlanFragment 封装了在一台机器上对数据集的操作逻辑。每个 PlanFragment 可以在每个 executor 节点生成 1 个或多个执行实例，不同执行实例处理不同的数据集，通过并发来提升查询性能。
  • Plan 分布式化的方法是增加 shuffle 算子，执行计划树会以 shuffle 算子为边界拆分为PlanFragment。

常见的查询优化器

RBO (Rule-based Optimizer) ---规则

CBO (Cost-based Optimizer) ---代价

RBO

  • 基于关系代数等价规则对逻辑计划进行变换

优化原则

Read data less and faster (I/O)

Transfer data less and faster (Network)

Process data less and faster (CPU & Memory)

四种优化 反法 ：如图

RF解释：

JOIN中使用的两个表往往是一个大表一个小表，例如通常进行JOIN时候会选取事实表和多个维度表，也诸如星型结构和雪花型结构的查询，因此这个假设在大多数情况下是可以成立的。通常情况下对小表扫描的（HdfsScanNode）执行速度要快于大表（毕竟数据量小很多），这样可以先对小表执行扫描操作，将输出的记录交给JOIN节点，而大表则会主动等待一段时间（默认是1s），JOIN节点会根据小表输出的记录计算出一个过滤条件，这个条件就是RF。

  • 局限性：

    • 无法解决多表连接问题
    • 无法确定和选择最优的分布式 Join/Aggregate 执行方式

CBO

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

√执行计划的代价等于所有算子的执行代价之和

√通过RBO得到(所有)可能的等价执行计划

●算子代价:CPU，内存，磁盘I/O，网络I/O等代价

√和算子输入数据的统计信息有关:输入、输出结果的行数，每行大小.….

√叶子算子Scan:通过统计原始表数据得到

√中间算子:根据一定的推导规则，从下层算子的统计信息推导得到√和具体的算子类型，以及算子的物理实现有关

√例子:Spark Join 算子代价= weight * row_count + (1.0 - weight) * size

统计信息

  • 基表统计信息

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

  • 推导统计信息

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

  • 统计信息的三种收集方式：

    • 建表时候DDL时获取或者更新统计信息
    • 手动执行explain analysze statement
    • 动态采样 select count(*) from table_name

推导规则

CBO-执行计划枚举

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

总结

主流 RBO 实现一般都有几百条基于经验归纳得到的优化规则

RBO 实现简单，优化速度快

RBO不保证得到最优的执行计划

CBO使用代价模型和统计信息估算执行计划的代价

CBO使用贪心或者动态规划算法寻找最优执行计划

大数据场景下CBO对查询性能非常重要

avatar
文章
阅读
粉丝