24-Hive优化(下)

1. 分组聚合优化

1.1 优化说明

Hive 中未经优化的分组聚合，是通过一个 MapReduce Job 实现的。Map 端负责读取数据，并按照分组字段分区，通过 Shuffle，将数据发往 Reduce 端，各组数据在 Reduce 端完成最终的聚合运算。

Hive 对分组聚合的优化主要围绕着减少 Shuffle 数据量进行，具体做法是 map-side 聚合。

所谓 map-side 聚合，就是在 Map 端维护一个 hash table，利用其完成部分的聚合，然后将部分聚合的结果，按照分组字段分区，发送至 Reduce 端，完成最终的聚合。map-side 聚合能有效减少 Shuffle 的数据量，提高分组聚合运算的效率。

map-side 聚合相关的参数如下：

-- 启用 map-side 聚合（默认开启）
set hive.map.aggr=true;

-- 用于检测源表数据是否适合进行 map-side 聚合。检测的方法如下：
-- 先对若干条数据进行 map-side 聚合，若聚合后的条数和聚合前的条数比值小于该值，则认为该表适合进行 map-side 聚合；
-- 否则，认为该表数据不适合进行 map-side 聚合，后续数据便不再进行 map-side 聚合。
set hive.map.aggr.hash.min.reduction=0.5;

-- 用于检测源表是否适合 map-side 聚合的条数
set hive.groupby.mapaggr.checkinterval=100000;

-- map-side 聚合所用的 hash table 占用 map task 堆内存的最大比例，若超出该值则会对 hash table 进行一次 flush。
set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

1.2 优化案例

前置操作：

-- 订单表2kw，内存不设大点跑都跑不起来
set mapreduce.map.memory.mb=10240;
set mapreduce.reduce.memory.mb=10240;
-- yarn-site.xml 已经设置过了：
-- yarn.nodemanager.resource.memory-mb=14336
-- yarn.scheduler.maximum-allocation-mb=10240

测试用的分组 SQL 如下：

select product_id, count(*)
from order_detail
group by product_id;

开启优化前（因为默认开启，所以这里要手动关闭）：

set hive.map.aggr=false;

查看下执行计划：

然后执行上述 SQL，花了 40+ 秒。然后再开启该配置，再重新跑，还是 40+ 秒。这时候去看 MapTask 的执行日志。

100w商品/2000w订单 = 1/20 < 0.5，为啥 Hive 会认为不适合做？

Hive 不是随机抽样，是直接批量取每个分区文件开头 N 条，所以极易受「分组字段」在文件/表中分布的影响。也就是说，如果分组字段是顺序存在表中或是不够散开，很容易会影响 Hive 的判断。

所以，这里再设置 hive.map.aggr.hash.min.reduction=1 让 Hive 强制使用 Map 分组聚合。

然后再跑测试 SQL，执行时间 30+ 秒～ 此时再去看看 MapTask 执行日志

注意，看 MapTask 日志的时候有可能会出现 Map output records 大于分组字段的基数的情况。

这和参数 hive.map.aggr.hash.force.flush.memory.threshold 有关。map-side 聚合所用的 hash table 占用 map task 堆内存的最大比例，若超出该值则会对 hash table 进行一次 flush。

比如说，flush 之前聚合过 product_id=1101 的数据了，flush 完后，又统计到 prodcut_id=1101 的数据。

可以把 map 内存调大点，flush 的次数就少点了。

2. Join 优化

2.1 Join 算法概述

Hive 拥有多种 join 算法，包括 Common Join、Map Join、Bucket Map Join、Sort Merge Buckt Map Join 等，下面对每种 join 算法做简要说明。

a. Common Join

Common Join 是 Hive 中最稳定的 join 算法，其通过一个 MapReduce Job 完成一个 join 操作。Map 端负责读取 join 操作所需表的数据，并按照关联字段进行分区，通过 Shuffle，将其发送到 Reduce 端，相同 key 的数据在 Reduce 端完成最终的 join 操作。

举例说明：

1. 【Map 阶段】读取源表的数据，Map 输出时候以 join 条件中的列为 key，如果 join 有多个关联字段，则以这些关联字段的组合作为 key。Map 输出的 value 为 join 之后所关心（select 或 where 中需要用到）的列；同时在 value 中还会包含表的 Tag 信息，用于标明此 value 对应哪个表；按照 key 进行排序。
2. 【Shuffle 阶段】根据 key 的值进行 hash，并将 KV 按照 hash 结果推送至不同的 Reducer 中，这样确保两个表中相同的 key 位于同一个 Reducer 中。
3. 【Reduce 阶段】根据 key 的值完成 join 操作，期间通过 Tag 来识别不同表中的数据。

需要注意的是，SQL 语句中的 join 操作和执行计划中的 Common Join 任务并非一对一的关系，一个 SQL 语句中的相邻的且关联字段相同的多个 join 操作可以合并为一个 Common Join 任务。

例如：

select 
    a.val, 
    b.val, 
    c.val 
from a 
join b on (a.key = b.key1) 
join c on (c.key = b.key1)

上述 SQL 语句中两个 join 操作的关联字段均为 b 表的 key1 字段，则该语句中的两个 join 操作可由一个 Common Join 任务实现，也就是可通过一个 MR 任务实现。

select 
    a.val, 
    b.val, 
    c.val 
from a 
join b on (a.key = b.key1) 
join c on (c.key = b.key2)

上述 SQL 语句中的两个 join 操作关联字段各不相同，则该语句的两个 join 操作需要各自通过一个 Common Join 任务实现，也就是通过两个 MR 任务实现。

b. Map Join

笼统地说，Hive 中的 join 可分为 Common Join（Reduce 阶段完成 join）和 Map Join（Map 阶段完成 join）。

Map Join 算法可以通过两个只有 map 阶段的 Job 完成一个 join 操作，其适用场景为「大表 join 小表」。

若某 join 操作满足要求，则第 1 个 Job 会读取小表数据，将其制作为 hash table，并上传至 Hadoop 分布式缓存（本质上是上传至 HDFS）；第 2 个 Job 会先从分布式缓存中读取小表数据，并缓存在 Map Task 的内存中，然后扫描大表数据，这样在 map 端即可完成关联操作。

c. Bucket Map Join

Bucket Map Join 是对 Map Join 算法的改进，其打破了 Map Join 只适用于大表 join 小表的限制，可用于「大表 join 大表」的场景。

若能保证参与 join 的表均为分桶表，且关联字段为分桶字段，且其中一张表的分桶数量是另外一张表分桶数量的整数倍，就能保证参与 join 的两张表的分桶之间具有明确的关联关系，所以就可以在两表的分桶间进行 Map Join 操作了。这样一来，第二个 Job 的 Map 端就无需再缓存小表的全表数据了，而只需缓存其所需的分桶即可。

大表分几个桶，就启动多少个 Mapper，每个 Mapper 拉取对应的小表分桶进行缓存，然后扫描大表分桶数据进行 join。

d. SMB Map Join

Sort Merge Bucket Map Join 基于 Bucket Map Join。

SMB Map Join 要求，参与 join 的表均为分桶表，且需保证分桶内的数据是有序的，且分桶字段、排序字段和关联字段为相同字段，且其中一张表的分桶数量是另外一张表分桶数量的整数倍。

SMB Map Join 同 Bucket Join 一样，同样是利用两表各分桶之间的关联关系，在分桶之间进行 join 操作。不同的是分桶之间的 join 操作的实现原理：Bucket Map Join 两个分桶之间的 join 实现原理为 Hash Join 算法；SMB Map Join 两个分桶之间的 join 实现原理为 Sort Merge Join 算法。

Hash Join 和 Sort Merge Join 均为关系型数据库中常见的 join 实现算法。Hash Join 的原理相对简单，就是对参与 join 的一张表构建 hash table，然后扫描另外一张表，然后进行逐行匹配。Sort Merge Join 需要在两张按照关联字段排好序的表中进行。

Hive 中的 SMB Map Join 就是对两个分桶的数据按照上述思路进行 join 操作的。可以看出 SMB Map Join 与 Bucket Map Join 相比，在进行 join 操作时，Map 端是无须对整个 Bucket 构建 hash table，也无须在 Map 端缓存整个 Bucket 数据的，每个 Mapper 只需要按顺序逐个 key 读取两个分桶的数据进行 join 即可。

2.2 Map Join

a. 优化说明

Map Join 有两种触发方式，一种是用户在 SQL 语句中增加 hint 提示，另外一种是 Hive 优化器根据参与 join 表的数据量大小自动触发。

一、Hint 提示

用户可通过如下方式，指定通过 Map Join 算法，并且 ta 将作为 Map Join 中的小表。这种方式已经过时，不推荐使用。

select /*+ mapjoin(ta) */
    ta.id,
    tb.id
from table_a ta
join table_b tb
on ta.id=tb.id;

二、自动触发

Hive 在编译 SQL 语句阶段，起初所有的 join 操作均采用 Common Join 算法实现。

之后在物理优化阶段，Hive 会根据每个 Common Join Task 所需表的大小判断该 Common Join Task 是否能够转换为 Map Join Task，若满足要求，便将 Common Join Task 自动转换为 Map Join Task。

但有些 Common Join Task 所需的表大小，在 SQL 的编译阶段是未知的（例如对子查询进行 join 操作），所以这种 Common Join Task 是否能转换成 Map Join Task 在编译阶是无法确定的。

针对这种情况，Hive 会在编译阶段生成一个条件任务（Conditional Task），其下会包含一个「计划列表」，计划列表中包含转换后的 Map Join Task 以及原有的 Common Join Task。最终具体采用哪个计划，是在运行时决定的。

大致思路如下图所示：

Map Join Task 自动转换的具体判断逻辑如下图所示（基于 Common Join Task，不是 SQL 中的 join 语句）：

（1）寻找大表候选人

结合 join 方式分析，以 a LEFT JOIN b 为例：

• 缓存b｜扫描a => 能不能 join 上 a 的记录都会返回，只不过如果关联不上 b 相关的字段返回 null；
• 缓存a｜扫描b => 能 join 上返回；join 不上不返回，那么到最后，返回来的数据只是 a 和 b 能关联上的。

小结：

• LEFT JOIN 的左表必须是大表
• RIGHT JOIN 的右表必须是大表
• INNER JOIN 左表或右表均可以作为大表
• FULL JOIN 不能使用 Map Join（因为全外联接要返回两表各自所有的数据，所以只能 Common Join）

（2）noconditionaltask：无条件转 Map Join

｜=> false

分别尝试以每个大表候选人作为大表生成 Map Join Task。比如一个 Common Join Task（三表关联）可生成的 Map Join Task 可以有如下 3 种：

1. a做大表、b&c缓存
2. b做大表、a&c缓存
3. c做大表、a&b缓存

再用有限的信息排除不可能的执行计划。比如某大表候选人大小已知（这个判断其实是多余的）且要被缓存的表大小已经超过了设置的小表阈值，这种肯定不行，就不生成对应的 Map Join Task。

筛选过后，如果不剩 Map Join Task 了，则执行 Common Join Task。若还有 Map Join Task 保留下来，就生成 Conditional Task，将保留下来的 Map Join Task 和原有的 Common Join Task 加入其任务列表，最终执行计划在运行时决定。

｜=> true

大表之外的小表大小均已知，且小表总大小小于设置的阈值 => 生成最优 Map Join Task

如果不满足这个条件，还是会去生成 Conditional Task，回到上一个逻辑。

生成最优 Map Join Task 后，以三表关联为例，如果三表关联字段不同，会生成两个 Common Join Task（也就是说上图的逻辑会走两遍），由此引出最后一个优化点：若子任务也是 Map Join，且当前任务和子任务的所有小表大小均已知且小于阈值，可做任务合并。

（3）图中涉及到的参数

-- 开启'Map Join自动转换'
set hive.auto.convert.join=true;

-- 有条件转Map Join时的小表之和阈值
-- 一个Common Join Operator转为Map Join Operator的判断条件，若该Common Join相关的表中，存在N-1张表的已知大小总和<=该值，
-- 则生成一个Map Join Task，此时可能存在多种N-1张表的组合均满足该条件，则Hive会为每种满足条件的组合均生成一个Map Join Task，
-- 同时还会保留原有的Common Join Task作为BackUp Task，实际运行时优先执行Map Join Task，若不能执行成功，则启动BackUp Task。
set hive.mapjoin.smalltable.filesize=250000;

-- 开启'无条件转Map Join'
set hive.auto.convert.join.noconditionaltask=true;

-- 无条件转Map Join时的小表之和阈值
-- 若一个Common Join operator相关的表中，存在N-1张表的大小总和<=该值，此时Hive便不会再为每种N-1张表的组合
-- 均生成Map Join Task，同时也不会保留Common Join作为后备计划。而是只生成一个最优的Map Join Task。
set hive.auto.convert.join.noconditionaltask.size=10000000;

b. 优化案例

示例 SQL：

explain
select *
from order_detail od
         join product_info product on od.product_id = product.id
         join province_info province on od.province_id = province.id;

可使用 desc formatted <table_name> 查看表信息。

表名	大小
order_detail	1176009934（约 1122M）
product_info	25285707（约 24M）
province_info	369（约 0.36K）

首先 set hive.auto.convert.join=false 然后查看 SQL 执行计划。

上述 SQL 语句共有三张表进行两次 join 操作，且两次 join 操作的关联字段不同。故优化前的执行计划应该包含两个 Common Join operator，也就是由两个 MapReduce 任务实现。

三张表中，product_info 和 province_info 数据量较小，可考虑将其作为小表，进行 Map Join 优化。根据前文 Common Join Task 转 Map Join Task 的判断逻辑图，可得出以下三种优化方案：

（1）如下配置可保证将两个 Common Join operator 均可转为 Map Join operator，并保留 Common Join 作为后备计划，保证计算任务的稳定。

-- 启用Map Join自动转换
set hive.auto.convert.join=true;
-- 不使用'无条件转Map Join'
set hive.auto.convert.join.noconditionaltask=false;
-- 调整filesize使其 >= product_info
set hive.mapjoin.smalltable.filesize=25285707;

调整完的执行计划如下图：

实际在本地去跑这个 SQL，查看 YARN 发现跑的 s8 和 s2：

（2）如下配置可直接将两个 Common Join operator 转为两个 Map Join operator，并且由于两个 Map Join operator 的小表大小之和小于等于hive.auto.convert.join.noconditionaltask.size，故两个 Map Join operator 任务可合并为同一个。这个方案计算效率最高，但需要的内存也是最多的。

-- 启用Map Join自动转换
set hive.auto.convert.join=true;
-- 无条件转Map Join
set hive.auto.convert.join.noconditionaltask=true;
-- 调整size使其 >= (product_info.size + province_info.size)
set hive.auto.convert.join.noconditionaltask.size=25286076;

调整完的执行计划如下图：

（3）这样可直接将两个 Common Join operator 转为 Map Join operator，但不会将两个 Map Join 的任务合并。该方案计算效率比方案二低，但需要的内存也更少。

-- 启用Map Join自动转换
set hive.auto.convert.join=true;
-- 无条件转Map Join
set hive.auto.convert.join.noconditionaltask=true;
-- 调整size使其 >= product_info
set hive.auto.convert.join.noconditionaltask.size=25285707;

调整完的执行计划如下图：

2.3 Bucket Map Join

a. 优化说明

Bucket Map Join 不支持自动转换，发须通过用户在 SQL 语句中提供如下 Hint 提示并配置如下相关参数，方可使用。

-- Hint提示
select /*+ mapjoin(ta) */
    ta.id,
    tb.id
from table_a ta
join table_b tb on ta.id=tb.id;

-- 相关参数
-- 关闭cbo优化，cbo会导致hint信息被忽略
set hive.cbo.enable=false;
-- Map Join Hint默认会被忽略(因为已经过时)，需将如下参数设置为false
set hive.ignore.mapjoin.hint=false;
-- 启用Bucket Map Join优化功能
set hive.optimize.bucketmapjoin = true;

b. 优化案例

示例 SQL：

select
    *
from(
    select
        *
    from order_detail
    where dt='2020-06-14'
)od
join(
    select
        *
    from payment_detail
    where dt='2020-06-14'
)pd
on od.id=pd.order_detail_id;

上述 SQL 语句共有两张表一次 join 操作，故优化前的执行计划应包含一个 Common Join Task，通过一个 MapReduce Job 实现。执行计划如下图所示：

经分析，参与 join 的两张表，数据量如下。

表名	大小
order_detail	1176009934（约 1122M）
payment_detail	334198480（约 319M）

两张表都相对较大，若采用普通的 Map Join 算法，则 Map 端需要较多的内存来缓存数据，当然可以选择为 Map 端分配更多的内存，来保证任务运行成功。但是 Map 端的内存不可能无上限的分配，所以当参与 Join 的表数据量均过大时，就可以考虑采用 Bucket Map Join 算法。

下面演示如何使用 Bucket Map Join。

首先需要依据源表创建两个分桶表，order_detail 建议分 16 个 bucket，payment_detail 建议分 8 个 bucket，注意分桶个数的倍数关系以及分桶字段。

-- 订单表
drop table if exists order_detail_bucketed;
create table order_detail_bucketed(
    id           string comment '订单id',
    user_id      string comment '用户id',
    product_id   string comment '商品id',
    province_id  string comment '省份id',
    create_time  string comment '下单时间',
    product_num  int comment '商品件数',
    total_amount decimal(16, 2) comment '下单金额'
)
clustered by (id) into 16 buckets
row format delimited fields terminated by '\t';

-- 支付表
drop table if exists payment_detail_bucketed;
create table payment_detail_bucketed(
    id              string comment '支付id',
    order_detail_id string comment '订单明细id',
    user_id         string comment '用户id',
    payment_time    string comment '支付时间',
    total_amount    decimal(16, 2) comment '支付金额'
)
clustered by (order_detail_id) into 8 buckets
row format delimited fields terminated by '\t';

然后向两个分桶表导入数据：

-- 订单表
insert overwrite table order_detail_bucketed
select
    id,
    user_id,
    product_id,
    province_id,
    create_time,
    product_num,
    total_amount   
from order_detail
where dt='2020-06-14';

-- 支付表
insert overwrite table payment_detail_bucketed
select
    id,
    order_detail_id,
    user_id,
    payment_time,
    total_amount
from payment_detail
where dt='2020-06-14';

设置以下参数：

set hive.cbo.enable=false;
set hive.ignore.mapjoin.hint=false;
set hive.optimize.bucketmapjoin = true;

最后重写 SQL 语句：

select /*+ mapjoin(pd) */
    *
from order_detail_bucketed od
join payment_detail_bucketed pd on od.id = pd.order_detail_id;

优化后的执行计划如图所示：

需要注意的是，Bucket Map Join 的执行计划的基本信息和普通的 Map Join 无异，若想看到差异，可执行如下语句查看执行计划的详细信息。详细执行计划中，如在 Map Join Operator 中看到 “BucketMapJoin: true”，则表明使用的 Join 算法为 Bucket Map Join。

explain extended select /*+ mapjoin(pd) */
    *
from order_detail_bucketed od
join payment_detail_bucketed pd on od.id = pd.order_detail_id;

2.4 Sort Merge Bucket Map Join

a. 优化说明

Sort Merge Bucket Map Join 有两种触发方式，包括 Hint 提示和自动转换。Hint 提示已过时，不推荐使用。下面是自动转换的相关参数：

-- 启动Sort Merge Bucket Map Join优化
set hive.optimize.bucketmapjoin.sortedmerge=true;
-- 使用自动转换SMB Join
set hive.auto.convert.sortmerge.join=true;

b. 优化案例

示例 SQL 同 Bucket Map Join：

select
    *
from(
    select
        *
    from order_detail
    where dt='2020-06-14'
)od
join(
    select
        *
    from payment_detail
    where dt='2020-06-14'
)pd
on od.id=pd.order_detail_id;

上述 SQL 语句共有两张表一次 join 操作，故优化前的执行计划应包含一个 Common Join Task，通过一个 MapReduce Job 实现。

优化思路除了可以考虑采用 Bucket Map Join 算法，还可以考虑 SMB Join。相较于 Bucket Map Join，SMB Map Join 对分桶大小是没有要求的。下面演示如何使用 SMB Map Join。

首先需要依据源表创建两个的有序的分桶表，order_detail 建议分 16 个 bucket，payment_detail 建议分 8 个 bucket，注意分桶个数的倍数关系以及分桶字段和排序字段。

-- 订单表
hive (default)> 
drop table if exists order_detail_sorted_bucketed;
create table order_detail_sorted_bucketed(
    id           string comment '订单id',
    user_id      string comment '用户id',
    product_id   string comment '商品id',
    province_id  string comment '省份id',
    create_time  string comment '下单时间',
    product_num  int comment '商品件数',
    total_amount decimal(16, 2) comment '下单金额'
)
clustered by (id) sorted by(id) into 16 buckets
row format delimited fields terminated by '\t';

-- 支付表
hive (default)> 
drop table if exists payment_detail_sorted_bucketed;
create table payment_detail_sorted_bucketed(
    id              string comment '支付id',
    order_detail_id string comment '订单明细id',
    user_id         string comment '用户id',
    payment_time    string comment '支付时间',
    total_amount    decimal(16, 2) comment '支付金额'
)
clustered by (order_detail_id) sorted by(order_detail_id) into 8 buckets
row format delimited fields terminated by '\t';

然后向两个分桶表导入数据（略）。

设置以下参数：

-- 启动Sort Merge Bucket Map Join优化
set hive.optimize.bucketmapjoin.sortedmerge=true;
-- 使用自动转换SMB Join
set hive.auto.convert.sortmerge.join=true;

最后在重写 SQL 语句：

select
    *
from order_detail_sorted_bucketed od
join payment_detail_sorted_bucketed pd
on od.id = pd.order_detail_id;

优化后的执行计如图所示：

3. 数据倾斜优化

数据倾斜问题，通常是指参与计算的数据分布不均，即某个 key 或某些 key 的数据量远超其他 key，导致在 Shuffle 阶段，大量相同 key 的数据被发往同一个 Reducer，进而导致该 Reducer 所需的时间远超其他 Reducer，成为整个任务的瓶颈。

Hive 中的数据倾斜常出现在「分组聚合」和「Join 操作」的场景中，下面分别介绍在这两种场景下的优化思路。

3.1 分组聚合导致的数据倾斜

a. 优化说明

前文提到过，Hive 中未经优化的分组聚合，是通过一个 MapReduce Job 实现的。Map 端负责读取数据，并按照分组字段分区，通过 Shuffle，将数据发往 Reduce 端，各组数据在 Reduce 端完成最终的聚合运算。

如果 group by 分组字段的值分布不均，就可能导致大量相同的 key 进入同一 Reduce，从而导致数据倾斜问题。

由分组聚合导致的数据倾斜问题，有以下两种解决思路：

（1）Map-Side 聚合

开启 Map-Side 聚合后，数据会现在 Map 端完成部分聚合工作。这样一来即便原始数据是倾斜的，经过 Map 端的初步聚合后，发往 Reduce 端的数据也就不再倾斜了。最佳状态下，Map 端聚合能完全屏蔽数据倾斜问题。

相关参数如下：

-- 启用Map-Side聚合
set hive.map.aggr=true;

-- 用于检测源表数据是否适合进行Map-Side聚合。
-- 检测的方法是：先对若干条数据进行Map-Side聚合，若聚合后的条数和聚合前的条数比值小于该值，则认为该表适合进行Map-Side聚合；
-- 否则，认为该表数据不适合进行Map-Side聚合，后续数据便不再进行Map-Side聚合。
set hive.map.aggr.hash.min.reduction=0.5;

-- 用于检测源表是否适合Map-Side聚合的条数
set hive.groupby.mapaggr.checkinterval=100000;

-- Map-Side聚合所用的hash table，占用map task堆内存的最大比例，若超出该值，则会对hash table进行一次flush。
set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

（2）Skew-GroupBy 优化

原理是启动两个 MR 任务，第一个 MR 按照「随机数」分区，将数据分散发送到 Reduce，完成部分聚合，第二个 MR 按照分组字段分区，完成最终聚合。

相关参数如下：

-- 启用分组聚合数据倾斜优化
set hive.groupby.skewindata=true;

b. 优化案例

示例 SQL：

select
    province_id,
    count(*)
from order_detail
group by province_id;

该表数据中的 province_id 字段是存在倾斜的，若不经过优化，通过观察任务的执行过程，是能够看出数据倾斜现象的。

需要注意的是，Hive 中的 Map-Side 聚合是默认开启的，若想看到数据倾斜的现象，需要先将 hive.map.aggr 设置为 false。

（1）使用「Map-Side 聚合」优化，设置如下参数：

-- 启用 Map-Side
set hive.map.aggr=true;
-- 关闭 Skew-GroupBy
set hive.groupby.skewindata=false;

很明显可以看到开启 Map-Side 聚合后，Reduce 数据不再倾斜。

（2）使用「Skew-GroupBy」优化，设置如下参数：

-- 启用 Skew-GroupBy
set hive.groupby.skewindata=true;
-- 关闭 Map-Side
set hive.map.aggr=false;

开启 Skew-GroupBy 后的执行计划如下图所示：

开启 Skew-GroupBy 优化后，可以很明显看到该 SQL 执行在 Yarn 上启动了两个 MR 任务，第一个 MR 打散数据，第二个 MR 按照打散后的数据进行分组聚合。

3.2 Join 导致的数据倾斜

a. 优化说明

前文提到过，未经优化的 join 操作，默认是使用 Common Join 算法，也就是通过一个 MapReduce Job 完成计算。Map 端负责读取 join 操作所需表的数据，并按照关联字段进行分区，通过 Shuffle 将其发送到 Reduce 端，相同 key 的数据在 Reduce 端完成最终的 join 操作。

如果关联字段的值分布不均，就可能导致大量相同的 key 进入同一 Reduce，从而导致数据倾斜问题。

由 Join 导致的数据倾斜问题，有如下三种解决方案：

（1）Map Join

使用 Map Join 算法，join 操作仅在 Map 端就能完成，没有 Shuffle 操作，没有 Reduce 阶段，自然不会产生 Reduce 端的数据倾斜。

该方案适用于大表 join 小表时发生数据倾斜的场景。

相关参数如下：

set hive.auto.convert.join=true;
set hive.mapjoin.smalltable.filesize=250000;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000000;

（2）Skew Join

Skew Join 的原理是，为倾斜的大 key 单独启动一个 Map Join 任务进行计算，其余 key 进行正常的 Common Join。

相关参数如下：

-- 启用 Skew Join 优化
set hive.optimize.skewjoin=true;
-- 触发 Skew Join 的阈值，若某个 key 的行数超过该参数值则会触发
set hive.skewjoin.key=100000;

这种方案对参与 join 的源表大小没有要求，但是对两表中倾斜的 key 的数据量有要求，要求一张表中的倾斜 key 的数据量比较小（方便走 Map Join）。

（3）调整 SQL 语句

若参与 join 的两表均为大表，其中一张表的数据是倾斜的，此时也可通过以下方式对 SQL 语句进行相应的调整。

假设原始 SQL 语句如下：AB两表均为大表，且其中一张表的数据是倾斜的。

select
    *
from A
join B
on A.id=B.id;

未经优化的大表和大表 join：

图中 1001 为倾斜的大 key，可以看到，其被发往了同一个 Reduce 进行处理。

调整 SQL 语句如下：

select
    *
from (
    select
        concat(id, '_', cast(rand()*2 as int)) id, -- 打散操作
        value
    from A
) ta
join (
    select
        concat(id, '_', 0) id,
        value
    from B
    union all                                      -- 扩容操作
    select
        concat(id, '_', 1) id,
        value
    from B
) tb
on ta.id=tb.id;

优化后的大表和大表 join：

b. 优化案例

示例 SQL：

select
    *
from order_detail od
join province_info pi
on od.province_id=pi.id;

order_detail 中的 province_id 字段是存在倾斜的，若不经过优化，通过观察任务的执行过程，是能够看出数据倾斜现象的。

需要注意的是，Hive 中的 Map Join 自动转换是默认开启的，若想看到数据倾斜的现象，需要先将 hive.auto.convert.join 设置为 false。

（1）使用 Map Join 进行优化

设置如下参数：

-- 启用 Map Join
set hive.auto.convert.join=true;
-- 关闭 Skew Join
set hive.optimize.skewjoin=false;

可以很明显看到开启 Map Join 以后，MR 任务只有 Map 阶段，没有 Reduce 阶段，自然也就不会有数据倾斜发生。

（2）使用 Skew Join 进行优化

设置如下参数：

-- 启动 Skew Join
set hive.optimize.skewjoin=true;
-- 关闭 Map Join
set hive.auto.convert.join=false;

开启 Skew Join 后，使用 explain 可以很明显看到执行计划如下图所示，说明 Skew Join 生效，任务既有 Common Join，又有部分 key 走了 Map Join（Stage5&3）。

并且该 SQL 在 Yarn 上最终启动了两个 MR 任务，而且第二个任务只有 Map 没有 Reduce 阶段，说明第二个任务是对倾斜的 key 进行了 Map Join。

4. 其他优化

4.1 CBO 优化

a. 优化说明

CBO 是指 Cost based Optimizer，即基于计算成本的优化。

在 Hive 中，计算成本模型考虑到了：数据的行数、CPU、本地IO、HDFS IO、网络IO等方面。

Hive 会计算同一 SQL 的不同执行计划的计算成本，并选出成本最低的执行计划。目前 CBO 在 Hive 的 MR 引擎下主要用于 join 的优化，例如多表 join 的 join 顺序。

-- 是否启用CBO优化 
set hive.cbo.enable=true;

b. 优化案例

示例 SQL：

select
    *
from order_detail od
join product_info product on od.product_id=product.id
join province_info province on od.province_id=province.id;

关闭 CBO 优化：

-- 关闭CBO优化 
set hive.cbo.enable=false;

-- 为了测试效果更加直观，关闭Map Join自动转换
set hive.auto.convert.join=false;

根据执行计划，可以看出三张表的 join 顺序和 SQL 书写顺序一致：

开启 CBO 优化：

--开启cbo优化 
set hive.cbo.enable=true;
--为了测试效果更加直观，关闭map join自动转换
set hive.auto.convert.join=false;

执行计划的 join 顺序如下：

根据上述案例可以看出，CBO 优化对于执行计划中 join 顺序是有影响的，其之所以会将 province_info 的 join 顺序提前，是因为 province_info 的数据量较小，将其提前会有更大的概率使得中间结果的数据量变小，从而使整个计算任务的数据量减小，也就是使计算成本变小。

4.2 谓词下推

a. 优化说明

谓词下推（Predicate Pushdown）是指：尽量将过滤操作前移，以减少后续计算步骤的数据量。

-- 是否启动谓词下推(PPD)优化
set hive.optimize.ppd = true;

CBO 优化也会完成一部分的谓词下推优化工作，因为在执行计划中，谓词越靠前，整个计划的计算成本就会越低。

b. 优化案例

示例 SQL：

select
    *
from order_detail
join province_info
where order_detail.province_id='2';

关闭谓词下推优化：

-- 是否启动谓词下推(PPD)优化
set hive.optimize.ppd = false;

-- 为了测试效果更加直观，关闭CBO优化
set hive.cbo.enable=false;

通过执行计划可以看到，过滤操作位于执行计划中的 join 操作之后：

开启谓词下推优化：

-- 是否启动谓词下推(PPD)优化
set hive.optimize.ppd = false;
-- 为了测试效果更加直观，关闭CBO优化
set hive.cbo.enable=false;

通过执行计划可以看出，过滤操作位于执行计划中的 join 操作之前：

把示例 SQL 手动做过滤，得出的执行计划和上图一致。

select
    *
from
(
  select * from order_detail where province_id='2';
) t1
join province_info;

注意，在 Hive 里不要担心因为子查询而影响性能，这不是 DBMS。

4.3 Fetch 抓取

Fetch 抓取是指：Hive 中对某些情况的查询可以不必使用 MapReduce 计算。

例如 select * from emp 在这种情况下，Hive 可以简单地读取 emp 对应的存储目录下的文件，然后输出查询结果到控制台。

-- 是否在特定场景转换为 fetch 任务
-- > none           表示不转换
-- > minimal    表示支持select *、分区字段过滤、limit等
-- > more           表示支持select 任意字段、包括函数、过滤、limit等
set hive.fetch.task.conversion=more;

4.4 本地模式

使用 Hive 的过程中，有一些数据量不大的表也会转换为 MapReduce 处理，提交到集群时，需要申请资源，等待资源分配，启动 JVM 进程，再运行 Task，一系列的过程比较繁琐，本身数据量并不大，提交到 YARN 运行返回会导致性能较差的问题。

Hive 为了解决这个问题，延用了 MapReduce 中的设计，提供本地计算模式，允许程序不提交给 YARN，直接在本地运行，以便于提高小数据量程序的性能。

-- 开启自动转换为本地模式
set hive.exec.mode.local.auto=true;  

-- 设置local MapReduce的最大输入数据量，当输入数据量小于这个值时采用local MapReduce的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;

-- 设置local MapReduce的最大输入文件个数，当输入文件个数小于这个值时采用local MapReduce的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

Hive 为了避免大数据量的计算也使用本地模式导致性能差的问题，所以对本地模式做了以下限制，如果以下任意一个条件不满足，那么即使开启了本地模式，将依旧会提交给 YARN 集群运行。

4.5 并行执行

Hive 会将一个 SQL 转化成一个或者多个 Stage，每个 Stage 对应一个 MR Job。默认情况下，Hive 同时只会执行一个 Stage。

某些 SQL 会包含多个 Stage，有时候 Stage 彼此之间有依赖关系，只能挨个执行，但是在一些别的场景下，很多的 Stage 之间是没有依赖关系的，但是 Hive 依旧默认挨个执行每个 Stage，这样会导致性能非常差。

我们可以通过修改参数，开启并行执行，当多个 Stage 之间没有依赖关系时，允许多个 Stage 并行执行，提高性能。

-- 开启Stage并行化，默认为false
SET hive.exec.parallel=true;
-- 同一个SQL允许最大并行度，默认为8
SET hive.exec.parallel.thread.number=16; 

注意：线程数越多，程序运行速度越快，但同样更消耗 CPU 资源。

4.6 严格模式

Hive 可以通过设置某些参数防止危险操作：

（1）分区表不使用分区过滤

将 hive.strict.checks.no.partition.filter 设置为 true 时，对于分区表，除非 WHERE 语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。

进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。

（2）使用 ORDER BY 没有 LIMIT 过滤

将 hive.strict.checks.orderby.no.limit 设置为 true 时，对于使用了 ORDER BY 语句的查询，要求必须使用 LIMIT 语句。

因为 ORDER BY 为了执行排序过程会将所有的结果数据分发到同一个 Reduce 中进行处理，强制要求用户增加这个 LIMIT 语句可以防止 Reduce 额外执行很长一段时间（开启了 LIMIT 可以在数据进入到 Reduce 之前就减少一部分数据）。

（3）笛卡尔积

将 hive.strict.checks.cartesian.product 设置为 true 时，会限制笛卡尔积的查询。

对关系型数据库非常了解的用户可能期望在执行 JOIN 查询的时候不使用 ON 语句而是使用 WHERE 语句，这样关系数据库的执行优化器就可以高效地将 WHERE 语句转化成 ON 语句。不幸的是，Hive 并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。

4.7 JVM 重用

JVM 正常指代一个 Java 进程，Hadoop 默认使用派生的 JVM 来执行 MR，如果一个 MapReduce 程序中有 100 个 Map、10 个 Reduce，Hadoop 默认会为每个 Task 启动一个 JVM 来运行，那么就会启动 100 个 JVM 来运行 MapTask，在 JVM 启动时内存开销大，尤其是 Job 大数据量情况，如果单个 Task 数据量比较小，也会申请 JVM 资源，这就导致了资源紧张及浪费的情况。

为了解决上述问题，MapReduce 中提供了 JVM 重用机制来解决，JVM 重用可以使得 JVM 实例在同一个 Job 中重新使用 N 次，当一个 Task 运行结束以后，JVM 不会进行释放，而是继续供下一个 Task 运行，直到运行了 N 个 Task 以后，就会释放，N 的值可以在 Hadoop 的 mapred-site.xml 文件中进行配置，通常在 10~20 之间。

-- Hadoop3之前的配置，在mapred-site.xml中添加以下参数。Hadoop3中已不再支持该选项
mapreduce.job.jvm.numtasks=10