最近经常有优化sql的任务，但是自己能力有限，只能凭经验去优化，现整理加学习一波，也欢迎各位学习和讨论。

我们经常用hivesql 的模型就是 join.如下。

insert overwrite table a select * from b left join c

这里面发生了什么，执行流程是什么，为什么有的insert要几十分钟有的只要几分钟。

CREATE TABLE cc_test.multi_join
(
id int,
name string,
desc string
)
stored as orc;

--造1000w条数据 文件大小为300M

insert overwrite table cc_test.multi_join
select if(row_number() over ()<1000,1,row_number() over ()),
'cclovezbf'||(row_number() over ()),
repeat('desc',1000)||(row_number() over ())
from (select explode(split(space(10000000),'')) t)t

已知上面的数据为1000w条

其中 id=1的有1000条 其余id=1000到1000w的依次分布

1.explain 和 set

所有的优化和学习离不开explain和set

set分 set 和set -v 两者差别不清楚，但是有些设置参数需要自己查。

explain就是解析sql的执行顺序和逻辑。

2.

insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join

执行过程

这里为啥有2个map？为啥reduce又只有1个呢？

3.distribute by

explain
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join
distribute by round(rand()*30)

而且大家注意到没有 上面的31个文件为什么有2个5.6M，其他的是11.1M，为什么这么特殊？不是平均分配吗？有人告诉过你这个点吗？我来为你揭秘，这个就是纯属数学概率问题。
已知 rand=0-0.99 例如我们rand()4 本意是想 数值从1到4或者 0到4平均分配。rand4=0-3.99

round(rand*4）最终(0-0.49)=0 (0.5-1.49)=1 (1.5-2.49)=2 (2.5-3.49)=3 (3.5-3.99)=4 看到没

最终=0和4的概率会明显小于 =123的概率

其实正确的做法是int(rand*4) 这样分布的概率就会平均

4.reduce的个数

set mapred.reduce.tasks=-1;
set hive.exec.reducers.bytes.per.reducer=67108864 ##默认64M

set mapred.reduce.tasks=40;
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join
distribute by round(rand()*30);

此时大家猜一猜文件数是40还是30呢？答案是22个。

通过文件的序号可以看到这里应该是有类似merge操作的。此处暂且留下疑问

那么么我们将reudce个数改小呢？

set mapred.reduce.tasks=5;
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join
distribute by round(rand()*30);

可以看到文件数为5

说明该参数set mapred.reduce.tasks与生成的文件数有关，注意不是最终文件数。

再说说为什么reduce=40 distribute30 生成22个文件，根据我测试

reduce=100 distribute30 生成25个文件

reduce=300 distribute30 生成27个文件

reduce=301 distribute30 生成25个文件

reduce=500 distribute30 生成27个文件

文件数和reduce个数好像又成正比，但是reduce:300增加到301的时候文件数为什么变少了？其实聪明的小伙伴此时应该都想到了，可能就是取模。

简单的来说 map阶段获取的数据 在distrbue分为30份为data1/data2/....data30

reduce=300,data1/2/3/4对hash(300)取模后=1，data5到30分布在分区2-27，最终27个文件

reduce=300,data1/2/3/4/5/6对hash(300)取模后=1，data7到30分布到分区2-25，最终25个文件

验证下

reduce=15 distribute30 生成12个文件

reduce=14 distribute30 生成14个文件

reduce=13 distribute30 生成9个文件

这说明reduce=13时运气不好 取模后的值大多都在一个分区 如上，都在1/2/6/12号分区，没有取模后=3/5号分区的，所以文件数<reudce个数

至于reduce=5 30个文件大概率是会均分到5个reduce。

那么我们 怎么合理的使用这两个参数呢？

比如我们的目的是最终生成10个文件，数据尽量分散或者平均。

首先reduce=10毫无疑问，distributeby 如何设置呢？ 设置小了文件数可能<10，设置大了就会越分散，并且经过测试 distribute 不会产生额外的mr，只会在map阶段产出一个分区

由此可以得到结论：该参数会影响生成的最后文件，但是不是最终决定文件个数的决定性因数。

比如distribute by 和merge都会影响最后生成的文件个数。

题外话知道为什么该参数默认=-1吗？就是因为你有时候设置了具体的参数，但是结果数不一致，会导致你怀疑人生，所以reduce默认时按照大小生成reduce个数的。这里又涉及到一个知识点，reduce个数设置过大，会导致空转白白浪费资源。

5.map的个数

mapreduce.input.fileinputformat.split.minsize=1

mapreduce.input.fileinputformat.split.maxsize=256000000

细心的小伙伴在实验的时候肯定会发现 map数量少的时候跑的很慢，数量多的时候跑的很快。

那么map数量和什么有关呢？

1.文件数量， 一般来说文件数量=map数，注意这里说的一般来说。

2.文件的总大小。

我上面提到的两个参数又是什么意思呢？是说map阶段读取的文件大小最小是1最大时256M。

例如我们现在的数据为2个180M

1k<180M <256M 所以刚好使用2个Map

如果我们修改参数

set mapreduce.input.fileinputformat.split.minsize=64000000;
set mapreduce.input.fileinputformat.split.maxsize=64000000;

设置map读取的范围时64M 那么180M如何拆分呢？

注意这里很有意思用了4个map，最后输出文件为4，但是仔细看文件大小 两大121M两小59M

121+59M=180M 刚好等于之前没拆分的大小。

继续实验

5.多出来的reduce(统计信息)

set hive.stats.autogather=true;

set hive.stats.column.autogather=true;

细心的小伙伴可能会发现之前的执行过程有点奇怪。

map1：读取from 表数据 map1input=1000w output=1000w 没问题

reduce2：插入到insert表的过程 reduce读取map的数据，input=1000w也没问题，output=10?这个10是什么呢？暂时猜测是最终的文件数

reduce3：这个是什么呢？而且reduce3只有一个task，我们可是设置reduce=10了的呀？而且这个的output=0这又是什么呢 ？

这里就用到我们的explain了。

explain
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join_bak
distribute by int (rand()*3000);

看到这个大部分人就知道了，这个是hive默认的统计信息，意思就是当你覆盖表信息的时候，hive就要统计下，这个一般不会耗时很久，还是做个实验

可以看到reduce3没有了，这样就能更好的分析执行过程了。。

6.文件合并的优化

#小于该大小的就会被合并

set hive.merge.smallfiles.avgsize=16777216

文件合并的最后大小

set hive.merge.size.per.task=268435456
set hive.merge.sparkfiles=false
set hive.merge.tezfiles=false

//reudce阶段合并
set hive.merge.mapredfiles=false

//map阶段合并
set hive.merge.mapfiles=true

有时候我们的目录下面都是小文件，这样会可能会开启很多map任务

insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join_bak distribute by int (rand()*3000);
##为什么只有956个呢？ 因为3000 hash后也没有均匀分布

6000之后reduce的个数正好就是1009了。

set hive.exec.reducers.max=1009

已知我们读取的表数据有360M 文件数有1009个 那我们读取的时候会有多少个map呢？

insert overwrite table cc_test.multi_join select * from cc_test.multi_join ;

答案是25个？ 为什么是25个呢？ 这个14.4又是什么呢？所以我们考虑到的事情，比我们优秀的人更早就就考虑到了。下面的是默认参数

set hive.merge.mapfiles=true;

//小文件合并的阈值16M是不是和14.4很像呢？

set hive.merge.smallfiles.avgsize=16777216;

如何证实呢？set hive.merge.smallfiles.avgsize=33554432 这样是不会map会变为12或13呢？

发现还是25个，此时有点想不通了，是参数没起作用还是我理解错了？ 这个暂且不说。这问题留到下面讲。

先说ditribute by 这个参数可以让reduce个数更多，但是我们有时候真的需要这么多文件吗？这个对于namenode来说负担很大，还有reduce个数多了，map数也多了，真的好吗？

所以我们需要处理这些小文件，有人又说了 distributeby(rand*6) 这样就只有6个文件了，或者set reduce.task=6也可以。但是我们现在既想任务跑的快，又想文件少。

这个时候就要说

set hive.merge.tezfiles=true;

set hive.merge.mapredfiles=true; #默认关闭 我们此时打开

set hive.merge.smallfiles.avgsize=16777216; #文件小于这个参数的开始被merge

set hive.merge.size.per.task=268435456;

已知文件个数为1009 每个文件差不多在300K左右。

insert overwrite table cc_test.multi_join_bak select * from cc_test.multi_join_bak

可以看到mapreduce阶段完了之后 又多了一个file merge阶段，说明参数确实起作用了。

最后形成的文件大小也是接近于256M。

所以这个参数适用于小文件比较多的表，注意最后的merge。

再说说最开始的map数据为什么不变呢？

set mapreduce.input.fileinputformat.split.minsize=1;
set mapreduce.input.fileinputformat.split.maxsize=256000000;

set tez.grouping.min-size=16777216;
set tez.grouping.max-size=134217728;

hive.merge.smallfiles.avgsize=16777216;

hive.merge.mapfiles=true;

7.资源控制(TEZ)

set tez.grouping.min-size=16777216 ##16M

set tez.grouping.max-size=134217728 ## 128这个是我这边集群的

set tez.grouping.split-waves=1.7 #调整一个比例的，

set hive.merge.smallfiles.avgsize=16777216;
set hive.merge.mapfiles=true;

set mapreduce.input.fileinputformat.split.minsize=1;
set mapreduce.input.fileinputformat.split.maxsize=256000000;

上面第6项说到了 360M的1000个文件读取的map数是25个？而且设置set hive.merge.smallfiles.avgsize=32M没有任何效果 ，这个很正常，这个参数代表的是小于32M以下的文件会被合并为一个大文件放到一个MAP里，注意这里并没有说这些小文件合并为多大了就不合并了？ 1000个100k的小文件是合成100个map还是10个 还是1个，肯定是有参数限制了的。

set hive.merge.mapfiles=false;

insert overwrite table cc_test.multi_join select * from cc_test.multi_join ;

结果还是25个map，

此时就隐隐约约感觉有点奇怪了？我map阶段都不合并文件了？为啥还是这么多map呢？

答案就是 set tez.grouping.min-size=16777216=16M 这个参数规定了每个map的group里最少是16M，这里的16M是什么呢？就是上文说的14.4。

set tez.grouping.min-size=4194304; #4M

set hive.merge.mapfiles=true;

insert overwrite table cc_test.multi_join select * from cc_test.multi_join ;

可以看到map数量变了16M->4M，map数 25->98， 一个缩小4倍 一个增多4倍；

set tez.grouping.split-waves=1.7 # 这个参数经过测试是会影响map的数量的，但是影响不大，我以前看过源码稍微研究了下，有兴趣的自己看下

7.join优化之mapjoin

set hive.auto.convert.join=true #默认true
set hive.mapjoin.smalltable.filesize=25000000 #默认25M 注意是我这边集群默认
set hive.auto.convert.join.noconditionaltask=true #默认true
set hive.auto.convert.join.noconditionaltask.size=52428800 #50M

上面所有的示例都没有涉及到join优化，涉及到join优化先得说下mapjoin，mapjoin默认开启。具体有什么好处就不说了。但是我说一点，并不是所有的任务都是mapjoin好，我遇到过mr比mapjoin跑的快的。

未完待续。。。