开发者学堂课程【MaxCompute 行业应用及调优：MaxcomputerSQL 优化】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：
https://developer.aliyun.com/learning/course/88/detail/1350
MaxcomputerSQL 优化
内容介绍
一.SQL 成本计算
二.SQL IO 读取优化
三.SQL 计算优化
四.整体优化
一、SQL 计算成本
SQL 运行过程：
用户写了一个 sql 然后就会传入到我们的平台，就会有一个编译器进行语法解析，语法解析之后就会生成一个逻辑 plan ，
然后会有一个优化器，对它进行各种逻辑优化和物理优化，最终生成一个可执行的plan，
然后这个 plan 就会把它交给资源调度器，调度器启动作业管理器来管理这个 plan 的执行，
作业管理器总共会启动两个 task，来执行这个 sql，两个 task 直接是有数据依赖关系的，
如 task1 执行完了 task2 才能执行，每一个 task 会有很多阶层来执行，数据文件的读写就会使用分布式文件系统，来进行文件的读写，这就是整个 sql 的运行的整个机制。
我们如何优化一个sql，让优化器更好的识别它，更好的做出更优化的执行 plan，并且能在执行计算阶段，更好的节省资源。
计算方法：
①读取 IO 数据量，从你读取的表数据出发。
②Sql 复杂度由 Sql 里面多少耗资源的算子组成，越多复杂度越高，
算子比较耗资源的如：
join/Groud by/order by/distinct/window func/insert into因此优化 SQL 的过程，实际上就是要尽可能减少 IO 读取，尽可能减少计算资源使用，尽可能降低 SQL 复杂度，尽可能提升运行速度。
二.优化过程
SQL IO 读取优化
1.建立分区
建立分区表，一般数据比较大就是要建表，但是分区层次不要太多，允许的最大量是六级，一般三级就可以，太多就把数据切得太小了。
2.分区裁剪
①避免全表扫描，减少资源浪费
②case：where pt=xxx and region=xxx
③分区尽量按层级顺序裁剪
④分区值尽量常量化，避免不可确定值，如 UDF
⑤分区值尽量避免引用列的表达式计算或者子查询
3.写分区
①写入静态分区，优化数据存储
②避免动态分区，防止文件过多和计算长尾
4.只引用有效列
①避免 select from xxx
②常量代替引用列，如 count（c）_＞count（1）//c not null
5.尽可能 pushdown 过滤条件
①where a＞and（b＞1or c ＜1）
6.limit N.只读取需要的数据然后就停止。
6.源表合并
1.合并不同 Sql，一读多计算
①读取相同源表可合并，节省 IQ 和计算资源
②对源表统计多种指标计算或者筛选不同数据处理
③避免规模过大，运行时间过长
2.Multi insert，动态分区，一读多写
①同一 sql 读取相同源表，系统会优化只读取一次
②资源足够，也可以考虑拆分 sql，读取和计算更好并行，资源换时间
3.子查询合并
①对于 sql 中相同的子查询也会合并成一个源
②尽可能保持子查询语句一样，触发合并
三.SQL 计算优化
Join连接_Mapjoin优化
1.运行原理
①小表数据全部加载内存
②读取大表的每个task加载一份小表数据
2.Case1：大小表数据相差大，小表满足限制条件
①静态维度表，轻度汇总表等
②大小表数据量相差越大越有优势，越能节省 IO
3.Case2.不等值条件，or 条件等复杂 on 条件
①显性指定 mapioin
②不支持其他关联方式
4.Case3：autoMapjoin
Case4：数据倾斜，导致长尾
表现：极少数joiner进程处理数据量远大于其他进程，运行时间过长
原因：
①热点 key：Null值，空值，缺省值，大 key 值等
②数据分发不均衡：数据属性，压缩率不均等
③建表不合理
操作：
热点 key 尽量先去重或过滤无效值
对一些热点缺省无效值加一个随机前缀，分散数据
修改 SQL 隔离热点 key 和非热点 key 分别做关联，再 union all 起来
显性指定mapjoin
增加源表作为大表的并发度：set odps.sql.mapper.split.size=128
增加子查询作为大表并发度：set odps.sql.joiner.instances=1111
限制条件：
①left outer join 的左表必须是大表
②right outer join 的右表必须是大表
③inner join 左表或右表均可以作为大表
④full outer join 不能使用 Mapjoin
⑤Mapjoin 支持小表为子查询
⑥使用 Mapjoin 时，需要引用小表或是子查询时，需要引用别名
在 Mapjoin 中，可以使用不等值连接或者使用or连接多个条件
最多支持指定8张小表，否则报语法错误
⑦如果使用 Mapjoin，则所用小表占用的内存总和不得超过512MB
多个表 join 时，最左边的两个表不能同时是 Mapjoin 的表
四.整体优化
①全链路关键节点优化
1.找出关键节点路径上所有 SQL，记录运行时间
2.优化核心节点和依赖节点
3.合理调节，理清并行和串行关系
②单 SQL 作业内部计算节点优化
1.找出 SQL 所有 task 运行时间的关键路径
2.对关键节点进行优化，适当多分配一些资源
长周期指标统计优化
③问题
1.时间太长，累计的数据量太多，如对一年的数据进行统计
2.单 sql 消耗太多资源，万级别的并发度，甚至超限呢，运行失败
3.容易产生数据倾斜和各种长尾，运行不稳定
④解决方案
1.选取适当的时间维度建立分区表和增量表
2.根据适当的时间维度做中间轻度汇总，生成中间表
3.基于中间汇总表做关联，避免明细关联
4.基于中间汇总表做总汇总
5.也可以根据上次的汇总做累计汇总
其他常见问题
1.内存使用过多
①确认是否数据倾斜，解决方案前面有讲述
②单行数据 size 或者字段 size 是否存在超大值，比如 wm_concat，map 等操作
③自定义 UDF 使用内存过多
2.SQL 处理过程中产生的数据量过大
①数据膨胀，如 UDTF，join 等操作导致
②跨 task 的数据量暴涨，甚至超限
3.UDF 超时或 fail
①用户需检查自定义代码的逻辑是否存在性能瓶颈点
②是否有网络或者文件操作或权益问题
4.JOb 等待运行时间较长
①Quota 组资源不足，增加资源或者释放占用的资源
②集群超负载运行，等待资源释放
5.长尾现象
①数据倾斜
②单台机器资源竞抢严重，如 CPU 负载过重
③读写慢，碰到慢盘
④单个 task 运行失败，重跑
