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

宇汐
107 阅读3分钟

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

CBO概念

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

    • 执行计划的代价等于所有算子的执行代价之和
    • 通过RBO得到（所有）可能的等价执行计划

  • 算子代价：CPU，内存，磁盘I/O，网络I/O等代价

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

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

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

      • 例子：Spark Join算子代价= weight * row_ count + (1.0 - weight) * size

CBO图示：

CBO概念图示.jpg

CBO统计信息

  • 原始表统计信息

    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
    • 列级别：min、max、num nulls、 num not nulls、num distinct value(NDV)、histogram 等

  • 推导统计信息

    • 选择率（selectivity）：对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数（cardinality）：在查询计划中常指算子需要处理的行数

准确的基数（cardinality），远比代价模型本身重要。

CBO统计信息的收集方式

  • 在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，触发数据库收集或更新统计信息

    -- 如 COMPUTE STATISTICS POR COIUMNS
ANALYZE TABLE table_name COMPUTE STATISTICS POR COIUMNS column-name1, columnname2.....

  • 动态采样

    SELECT count(*) FROM table_name;

CBO统计信息推导规则

假设列和列，之间是独立的，列的值是均匀分布

  • Filter Selectivity

    • AND条件: fs(a AND b) = fs(a) * fs(b)

    • OR条件: fs(aORb)= 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)

  • 统计信息出现的问题

    • 假设列和列，之间是独立的，列的值是均匀分布

      • 这个假设经常与现实不符

    • 考虑一个气车数据库automobiles,有10个制造商，100 个车型，filter为“制造商= '比亚迪’ 且车型=汉'根据独立性和均匀分布假设，则selectivity = 1/10 x 1/100 = 0.001，但是'比亚迪’ 和'汉'是相关联的，实际selectivitv = 1/100 = 0.01

      • 用户指定或者数据库自动识别相关联的列

    • 考虑中国人口数据库，性别，年龄，数量都不是均匀分布的

      • 直方图

CBO执行计划枚举

通查使用贪心算法或者动态规划选出最优执行计划。

CBO效果-TPC-DS Q25

SELECT <XXX>
FROM <8 tables>
WHERE <16 predicates>
GROUP BY <XXX>
ORDER BY <XXX>
LIMIT 100;

上述SQL语句关闭CBO的示意图如下，有如下问题：1.Shuffle数据量太大。2.执行效率差 CBO未使用效果.jpg

使用CBO优化之后示意图如下图：

CBO使用效果.jpg 需要注意的是有时开启CBO和未开启CBO的效率没有明显变化，是因为RBO能为这些查询找到最优的执行计划

CBO小节

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