SQL 查询优化 | 青训营笔记

AstroCat
98 阅读3分钟

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

大数据体系

业务应用BI / DM / 营销分析 / 精准推荐管控运维
数据开发Airflow / DAG集群创建
权限管控Apache Ranger / GDPR集群创建
分析引擎批式 (Spark / Hive / MR)
实时 (Flink)
交互 (Presto / ClickHouse / Doris)		集群管理
服务管理
资源调度YARN / K8S用户管理
存储系统HDFS / Base / NAS / Object Store / Data Lake监控报警
基础设施ECS / 存储 / VPC日志查询

SQL 处理流程

SQL -Parser- AST -Analyzer- Logical Plan -Optimizer- Physical Plan -Executor

  • Parser: String -> AST

    • Lexical analysis 词法分析：拆分字符串为 token: 关键词，const，运算符...
    • Syntactic analysis 语法分析：将 token 组成 AST node，得到 AST

  • Analyzer

    • 检查并绑定 database / table / column 等 metadata
    • SQL 合法性检查
    • AST -> Logical Plan

    之所以称它为逻辑的，是因为算子定义的是逻辑的计算操作，没有指定实际的算法

    • Logical Plan 
      - 节点 ~ 计算操作 = 算子 (operator)
- 数据流向：edge 从子节点到父节点
      • Physical Plan 执行计划子树
        • 最小化网络数据传输（利用数据的物理分布 aka 数据亲和性）
        • 增加 shuffle 算子 (Plan 分布式化的方法是增加 shuffle 算子，执行计划树会以 shuffle 算子为边界拆分为PlanFragment)
      • Executor
        • 单机并行：cache, pipeline, SIMD
        • 多机并行：1 fragment -> multiple instance Physical Plan

查询优化器

按照最小化网络数据传输的目标把 logical plan 拆分成多个 physical plan fragment

  • Top-down optimizer: 自上而下便利计划树，完整的最优执行计划
  • Bottom-up optimizer：从零开始，自下而上，完整的执行计划

RBO (Rule-based Optimizer)

  • 根据关系代数等价语义，重写查询
    • Pattern finding
  • 基于启发式规则
  • 仅访问表的元信息 (catalog)
优化规则
  • I/O read data less & faster
  • Network transfer data less & faster
  • CPU process data less & faster 主流实现一般有数百条经验规则，实现简单，优化速度快，但不保证 optimal
  • 列裁剪：自上而下计算需要的列，只 scan 需要的数据
  • 谓词下推：调换算子顺序，尽早过滤数据
  • 传递闭包：推导出新的过滤条件
  • *Runtime filter: min-max, in-list, bloom filter

Further reading: Rule-based Query Optimization by Vladimir Ozerov

CBO (Cost-based Optimizer)

CBO

  • 使用模型估算 RBO 得到的所有等价执行计划的代价，选择 min cost plan

  • 使用 动态规划 / 贪心算法 枚举所有执行计划，选出执行代价最小的执行计划

  • 算子代价包含 CPU，cache misses，memory，disk I/O，network I/O 等代价

  • 和算子的统计信息有关，比如输入、输出结果的行数，每行大小等

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

  • 原始表统计信息

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

      • 表或分区：行数、行平均大小、磁盘空间...
      • 列：min, max, num nulls, num not nulls, num distinct value(NDV), histogram...

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

    • how

      • DDL 里指定需要收集的统计信息，在写入 db 时会收集/更新

      • 手动执行 explain analyze statement 触发 db 收集/更新

        ANALYZE TABLE tab
COMPUTE STATISTICS FOR COLUMNS col1, col2
        • 动态采样
        SELECT count(*) FROM tab

  • 推导统计信息

    • 选择率 selectivity：对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数 cardinality：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数
      • 准确统计 cardinality 远比代价模型重要
统计信息推导规则

假设列与列间是独立的，列的值均匀分布，有:

  • Filter selectivity
    • cardinality(FILTER) = cardinality(A) * selectivity(FILTER)
    • fs(a AND b) = fs(a) * fs(b)
    • fs(a OR b) = fs(a) + fs(b) - fs(a) * fs(b)
    • fs(NOT a) = 1 - fs(a)
    • fs(x = literal) = (1 / NDV) if min <= literal <= max else 0
    • fs(x < literal) = (literal - min) / (max - min); bounded in [0, 1]

开源实践 example

  • Calcite
    • HepPleanner
    • VolcanoPlanner

前沿趋势

  • 引擎架构进化
    • 存储计算分离 解耦
    • 一体化（HTAP, HSAP, HTSAP）
  • Cloud
    • 云原生
    • serverless 云中立
  • 湖仓一体
    • Query federation
  • DATA + AI
avatar
文章
阅读
粉丝