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

郑丹丹_lychee
220 阅读6分钟

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

这节课程主要从四个方面展开，分别为：

1. 大数据体系和 SQL；

2. 常见的查询优化器；

3. 查询优化器的社区开源实践；

4. SQL 相关的前沿趋势。

【思维导图概览】 image.png

一、SQL的处理流程

1.1  Parser

string->AST（abstract  syntax tree，抽象语法树），通过词法分析将字符串拆分，再通过语法分析得到AST。

*词法分析：拆分字符串、得到关键词、数值常量、字符串常量、运算符号token

语法分析：将token组成一个AST node，得到一个AST

Parser的实现可以通过：递归下降（click house），flex（词法分析）和bison（语法分析）（postgreSQL）、javaCC（Flink）、Antlr（presto，spark）。

1.2 Analyser和Logical Plan

Analyzer

检查并绑定Database，table，column，并进行SQL的合法性检查，如min/max/avg的输入时数值。AST->Logical Plan

Logical Plan

逻辑地描述SQL对应的分步骤操作，通过算子（operator）进行计算操作。

1.3 查询优化

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

目标是找到一个正确且执行代价最小的物理执行计划；

查询优化器是数据库的大脑，最复杂的模块；

一般sql越复杂，join 的表越多，数据量就越大，查询优化的意义就越大。

Physical Plan和Executor

Plan Fragment ：执行计划子树

目标：最小化网络数据传输，利用上数据的物理分布（数据亲和性）（寻找最短路径，取代远程路径），增加shuffle算子。

Executor

单机并行：cache，pipeline，SIMD

多机并行：一个fragment对应多个实例

小结

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

二、常见查询优化器

2.1 常见的两种分类方法

第一种分类

Top-down Optimizer

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

Bottom-up Optimizer

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

第二种分类

Rule-Based Optimizer（RBO）
  • 基于关系代数等价语义，重写查询
  • 基于启发式规则（经验）
  • 会访问表的元信息（catalog），不会涉及具体的表数据（data）
Cost-based Optimizer（CBO)
  • 使用一个模型估计执行计划的代价，选择代价最小的执行计划

2.2 RBO

  • 关系代数
  • 等价变换：满足结合律、交换律、传递性

优化原则

  • Read data less and faster(I/O) 输入数据少，读得块
  • Transfer data less and faster(Network) 简化网络，数据传输快，读得快
  • Process data less and faster(CPU&Memory)优化内存，载入数据块，读得快

优化方法

  • 列裁剪（只读需要的列）
  • 谓词下推（如filter下推，提前过滤）
  • 传递闭包（根据表达式的等价关系，推导新的等价关系，再进行下推，如将现有的filter下推，创建一个新的filter下推）
  • runtime filter（设置特定条件的filter，将该条件用于限制、筛选另一个表里的数据，减少工作量）min-max\in-list\bloom filter

2.3 CBO

概念：使用模型估算执行计划的代价，选择代价最小的执行计划。执行计划的代价等于所有算子执行代价之和，通过RBO得到所有可能的等价执行计划。

算子代价：CPU、内存、磁盘I/O，网络I/O

CBO求解过程：统计信息+推导规则->计算算子代价->计算执行计划代价->执行计划枚举

统计信息与推导规则

原始表统计信息

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

推导统计信息

  • 选择率
  • 基数

统计信息收集方式

  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息（会影响实时导入的速率）;
  • 手动执行explain analyze statement，触发数据库收集或更新信息。（信息可能不是最新）analyze table tb_name compute statistics for columns co_name1,co_name2;
  • 动态采样 select count(*)from table.

统计信息推导规则

  • 假设：列和列之间独立，列值均匀分布(在现实中，与实际情况不符，大部分情况下列之间有关联关系）
  • Fliter Selectivity ：AND/OR/NOT/=/<
  • cardinality(FILTER)=cardinality(A)*selectivity(FILTER)
  • 执行计划枚举（通常使用贪心算法或动态规划选出最优的执行计划）

小结

RBO

  • 主流RBO实现一般都有几百条基于经验归纳得到的优化规则
  • 优点：实现简单
  • 缺点：不保证得到最优的执行计划
  • 单表扫描：索引扫描（随机I/O）和全表扫描（顺序I/O）的选择，如果查询的数据分布不均衡，索引扫描效率没有全表扫描效率高
  • 分布式情况下的join实现，没法选择hash join和排序join
  • 两表hash join，做不到识别小表

CBO

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

三、社区开源实践

Apache Calcite

  • 查询优化器，解析SQL执行优化，可以作为通用的SQL执行优化
  • One size fits all:统一的SQL查询引擎
  • 模块化、插件化、稳定可靠
  • 支持异构数据模型（关系型、半结构化、流式、地理空间数据
  • 内置RBO和CBO

Calcite RBO

HepPlanner

优化规则

  • Pattern：匹配表达式子树
  • 等价变换：得到新的表达式

匹配规则

  • ARBITRARY/DEPTH_FIRST：深度优先
  • TOP_DOWN:拓扑顺序
  • BOTTOM_UP:与top_down 相反
  • 遍历所有的rule
  • 优化速度快，实现简单，但是不保证最优

Calcite CBO

VolcanoPlanner

  • 基于Volcano/Cascade框架
  • 成本最优假设
  • Memo：存储候选执行计划，本质是AND/OR graph，共享子树减少内存开销
  • Group：等价计划合集
  • Top-down动态规划搜索
  • group winner：目前最优计划
  • 剪枝（branch-and-bound pruning）：减少搜索空间
  • top-down遍历：选择winner构建最优执行计划

小结

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

四、发展趋势

  • 引擎架构的进化
  • 云端
  • 湖仓一体
  • DATA+AI
avatar
文章
阅读
粉丝