查询优化器之RBO|青训营笔记

宇汐
237 阅读5分钟

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

今天主要根据老师课上所讲记录下SQL查询优化器的相关知识点，便于以后复习理解

1.查询优化器的分类

  1. Rule-based Optimizer (RBO)

    • 根据关系代数规则对逻辑计划进行变换，重写查询
    • 基于启发式规则
    • 会访问表的元信息（catalog），不会涉及具体的表数据（data）

  2. Cost-based Optimizer (CBO)

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

  3. Top-down Optimizer

    • 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划。
    • 比如：Volcano/Cascade, SQLServer

  4. Bottom-up Optimizer

    • 从零开始，由下往上遍历计划树，找到完整的执行计划
    • 比如：System R，PostgreSQL, IBM DB2

2.RBO

了解RBO中的关系代数

  • 运算符：Select (σ)，Project (π)，Join (x)（符号x上下还有-） ，Rename (ρ)，Union (U) 等
  • 等价交换：结合律，交换律，传递性
  • 具体SQL语句转为关系代数的图示

关系代数.jpg RBO查询优化器的实现

  • Pattern（模式）：定义了特定结构的 Operator 子树（结构）
  • Rule（规则）：定义了如何将其匹配的节点替换（Substitute）为新形态，从而生成新的、等价的Operator 树（原地替换）
  • 优化器搜索过程被抽象为不断匹配 Pattern 然后应用 Rule 转换，直到没有可以匹配的 rule

RBO优化原则

  • 优化IO：读取的数据更少，速度更快（Read data less and faster）
  • 优化网络：传输的数据更少，速度更快（Transfer data less and faster）
  • 优化CPU/内存：cpu处理数据更少，速度更快（Process data less and faster）

以下面的SQL语句为例一步步分析优化

-- 关联两个表根据条件去查询两个字段，
SELECT pv.siteld, user.name
FROM pv JOIN user
ON pv.siteld = user.siteld AND pv.userld = user.id
WHERE user.siteld > 123;

它优化前的逻辑计划如下图：

优化前的逻辑计划jpg.jpg

几条常见的优化原则

  1. 列裁剪

    不需要对两个表的所有列都进行读取扫描，只扫描需要的列即可，把其他不需要的列裁剪去掉。如上例，两个表中用到的列有pv中的两列pv(siteid,userid)，user中的三列user(id,siteid,name)，从上往下扫描看都需要哪些列，依次构成新的集合，最终合并为所有算子的集合，只用扫描这几列即可，优化后如下图：

RBO-列裁剪.jpg

  1. 谓词下推

    谓词就是where后面的条件，它先执行与后执行的效率是不一样的，是否能下推还要具体看连接的条件。这个例子中ON pv.siteld = user.siteld AND pv.userld = user.id WHERE user.siteld > 123;，可以先将user.siteid>123这个条件先过滤，然后再进行两表的连接条件，并不影响最后的结果，减少了连接的次数，优化后如下图：

RBO-谓词下推.jpg

  1. 传递闭包

    在一些SQL语句中，可以根据连接条件与谓词条件进行结合，看是否能推出其他可以下推的条件，进而减少连接次数，加快速度。

    -- pv.siteld = user.siteld 这个条件与 user.siteld > 123 其实是可以根据传递性继续推
-- 进而可以得到pv.siteid也大于123，进而先把这个条件也先过滤出来，后去连接，进而加快连接速度
SELECT pv.siteld, user.name
FROM pv JOIN user
ON pv.siteld = user.siteld AND pv.userld = user.id
WHERE user.siteld > 123;

RBO-传递闭包.jpg

  1. RunTime Fileter(运行时过滤器)

    Runtime Filter是一种在数据库中广泛使用的一种优化技术，其基本原理是通过在join的probe端（查询端）提前过滤掉那些不会命中join的输入数据来大幅减少join中的数据传输和计算，从而减少整体的执行时间。

RBO-RunTime Filter.jpg

比如右图，最右边的的filter现将大于123的siteid过滤出来，加入过滤后的范围是1-100，那么将这个runtime filter传递给左端，那么他只需要扫描这个范围内的数据即可。

看一下三个常用的过滤器

-   min-max：过滤出一个最大最小的范围，这个在数据比较紧密的时候可以使用
-   in-list：如果过滤出来的值很少，比如过滤出的范围是0-100w，但数只有100个，那么使用这个更好
-   bloom filter：布隆过滤器，大小相对固定，实现布隆算法。如果一个数不在，肯定不在，如果一个数在，可能会存在，有较小的误差。

RBO小节

  1. 主流RBO实现一般都有几百条基于经验归纳得到的优化规则。

  2. 优点：实现简单，优化速度快。

  3. 缺点：不保证得到最优的执行计划

    1. 单表扫描：索引扫描(随机1/O) vs. 全表扫描(顺序/O)

      • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描

    2. 无法确定和选择最优的分布式 Join执行方式，不知道使用哪个Join 的实现: Hash Join vs SortMerge Join

    3. 两表Hash Join：用小表构建哈希表一如何识别小表RBO是做不到的

    4. 对于多表连接，无法判断哪种连接顺序是最优的

      • 是否要对每种组合都探索?
      • N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序 e.g. N= 10->总共3, 628, 800个连接顺序
avatar
文章
阅读
粉丝