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

纯纯子
136 阅读8分钟

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

第一节 ： SQL查询优化器浅析

概述

本节课主要4个方面

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

一、大数据体系和SQL

  1. 了解编译原理相关的基础知识
  • 词法分析
  • 语法分析
  • 抽象语法树
  1. 了解SQL的执行计划
  • 逻辑计划
  • 物理计划
  • 分布式执行计划
  • Left-deep tree
  1. 了解SQL执行的基本流程
  • 任务调度 ：DAG
  1. 了解分布式系统中的shuffle的实现方式
  • Broadcast shuffle & Repartion shuffle
  • 参考 MapReduce 和 Spark 系统
  1. 了解SQL中group-by 和 join 的执行方式
  • Hash-based & Sort-based

1.1 SQL的处理流程


graph LR;
	sql处理流程--sql--> parser
	parser--AST-->Analyzer
	Analyzer--Logical Plan-->Optimizer
	Optimizer--Physical Plan-->Executor
1.1.1 组件介绍
  1. Parser
  • string -> AST(abstract syntax tree) 抽象语法树（输出经过的步骤）
    • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
    • 语法分析：将 token 组成 AST node，最终得到一个 AST

-实现：递归下降（clickhouse)、Flex 和 Bison （PostgreSQL）、Javacc（Flink）、Antlr（Presto，Spark）

  1. Analyzer和Logical Plan

  • Analyzer

    • 检查并绑定 Database , Table,Column等元信息
    • SQL 的合法性检查，比如 min/max/avg 的输入是数值
    • AST -> Logical Plan

  • Logical Plan

    • 逻辑地描述 SQL 对应的分步骤计算操作
    • 计算操作：算子（operator）
  1. Physical & Executor

  • Plan Fragment ：执行计划子树

    • 目标：最小化网络数据传输
    • 利用上数据的物理分布（数据亲和性）
    • 增加shuffle算子

  • executor

    • 单机并行：cache,pipeline,SIMD
    • 多机并行: 一个fragment对应多个实例
    • Executor 按照物理执行计划扫描和处理数据，充分利用机器资源（CPU 流水线，乱序执行，cache，SIMD）

  1. 逻辑计划数 逻辑计划树

    所谓逻辑计划树，可以理解为逻辑地描述一个 SQL 如何一步步地执行查询和计算，最终得到执行结果的一个分步骤地计划。树中每个节点是是一个算子，定义了对数据集合的计算操作（过滤，排序，聚合，连接），边代表了数据的流向，从孩子节点流向父节点。之所以称它为逻辑的，是因为算子定义的是逻辑的计算操作，没有指定实际的算法，比如对于逻辑的排序算子，逻辑计划树里没有指定使用快排还是堆排。

​

  1. 查询优化(point)

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

  1. 物理执行计划 物理执行计划

1.优化器的输出是一个分布式的物理执行计划。 2.分布式物理执行计划的目标是在单机 Plan 的基础上最小化数据移动和最大化本地 Scan，生>成 PlanFragment 树。 3.一个 PlanFragment 封装了在一台机器上对数据集的操作逻辑。每个 PlanFragment 可以在每个 executor 节点生成 1 个或多个执行实例，不同执行实例处理不同的数据集，通过并发来提升查询性能。 4.Plan 分布式化的方法是增加 shuffle 算子，执行计划树会以 shuffle 算子为边界拆分为PlanFragment。

二、查询优化器分类

常见的查询优化器

  • Top-down Optimizer
  • Bottom-up Optimizer

2.1 RBO-优化原则

  • read data less and faster(I/O)

  • transfer data less and faster (Network)

  • process data less and faster (COU & Memory)

  • Rule-based Optimizer，RBO

  • Rule:定义了如何将其匹配的节点替换（Substitute）为新形态，从而生成新的、等价的Operator 树（原地替换）

  • Pattern：定义了特定结构的 Operator 子树（结构）

  • 优化器搜索过程被抽象为不断匹配 Pattern 然后应用 Rule 转换，直到没有可以匹配的 rule

  • 局限性:

    • 无法解决多表连接的问题
    • 无法确定和选择最优的分布式join、Aggregate 执行方法

  • 交换律、结合律、传递性

  • RBO 优化规则

    • 列裁剪

    • 谓词下推

    • 传递闭包

    • Runtime Filter（min-max filter，in-list filter，bloom filter）

    • Join 消除

    • 谓词合并

2.2CBO 相关概念

==Cost-based Optimizer，CBO==


graph LR;
	原始表统计信息&推导规则 --> 算子代价计算规则
	算子代价计算规则 --> 执行计划代价
	执行计划代价 --> 枚举
    1. 使用一个模型估算执行计划的代价，选择代价最小的执行计划
    1. 分而治之，执行计划的代价等于所有算子的执行代价之和
    1. 通过 RBO 得到（所有）可能的等价执行计划（非原地替换）
    1. 算子代价包含 CPU，cache misses，memory，disk I/O（磁盘），network I/O 等代价
    • 和算子的统计信息有关，比如输入、输出结果的行数，每行大小等
    • 叶子算子 scan：通过统计原始表数据得到
      • 中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
      • 和具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）
    1. 使用动态规划枚举所有执行计划，选出执行代价最小的执行计划

    1. 基表统计信息

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

  • 推导统计信息

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

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

create table region(
	R_REGIONKEY INT NOT NULL,
	R_NAME CHAR(25) NOT NULL,
	R_COMMENT VARCHAR(152)
	)DUPLICATE KET(R_REGIONKEY)
	DISTRIBUTED BY HASH(R_REGIONKEY)BUCKETS 1
	PROPERTIES("stats_columns"="R_NAME");

手动执行 explain analyze ststement,触发数据库收集或者更新统计信息（==缺点：信息比较旧==）

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name1,column-name2……

动态采样

SELECT count(*) FROM table_name;

CBO 执行计划枚举

  • 单表扫描：索引扫描（随机I/O）& 全表扫描（顺序I/O）
    • 如果查询的数据分布非常不均匀，索引扫描可能不如全表扫描
  • join 的实现：Hash join & sortmerge join
  • 两表 hash join：用小表构建哈希表——如何识别小表？
  • 多表join

小结

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

三、查询优化器的社区开源实践

Apache Calcite 概览

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

3.1 Calcite RBO

  • Heplanner
    • 优化规则
      • pattern：匹配表达式子树
      • 等价变换：得到新的表达式
    • 内置有100+优化规则
    • 四种匹配规则
      • ARBIRARY/DEPTH_FIRST
      • TOP_COWN:拓扑顺序
      • BOTTOM_UP：与TOP_DOWN相反
    • 遍历所有的rule，直到没有rule可以触发
    • 优化速度快，实现简单，但是不保证最优

3.2 Calcite CBO

  • VolcanoPlanner

    • 基于Volcano/Cascade框架
    • 成本最优假设
    • Memo：存储候选执行计划
      • Group：等价计划集合
    • Top-dow动态规划搜索

  • Volcano/Cascade 框架

    • Memo
      • img
      • Cascades Optimizer 在搜索的过程中，其搜索的空间是一个关系代数算子树所组成的森林，而保存这个森林的数据结构就是 Memo。Memo 中两个最基本的概念就是 Expression Group（下文简称 Group） 以及 Group Expression（对应关系代数算子）。每个 Group 中保存的是逻辑等价的 Group Expression，而 Group Expression 的子节点是由 Group 组成。
    • Memo 本质是 AND/OR Graph，通过共享相同的子树减少内存开销，记录搜索过的子树的最优执行计划（winner）
      • img

  • Branch-and-Bound Pruning

    • 已搜索完成的物理计划的代价最小值成为 Cost Upper Bound。当新的搜索分支的代价高于它时，不需继续搜索。初始 Cost Upper Bound可由优化器根据启发式规则估算。

3.3 小结

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

四、SQL 相关的前沿趋势

4.1 前沿趋势--概览

  • 存储计算分离

  • 一体化：HSAP, HTAP, HTSAP

  • 云原生：Cloud Native, Serverless

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

  • 智能化

    • AI4DB
      • 自配置：智能调参（OtterTune，QTune）、负载预测、负载调度
      • 自诊断和自愈合：软硬件错误、错误恢复和迁移
      • 自优化：统计信息估计（ Learned cardinalities ）、代价估计、学习型优化器（IBM DB2 LEO），索引推荐，视图推荐
    • DB4AI
      • 内嵌人工智能算法（MLSQL，SQLFlow）
      • 内嵌机器学习框架（SparkML， Alink， dl-on-flink ）
avatar
文章
阅读
粉丝