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

大数据小萌新
170 阅读6分钟

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

SQL查询优化器

  1. SQL在大数据体系中的地位

在数据体系中的分析引擎部分，目前所有主流的框架都支持sql。 例如：

  • 批式分析（离线分析）：spark、hive、MR
  • 实时分析：Flink
  • OLAP：ClickHouse、Doris等

image.png

  1. SQL的处理流程

用用户侧（客户端）被输入的SQL语句一般需要通过连接器进入sql引擎，然后进行词法分析，逻辑优化，物理执行等几个部分，下面我们对解释器、分析器，优化器，执行器进行详细介绍。

  • 解释器parser：String->AST,词法分析:拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token，语法：将token组成AST node 最终得到一个AST。 大数据中sql常见实现方式：ClickHouse(递归下降）、PostgreSQL(Flex和Bison)、Flink(JavaCC)、Presto Spark(Antlr)。
  • 分析器analyzer：主要分成两个部分Analyzer和Logical Plan。Analyer:检查并绑定Database,Table,Column等元信息；SQL合法性检查，比如说min/max/avg的输入是数值；AST->Logical Plan。Logical Plan:逻辑地描述对应步骤计算操作；计算操作：算子（operator)
  • 查询优化器Optimizer:目的：找到一个正确且执行代价最小的物理执行计划；查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的；一般SQL越复杂，join表越多，数据量越大，查询优化意义越大，不同执行方式的性能差别可能又成百上千倍。（具体优化方式算法后面会详细介绍）
  • 执行器：主要分成两个部分Physical Plan和Excutor。Plan Fragment(执行计划子树):目标：最小化网络苏剧传输；利用数据的物理分布(数据亲和性)；增强Shffle算子。Executor(执行):单机并行：cache、pipeline、SIMD;多机并行：一个fragment对应多个实例。

image.png

  1. 查询优化器常见分类

优化顺序分类

  • Top-down Optimizer:从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划（Volcano/Cascade,SQLSever)
  • Bottom-up Optimizer：从零开始，由下往上遍历计划树，找到完整的执行计划（System R,PostgreSQL,IBM DB2)

优化算法分类

  • Rule-based Optimizer(RBO):根据关系代数等价语义，重写查询；基于启发性规则；会访问表的元信息（catalog），不会涉及具体的表数据（data)。
  • Cost-based Optimizer(CBO):使用一个模型估算执行计划的代价，选择代价最小的执行计划
  1. Rule-based Optimizer(RBO)
  • 关系代数：运算符 Select(\alphaα),Project(\piπ),Join(\bowtie⋈),Rename(\rhoρ),Union(UU)等; 等价交换：结合律，交换律，传递性
  • 优化原则：读取数据更快更少（I/O);传输数据更快更少（Network);处理数据更快更少（CPU&Memory)
  • 列裁剪:裁剪掉最后select不需要的列，例如：
SELECT   `pv.siteId,user.name`
         FROM pv JOIN user
         ON pv.sited = user.sitedId AND pv.userId =user.id
         WHERE user.siteId > 123;
复制代码
  • 谓词下推：WHERE中的谓词条件进行下移，在SCAN过程中提前进行筛选
SELECT   pv.siteId,user.name
         FROM pv JOIN user
         ON pv.sited = user.sitedId AND pv.userId =user.id
         WHERE `user.siteId > 123`;
复制代码
  • 传递闭包：谓词下推具有相应的传递性
SELECT   pv.siteId,user.name
         FROM pv JOIN user
         ON `pv.sited = user.sitedId` AND pv.userId =user.id
         WHERE `user.siteId > 123`;
复制代码
  • Runtime Fliter:Hash等算法对SCAN数据进行映射，然后对需要join的SCAN数据利用生成的hash表进行进行时Filter（一种方案）（min-max filter，in-list filter，bloom filter）
  • 谓词合并：将可运算常量，布尔表达式，包含关系的谓词进行合并
  1. Rule-based Optimizer(RBO)
  • 概念：使用一个模型估算执行计划的代价，选择代价最小的执行计算。（所有算子的执行代价之和，通过RBO得到所有可能的等价执行计划）;算子代价：CPU、内存、磁盘I/O，网络I/O等代价，与算子输入数据的统计信息有关，和具体的算子类型，以及算子的物理实现有关。

image.png

  • 统计信息：原始表的统计信息（表或者分区级别：行数、行平均小大;列级别：min、max、nums nulls、histogram等）;推导统计信息（选择率：对于某一个过滤条件，查询会从表中返回多大比例的数据 基数：在查询计划中常指需要处理的行数)
  • 统计信息的收集方式：在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息;手动执行explain analyze statement，触发数据库收集或者更新统计信息;动态采样SELECT count(*) FROM table_num
  • 统计信息推导规则
AND 条件 fs(a AND b)= fs(a)*fs(b)
OR 条件  fs(a OR b)=fs(a)+fs(b)-(fs(a)*fs(b))
NOT 条件 fs(NOT a)=1.0-fs(a)
等于条件(x=literal literal<min && literal>max:0 1/NDV)
小于条件(x<literal literal<min:0 literal>max:1 (literal-min)/(max-min)
复制代码
  • 执行计划枚举： 单表扫描：索引扫描（随机I/O)vs全表扫描（顺序I/O),如果扫描的数据分布非常不均匀，索引扫描可能不如全表扫描;join实现：Hash join VS SortMerge Join;两表Hash Join:用小表构建哈希表-如何识别小表？;多表join：那种连接顺序是最优的，是否每种组合都探索(通常使用贪心算法或者动态规划选出最优的执行计划)
  1. 开源社区实践
  • 总览

image.png

  • Apache Calctie:统一的SQL查询引擎;模块化，插件化，稳定可靠;支持异构数据模型(关系型，半结构化，流式，地理空间数据;内置RBO和CBO;
  • Calcite RBO:HepPlanner:规则优化(Pattern匹配表达式子树)、等价交换:得到新的表达式、内置100+优化规则、

四种匹配规则（深度优先、拓扑顺序、与TOP_DOWN相反)、遍历所有的rule，直到rule可以被触发、优化速度快; VolanPlanner:基于Volcano/Cascade框架、成本最佳假设、Memo:存储候选执行计划、Top-down动态规划搜索、应用Rule搜索候选计划;

  1. 前沿趋势
  • 引擎架构的进化：存储计算分离
  • Cloud：云原生
  • 湖仓一体
  • DATA + AI

8.总结

  • SQL处理流程主要分成解释器（AST),分析器（逻辑执行），优化器（逻辑执行），执行器（物理执行）;
  • 解释器，分析器主要起到一定翻译转换过程，涉及到很多性能方面的影响（有一定影响）;
  • 物理执行主要在分布式内涉及网络和存储方面的调度和优化;
  • 优化器是SQL处理流程中最为影响性能的环节（阶段），优化器的好坏直接决定着SQL处理的时间长短;
  • 优化器有很多成熟的实践引擎，但总体上分成两个部分：根据DDL进行的逻辑优化、结合实际表数据进行的优化方式，详细的优化算法见前面;
  • 优化器要与时俱进，要具备处理大数据分布式云原生等业务场景，同时也有结合一些火热的方向，比如说AI;
avatar
文章
阅读
粉丝