1.1 SQL optimizer课前及课上笔记 | 青训营笔记

momo888
255 阅读4分钟

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

课前预习

（参考cmu 15-445 query-optimizer）

query-optimizer

第一个实现：IBM system R

sql处理流程：

image.png

上课内容

大数据体系-one sql rules big data all

image.png

思考：为什么sql query-optimizer要放在第一节课？

  • sql已成为大数据处理事实标准的接口

sql处理流程

(个人感觉先修课程有：数据库系统概论（尤其关系代数相关），形式语言与自动机，编译原理，概率论与数理统计）

Parser：String->AST

词法分析（Flex）、语法分析（Bison）、递归下降（clickHouse）

Analyzer：AST->Logical Plan

（和cmu提到的Binder应该是同一个）

  • 元信息
  • SQL合法性

Optimizer: Logical Plan->Physical Plan

Logical Plan

描述SQL对应的 分步骤 计算操作（算子）

left-deep tree：join放左边，scan放右边，顺序不能互换

Physical plan

将执行计划拆分为多个plan fragment

目标：最小化网络数据传输 注意事项：

  • 考虑数据亲和性；
  • 增加shuffle算子

executor :Physical Plan ->result

单机并行：cache、pipeline等 多机并行：拆分

query-optimizer作用：

  • sql是声明式语言，用户注重的是结果。找到一个 正确且执行代价最小的执行计划 很重要
  • 查询优化器是数据库的大脑。数据量越大，优化的意义就越大。

query-optimizer分类：

按遍历顺序：

由上往下遍历计划树

Volcano/Cascade,SQLServer

由上往下遍历计划树

System R ,PostgreSQL

按优化规则：

RBO(rule）

基于关系代数的等价变换 可优化点：

  • I/O
  • network
  • cpu memory

常用优化规则： 主流RBO一般有几百条基于经验的优化规则

  • 列裁剪：从上往下扫描，到树的底层的时候只扫描需要扫描的列

image.png

  • 谓词下推：

  • 传递闭包： 从应用层面理解就是：可以从条件a推出条件b。 比如可以从

image.png 推出 pv.siteId>123 在树形结构中表现为可以增加一个filter:pv.siteId>123

  • runtime filter 思想：

    • min-max: 限制：数据范围不应过大

    • in-list: 限制：集合个数多时，list会很大

    • bloom filter:（存储引擎相关） 固定大小

使用RBO面临的一些问题：遇到这些问题的时候不够灵活，可能非最优决策

  • 索引扫描（随机I/O）不适用于查询数据分布不均衡的情况（适用全表扫描(顺序I/O））
  • join类型选择：hash VS sortmerge
  • 用小表构建哈希表时——如何识别小表？ 选择错误的一边构建哈希表容易导致内存溢出
  • 多表join时如何确定最优连接顺序？是否应探索所有组合？
CBO(cost）

算子代价：cpu,内存，磁盘I/O,网络I/O等方面 - 算子输入数据的统计信息 - 叶子算子scan：统计原始表数据 - 中间算子：通过下层算子的统计信息推导 - 算子类型及物理实现

image.png

统计信息：

类型：

  • 原始表获得

  • 推导得来

    • 选择率
    • 基数

收集方式：

  • 建表的时候指定（DDL）
  • ANALYZE TABLE(手动执行）
  • SELECT XXX(动态采样）

推导规则： 假设列与列之间相互独立，列的值均匀分布（概率论）(经常与实际情况不符）

AND OR 等

执行计划枚举（通常使用 贪心算法 或 动态规划）

（尝试解决RBO无法解决的问题）

小结：

重要概念：执行计划

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

社区开源实践

概览

image.png

Apache Calcite

image.png

  • one size fits all
  • 支持异构数据模型
    • 关系型
    • 半结构化
    • 流式
    • 地理空间数据
RBO模块：

HepPlanner

  • 内置100+优化规则
    • 原理：
      • pattern匹配表达式子树
      • 等价变换得到新的表达式
  • 匹配规则：
    • arbitary/depth_first：深度优先
    • top_down、bottom_up:拓扑排序
  • 结束条件： 遍历所有的rule，直到没有rule可被触发
CBO模块：

VolcanoPlanner

  • 基于Volcano/Cascade框架
  • Meno
  • 本质：AND/OR 图
  • 共享子树减少内存开销
  • 剪枝
  • 动态规划

image.png

前沿趋势

  • 存储计算分离
  • 将事务型和分析型数据一体化，在一个系统中支持所有场景 （HTAP,HSAP,HTSAP）
  • 云原生
  • 湖仓一体 数据湖：把原始数据作为文件存下来，数据不规则、凌乱 仓：数据按一定规则存放 联邦查询，对查询优化器有要求 两者结合
  • 数据+人工智能

    • AIforDB

      • 自配置：智能调参、负载预测
      • 自诊断和自愈合：错误恢复和迁移
      • 自优化

    • DBforAI

    • 内嵌AI算法（MLSQL,SQLFlow）

    • 内嵌机器学习框架（SparkML,Alink等）

avatar
文章
阅读
粉丝