MaxCompute笛卡尔积逻辑的参数优化&复杂JOIN逻辑优化
1. 优化概述
最近协助一个项目做下优化任务的工作。因为主要数据都是报表，对数对的昏天暗地的不敢随便调整SQL逻辑，所以本身只想做点参数调整，但是逼不得已后来还是改了一下SQL。
这篇文章主要讲一个SQL优化反映的两个优化点。分别是：
一、笛卡尔积逻辑的参数优化。
二、一个复杂JOIN逻辑的优化思路。
2. 笛卡尔积逻辑参数优化
早期版本MaxCompute（ODPS）好像对笛卡尔积的支持并不友好，对笛卡尔积逻辑检测很严格，但是现在的版本就比较正常了，在项目里最近这段时间遇到了2个性能问题都是这种笛卡尔积导致的。
笛卡尔积会造成数据量的膨胀，如果是一些数据量是几千几万的小表的一些关联，好像大家也体会不到什么性能问题，不过如果某个表是几千万、几亿这个规模就可能导致单个数据处理的WORKER超出其预估，导致SQL运行时间超出预期。
2.1. 发现问题
一般对于特别去调优的场景，其实不好好看看输入输出的数据量，看看逻辑是不太好发现笛卡尔积的逻辑的。所以，一般是先从执行日志的问题着手。
如上图所示，很明显JOIN阶段有数据倾斜。J4_1_3_6_job_0阶段，10分钟只是执行了2个backups（我没有截全，其实时间更久）。
2.2. 对付倾斜的暴力参数方法
倾斜的核心逻辑是有个别WORKER处理的数据比其他WORKER要多数倍，并且超过了1个WORKER处理能力太多。一般我们的WORKER处理数据的时间在秒级是最常见的，但是到几分钟这种程度的倾斜一般也能接受。毕竟不倾斜的数据真实情况下很难遇到，但是有backups的场景就说明超出了这个WORKER的处理能力，这个程序居然跑挂了。然后又启动了一个WORKER，继续跑。
那这种情况，不管这个数据到底怎么倾斜，为什么倾斜，最简单暴力的方法就是：
1-把单个WORKER处理的数据量变小；
-- 让map阶段的worker变多【默认256，值域1-最大值】
SET odps.sql.mapper.split.size = 16;
-- 让reduce阶段的worker变多【默认-1，动态；最大1111，手动最大9999】
SET odps.sql.reducer.instances = 99;
-- 让join阶段的worker变多【默认-1，动态；最大1111，手动最大9999】
SET odps.sql.joiner.instances = 200;
注意：一个worker至少占用1core的CPU，如果集群不够大，或者分配给项目的资源有限，都会让执行的WORKER排队执行。再有，如果本来1个WORKER就能很快执行完的数据，你拆分成很多份，反而会让处理步骤更多，导致处理时间更慢。线上任务资源紧张的时候，要获取很多WORKER执行也会困难，导致长期的等待足够多的资源释放。所以，不是越大越好，最好是牛刀小试，浅尝即止。
2-让单个WORKER拥有更多的计算资源；
-- 让map阶段的CPU变多【默认100，值域50-800，官方认为没必要调整】
SET odps.sql.mapper.cpu = 800;
-- 让map阶段的内存变多【默认1024，值域256-12288，少量场景可调整】
SET odps.sql.mapper.memory = 12288;
-- 让reduce阶段的CPU变多【默认100，值域50-800，官方认为没必要调整】
SET odps.sql.reducer.cpu = 800;
-- 让reduce阶段的内存变多【默认1024，值域256-12288，少量场景可调整】
SET odps.sql.reducer.memory = 12288;
-- 让join阶段的CPU变多【默认100，值域50-800，官方认为没必要调整】
SET odps.sql.joiner.cpu = 800;
-- 让join阶段的内存变多【默认1024，值域256-12288，少量场景可调整】
SET odps.sql.joiner.memory = 12288;
注意：一个worker至少占用1core的CPU，1G内存。可以增加CPU和内存，让单个worker的资源更多，一方面不会因为资源不足挂了，另外一方面也可以提升运算速度。但是实际上一般CPU资源在集群中更为紧张，内存相对富裕。启动一个worker需要8核资源，启动10个worker就要80个，在资源不足的时候，只需要1核的worker可能获取资源就跑起来了，这个需要资源多的还需要等待，所以，不是越大越好，也不是越多越好。这些参数，都是尽量别用，只有你发现具体问题去解决的时候再斟酌使用。
在一些场景，这个暴力手段还是很好用，如果能解决问题又不影响其他任务运行，用用也不错。尤其是我们需要立即解决问题的时候，简单粗暴很正确。
3. 一个复杂JOIN逻辑的优化思路
一般JOIN逻辑都是很清晰的，但是有的时候开发同学会没有把握，那么最好的方法就是验证一下。
今天就遇到了一个特殊的关联逻辑：不等关联。
3.1. 理解原始的逻辑
原始脚本内容示例如下：
with ta as(
select a,b from values ('12', 2), ('22', 2), ('23', 4), ('34', 7), ('35', 2), ('46', 4), ('47', 7) t(a, b))
select x.a,sum(t.b) as b
FROM (select distinct a from ta) x
left outer join ta t
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a
group by x.a
;
a b
1 12 2
2 22 2
3 23 6
4 34 7
5 35 9
6 46 4
7 47 11
上面这段脚本其实只用到了一个表，字段a是按月报表的 统计日期，截取了一段“年”关联，并且关联限制了关联的统计月份不能大于当前月。
其实看到这个写法，我映入脑海的就是计算累计值。我原以为maxcompute有这个累计函数，不过确实没找到，不过窗口函数也可以计算。逻辑如下：
with ta as(
select a,b from values ('12', 2), ('22', 2), ('23', 4), ('34', 7), ('35', 2), ('46', 4), ('47', 7) t(a, b))
select t.a,sum(t.b) over （partition by SUBSTR(t.a, 1, 1) order by t.a）as b
FROM ta t
;
a b
1 12 2
2 22 2
3 23 6
4 34 7
5 35 9
6 46 4
7 47 11
通过上面这段SQL，我们发现用窗口计算累计值是可以替代原来的JOIN逻辑的。因为上面我们看执行计划，发现SQL性能瓶颈出现在JOIN阶段，如果没有JOIN，自然没有这个问题了。
但是仔细查看了原始脚本的产出后，发现数据量是膨胀的，也就是说JOIN后的记录数比原始表多了。这主要因为脚本保留了JOIN的两个表的不等关联关联字段，形成了【2022年2月关联2022年1月】、【2022年2月关联2022年2月】，原来2月就一行现在变成两行。这个时候窗口函数没办法实现数据量变多的逻辑，只能老老实实用JOIN。
3.2. 进一步分析原始逻辑
咨询分析后发现其实逻辑如下（去掉了聚合示意）：
with ta as(
select a,b from values ('12', 2), ('22', 2), ('23', 4), ('34', 7), ('35', 2), ('46', 4), ('47', 7) t(a, b))
select t.*,x.*
FROM (select distinct a from ta) x
left outer join ta t
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a;
a b a2
1 12 2 12
2 22 2 22
3 22 2 23
4 23 4 23
5 34 7 34
6 34 7 35
7 35 2 35
8 46 4 46
9 46 4 47
10 47 7 47
上面的逻辑我们可以看到，JOIN的两张表其实是一张表产出，关联字段是原始明细表group by日期（字段a）获取。所以，两个表没必要写成左连接，内连接也是可以的。因为如果左连接，就没办法用mapjoin小表的方法跳过JOIN步骤，但是内连接就可以了。逻辑如下：
with ta as(
select a,b from values ('12', 2), ('22', 2), ('23', 4), ('34', 7), ('35', 2), ('46', 4), ('47', 7) t(a, b))
select /*+mapjoin(x)*/t.*,x.*
FROM ta t
join (select distinct a from ta) x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a;
a b a2
1 12 2 12
2 22 2 22
3 22 2 23
4 23 4 23
5 34 7 34
6 34 7 35
7 35 2 35
8 46 4 46
9 46 4 47
10 47 7 47
如上我们可以看到数据结果都是一样的，所以这两个JOIN逻辑是可以替代的。因为考虑到原始脚本的复杂性，我还是拿原始脚本修改做了数据比对测试，结果也是全部记录的字段值都是一样的。（原型只是一个方向，具体场景一定要做足验证测试）因为原始脚本还是比较复杂，我就不展示了，我把全字段比对两种写法的SQL写一下，方便大家测试使用。
select count(*) from (
select 1
from ta a
join tb b
on 1=1
and coalesce(a.col1,'')= coalesce(b.col1,'')
and coalesce(a.col2,'')= coalesce(b.col2,'')
......
and coalesce(a.coln,0 )= coalesce(b.coln,0)
)t
;
注意：全字段比对关联，返回的记录数要与单表记录数一致。
4. 长脚本优化其他要注意的点
4.1. 一次处理多次引用
实际上这个原始脚本很长，差不多1000行，每一段SQL运行时间都不算长。即便是上面提到的笛卡尔积的场景，也才十几分钟就跑完了，但是整体运行时间超过了1小时40分钟。里面的SQL写了十多段，简直是手工串行执行任务。
但是很快我就发现一个问题，脚本里面用了很多次insert into，说明多段SQL其实可以使用union all一次写入的。尤其是我看到很多段SQL的使用的表和表的关联逻辑都是一样的，就像我在上面优化的那段SQL，连着好几段SQL都是表和关联逻辑完全一样。这种时候，如果写成union all，map阶段和join阶段其实只需要做一次，写了4遍就需要做四次。对于一段就要跑十几分钟的脚本，直接就让本来只需要十几分钟就跑完的逻辑运行时间翻了4倍。在这个脚本中，这样的地方有2大段，涉及很多小段。
--建立测试表
create table tmp_mj_ta as
select a,b from values ('12', 2), ('22', 2), ('23', 4), ('34', 7), ('35', 2), ('46', 4), ('47', 7) t(a, b);
--建立写入表
drop table if exists tmp_mj_01;
create table tmp_mj_01 (xa string,ta string,tb bigint,tc string);
-- 1-多段insert into写入，关联逻辑一样，每一段都有map阶段和Join阶段
insert overwrite table tmp_mj_01
select /*+mapjoin(x)*/x.a,t.a,t.b,'01' as c
FROM tmp_mj_ta t
join (select distinct a from tmp_mj_ta) x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a;
insert into table tmp_mj_01
select /*+mapjoin(x)*/x.a,t.a,t.b,'02' as c
FROM tmp_mj_ta t
join (select distinct a from tmp_mj_ta) x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a;
insert into table tmp_mj_01
select /*+mapjoin(x)*/x.a,t.a,t.b,'03' as c
FROM tmp_mj_ta t
join (select distinct a from tmp_mj_ta) x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a;
-- 2-改为union all写入，关联逻辑一样，只有一个map阶段和Join阶段
insert overwrite table tmp_mj_01
select /*+mapjoin(x)*/x.a,t.a,t.b,'01' as c
FROM tmp_mj_ta t
join (select distinct a from tmp_mj_ta) x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a
union all
select /*+mapjoin(x)*/x.a,t.a,t.b,'02' as c
FROM tmp_mj_ta t
join (select distinct a from tmp_mj_ta) x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a
union all
select /*+mapjoin(x)*/x.a,t.a,t.b,'03' as c
FROM tmp_mj_ta t
join (select distinct a from tmp_mj_ta) x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a;
还有上面处理逻辑中的(select distinct a from ta)逻辑，出现很多次。这种时候，可以使用with表达式来写在最前面一次，多个union步骤都可以引用，不但代码整洁可读性强，也不容易出错。
--更为简洁的写法with表达式
-- 改为union all写入，关联逻辑一样，只有一个map阶段和Join阶段
with tmp_mj_tx as (
select distinct a from tmp_mj_ta
)
insert overwrite table tmp_mj_01
select /*+mapjoin(x)*/x.a,t.a,t.b,'01' as c
FROM tmp_mj_ta t
join tmp_mj_tx x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a
union all
select /*+mapjoin(x)*/x.a,t.a,t.b,'02' as c
FROM tmp_mj_ta t
join tmp_mj_tx x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a
union all
select /*+mapjoin(x)*/x.a,t.a,t.b,'03' as c
FROM tmp_mj_ta t
join tmp_mj_tx x
ON SUBSTR(x.a, 1, 1) = SUBSTR(t.a, 1, 1)
AND x.a >= t.a;
4.2. 检查自动mapjoin小表是否生效
MaxCompute（ODPS）的最新版本是支持自动识别小表，执行MAPJOIN的。但是天有不测风云，这个脚本中刚好有一段SQL的这个自动没生效，导致这段逻辑运行了几十分钟。
在查看日志的时候，如果遇到这种问题，后面一定要显示的在脚本中加上mapjoin提示。如果不放心，建议开发同学主动把小表都写到mapjoin提示里面。小心使得万年船，谨慎一点也不错。
