MaxCompute SQL 优化 | 学习笔记

本文涉及的产品
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
简介: 快速学习 MaxCompute SQL 优化

开发者学堂课程【SaaS  模式云数据仓库系列课程 —— 2021数仓必修课MaxCompute SQL优化】学习笔记,与课程紧密联系,让用户快速学习知识。

课程地址:https://developer.aliyun.com/learning/course/55/detail/1046



MaxCompute SQL  优化


内容简介:

1.SQL  成本计算

2.SQL IO 读取优化

3.SQL  计算优化

4.SQL  整体优化



SQL  运行过程

select sum(vl)from A group by k1 where c1 >100;

image.png


1.SQL  成本计算

> 计算成本 <-读取 IO  数据量*Sql  复杂度

> Sql  复杂度∶Join/ Group By/Order By/Distinct /window func/ Insert into

因此优化  SQL  的过程,实际上就是要尽可能减少  IO  读取,尽可能减少计算资源使用,尽可能降低  SQL  复杂度,尽可能提升运行速度

So, Let's go!


2.SQL IO  读取优化

表分区优化

>建立分区表

·Create Table t1()partitioned by (pt string, region string)

·分区层数不要太多

>分区裁剪

·避免全表扫描,减少资源浪费

·Case: where pt=xxx and region=xxx  

·分区尽量按层级顺序裁剪

·分区值尽量常量化,避免不可确定值, 如UDF ·分区值尽量避免引用列的表达式计算或者子查询

>写分区

·写入静态分区,优化数据存储

·避免动态分区,防止小文件过多和计算长尾

列裁剪、条件过滤

>只引用有效列

·避免  select* from xxx

·常量代替引用列,如count(c)->count(1)//c not null  

>尽可能  pushdown 过滤条件

·where a>10 and (b>1orc<1)

> Limit N

源表合并

>合并不同  Sql,一读多计算

·读取相同源表可合并,节省  IO  和计算资源

·对源表统计多种指标计算或者筛选不同数据处理

·避免规模过大,运行时间过长

>Multi Insert  ,动态分区,一读多写

·同一 sql  读取相同源表,系统会优化只读取一次

·资源足够,也可以考虑拆分  sql,读取和计算更好并行,资源换时间

>子查询合并

·对于  Sql  中相同的子查询也会合并成一个源

· 尽可能保持子查询语句一样,触发合并

image.pngimage.png


Join  连接-MapJoin  优化

运行原理·小表数据全部加载内存

·读取大表的每个  task  加载一份小表数据

>Case1  ∶大小表数据相差大,小表满足限制条件

·静态维度表,轻度汇总表等

Select/产+mapjoin(supplier)*/ t1.id….from sales t1join supplier t2 on t1.cus_id = t2.id;

·大小表数据量相差越大越有优势,越能节省  IO

>Case2  ∶不等值条件,or  条件等复杂  on 条件

· 显性指定mapjoin

Select /+mapjoin(supplier)*/ t1.id,… from sales t1 join supplier t2 on t1.sales_price < t2.order_price;

·不支持其他关联方式

限制条件∶

· left outer join  的左表必须是大表。

· right outer join  的右表必须是大表。

· inner join  左表或右表均可以作为大表。

·full outer join  不能使用  MapJoin。

· MapJoin  支持小表为子查询。

· 使用  MapJoin  时,需要引用小表或是子查询时,需要引用别名。

· 在  MapJoin 中,可以使用不等值连接或者使用or连接多个条件。

· 最多支持指定8张小表,否则报语法错误。

· 如果使用  MapJoin  ,则所有小表占用的内存总和不得超过 512MB.  

· 多个表  Join  时,最左边的两个表不能同时是 MapJoin  的表。


3.SQL  计算优化

Join  连接- MapJoin  优化

> Case3: AutoMapjoin

·Select t1.id.. from sales tljoin supplier t2 on t1.cus_id = t2.id;

> Case4∶  数据倾斜,导致长尾

表现∶极少数 joiner  进程处理数据量远大于其他进程,运行时间过长

原因∶

·热点  key∶  nul  值,空值,缺省值,大  key  值等

·数据分发不均衡∶数据属性,压缩率不均等

·建表不合理

操作∶

·热点  Key  尽量先去重或过滤无效值

·对一些热点缺省无效值加一个随机前缀,分散数据

·修改  SQL  隔离热点Key和非热点Key分别做关联,再union all起来

·显性指定  mapjoin

·增加源表作为大表的并发度∶ set odps.sql.mappersplitsize=128;

·增加子查询作为大表并发度∶set odps.sqljoiner.instances=1111;

Full outer Join  全连接优化

>优化限制

·只能使用  MergeJoin,无法直接  Mapjoin  

·出现数据倾斜,很难通过参数设置调整

>解决∶尽量转化为  mapjoin

·将   full outer join  转换为left outer join + union all

·或者转换为  left anti join + union all

·将小表设置为  mapjoin  表

>SQL  案例

优化前∶  select coalesce(t1.key, t2.key) as key, coalesce(t1.val, t2,val) as val

from t1 full outer join t2 on t1.key and t2.key;

优化后∶select key, val from(select /*mapjoin(t2)*/ t1.key, t1.val from t1 left outer join t2 on t1.key = t2.key where t2.key is null union all select key, val from t2) t

Join   条件过滤

>Left outer join

· on  条件∶右表条件下推,左表不下推,如   Lt.a=1 and Rt.b =1;

·where  条件∶左表条件下推,右表非  null  条件可退化为 inner join  并下推,如 Lta=1 and Rt.b=1;

> Right outer join

· on  条件∶左表条件下推,右表不下推;

·where  条件∶右表条件下推,左表非null条件可退化为inner join并下推;

> Full outer join

· on  条件∶左表和右表条件都不下推,如  Lt.a=1 and Rt.b = 1;

·where  条件∶左表非  null  条件可退化为左连接,右表非  null  条件可退化为右连接,并且都可下推

类型转换

>尽可能保证表达式两边的类型一致

·建表时,尽可能让关联表的Key类型保持一致

·使用  Cast  显性转换

>隐式转换

· Join on   条件,类型不一致会触发隐式转换

·容易造成精度问题,比如  string  和  bigint  都转成  double  来比较相等

·悲剧情况下,可能触发数据倾斜


4.  SQL 整体优化

聚合运算优化

>少用  Distinct  聚合函数

导致数据膨胀厉害,网络传输和处理的数据量暴增

·IO,CPU  和  memory  资源消耗增加,运行时间变长

·容易触发数据倾斜

>数据倾斜,导致长尾原因∶

·Key  值为缺省值∶null  值,空值,缺省无效值,常量值等

·业务数据热点集中∶  GroupBy key  的维度过小很容易造成热点  Key

·Distinct  聚合函数过多

操作∶

·系统自动处理((消耗更多资源)∶odps.sql.groupby.skewindata=true;

·手动修改  SQL∶select pid,count(price) from sales group by pid;//如果key存在大量缺省无效值  "-1"

修改后  ∶select newpid, count(price) from (select case when pid="-1"then

concat(rand(),"default")else pid end as new pid from sales) group by newpid

·调整并发度  ∶set odps.sql.joiner.instances=1111;

算子顺序优化

> Join reorder

·存在多个表  join  时, join  顺序很关键

·优先选择  join  结果输出小的表先关联

·有效减少中间数据量,节省  IO  和计算资源

>优先执行可去重的算子

· Join->group by可以优化为先group by->join

如∶select key, max(val) from(select t1.key, t1.val from t1join t2 on t1.key=t2.key) a group by key 修改为 select a.key, a.val from (select key, max(val) as val from t1 group by key) a

join (select key, max(val) as val from t2 group by key) b on a.key = b.key

·如有大量  key  重复的话,可大大减少中间数据的处理,节省计算资源,显著提升运行速度

>分组Key相同的算子可以放一起

· Join 和  group by  的 key  相同的话,可以减少一级  task,从而较少数据网络传输

动态分区

>使用场景

·只需要指定分区列,输出数据根据实际值写入不同的分区

·不用手动创建分区,计算前也不知道数据属于哪个静态分区

·SQL:Insert into table t1 partition (pt) select *from src

>执行 影响∶小文件过多

·单  task  处理文件数目有限制

·需要更多的  task  调度资源

·task  读写速度受影响并且耗更多内存资源

·对分布式存储系统也会带来一定的压力

·对磁盘读写也有一定的影响

推荐使用

·设计  SQL  一定要尽可能写入静态分区

·避免小文件过多∶  set odps.sql.reshuffle.dynamicpt=true

image.png

动态分区

>Reshuffle  影响

·有效减少小文件产生和写数据的task内存使用

·会多增加一级  reduce  处理,运行时间变长

·消耗额外的计算资源,Disk/Net IO,CPU,Memory

·关注有无数据倾斜可能

·如果动态分区很少,可以关闭这个flag

>Merge   小文件操作

· set odps.task.merge.enable=true

·系统会根据需要启动后台进程执行  merge  操作

常见优化  tips

>尽量用内置的  UDF/UDAF  函数

·内置  UDF  在实现做了很多优化,运行快,省资源,稳定,常量折叠

·用户自定义  UDF  运行慢,资源不可控,甚至触发超时和  OOM  

>善用窗口函数

·能灵活处理很多复杂问题,组内排序,  TopN,RowIdx等

·Partition by  开窗,order by排序

>尽量避免  Order by

·Order by  会触发全局排序,只能单点运行,效率极低

·如业务允许,可改成   distribute by + sort by

>尽量避免非等值连接

关键路径优化

>全链路关键节点优化

·找出关键路径上所有   SQL,记录运行时间

·优化核心节点和依赖节点

·合理调度,理清并行和串行关系

>单  SQL  作业内部计算节点优化

·找出  SQL  所有  task  运行时间的关键路径

·对关键节点进行优化,适当多分配一些资源

image.png

长周期指标统计优化

>问题

·时间太长,累计的数据量太多,如对一年的数据进行统计

·单  sql  消耗太多资源,万级别的并发度,甚至超限,运行失败

·容易产生数据倾斜和各种长尾,运行不稳定

>解决方案

·选取适当的时间维度建立分区表和增量表

·根据适当的时间维度做中间轻度汇总,生成中间表·基于中间汇总表做关联,避免明细关联

·基于中间汇总表做总汇总

·也可以根据上次的汇总做累计汇总

其他常见问题

>长尾现象

·数据倾斜了

·单台机器资源竞抢严重,如CPU负载过重

·读写慢,碰到慢盘

·单个  task  运行失败,重跑

> 内存使用过多

·确认是否数据倾斜,解决方案前面有讲述

·单行数据  size  或者字段  size  是否存在超大值,比如  wm_concat,map  等操作

·自定义  UDF  使用内存过多

>SQL  处理过程中产生的数据量过大

·数据膨胀,如  UDTF,join  等操作导致

·跨  task  的数据量暴涨,甚至超限

>UDF  超时或  fail

·用户需检查自定义代码的逻辑是否存在性能瓶颈点

·是否有网络或者文件操作或权限问题

> Job  等待运行时间较长

·Quota  组资源不足,增加资源或者释放占用的资源

·集群超负载运行,等待资源释放

总结

>宏观把握最关键

·熟悉掌控全链路所有  SQL  运行特征

·合理分配资源到每个任务上,资源使用效率最大化,时间运行最小化

>SQL  调优靠工具

·合理建表

· 熟悉  SQL  运行所有  Task  的资源使用和运行时长

·优化长尾和  Task  链路

>微观调控看细节

·了解  SQL  每个算子的含义和基本运行机制

·熟悉在不同场景下合理选择具备优势的算子完成计算

>熟悉使用工具调查问题

·Explain  命令, logview  使用等

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
相关文章
|
1天前
|
SQL 分布式计算 DataWorks
DataWorks产品使用合集之未保存的ODPS SQL语句该如何找回
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
27天前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
45 13
|
19天前
|
SQL 安全 数据库
如何优化SQL查询
【8月更文挑战第1天】如何优化SQL查询
26 2
|
19天前
|
SQL 缓存 关系型数据库
SQL如何优化查询?
【8月更文挑战第1天】SQL如何优化查询?
29 1
|
26天前
|
SQL
SQL开发问题之当从数据源读取多个字段时优化 COUNT(DISTINCT ...) 的查询的问题如何解决
SQL开发问题之当从数据源读取多个字段时优化 COUNT(DISTINCT ...) 的查询的问题如何解决
|
26天前
|
SQL 分布式计算 MaxCompute
SQL开发问题之对于ODPS中的UNION操作,执行计划的问题如何解决
SQL开发问题之对于ODPS中的UNION操作,执行计划的问题如何解决
|
23天前
|
SQL 数据采集 数据管理
SQL数据:探索、管理与优化的全面解析
在信息化时代,数据成为企业核心资产。本文探讨SQL在数据探索、管理与优化中的作用:使用DESC、SELECT了解数据集;评估数据质量;发现数据特征。管理方面,涵盖数据存储、检索、更新与维护。优化则涉及索引、查询及数据库设计,确保高性能和效率。掌握SQL能有效挖掘数据价值,支持企业决策与创新。
27 1
|
2天前
|
SQL 数据库 数据库管理
SQL查询是否都需要解析:深入解析SQL执行流程与优化技巧
在数据库管理系统中,SQL(Structured Query Language)查询是用户与数据库交互的主要方式
|
25天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之SQL查询该如何优化
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
4天前
|
存储 SQL JSON
一些MaxCompute日常优化案例分享
MaxCompute优化是一个多样而又重要的过程,优化过程需要能够深入理解ODPS的工作原理和内部机制,本文总结了以下几个日常优化案例,最终优化手段可能非常简单,但其中的分析过程较为重要,希望对大家有所启发。

热门文章

最新文章