大數據SQL數據傾斜與數據膨脹的優(yōu)化與經驗總結
背景
目前市面上大數據查詢分析引擎層出不窮，如Spark，Hive，Presto等，因其友好的SQL語法，被廣泛應用于各領域分析，公司內部也有優(yōu)秀的ODPS SQL供用戶使用。筆者所在團隊的項目也借用ODPS SQL去檢測業(yè)務中潛在的安全風險。在給業(yè)務方使用與答疑過程中，我們發(fā)現(xiàn)大多含有性能瓶頸的SQL，主要集中在數據傾斜與數據膨脹問題中。因此，本文主要基于團隊實際開發(fā)經驗與積累，并結合業(yè)界對大數據SQL的使用與優(yōu)化，嘗試給出相對系統(tǒng)性的解決方案。
本文主要涉及業(yè)務SQL執(zhí)行層面的優(yōu)化，暫不涉及參數優(yōu)化。若設置參數，首先確定執(zhí)行層面哪個階段（Map/Reduce/Join）任務執(zhí)行時間較長，從而設置對應參數。
本文主要分為以下三個部分：
一、會引入數據傾斜與數據膨脹問題
二、介紹當數據傾斜與數據膨脹發(fā)生時，如何排查與定位
三、會從系統(tǒng)層面給出常見優(yōu)化思路。
一、問題篇
數據傾斜
數據傾斜是指在分布式計算時，大量相同的key被分發(fā)到同一個reduce節(jié)點中。針對某個key值的數據量比較多，會導致該節(jié)點的任務數據量遠大于其他節(jié)點的平均數據量，運行時間遠高于其他節(jié)點的平均運行時間，拖累了整體SQL執(zhí)行時間。
其主要原因是key值分布不均導致的Reduce處理數據不均勻。本文將從Map端優(yōu)化，Reduce端優(yōu)化和Join端優(yōu)化三方面給出相應解決方案。
數據膨脹
數據膨脹是指任務的輸出條數/數據量級比輸入條數/數據量級大很多，如100M的數據作為任務輸入，最后輸出1T的數據。這種情況不僅運行效率會降低，部分任務節(jié)點在運行key值量級過大時，有可能發(fā)生資源不足或失敗情況。
二、排查定位篇
本節(jié)主要關注于業(yè)務SQL本身引起的長時間運行或者失敗，對于集群資源情況，平臺故障本身暫不考慮在內。
首先檢查輸入數據量級。與其他天相比有無明顯量級變化，是否因為數據量級的問題天然引起任務運行時間過長，如雙11，雙十二等大促節(jié)點。 觀察執(zhí)行任務拆分后各個階段運行時間。與其他天相比有無明顯量級變化；在整個執(zhí)行任務中時間耗時占比情況。 最耗時階段中，觀察各個Task的運行情況。Task列表中，觀察是否存在某幾個Task實例耗時明顯比平均耗時更長，是否存在某幾個Task實例處理輸入/輸出數據量級比平均數據量級消費產出更多。 根據步驟3中定位代碼行數，定位問題業(yè)務處理邏輯。
三、優(yōu)化篇
數據傾斜
1. Map端優(yōu)化
1.1 讀取數據合并
在數據源讀取查詢時，動態(tài)分區(qū)數過多可能造成小文件數過多，每個小文件至少都會作為一個塊啟動一個Map任務來完成。對于文件數量而言，等于 map數量 * 分區(qū)數。對于一個Map任務而言，其初始化的時間可能遠遠大于邏輯處理時間，因此通過調整Map參數把小文件合并成大文件進行處理，避免造成很大的資源浪費。
1.2 列裁剪
減少使用select * from table語句，過多選擇無用列會增加數據在集群上傳輸的IO開銷；對于數據選擇，需要加上分區(qū)過濾條件進行篩選數據。
1.3 謂詞下推
在不影響結果的情況下，盡可能將過濾條件表達式靠近數據源位置，使之提前執(zhí)行。通過在map端過濾減少數據輸出，降低集群IO傳輸，從而提升任務的性能。
1.4 數據重分布
在Map階段做聚合時，使用隨機分布函數distribute by rand()，控制Map端輸出結果的分發(fā)，即map端如何拆分數據給reduce端（默認hash算法），打亂數據分布，至少不會在Map端發(fā)生數據傾斜。
2. Reduce端優(yōu)化
2.1 關聯(lián)key空值檢驗
部分實例發(fā)生長尾效應，很大程度上由于null值，空值導致，使得Reduce時含有臟值的數據被分發(fā)到同一臺機器中。
針對這種問題SQL，首先確認包含無效值的數據源表是否可以在Map階段直接過濾掉這些異常數據；如果后續(xù)SQL邏輯仍然需要這些數據，可以通過將空值轉變成隨機值，既不影響關聯(lián)也可以避免聚集。
SELECT??ta.id
FROM????ta
LEFT?JOIN?tb
ON??????coalesce(ta.id?,?rand())?=?tb.id;
2.2 排序優(yōu)化
Order by為全局排序，當表數據量過大時，性能可能會出現(xiàn)瓶頸；Sort by為局部排序，確保Reduce任務內結果有序，全局排序不保證；Distribute by按照指定字段進行Hash分片，把數據劃分到不同的Reducer中；CLUSTER BY：根據指定的字段進行分桶，并在桶內進行排序，可以認為cluster by是distribute by+sort by。
對于排序而言，嘗試用distribute by+sort by確保reduce中結果有序，最后在全局有序。
--?原始腳本
select?*
from?user_pay_table
where?dt?=?'20221015'
order?by?amt
limit?500
;
--?改進腳本
SELECT??*
FROM????user_pay_table
WHERE???dt?=?'20221015'
DISTRIBUTE?BY?(?CASE
???????????????????WHEN?amt????????????????????WHEN?amt?>=?100?AND?age?<=?2000?THEN?1
???????????????????ELSE?2
?????????????????END?)
?SORT?BY?amt
LIMIT?500
;
3. Join端優(yōu)化
3.1 大表join小表
通過將需要join的小表分發(fā)至map端內存中，將Join操作提前至map端執(zhí)行，避免因分發(fā)key值不均勻引發(fā)的長尾效應，復雜度從（M*N）降至（M+N），從而提高執(zhí)行效率。ODPS SQL與Hive SQL使用mapjoin，SPARK使用broadcast。
3.2 大表join大表
長尾效應由熱點數據導致，可以將熱點數據加入白名單中，通過對白名單數據和非白名單數據分別處理，再合并數據。
具體表現(xiàn)為打散傾斜key，進行兩端聚合(針對聚合)或者拆分傾斜key進行打散然后再合并數據。
數據膨脹
避免笛卡爾積
Join關聯(lián)條件有誤，表Join進行笛卡爾積，造成數據量爆炸。
關聯(lián)key區(qū)分度校驗
關注JoinKey區(qū)分度，key值區(qū)分度越低（distinct數量少），越有可能造成數據爆炸情況。如用戶下的性別列，交易下的省市列等。
聚合操作誤用
部分聚合操作需要將中間結果記錄下來，最后再生成最終結果，這使得在select操作時，按照不同維度去重Distinct、不同維度開窗計算over Partition By可能會導致數據膨脹。針對這種業(yè)務邏輯，可以將一個SQL拆分成多個SQL分別進行處理操作。
總結
大數據SQL優(yōu)化是一項涉及知識面較廣的工作，除了分析現(xiàn)有執(zhí)行計劃之外，還需要學習相應查詢分析引擎設計原理。針對我們日常遇到的問題現(xiàn)總結分享給大家，供大家查閱。
