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

Matrix8295
149 阅读6分钟

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

0 引言

结构.png HSAP实时引擎：
大数据体系:个人觉得后续需要学习深入的（Spark、Flink、Hadoop、HBase、Presto） 大数据体系.png

1 大数据体系和SQL

目的：用SQL处理所有的大数据。

1.1 SQL处理流程

SQL处理流程.png

  • Parser
    过程：将String转化成AST(abstract syntax tree)，转化过程需要进行词法分析（拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token）和语法分析（将token组成AST node，最终得到一个AST）。
    实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），JavaCC（Flink），Antlr（Presto，Spark）。 AST.png
  • Analyzer
    过程：将AST转化为Logical Plan(逻辑的描述SQL对应的分步骤计算操作（算子 operator）)。
    工作：检查并绑定Database，Table，Column等源信息、SQL的合法性检查，比如min/max/avg的输入是数值。
    逻辑描述.png
  • Optimizer
    查询优化
  • Executor
    过程：查询优化组件生成Plan Fragment(执行计划子树，目标为了最小化网络数据传输。利用上数据的物理分布（数据亲和性），增加Shuffle算子)。
    工作：单机并行（cache，pipeline，SIMD）、多机并行（一个fragment对应多个实例）。 执行计划子树.png

2 常见的查询优化器

2.1 查询优化器分类

Top-down Optimizer（自顶向下优化器）：从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划。例子: Volcano/Cascade，SQLServer

Bottom-up Optimizer（自下而上优化器）：从零开始，由下往上遍历计划树，找到完整的执行计划。例子: System R,PostgreSQL,IBM DB2

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

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

2.2 RBO(Rule-based Optimizer)

关系代数：
运算符：Select(σ{\sigma}) ,Project (π{\pi}) ,Join (⋈{\Join}) ,Rename (p) , Union (∪{\cup}）等。
等价变换：结合律，交换律，传递性。Select:σp1⋀P2⋀...PN(R)=σp1(σp2(...σPN))){\sigma_{p1 \bigwedge P2 \bigwedge ... PN}(R)=\sigma_{p1}(\sigma_{p2}(...\sigma_{PN})))}。Join:R⋈S=S⋈R,(R⋈S)⋈T=R⋈(S⋈T){R\Join S=S\Join R,(R\Join S)\Join T = R\Join (S\Join T)} 关系代数.png 优化原则：
I/O、Network、CPU&Memory

SELECT pv.siteld, user.name
FROM pv JOIN user 
ON pv.siteld = user.siteld AND pv.userld = user.id
WHERE user.siteld > 123;
  • 列裁剪
    列裁剪.png
  • 谓词下推
    谓词下推.png
  • 传递闭包
    传递闭包.png
  • Runtime Filter
    RuntimeFilter.png 总结：
    主流RBO实现一般都有几百条基于经验归纳得到的优化规则。
    优点:实现简单，优化速度快。
    缺点:不保证得到最优的执行计划。
    1.单表扫描:索引扫描(随机I/O)vs.全表扫描(顺序I/O)。如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
    2.Join 的实现: Hash Join vs. SortMerge Join
    3.两表Hash Join:用小表构建哈希表——如何识别小表?
    4.多表Join:哪种连接顺序是最优的?是否要对每种组合都探索?N个表连接，仅仅是 left-deep tree就有差不多N!种连接顺序；e.g.N = 10->总共3, 628,800个连接顺序

2.3 CBO(Cost-based Optimizer)

使用一个模型估算执行计划的代价，选择代价最小的执行计划。执行计划的代价等于所有算子的执行代价之和、通过RBO得到(所有)可能的等价执行计划。
算子代价:CPU，内存，磁盘I/O，网络I/O等代价。
和算子输入数据的统计信息有关:输入、输出结果的行数，每行大小...
1.叶子算子Scan:通过统计原始表数据得到
2.中间算子:根据一定的推导规则，从下层算子的统计信息推导得到。
和具体的算子类型，以及算子的物理实现有关。
例子: Spark Join 算子代价= weight * row_count + (1.0 - weight)* size
CBO.png

  • 统计信息（概念、收集方式、推导规则、问题）

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

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

CREATE TABLE REGION(
R_REGIONKEY INT NOT NULL,
RNAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152)
) DUPLICATE KEY(R_REGIONKEY)
DISTRIBUTED BY HASH(R_REGIONKEY) BUCKETS 1
PROPERTIES("stats columns" = "R_NAME");

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

ANALYZE TABLE table name COMPUTE STATISTICS FOR COLUMNS column-name1, column-name2,....

3.动态采样

SELECT count(*) FROM table name

假设列和列之间是独立的，列的值是均匀分布
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) 推导规则.png

统计信息问题.png

  • 执行计划枚举

1.单表扫描:索引扫描（随机I/O) vs.全表扫描（顺序I/O)
如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
2.Join 的实现: Hash Join vs. SortMerge Join
3.两表Hash Join:用小表构建哈希表——如何识别小表
4.多表Join: 哪种连接顺序是最优的?
是否要对每种组合都探索?
N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序
e.g.N =10-→总共3,628,800个连接顺序
5.等等

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

动态规划举例：T⋈S⋈R{T\Join S \Join R}

SELECT *FROMR,S,T
WHERE R.a= s.a
AND S.b = T.b

动态规划.png

  • TPC-DS

3 社区开源实践

社区开源.png Apache Calcite

  • 概述
    1.One size fits all:统一的SQL查询引擎
    2.模块化，插件化，稳定可靠
    3.支持异构数据模型
    关系型
    半结构化
    流式
    地理空间数据
    4.内置RBO和CBO
    ApacheCalcite.png

  • Calcite RBO
    HepPlanner：
    1.优化规则(Rule)
    Pattern:匹配表达式子树
    等价变换:得到新的表达式
    2.内置有100＋优化规则
    3.四种匹配规则
    ARBITRARY/DEPTH_FIRST:深度优先
    TOP DOWN:拓扑顺序
    BOTTOM_UP: 与TOP_DOWN相反
    4.遍历所有的rule，直到没有rule可以被触发
    5.优化速度快，实现简单，但是不保证最优 CalciteRBO.png

  • Calcite CBO
    VolcanoPlanner：
    1.基于Volcano/Cascade框架成本最优假设
    2.Memo:存储候选执行计划
    Group:等价计划集合
    3.Top-down动态规划搜索 CalciteCBO.png

应用Rule搜索候选计划：
1.Memo：
本质:AND/OR graph
共享子树减少内存开销
Memo.png 2.Group winner:目前的最优计划
GroupWinner.png 3.剪枝(Branch-and-bound pruning):减少搜索空间
剪枝.png 4.Top-down遍历:选择winner构建最优执行计划
Top-Down遍历.png

4 前沿趋势

前沿趋势.png

  • AI4DB：
    自配置（智能调参、负载预测/调度）、自诊断和自愈合（错误恢复和迁移）、自优化（统计信息估计、代价估计、学习型优化器、索引/视图推荐）
  • DB4AI：
    内嵌人工智能算法(MLSQL,SQLFlow)
    内嵌机器学习框架(SparkML,Alink,dl-on-flink )

大数据创业如火如荼，SQL查询优化器仍然是必不可少的一个重要组件
引擎架构的进化、云原生、湖仓一体等对SQL查询优化器有新的要求和挑战
Al加持，学习型查询优化器在不断进化

5 参考链接

【大数据专场 学习资料一】第四届字节跳动青训营

avatar
文章
阅读
粉丝