SQL Optimizer 浅析 ｜ 青训营笔记

阿狸啦啦啦
143 阅读10分钟

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

SQL Optimizer 浅析

1658627931249.png

1. 大数据体系和SQL

  • 为什么把SQL放在课程第一节来讲？

SQL简单、常用，并且sql成了很多系统接口。

大多数数据库均用SQL作为共同的数据存储语言和标准接口，使不同的数据库系统之间的互操作有了共同的基础

1.1.大数据体系中的sql

1658636810713.png

希望能用sql处理所有大数据

1.2.SQL的处理流程

1658628603756.png

1.2.1.Parser

  • String -> AST(abstract syntax tree 抽象语法树)

    • 词法分析：计算机科学中将字符序列转换为单词（Token）序列的过程。

      • 拆分字符串，得到 token (关键词、数值常量、字符丰常量、运算符号)等。

    • 语法分析 ：语法分析是编译过程的一个逻辑阶段。语法分析的任务是在词法分析的基础上将单词序列组合成各类语法短语。

      • 将 token 组成 AST node，最终得到一个AST。

  • 实现：

    • 递归下降（ClickHouse）
    • Flex和Bison(PostgreSQL)
    • JavaCC(Flink)
    • Antlr(Presto、Spark)

1658638455132.png

1.2.2.Analyzer和Logical Plan

  • Analyzer 分析器

    • 检查并绑定Database, Table, Column等元信息
    • SQL 的合法性检查， 比如min/max/avg 的输入是数值
    • AST -> Logical Plan

  • Logical Plan 逻辑计划

    • 逻辑地描述SQL对应的分步骤计算操作（逻辑计划是执行查询所需的多个连续步骤，由一棵树表示，其中树中的每个节点都执行一个简单的操作）

    • 如图所示，从数据集中查询数据时，数据从树的底部流向顶部。每个节点处理传入的数据并将结果进一步推到树的上一层。在树的底部，节点将扫描表，而顶部节点包含查询的最终结果。每个节点执行一个操作，也称为计划操作符。

    • 计算操作：算子(operator)

      • 扫描：树的叶子包含扫描节点。这些节点扫描表并应用谓词，例如 WHERE 过滤器。每种不同类型的数据访问都有一个扫描节点：HDFS csv、HDFS Parquet、HBase……
      • 聚合：这些节点代表聚合函数，例如 GROUP-BY 和 SUM。
      • 联接：联接运算符在右侧表中为左侧表中的每一行查找匹配行。存在几种实现连接运算符的算法：嵌套循环、排序合并和哈希连接是最著名的。一旦 Query Planner 完成将查询转换为逻辑计划，它会继续执行第二步：优化该计划。
      • Top-N：顾名思义，这个节点对结果进行排序，只保留前N个结果。

1658629053142.png

1.2.3.Physical Plan 和 Executer

  • Plan Fragment：执行计划子树

    • 目标：最小化网络数据传输（通过最大化数据局部性来最小化节点之间的整体数据传输。每个计划运算符都转换为其分布式版本）

      • 扫描：扫描碎片（如图左下方，F#1、F#2、F#4）发送到相关数据节点，扫描文件本地的HDFS或HBase块。谓词将过滤掉尽可能多的行以最小化中间结果。 Scan 节点与 Impala 磁盘管理器通信以直接从磁盘读取 HDFS 块，绕过 DataNode RPC 层。如果查询涉及 HBase 表，则每个节点都会对节点托管的键范围执行 HBase 扫描。
      • Hash-Aggregation：每个聚合被分解为 2 个部分。第一个，预聚合，在每个节点上运行，只聚合本地数据。每个预聚合的结果被发送到单个节点，第二部分，合并聚合发生。回到例子，几个节点会执行group-by操作，然后是操作sum(weblog.bytes)（图中片段F#6）。每个小计都发送到合并聚合（片段 F#7），它将计算小计的总和（sum(sum(weblog.bytes)）。
      • Top-N：与Hash-Aggregate类似，排序被分解为一个pre Top-N，它在多个节点上并行运行，一个merge Top-N，它对预先排序的子部分进行排序。
      • 交换：参与 JOIN 算子的节点需要交换数据。根据每个参与表的估计大小，Impala 在两种不同的交换策略之间进行选择：

    • 利用上数据的物理分布(数据亲和性)

    • 增加Shuffle算子

  • Executor

    • 单机并行：cache, pipeline, SIMD
    • 多机并行：一个fragment对应多个实例

1658640729852.png

1.2.4.查询优化

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

1.3.小结

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

2.常见的查询优化器

2.1.查询优化器的分类

1658640946289.png

2.2.RBO (Rule-based Optimizer)

2.2.1.关系代数

  • 关系代数是一种抽象的数据查询语言，用 对关系的运算 来表达查询
  • 运算符：Select (σ)、Project (π)、Join (∞)、Rename (ρ)、Union (U)等
  • 等价变换：结合律、交换律、传递律

2.2.2.优化原则

  • Read data less and faster (I/O)

  • Transfer data less and faster (Network)

  • Process data less and faster (CPU & Memory)

数据读取、传输、处理尽可能少而快

1658641622860.png

2.2.3.一些RBO优化规则

2.2.3.1.列裁剪

列裁剪的基本思想在于：对于算子中实际用不上的列，优化器在优化的过程中没有必要保留它们。对这些列的删除会减少 I/O 资源占用，并为后续的优化带来便利。

1658641914876.png

2.2.3.2.谓词下推

谓词下推将查询语句中的过滤表达式计算尽可能下推到距离数据源最近的地方，以尽早完成数据的过滤，进而显著地减少数据传输或计算的开销。

1658642076935.png

2.2.3.3.传递闭包

传递闭包，根据谓词的等价关系可以推出新的过滤条件

1658642187958.png

2.2.3.4.Runtime Filter

Runtime Filter，它旨在在运行时为某些 Join 查询动态生成筛选条件，以减少扫描的数据量，避免不必要的 I/O 和网络传输，并加快查询速度。

1658642557882.png

  • min-max：builder方的min-max传给runtime filter的另一方用以加以筛选。缺陷：min-max范围必须很紧密。

  • in-list：另一侧的数据要在builder侧的list里面。缺陷：builder方的list很多的话，传输代价也很大。

  • bloom filter：固定效果。说不在就不在，说在就可能在。后面会详细介绍~

2.2.4.RBO小结

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

  • 优点:实现简单，优化速度快

  • 缺点:不保证得到最优的执行计划

    • 单表扫描:索引扫描(随机I/O) vs.全表扫描(顺序I/O)

      • 如果查询的数据分布非常不均衡，索引描可能不如全表扫描

    • Join的实现: Hash Join vs. SortMerge Join（没法选择更适合的join）

    • 两表Hash Join:用小表构建哈希表——如何识别小表?(识别不了小表)

    • 多表Join:

      • 哪种连接顺序是最优的?

      • 是否要对每种组合都探索?

        1. N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序
        2. e.g.N= 10->总共3, 628, 800个连接顺序

1658643607942.png

2.3.CBO (Cost-based Optimizer)

2.3.1.概念

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

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

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

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

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

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

    • 例子: Spark Join算子代价= weight * row_ count + (1.0 - weight) * size

1658644593071.png

2.3.2.统计信息

  • 统计信息给出表的大小、多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时候过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。

  • 因为数据库中的对象会经常的变化，所以统计信息必须有规律的更新以便更加准确的描述这些数据库对象。

  • 两类：

    • 原始表统计信息

      • 表或者分区级别:行数、行平均大小、表在磁盘中占用了多少字节等
      • 列级别: min、max、num nulls 空值、 num not nulls 非空值、num distinct value(NDV) 不同值的数量、histogram直方图 等

    • 推导统计信息

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

  • 收集方式

    • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息（会影响实时导入、实时插入速率）

1658645755188.png - 手动执行explain analyze statement,触发数据库收集或者更新统计信息

1658645773035.png - 动态采样

1658645783134.png

  • 推导规则

    fs 选择率

1658647158927.png

  • 问题

    假设与实际不符（列与列相互独立，列的值均匀分布）

2.3.3.执行计划枚举

1658648072507.png

1658648696834.png

1658648713667.png

1658648723170.png

2.3.4.CBO小结

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

2.4.小结

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

3.社区开源实践

3.1.概览

1658649574305.png

3.2.Apache Calcite

3.2.1.概览

1658649626438.png

Apache Calcite是一款开源的动态数据管理框架，它提供了标准的 SQL 语言、多种查询优化和连接各种数据源的能力，但不包括数据存储、处理数据的算法和存储元数据的存储库。

Calcite 的目标是“one size fits all（一种方案适应所有需求场景）”，希望能为不同计算平台和数据源提供统一的查询引擎。

3.2.2.Calcite RBO

  • HepPlanner (启发式Planner 、"基于规则的Planner")

    • 优化规则(Rule)

      • Pattern: 匹配表达式子树
      • 等价变换: 得到新的表达式

    • 内置有100+优化规则

    • 四种匹配规则

      • ARBITRARY/DEPTH FIRST:深度优先
      • TOP_ DOWN:拓扑顺序
      • BOTTOM UP:与TOP DOWN相反

    • 遍历所有的rule,直到没有rule可以被触发

    • 优化速度快，实现简单，但是不保证最优

3.2.3.Calicite CBO

  • VolcanoPlanner

    • 基于Volcamno)/( ascade框架

    • 成本最优假设

    • Memo: 存储候选执行计划

      • Group: 等价计划集合

    • Top-down 动态规划搜索

    • 应用Rule搜索候选计划

    • Memo

      • 本质: AND/OR graph
      • 共享子树减少内存开销

    • Group winner:目前的最优计划1658650651563.png

    • 剪枝(Branch- and- bound pruning) :减少搜索空间1658650576334.png

    • TOP dowmn遍历:选择winere构建最优执行计划1658650777346.png

3.3.小结

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

4.前沿趋势

对SQL优化器的新要求：

  1. 引擎架构的进化（存储计算分离）
  2. Cloud 云原生
  3. 湖仓一体 query fedreation
  4. DATA+AI

1658651452121.png

5.课程总结

1658651519919.png

avatar
文章
阅读
粉丝