背景

同事写了这样一段HQL(涉及公司数据，表名由假名替换，语句与真实场景略有不同，但不影响分析)：

1. CREATE TABLE tmp AS 
2. SELECT 
3.        t1.exk, 
4.        t1.exv, 
5.        M.makename AS m_makename, 
6.        S.makename AS s_makename, 
7. FROM 
8.   (SELECT 
9.           exk, 
10.           exv 
11.    FROM xx.xxx_log 
12.    WHERE etl_dt = '2017-01-12' 
13.      AND exk IN ('xxID', 'yyID') ) t1 
14. LEFT JOIN xx.xxx_model_info M ON (M.modelid=t1.exv AND t1.exk='xxID') 
15. LEFT JOIN xx.xxx_style_info S ON (S.styleid=t1.exv AND t1.exk='yyID') 

任务运行过程中非常缓慢，同事反映说这个任务要跑一个多小时。初步问了下，xx.xxx_log表数据量在分区etl_dt = '2017-01-12'上大概1亿3000万，xx.xxx_model_info大概3000多，xx.xxx_style_info大概4万多。

分析

第一步，分析HQL语句着手

从同事提供的数据量上看，两个left join显然应该是mapjoin，因为数据量差距悬殊。当前只有HQL语句，所以优化第一步当然要从HQL语句本身出发，看HQL语句是否有不恰当的地方。

从语句上看，就是取三张表的数据，按条件进行join，最后创建并插入一张hive表。语句上看没什么问题。

那就来看执行计划吧~ 我们只看建表后面的SELECT语句，如下

1. STAGE DEPENDENCIES: 
2.   Stage-5 is a root stage 
3.   Stage-4 depends on stages: Stage-5 
4.   Stage-0 depends on stages: Stage-4 
5.  
6. STAGE PLANS: 
7.   Stage: Stage-5 
8.     Map Reduce Local Work 
9.       Alias -> Map Local Tables: 
10.         m  
11.           Fetch Operator 
12.             limit: -1 
13.         s  
14.           Fetch Operator 
15.             limit: -1 
16.       Alias -> Map Local Operator Tree: 
17.         m  
18.           TableScan 
19.             alias: m 
20.             Statistics: Num rows: 3118 Data size: 71714 Basic stats: COMPLETE Column stats: NONE 
21.             HashTable Sink Operator 
22.               filter predicates: 
23.                 0 {(_col0 = 'xxID')} {(_col0 = 'yyID')} 
24.                 1  
25.                 2  
26.               keys: 
27.                 0 UDFToDouble(_col1) (type: double) 
28.                 1 UDFToDouble(modelid) (type: double) 
29.                 2 UDFToDouble(styleid) (type: double) 
30.         s  
31.           TableScan 
32.             alias: s 
33.             Statistics: Num rows: 44482 Data size: 1023086 Basic stats: COMPLETE Column stats: NONE 
34.             HashTable Sink Operator 
35.               filter predicates: 
36.                 0 {(_col0 = 'xxID')} {(_col0 = 'yyID')} 
37.                 1  
38.                 2  
39.               keys: 
40.                 0 UDFToDouble(_col1) (type: double) 
41.                 1 UDFToDouble(modelid) (type: double) 
42.                 2 UDFToDouble(styleid) (type: double) 
43.  
44.   Stage: Stage-4 
45.     Map Reduce 
46.       Map Operator Tree: 
47.           TableScan 
48.             alias: xx.xxx_log 
49.             Statistics: Num rows: 136199308 Data size: 3268783392 Basic stats: COMPLETE Column stats: NONE 
50.             Filter Operator 
51.               predicate: ((exk) IN ('xxID', 'yyID')) (type: boolean) 
52.               Statistics: Num rows: 22699884 Data size: 544797216 Basic stats: COMPLETE Column stats: NONE 
53.               Select Operator 
54.                 expressions: exk (type: string), exv (type: string) 
55.                 outputColumnNames: _col0, _col1 
56.                 Statistics: Num rows: 22699884 Data size: 544797216 Basic stats: COMPLETE Column stats: NONE 
57.                 Map Join Operator 
58.                   condition map: 
59.                        Left Outer Join0 to 1 
60.                        Left Outer Join0 to 2 
61.                   filter predicates: 
62.                     0 {(_col0 = 'SerialID')} {(_col0 = 'CarID')} 
63.                     1  
64.                     2  
65.                   keys: 
66.                     0 UDFToDouble(_col1) (type: double) 
67.                     1 UDFToDouble(modelid) (type: double) 
68.                     2 UDFToDouble(styleid) (type: double) 
69.                   outputColumnNames: _col0, _col1, _col13, _col32 
70.                   Statistics: Num rows: 49939745 Data size: 1198553901 Basic stats: COMPLETE Column stats: NONE 
71.                   Select Operator 
72.                     expressions: _col0 (type: string), _col1 (type: string), _col13 (type: string), _col32 (type: string) 
73.                     outputColumnNames: _col0, _col1, _col2, _col3 
74.                     Statistics: Num rows: 49939745 Data size: 1198553901 Basic stats: COMPLETE Column stats: NONE 
75.                     File Output Operator 
76.                       compressed: false 
77.                       Statistics: Num rows: 49939745 Data size: 1198553901 Basic stats: COMPLETE Column stats: NONE 
78.                       table: 
79.                           input format: org.apache.hadoop.mapred.TextInputFormat 
80.                           output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 
81.                           serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 
82.       Local Work: 
83.         Map Reduce Local Work 
84.  
85.   Stage: Stage-0 
86.     Fetch Operator 
87.       limit: -1 
88.       Processor Tree: 
89.         ListSink 

执行计划上分为三个stage，第一个处理两张小表的，把小表内容处理成HashTable来做mapjoin，这个跟我们上面的分析一致。第二个用于处理大表和小表的mapjoin，最后一个则是关联后的数据输出。

从执行计划上看，似乎也没什么问题，一切很正常~~

既然从SQL本身找不到问题，那说明有可能出现在数据上或机器上，就只能具体问题具体看了。

第二步，查建表语句，看表的压缩格式是不是不支持分割导致部分map任务处理时间过长

Hive支持多种压缩格式，有的压缩格式支持split，而有的并不支持，比如LZO。当不支持split的时候，数据块有多大，Hive的map任务就得处理多大，而Hive表的分区数据有可能存在不均衡的现象，就会导致有的map快，有的map慢。当遇到LZO格式的时候，最好的方式是建立索引，可以加快处理速度。

用show create table从建表语句里看，并没有使用LZO的表。而在hdfs上直接查看三张表的文件大小，最大的那张表加上条件后还有24个分区，每个分区的大小不一样。但是由于任务有40个map，由此可知有些分区的数据是拆成了几个map任务的，所以再一次证明是可切分的，排除不可切分导致的map任务问题。

第三步，分析任务运行状况

找到运行完的任务，查看运行界面图可以看到，map任务的时间长短不一，最短的1分钟之内，最长的达半个多小时。

乍一看，好像是数据倾斜导致的，要确定是否数据倾斜，我们需要随机挑几个时间长的map任务，和时间短的map任务，看看各自的数据量和数据大小。

对比发现，数据量基本都在100万到130之间，而数据大小也在100多MB左右(不能只看数据量，数据大小也很重要，防止空列这种数据倾斜情况)。

这样一来，map端的数据倾斜其实是不存在的，所以map任务应该基本上是均衡的。那为什么时间上会相差这么大呢?

进而猜测，是不是因为某些慢的任务刚好被挤在某台或某几台机器上，而刚好这几台机器负载重，所以比较慢?

到这里，我们统计下慢的几个map任务都在什么机器上，统计完发现，果然最慢的十几个任务集中分布在两台机器上，一台机器大概六七个的样子。按7个任务算，每个任务读100多MB的文件，怎么说都在几分钟之内可以搞定吧，所以好像真的跟机器负载有关系~

所以机器这里我们也必须去看一看，看看是不是负载导致的。重新跑一下上面的任务，找到map慢的几台机器，做如下查看：

分别从CPU、内存、磁盘IO和网络IO来看，这是服务器状况查看的基本入口：

• top 命令可以辅助我们查看CPU的状况，结果发现CPU平均负载不过50%
• iostat -x 5 命令可以辅助我们查看磁盘IO情况，我们发现请求数比较高但是平均等待队列并不高，磁盘读写都跟得上，所以磁盘也不是问题
• sar -n DEV 5 命令可以辅助我们查看网络IO的情况，服务器至少是千兆网卡，支持至少1Gb/s的速度，而从输出来看，网络远远不是问题

由此，我们排除了机器负载过高导致无法服务的问题，同时问了下同事，说是这个任务跑了好多次都这样，好多次都这样说明机器应该不是问题，因为机器随机分，不可能每次都分到慢的机器上。所以说每次都map慢跟机器无关，而是我们SQL的问题。

第四步，再触SQL，分段分析

上面的分析已经确认跟机器无关，与数据不可分割也无关，而执行计划上看也没什么问题。那么只好一段一段的来看SQL了。

1、两张小表是要分发到各节点的，所以不考虑，我们按条件读一次大表的数据，统计下行数

1. SELECT COUNT(1) 
2. FROM xx.xxx_log 
3. WHERE etl_dt = '2017-01-12' 
4.   AND exk IN ('xxID', 'yyID') 

结果发现时间只花了2分钟左右，说明SQL不慢在这里。只能慢在join两张小表上了，而小表join是mapjoin，理论上应该不慢才对。

2、考虑只join一张表来看 先选表xx.xxx_model_info

1. SELECT COUNT(1) 
2. from ( 
3. SELECT 
4.        t1.exk, 
5.        t1.exv, 
6.        M.makename AS m_makename 
7. FROM 
8.   (SELECT 
9.           exk, 
10.           exv 
11.    FROM xx.xxx_log 
12.    WHERE etl_dt='2017-01-12' 
13.      AND exk IN ('xxID', 'yyID') ) t1 
14. LEFT JOIN xx.xxx_model_info M ON (M.modelid=t1.exv AND t1.exk='xxID')) tmp 

上面是跟3118的一张小表join，可以看到执行计划是mapjoin，而执行时间则出乎意料，用了大概2分钟，与单独计算大表行数差不多。

由此可以想到，mapjoin很慢应该与另一张表有关系，我们下面再执行跟另一张表join的情况，如果还是这么快，那说明两个同时mapjoin在Hive上可能存在缺陷，而如果很慢，则说明mapjoin只跟那张小表有关系。

再选表xx.xxx_style_info

1. SELECT COUNT(1) 
2. from ( 
3. SELECT 
4.        t1.exk, 
5.        t1.exv, 
6.        S.makename AS s_makename 
7. FROM 
8.   (SELECT 
9.           exk, 
10.           exv 
11.    FROM xx.xxx_log 
12.    WHERE etl_dt='2017-01-12' 
13.      AND exk IN ('xxID', 'yyID') ) t1 
14. LEFT JOIN xx.xxx_style_info S ON (S.styleid=t1.exv AND t1.exk='yyID') ) tmp 

这下执行结果奇慢无比，map阶段进展很缓慢。由此说明大表与这张小表的mapjoin存在问题，可是mapjoin为啥还存在问题呢? 问题又在哪呢?

第五步，仍不放弃执行计划

当看到上面问题的时候，一头雾水，所以着重再看执行计划是一个不错的方案。很容易想到，同是两个数据量相差不大的小表，mapjoin的运行速度为什么会不一样?是字段类型导致join连接出问题?

当我们仔细再去看最上面的执行计划的时候，我们会发现我们之前忽视的一个细节，那就是执行计划里有UDFToDouble这个转换，很奇怪我们并没有调用这样的UDF啊，怎么会有这样的转换呢? 唯一的解释只能是join字段匹配。

我们查一下join字段发现，大表的exv字段是string类型，两个小表的关联字段都是int型，它们在join的时候，居然都先转成了double型??? 这是什么鬼? 难道不应该都往string类型转换，然后再join吗?

查下Hive官网才发现，类型关系是酱紫的...

double类型是string类型和int类型的公共类型，所以它们都会往公共类型上转!

实际写SQL中，也强烈建议自己做类型匹配的处理，不要拜托给解析器，不然问题很严重。

我们把小表的int类型转换为string类型再做上面第二张表的join，如下：

1. SELECT COUNT(1) 
2. from ( 
3. SELECT 
4.        t1.exk, 
5.        t1.exv, 
6.        S.makename AS s_makename 
7. FROM 
8.   (SELECT 
9.           exk, 
10.           exv 
11.    FROM xx.xxx_log 
12.    WHERE etl_dt='2017-01-12' 
13.      AND exk IN ('xxID', 'yyID') ) t1 
14. LEFT JOIN xx.xx_style_info S ON (cast(S.styleid as string)=t1.exv AND t1.exk='yyID') ) tmp 

结果符合预料，2分钟左右的时间可以完成这个SQL任务。而当整个任务也这么改之后，任务跑完也只要几分钟!

由此可见，事情都因细节而起!做join操作的时候，写SQL的人其实是最清楚字段类型的，做上字段类型匹配小菜一碟，可以避免很多问题!!!

第六步，解开谜团

到这里，我们这个Hive任务的问题已经找到，那就是join两边key的数据类型不对，导致两边的数据类型都要向上做提升才能关联。

但其实还是有问题的，上面第四步的实验提到，当用大表与3118条数据的小表xx.xxx_model_info进行关联的时候，很快可以出结果。但是当用大表与另一张小表xx.xxx_style_info进行关联时，却发现奇慢无比，也即问题跟它有很大关系。大表无论与哪张小表关联，都要做类型提升，两张小表的数据量相比大表而言，其实相差不大，但为啥数据量稍大的小表关联就出问题呢?

我们单独对三张表做类型转换，转为double类型，结果发现三张表的类型转换都很快，并不存在因为数据不同导致转换速度不一样的情况，由此排除是类型提升时出的问题。因而问题最有可能出现在MapJoin身上!

在MapJoin阶段，会把小表的内容加载到内存中，使用容器HashMap做存储，然后对大表的关联列进行扫描，每扫描一行都会去查看HashMap中有没有对应的关联列。这样做起来其实是很快的，同时在Map端也减少了大量数据输出到Reduce端。

HashMap不允许key有重复，在Hive里，如果key有重复怎么办呢?显然是不能把重复数据直接覆盖的，因为key重复不代表value里的其他列也是重复的。这时Hive会把小表的存储由HashMap降级为LinkedList，而HashMap里key是否重复由key对应类型的hashcode和equals方法决定。

在MapJoin阶段，double类型使用的是DoubleWriteable，它的hashcode实现是一个错误的实现，如下：

1. return (int)Double.doubleToLongBits(value); 

long转为int会产生溢出，因此不同的value很可能得到相同的hashcode，hashcode碰撞非常明显。

这个问题早在 Hadoop-12217 里被提到，因为他们在使用Hive的时候碰到了和我相同的问题，就是类型提升为double时出现MapJoin异常缓慢的情况。其描述如下：

其patch里提到正确的更改方式如下：

1. long dblBits = Double.doubleToLongBits(value); 
2. return (int) (dblBits ^ (dblBits >>> 32)); 

不过这个bug目前并未修复(当前版本：Hadoop 2.6.0-cdh5.5.1, Hive 1.1.0-cdh5.5.1)，由于它导致我们数据量稍大的那张小表在MapJoin阶段由HashMap转为了LinkedList，因此数据扫描及其缓慢。而另一张3118条数据的小表，则刚好不存在hash code碰撞的问题，所以Map Join很快。

所以，最终的问题就在于此，所有的表象皆由它引起。最好的解决办法是在Join之前先做转换，让join时的键关联保持类型一致并不为double类型即可。这个需要在写HQL时时常注意，问题虽小，但是要找到它确实不容易，很是花时间。

本文作者：佚名

来源：51CTO