浅析SQL查询优化器 | 青训营笔记

林鹿白
212 阅读6分钟

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

一、大数据体系和SQL。

1.大数据体系中的SQL：

①大数据体系共有七个步骤，分为业务应用、数据开发、权限管控、分析引擎、资源调度、存储系统和基础设施，SQL就是分析引擎，它分为批式分析、实时分析、交互分析等，它的主要工作是集群管理、服务管理。

2.SQL的处理流程：
  1. 流程图：借助SQL转变为Parser,借助AST由Parser变为Analyzer，借助Logical Plan由Analyzer变成Optimizer，借助Physical Plan由Optimizer变成Executor。
  2. Parser：
  • String->AST（abstract syntax tree）
    • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
    • 语法分析：将token组成AST node，最终得到一个AST
    • 实现：递归下降，Flex和Bison，JavaCC，Antlr
  1. Analyzer和Logical Plan：
  • Analyzer：①检查并绑定Database，Table，Column等元信息②SQL的合法性检查，比如min/max/avg的输入是数值③AST->Logical Plan
  • Logical Plan：①逻辑地描述SQL对应的分步骤计算操作②计算操作：算子（operator）
  1. 查询优化：①SQL是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做②目标：找到一个正确且代价最小的物理执行计划③查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的④一般SQL越复杂，Join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能成百上千倍
  2. Physical PLan和 Executor：
  • Plan Fragment：执行计划子树
    • 目标：最小化网格数据传输
    • 利用上数据的物理分布（数据亲和性）
    • 增加shuffle算子
  • Executor：
    • 单机并行：cache，pipeline，SIMD
    • 多机并行：一个fragment对应多个实例

二、常见的查询优化器

1.查询优化器的分类：
  • Top-down Optimizer：
    • 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划。
  • Bottom-up Optimizer：
    • 从零开始，由下往上遍历计划树，找到完整的执行计划。
  • Rule-based Optimizer（RBO）：
    • 根据关系代数等价语义，重写查询
    • 基于启发式规则
    • 会访问标的元信息（catalog），不会涉及具体的表数据（data）
  • Cost-based Optimizer（CBO）：
    • 使用一个模型估算执行计划的代价，选择代价最小的执行计划
2.RBO：

1.RBO-关系代数

  • 运算符：Select、Project、Join、Rename、Union等
  • 等价交换：结合律、交换律、传递性
  1. RBO-优化原则：
  • 读数据操作尽可能少（I/O）
  • 传输数据尽可能少（Network）
  • 处理数据尽可能少（CPU&Memory）
  1. RBO-优化方式：
  • 列剪裁
    • 将不需要的列信息直接剪裁，大幅度减少查询时读取的数据量
  • 谓词下推
    • 将数据过滤条件向下推，大幅度减少join操作时所需要读取的数据量
  • 传递闭包
    • 根据表达式等价关系，过滤条件，推导出一个新的过滤条件，从而减少join操作时所需要读取的数据量
  • Runtime Filter
    • 在运行时利用列数据的min-max，in-list等信息减少bloom-filter操作时所需要读取的数据量
3. CBO：

1.CBO-概念：

  • 使用一个模型估算执行计划的代价，选择代价最小的执行计划
    • 执行计划的代价等于所有算子的执行代价之和
    • 通过RBO得到所有可能的等价执行计划
  • 算子代价：CPU，内存，磁盘I/O，网络I/O等代价
    • 和算子输入数据的统计信息有关：输入、输出结果的行数，每行大小...
      • 叶子算子Scan：通过统计原始表数据得到
      • 中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
    • 和具体的算子类型，以及算子的物理实现有关
  1. CBO-统计信息：
  • 原始表统计信息
    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
    • 列级别：min、max、num nulls、num not nulls、num distinct value（NDV）、histogram等
  • 推导统计信息
    • 选择率（selectivity）：对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数（cardinality）：在查询计划中常指算子需要处理的行数
  1. CBO-统计信息收集的方式：
  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息
  • 手动执行explain analyze statement，触发数据库收集或者更新统计信息
  • 动态采样
  1. CBO-统计信息推导规划

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

ede0554e81aa269bb01397eb56f07d5.jpg 5.CBO-统计信息的问题

  • 假设经常与现实不符
  • 用户指定或者数据库自动识别相关联的列
  1. CBO-执行计划枚举

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

三、社区开源实践。

1.社区开源实践-概览
数据库SQL Optimzer选型
Hive、Flink、Alibaba MaxComputer等基于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
2.社区开源实践-Apache Calcite概览
  • One size fits all：统一的SQL查询引擎
  • 模块化，插件化，稳定可靠
  • 支持异构数据模型
    • 关系型
    • 半结构化
    • 流式
    • 地理空间数据
  • 内置RBO和CBO

四、前沿趋势

1.前沿趋势-概览（对SQL优化器的新要求）

  • 引擎构架的进化
    • 存储计算分离
    • 一体化（HTAP、HSAP、HTSAP）
  • Cloud
    • 云原生
    • serverless
  • 湖仓一体
    • Query Federation
  • DATA + AI

2.前沿趋势-DATA+AI

  • AI4DB
    • 自配置
      • 智能调参
      • 负载预测/调度
    • 自诊断和自愈合：错误恢复和迁移
    • 自优化
      • 统计信息估计
      • 代价估计
      • 学习型优化器
      • 索引/视图推荐
  • DB4AI
    • 内嵌人工智能算法（MISQL，SQLFlow）
    • 内嵌机器人学习框架（SparkML、Alink、dl-on-flink）

总结

本次学习我记忆最深刻的是RBO和CBO这两个优化器，毕竟是第一次接触，所以在学习的过程中难免会觉得吃力，尤其是这两点，他们的知识点对我来说显得驳杂，不太好理解，下去之后我会多看，争取能有进一步的理解。

avatar
文章
阅读
粉丝