SQL流程&查询优化器&实践 ｜ 青训营笔记

用户1072980630308
177 阅读9分钟

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

大数据体系

image.png

课程目录

image.png

大数据体系和SQL

SQL的处理流程

image.png

Parser

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

Analyzer and Logical Plan

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

查询优化

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

Physical Plan and Executor

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

image.png

小结

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

常见的查询优化器

image.png

查询优化树分类

第一类(执行方向)
  • Top-down Optimizer
    • 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
    • 例子：Volcano/Cascade，SQLServer
  • Bottom-up Optimizer
    • 从零开始，由下往上遍历计划树，找到完整的执行计划
    • 例子：System R，PostgreSQL，IBM DB2
第二类(基于不同的选择方向)
  • Rule-based Optimizer (RBO)
    • 根据关系代数等价语义，重写查询
    • 基于启发式规则
    • 会访问表的元信息(catalog)，不会涉及具体的表数据(data)
  • Cost-based Optimizer (CBO)
    • 使用一个模型估算执行计划的代价，选择代价最小的执行计划

RBO

关系代数

image.png image.png image.png

优化原则

  • Read data less and faster (I/O)
  • Transfer data less and faster (Network)
  • Process data less and faster (CPU & Memory) 以下优化方式基于此SQL image.png 优化前的逻辑计划 image.png

列裁剪

仅选择SQL中出现的列名 image.png image.png

谓词下推

像这样的，不必等到join后再筛选，而是在选着列之后，就可以进行筛选 image.png image.png

谓词下推不同的join有不同实现： image.png

  1. 对于Join(Inner Join)、Full outer Join，条件写在on后面，还是where后面，性能上面没有区别
  2. 对于Left outer Join，右侧的表写在on后面、左侧的表写在where后面，性能上有提高
  3. 对于Right outer Join，左侧的表写在on后面、右侧的表写在where后面，性能上有提高
  4. 当条件分散在两个表时，谓词下推可按上述结论2和3自由组合，情况如下：
SQL过滤时机
select ename,dept_name from E left outer join D on ( E.dept_id = D.dept_id and E.eid='HZ001' and D.dept_id = 'D001');dept_id在map端过滤，eid在reduce端过滤
select ename,dept_name from E left outer join D on ( E.dept_id = D.dept_id and D.dept_id = 'D001') where E.eid='HZ001';dept_id，eid都在map端过滤
select ename,dept_name from E left outer join D on ( E.dept_id = D.dept_id and E.eid='HZ001') where D.dept_id = 'D001';dept_id，eid都在reduce端过滤
select ename,dept_name from E left outer join D on ( E.dept_id = D.dept_id ) where E.eid='HZ001' and D.dept_id = 'D001';dept_id在reduce端过滤，eid在map端过滤

注意：如果在表达式中含有不确定函数，整个表达式的谓词将不会被pushed。

传递闭包

基于某种条件，一边满足，则另一半也满足，则可以进行双边优化 image.png image.png

Runtime Filter

在运行时，才可知晓结果的量级，通过min-max，in-list，bloom filter可以实现对应的范围缩减，使得另一个得到运行时的优化 image.png

小结

  • 主流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个连接顺序
  • ... image.png
  • 5G -> 过滤 -> 1M
  • 100M
  • 虽然开始判断选择100M的好，但在运行时，过滤后5G就缩减为1M，则是1M的更好，无法判断涅~

CBO

概念

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

统计信息

  • 原始表统计信息
    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
    • 列级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等
  • 推导统计信息
    • 选择率(selectivity)：对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数(cardinality)：在查询计划中常指算子需要处理的行数
准确的cardinality，远比代价模型本身重要。——"How Good Are Query Optimizers, Really?"

统计信息的收集方法

  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息 image.png
  • 手动执行explain analyze statement，触发数据库收集或者更新统计信息 image.png
  • 动态采样 image.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.0
      • 1.0 / NDV
    • 小于条件(x < literal)
      • literal < min：0.0
      • literal > max：1.0
      • (literal - min) / (max - min) image.png

统计信息的问题

  • 假设列和列之间是独立的，列的值是均匀分布 ————>这个假设经常与现实不符!
  • 考虑一个汽车数据库automobiles，有10个制造商，100个车型，filter为“制造商 = ‘比亚迪’ 且 车型 = ‘汉’”，根据独立性和均匀分布假设，则selectivity = 1/10 × 1/100 = 0.001,但是‘比亚迪′和‘汉’是相关联的，实际selectivity = 1/100 = 0.01
    ————>用户指定或者数据库自动识别相关联的列
  • 考虑中国人口数据库，性别，年龄，数量都不是均匀分布 ————>直方图

执行计划枚举

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

执行计划枚举——动态规划

image.png

image.png

image.png

image.png

image.png

效果—TPC—DS Q25

image.png

image.png

效果—TPC—DS

image.png

image.png

小结

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

小结

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

社区开源实践

Apache Calcite

概览

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

Calcite RBO

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

Calcite CBO

  • VolcanoPlanner

    • 基于Volcano/Cascade框架
    • 成本最优假设
    • Memo：存储候选执行计划(慢慢变为最优执行计划)
      • Group：等价计划集合(是动态规划中每一个状态的表示)
    • Top-down动态规划搜索 image.png

  • VolcanoPlanner

    • 应用Rule搜索候选计划
    • Memo
      • 本质：AND/OR graph
      • 共享子树减少内存开销 image.png

  • VolcanoPlanner

    • Group winner：目前的最优计划 image.png

  • VolcanoPlanner

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

  • 可行的Aggregate(450的枝被剪掉了)

    • 总的cost = 500
    • 自己的cost = 150
    • 孩子节点cost上限 = 350

  • VolcanoPlanner

    • Top-down遍历：选择winner构建最优执行计划(重构最优执行计划) image.png

小结

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

课程总结

image.png

avatar
文章
阅读
粉丝