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

foliage
114 阅读5分钟

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

一、大数据结构体系

image.png

大数据体系分类：

  • 存储体系：Hadoop-HDFS、HBase、MongoDB、Cassandra

  • 计算体系：Hadoop-MapReduce、Spark、Storm、Flink

  • 数据同步：Sqoop、DataX

  • 资源调度：YARN、Oozie、Zookeeper

  • 日志收集：Flume、Logstash、Kibana

  • 分析引擎：Hive、Impala、Presto、Phoenix、SparkSQL

  • 集群监控：Ambari、Ganglia、Zabbix

SQL是处理大数据事实的一个接口，像上面很多分析引擎都是支持和提供SQL接口，而SQL又可以很容易实现对数据的处理，因此SQL在大数据中十分重要。可以用一句话概括SQL对大数据的重要程度：ONE SQL RULES BIG DATA ALL。

二、SQL的处理流程

image.png

  1. Parser

    • string-> AST（abstract syntax tree）

      • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号，这些被拆分的字符串被称为token。
      • 语法分析：将token组成AST node ，最终得到一个AST。

    • 实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），javaCC（Flink），Antlr（Presto，Spark）

2.Analyzer 和 Logical Plan

1）Analyzer

  • 检查并绑定Database，table，column等原信息
  • SQL的合法性检查，比如min/max/avg的输入是数值
  • AST->Logical Plan
  1. Logical Plan
  • 逻辑地描述SQL对应分步骤计算操作
  • 计算操作：算子（operator）

  1. SQL查询优化

    • SQL一种声明式语言，用户只描述做什么，没有告诉数据库怎么做
    • 目标：找到一个正确且执行代价最小的物理执行计划
    • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是无法求得最优解
    • 一般SQL越复杂，join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方法的性能差别可能有成百上千倍

  2. physical Plan 和 executor

    plan gregment：执行计划子树

    • 目标：最小化网络数据传输
    • 利用上数据的物理分步（数据亲和性）
    • 增加shuffle算子

    executor

    • 单机并行：cache，pipeline，SIMD
    • 多级并行：一个fragment对应多个实例

  3. 小结

    • one SQL rules big data all
    • SQL需要依次经过parser、Analyzer、Optimizer和Executor的处理
    • 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
    • 查询优化器需要感知数据分布，充分利用数据的亲和性
    • 查询优化器按照最小化网络数据传输的目标把 逻辑计划拆分成多个物理计划片段

三、常见的查询优化器

查询优化器的分类

  1. 按遍历计划树的方式

    • Top-down Optimizer

      • 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
      • 例子：Volcano/Cascade ，SQLserver

    • Bottom-up Optimizer

      • 从零开始，由下往上遍历计划树，找到完整的执行计划
      • 例子：system R ,RostreSQL,IBM DB2

  2. 按采用什么规则分类

    • Rule-based Optimizer（RBO）

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

    • Cost-based Optimizer

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

RBO的优化原则和实现方式

1.RBO优化原则

  • 读少量数据并且以最快的方式读（I/O）
  • 交换数据更少并且以最快的方式交换（network）
  • 进程中数据更少并且更快（CPU & Memory）

2.优化方式

（1）列裁剪：只读取用到的列，可以减低I/O。

（2）谓词下推：让过滤条件先执行，可以减少运算，减低I/O。

（3）传递闭包：可以用已知的关系，推导出还满足的关系，然后用该关系取过滤元数据，可以减少运算，减低I/O。

（4）runtime filter：运行时过滤，就是用当前已经过滤的数据去判断下一次要过滤的数据，已达到减低I/O。常见的runtime filter有如下： min-max、in-list 、bloom filter。

CBO的执行方式和原理

什么是CBO：

image.png

CBO是使用一个模型估算执行计划的代价，选择代价最小的执行计划。执行计划的代价等于所有算子的执行代价之和，通过RBO得到（所有）可能的等价执行计划，然后选择最小执行代价的去执行最终的查询。

算子的代价：

CPU，内存，磁盘I/O,网络I/O等代价

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

    • 叶子算子Scan：通过统计原始表数据得到
    • 中间算子：根据一定的推导规则，从下层算子的统计信息推导得到

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

  • 例子：Spark join 算子代价 = weight * row_count + (1.0 - weight) * size

统计信息的收集：

1.在DDL中指定需要收集的统计信息

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

3.动态采样

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

四、Apache Calcite概览

image.png

  • One size fits all：统一的SQL查询引擎
  • 模块化、插件化、稳定可靠
  • 支持异构数据结构
  • 内置RBO和CBO

五、总结

  • 了解大数据结构体系，大致了解了相关组件的作用，但只限于表面，还需深度学习。
  • 了解了SQL的处理流程，先对输入的字符串进行词法分析得到AST，然后经过Anlyzer查询是否存在语法错误和绑定相关的表和列得到Logical plan，然后再经过查询优化得到Physical plan，最后交由相关组件部署执行。
  • 了解了查询优化器核心的优化Optimizer的具体实现方式和相关优化方式。
  • 了解了Apache Calcite
avatar
文章
阅读
粉丝