大表join小表优化

川长思鸟来 2023-01-16 15:20 220阅读 0赞

### 大表join小表优化 ###

和join相关的优化主要分为mapjoin可以解决的优化（即大表join小表）和mapjoin无法解决的优化（即大表join大表），前者相对容易解决，后者较难，比较麻烦。

首先介绍大表join小表优化。以销售明细表为例来说明大表join小表的场景。

假如供应商进行评级，比如（五星、四星、三星、二星、一星），此时因为人员希望能够分析各供应商星级的每天销售情况及其占比。

开发人员一般会写出如下SQL：

**select ** seller\_star, count(order\_id) as order\_cnt

**from**

(**select** order\_id,seller\_id    ** from** sales\_detail\_table**  where **partition\_value='20181010' ) a

**left outer join**

( **select** seller\_id, seller\_start   **from** dim\_seller  **where** partition\_value =''20181010' ) b

**on** a.seller\_id = b.seller\_id

**group by** b.seller\_star;

现实世界的二八准则将导致订单集中在部分供应商上，而好的供应商的评级通常会更高，此时更加加剧了数据倾斜的程度，如果不加以优化，上面SQL将会耗费很长时间，，甚至运行不出结果。

通常来说，供应商是有限的，比如上千家，上万家，数量不会很大，而销售明细表比较大，这就是典型的大表join小表的问题，可以通过mapjoin的方式来优化，只需要添加mapjoin hint即可，

优化后的SQL如下：

**　　　　　　select ** /\*+mapjoin(b)\*/

seller\_star, count(order\_id) as order\_cnt

**from**

(**select** order\_id,seller\_id    ** from** sales\_detail\_table**  where **partition\_value='20181010' ) a

**left outer join**

( **select** seller\_id, seller\_start   **from** dim\_seller  **where** partition\_value =''20181010' ) b

**on** a.seller\_id = b.seller\_id

**group by** b.seller\_star;

/\*+mapjoin(b)\*/ 即是mapjoin hint,如果需要多个mapjoin多个表，则格式为：/\*+mapjoin(b,c,d)\*/.。 Hive对于mapjoin是默认开启的，设置参数为：

Set hive.auto.convert.join = true;

mapjoin优化是在Map阶段进行join，而不是通常那样在Reduce阶段按照join列进行分发后在每个Reduce节点上进行join，不需要分发也就没有倾斜的问题，相反，Hive会将小表

全量复制到每个Map任务节点（对于本例是dim\_seller表，当然进全量复制b表 sql指定的列），然后每个Map任务节点执行lookup小表即可。

从上面的分析可以看出，小表不能太大，否则全量复制分发得不偿失，实际上Hive根据参数hive.mapjoin.smalltable.size(0.11.0版本后是hive.auto.convert.join.nonconditionaltask.size) 来确定小表的

大小是否满足条件（默认25MB），实际中此参数允许的最大值可以修改，但是一般最大不能超过1GB（太大的话Map任务所在的节点内存会撑爆，Hive会报错。另外需要注意的是，HDFS显示的文件

大小是压缩后的大小，当实际加载到内存的时候，容量会增大很多，很多场景下会膨胀10倍）。

还没有评论，来说两句吧...