本文假定读者对执行计划，普通 join 执行，Map join，Bucket Map join，SMB((Sort Merge Bucket) join， Skew Join 的执行过程比较熟悉。

背景知识

Hive 执行计划解释示例Hive Bucket Table 的功能和使用方法详解Hive 使用 List Bucketing 表解决数据倾斜问题

1. 基于规则的优化

基于规则的优化（Rule Based Optimization）:预先定义一些规则，按这些规则进行优化，效率更高。
不同的 SQL 处理器根据不同的引擎有不同的规则。如 Oracle 等传统数据库有使用索引的很多规则，这些规则在大数据框架里不适用。大数据计算框架里也有很多规则，传统的数据库不适用。

常见的基于规则的优化的规则有：谓词下推、常量上推和 Limit 限定。

1.1 PPD – Predicate Push Down(谓词下推)：

谓词下推包含两个内容，第 1 个是检索条件下推，第 2 个是查询字段下推。举例如下：
orders: 订单表，每个订单一条记录
lineitem：订单明细表，每个订单中每个物品一条记录，按 orderkey 和 orders 表关联。

select  c.name, sum(quatity),sum(totalprice) 
from (
     select l.quatity, l.totalprice, o.custkey
     from lineitem l join orders o
     on (l.orderkey = o.orderkey)
 ) temp join customer c on (temp.custkey = c.custkey)
 where temp.o_order_date='20210101' and temp.custkey='001'
 group by c.name ;

以上查询是查询某一个客户编号的客户名称，和 2021 年买的所有商品的数量和金额。

1.1.1 检索条件下推：

检索条件下推就是尽量把检索条件尽可能的推到里面。
谓词下推后的操作类似于以下 SQL：

select  c.name, sum(quatity),sum(totalprice) 
from (
     select l.quatity, l.totalprice, o.custkey
     from lineitem l 
     join 
     (select * 
          from orders 
          where o_order_date='20210101' and custkey='001'
     ) o
     on (l.orderkey = o.orderkey)
 ) temp join customer c on (temp.custkey = c.custkey)
 group by c.name ;

谓词下推后，orders 表中参加 join 计算的记录数减少，提高了计算效率。

1.1.2 查询字段下推

查询字段下推是指尽量在表扫描中查询需要的字段。
优点：

  1. 列存储的表可以只从文件里读取相应的列，提高读取速度。
  2. 可以减少参加计算的数据量，提高计算速度。
select  c.name, sum(quatity),sum(totalprice) 
from (
     select l.quatity, l.totalprice, o.custkey
     from lineitem l join orders o
     on (l.orderkey = o.orderkey)
 ) temp join customer c on (temp.custkey = c.custkey)
 where temp.o_order_date='20210101' and temp.custkey='001'
 group by c.name ;

如果在表扫描中不是检索所有的字段，而是仅检索需要的字段。需要的字段包括 select 中的字段，谓词判断中用到的字段，关联条件用到的字段，group by 用到的字段。
在表扫描的时候只检索的字段如下：
lineitem： orderkey， quatity，totalprice。
orders： orderkey，o_order_date，custkey。
customer：custkey，name 字段。

同样的道理，如果子查询中查询的字段，在上层查询用不到，在子查询中也可以去掉。

1.1.1.3 关联条件的下推
select * from A a join B b on a.id=b.a_id and a.id=1

on 部分的关联条件下推到 A 表的遍历中。

Filter Set 下推

如以下语句，先求两张表的 union all，再把 union all 的结果执行过滤。

select * from (
  (select ws_item_sk item, ws_quantity quantity from web_sales) 
  union all 
  (select ss_item_sk item, ss_quantity quantity from store_sales)
) t where item='22144';

改写为以下语句，在 table scan 的时候进行过滤：

select * from (
  (select ws_item_sk item, ws_quantity quantity from web_sales where ws_item_sk='22144') 
  union all 
  (select ss_item_sk item, ss_quantity quantity from store_sales where ss_item_sk='22144')
) t;

1.2 汇聚去除

create table t1(c1 string);
alter table t1 add constraint t1_pk primary key(c1) disable novalidate rely;
insert into t1 values("1");
explain select distinct c1 from t1;

可以看到，没有 group by 操作。因为 t1 中 c1 是主键，所以 distinct 可以直接去掉。

STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: t1
          Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: c1 (type: string)
            outputColumnNames: _col0
            Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: NONE
            ListSink

Time taken: 0.201 seconds, Fetched: 17 row(s)

1.3 投影去除 Project Remove

select a_id,  b_id ,c.id c_id from (
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a join b on a.id = b.id
) t_ab 
join c on a_id = c.id

因为最终结果不需要 a_name, b_name ，所以中间的查询 t_ab 可以把 a_name, b_name 部分去掉。

1.4 排序去除

如果底层的数据已经有序，则上层的排序可以去除。

1.5 常量上推

常量上推就是把常量推到上面的查询中，越往后计算越好。

select a_c1, b_c1, name, xxx from (
  select a.a_c1,b.b_c1,'fix value'  name 
  from a join b 
  on xxx 
  where xxx
) t join c on xxx where xxx

‘fix value’ 这个字段在子查询中是一个常量，可以推到上面的查询中，改写之后SQL以下 ：

select a_c1, b_c1, 'fix value' name, xxx from (
  select a.a_c1,b.b_c1 from a join b on xxx where xxx
) join c on xxx where xxx

1.6 检索条件的传递

以下语句查询 某个客户2021年1月1日的订单数量。

select count(1) cnt 
from orders o join customer c on (o.custkey = c.custkey)
where o.o_order_date='20210101' and c.custkey='001';

在遍历 orders 的记录时，自动加上 custkey = ‘001’ 的条件。

1.7 Limit 限定

select  * from web_sales join web_site on ws_web_site_sk = web_site_sk and web_site_sk=10 limit 10;

由于最多只保留 10 个结果，可以让每个 reduce 都限定输出 10 条数据后结束。

1.8 or 转换为 in

如此 SQL 查找特定 100 个用户的订单数量

select count(1) from orders where custkey = '001' or custkey = '002' or … or custkey = '100';

如果以上 SQL，custkey 的基数很大，那么绝大部分会判断 100 次。
当按 custkey 的 or 的数量大于一定阈值时（默认 31），可以转换为 in，可以执行的更高效。以上 SQL 改写为：

select count(1) from orders where custkey in ( '001', '002' , … ,'100');

In 的条目组成一个 hash 表，每个 orders 的记录，根据 custkey 的值，判断 hash 表里是否有记录，只用判断一次。

1.9 Distinct 汇聚改写

如 select count(distinct c1) from t1; Map 对 c1 进行 group by，然后按 c1 进行 partition 发往 reduce 2。c1 相同的记录发往同一个 reduce。
Reduce 2把接收的数据再进行 group by 汇聚，这步完成去重操作。然后进行 count(_col0) 操作，输出本 reduce 中 distinct 的个数。
Reduce 3 把各 reduce2 的结果进行 partial 汇聚操作。

hive> explain select count(distinct c1) from t1;
OK
Plan optimized by CBO.

Vertex dependency in root stage
Reducer 2 <- Map 1 (SIMPLE_EDGE)
Reducer 3 <- Reducer 2 (CUSTOM_SIMPLE_EDGE)

Stage-0
  Fetch Operator
    limit:-1
    Stage-1
      Reducer 3
      File Output Operator [FS_11]
        Group By Operator [GBY_9] (rows=1 width=8)
          Output:["_col0"],aggregations:["count(VALUE._col0)"]
        <-Reducer 2 [CUSTOM_SIMPLE_EDGE]
          PARTITION_ONLY_SHUFFLE [RS_8]
            Group By Operator [GBY_7] (rows=1 width=8)
              Output:["_col0"],aggregations:["count(_col0)"]
              Group By Operator [GBY_4] (rows=1 width=22540)
                Output:["_col0"],keys:KEY._col0
              <-Map 1 [SIMPLE_EDGE] vectorized
                SHUFFLE [RS_14]
                  PartitionCols:_col0
                  Group By Operator [GBY_13] (rows=1 width=22540)
                    Output:["_col0"],keys:c1
                    Select Operator [SEL_12] (rows=1 width=22540)
                      Output:["c1"]
                      TableScan [TS_0] (rows=1 width=22540)
                        test@t1,t1,Tbl:COMPLETE,Col:NONE,Output:["c1"]

基于代价的优化

基于代价的优化是比较不同的执行计划，选择代价低的执行计划执行。

设置 hive.cbo.enable=true 开启基于代价的优化。

基于代价的优化用于 JOIN 之间的排序和 join 算法的选择，依靠统计信息。

JOIN 顺序对执行效率有比较大的影响，举例说明如下：

select  c.name, sum(quatity),sum(totalprice) 
from (
     select l.quatity, l.totalprice, o.custkey
     from lineitem l join orders o
     on (l.orderkey = o.orderkey)
 ) temp join customer c on (temp.custkey = c.custkey)
 where  temp.custkey='001'
 group by c.name ;

如 lineitem 有 10 万条记录，orders有 1 万条记录。customer 有100 条，按 custkey=‘001’ 过滤后剩 1 条。

  • 方案 1
    inter1 = lineitem JOIN orders ，inter1 有10 万条记录。
    result = inter1 join customer， 10 万条 记录 join 1 条 得到结果
  • 方案 2
    inter1 = customer join orders，结果仅有 100 条
    result = inter1 join lineitem

方案 1 第 1 步两个大表进行 join 计算，需要两个表的数据都需要 shuffle 到 reduce。
方案 2 每一个 join 都是一个大表关联一个小表，都可以用 map join 完成。

基于规则的优化考虑以下因素：

基于规则的优化考虑以下因素：

  1. CPU 使用量
  2. IO 使用量
  3. 计算产生的记录数（不同的算法，产生的中间计算结果的数量不相等）
  4. 记录平均大小

记录平均大小和记录数用来估计存储需要的内存，可以决定是否可以采用某些 join 算法，如 Map/ Bucket join。

Calcite 默认使用火山优化器（Volcano optimizer）来计算不同执行计划的代价。火山代价（VolcanoCost）仅用记录数来评判执行计划是否高效。

HiveCost 是 Hive 的代价实现，该代价包括 CPU, I/O, 记录数和记录平均大小。HiveCost 认为 CPU + I/O 优先级更高。

计算代价时使用的代价变量

  • Hr - 从 HDFS 读 1 字节花费的代价（纳秒数）
  • Hw - 往 HDFS 写 1 字节花费的代价（纳秒数）
  • Lr - 从本地文件系统读 1 字节花费的代价（纳秒数）
  • Lw - 往本地文件系统写 1 字节花费的代价（纳秒数）
  • NEt - 在网络上任意两个节点传输 1 个字节的代价（纳秒数）
  • T® - 关系 R 记录数
  • Tsz - 记录的平均大小
  • V(R, a) - 关系 R 中 属性 a 不同值的数量。
  • CPUc - 做一次比较的 cpu 代价（纳秒数）

代价计算假设

  1. 磁盘, HDFS, 网络读写比其他代价更高
  2. 计算 I/O 代价时，不考虑硬件的类型，一次 IO 操作的数据大小，顺序读写还是随机读写，磁盘一个 block 的大小，存储分布情况。
  • 所有的记录数大小一样。
  • 不同服务器之间做 shuffle 时，不考虑部分下游 task 在本机时不用传输数据的情况。
  • 对应 CPU 代价，仅考虑比较代价。假定一次比较花费 1 纳秒。
  • 不考虑 TEZ 容器重用。
  • HDFS 读的代价是本地读代价的 1.5 倍。HDFS 写的代价是本地写代价的 10 倍。

代价的变量设置（可以通过 Hive 参数调整）

  • CPUc = 1 纳秒
  • NEt = 150 * CPUc 纳秒
  • Lw = 4 * NEt
  • Lr = 4 * NEt
  • Hw = 10 * Lw
  • Hr = 1.5 * Lr

各种操作的代价

参考 HiveOnTezCostModel.java

Table Scan 的代价

T® = 记录数
Tsz = 记录平均大小
V(R, a) = 从 meta 获取
CPU Usage = 0
IO Usage = Hr * T® * Tsz. (从 HDFS 读一字节的开销 * 记录数 * 平均记录大小)

Filter

CPU Usage = T® * CPUc
IO Usage = 0

Select

CPU Usage = 0
IO Usage = 0

Common Join

R1 join R2
CPU Usage = 每张表的排序代价 + 两个排序流的 merge 代价 = [(T(R1) * Log(T(R1) * CPUc + T(R2) * Log(T(R2) * CPUc) ] + [(T(R1) + T(R2) ) * CPUc]

IO Usage = Map 写 shuffle 数据到本地文件系统，Map 从本地文件系统读 shuffle 数据， shuffle 数据 通过网络从 Map 服务器传输到 reduce 所在的服务器 = Lw * (T(R1) * Tsz1 + T(R2) * Tsz2) + Lr * (T(R1) * Tsz1 + T(R2) * Tsz2) + NEt * (T(R1) * Tsz1 + T(R2) * Tsz2)

Map Join

R1 JOIN R2，R1是小表，R2 是大表
CPU Usage = 小表构建 HashTable 开销 + 大表 Join 的开销 = T(R1) + T(R2） * CPUc

IO Usage = 传输一次小表的代价 * Map 的数量 = NEt * (T(R1) * Tsz1) * Map 的数量
假定大表数据在本地，不需要传输。存算分离场景是否也要计算大表 IO 开销？

Bucket Map Join

R1 JOIN R2 on R1.c1 = R2.c2。R1 按 c1 bucket，R2 按c2 bucket。
一张表的 bucket 的数量是另一张的整数倍。

CPU Usage = 小表构建 HashTable 开销 + 大表 Join 的开销 = T(R1) * CPUc + T(R2） * CPUc
IO Usage 和 Map Join 一样

SMB JOIN（Sort Merge Bucket Join）

数据都有序，小表不需要构建 Hash Table。
CPU Usage = （T(R1) + T(R2)) * CPUc
IO Usage 和 Map Join 一样

Skew Join

查询重写为两个 join 的结果 union 在一起。计算各自 join 的代价

Distinct/ Group by

CPU Usage = 排序的代价 + 分组的代价 = （T® * log T®) * CPUc