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

JESSY
217 阅读9分钟

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

01 大数据体系和SQL

1.1 大数据体系中的SQL(为什么讲SQL优化器)


图的介绍

1.2 SQL处理流程

Parser

  • String -> AST(abstractsyntaxtree)

    • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token

    • 语法分析：将token组成ASTnode,最终得到 一个AST

  • 实现：递归下降(CIickHouse)，FIex和Bison(PostgreSQL)，JavaCC(FIink)，AntIr(Presto,Spark)

AnaIyzer和LogicaIPIan

  • AnaIyzer

    • 检查并绑定Database,Table,Column等元信息

    • SQL的合法性检查，比如min/max/avg的输入是数值

    • AST -> LogicaIPIan

  • LogicaIPIan

    • 逻辑地描述SQL对应的分步骤计算操作

    • 计算操作：算子(operator)

    SELECT country.name, SUM(weblog.bytes) as total】
FROM country

INNER JOIN geoip ON country.id = geoip.country_id

INNER JOIN weblog ON geoip.host = weblog.host

WHERE weblog.reply = "200" and weblog.host is not null

GROUP BY country.name

ORDER BY total

LIMIT 10

查询优化

  • SQL是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做

  • 目标：找到一个正确且执行代价最小的物理执行计划

  • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的

  • 一般SQL越复杂，Join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上倍

PhysicaIpIan和Executor

  • PlanFragment:执行计划子树

    • 目标：最小化网络数据传输

    • 利用上数据的物理分布（数据亲和性）

    • 增加ShuffIe算子

  • Executor

    • 单机并行：cache,pipeline,SIMD

    • 多机并行： 一个fragment对应多个实例

1.3 小结

  • OneSQL rules big data all

  • SQL需要依次经过Parser,Analyzer,Optimizer和Executor的处理

  • 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要

  • 查询优化器需要感知数据分布，充分利用数据的亲和性

  • 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段

02 常见的查询优化器

2.1 查询优化器分类

  • Top-down Optimizer

    • 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划

    • 例子：VoIcano/Cascade,SQLServer

  • Bottom-up Optimizer

    • 从零开始，由下往上遍历计划树，找到完整的执行计划

    • 例子：SystemR, PostgreSQL, IBMDB2

  • Rule-based Optimizer（RBO）

    • 根据关系代数等价语义，重写查询

    • 基于启发式规则

    • 会访问表的元信息(catalog),不会涉及具体的表数据(data)

  • Cost-based Optimizer（CBO）

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

2.2 RBO(RuIe-based Optimizer)

RBO-关系代数

RBO-优化原则

RBO-列裁剪

RBO-谓词下推

RBO-传递闭包

RBO-Runtime FiIter

RBO 小结

  • 主流RBO实现一般几百条基于经验归纳得到的优化规则
  • 优点：实现简单，优化速度快
  • 缺点：不保证得到最优的执行计划
    • 单表扫描：索引扫描（随机I/O）vs. 全表扫描（顺序I/O）
      • 如果查询的分布非常不均衡，索引扫描可能不如全表扫描
    • Join的实现：Hash Join vs. SortMerge Join
    • 两表Hash Join：用小表构建哈希表 - 如何识别小表？
    • 多表Join：
      • 哪种连接顺序是最优的？
      • 是否要对每种组合都探索？
        • N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序
        • e.g.N=10 -> 总共3, 628, 800个连接顺序

2.3 CBO(Cost-basedOptimizer)

CBO-概念

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

    • 执行计划的代价等于所有算子的执行代价之和

    • 通过RBO得到（所有）可能的等价执行计划

  • 算子代价：CPU，内存，磁盘/0，网络I/O代价

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

      • 叶子算子Scan：通过统计原始表数据得到

      • 中间算子：根据 一定的推导规则，从下层算子的统计信息推导得到

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

    • 例子：Spark Join算子代价=weight * rows_count+（1.0 - weight) * size

image.png

CBO-统计信息

  • 原始表统计信息

    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等

    • 列级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等

  • 推导统计信息

    • 选择率（selectivity）：对于某 一个过滤条件，查询会从表中返回多大比例的

    • 基数（cardinality）：在查询计划中常指算子需要处理的行数

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 ("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-统计信息推导规则

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

  • FiIterSeIectivity
    • AND条件：fs(a AND b)=fs(a) * fs(b)
    • OR条件：fs(a OR b)=fs(a)+fs(b) - (fs(a)*fs(b))
    • NOT条件：fs(NOTa)=1，0-fs(a)
    • 等于条件（×=literal)
      • literal < min && literal > max: 0
      • 1 / NDV
    • 小于条件(x<literal)
      • literal < min:0
      • literal > max:1
      • (literal - min) / (max - min)

CBO-统计信息的问题

  • 假设列和列之间是独立的，列的值是均匀分布： 这个假设经常与现实不符！

  • 考虑一个汽车数据库automobiles，有10个制造商，100个车型，filter为“制造商=‘比亚迪’且车型=‘汉'”，根据独立性和均匀分布假设，则 selectivity =1/10×1/100=0.001，但是‘比亚迪’和‘汉’是相关联的，实际selectivity=1/100=0.01： 用户指定或者数据库，自动识别相关联的列

  • 考虑中国人口数据库，性别，年龄，数量都不是均匀分布：直方图

CBO-执行计划枚举

  • 单表扫描：索引扫描（随机 1/O）vs 全表扫描（顺序 I/O）

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

  • Join的实现：Hash Join vs. SortMerge Join

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

  • 多表Join:

    • 哪种连接顺序是最优的？

    • 是否要对每种组合都探索？

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

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

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

CBO执计划枚举-动态规划

CBO效果-TPC-DS Q25

CBO效果-TPC-DS

CBO小结

  • CBO使用代价模型和统计信息估算执行计划的代价

  • CBO使用贪心或者动态规划算法寻找最优执行计划

  • 在大场景下CBO对查询性能非常重要

2.4 小结

  • 主流RBO实现 一般都有几百条基于经验归纳得到的优化规则

  • RBO实现简单，优化速度快

  • RBO不保证得到最优的执行计划

  • CBO使用代价模型和统计信息估算执行计划的代价

  • CBO使用贪心或者动态规划算法寻找最优执行计划

  • 大场景下CBO对查询性能非常重要

03 社区开源实践

3.1 社区开源实践 - 概览

image.png

3.2 ApacheCalcite

ApacheCalcite 概览

  • One size fits all：统一的SQL查询引擎

  • 模块化，插件化，稳定可靠

  • 支持异构数据模型

    • 关系型

    • 半结构化

    • 流式

    • 地理空间数据

  • 内置RBO和CBO

社区开源实践 - CaIcite RBO

  • HepPIanner

    • 优化规则(Rule)

      • Pattern：匹配表达式子树

      • 等价变换：得到新的表达式

  • 内置有100+优化规则

  • 四种匹配规则

    • ARBITRARY/DEPTH_FIRST:深度优先

    • TOP_DOWN：拓扑顺序

    • BOTTOM_UP:与TOP_DOWN相反

  • 遍历所有的rule,直到没有rule可以被触发

  • 优化速度快，实现简单，但是不保证最优

![](vx_images/86772815247689.png =430x)

社区开源实践-CaIcite CBO

  • VolcanoPlanner

    • 基于Volcano/Cascade框架

    • 成本最优假设

    • Memo:存储候选执行计划

      • Group: 等价计划集合

    • Top-down动态规划搜索

  • VolcanoPlanner

    • 应用搜索候选计划

    • Memo

      • 本质：AND/OR graph

      • 共享子树减少内存开销

  • VolcanoPlanner

    • Group winner:目前的最优计划

  • VolcanoPIanner

    • 剪枝(Branch-and-bound pruning)：减少搜索空间

    可行的 Aggregate：总的cost=500，自己的cost=150，孩子节点cost上限=350

  • VolcanoPlanner

    • Top-down遍历：选择winner构建最优执行计划

3.3 小结

  • 主流的查询优化器都包含RBO和CBO

  • Apache Calcite是大数据领域很流行的查询优化器

  • Apache Calcite RBO定义了许多优化规则，使用pattern匹配子树，执行等价变换

  • Apache Calcite CBO基于Volcano/Cascade框架

  • Volcano/Cascade的精髓：Memo、动态规划、剪枝

04 前沿优势

2021年初，Starburst Data公司为其Trino系统（以前的PrestoSQL）筹集了1亿美元。

2021年6月，Apache Kafka商业化公司Confluent登陆NSDAQ,首日涨25%,市值超110亿美元。

2021年8月，Apache lceberg 的创建者Ryan Blue 正式成立围绕Iceberg的商业公司Tabular。

2021年8月，Spark背后的公司Databricks宣布获得16亿美元融资，最新估值飙升至380亿美元。

2021年9月，ClickHouse的创建者Alexey正式成立公司：ClickHouse.Inc 获得了5000万美元融资。

2021年10月，基于Apache Pulsar的商业化公司StreamNative宣布获得2300万美元A轮融资。

4.1 概览

  • 对SQL优化器有新的要求！

    • 引擎架构的进化：

      • 存储计算分离

      • 一体化（HTAP,HSAP,HTSAP）

    • Cloud：

      • 云原生

      • serverless

    • 湖仓一体：

      • Query Federation

    • DATA + AI

4.2 前沿趋势-DATA+AI

  • Al4DB

    • 自配置

      • 智能调参（OtterTune, QTune）

      • 负载预测/调度

    • 自诊断和自愈合：错误恢复和迁移

    • 自优化：

      • 统计信息估计（Learn cardinalities）

      • 代价估计

      • 学习型优化器（IBM DB2 LEO）

      • 索引/视图推荐

  • DB4Al

    • 内嵌人工智能算法(MLSQL, SQLFlow)

    • 内嵌机器学习框架(SparkML, Alink, dl-on-flink）

4.3 小结

  • 大数据创如火如荼，SQL查询优化器仍然是必不可少的一个重要组件

  • 引擎架构的进化、云原生、湖仓等对SQL查询优化器有新的要求和挑战

  • AI加持，学习型查询优化器在不断进化

avatar
文章
阅读
粉丝