课堂笔记1|SQL Optimizer 解析

用户5836265636748
146 阅读10分钟

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

总览——大数据体系

image.png

Part1 大数据体系和SQL

1.1 大数据体系中的SQL

One SQL rules big data all. -> 用SQL处理所有的大数据

SQL的优势：

（1）比其他编程语言简单、易用

（2）丰富的接口——分析引擎的Spark、MapReduce、Flink等等都提供/增加了SQL接口

1.3 SQL的处理流程

image.png

SQL的处理流程——Parser

输入：字符串

输出：抽象语法树（AST, abstract syntax tree）

2个功能：

1. 词法分析： 拆分字符串，得到token（token：关键词、数值常量、字符串常量、运算符号等）

2. 语法分析： 将token组成AST node，最终得到AST。（即：token -> node -> tree）

Parser的实现：

  • 递归下降 (ClickHouse)
  • Flex 和 Bison(PostgreSQL)
  • JavaCC (Flink)
  • Antlr (Preto, Spark)

AST： image.png （将SELECT语句拆分为多个部分）

SQL的处理流程——Analyzer 和 Logical Plan

Analyzer的输入：AST

Analyzer的输出：Logical Plan

Analyzer的功能：

  • 检查并绑定Database, Table, Column等元信息
  • SQL语法的合法性检查
  • 将AST转化为Logical Plan （在某些系统中这个工作由一个 Converter 完成）

Logical Plan：

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

image.png

将上述SQL转化为对应的逻辑计划：

image.png

补充：left-deep tree是新join进来的表一定在右边，下如图：

image.png 参考：数据库内核杂谈（八）：数据库优化器（下）_语言 & 开发_顾仲贤_InfoQ精选文章

SQL的处理流程——查询优化☆☆☆

为什么需要查询优化？

SQL是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做。因此具有优化的空间。

目标

找到一个正确执行、代价最小的物理执行计划

查询优化的重要性

  1. 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP 的
  2. 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍 （类比 gcc/g++ 编译程序时的编译级别（-O1, -O2, -O3），经过编译优化的程序运行效率更高）

SQL的处理流程——Physical Plan 和 Executor

分布式环境下，对逻辑计划的各个部分进行拆分，将完整执行计划拆分为子树，即Plan Fragment。

各个节点收到Plan Fragment后，利用Executor执行计划。

Plan Fragment：执行计划子树

  • 目标：最小化网络数据传输
  • 利用数据的物理分布（数据亲和性）
  • 增加Shuffle算子 （分布式系统中，一个表中的数据可能存储在多个节点中，读取数据时尽量使节点读取本地的数据，避免网络开销）

（Shuffle算子用于连接各个节点，发送/接收数据）

Executor

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

image.png

image.png

小结

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

Part2 常见的查询优化器

2.1 查询优化器分类

按遍历方式分类：

1. Top-down Optimizer

从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划

2. Bottom-up Optimizer

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

按优化方式分类：

1. Rule-based Optimizer (RBO)

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

2. Cost-based Optimizer (CBO)

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

2.2 常见的查询优化器——RBO

2.2.1 关系代数

image.png

2.2.2 优化原则

4种优化规则：

  • 列裁剪
  • 谓词下推
  • 传递闭包
  • Runtime Filter

总体原则：尽可能读取 更少的 列 / 行

2.2.3 列裁剪

尽早裁剪掉用不到的列 优化器从上往下扫描，传递到最底层的 SCAN 时，就知道只需要哪些表中的哪些列

image.png

2.2.4 谓词下推

尽早对 where 语句中的条件进行筛选

image.png

注意：谓词下推有条件！

（思考题：不同的连接方式如 内连接、左外连接、右外连接的谓词下推条件是什么？）

2.2.5 传递闭包

“传递性”的应用：由原来的过滤条件推导出新的过滤条件

image.png

2.2.6 Runtime Filter

在执行时产生的Filter

（在执行过程中，根据扫描到的数据的特点，确定新的Filter？）

image.png

三种Filter特点
min-max限定了筛选范围，不适用于范围很大但包含值很少的情况
in-list限定了筛选值，不适用于需要筛选的值很多的情况
bloom filter限定某个值是否应该被读取？

小结

  • 主流 RBO 实现一般几百条基于经验归纳得到的优化规则
  • 优点：实现简单、优化速度块
  • 缺点：不能保证得到最优的执行计划

1）单表扫描

索引扫描（随机I/O）vs. 全表扫描（顺序I/O）

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

2）join的实现

Hash join vs. SortMerge Join

3）两表 Hash Join

用小表构建Hash表——如何识别小表？

4）多表Join

哪种连接顺序是最优的？

是否对每种组合都探索？（N个表连接，Left-deep tree 有N！种连接顺序）

image.png

2.3 常见的查询优化器——CBO

概念

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

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

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

  • 和算子输入数据的统计信息有关:输入、输出结果的行数，每行大小...
    • 叶子算子Scan：通过统计原始表数据得到
    • 中间算子：根据定的推导规则，从 下层 算子的统计信息推导得到
  • 和具体的算子类型，以及算子的物理实现有关
  • 例子
    • Spark Join算子代价统计信息= weight * row_ count + (1.0 - weight) * size 其中，weight表示权重，用于调控 CPU（row_ count） 与内存（size）的重要性占比

CBO的执行流程：

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

2.3.1 统计信息

  • 原始表统计信息

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

  • 推导统计信息

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

准确的 cardinality，远比代价模型本身重要。

统计信息的收集方式

  1. 在 DDL 里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息

    对 R_NAME 列收集统计信息：

    CREATE TABLE REGION(
 R_REGIONKEY INT NOT NULL,
 R_NAME CHAR(25) NOT NULL,
 R_COMMENT VARCHAR(152)
) DUPLICATE KEY(RREGIONKEY)
DISTRIBUTED BY HASH(RREGIONKEY) BUCKETS 1
PROPERTIES ("stats_columns" = "R_NAME");

    缺点：影响实时导入数据的速率

  2. 手动执行 explain analyze statement ，触发数据库收集或者更新统计信息

    ANALYZE TABLE table_ name COMPUTE STATISTICS FOR COLUMNS column-name1, column-name2, ....

    缺点：数据延迟

  3. 动态采样

    用SELECT语句统计表的行数：

    SELECT count(*) FROM table_name;
统计信息推导规则

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

  • Filter Selectivity——计算选择率

    1. AND条件: fs(a AND b) = fs(a) * fs(b)

    2. OR条件: fs(a OR b) = fs(a) + fs(b)一(fs(a) * fs(b))

    3. NOT条件: fs(NOT a)= 1.0- fs(a)

    4. 等于条件（x = literal）

      literal < min && literal > max：0 （literal小于列最小值 并且 大于最大值？）

      1 / NDV

    5. 小于条件（x < literal）

      literal < min：0

      literal > max：1

      (literal- min) / (max - min)

    举例：Filter的基数 = A节点的基数 * Filter的选择率（Filter是A节点的父节点）
cardinality(FILTER) = cardinality(A) ∗ selectivity(FILTER)cardinality(FILTER) = cardinality(A) * selectivity(FILTER)
统计信息的问题

假设 “列和列之间是独立的，列的值是均匀分布” 不符合现实情况！

1. 列和列之间不一定是相互独立的

解决方案：用户指定或数据库自动识别相关联的列

2. 列的值不一定是均匀分布的

解决方案：直方图

2.3.2 执行计划枚举

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

动态规划

image.png

image.png

image.png

image.png

image.png

CBO效果：

image.png

image.png

CBO小结

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

2.4 查询优化器小结

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

Part3 社区开源实践

概览

数据库SQL Optimzer选型
Hive、Flink、 Alibaba MaxCompute等基于Apache Calcite，属于Volcano/Cascade框架
Greenplum、HAWQ自研Orca，属于Volcano/Cascade框架
Alibaba Hologres (定位HSAP）基于Orca，属于Volcano/Cascade框架
TiDB自研，属于Volcano/Cascade框架
Spark自研，RBO + CBO
Presto自研，RBO + CBO
Doris自研，RBO + CBO
ClickHouse自研，RBO
Alibaba OceanBase自研，RBO + CBO

3.1 Apache Calcite 概览

  • One size fits all：统一的SQL查询引擎
  • 模块化，插件化，稳定可靠
  • 支持异构数据模型
    • 关系型
    • 半结构化
    • 流式
    • 地理空间数据
  • 内置 RBO 和 CBO

Apache Calcite的基本组件如下： image.png

Metadata Providers：提供表的基本信息

Pluggable Rules：适用于特定系统的优化规则

3.2 Calcite RBO

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

image.png

3.3 Calcite CBO

  • VolcanoPlanner

    • 基于Volcano/Cascade框架

    • 成本最优假设

image (1).png

image.png

image.png

  • Memo：存储候选执行计划

    • Group: 等价计划集合

    • 本质：AND/OR graph

    • 共享子树减少内存开销

  • Top-down 动态规划搜索
    • 选择winner构建最优执行计划

image.png

  • 应用 Rule 搜索候选计划

  • Group winner：目前的最优计划

  • 剪枝（Branch-and-bound pruning）：减少搜索空间

image.png

小结

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

Part4 前沿趋势

对SQL优化器的新要求：

  1. 引擎架构的进化

    （1） 存储计算分离

    （2）一体化 （HTAP, HSAP, HTSAP），融合事务性和分析型数据

  2. Cloud

    云原生，serverless/K8S，根据流量缩减/扩充计算节点

  3. 湖仓一体

    数据湖：以文件形式存储原始数据

    将数据仓库与数据湖统一

  4. DATA + AI

    （1）AI4DB

    （2）DB4AI

课程总结

image.png

avatar
文章
阅读
粉丝