SQL 查询优化器 简介及其应用 | 青训营笔记

梨涡泥窝
234 阅读8分钟

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

大数据体系

image-20220725230501867.png

大数据体系和SQL

简介

  • 介绍大数据体系和SQL的处理流程，重点介绍SQL在分布式环境下的处理
  • One SQL rules big data all

处理过程

image-20220725230602310.png

Parser

  • String -> AST ( abstract syntax tree )

    • 词法分析:拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
    • 语法分析:将token组成.AST node，最终得到一个AST
    • 实现:递归下降(ClickHouse)，Flex和Bison(PostgreSQL) , JavaCC(Flink)，Antlr (Presto, Spark)

Analyzer和Logical Plan

  • Analyzer

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

  • Logical Plan

    • 逻辑地描述SQL对应的分步骤计算操作
    • 计算操作:算子( operator )

查询优化

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

Plan Fragment和Executor

  • Plan Fragment:执行计划子树

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

  • Executor

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

小结：

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

常见的查询优化器

两类优化器

第一类

  • Top-down Optimizer(自顶向下优化器)

    • 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
    • 例子:Volcano/Cascade , SQLServer

  • Bottom-up Optimizer(自下而上优化器)

    • 从零开始，由下往上遍历计划树，找到完整的执行计划
    • 例子:System R ，PostgreSQL，IBM DB2

第二类

  • Cost-based optimizer (CBO,基于成本的优化器)

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

  • Rule-based Optimizer (RBO,基于规则的优化器)

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

RBO

关系代数

image-20220725231304331.png

优化原则

  • 读取数据更少更快（I/O）
  • 传输数据更少更快（网络）
  • 处理数据更少更快（CPU和内存）

列裁剪

image-20220725231526275.png

谓词下推

image-20220725231552400.png

传递闭包

image-20220725231608494.png

runtime filter (运行时筛选器)

image-20220725231626056.png

RBO总结

  • 主流 RBO实现一般都有几百条基于经验归纳得到的优化规则
  • 优点:实现简单，优化速度快

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

    • 单表扫描:索引扫描（随机I/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个连接顺序

CBO

CBO概念

image-20220725231841029.png

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

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

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

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

      • 叶子算子Scan:通过统计原始表数据得到
      • 中间算子:根据一定的推导规则，从下层算子的统计信息推导得到
    • 和具体的算子类型，以及算子的物理实现有关
    • 例子：Spark Join算子代价= weight * row_count + (1.0 - weight) * size

统计信息

  • 原始表统计信息

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

  • 推导统计信息

    • 选择率( selectivity ):对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数( cardinality )∶在查询计划中常指算子需要处理的行数
  • 准确的cardinality，远比代价模型本身重要。 ——“How Good Are Query Optimizers, Really?
  • 统计信息的收集方法

image-20220725231939325.png

  • 统计信息推导规则

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

    • Filter Selectivity

      • AND条件: fs(a AND b)= fs(a)* fs(b)
      • OR条件: fs(a OR b)= fs(a) + fs(b)-(fs(a) * fs(b)
      • NOT条件:fs(NOT a)= 1.0 - fs(a)

      • 等于条件(x = literal )

        • literal < min && literal > max : 0
        • 1/NDVR

      • 小于条件(x< literal )

        • literal < min : 0
        • literal > max : 1
        • ( literal - min ) / ( max - min )

统计信息的问题

  • 直方图怎么处理？

执行计划枚举

  • 单表扫描:索引扫描（随机I/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个连接顺序
  • 通常使用贪心算法或者动态规划选出最优的执行计划

CBO效果

image-20220725232726338.png

  • TPC（Transaction Processing Performance Council） 是什么？

    • 在80年代初期，交易的自动化越来越普及，比如ATM机，并逐渐扩展到各种方面比如售货店买东西，加油站加油等，即所谓的OnLine Transaction Processing，OLTP系统开始涌现。此时出现了一些benchmark，比如IBM提出的TP1，Jim Gray等人提出的DebitCredit，而因为缺乏很好的review和发布流程，这会让大家的性能结果可信度较低，往往是一片混乱。最终在1988年，Omri Serlin联合了一些公司成立了TPC，也就是Transaction Processing Performance Council。

  • TPC-DS（decision support）

    • TPC组织总是希望可以构建更好的benchmark，workloads会随着应用发展的变化而变化，比如TPC-A和TPC-B已经不再使用，OLTP的标准现在是TPC-C。另外TPC也不再局限于做TP领域的benchmark，对于不同的workloads都有对应的benchmark，比如用于决策支持的TPC-DS。

    • 业务模型分为以下三个方面

      • 数据模型和数据访问
      • 查询模型
      • 数据维护

CBO小结

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

小结

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

社区开源实践

概览

image-20220725233240601.png

Apache Calcite概览

  • One size fits all:统一的SQL查询引擎模块化，插件化，稳定可靠

  • 支持异构数据模型

    • 关系型
    • 半结构化
    • 流式
    • 地理空间数据
  • 内置RBO和CBO

Calcite RBO

  • HepPlanner

    • 优化规则( Rule )

      • Pattern :匹配表达式子树
      • 等价变换:得到新的表达式
    • 内置有100+优化规则

    • 四种匹配规则

      • ARBITRARY/DEPTH_FIRST:任意/深度优先
      • TOP_DOWN:拓扑顺序
      • BOTTOM_UP:与TOP_DOWN相反（自底向上）
    • 遍历所有的rule，直到没有rule可以被触发
    • 优化速度快，实现简单，但是不保证最优

  • VolcanoPlanner

    • 基于Volcano/Cascade框架
    • 成本最优假设

    • Memo:存储候选执行计划

      • Group:等价计划集合

    • Top-down动态规划搜索

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

    • 应用Rule搜索候选计划

      • Memo

        • 本质:AND/OR graph
        • 共享子树减少内存开销
    • Group winner :目前的最优计划
    • 剪枝(Branch-and-bound pruning):减少搜索空间

小结

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

前沿趋势

DATA+AI

AI4DB

  • 自配置

    • √智能调参(OtterTune,QTune )
    • √负载预测/调度
  • √自诊断和自愈合∶错误恢复和迁移

  • √自优化︰

    • √统计信息估计(Learned cardinalities )
    • √代价估计
    • √学习型优化器（IBM DB2 LEO )
    • √索引/视图推荐

DB4AI

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

小结

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

课程总结

image-20220725233858799.png

avatar
文章
阅读
粉丝