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

用户7477778419296
99 阅读6分钟

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

大数据体系

大数据体系.png

大数据体系中的SQL

为什么 SQL 如此流行？

  1. 有 MySQL、Oracle 之类使用 SQL 作为交互语言的数据库

  2. 有 JDBC、ODBC 之类和各种数据库交互的标准接口

  3. 有大量数据科学家和数据分析师等不太会编程语言但又要使用数据的人

  4. 多个大数据计算引擎都支持 SQL 作为更高抽象层次的计算入口

    1. MR -> Hive SQL
    2. Spark -> Spark SQL
    3. Flink -> Flink SQL

处理流程

SQL ——>Parser——AST——>Analyzer ——Logical Plan——>Optimizer——Physical Plan——>executor

1.Parser

  • String->AST

  • 将文本拆分为输入字符串，输出抽象语法树

    • 词法分析：拆分字符串，得到关键词，数值常量，字符串常量，运算符号等token
    • 语法分析：将token组成AST node，最后得到一个AST
  • 实现方式：递归下降，Flex，Bison，JavaCC，Antlr

2.Analyzer

  • 检查并绑定Database，Table,Column等元信息是否存在合法
  • SQL的合法性检查，比如min/max/avg输入的是否是数值
  • AST->Logical Plan 输入抽象语法树，输出逻辑计划

LogicalPlan.gif

  • SCAN：树的叶子包含扫描节点。这些节点扫描表并应用谓词，如 WHERE 筛选器。每种不同类型的数据访问都有一个扫描节点：HDFS csv，HDFS Parquet，HBase，...
  • JOIN：这些节点表示聚合函数，例如 GROUP-BY 和 SUM
  • Top-N：此节点对结果进行排序，仅保留前 N 个结果
  • left-deep tree的特点，join的右边必须是一个表

3.Logical Plan

  • 逻辑地描述SQL对应的分步骤计算操作
  • 计算操作：算子（树中的节点）

4.PlanFragment

  • 目标：最小化网络数据传输
  • 利用上数据的物理分布（数据亲和性）
  • 增加shuffle算子

DistributedPlanFragments.gif

5.Executor

  • 按照物理执行计划扫描和处理数据，充分利用机器资源
  • 单机并行：cache，pipeline，SIMD
  • 多级并行：一个fragment对应多个实例

常见的查询优化器

查询优化器分类

1.遍历树的顺序划分

Top-down Optimizer

  • 从目标输出开始，由上往下遍历计划书，找到完整的遍历计划树，找到完整的最优执行计划
  • 例子：Volcano/Cascade，SQLSwrver

Bottom-up Optimizer

  • 从零开始，由下往上便利计划数，找到完整的执行计划
  • 例子：System R, postgreSQL，IBM DB2

2.优化方法划分

Rule-based Optimizer（RBO)

  • 根据关系代数等价于语义，重写查询
  • 基于启发式规则
  • 会访问表的元信息，不会涉及具体的表数据

Cost-based Optimizer（CBO)

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

RBO

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

优化原则

  • I/O 读更少的数据，读数据更快
  • Network 传输数据更少更快
  • CPU&Memory cpu指令数更少，内存占用更少

优化方法

  • 列裁剪

    • 从上到下检查语法树需要用到哪些列，减少需要scan的列数

  • 谓词下推

    • 谓词是一些表达式，就是返回boolean值即true和false的函数，或是隐式转换为bool的函数
    • SQL中的谓词主要有 LKIE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS
    • 将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据

  • 传递闭包

    • 通过对语句的分析，对数据的操作进行关系传递
    • 例如：pv.siteid = user.siteid , user.siteid > 123 ——> pv.siteid >123

  • Runtime Filter

    • 通过在join的probe端提前过滤掉那些不会命中join的输入数据来大幅减少join中的数据传输和计算，从而减少整体的执行时间
    • 记录下数据的元信息（如：min-max，in-list，bloom filter）然后继续过滤

小结

  • 实现简单，优化速度快
  • 无法解决多表连接问题
  • 无法确定和选择最优的执行方式

CBO

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

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

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

    • 和算子输入数据的统计信息有关：算子的输入，输出结果的行数，每行大小...

      • 叶子算子Scan：通过统计原始表数据得到
      • 中间算子：根据一定的推到规则，从下层算子的统计信息推导得到

    • 和具体的算子类型，以及算子的物理实现有关

统计信息+推导规则——>计算算子代价——>计算执行计划代价——>执行计划枚举

统计信息

基表统计信息

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

推导统计信息

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

收集方式

  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计消息
  • 手动执行，触发数据库收集或者更新统计消息
  • 动态采样

推导规则

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

执行计划枚举

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

小结

  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心活动态规划算法寻找最优执行计划
  • 在大数据场景下CBO对查询性能非常重要

社区开源实践

  • 主流的查询优化器都包含 RBO 和 CBO
  • Apache Calcite 是大数据领域很流行的查询优化器
  • Apache Calcite RBO 定义了许多优化规则，使用 pattern 匹配子树，执行等价变换
  • Apache Calcite CBO 基于 Volcano/Cascade 框架
  • Volcano/Cascade 的精髓：Memo、动态规划、剪枝

前沿趋势

  • 存储计算分离
  • HSAP, HTAP, HTSAP
  • Cloud Native, Serverless
  • 数据仓库，数据湖，湖仓一体，联邦查询

  • 智能化

    • AI4DB

      • 自配置：智能调参（OtterTune，QTune）、负载预测、负载调度
      • 自诊断和自愈合：软硬件错误、错误恢复和迁移
      • 自优化：统计信息估计（ Learned cardinalities ）、代价估计、学习型优化器（IBM DB2 LEO），索引推荐，视图推荐

    • DB4AI

      • 内嵌人工智能算法（MLSQL，SQLFlow）
      • 内嵌机器学习框架（SparkML， Alink，dl-on-flink ）

总结

这是进入大数据青训营的第一节课，通过这节课的学习，我对于SQL在于大数据领域的应用更加地清晰了。深入地了解了SQL的运行过程，不同的查询优化器以及他们的优化原理。最后还了解到了查询优化器在开源社区中的具体实践以及未来的发展趋势。

avatar
文章
阅读
粉丝