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 Ddata使用MAP JOIN处理,非倾斜数据使用MERGE JOIN进行处理,最后再合并两部分的计算结果。此功能对于大数据量JOIN,收益非常明显,降低失败后的人工处理成本。
场景2:估算输出记录数。 利用ndv、min、max进行输出记录数的估算是基于数据“平均”的假设。在用户数据存在明显倾斜时,基于前面假设的推论会存在“失真”。需要对倾斜数据进行特殊处理,而其它数据利用平均假设更合适。

Analyze使用说明

以分区表和非分区表为例介绍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}

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 tableinsert intoinsert 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;