SQL -Optimizer｜ 青训营笔记

YokeMan
87 阅读5分钟

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

SQL的处理流程

主要是以下四个组件

image.png

Parser

作用：String->AST(abstract syntax tree)

  1. 把文本变成抽象语法树结构（AST）
  2. 涉及词法分析阶段（拆分字符串，提取关键字，字符串，数值等）和语法分析阶段（把词条按照定义的语法规则组装成抽象语法树结构）
  3. 实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），JavaCC（Flink），Antlr（Presto, Spark）

image.png

Analyzer

  1. 检查并绑定数据库、表、列名等信息是否正确
  2. SQL的合法性检查，例如min/max/avg是不是数值
  3. AST->Logical Plan（抽象语法树转换成逻辑执行计划）

image.png

Logical Plan

  1. 逻辑的描述SQL是怎么查询计算的
  2. 是一个树，每个节点都是一个算子（不涉及具体算法）

image.png

查询优化Optimizer

  1. 为什么要：SQL只是一种声明式的语言，因为用户用SQL只描述需要做什么，没告诉DB怎么做
  2. 目标：找到正确且执行代价最小的物理执行计划
  3. 很多问题都是NP
  4. SQL越复杂，join表越多，数据量越大，优化意义就越大

Physical Plan和Executor

  • Plan Fragment：执行计划子树
    1. 目标：最小化网络数据传输
    2. 利用上数据的物理分布（数据亲和性）
    3. 增加Shuffle算子
  • Executor
    1. 单机并行：Cache，Pipeline，SIMD
    2. 多机并行：一个fragment对应多个实例

常见的查询优化器

查询优化器分类（根据优化方法分类）

RBO

  1. 根据关系代数等价语义，重写查询
  2. 基于启发式规则
  3. 会访问表的元信息（catalog），不会涉及具体的表数据（data）
优化原则
  • Read data less and faster (I/O)
  • Transfer data less and faster (Network)
  • Process data less and faster (CPU & Memory)
具体方法

  • 列裁剪

    • 尽可能减少数据
    • 从上往下筛选出需要的列，去掉不用的列

  • 谓词下推（约束条件 表达式）

    • 过滤数据在前面过滤和在后面过滤结果不受影响
    • 所以想谓词能不能往前推，尽早的过滤数据

  • 传递闭包

    • 根据一些表达式的等价关系，加上过滤条件，可以推出一个新的过滤关系

  • Runtime filter

    • 对一个join如果能在查询端提早过滤不必要数据，可减少开销
    • min-max的缺点：范围必须很紧密
    • in-list：只需要扫描in-list里的数据。缺点：集合个数很多时，in-list也很大
    • bloom filter：特性：大小不随集合大小改变，固定大小，给一个数可以判断在不在

  • 小结

    • 主流RBO实现一般都有几百条基于经验归纳得到的优化规则
    • 优点:实现简单，优化速度快
    • 缺点:不保证得到最优的执行计划

image.png

CBO

  • 使用一个模型估算执行计划的代价，选择代价最小的执行计划
    • 执行计划的代价等于所有算子的执行代价之和
    • 通过RBO得到(所有)可能的等价执行计划
  • 算子代价:CPU，内存，磁盘IO，网络I/O等代价

统计信息+推导规则→计算算子代价→计算执行计划代价→执行计划枚举

CBO-统计信息

原始表统计信息

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

推导统计信息

  • 选择率( selecthwty) ：对于某一个过滤条件查询会从表中返回多大比例的数据
  • 基数( careinality ) ：在查询计划中常指算子需要处理的行数
CBO-统计信息的收集方式

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

    CREATE TABLE REGION( R_ REGIONKEY INT NOT NULL, R NAME CHAR(25) NOT NULL, R_ COMMENT VARCHAR(152) ) DUPLICATE KEY(R_ REGIONKEY) DISTRIBUTED BY HASH(R_ REGIONKEY) BUCKETS 1 PROPERTIES (" sotumnselelR w");

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

ANALYZE TABLE table_name COMPUTE STATISICS FOR COLUMNS column-name1,column-name2....

  • 动态采样

SELECT count(*) FROM table_name

CBO-统计信息推导规则

  • Filter Selectivity

    • 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
      • 1/NDV

  • 小于条件(x < literal )

    • literal<min:0
    • literal>max:1
    • (literal-min)/(max-min)
CBO-执行计划枚举

  • 单表扫描:索引扫描(随机I/O) vs 全表扫描(顺序IO)

    • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描

  • Join的实现: Hash Join Vs. SortMerge Join

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

  • 多表Join :

    • 哪种连接顺序是最优的?
    • 是否要对每种组合都探索?

  • N个表连接,仅仅是left-deep tree就有差不多N!种连接顺序

    • e.g. N= 10->总共3, 628, 800个连接顺序

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

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

总结

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