MaxcomputerSQL 優化| 學習筆記

開發者學堂課程【MaxCompute 行業應用及調優：MaxcomputerSQL 優化】學習筆記，與課程緊密聯系，讓使用者快速學習知識。

課程位址：

https://developer.aliyun.com/learning/course/88/detail/1350

MaxcomputerSQL 優化

内容介紹

一.SQL 成本計算

二.SQL IO 讀取優化

三.SQL 計算優化

四.整體優化

一、SQL 計算成本

SQL 運作過程：

使用者寫了一個 sql 然後就會傳入到我們的平台，就會有一個編譯器進行文法解析，文法解析之後就會生成一個邏輯 plan ，

然後會有一個優化器，對它進行各種邏輯優化和實體優化，最終生成一個可執行的plan，

然後這個 plan 就會把它交給資源排程器，排程器啟動作業管理器來管理這個 plan 的執行，

作業管理器總共會啟動兩個 task，來執行這個 sql，兩個 task 直接是有資料依賴關系的，

如 task1 執行完了 task2 才能執行，每一個 task 會有很多階層來執行，資料檔案的讀寫就會使用分布式檔案系統，來進行檔案的讀寫，這就是整個 sql 的運作的整個機制。

我們如何優化一個sql，讓優化器更好的識别它，更好的做出更優化的執行 plan，并且能在執行計算階段，更好的節省資源。

 計算方法：

①讀取 IO 資料量，從你讀取的表資料出發。

②Sql 複雜度由 Sql 裡面多少耗資源的算子組成，越多複雜度越高，

算子比較耗資源的如：

join/Groud by/order by/distinct/window func/insert into是以優化 SQL 的過程，實際上就是要盡可能減少 IO 讀取，盡可能減少計算資源使用，盡可能降低 SQL 複雜度，盡可能提升運作速度。

二.優化過程

SQL IO 讀取優化

1.建立分區

建立分區表，一般資料比較大就是要建表，但是分區層次不要太多，允許的最大量是六級，一般三級就可以，太多就把資料切得太小了。

 2.分區裁剪

①避免全表掃描，減少資源浪費

②case：where pt=xxx and region=xxx

③分區盡量按層級順序裁剪

④分區值盡量常量化，避免不可确定值，如 UDF

⑤分區值盡量避免引用列的表達式計算或者子查詢

 3.寫分區

①寫入靜态分區，優化資料存儲

②避免動态分區，防止檔案過多和計算長尾

4.隻引用有效列

①避免 select from xxx

②常量代替引用列，如 count（c）_＞count（1）//c not null

 5.盡可能 pushdown 過濾條件

①where a＞and（b＞1or c ＜1）

6.limit N.隻讀取需要的資料然後就停止。

 6.源表合并

1.合并不同 Sql，一讀多計算

①讀取相同源表可合并，節省 IQ 和計算資源

②對源表統計多種名額計算或者篩選不同資料處理

③避免規模過大，運作時間過長

2.Multi insert，動态分區，一讀多寫

①同一 sql 讀取相同源表，系統會優化隻讀取一次

②資源足夠，也可以考慮拆分 sql，讀取和計算更好并行，資源換時間

3.子查詢合并

①對于 sql 中相同的子查詢也會合并成一個源

②盡可能保持子查詢語句一樣，觸發合并

Join連接配接_Mapjoin優化

1.運作原理

①小表資料全部加載記憶體

②讀取大表的每個task加載一份小表資料

 2.Case1：大小表資料相差大，小表滿足限制條件

①靜态次元表，輕度彙總表等

②大小表資料量相差越大越有優勢，越能節省 IO

 3.Case2.不等值條件，or 條件等複雜 on 條件

①顯性指定 mapioin

②不支援其他關聯方式

 4.Case3：autoMapjoin

Case4：資料傾斜，導緻長尾

表現：極少數joiner程序處理資料量遠大于其他程序，運作時間過長

原因：

①熱點 key：Null值，空值，預設值，大 key 值等

②資料分發不均衡：資料屬性，壓縮率不均等

③建表不合理

操作：

熱點 key 盡量先去重或過濾無效值

對一些熱點預設無效值加一個随機字首，分散資料

修改 SQL 隔離熱點 key 和非熱點 key 分别做關聯，再 union all 起來

顯性指定mapjoin

增加源表作為大表的并發度：set odps.sql.mapper.split.size=128

增加子查詢作為大表并發度：set odps.sql.joiner.instances=1111

限制條件：

①left outer join 的左表必須是大表

②right outer join 的右表必須是大表

③inner join 左表或右表均可以作為大表

④full outer join 不能使用 Mapjoin

⑤Mapjoin 支援小表為子查詢

⑥使用 Mapjoin 時，需要引用小表或是子查詢時，需要引用别名

在 Mapjoin 中，可以使用不等值連接配接或者使用or連接配接多個條件

最多支援指定8張小表，否則報文法錯誤

⑦如果使用 Mapjoin，則所用小表占用的記憶體總和不得超過512MB

多個表 join 時，最左邊的兩個表不能同時是 Mapjoin 的表

①全鍊路關鍵節點優化

1.找出關鍵節點路徑上所有 SQL，記錄運作時間

2.優化核心節點和依賴節點

3.合理調節，理清并行和串行關系

②單 SQL 作業内部計算節點優化

1.找出 SQL 所有 task 運作時間的關鍵路徑

2.對關鍵節點進行優化，适當多配置設定一些資源

長周期名額統計優化

③問題

1.時間太長，累計的資料量太多，如對一年的資料進行統計

2.單 sql 消耗太多資源，萬級别的并發度，甚至超限呢，運作失敗

3.容易産生資料傾斜和各種長尾，運作不穩定

④解決方案

1.選取适當的時間次元建立分區表和增量表

2.根據适當的時間次元做中間輕度彙總，生成中間表

3.基于中間彙總表做關聯，避免明細關聯

4.基于中間彙總表做總彙總

5.也可以根據上次的彙總做累計彙總

其他常見問題

1.記憶體使用過多

①确認是否資料傾斜，解決方案前面有講述

②單行資料 size 或者字段 size 是否存在超大值，比如 wm_concat，map 等操作

③自定義 UDF 使用記憶體過多

2.SQL 處理過程中産生的資料量過大

①資料膨脹，如 UDTF，join 等操作導緻

②跨 task 的資料量暴漲，甚至超限

3.UDF 逾時或 fail

①使用者需檢查自定義代碼的邏輯是否存在性能瓶頸點

②是否有網絡或者檔案操作或權益問題

4.JOb 等待運作時間較長

①Quota 組資源不足，增加資源或者釋放占用的資源

②叢集超負載運作，等待資源釋放

5.長尾現象

①資料傾斜

②單台機器資源競搶嚴重，如 CPU 負載過重

③讀寫慢，碰到慢盤

④單個 task 運作失敗，重跑