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

Xu1Aan
149 阅读9分钟

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

1 大数据体系与SQL

本小节将介绍大数据体系和SQL的处理流程、重点介绍SQL在分布式环境下的处理

1.1 大数据体系概述

大数据技术大致可分为三个核心：数据接入部分，数据计算部分，结果存储部分

数据接入部分: Kafaka、 hdfs、 hive等

数据计算部分: spark flink mapreduce等

结果存储部分： hbase mysql elasticsearch等

而完整的大数据项目会有更多组件来维护系统的运行以及功能的拓展，下面是比较详细的大数据体系图

大数据体系.png

其中分析引擎是数据处理的核心部分，目前大多数分析引擎都是以SQL作为更高抽象层次的计算入口，SQL已经成为了一个趋势以及很多框架的一个接口。

  • Hive：把sql解析后用MapReduce跑
  • SparkSQL：把sql解析后用Spark跑，比hive快点
  • Phoenix：一个绕过了MapReduce运行在HBase上的SQL框架
  • Drill/Impala/Presto 交互式查询,都是类似google Dremel
  • -Druid/Kylin olap预计算系统

1.2 SQL处理流程

SQL语句首先通过Parser解析器进行语法分析和词法分析将文本变成抽象语法树(AST)，再由Analyzer将AST转换成逻辑计划树（Logical Plan），接着由Optimizer对逻辑计划树进行分析优化得到查询计划（Physical Plan），最后交给Executor进行执行。

SQL执行流程.png

1.2.1 Parser

Parser的主要功能是将文本变成抽象语法树

  • String → AST（abstract syntax tree）

    • 词法分析：拆分字符串，得到关键词，数值常量，字符串常量等token
    • 语法分析：将token组成AST node，最终得到一个AST

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

Parser.png

1.2.2 Analyzer 与 Logical Plan

  • Analyzer

    • 访问库/表（Database、Table、Column）元信息并绑定；
    • 去判断SQL是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确；
    • 将AST转换成逻辑计划树

    例如：

Analyzer.png

  • Logical Plan

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

    将上述SQL语句转化为下图的逻辑计划，这个计划只是逻辑上的，但实际却不是这样实现的

logical plan.png

1.2.3 Optimizer（查询优化器）
  • SQL是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做
  • 目标：找到一个正确且执行代价最小的物理执行计划
  • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的
  • 一般SQL越复杂，Join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍。
1.2.4 Physical 与 Executor

  • Plan Fragment：执行计划子树

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

  • Executor

    • 单机并行：cache，pipeline，SIMD

    • 多机并行：一个fragment对应多个实例

Executor.png

1.2.5 小结
  • One SQL rules big data all
  • SQL需要一次经过Parser，Analyzer，Optimizer和Executor的处理
  • 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
  • 查询优化器需要感知数据分布，充分利用数据的亲和性
  • 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段

2 常见的查询优化器

本小节将介绍查询优化器的分类、重点介绍RBO（基于规则）和CBO（基于代价）原理

2.1 常见的查询优化器

(1) 按照遍历树的顺序分类：

  • Top-down Optimizer

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

  • Borron-up Optimizer

    从零开始，由上往上遍历计划树，找到完整的执行计划

(2) 根据优化方法分类：

  • Rule-base Optimizer（RBO）

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

  • Cost-based Optimizer (CBO)

    • 使用模型估算执行计划的代价，选择代价最小的计划
2.2 RBO（基于规则的优化器）

关系代数：

  • 运算符： Select({\sigma}), Project({\pi}), Join(\Join), Rename({\rho}), Union({\cup})等

  • 等价变换：交换律，结合律，传递性

    • Select: {\sigma}{p_1{\wedge}p_2{\wedge}...{\wedge}P_N}={\sigma{p_1}({\sigma}{p_2}(...{\sigma}{P_N}))}

    • Join: {R{\Join}S=S{\Join}R}，{(R{\Join}S){\Join}T}={R{\Join}(S{\Join}T)}

    • ...

RBO关系代数.png

优化原则：

  • I/O：读更少的数据且读取速度更快
  • 网络：传输数据更少且速度更快
  • CPU和内存：处理的数据更少且速度更快

裁剪规则

  • 列裁剪：查询中对应算子用不到的列，在运行和计算中无需保留，减少资源占用（例如从扫描整张表优化为只扫描某些列）

  • 谓词下推：【谓词：WHERE条件中的表达式】在某些场景下查询需要过滤数据，尽量将条件过滤提前，过滤一些不必要的数据，减少查询时间（例如一个Filter对JOIN的结果没有影响，因此可以在执行JOIN之前就先过滤一些内容）

  • 传递闭包：【表达式的等价关系 + 过滤条件 → 新的过滤条件】例如在JOIN条件之前有其中一个表的条件过滤，那么在另一张表可以加入新的条件过滤。

  • Runtime Filter：【JOIN右表 Runtime Filter Builder 数据信息传递给 JOIN左表 Runtime Filter】执行时才能使用的过滤器。对于一个JOIN，如果能在查询端提早过滤，那么在运行JOIN时就很快，因为无需计算很多hashcode。

    • min-max：经过过滤器后知道JOIN右表的max和min即数据范围，那么在运行时传递给左表进行数据范围过滤

      • 缺点：min-max区间是连续的，范围需要紧密，否则扫描数据量很大

    • in-list：用一个集合记录包含的范围。[1-100, 10000] 离散数据

      • 缺点：若数据很多，则list很大，网络传输开销大

    • bloom：大小不随集合大小改变。构建JOIN右表的一个Hash表同时构建出bloom filter，查询JOIN左表的时候，如果bloom filter里没有这个数据，则无需查询出来。

局限性

  • 无法解决多表连接问题
  • 无法确定和选择最优的分布式 Join/Aggregate 执行方式：例如无法确定索引扫描还是全表扫描
2.3 CBO 基于代价的优化器

CBO流程.png

算子代价包括CPU、内存、磁盘I/O、网络I/O等，具体包括算子输入数据的统计信息（输入、输出结果的行数，每行大小...）和具体算子的类型，以及算子的物理实现有关

统计信息

  • 原始表统计信息：

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

  • 推导统计信息：

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

(1) 收集方式：

  • DDL里指定所需统计信息，数据库在数据写入时收集或更新信息【会影响实时更新数据的速率】
  • 手动执行 explain analyze statement【手动的，不能及时获得更新信息】
  • 动态采样： select count(*) from ...【通过Query来实现】

(2) 过滤器选择率规则（假设列和列之间是独立的，列的值为均匀分布）：AND、OR、NOT、等于、小于条件。

  • 假设经常与现实违背 => 用户指定或者数据库自动识别相关联的列
  • 数据不是均匀分布 => 使用直方图处理

执行计划枚举

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

例如有三个表连接的操作，通过求解两个表连接的代价，动态地得到最小的三个表执行计划代价。

2.4 小结
  • 主流RBO实现一般都有几百条基于经验归纳得到的优化规则
  • RBO实现简单，优化速度快
  • RBO不保证得到最优的执行计划
  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心或者动态规划算法寻找最优执行计划
  • 大数据场景下CBO对查询性能非常重要

3 社区开源实践

本小节将介绍查询优化器在社区的开源实践、重点介绍Apache Calcite项目

3.1 社区开源实践概览

3.2 Apache Clacite

Apache Clacite 是大数据领域很流行的查询优化器

  • Apache Clacite RBO 是基于 Volcano/Cascade 框架的，其定义了许多优化规则，例如 HepPlannner 使用了pattern匹配表达式子树

    • 遍历表达子树去匹配规则，然后进行等价替换得到新的表达式子树，不断地循环，直到所有的路遍历完无法在子树中进行匹配

  • Volcano/Cascade 精髓：Memo（备忘录，存储候选执行计划；Group：等价计划集合）、动态规划、剪枝（减少搜索空间）

4 前沿趋势

本小节将介绍SQL引擎的前沿趋势，重点介绍AI和DB相结合

  • 引擎架构的进化：存储计算分离、一体化（HTAP，HSAP，HTSAP）【解耦、池化、一体化】

  • Cloud：云原生、serverless（弹性计算）

  • 湖仓一体（数据仓库，数据湖），联邦查询

  • DATA + AI：AI4DB、DB4AI

    • AI4DB：自配置（智能调参 如OtterTune、QTune；负载预测/调度）、自诊断和自愈合（错误恢复及迁移）、自优化（统计信息估计[Learned cardinalities]、代价估计、学习型优化器[IBM DB2 LEO]、索引/视图推荐）
    • DB4AI：内嵌人工智能算法（MLSQL, SQLFlow）、内嵌机器学习框架（SparkML, Alink, dl-on-flink）

引用参考

第四届字节跳动青训营大数据资料

avatar
文章
阅读
粉丝