开发者学堂课程【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;
SQL 成本计算
> 计算成本 <-读取 IO数据量*Sql复杂度
> Sql 复杂度∶Join/ Group By/Order By/Distinct /window func/ Insert into
因此优化 SQ L 的过程，实际上就是要尽可能减少IO读取，尽可能减少计算资源使用，尽可能降低 SQL 复杂度，尽可能提升运行速度
So, Let's go!
表分区优化
>建立分区表
·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∶不等值条件，o r 条件等复杂 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，则所有小表占用的内存总和不得超过 5 12MB.
· 多个表 Join 时，最左边的两个表不能同时是 MapJoin 的表。
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 来比较相等
·悲剧情况下，可能触发数据倾斜
聚合运算优化
>少用 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
>执行影响∶小文件过多
·单 tas k 处理文件数目有限制
·需要更多的 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 负载过重
·读写慢，碰到慢盘
·单个 t ask 运行失败，重跑
> 内存使用过多
·确认是否数据倾斜，解决方案前面有讲述
·单行数据size或者字段 size 是否存在超大值，比如 wm_concat，map 等操作
·自定义 UDF 使用内存过多
>SQL 处理过程中产生的数据量过大
·数据膨胀，如 UDTF，join 等操作导致
·跨 task 的数据量暴涨，甚至超限
>UDF 超时或 fail
·用户需检查自定义代码的逻辑是否存在性能瓶颈点
·是否有网络或者文件操作或权限问题
> Job 等待运行时间较长
·Quota 组资源不足，增加资源或者释放占用的资源
·集群超负载运行，等待资源释放
总结
>宏观把握最关键
·熟悉掌控全链路所有 SQ L 运行特征
·合理分配资源到每个任务上，资源使用效率最大化，时间运行最小化
>SQL 调优靠工具
·合理建表
· 熟悉 SQL 运行所有 Task 的资源使用和运行时长
·优化长尾 和 Tas k 链路
>微观调控看细节
·了解 SQL 每个算子的含义和基本运行机制
·熟悉在不同场景下合理选择具备优势的算子完成计算
>熟悉使用工具调查问题
·Explain 命令， logview 使用等