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

用户9501747993634
169 阅读9分钟

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

大数据体系和SQL

大数据体系自上而下分为七层，分别有业务应用、数据开发、权限管控、分析引擎、资源调度、存储系统、基础设施

大数据体系

One SQL rules big data all

SQL，作为一门查询查询结构化数据的语言，有着语法简单，表达能力强，容易理解的特点。由于它的特点，在大数据处理（尤其是分析引擎）中也是颇受青睐，因此，对SQL语句的深入了解，对于我们大数据的学习也是相当有利的。

SQL语句在大数据中的应用情况

Hive

hive是基于Hadoop构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据：可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能；可以将SQL语句转换为MapReduce任务运行，通过自己的SQL查询分析需要的内容，这套SQL简称Hive SQL，使不熟悉mapreduce的用户可以很方便地利用SQL语言查询、汇总和分析数据。（1）

Spark

Spark SQL 是 Spark 处理结构化数据的一个模块。与基础的 Spark RDD API 不同，Spark SQL 提供了查询结构化数据及计算结果等信息的接口。在内部，Spark SQL 使用这个额外的信息去执行额外的优化。有几种方式可以跟 Spark SQL 进行交互，包括 SQL 和 Dataset API。当使用相同执行引擎进行计算时，无论使用哪种 API / 语言都可以快速的计算。这种统一意味着开发人员能够在基于提供最自然的方式来表达一个给定的 transformation API 之间实现轻松的来回切换不同的。（2）

SQL的处理流程

graph LR;

SQL== SQL ==> Parser == AST ==> Analyzer== Logical Plan ==> Optimizer== Physical Plan ==> Executor

Parser

在解析过程中，输入的String通过词法分析生成一系列token，然后再通过语法分析将token组成AST node，最终得到AST。

词法分析

将 SQL 字符串拆分成包含关键词识别的字符段（Tokens）。

相关的token有

  • 注释。
  • 空格。
  • 关键字（SELECT、CREATE）。
  • 操作符（+、-、>=）。
  • 开闭合标志（(、CASE、)）。
  • 占位符（?）。
  • 引号包裹的文本、数字、字段。
  • 方言语法（${variable}）。
语法分析

其实就是利用算法将token组成一个AST。

Parser的实现

递归下降（ClickHouse），Flex和Bison（PostgreSQL），JavaCC（Flink），Antlr（Presto，Spark）

Analyzer

模块会遍历整个AST，并对AST上的每个节点进行数据类型的绑定以及函数绑定，然后根据元数据信息Catalog对数据表中的字段进行解析。（4）

  • 检查并绑定Database，Table，Column等信息
  • SQL的合法性检查。
  • 将AST转换为一个Logical Plan
Logical Plan

逻辑地描述SQL的分步骤计算操作 计算操作：算子

Optimizer

接下来就是重头戏了，查询优化，SQL是一种声明式语言，用户只描述做什么，但是没有告诉数据库怎么做，因此，对数据库来说，为用户的命令找到一个正确且执行代价最小的物理执行计划是非常重要的，有助于节省资源，缩短查询时间。下面是对Optimizer的基本介绍，后面还会有更加详细的说明。

Optimizer是查询优化，它可以跟踪优化器做出的各种决策并将跟踪结果记录在某表中，Physical Plan 执行计划子树，利用数据的物理分布（数据亲和性）并增加shuffle算子。按照最小化网络数据传输的目标吧逻辑计划分成多个物理计划片段。

查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要。

Executor

一个逻辑计划（Logical Plan）经过一系列的策略处理之后，得到多个物理计划（Physical Plans），最后经过多个策略选择之后，会确定一个物理计划，Executor就是执行物理计划的环节，执行中，我们常常希望可以运用上并行的机制，具体上可以分为两个维度：单机并行和多机并行。单机并行时，我们可以利用CPU cache，指定流水线乱序执行，SIMD；多机并行时我们可以让一个fragment对应多个实例。

常见的查询优化器

查询优化器可以按遍历方式和优化方式分成两类。

遍历方式

Top-down Optimizer

从目标输出开始，由上至下变量计划树，找到完整的最优执行计划。

eg：Volcano/Cascade,SQLServer

Bottom-up Optimizer

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

eg:System R， PostgreSQL，IBM DB2

优化方式

Rule-based Optimizer（RBO）

基于关系代数等价规则对逻辑计划进行变换 ，访问表的元信息，不会涉及具体的表数据，根据关系代数等价语义，重写查询。

优化原则

(I/O) Reed data less and faster

(network) Transfer data less and faster

(CPU & Memory) Process data less and faster

常见的优化方式

列裁剪

根据output除去无用的输出列。

谓词下推

谓词，指的是描述事物或状态的词语，简单来说就是“where”后的限制条件；谓词下推，其实就将过滤条件下推到数据源或者更加靠近数据源的地方，本质目的是为了更快将有用的信息过滤出来。

传递闭包

其实就是根据语句，将已知条件进一步传递，推导出新的条件。

Runtime Filter

Runtime Filter是[4]中提到的在数据库中广泛使用的一种优化技术，其基本原理是通过在join的probe端提前过滤掉那些不会命中join的输入数据来大幅减少join中的数据传输和计算，从而减少整体的执行时间。

小结

总的来说，主流RBO实现一般都有几百条基于经验归纳得到的优化规则，具有实现简单，优化速度快的优点。 但是，由于繁多的规则，如果编写人员不够了解RBO的细则，那么写出来的SQL性能可能会非常差；有时，数据的量级也有可能会严重影响SQL的性能。因此，RBO不保证得到最优的执行计划。

Cost-based Optimizer（CBO）

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

计算代价

执行计划的代价等于所有算子的执行代价之和，而算子的代价包含 CPU，cache misses，memory，disk I/O，network I/O 等代价，同时，也与以下因素有关：

  • 和算子输入数据的统计信息有关
  • 和算子的类型，已经算子的物理实现有关。
    • 算子类型
      • 叶子算子：通过推导原始表数据
      • 中间算子：根据一定推导规则，从下层算子的统计信息中推导得到。

CBO的统计信息

原始表统计信息
  • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等 - 列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)、histogram 等
推导统计信息
  • 选择率（selectivity）  ：对于某一个过滤条件，查询会从表中返回多大比例的数据
  • 基数（cardinality）  ：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数
统计信息的收集方式
  • 在DDL中指定需要收集的统计信息，数据库在数据写入时收集或者更新统计信息
  • 手动执行，触发数据库收集或者更新统计信息
  • 动态采样

小结

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

社区开源实践

Apache Calcite

如果用一句话形容 Calcite，Calcite 是一个用于优化异构数据源的查询处理的基础框架。

特点

  • One size fits all：统一的SQL查询引擎

  • 模块化，插件化，稳定可靠

  • 支持异构数据模型

    • 关系型
    • 半结构化
    • 流式
    • 地理空间数据

  • 内置 RBO 和 CBO

Calcite RBO

Calcite的RBO叫HepPlanner，它是一个启发式的优化器，按照规则进行匹配，直到达到次数限制（match次数限制）或者遍历一遍后不再出现 rule match 的情况才算完成。

  • 优化规则 (Rule)
    • Pattern：匹配表达式子树
    • 等价变换：得到新的表达式
  • 内置有100+优化规则
  • 四种匹配规则
    • ARBITRARY/DEPTH FIRST: 深度优先
    • TOP_ DOWN：拓扑顺序
    • BOTTOM UP：与TOP DOWN相反
  • 优化速度快，实现简单，但是不保证最优

Calcite CBO

Calcite 的CBO叫VolcanoPlanner，会一直迭代 rules直到找到代价最小的paln。

  • 基于Volcano/Cascade框架
  • 成本最优假设
  • Memo：存储候选执行计划
-   Group: 等价计划集合
-   本质：AND/OR graph
-   共享子树减少内存开销

  • Top-down 动态规划搜索

    • 选择winner构建最优执行计划

小结

  • 主流的查询优化器都包含RBO和CBO

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

  • Apache Calcite RBO定义了许多优化规则，使用pattern匹配子树，执行等价变换

  • Apache Calcite CBO 基于Volcano/Cascade框架

  • Volcano/Cascade的精髓: Memo、 动态规划、剪枝

前沿趋势

大数据创业如火如荼，SQL查询优化器任然是一个必不可少的重要组件，引擎架构的进化，云原生，湖仓一体等对SQL查询优化器有新的要求和调整，AI加成，学习型查询优化器也在不断进化。

引用参考

（1）万川梅，谢正兰编 著．HADOOP应用开发实战详解（修订版）：中国铁道出版社，2014.08：第188页

（2）Spark2.2.0中文文档 spark.apachecn.org

（3）浅谈SQL编译 词法分析

（4）Spark SQL底层执行流程详解 - 掘金 (juejin.cn)

avatar
文章
阅读
粉丝