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

小小冰青松
195 阅读5分钟

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

学习了解了大数据体系和SQL，常见的查询优化器，查询优化器的社区开源实践和SQL相关的前沿趋势

一、大数据体系和SQL

1.1 SQL的处理流程

1.png

1.2.1 SQL的处理流程

· String->AST 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token 语法分析：将token组成AST node，最终得到一个AST · 实现：递归下降，Flex和Bisn（PostgreSQL），JavaCC（Flink），Antlr（Presto，spark）

2.png

1.2.2 SQL的处理流程--Analyzer和Logical Plan

Analyzer

  1. 访问库/表元信息并绑定
  2. 判断 SQL 是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确
  3. 将 AST 转换成逻辑计划树（在某些系统中这个工作由一个 Converter 完成）

Logical Plan

1.逻辑地描述SQL对应的分步骤计算操作

2.计算操作：算子（operator）

3.png

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

·sql是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做

·目标：找到一个正确且执行代码最小的物理执行计划

·查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的

·一般SQL越复杂，join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍

1.2.4 SQL的处理流程Physical和Executor

Physical：执行计划子树
目标：最小化网络数据传输
利用上数据的物理分布（数据亲和性）
增加shuffle算子
Executor
单机并行：cache，pipeline，SIMD
多机并行：一个fragment对应多个实例
  • 1.小结：
  • one sql rules big data all
  • SQL需要依次经过Parser，Analyzer，Optimizer和Executor的处理
  • 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
  • 查询优化器需要感知数据分布，充分利用数据的亲和性
  • 查询优化器按照最小网络数据传输的目标把逻辑计划拆分成多个物理计划片段

2.常见的查询优化器

2.1查询优化器分类

Top-down Optimizer

·从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划

·例子：Volcano/Cascade,SQLServer

Bottom-up Optimizer

·从零开始，由下往上遍历计划树，找到完整的执行计划

·例子：System R，PostgreSQL，IBM DB2

查询优化器：
RBO
根据关系代数等价语义，重写查询
基于启发式规则
会访问表的元信息，不会涉及具体的表数据（data）
CBO
使用一个模型估算执行计划的代价，选择代价最小的执行计划

2.2.1 RBO-关系代数

运算符：Select，Project，Join，Rename，Union

等价变换：结合律，交换律，传递性

2.2.2 RBO-优化规则

（I/O） Network CPU内存

2.2.3 RBO-列裁剪

6.png 2.2.4 RBO-谓词下推

where user.sitedld>123

思考：不同类型的join 哪些能推

2.2.5 RBO-传递闭包

7.png 2.2.6 RBO-Runtime Filter

8.png 运行时 Filter

JOIN hash表，提早过滤部分数据，右边Filter优化执行计划

2.2 RBO小结

·主流RBO实现一般都有几百条基于经验归纳得到的优化规则

·优点：实现简单，优化速度快

·缺点：不保证得到最优的执行计划

2.3 CBO

使用一个模型估算执行计划的代价，选择代价最小的执行计划 算子代价：CPU,内存，磁盘I/O，网络I/O等代价

2.3.1 CBO - 统计信息

原始表统计信息

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

推导统计信息

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

2.3.1 CBO - 统计信息的收集方式

在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息

手动执行explain analyze statement，触发数据库收集或者更新统计信息

动态采样

SELECT count（*） FROM table_name

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

2.3.2 CBO 效果 -TPC-DS

9.png 大概一半的查询都没显示出性能变化，这是因为RBO能为这些查询找到最优执行计划

16个查询在CBO下有更好的执行性能

10.png

2.3 CBO小结

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

02.小结

1.主流RBO 实现一般都有几百条基于经验归纳得到的优化规则

2.RBO实现简单，优化速度快

3.RBO不保证得到最优的执行计划

4.CBO使用代价模型和统计信息估算执行计划的代价

5.CBO使用贪心或者动态规划算法寻找最优执行计划

6.大数据场景下CBO对查询性能非常重要

社区开源实践

03概览

11.png

Apache Calcite

13.png

One size fits all：统一的SQL查询引擎

模块化，插件化，稳定可靠

支持异构数据模型

关系型
半结构化
流式
地理空间数据

内置RBO和CBO

SQL相关的前沿趋势

·智能化：AI4DB,DB4AI 自配置

·存储计算分离

·HSAP,HTAP,HTSAP

·Cloud Native，Serverless

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

其他问题：

Top-down 和 Bottom-up 的优化方式各有什么优缺点

TOP-DOWN VS. BOTTOM-UP

自上而下的优化：从你想要的最终结果开始，然后在树上寻找最适合你的目标。

自下而上的优化：从一无所有开始，然后制定计划以达到您想要的最终结果。

直方图的作用

1、显示质量波动的状态；

2、较直观地传递有关过程质量状况的信息；

3、当人们研究了质量数据波动状况之后，就能掌握过程的状况，从而确定在什么地方集中力量进行质量改进工作。

avatar
文章
阅读
粉丝