SQL Optimizer 解析 | 青训营笔记

serendipity__
120 阅读7分钟

这是我参与「第四届青训营 」笔记创作活动的第1天，第一次学习大数据，感觉有点难消化，，，

一、大数据体系和SQL

1、大数据体系

image.png

2、SQL的处理流程

image.png

SQL 相关的前沿趋势

  • 存储计算分离

  • HSAP, HTAP, HTSAP

  • Cloud Native, Serverless

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

  • 智能化：AI4DB，DB4AI

2.1 SQL处理流程——Parser

  • String->AST（abstract syntax tree）
    • 词法分析：拆分字符串，得到关键词，数量常量，字符串常量，运算符号等token
    • 语法分析：将token组成AST node，最终得到一个AST
  • 实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），Javacc（Flink），Antlr（Presto，Spark）

image.png

  • Analyzer
    • 检查并绑定Database，Table，Column等原信息
    • SQL的合法检查，比如min/max/avg的输入是数值
    • AST->Login Plan
  • Login Plan
    • 逻辑地描述SQL对应的分步骤计算操作
    • 计算操作：算子（operator）

2.2 SQL的处理流程-查询优化

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

常见的查询优化器

  • Top-down Optimizer

  • Bottom-up Optimizer

  • Rule-based Optimizer，RBO

    • Rule
    • Pattern

  • Cost-based Optimizer，CBO

    • 动态规划

  • 交换律、结合律、传递性

  • RBO 优化规则

    • 列裁剪
    • 谓词下推
    • 传递闭包
    • Runtime Filter（min-max filter，in-list filter，bloom filter）
    • Join 消除
    • 谓词合并

  • CBO 相关概念

    • 统计信息
      • Number of Distinct Value，NDV
      • Selectivity
      • Cardinality
    • 代价模型

查询优化器的社区开源实践

  • Apache Calcite

  • Orca

  • Volcano/Cascade 框架

    • Memo
    • AND/OR Graph
    • Expression group
    • Group expression
    • Pattern
    • Rule
    • Branch-and-Bound Pruning
    • Winner

2.3 SQL的处理流程-Physical Plan和Executor

  • Plan Fragment：执行计划子树
    • 目标：最小化网络数据传输
    • 利用上数据的物理分布（数据亲和性）
    • 增加 Shuffle算子
  • Excutor
    • 单机并行：cache，pipeline，SIMD
    • 多机并行：一个fragment对应多个实例

物理执行计划

image.png

小结：

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

二、 常见的查询优化器

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

1、查询优化器分类

分类一：

image.png

分类二：

  • Rule-based Optimizer (RBO)
    • 根据关系代数等价语义，重写查询√基于启发式规则
    • 会访问表的元信息.(catalog)，不会涉及具体的表数据(data)
  • Cost-based Optimizer (CBO)
    • 使用一个模型估算执行计划的代价，选择代价最小的执行计划

1.1 RBO-关系代数

  • 运算符：Select( σ)，Project(π) ，Join(⋈)，Rename( ρ)，Union(∪)等
  • 等价变换：交换律，结合律，传递性 eg：

image.png

image.png

1.2 RBO-优化原则

image.png

1.3 RBO-列裁剪

image.png image.png

1.4 RBO-谓词下推

image.png

image.png

1.5 RBO-传递闭包

image.png

image.png

1.6 RBO-Runtime Filter

image.png image.png

RBO小结

  • 主流RBO实现一般都有几百条基于经验归纳得到的优化规则
  • 优点:实现简单，优化速度快
  • 缺点:不保证得到最优的执行计划
    • 单表扫描:索引扫描(随机1/O) vs.全表扫描(顺序I/O)
      • 如果查询的数据分布非常不均衡，索引描可能不如全表扫描
    • Join的实现: Hash Join vs. SortMerge Join
    • 两表Hash Join:用小表构建哈希表如何识别小表?
    • 多表Join:
      • 哪种连接顺序是最优的?
      • 是否要对每种组合都探索?
        • N个表连接,仅仅是left-deep tree就有差不多N! 种连接顺序
        • e.g.N= 10->总共3, 628, 800个连接顺序 eg： image.png

2.1 CBO概念

  • 使用一个模型估算执行计划的代价，选择代价最小的执行计划
    • 执行计划的代价等于所有算子的执行代价之和
    • 通过RBO得到(所有)可能的等价执行计划
  • 算子代价: CPU,内存，磁盘I/O,网络I/O等代价
    • 和算子输入数据的统计信息有关:输入、输出结果的行数，每行大小...
      • 叶子算子Scan:通过统计原始表数据得到
      • 中间算子:根据定的推导规则， 从下层算子的统计信息推导得到
    • 和具体的算子类型，以及算子的物理实现有关
    • 例子: Spark Join算子代价= weight * rOw_ count + (1.0 - weight) * size

image.png

2.2 CBO-统计信息

  • 原始表统计信息
    • V表或者分区级别:行数、行平均大小、表在磁盘中占用了多少字节等
    • V列级别: min、max、num nulls、num not nulls、 num distinct value(NDV)、histogram 等
  • 推导统计信息
    • V选择率(selectivity) :对于某一个过滤条件， 查询会从表中返回多大比例的数据
    • 1基数(carclinality) :在查询计划中常指算子需要处理的行数

2.3 CBO-统计信息收集方式

  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息
R_REGIONKEY INT NOT NULL, 
R_NAME CHAR(25)NOTNULL, 
R_COMMENT VARCHAR(152) 
)DUPLICATE KEY(R_REGIONKEY) 
DISTRIBUTED BY HASH(R_REGIONKEY) BUCKETS 1 
PROPERTlES("stats_columns" = "R_NAME");
  • 手动执行explain analyze statement，触发数据库收集或者更新统计信息

ANALYZETABLE table_name cOMPUTE STATISTICSFOR COLUMNS column-name1, column-name2,...

  • 动态采样 SELECT count（*）FROM table_name

2.4 CBO-统计信息推导规则

image.png

2.5 CBO-统计信息的问题

image.png

2.6 CBO-执行计划枚举

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

  • 单表扫描:索引扫描(随机/O)vs.全表扫描(顺序/O)
    • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
  • Join的实现:Hash Join vs.SortMerge Join
  • 两表Hash Join:用小表构建哈希表如何识别小表?
  • 多表Join:
    • 哪种连接顺序是最优的?
    • 是否要对每种组合都探索?
      • N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序
      • e.g.N=10->总共3,628,800个连接顺序

2.6.1 CBO-执行计划枚举-动态规划

image.png

image.png

2.7 CBO效果-TPC-DS Q25

image.png

image.png

image.png

image.png

CBO小结

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

三、社区开源实践

社区开源实践-概览

image.png

3.1 社区开源实践=Apache Calcite 概览

image.png

3.2 社区开源实践-Calcite RBO

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

image.png

3.3 社区开源实践-Calcite CBO

- VolcanoPlanner

  • 基于Volcano/Cascade框架
  • 成本最优假设
  • Memo:存储候选执行计划
    • Group: 等价计划集合
  • Top-down 动态规划搜索

image.png

- VolcanoPlanner

  • 应用Rule搜索候选计划
  • Memo
    • 本质: AND/OR graph
    • 共享子树减少内存开销
  • Group winner：目前的最优计划

image.png

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

image.png

  • Top-down遍历:选择winner构建最优执行计划

image.png

小结：

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

四、前沿趋势

image.png

4.1 前沿趋势-DATA+AI

  • AI4DB
  • DB4AI
    • 内嵌人工智能算法(MLSQL，SQLFIow)
    • 内嵌机器学习框架(SparkML, Alink, dl-on-flink)

image.png

avatar
文章
阅读
粉丝