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

huntressie
101 阅读5分钟

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

  • 大数据体系

image.png One SQL rules big data all

  • SQL在分布式环境下的处理
    • 处理流程
      • SQL---|Parser|---AST---|Analyzer|---Logical Plan---|Optimizer|---Physical Plan---|Executor|
      • Parser
        • String->AST(Abstract syntax tree)
          • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号Token
          • 语法分析：将Token组成AST node，最终得到AST
        • 实现
          • 递归下降(ClickHouse)
          • Flex与Bison(PostgreSQL)
          • JavaCC(Flink)
          • Antlr(Presto, Spark)
      • Analyzer
        • AST->Logical Plan
          • 检查并绑定Database,Table,Column等元信息
          • SQL的合法化检查，比如min/max/avg的输入为数值
          • Logical Plan
            • 逻辑地描述SQL对应的分步骤计算操作
            • 计算操作：算子(operator)
      • Optimizer
        • why: SQL是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做
        • target：找到一个正确且执行代价最小的物理执行计划
        • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的
        • 一般SQL越复杂，Join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能成百上千倍
        • Physical Plan
          • Plan Fragment：执行计划子树
            • 目标：最小化网络数据传输
            • 利用数据的物理分布（数据亲和性）
            • 增加Shuffle算子
      • Executor
        • 单机并行：cache,pipeline,SIMD
        • 多机并行：一个fragment对应多个实例
  • 常见的查询优化器
    • Top-down Optimizer
      • 从目标输出开始，由上往下遍历计划数，找到完整的最优执行计划
      • Volcano/Cascade,SQLServer
    • Bottom-up Optimizer
      • 从零开始，由下往上遍历计划数，找到完整的执行计划
      • System R,PostgreSQL,IBM DB2
    • RBO(Rule-based Optimizer)
      • 根据关系代数等价语义，重写查询
        • 关系代数
          • 运算符：Select,Project,Join,Rename,Union
          • 等价变换：结合律，交换律，传递性
      • 基于启发式规则
      • 会访问表的元信息(catalog)，不会涉及具体的表数据(data)
      • 优化原则
        • Read data less and faster(I/O)
        • Transfer data less and faster(Network)
        • Process data less and faster(CPU&Memory)
      • 优化方法
        • 列裁剪
          • 首先将不需要的列裁剪掉
        • 谓词下推
          • 谓词(where)
          • 尽早过滤数据
        • 传递闭包
          • 根据表达式等价关系+过滤条件推导出新的过滤条件
        • Runtime Filter
          • 通过join右侧的表的特性(min-max,in-list,bloom filter)推导出左侧表的过滤条件
      • 小结
        • 主流RBO实现一般都有几百条基于经验归纳得到的优化规则
        • 优点：实现简单，优化速度快
        • 缺点：不保证得到最优的执行计划
          • 单表扫描：索引扫描(随机I/O)vs全表扫描(顺序I/O)
            • 如果查询数据分布非常不均衡，索引扫描可能不如全表扫描
          • Join的实现:Hash Join vs. SortMerge Join
          • 两表Hash Join：用小表构造哈希表——如何识别小表
          • 多表Join：
            • 哪种连接顺序最优
            • 是否对每种组合都探索
    • CBO(Cost-based Optimizer)
      • |统计信息+推导规则|->|计算算子代价|->|计算执行计划代价|->|执行计划枚举|
      • 使用一个模型估算执行计划的代价，选择代价最小的执行计划
        • 执行计划的代价等于所有算子的执行代价之和
        • 通过RBO得到(所有)可能的等价执行计划
      • 算子代价:CPU,内存,磁盘I/O,网络I/O等代价
        • 和算子输入数据的统计信息有关:输入、输出结果的行数，每行大小...
          • 叶子算子Scan:通过统计原始表数据得出
          • 中间算子:根据一定的推导规则，从下层算子的统计信息推导得出
        • 和具体的算子类型，以及算子的物理实现有关
        • 例子:Spark Join算子代价=weight*row_count+(1.0-weight)*size
      • 统计信息
        • 原始表统计信息
          • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
          • 列级别：min,max,num nulls,num not nulls,num distinct value(NDV),histogram等
        • 推导统计信息
          • 选择率(Selectivity):对于某一个过滤条件，查询会从表中返回多大比例的数据
          • 基数(Cardinality):在查询计划中常指算子需要处理的行数
            • 准确的cardinality，远比代价模型本身重要
        • 收集统计信息
          • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息
          • 手动执行explain analyze statement,触发数据库收集或者更新统计信息
          • 动态采样
        • 统计信息推导规则
          • 假设列和列之间是独立的，列的值是均匀分布的
          • Filter Selectivity
            • AND条件
            • OR条件
            • NOT条件
            • 等于条件
            • 小于条件
            • cardinality(FILTER)=cardinality(A)*selectivity(FILTER)
      • 执行计划枚举
        • 通常使用贪心算法或者动态规划选出最优执行计划
  • 社区开源实践
    • Apache Calcite
      • One size fits all:统一的SQL查询引擎
      • 模块化，插件化，稳定可靠
      • 支持异构数据模型
        • 关系型
        • 半结构型
        • 流式
        • 地理空间数据
      • 内置RBO和CBO
        • RBO
          • HepPlanner
            • 优化规则
              • Pattern:匹配表达式子树
              • 等价交换：得到新的表达式
              • 内置100+优化规则
            • 四种匹配规则
              • ARBITRARY/DEPTH_FIRST:深度优先
              • TOP_DOWN:拓扑排序
              • BOTTOM_UP:与TOP_DOWN相反
            • 遍历所有规则，知道没有规则可以被触发
            • 优化速度快，实现简单，但是不保证最优
        • CBO
          • ValcanoPlanner
            • 基于Valcano/Cascade框架
            • 成本最优假设
            • 应用规则搜索候选计划
            • Memo：存储候选执行计划
              • Group：等价计划集合
              • 本质是AND/OR graph
              • 共享子树减少内存开销
            • Group Winner：目前的最优计划
            • 剪枝(Branch-and-bound pruning)：减少搜索空间
            • TOP-DOWN遍历：选择winner构建最优执行计划
  • 前沿趋势
    • 引擎架构的进化
      • 存储计算分离
      • 一体化(HTAP,HSAP,HTSAP)
    • Cloud
      • 云原生
      • serverless
    • 湖仓一体
      • Query Federation
    • DATA+AI
      • AI4DB
        • 自配置
          • 智能调参(OtterTune,QTune)
          • 负载预测/调度
        • 自诊断和自愈合：错误恢复和迁移
        • 自优化
          • 统计信息估计(Learned cardinalities)
          • 代价估计
          • 学习型优化器(IBM,DB2,LEO)
          • 索引/视图推荐
      • DB4AI
        • 内嵌人工智能算法(MLSQL,SQLFlow)
        • 内嵌机器学习框架(SparkML,Alink,dl-on-flink)
avatar
文章
阅读
粉丝