MaxComputeSql性能調優

04-29

部分用戶(尤其對外輸出)使用MaxCompute(原Odps)時，由於對產品的使用層面和執行層面了解程度不同，導致提交的任務執行時間過長、佔用了較多集群資源；嚴重的會導致失敗、不僅需要投入支持同學精力協助解決、也影響了用戶正常業務。合併整理部分性能提升方法方便支持用戶查詢和優化Sql，提高效率；部分需要原來手動調優的如mapjoin、ppd謂詞下推注意分區位置等原有的調優設置在不斷衍進的產品中都已實現了自動化調優、不同階段的產品調優參數和細節會有不一致、但是熟悉了調優思路和方法後可以做到舉一反三、逐步深入。

一. 整體思路

整體上，調優從底層到sql可以有多個層面的調優，隨著產品的不斷完善，部分調優已經實現了自動化。如果用戶能熟悉常見的調優機制和執行原理，在開發執行sql、業務邏輯和相關參數設置調整來提高性能、可以做到事半功倍。

1. 硬體及操作系統層面調優：包括磁碟I/O調優（多路復用等）、網路調優（緩衝區大小、連接數放大等）、內存調優（虛擬內存設置、內存控制等）；

2. 分散式計算平台及存儲層面調優：存儲格式設置、壓縮格式設置、RPC調用設置、連接數控制設置、調度機制設置、block及分片設置、執行資源設置等；

3. 業務邏輯層面及參數調整，除整體執行的調優外，對不同類型的操作進行參數級別的調優、針對聚合、連接、一讀多寫等修改為不同的sql或者設置不同的參數可以極大的提高性能；

4. Sql層面及應用層面的調優，重構sql寫法、合併sql，大小表連接修改為mapjoin等，在odps2.0中已處理了自動mapjoin等、目前未升級用戶及對外輸出的用戶仍需要修改sql來支持；

不同層面調優及優缺點見下：

二. 場景及調優列舉

1. 大小表關聯修改為mapjoin，增加Mapjoin hint

方法及注意事項：

所有指定的小表佔用的內存總和不超過 512M
多表Join時，最左邊的兩個表，不能同時是 Mapjoin 的表
不同的關聯方式（left/right/inner)，對錶的順序有要求

left outer join 左表必須為大表
right outer join 右表必須為大表
inner join 左右表皆可為大表

full outer join 不能直接使用 mapjoin，修改為mapjoin + union all
最新的發布版本上已經支持了automapjoin，可以根據join表的大小自動把小錶轉為mapjoin，對部分沒有顯示增加hint的sql和中間結果為小表的sql進行自動優化

2. 數據傾斜

數據傾斜表現:

任務進度長時間維持在99%，查看監控，只有少量 reduce 子任務未完成

單一 reduce 記錄數與平均記錄數差異多大，最長時長遠大於平均時長

優化方法及注意事項：

聚合傾斜，設置參數：set odps.sql.groupby.skewindata=true
關聯傾斜，設置參數:set odps.sql.skewinfo=tab1:(col1,col2)[(v1,v2),(v3,v4),...|(v1,v2),(v3,v4),...];set odps.sql.optimize.skewjoin=true
關聯傾斜，同時大小表，考慮修改為mapjoin
具體問題具體分析：因為聚合key值null引起的數據傾斜，可以修改null為隨機值，打撒數據分發到不同的instance執行

3. Map 端一讀多寫

場景及優化：

多次讀取同一張物理表，執行不同操作，寫入多張表；考慮與multiinsert 的聯繫和區別，是否合適做修改

建立臨時表，實現臨時表的並行化

注意事項：

優點：大大節省了集群的計算資源和磁碟I/O資源
缺點：寫的次數非常多時，可能導致性能問題，影響任務整體執行效率

4 分區裁剪

場景：事實表很多分區，數據量大

優化：避免全表掃描，減少資源浪費；關注分區裁剪有無生效，見下注意事項：從表設計、使用上注意，盡量讓分區裁剪生效

注意事項：

過濾條件中的分區列上有UDF 則分區裁剪生效
表關聯時關聯條件中包含分區列：

出現在 on 條件中，分區裁剪生效
出現在 where 條件中，主表分區裁剪生效，其餘可能失敗

5. SQL 合併

場景：

1. 多次讀取相同的數據且源數據數據量大、性能差、費用高

2. 統一業務流程前後關聯sql或統計多種指標、篩選不同數據的sql

優化方法及注意事項：通過修改sql，合併為1個sql執行，盡量減少對相同數據源的讀取次數，達到一次掃描計算多個基礎統計量，一次掃描，處理多個篩選條件；以下調整列舉：

CASE … WHEN…：合併相同數據源的不同子查詢的關聯
動態分區&多路插入等：將滿足不同條件的會員統計信息插入到不同的表或表分區中去
前後流程sql合併為1個sql執行

6 使用窗口函數優化SQL

窗口函數：

1. 可以進行靈活的分析處理工作

2. 使用 partition by 開窗，order by 排序

3. 可以用 rows 指定開窗範圍

4. 豐富的開窗函數

優化及注意事項：合理使用窗口函數，可以減少Join次數，提高運行性能;不用窗口函數處理需要寫複雜sql的功能，用開窗函數可以高效執行得到預期結果。

本來選自阿里雲大數據產品專家「隱林」，擅長MaxCompute、機器學習、分散式、可視化、人工智慧等大數據領域。