简介：这篇文章次要讲一个 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… 本文为阿里云原创内容，未经容许不得转载。