全部產品
Search

搜尋本產品

    MaxCompute：最佳化器

    文件中心

    MaxCompute：最佳化器

    更新時間：Jun 08, 2024

    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;