SQL Optimizer解析 ｜ 青训营笔记

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

大数据体系

基础设施：ECS、存储、VPC 存储系统：HDFS、HBase、Nas、Object Store、数据湖 资源调度：Yarn、K8S 分析引擎：（SQL接口） 批式分析：Spark、Hive、MR 实时分析：Flink 交互分析：Presto、ClickHouse、Doris 权限管控：Apache Ranger、GDPR 数据开发：Airflow、DAG 业务应用：BI报表、数据挖掘、营销分析、精准推荐

消息队列（分析引擎-资源调度-存储系统-基础设置）：Kafka、Pulsar、NSQ

管理控件：管控运维、集群创建、集群管理、服务管理、用户管理、监控报警、日志查询

SQL： SQL --> ||Parser|| --> AST --> ||Analyzer|| --> Logical Plan --> ||Optimizer|| --> Physical Plan --> ||Executor||

• Parser阶段:将文本变为抽象语法树结构AST。 词法分析：拆分字符串，得到关键词，数值常量、字符串常量、运算符号等token。 语法分析：将token组成ASTnode，最终得到一份AST。 实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），JavaCC（Flink），Antlr（Presto，Spark）

• Analyzer：访问表元信息（Database、Table、Column）并绑定、判断SQL是否合法、将AST转化为逻辑计划树（Logical Plan）

• 逻辑计划树（Logical Plan--Left deep tree）：描述SQL的执行过程，Logical Plan中的每一个节点都是一个算子（operator），边代表数据的流向。SCAN算子扫描表的数据。Top-N可以理解为堆排序。

• 查询优化:应对大数据查询做出的优化，为SQL找到执行代价最小的执行计划。

• 优化器的输出是一分布式的物理执行计划，物理执行计划的目标是在单机Plan的基础上最小化数据移动和最大化本地Scan生成PlanFragment树。一个PlanFragment封装了在一台机器上对数据集的操作逻辑，可以在每个executor节点生成1个或多个执行实例，不同执行实例处理不同的数据集，通过并发提升查询性能。增加shuffle算子进行分布式化，拆分PlanFragment

executor：按照物理计划扫描处理数据，充分利用机器资源。

常见的查询优化器

查询优化器分类： Top-down Optimizer：从目标输出开始，从上到下遍历计划树，找到完整的最优执行计划。SQLServer。 Bottom-up Optimizer：从零开始，从下到上遍历计划树。Sytem R， PostgreSQL，IBM DB2。

RBO（Rule-Based Optimizer）：

基于怠速等价规则对逻辑计划进行变换 实现上： Pattern：定义了特定结构的Operator子树 Rule：定义了如何将其匹配的节点替换为新形态，从而生成新的、等价的Operator树 优化器搜索过程被抽象为不断匹配的Pattern然后应用Rule转换，直到没有匹配的Rule。 局限性：无法解决多表连接问题、无法确定和选择最优的分布式Join/Aggregate执行方式

• 列裁剪：去掉表中的无关列，SCAN的过程中扫描相关列。
• 谓词下推：例如过滤谓词准确下推到某一表的SCAN之前，避免多个无关表使用谓词。
• 传递闭包：经过推到，传递性，增加新的谓词。
• Runtime Filter：传递一个满足要求的小范围数据到join的另一个分支。

缺点：

• 单表扫描：索引扫描和全表扫描
• join实现：hash join和sortmerge join
• 两表的HASH join：用小表构建哈希表，没办法识别小表
• 多表join：无法选择最优连接

CBO（Cost-Based Optimizer）：

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

• 原始表统计信息：
  • 搜集方式：在DDL里指定要搜集的信息，写入更新、手动执行explain analyze statement、动态采样
• 推导统计信息：
  • 选择率：返回数据的比例
  • 基数：算子需要处理的行数（输入数据量）
  • 执行计划的枚举：根据不同情况选择代价最小的路径。

社区开源实践

apache calcite：Memo、动态规划、剪枝

前沿趋势--SQL优化器的新要求

引擎架构的进化：存储计算分离，一体化（HATP、HSAP、HTSAP） 云原生：K8S，动态调整集群规模 湖仓一体：原始数据数据湖，数据仓库主要是关系型数据库 DATA+AI: AI4DB:自配置、自优化 、自诊断自愈合（错误恢复和迁移） DB4AI:MLSQL SQLflow SparkML Alink dl-on-flink