开发者社区> 暮角> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MaxCompute笛卡尔积逻辑的参数优化&复杂JOIN逻辑优化

简介: 这篇文章主要讲一个SQL优化反映的两个优化点。分别是: 一、笛卡尔积逻辑的参数优化。 二、一个复杂JOIN逻辑的优化思路。
+关注继续查看
福利推荐:阿里云、腾讯云、华为云等大品牌云产品全线2折优惠活动来袭,4核8G云服务器899元/3年,新老用户共享优惠,点击这里立即抢购>>>

1.??优化概述

最近协助一个项目做下优化任务的工作。因为主要数据都是报表,对数对的昏天暗地的不敢随便调整SQL逻辑,所以本身只想做点参数调整,但是逼不得已后来还是改了一下SQL。

这篇文章主要讲一个SQL优化反映的两个优化点。分别是:

一、笛卡尔积逻辑的参数优化。

二、一个复杂JOIN逻辑的优化思路。

2.??笛卡尔积逻辑参数优化

早期版本MaxCompute(ODPS)好像对笛卡尔积的支持并不友好,对笛卡尔积逻辑检测很严格,但是现在的版本就比较正常了,在项目里最近这段时间遇到了2个性能问题都是这种笛卡尔积导致的。

笛卡尔积会造成数据量的膨胀,如果是一些数据量是几千几万的小表的一些关联,好像大家也体会不到什么性能问题,不过如果某个表是几千万、几亿这个规模就可能导致单个数据处理的WORKER超出其预估,导致SQL运行时间超出预期。

2.1.?发现问题

一般对于特别去调优的场景,其实不好好看看输入输出的数据量,看看逻辑是不太好发现笛卡尔积的逻辑的。所以,一般是先从执行日志的问题着手。

x.png

如上图所示,很明显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) overpartition 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提示里面。小心使得万年船,谨慎一点也不错。

image.png

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云MaxCompute(大数据)公开数据集---带你玩转人工智能
目前阿里云大数据产品已经免费向全部用户开放了多种公用数据集。开放的数据类别包括:股票价格数据,房产信息,影视及其票房数据。
22327 0
阿里云大数据利器Maxcompute学习之-假如你使用过hive
如果您是一个大数据开发工程师并且使用过hadoop的hive框架,那么恭喜您,阿里云的大数据计算服务-Maxcompute,您已经会了90%。这篇文章就来简单对比下Maxcompute和hive的异同,来方便刚开始使用Maxcompute的用户,从hive秒速迁移到Maxcompute的使用上。
9317 0
阿里云大数据利器Maxcompute学习之--分区表的使用
初学大数据Maxcompute时部分用户不是很熟悉Maxcompute分区表的概念和使用,那这篇文章来简单介绍下分区表的概念及使用场景。? 实际上,分区在很多框架中都有这个概念,比如开源框架中的hive等。
13541 0
阿里云大数据MaxCompute计算资源分布以及LogView分析优化
MaxCompute(原ODPS)的概念 海量数据处理平台,服务于批量结构化数据的存储和计算,提供海量数据仓库的解决方案以及针对大数据的分析建模服务.(官方文档有这里就不多做介绍了)官方文档链接 优势 用户不必关心分布式计算细节,从而达到分析大数据的目的。
6053 0
深入阿里云大数据IDE–MaxCompute Studio
在云栖社区主办的云栖计算之旅第5期–大数据与人工智能分享中,阿里云计算平台高级专家薛明为大家深入地介绍了阿里云大数据IDE–MaxCompute Studio,并对于其特性和背后的技术思想进行了讲解。
9108 0
《能源地图》:阿里云数加MaxCompute轻松处理8000亿条气象数据
十三五期间伴随能源互联网的建设,能源大数据及云计算能力长足发展,圈内诸多专家和组织陆续又开始讨论“能源地图”的深化应用, 什么是能源地图: 能源地图 1、反映一定地域范围内能源的发展条件及其水平的地区差异的专题地图。
2891 0
阿里云数加MaxCompute购买咨询钉钉群
为企业解答阿里云MaxCompute大数据计算产品购买问题。
13126 0
持续创新和改进,为用户创造最大价值,阿里云数加MaxCompute获得C-Tech Awards 2016年度“最具技术创新奖”
“C-Tech Awards 2016最具价值大奖评选”活动已经正式结束。从2016年12月22日线上征集至今,活动获得各大技术行业企业的高度关注,现已有来自国内外的近400家企业参与到了本次评选活动中,并提交了全面的产品简介和创新点解析。
3097 0
【物流大数据实践】基于阿里云Maxcompute实现物流跟踪
摘要: 目前我国物流业保持较快增长,但还是存在一些问题:物流成本高、效率低,条块分割严重(自营物流、规模小、技术落后、标准不统一)、基础设施相对滞后(物流基础设施之间不衔接、不配套),对订单创建到用户签收整套完整流程缺乏完善的监控和预警手段.
6348 0
【阿里云大数据产品MaxCompute(原名ODPS)】DT时代企业数据资产的护卫舰
MaxCompute设计之初就是面向多租户,确保租户的数据安全是MaxCompute的必备功能之一。在MaxCompute系统的安全设计和实现上,MaxCompute的工程师们会遵循一些经过实践检验的安全设计原则(如Saltzer-Schroeder原则)。
3865 0
+关注
暮角
阿里云GTS-交付技术部-技术中台组
文章
问答
来源圈子
更多
MaxCompute(原ODPS)是一项面向分析的大数据计算服务,它以Serverless架构提供快速、全托管的在线数据仓库服务,消除传统数据平台在资源扩展性和弹性方面的限制,最小化用户运维投入,使您经济并高效的分析处理海量数据。
+ 订阅
相关文档: MaxCompute
文章排行榜
最热
最新
相关电子书
更多
MaxCompute SQL计算成本调优以及优化方法
立即下载
MaxCompute计算长尾问题优化
立即下载
面向索引、模型、检索联合优化的下一代推荐技术
立即下载


http://www.vxiaotou.com