大数据体系和SQL查询优化器浅析 | 青训营笔记

冬天真的冷
134 阅读5分钟

image.png

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

一、大数据体系和SQL

1、大数据体系

image.png

  • SQL一般应用于批式处理、实时处理和交互式分析，属于分析引擎环节。

2、SQL的处理流程

image.png

Parser

  • 把文本变成抽象语法树结构（AST）
  • 语法分析：拆分字符串，得到关键词，数值常量，字符串常量，运算符号等token
  • 词法分析：将token组成AST node，最终得到AST
  • 实现：递归下降(Click House)，Flex和Bison(PostgreSQL)，JavaCC(Flink),Antlr(Presto,Spark)

image.png

Analyzer

  • 检查并绑定Database,Table,Column等元信息
  • SQL的合法性检查，比如min/max/avg都是输入的数值，不能是字符串等
  • AST->Logical Plan
  • Logical Plan:逻辑地描述SQL对应的分步骤计算操作
  • 计算操作：算子（operator）

image.png

Optimizer

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

Executor

Plan Fragment:执行计划子树：

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

Executor两种运行模式：

  • 单机并行：cache,pipeline,SIMD
  • 多级并行：一个fragment对应多个实例

二、查询优化器

1、常见的查询优化器

按照遍历树的顺序划分： Top-down Optimizer：

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

Bottom-up Optimizer：

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

根据优化方法划分： Rule-based Optimizer(RBO)

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

Cost-base Optimizer(CBO)

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

2、RBO

关系代数

image.png

image.png

优化原则

  • I/O：读更少数据且更快
  • Network：传输的数据更少且更快
  • CPU & Memory：处理数据更少且更快

优化方式

优化方式包括很多，像列裁剪、谓词下推、传递闭包、Runtime-Filter等等。

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

3、CBO

概念

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

  • 执行计划的代价等于所有算子的执行代码之和
  • 通过RBO得到可能的等价执行计划

算子代价：CPU，内存等代价

  • 和算子输入数据的统计信息有关
  • 叶子算子Scan：通过统计原始表数据得到
  • 中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
  • 和具体的算子类型，以及算子的物理实现代码
  • 例子：spark join 算子代价 = weight*row_count + (1.0 - weight)*size

image.png

统计信息

原始表统计信息

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

推导统计信息

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

CBO-统计信息的收集方式

  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息
CREATE TABLE REGION(
R_REGIONKEY INT NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152)
) DUPLICATE KEYIR REGIONKEY)
DISTRIBUTED BY HASH(R_REGIONKEY) BUCKETS 1 
PROPERTIES ("stats_columns" = "R_NAME");
复制代码
  • 手动执行 explain analyze statement，触发数据库收集或者更新统计信息
ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name1, column-name2...
复制代码
  • 动态采样
SELECT count(*) FROM table_name
复制代码

CBO-统计信息推导规则

前提假设列和列之间独立，列的值是均匀分布

image.png

  • 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-执行计划枚举

单表扫描:索引扫描(随机 1/O)vs. 全表扫描(顺序 |/O)

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

Join 的实现:Hash Join vs. SortMerge Join
两表 Hash Join:用小表构建哈希表-如何识别小表?
多表 Join:

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

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

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

avatar
文章
阅读
粉丝