企业实战总结：SQL Join执行的常见问题及解决方案

1.背景

SQL 的join 对于数据开发同学是最经常遇到的操作，通过表与表之间的关联来得到想要的数据。

但是在开发中我们会遇到一些莫名奇妙的问题，本文就选择最常见的两类问题跟大家分享。

2.结果不符合预期

2.1 string和bigint做join，出现重复数据

这个问题源于底层的隐式转换规则，当string和bigint做比较的时候，会将两侧分别转换为double类型，而这个转换可能存在一定的精度损失，导致出现本来不相等的两个值能够join上，例如：

create table tbl_str as select "111111111111111111" k;
create table tbl_int as select  111111111111111110 k;
select a.k ak, b.k bk
from tbl_str a
join tbl_int b
on a.k = b.k;
-- 并不相同的两个值可以得到结果
+----+------------+
| ak | bk         |
+----+------------+
| 111111111111111111 | 111111111111111110 |
+----+------------+
-- 原因在于上述sql被转化为了
select a.k ak, b.k bk
from tbl_str a
join tbl_int b
on cast(a.k as double) = cast(b.k as double);

其实大家在写join语句的时候，一般对join key的类型不会太在意，而且对于比较小的数字，转化成double类型也并不会有精度损失，但是随着业务的变化，有可能会出现较长的数字id，这里就有存在结果不正确的风险。针对这种情况，一般的解决方法是在join on条件的一侧做一次显式的转换，让两侧的类型一致即可。

select a.k ak, b.k bk
from tbl_str a
join tbl_int b
on a.k = cast(b.k as string);

2.2 string和bigint做join，出现重复数据

这个情况和上述情况的原因是一样的，但是出现的现象不同。这里是string列中出现"nan"时，会转换为double的nan值，目前MaxCompute还不支持nan的double值，此时会出现排序异常无法退出的情况。解决方法同上。

2.3?过滤条件位置不对导致outer join退化为inner join

例如本来想做一个outer join，但是由于分区过滤条件写到外面导致实际执行的是inner join，本质是使用JOIN时分区剪裁失效导致 ，例如：

select a.k ak, b.k bk
from tbl1 a
left outer join tbl2 b
on a.k = b.k
where b.ds='20210101';

需要把限制条件写到where子句中；

select a.k ak, b.k bk
from tbl1 a
left outer join
(select k from tbl2 where ds = '20210101') b
on a.k = b.k ;

分区裁剪失效的情况

自定义函数导致分区剪裁失效：需要使用关键字explain + sql来查看

使用JOIN时分区剪裁失效

在SQL语句中使用JOIN进行关联时：

1.如果分区剪裁条件放在WHERE子句中，则分区剪裁会生效。

2.如果分区剪裁条件放在ON子句中，从表的分区剪裁会生效，主表则不会生效。

LEFT OUTER JOIN

1.分区剪裁条件均放在ON子句中：左表进行了全表扫描，只有右表的分区裁剪有效果；

2.分区剪裁条件均放在WHERE子句中：两张表的分区裁剪都有效果。

RIGHT OUTER JOIN与LEFT OUTER JOIN类似，如果分区剪裁条件放在ON子句中则只有RIGHT OUTER JOIN的左表生效。如果分区剪裁条件放在WHERE中，则两张表都会生效。、

FULL OUTER JOIN

分区剪裁条件只有都放在WHERE子句中才会生效，放在ON子句中都不会生效。

3.性能问题

3.1 join一侧大表，一侧小表(几十MB以内)

这种可以使用mapjoin的方式，目前MaxCompute会自动对一些小表做mapjoin，但是阈值比较保守，用户也可以用hint自己打开。下面例子就是对b和c都是小表的场景做的优化。

select /*+mapjoin(b,c)*/ a.k ak, b.v1, c.v2
from tbl1 a
left outer join tbl2 b
on a.k = b.k
left outer join tbl3 c
on a.k = c.k;

需要注意的是mapjoin括号中的表是小表侧，而且是不能当做可能会补null的一边，也就是left join的左表，right join的右表，full join的两端，否则就算是写了hint也也不起作用。有一个配置可以调整mapjoin的小表侧的大小，超出会在运行过程中报错，目前默认值是640（单位MB），最大可以增加到8192。

3.2 多路join,join on 多列，有相同的前缀列

多路的join的on条件，如果使用的都是merge join且有部分相同的列，可以把较短的放前面：

select a.*, b.v1, c.v2
from tbl1 a
left outer join tbl2 b
on a.k1 = b.k1 and a.k2 = b.k2
left outer join tbl3 c
on a.k1 = c.k1;

这里tbl3的join key为k1，tbl2的join key是k1, k2，tbl3的join key更短，可以放在前面。

select a.*, b.v1, c.v2
from tbl1 a
left outer join tbl3 c
on a.k1 = c.k1;
left outer join tbl2 b
on a.k1 = b.k1 and a.k2 = b.k2

这样可以尽可能使用前一个join已经shuffle好的结果，通过一个局部的排序就避免第二步产生额外的shuffle。

此外还有一种情况，join多列，列都相同，但是顺序不一致，也可能导致执行计划较差。

select a.*, b.v1, c.v2
from tbl1 a
left outer join tbl2 b
on a.k1 = b.k1 and a.k2 = b.k2
left outer join tbl3 c
on a.k2 = c.k2 and a.k1 = c.k1;
-- 需要调整join的k1 k2的顺序为
-- on a.k1 = c.k1 and a.k2 = c.k2;

3.3?多路join,每个join的key不一致，表的大小差异较大

多路join，每个join的key不一致会产生多次的shuffle，每次的shuffle的数据是上一次join产生的结果，为了减少shuffle的数据量，应该在不改变语义的情况下，遵从以下原则，优先级由高到低：

1.能过滤数据的join放前面，产生数据膨胀的放后面；

2.结果行数区别不大的情况下，每行数据小的放前面，每行数据大的放后面；3.mapjoin放在最后面；

下面的例子中，如果b表可以过滤a表的数据，c和d均不会产生膨胀，且c表join过来的字段较小，e表会产生膨胀，则一个比较理想的join顺序如下：

select a.k ak, b.v1, c.v2, d.v3
from tbl1 a
join tbl2 b --b表可以过滤a表的部分数据
on a.k1 = b.k1
left outer join tbl3 c --c不会导致数据膨胀，且c.v2较小
on a.k2 = c.k2
left outer join tbl4 d --d不会导致数据膨胀，d.v3比c.v2较大
on a.k3 = d.k3
left outer join tbl5 e --e会产生数据膨胀
on a.k4 = e.k4

3.4?join中有一侧表存在数据倾斜

数据倾斜也是join中的一类常见问题，例如要对在购物车的商品以商品id做关联，总是免不了出现部分热门商品在购物车的次数远远超出平均值的情况。

select a.id, b.name
from shopping_cart a
left outer join dim b
on a.id = b.id;

这时会出现严重的长尾。以前为了避免这种情况，往往需要用户自己拆表，长尾的数据做mapjoin，非长尾的数据做merge join。