AI 助理
备案控制台
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL SQL参考 优化器

优化器

更新时间: 2024-05-22 14:47:17

MaxCompute的优化器是基于代价的优化器，需要基于数据的一些特征（即元数据），例如行数、字符串平均长度，准确估算代价。本文为您介绍MaxCompute收集元数据的方法，为优化查询性能提供帮助。

背景信息

如果获取不到准确的元数据，优化器会对代价产生误判，生成不良的执行计划，因此元数据对于优化器至关重要。表的元数据主要是通过对其数据收集统计信息（Column stats）来获取，该元数据是推算其它元数据的基础。

MaxCompute提供了如下两种收集方式：

  • 异步收集框架（Analyze）：用户异步通过analyze命令收集。需要用户主动收集。

    说明

    MaxCompute客户端版本要求在0.35以上。

  • 同步收集框架（Freeride）：在数据生成的同时，自动收集Column stats，更加自动化，但对查询时延有影响。

MaxCompute对不同数据类型的数据收集的Column stats指标如下。

Column stats指标/数据类型

数值类型（TINYINT、SMALLINT、INT、BIGINT、DOUBLE、DECIMAL、NUMERIC）

字符类型（STRING、VARCHAR、CHAR）

二进制类型（BINARY）

布尔类型（BOOLEAN）

日期类型（TIMESTAMP、DATE、INTERVAL）

复杂类型（MAP、STRUCT、ARRAY）

min（最小值）

Y

N

N

N

Y

N

max（最大值）

Y

N

N

N

Y

N

nNulls（空值个数）

Y

Y

Y

Y

Y

Y

avgColLen（平均列长度）

N

Y

Y

N

N

N

maxColLen（最大列长度）

N

Y

Y

N

N

N

ndv（不同值个数）

Y

Y

Y

Y

Y

N

topK（出现频率最高的前K个值）

Y

Y

Y

Y

Y

N

说明

Y表示支持，N表示不支持。

使用场景

Column stats指标的使用场景如下：

Column stats指标

功能

场景

说明

min（最小值）或max（最大值）

获取最小值或最大值提升性能优化准确率。

场景1：估算输出记录数。

只提供数据类型时，值域很大。当提供了最大最小值时，优化器可以对过滤条件的选择度有更合理的估计，从而提供更优的执行计划。

场景2：将过滤条件下推至存储层，减少读取的数据量。

在MaxCompute中，过滤条件a < -90可以被下推至存储层，以减少数据的读取。而a + 100 < 10则无法完成下推。如果考虑a的溢出，这两个过滤条件是不等价的。假设a有最大值，转换就变成等价的。因此，如果有最大值最小值，可以对更多的过滤条件进行下推，减少读取的数据量，节省费用。

nNulls（空值个数）

根据是否为空值信息提高判断效率。

场景1：运行作业时减少NULL判断。

在运行作业时，对于任何类型数据都需要判断是否为NULL，如果能准确得到nNulls=0，此判断逻辑可以被忽略以提高计算性能。

场景2：基于过滤条件裁剪数据。

如果整列数据值都为NULL，一般的过滤条件可以直接变成always false条件而被裁剪，提高剪裁效率。

avgColLen（平均列长度）或maxColLen（最大列长度）

获取列长度信息，预估资源消耗，减少Shuffle。

场景1：Hash聚簇表内存估计。

例如，根据avgColLen，可以估计变长字段的内存消耗，得到Record的内存消耗。从而可以选择性进行Auto Mapjoin，即建立Hash聚簇表Broadcast处理机制，减少一次Shuffle操作。对于输入为大表的场景，减少Shuffle的代价非常明显，能有效提升性能。

场景2：减少Shuffle的数据量。

无。

ndv（不同值个数）

根据基数信息提高执行计划的质量。

场景1：JOIN的输出记录数推算。

  • 数据膨胀：当两个表Join key的ndv都比行数小很多时，证明数据大量重复，大概率存在数据膨胀，优化器可以采取相关措施来规避数据膨胀带来的问题。

  • 数据过滤：当小表的ndv比大表小很多时，说明执行JOIN操作后，大表的数据会被大量过滤。优化器可以根据这些判断给出相关优化决策。

场景2：JOIN排序。

基于估算的输出记录数，优化器还可以自动调整JOIN顺序。例如把会有数据过滤的JOIN操作往前调，把有数据膨胀的JOIN操作往后调。

topK（出现频率最高的前K个值）

估算数据分布减少数据倾斜带来的性能影响。

场景1：倾斜数据进行JOIN的优化处理。

当JOIN的输入均较大，且无法通过Mapjoin方式将非大表全装载至内存时，在一路中某些数据存在倾斜状态，而其它路比较有限。MaxCompute可以自动转换成Skew Data使用MAP JOIN处理，非倾斜数据使用MERGE JOIN进行处理，最后再合并两部分的计算结果。此功能对于大数据量JOIN，收益非常明显，降低失败后的人工处理成本。

场景2：估算输出记录数。

利用ndv、min、max进行输出记录数的估算是基于数据“平均”的假设。在用户数据存在明显倾斜时，基于前面假设的推论会存在“失真”。需要对倾斜数据进行特殊处理，而其它数据利用平均假设更合适。

Analyze使用说明

收集Column Stats

以分区表和非分区表为例介绍Analyze使用方法。

  • 非分区表

    支持对指定的列或全部列收集Column Stats。

    1. 通过MaxCompute客户端创建一张非分区表analyze2_test，命令示例如下：

      create table if not exists analyze2_test (tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, double1 double, decimal1 decimal, decimal2 decimal(20,10), string1 string, varchar1 varchar(10), boolean1 boolean, timestamp1 timestamp, datetime1 datetime ) lifecycle 30;

    2. 向表中插入数据，命令示例如下：

      insert overwrite table analyze2_test select * from values (1Y, 20S, 4, 8L, 123452.3, 12.4, 52.5, 'str1', 'str21', false, timestamp '2018-09-17 00:00:00', datetime '2018-09-17 00:59:59') ,(10Y, 2S, 7, 11111118L, 67892.3, 22.4, 42.5, 'str12', 'str200', true, timestamp '2018-09-17 00:00:00', datetime '2018-09-16 00:59:59') ,(20Y, 7S, 4, 2222228L, 12.3, 2.4, 2.57, 'str123', 'str2', false, timestamp '2018-09-18 00:00:00', datetime '2018-09-17 00:59:59') ,(null, null, null, null, null, null, null, null, null, null, null , null) as t(tinyint1, smallint1, int1, bigint1, double1, decimal1, decimal2, string1, varchar1, boolean1, timestamp1, datetime1);

    3. 执行analyze命令收集某一列、多列或全部列的Column Stats，命令示例如下：

      --收集tinyint1列的Column Stats。
analyze table analyze2_test compute statistics for columns (tinyint1); 

--收集smallint1、string1、boolean1和timestamp1列的Column Stats。
analyze table analyze2_test compute statistics for columns (smallint1, string1, boolean1, timestamp1);

--收集全部列的Column Stats。
analyze table analyze2_test compute statistics for columns;

    4. 执行show statistic命令测试Column Stats收集结果，命令示例如下：

      --测试tinyint1列的Column Stats收集结果。
show statistic analyze2_test columns (tinyint1);

--测试smallint1、string1、boolean1和timestamp1列的Column Stats收集结果。
show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);

--测试全部列的Column Stats收集结果。
show statistic analyze2_test columns;

      返回结果如下：

      --tinyint1列的Column Stats收集结果。
ID = 20201126085225150gnqo****
tinyint1:MaxValue:      20                   --对应max。
tinyint1:DistinctNum:   4.0                  --对应ndv。
tinyint1:MinValue:      1                    --对应min。
tinyint1:NullNum:       1.0                  --对应nNulls。
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}      --对应topK。10=1.0表示列值10出现的频次为1。topK最多显示前20个最高频次的值。

--smallint1、string1、boolean1和timestamp1列的Column Stats收集结果。
ID = 20201126091636149gxgf****
smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

string1:MaxLength       6.0                  --对应maxColLen。
string1:AvgLength:      3.0                  --对应avgColLen。
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

--全部列的Column Stats收集结果。
ID = 20201126092022636gzm1****
tinyint1:MaxValue:      20
tinyint1:DistinctNum:   4.0
tinyint1:MinValue:      1
tinyint1:NullNum:       1.0
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}

smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

int1:MaxValue:  7
int1:DistinctNum:       3.0
int1:MinValue:  4
int1:NullNum:   1.0
int1:TopK:      {4=2.0, 7=1.0}

bigint1:MaxValue:       11111118
bigint1:DistinctNum:    4.0
bigint1:MinValue:       8
bigint1:NullNum:        1.0
bigint1:TopK:   {8=1.0, 2222228=1.0, 11111118=1.0}

double1:MaxValue:       123452.3
double1:DistinctNum:    4.0
double1:MinValue:       12.3
double1:NullNum:        1.0
double1:TopK:   {12.3=1.0, 67892.3=1.0, 123452.3=1.0}

decimal1:MaxValue:      22.4
decimal1:DistinctNum:   4.0
decimal1:MinValue:      2.4
decimal1:NullNum:       1.0
decimal1:TopK:  {2.4=1.0, 12.4=1.0, 22.4=1.0}

decimal2:MaxValue:      52.5
decimal2:DistinctNum:   4.0
decimal2:MinValue:      2.57
decimal2:NullNum:       1.0
decimal2:TopK:  {2.57=1.0, 42.5=1.0, 52.5=1.0}

string1:MaxLength       6.0
string1:AvgLength:      3.0
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

varchar1:MaxLength      6.0
varchar1:AvgLength:     3.0
varchar1:DistinctNum:   4.0
varchar1:NullNum:       1.0
varchar1:TopK:  {str2=1.0, str200=1.0, str21=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

datetime1:DistinctNum:  3.0
datetime1:NullNum:      1.0
datetime1:TopK:         {1537117199000=2.0, 1537030799000=1.0}

  • 分区表

    支持对指定的某个分区收集Column Stats。

    1. 通过MaxCompute客户端创建一张分区表srcpart，命令示例如下：

      create table if not exists srcpart_test (key string, value string) partitioned by (ds string, hr string) lifecycle 30;

    2. 向表中插入数据，命令示例如下：

      insert into table srcpart_test partition(ds='20201220', hr='11') values ('123', 'val_123'), ('76', 'val_76'), ('447', 'val_447'), ('1234', 'val_1234');
insert into table srcpart_test partition(ds='20201220', hr='12') values ('3', 'val_3'), ('12331', 'val_12331'), ('42', 'val_42'), ('12', 'val_12');
insert into table srcpart_test partition(ds='20201221', hr='11') values ('543', 'val_543'), ('2', 'val_2'), ('4', 'val_4'), ('9', 'val_9');
insert into table srcpart_test partition(ds='20201221', hr='12') values ('23', 'val_23'), ('56', 'val_56'), ('4111', 'val_4111'), ('12333', 'val_12333');

    3. 执行analyze命令收集指定分区的Column Stats，命令示例如下：

      analyze table srcpart_test partition(ds='20201221') compute statistics for columns (key , value);

    4. 执行show statistic命令测试Column Stats收集结果，命令示例如下：

      show statistic srcpart_test partition (ds='20201221') columns (key , value);

      返回结果如下：

      ID = 20210105121800689g28p****
(ds=20201221,hr=11) key:MaxLength       3.0
(ds=20201221,hr=11) key:AvgLength:      1.0
(ds=20201221,hr=11) key:DistinctNum:    4.0
(ds=20201221,hr=11) key:NullNum:        0.0
(ds=20201221,hr=11) key:TopK:   {2=1.0, 4=1.0, 543=1.0, 9=1.0}

(ds=20201221,hr=11) value:MaxLength     7.0
(ds=20201221,hr=11) value:AvgLength:    5.0
(ds=20201221,hr=11) value:DistinctNum:  4.0
(ds=20201221,hr=11) value:NullNum:      0.0
(ds=20201221,hr=11) value:TopK:         {val_2=1.0, val_4=1.0, val_543=1.0, val_9=1.0}

(ds=20201221,hr=12) key:MaxLength       5.0
(ds=20201221,hr=12) key:AvgLength:      3.0
(ds=20201221,hr=12) key:DistinctNum:    4.0
(ds=20201221,hr=12) key:NullNum:        0.0
(ds=20201221,hr=12) key:TopK:   {12333=1.0, 23=1.0, 4111=1.0, 56=1.0}

(ds=20201221,hr=12) value:MaxLength     9.0
(ds=20201221,hr=12) value:AvgLength:    7.0
(ds=20201221,hr=12) value:DistinctNum:  4.0
(ds=20201221,hr=12) value:NullNum:      0.0
(ds=20201221,hr=12) value:TopK:         {val_12333=1.0, val_23=1.0, val_4111=1.0, val_56=1.0}

刷新元数据中表的记录数

MaxCompute中多种任务都可能会影响表的记录数，而大部分任务只统计任务本身影响的记录数，并且一些任务因为分布式任务的动态性和数据更新关系在时间上的不确定性，并不保证对影响的记录数统计全部准确，因此可以使用Analyze命令刷新元数据中表的记录数统计值，保证记录数的准确性。表的记录数支持在DataWorks数据地图中查看，详情请参见查看表详情。

  • 刷新全表的记录数。

    set odps.sql.analyze.table.stats=only; 
analyze table <table_name> compute statistics for columns;

    table_name为表名称。

  • 刷新表中某列的记录数。

    set odps.sql.analyze.table.stats=only; 
analyze table <table_name> compute statistics for columns (<column_name>);

    table_name为表名称，column_name为列名称。

  • 刷新分区中某列的记录数。

    set odps.sql.analyze.table.stats=only; 
analyze table <table_name> partition(<pt_spec>) compute statistics for columns (<column_name>);

    table_name为表名称，pt_spec为分区值，column_name为列名称。

Freeride使用说明

您需要在Session级别同时执行如下两个命令设置属性：

  • set odps.optimizer.stat.collect.auto=true;：启用Freeride功能，自动收集表的Column Stats。

  • set odps.optimizer.stat.collect.plan=xx;：配置收集计划，收集指定列的指定Column Stats指标。

    --收集target_table表中列名为key的avgColLen指标。
set odps.optimizer.stat.collect.plan={"target_table":"{\"key\":\"AVG_COL_LEN\"}"}

--收集target_table表中列名为s_binary的min和max，以及列名为s_int的topK和nNulls指标。
set odps.optimizer.stat.collect.plan={"target_table":"{\"s_binary\":\"MIN,MAX\",\"s_int\":\"TOPK,NULLS\"}"};
说明

如果出现配置上述属性后，无法收集到信息的问题，可能是Freeride功能未生效。您需要查看Logview的json summary页签中是否可以找到odps.optimizer.stat.collect.auto属性。如果没有找到该属性，说明当前服务器版本较低，无法使用该功能。MaxCompute会陆续将服务器版本升级至支持Freeride功能的版本。

Column Stats指标在set odps.optimizer.stat.collect.plan=xx;中的标识对照关系为：

  • min：MIN

  • max：MAX

  • nNulls：NULLS

  • avgColLen：AVG_COL_LEN

  • maxColLen：MAX_COL_LEN

  • ndv：NDV

  • topK：TOPK

MaxCompute支持通过create table、insert into、insert overwrite三种方式触发Freeride收集Column Stats。

为呈现上述三种方式的实现，假设先创建一个源表src_test并插入数据，命令示例如下：

create table if not exists src_test (key string, value string);
insert overwrite table src_test values ('100', 'val_100'), ('100', 'val_50'), ('200', 'val_200'), ('200', 'val_300');

  • create table：在创建目标表target的同时，收集对应的Column Stats。命令示例如下：

    --创建目标表。
set odps.optimizer.stat.collect.auto=true;
set odps.optimizer.stat.collect.plan={"target_test":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
create table target_test as select key, value from src_test;
--测试Column Stats收集结果。
show statistic target_test columns;

    返回结果如下：

    key:AvgLength: 3.0
key:NullNum:  0.0

  • insert into：在使用insert into追加数据时，收集对应的Column Stats。命令示例如下：

    --创建一个目标表。
create table freeride_insert_into_table like src_test;
--追加数据。
set odps.optimizer.stat.collect.auto=true;
set odps.optimizer.stat.collect.plan={"freeride_insert_into_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
insert into table freeride_insert_into_table select key, value from src order by key, value limit 10;
--测试Column Stats收集结果。
show statistic freeride_insert_into_table columns;

  • insert overwrite：在使用insert overwrite覆盖数据时，收集对应的Column Stats。命令示例如下：

    --创建一个目标表。
create table freeride_insert_overwrite_table like src_test;
--覆盖数据。
set odps.optimizer.stat.collect.auto=true;
set odps.optimizer.stat.collect.plan={"freeride_insert_overwrite_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
insert overwrite table freeride_insert_overwrite_table select key, value from src_test order by key, value limit 10;
--测试Column Stats收集结果。
show statistic freeride_insert_overwrite_table columns;
上一篇: Time travel查询与增量查询 下一篇: Histogram
文档推荐
阿里云首页 云原生大数据计算服务 MaxCompute 相关技术圈