全部產品
Search
文件中心

MaxCompute:SQL調優

更新時間:Nov 16, 2024

本文為您介紹常見的SQL問題以及最佳化樣本。

並行度最佳化

並行度是衡量並行計算程度的一個指標,從執行計畫上來看,例如ID為M1的任務,使用1000個Instance來執行,我們就說M1的並行度是1000。合理地設定並調整任務並行度,可以使任務執行效率更高。

並行度最佳化情境如下。

強制一個Instance執行

某些操作強制一個Instance執行任務,例如:

  • 做彙總的時候,沒有進行group by或者group by一個常量。

  • 視窗函數的over語句裡指定partition by一個常量。

  • SQL中指定distribute bycluster by一個常量。

解決方案:針對此情形,建議您檢查這些操作是否必要,能否去掉,盡量取消類似操作,避免強制一個Instance執行任務。

Instance數量過多或過少

重要

調整並行度不一定是越多越好,Instance數量過多會從如下兩個方面影響執行速度:

  • Instance越多,等待資源的時間越長,排隊次數也更多。

  • 每個Instance初始化需要時間,並行度越高,初始化佔用的總時間就越長,有效執行時間佔比就越低。

以下情形會導致使用很多Instance:

  • 需要讀取很多小分區的資料,例如一個資料查詢SQL語句讀取10000個分區,那麼系統會強制使用10000個Instance。

    解決方案:您需要設計SQL,減少分區的數量,可以從進行分區裁剪、篩除不需要讀的分區、將大作業拆分成小作業方面進行考慮。

  • 每次讀取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;
    • 方法3:在表層級按照大小、行數或指定並行度進行切分。

    由於方法1中odps.sql.mapper.split.size只支援Mapper Stage的整體設定,且最低為1 MB,必要時,您可以根據表的維度調整並行度,尤其是在表中每行資料的size較小,而後續計算負擔較重的情況下,可以減少平行處理的行數,從而提高任務的並行度。

    調整並行度的方式如下:

    • 設定表層級單個平行處理的分區資料大小。

      SET odps.sql.split.size = {"table1": 1024, "table2": 512};
    • 設定表層級單個平行處理的行數。

      SET odps.sql.split.row.count = {"table1": 100, "table2": 500};
    • 設定表層級的並行度。

      SET odps.sql.split.dop = {"table1": 1, "table2": 5};
    說明

    odps.sql.split.row.countodps.sql.split.dop只能用於內部表、非事務表和非聚簇表。

  • 非讀表的Task

    主要有如下三種方式調整並行度:

    • 方法1:調整odps.stage.reducer.num值。使用如下命令強制設定Reducer並行度,該設定將影響所有相關的Task。

      -- 設定Reduce Task的instance數量
      -- 可調整區間為[1,99999]
      SET odps.stage.reducer.num=<value>;
    • 方法2:調整odps.stage.joiner.num值。使用如下命令強制設定Reducer並行度,會影響所有相關的Task。

      -- 設定Joiner Task的instance數量
      -- 可調整區間為[1,99999]
      SET odps.stage.joiner.num=<value>;
    • 方法3:調整odps.sql.mapper.split.size值。

      非讀表Task的並行度會受到輸入Task的並行度影響,通過調整讀表Task的並行度間接調整非讀表Task的並行度。

視窗函數最佳化

如果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;