本文主要基于團隊實際開發經驗與積累，并結合了業界對大資料SQL的使用與優化，嘗試給出相對系統性的解決方案，

背景

目前市面上大資料查詢分析引擎層出不窮，如Spark，Hive，Presto等，因其友好的SQL語法，被廣泛應用于各領域分析，公司內部也有優秀的ODPS SQL供用戶使用，

筆者所在團隊的專案也借用ODPS SQL去檢測業務中潛在的安全風險，在給業務方使用與答疑程序中，我們發現大多含有性能瓶頸的SQL，主要集中在資料傾斜與資料膨脹問題中，因此，本文主要基于團隊實際開發經驗與積累，并結合業界對大資料SQL的使用與優化，嘗試給出相對系統性的解決方案，

本文主要涉及業務SQL執行層面的優化，暫不涉及引數優化，若設定引數，首先確定執行層面哪個階段（Map/Reduce/Join）任務執行時間較長，從而設定對應引數，

本文主要分為以下三個部分：第一部分，會引入資料傾斜與資料膨脹問題，第二部分，介紹當資料傾斜與資料膨脹發生時，如何排查與定位，第三部分，會從系統層面給出常見優化思路，

問題篇

資料傾斜

資料傾斜是指在分布式計算時，大量相同的key被分發到同一個reduce節點中，針對某個key值的資料量比較多，會導致該節點的任務資料量遠大于其他節點的平均資料量，運行時間遠高于其他節點的平均運行時間，拖累了整體SQL執行時間，

其主要原因是key值分布不均導致的Reduce處理資料不均勻，本文將從Map端優化，Reduce端優化和Join端優化三方面給出相應解決方案，

資料膨脹

資料膨脹是指任務的輸出條數/資料量級比輸入條數/資料量級大很多，如100M的資料作為任務輸入，最后輸出1T的資料，這種情況不僅運行效率會降低，部分任務節點在運行key值量級過大時，有可能發生資源不足或失敗情況，

排查定位篇

本節主要關注于業務SQL本身引起的長時間運行或者失敗，對于集群資源情況，平臺故障本身暫不考慮在內，

1.首先檢查輸入資料量級，與其他天相比有無明顯量級變化，是否因為資料量級的問題天然引起任務運行時間過長，如雙11，雙十二等大促節點，

2.觀察執行任務拆分后各個階段運行時間，與其他天相比有無明顯量級變化；在整個執行任務中時間耗時占比情況，

3.最耗時階段中，觀察各個Task的運行情況，Task串列中，觀察是否存在某幾個Task實體耗時明顯比平均耗時更長，是否存在某幾個Task實體處理輸入/輸出資料量級比平均資料量級消費產出更多，

4.根據步驟3中定位代碼行數，定位問題業務處理邏輯，

優化篇

資料傾斜

1. Map端優化

1.1 讀取資料合并

在資料源讀取查詢時，動態磁區數過多可能造成小檔案數過多，每個小檔案至少都會作為一個塊啟動一個Map任務來完成，對于檔案數量而言，等于 map數量 * 磁區數，對于一個Map任務而言，其初始化的時間可能遠遠大于邏輯處理時間，因此通過調整Map引數把小檔案合并成大檔案進行處理，避免造成很大的資源浪費，

1.2 列裁剪

減少使用select * from table陳述句，過多選擇無用列會增加資料在集群上傳輸的IO開銷；

對于資料選擇，需要加上磁區過濾條件進行篩選資料，

1.3 謂詞下推

在不影響結果的情況下，盡可能將過濾條件運算式靠近資料源位置，使之提前執行，通過在map端過濾減少資料輸出，降低集群IO傳輸，從而提升任務的性能，

1.4 資料重分布

在Map階段做聚合時，使用隨機分布函式distribute by rand()，控制Map端輸出結果的分發，即map端如何拆分資料給reduce端（默認hash演算法），打亂資料分布，至少不會在Map端發生資料傾斜，

2. Reduce端優化

2.1 關聯key空值檢驗

部分實體發生長尾效應，很大程度上由于null值，空值導致，使得Reduce時含有臟值的資料被分發到同一臺機器中，

針對這種問題SQL，首先確認包含無效值的資料源表是否可以在Map階段直接過濾掉這些例外資料；如果后續SQL邏輯仍然需要這些資料，可以通過將空值轉變成隨機值，既不影響關聯也可以避免聚集，


SELECT  ta.id
FROM    ta
LEFT JOIN tb
ON      coalesce(ta.id , rand()) = tb.id;

2.2 排序優化

Order by為全域排序，當表資料量過大時，性能可能會出現瓶頸；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 < 100                  THEN 0
                   WHEN amt >= 100 AND age <= 2000 THEN 1
                   ELSE 2
                 END )
 SORT BY amt
LIMIT 500
;

3. Join端優化

3.1 大表join小表

通過將需要join的小表分發至map端記憶體中，將Join操作提前至map端執行，避免因分發key值不均勻引發的長尾效應，復雜度從（M*N）降至（M+N），從而提高執行效率，ODPS SQL與Hive SQL使用mapjoin，SPARK使用broadcast，

3.2 大表join大表

長尾效應由熱點資料導致，可以將熱點資料加入白名單中，通過對白名單資料和非白名單資料分別處理，再合并資料，

具體表現為打散傾斜key，進行兩端聚合(針對聚合)或者拆分傾斜key進行打散然后再合并資料，

資料膨脹

1. 避免笛卡爾積

Join關聯條件有誤，表Join進行笛卡爾積，造成資料量爆炸，

2. 關聯key區分度校驗

關注JoinKey區分度，key值區分度越低（distinct數量少），越有可能造成資料爆炸情況，如用戶下的性別列，交易下的省市列等，

3. 聚合操作誤用

部分聚合操作需要將中間結果記錄下來，最后再生成最終結果，這使得在select操作時，按照不同維度去重Distinct、不同維度開窗計算over Partition By可能會導致資料膨脹，針對這種業務邏輯，可以將一個SQL拆分成多個SQL分別進行處理操作，

總結

大資料SQL優化是一項涉及知識面較廣的作業，除了分析現有執行計劃之外，還需要學習相應查詢分析引擎設計原理，針對我們日常遇到的問題現總結分享給大家，供大家查閱，

參考資料：

ODPS SELECT語法：https://help.aliyun.com/document_detail/73777.html?utm_content=g_1000230851&spm=5176.20966629.toubu.3.f2991ddcpxxvD1#section-ag9-2c4-t0e

Presto Query Lifecycle：https://varada.io/blog/presto/accelerate-presto-trino-queries-data-lake/

A Definitive Guide To Hive Performance Tuning- 10 Excellent Tips：https://www.hdfstutorial.com/blog/hive-performance-tuning/

Presto Performance: Speed, Optimization & Tuning：https://ahana.io/learn/presto-performance/

Hive Optimizing Joins：https://docs.cloudera.com/HDPDocuments/HDP2/HDP-2.0.0.2/ds_Hive/optimize-joins.html

作者|霖玉

本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/Optimization-and-experience-summary-of-Big-data-SQL-data-skew-and-data-expansion.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/555520.html

標籤：大數據

上一篇：使用 Easysearch 還原 Elasticsearch 快照資料

下一篇：返回列表

大資料SQL資料傾斜與資料膨脹的優化與經驗總結