SQL Optimizer 解析 |青训营笔记

用户9303169714586
61 阅读5分钟

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

一、大数据体系大数据体系和 SQL

image.png 大数据体系主要分为七层，从下至上依次为：

1.基础设施，主要是ESC,存储，VPC，主要有日志查询管理组件。

2.存储设施，有HDFS,HBase,等自研的存储系统，有监控报警功能。

3.资源调度，有YARN,K8S，主要有用户管理功能。

4.分析引擎，主要分为三类，有批式分析，Spark，Hive，MR;有实时分析Flink；有交互分析Presto等。消息队列主要用Kafka等，主要用于存储的计算的解耦。主要功能是集群管理，服务管理。

5.权限管控，有Apache Ranger，GDPR，用来集群创建。

6.数据开发，主要有一个DAG任务流调度系统和Airflow，用来集群创建。

7.业务应用，有BI报表，数据挖掘，营销分析等，用来管控运维。

sql的处理流程

image.png

  • 抽象语法树（Abstract Syntax Tree，AST）
  • 逻辑计划（Logical Plan）
  • 物理计划（Physical Plan）
  • 解析器（Parser）
  • 分析器（Analyzer）
  • 优化选择器（Optimizer）
  • 算子( operator )

Parser

●String -> AST (Abstruct Syntax Tree)：

  • 词法分析:拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
  • 语法分析:将token组成ASTnode，最终得到一个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 )

image.png

image.png

image.png

Physical Plan 和 Executor

● Physical Plan: 执行计划子树

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

● Executor

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

image.png

小结

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

二、常见的查询优化器

查询优化器分类

RBO:基于规则的优化方式 (Rule-Based Optimization)
CBO:基于代价的优化方式 (Cost-Based Optimization)

image.png

RBO

RBO-关系代数

  • 运算符：select，project，join，rename，union等
  • 等价变换：结合律，交换律，传递性

RBO-优化原则

  • I/O 读更少的数据，读数据更快
  • Network 传输数据更少更快
  • CPU&Memory cpu指令数更少，内存占用更少

RBO-列裁剪

image.png image.png

RBO-谓词下推

SQL中的谓词主要有 LKIE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS。

image.png image.png

RBO-传递 闭包

image.png image.png

RBO-Runtime Filter

image.png

image.png

RBO-小结

image.png

CBO

CBO-概念

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

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

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

  • 和算子输入数据的统计信息有关：算子的输入，输出结果的行数，每行大小
  • 叶子算子Scan：通过统计原始表数据得到
  • 中间算子：根据一定的推到规则，从下层算子的统计信息推导得到
  • 和具体的算子类型，以及算子的物理实现有关

image.png

CBO-统计信息

原始表统计信息

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

推导统计信息

  • 选择率：对于某一个过滤调价，查询会从表中返回多大比例的数据
  • 基数：在查询计划中长治算子需要处理的行数

统计信息的收集方式

统计信息收集方式包括DDL中收集，手动执行explain、analyze、statement触发数据库收集或者更新统计信息，动态采样等

统计信息推导规则

前提假设列和列之间独立，列的值是均匀分布 ①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 V1/NDV
⑤小于条件 (x < literal) literal < min: 0 literal > max: 1 (literal - min) / (max - min)

统计信息推导规则

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

CBO小结

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

小结

image.png

三、社区开源实践

一、概览\

image.png

二、Apache Calcite

1、概览

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

2、Calcite RBO

HepPlanner

  • 内置100+种优化规则，四种匹配机制，遍历所有的rule直到没有rule可以被触发
  • 优化速度快，实现简单，但是不保证最优

3、Calcite CBO

VolcanoPlanner

  • 基于Volcano/Cascadek框架
  • 成本最优假设
  • Mome：存储候选执行计划
  • Group：等价计划合集
  • Top-down：动态规划搜索
  • Volcano/Cascadek精髓：Memo、动态规划、剪枝

小结

image.png

四、前沿趋势

概览

（1）引擎架构的进化

  • 存储计算分离
  • 一体化（HTAP、HSAP、HTSAP）

(2)Cloud云原生

  • serverless

(3)湖仓一体

  • Query Federation

(4)DATA + AI

1、AI4DB

  • 自配置（智能调参、负载预测/调度）
  • 自诊断和自愈合（错误恢复和迁移）
  • 自优化（统计信息估计、代价估计、学习型优化器、索引/视图推荐）

2、DB4AI

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

小结

image.png

五、课程总结

image.png

六、个人思考

对整个大数据体系以及SQL有了初步的认识，在第一天课程里着重讲了查询优化器，对其的分类以及RBO、CBO有了一定的了解，知道他们的特点。

avatar
文章
阅读
粉丝