SQL Optimizer解析 | 青训营笔记

Seed_userx
177 阅读5分钟

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

大数据体系和SQL

大数据体系

  • 大数据体系 - One SQL rules big data all

image.png

大数据体系中的SQL

SQL的处理流程

image.png

Parser

  • String -> AST（abstract syntax tree）

    词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token

    语法分析： 将 token 组成AST node，最终得到一个 AST

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

image.png

下面是四个组件的具体说明：

Analyzer

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

例如：

 SELECT country.name, SUM(weblog.bytes) as total 
 FROM country
     INNER JOIN geoip ON country.id = geoip.country_id
     INNER JOIN weblog ON geoip.host = weblog.host
 WHERE weblog.reply = "200" and weblog.host is not null 
 GROUP BY country.name
 ORDER BY total
 LIMIT 10

image.png

Optimizer（查询优化器）

  • SQL 是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做
  • 目标：找到一个正确且执行代价最小的物理执行计划
  • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP（Non-deterministic Polynomial Complete） 的
  • 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍
  • 查询优化器需要感知数据分布，充分利用上数据的物理分布（数据亲和性）
  • 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段

Executor

  • 在SQL的执行过程中，Executor 执行器担当着一个重要的角色，相关操作都需要通过它来执行，相当于一个调度器，吧SQL语句交给它，它来带调用各个组件执行操作

常见的查询优化器

查询优化器的分类

  • Top-down Opptimizer

    从目标输出开始，由上往下遍历计划书，找到完整的最优执行计划

    例：Vocano/Casecade，SQLServer

  • Bottom-up Optimizer

    从零开始，由下往上遍历计划树，找到完整的执行计划

    例：System R，PostgreSQL，IBM DB2

  • Rule-based Optimizer（RBO）

    根据关系代数等价语义，重写查询

    基于启发式规则

    会访问表的元信息（catalog），CBO不会涉及具体的表数据（data）

  • Cost-based Optimizer（CBO）

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

下面具体介绍一下 RBO 和 CBO

RBO

  • 主流 RBO 实现一般都有几百条基于经验归纳得到的优化规则
  • 优点：实现简单，优化速度快
  • 缺点：不保证得到最优的执行计划
关系代数

  • 运算符：Select（σ），Project（Π），Rename（ρ），Union（U）等

  • 等价变换：结合律，交换律，传递性

    Select：σP1∩P2∩...PN（R）= σP1（σP2（...σPN（R）））

    ...

优化原则
  • Read data less and faster（I/O）
  • Transfer data less and faster（Network）
  • Process data less and faster（CPU & Memory）

优化前的逻辑计划

 SELECT pv.siteId, user.name
 FROM pv JOIN user
 ON pv.siteId = user.siteId AND pv.userId = user.id
 WHERE user.siteId > 123;

image.png

列裁剪
  • 列裁剪是SQL优化中最常见的优化规则，就是对于没有用到的列就没必要读取以减少IO的消耗

image.png

谓词下推
  • 将外层查询块的 WHERE 子句中的谓词移入所包含的较低层查询块，从而能够提早进行数据过滤以及有可能更好地利用索引。这在分区数据库环境中甚至更为重要，其原因在于，提早进行过滤有可能减少必须在数据库分区之间传递的数据量。

image.png

传递闭包
  • 对于SQL优化中的传递闭包，我的简单理解是存在间接关系的状态或条件，直接相连。

image.png

Runtime Filter
  • Runtime Filter 是在运行时对数据进行过滤，过滤一般发生在Join阶段。Runtime Filter优化方式与谓词下推的方式一样，同样是对数据进行过滤。其基本原理是通过在join的probe端提前过滤掉哪些不会命中join的输入数据来大幅度减少join中的数据传输和计算，从而减少整体的执行时间。

image.png

CBO

  • CBO 使用代价模型和统计信息估算执行计划的代价
  • CBO 使用贪心或者动态规划算法寻找最优执行计划
  • 在大数据场景下 CBO 对查询性能非常重要
统计信息

  • 原始表统计信息

    表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等

    列级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等

  • 推导统计信息

    选择率（selectivity）：对于某一个过滤条件，查询会从表中返回多大比例的数据

    基数（cardinality）：在查询计划中常指算子需要处理的行数

    • 准确的 cardinality，远比代价模型本身重要。
统计信息的收集方式
  • 在 DDL 里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息
 CREATE TABLE REGION(
     R_REGIONKEY INT NOT NULL,
     R_NAME CHAR(25) NOT NULL,
     R_COMMENT VARCHAR(152)
 ) DUPLICATE KEY(R_REGIONKEY)
 DISTRIBUTED BY HASH(R_REGIONKEY) BUCKETS 1
 PROPERTIES("stats_columns" = "R_NAME");
  • 手动执行 explain analyze statement，触发数据库收集或者更新统计信息
 ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name1, column-name2, ....
  • 动态采样
 SELECT count(*) FROM table_name
统计信息的问题
  • 假设列和列之间是独立的，列的值是均匀分布的（这个假设经常与现实不符！）
  • 根据独立性和均匀分步的假设，用户可以指定或者数据库自动识别相关联的列
  • 考虑到一些不是均匀分布的情况，如：中国人口数据库，性别，年龄，数量都不是均匀分布的，我们可以采用直方图的形式。
CBO的执行计划枚举
  • 通常使用贪心或者动态规划算法找到最小开销
avatar
文章
阅读
粉丝