SQL调优 - 云原生大数据计算服务 MaxCompute

本文为您介绍常见的SQL问题以及优化示例。

并行度优化

并行度是衡量并行计算并行程度的一个指标，从执行计划上来看，例如ID为M1的任务，使用1000个Instance来执行，我们就说M1的并行度是1000。需要注意的是：调整并行度不一定是越多越好，Instance数量过多会从如下两个方面影响执行速度：

Instance越多，等待资源需要更长的时间，排队次数也更多。
每个Instance初始化需要时间，并行度越高，初始化占用的总时间就越多，有效的执行时间占比就越低。

并行度优化场景如下。

强制一个Instance执行
某些操作强制一个Instance执行任务，例如：
- 做聚合的时候，没有进行group by或者group by一个常量。
- 窗口函数的over语句里指定partition by一个常量。
- SQL中指定distribute by、cluster by一个常量。
解决方案：针对此情形，建议您检查这些操作是否必要，能否去掉，尽量取消类似操作，避免强制一个Instance执行任务。
Instance过多
以下一些情形会导致强制使用很多Instance：
- 需要读取很多小分区的数据：例如一个数据查询SQL语句读取10000个分区，那么系统会强制使用10000个Instance。
  解决方案：您需要设计SQL，减少分区的数量，可以从进行分区裁剪、筛除不需要读的分区、将大作业拆分成小作业方面进行考虑。
- 数据压缩比很高，解压后256 MB变成了好几百GB的数据，导致读入256MB数据，处理解压后会产生非常多的Instance。
  解决方案：使用如下命令调小单个并发处理的数据大小。
```
set odps.stage.mapper.split.size=<256>;
set odps.stage.reducer.num =<并发数>;
```
- 每次读取256 MB数据太少，导致Instance的执行时间太短，而由于输入数据很大，反而导致了并行度过大，使Instance大多数时间在排队等资源，需要调大单个Instance读取的数据量。
  解决方案：使用如下命令调大单个并发处理的数据大小。
```
set odps.stage.mapper.split.size=<256>;
set odps.stage.reducer.num =<并发数>;
```
Instance数量设置方法
- 读表的Task
  - 方法1：通过设置参数调整并发度。
```
-- 设定一个map的最大数据输入量，单位MB
-- 默认256，区间[1，Integer.MAX_VALUE]
set odps.sql.mapper.split.size=<value>;
```
  - 方法2：MaxCompute提供split size hint方式，可以针对单个读表操作来调整并发度。
```
--设置split size大小为1MB，此hint会在读src表时，按照1MB的大小来切分task
select a.key from src a /*+split_size(1)*/ join src2 b on a.key=b.key;
```
- 非读表的Task
  主要有如下三种方式调整并发度：
  - 调整odps.sql.mapper.split.size值：
    非读表Task的并发度会受到输入Task的并行度影响，通过调整读表Task的并发度间接调整非读表Task的并发度。
  - 调整odps.sql.reducer.instances值：
    使用如下命令强制设置Reducer并发度，会影响所有的相关Task。
    -- 设定Reduce Task的instance数量 -- 可调整区间为[1，99999] set odps.sql.reducer.instances=<value>;
  - 调整odps.stage.joiner.num值：
    使用如下命令强制设置Joiner并发度，会影响所有的相关Task。
    -- 设定Joiner Task的instance数量 -- 可调整区间为[1，99999] set odps.stage.joiner.num=<value>;

窗口函数优化

如果SQL语句中使用了窗口函数，通常每个窗口函数会形成一个Reduce作业。如果窗口函数较多，会消耗过多的资源。您可以对符合下述条件的窗口函数进行优化：

窗口函数在OVER关键字后面要完全相同，要有相同的分组和排序条件。
多个窗口函数在同一层SQL中执行。

符合上述2个条件的窗口函数会合并为一个Reduce执行。SQL示例如下所示。

SELECT
RANK()OVER(PARTITION BY A ORDER BY B desc) AS RANK,
ROW_NUMBER()OVER(PARTITION BY A ORDER BY B desc) AS row_num
FROM MyTable;

子查询优化

子查询如下所示。

SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);

当此语句中的table_b子查询返回的col1的个数超过9999个时，系统会报错为records returned from subquery exceeded limit of 9999。此时您可以使用Join语句来代替，如下所示。

SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1)

说明

如果没有使用DISTINCT关键字，而子查询表c返回的结果中有相同的col1的值，可能会导致a表的结果数变多。
DISTINCT关键字会导致查询在同一个Worker中执行。如果子查询数据量较大，会导致查询比较慢。
如果业务上已经确保子查询中col1列值无重复，您可以删除DISTINCT关键字，以提高性能。

Join语句优化

当两个表进行Join操作时，建议在如下位置使用WHERE子句：

主表的分区限制条件可以写在WHERE子句中（建议先用子查询过滤）。
主表的WHERE子句建议写在SQL语句最后。
从表分区限制条件不要写在WHERE子句中，建议写在ON条件或者子查询中。

示例如下。

SELECT * FROM A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id WHERE A.dt=20150301；
SELECT * FROM A JOIN B ON B.id=A.id WHERE B.dt=20150301；--不建议使用。此语句会先执行Join操作后进行分区裁剪，导致数据量变大，性能下降。
SELECT * FROM (SELECT * FROM A WHERE dt=20150301)A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id；

聚合函数优化

使用wm_concat函数替代collect_list函数，实现聚合函数的优化，使用示例如下。

-- collect_list实现
select concat_ws(',', sort_array(collect_list(key))) from src;
-- wm_concat实现更优
select wm_concat(',', key) WITHIN group (order by key) from src;


-- collect_list实现
select array_join(collect_list(key), ',') from src;
-- wm_concat实现更优
select wm_concat(',', key) from src;