查询优化器（CBO）｜ 青训营笔记

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

CBO(Cost-based Optimizer)

概念

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

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

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

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

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

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

  • 例子:Spark Join算子代价= weight * row_count + (1.0 - weight) * size

• 

统计信息

• 原始表统计信息

  • 表或者分区级别:行数.行平均大小、表在磁盘中占用了多少字节等
  • 列级别: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.

    RcOMMENT VARCHAR(152)

    ) DUPLICATE KEY(R_REGioNkEY) DISTRIBUTED BY HASH(R_REGIONKEY)y BUCKETS 1PROPERTIES ("stats_columns” ="R_NAME"):

• 手动执行explain analyze statement，触发数据库收集或者更新统计信息

  ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name1, columr-name2, ..

• 动态采样

  SELECT count(*) FROM table_name

统计信息推导规则

（前提假设：假设列与列之间是独立的，列的值是均匀分布）

• Filter Selectivity

  • 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: o
    • 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,但是‘比亚迪’和‘汉’是相关联的,实际selectivity = 1/100= 0.01

——这个可以是用户指定或者数据库自动识别相关联的列

在通过直方图的形式——考虑中国人口数据库，性别，年龄，数量都不是均匀分布。

执行计划枚举

• 与RBO相同
• 因此通常使用贪心算法或者动态规划选出最优的执行计划

枚举-动态规划

上述条件：有R,S,T三张表，并且对其做了三表的连接

目标：

操作过程：

1. 为了实现三表的连接，首先是两表的连接，此处有两种方法，一种是Hash Join，另一种是SortMerge Join，由于满足交换律与结合律，所以，满足这些子问题连接的组合，——再计算出各个子问题的代价。

2. 在两表连接后再考虑三表的连接组合，再求出各个组合连接的代价，。

3. 筛选出代价最小的组合

效果-TPC-DS Q25

SELECT

FROM<8 tables>

WHERE<16 predicates>

GROUP BY

ORDER BY

LIMIT 100;

开启CBD：

1. 减少了90%的Shuffle数据量
2. 3.4倍的加速比

小结

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