本文假定读者对执行计划，普通 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 查询字段下推
查询字段下推是指尽量在表扫描中查询需要的字段。
优点：
- 列存储的表可以只从文件里读取相应的列，提高读取速度。
- 可以减少参加计算的数据量，提高计算速度。
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 完成。
基于规则的优化考虑以下因素：
基于规则的优化考虑以下因素：
- CPU 使用量
- IO 使用量
- 计算产生的记录数（不同的算法，产生的中间计算结果的数量不相等）
- 记录平均大小
记录平均大小和记录数用来估计存储需要的内存，可以决定是否可以采用某些 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 代价（纳秒数）
代价计算假设
-
- 磁盘, HDFS, 网络读写比其他代价更高
-
- 计算 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
1780
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
