本文為您介紹如何評估分區剪裁合理性。
背景資訊
MaxCompute分區表是指在建立表時指定分區空間,即指定表內的幾個欄位作為分區列。使用資料時,如果指定了需要訪問的分區名稱,則只會讀取相應的分區,避免全表掃描,提高處理效率,降低費用。
分區剪裁是指對分區列指定過濾條件,使得SQL執行時只用讀取表分區的部分資料,避免全表掃描引起的資料錯誤及資源浪費。但是分區失效的情況會經常發生。
本文將從以下兩方面介紹分區剪裁:
- 判斷分區剪裁是否生效。
- 分區剪裁失效的情境分析。
判斷分區剪裁是否生效
通過EXPLAIN命令查看SQL執行計畫,用於判斷SQL中的分區剪裁是否生效。
- 分區剪裁未生效。
explain select seller_id from xxxxx_trd_slr_ord_1d where ds=rand();
從執行計畫中可見,SQL讀取了表的1344個分區,即該表的所有分區。
- 分區剪裁生效
explain select seller_id from xxxxx_trd_slr_ord_1d where ds='20150801';
從執行計畫中可見,SQL唯讀取了表的20150801分區。
分區剪裁失效的情境分析
- 自訂函數導致分區剪裁失效
當分區剪裁的條件中使用了使用者自訂函數(或者部分內建函數)時,分區剪裁失效。所以,對於分區值的限定,如果使用了非常規函數,建議您使用Explain命令查看執行計畫,確定分區剪裁是否生效。
explain select ... from xxxxx_base2_brd_ind_cw where ds = concat(SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 1), SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 2))
說明 UDF已支援分區裁剪,詳情請參見WHERE子句(where_condition)文中的說明。 - 使用JOIN時分區剪裁失效
在SQL語句中使用JOIN進行關聯時:
- 如果分區剪裁條件放在WHERE子句中,則分區剪裁會生效。
- 如果分區剪裁條件放在ON子句中,從表的分區剪裁會生效,主表則不會生效。
下面針對三種JOIN具體說明:- LEFT OUTER JOIN
- 分區剪裁條件均放在ON子句中
set odps.sql.allow.fullscan=true; explain select a.seller_id ,a.pay_ord_pbt_1d_001 from xxxxx_trd_slr_ord_1d a left outer join xxxxx_seller b on a.seller_id=b.user_id and a.ds='20150801' and b.ds='20150801';
由上圖可見,左表進行了全表掃描,只有右表的分區裁剪有效果。
- 分區剪裁條件均放在WHERE子句中
set odps.sql.allow.fullscan=true; explain select a.seller_id ,a.pay_ord_pbt_1d_001 from xxxxx_trd_slr_ord_1d a left outer join xxxxx_seller b on a.seller_id=b.user_id where a.ds='20150801' and b.ds='20150801';
由上圖可見,兩張表的分區裁剪都有效果。
- 分區剪裁條件均放在ON子句中
- RIGHT OUTER JOIN
與LEFT OUTER JOIN類似,如果分區剪裁條件放在ON子句中則只有RIGHT OUTER JOIN的左表生效。如果分區剪裁條件放在WHERE中,則兩張表都會生效。
- FULL OUTER JOIN
分區剪裁條件只有都放在WHERE子句中才會生效,放在ON子句中都不會生效。
注意事項
- 分區剪裁如果失效影響較大,且不容易發現。因此,建議在代碼提交時關注分區剪裁失效問題。
- 自訂函數中使用分區剪裁時,需要修改類或者在SQL語句前設定
set odps.sql.udf.ppr.deterministic = true;
。詳情請參見WHERE子句(where_condition)。