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

用户6967181523519
119 阅读4分钟

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

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

一、 SQL处理流程

1.SQL的处理流程–Parser

（1）String -> AST ( abstract syntax tree )

  • 词法分析:拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
  • 语法分析:将token 组成AST node，最终得到一个AST

（2）实现:递归下降(ClickHouse)，Flex和Bison (PostgreSQL)， JavaCC(Flink)，Antlr (Presto, Spark)

2.SQL的处理流程–Analyzer和Logical Plan

（1）Analyzer

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

（2）Logical Plan

  • 逻辑地描述SQL对应的分步繇计算操作
  • 计算操作:算子(operator)

3.SQL的处理流程–查询优化

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

4.SQL的处理流程- Physical Plan和Executor

（1）Plan Fragment:执行计划子树

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

（2）Executor

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

二、查询优化器

1.查询优化器

  1. 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
  2. 查询优化器需要威知数据分布，充分利用数据的亲和性
  3. 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段

2.查询优化器分类

（1）Top-down Optimizer

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

（2）Bottom-up Optimizer

  • 从零开始。由下往上遍历计划树，找到完整的执行计划
  • 例子: System R，PostgresQL,IBM DB2

（3）Rule-based Optimizer (RBO)

  • 根据关系代数等价语义，重写查询
  • 基于启发式规则
  • 会访问表的元信息(catalog)。不会涉及具体的表数据(data)

（4）Cost-based Optimizer (CBO)

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

3.RBO详解

（1）RBO-优化原则

  1. Read data less and faster (l/O)
  2. Transfer data less and faster (Network)
  3. Process data less and faster (CPU &t Memory)

（2）优化规则

  1. 主流RBO实现一般都有几百条基于经验归纳得到的优化规则
  2. 例子：列裁剪、谓词下推、传递闭包、Runtime Filter

（3）优点:

实现简单,优化速度快

（4）缺点

不保证得到最优的执行计划。CBO可以通过执行计划枚举解决这些RBO出现的问题，
通常使用贪心算法或者动态规划选出最优的执行计划

  1. 单表扫描:索引扫描(随机1/o) vs.全表扫描(顺序l/O)： 如果查询的数据分布非常不均衡,索引扫描可能不如全表扫描

  2. Join 的实现:Hash Join vs.SortMerge Join

  3. 两表Hash Join:用小表构建哈希表——如何识别小表?

  4. 多表Join:

（1）哪种连接顺序是最优的?

（2）是否要对每种组合都探索?

  • N个表连接，仅仅是 left-deep tree就有差不多N!种连接顺斥
  • e.g.N=10->总共3,628,800个连接顺序

4.CBO

（1）CBO概念

CBO使用代价模型和统计信息估算执行计划的代价
CBO使用贪心或者动态规划算法寻找最优执行计划
在大数据场景下CBO对查询性能非常重要
  1. 使用一个模型估算执行计划的代价，选择代价最小的执行计划
  • 执行计划的代价等于所有算子的执行代价之和
  • 通过RBO得到(所有)可能的等价执行计划
  1. 算子代价:CPU,，内存，磁盘I/O，网络VO等代价

（1）和算子输入数据的统计信息有关:输入、输出结果的行数，每行大小..

  • 叶子算子Scan :通过统计原始表数据得到
  • 中间算子:根据—定的推导规则，从下层算子的统计信息推导得到

（2）和具体的算子类型，以及算子的物理实现有关

（3）例子:Spark Join 算子代价= weight* row_count + (1.0 - weight)* size

  1. 统计信息＋推导规则 -> 计算算子代价 -> 计算执行计划代价 -> 执行计划枚举

（2）统计信息

  1. 原始表统计信息
  • 表或者分区级别:行数、行平均大小、表在磁盘中占用了多少字节等
  • 列级别: min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等
  1. 推导统计信息
  • 选择率( selectivity ):对于某一个过滤条件，查询会从表中返回多大比例的数据
  • 基数 ( cardinality ):在查询计划中常指算子需要处理的行数
avatar
文章
阅读
粉丝