关于后端:MaxCompute笛卡尔积逻辑的参数优化复杂JOIN逻辑优化

简介： 这篇文章次要讲一个SQL优化反映的两个优化点。别离是： 一、笛卡尔积逻辑的参数优化。 二、一个简单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 bFROM (select distinct a from ta) xleft outer join ta tON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.agroup by x.a;   a   b1   12  22   22  23   23  64   34  75   35  96   46  47   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 bFROM ta t;   a   b1   12  22   22  23   23  64   34  75   35  96   46  47   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) xleft outer join ta tON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.a;   a   b   a21   12  2   122   22  2   223   22  2   234   23  4   235   34  7   346   34  7   357   35  2   358   46  4   469   46  4   4710  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 tjoin (select distinct a from ta) xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.a;   a   b   a21   12  2   122   22  2   223   22  2   234   23  4   235   34  7   346   34  7   357   35  2   358   46  4   469   46  4   4710  47  7   47如上咱们能够看到数据后果都是一样的，所以这两个JOIN逻辑是能够代替的。因为思考到原始脚本的复杂性，我还是拿原始脚本批改做了数据比对测试，后果也是全副记录的字段值都是一样的。（原型只是一个方向，具体场景肯定要做足验证测试）因为原始脚本还是比较复杂，我就不展现了，我把全字段比对两种写法的SQL写一下，不便大家测试应用。select count() from (select 1from ta ajoin tb bon 1=1and 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 asselect 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_01select /+mapjoin(x)/x.a,t.a,t.b,'01' as cFROM tmp_mj_ta tjoin (select distinct a from tmp_mj_ta) xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.a;insert into table tmp_mj_01select /+mapjoin(x)/x.a,t.a,t.b,'02' as cFROM tmp_mj_ta tjoin (select distinct a from tmp_mj_ta) xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.a;insert into table tmp_mj_01select /+mapjoin(x)/x.a,t.a,t.b,'03' as cFROM tmp_mj_ta tjoin (select distinct a from tmp_mj_ta) xON 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_01select /+mapjoin(x)/x.a,t.a,t.b,'01' as cFROM tmp_mj_ta tjoin (select distinct a from tmp_mj_ta) xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.aunion allselect /+mapjoin(x)/x.a,t.a,t.b,'02' as cFROM tmp_mj_ta tjoin (select distinct a from tmp_mj_ta) xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.aunion allselect /+mapjoin(x)/x.a,t.a,t.b,'03' as cFROM tmp_mj_ta tjoin (select distinct a from tmp_mj_ta) xON 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_01select /+mapjoin(x)/x.a,t.a,t.b,'01' as cFROM tmp_mj_ta tjoin tmp_mj_tx xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.aunion allselect /+mapjoin(x)/x.a,t.a,t.b,'02' as cFROM tmp_mj_ta tjoin tmp_mj_tx xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.aunion allselect /+mapjoin(x)*/x.a,t.a,t.b,'03' as cFROM tmp_mj_ta tjoin tmp_mj_tx xON SUBSTR(x.a, 1, 1) =  SUBSTR(t.a, 1, 1)AND x.a >= t.a;4.2. 查看主动mapjoin小表是否失效MaxCompute（ODPS）的最新版本是反对自动识别小表，执行MAPJOIN的。然而天有不测风云，这个脚本中刚好有一段SQL的这个主动没失效，导致这段逻辑运行了几十分钟。在查看日志的时候，如果遇到这种问题，前面肯定要显示的在脚本中加上mapjoin提醒。如果不释怀，倡议开发同学被动把小表都写到mapjoin提醒外面。小心使得万年船，审慎一点也不错。

 原文链接：https://click.aliyun.com/m/10... 本文为阿里云原创内容，未经容许不得转载。