MaxCompute SQL 优化

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

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

内容简介：

1.SQL 成本计算

2.SQL IO 读取优化

3.SQL 计算优化

4.SQL 整体优化

SQL 运行过程

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

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 中相同的子查询也会合并成一个源

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

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

动态分区

>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 运行时间的关键路径

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

长周期指标统计优化

>问题

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

·单 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 SQL 优化 | 学习笔记