大資料SQL優化之資料傾斜解決案例全集

1 什麼是資料傾斜

資料傾斜即指在大資料計算任務中某個處理任務的程式（通常是一個JVM程式）被分配到的任務量過多，導致任務執行時間超長甚至最終失敗，進而導致整個大任務超長時間執行或者失敗。外部表現的話，在HiveSQL任務裡看到map或者reduce的進度一直是99%持續數小時沒有變化；在SparkSQL裡則是某個stage裡，正在執行的任務數量長時間是1或者2不變。總之如果任務進度資訊一直在輸出，但內容長時間沒有任何變化的時候，大概率是出現資料傾斜了。有個特例需要注意，有時候大家會看到SparkSQL的任務資訊也顯示有1到2個任務在執行中，但進度資訊不再重新整理而表現為假死很久的時候，這通常是在進行最後階段的檔案操作，並不是資料傾斜（雖然這通常意味著小檔案問題嚴重）。

再細分一下，傾斜可以分為以下四類：

讀傾斜。即某個map（HiveSQL）或者task（SparkSQL）在讀取資料階段長期無法完成。這通常是因為檔案分塊過大或者此分塊資料有異常。這種場景出現頻率較小。
算傾斜。即在某個需要排序（如開窗函式或者非廣播關聯時）或者聚合操作的時候，同一個key（通常是一個或者多個欄位或者表示式的組合）的處理耗時過長。這通常是最多的情況，情況也較為複雜。
寫傾斜。即某個操作需要輸出大量的資料，比如超過幾億甚至幾十億行。主要出現在關聯後資料膨脹及某些只能由一個task來操作（如limit）的情況。
檔案操作傾斜。即資料生成在臨時資料夾後，由於數量巨大，重新命名和移動的操作非常耗時。這通常發生在動態分割槽導致小檔案的情況。目前在國內和印度區域已經因為我們預設進行小檔案合併而不再存在這個情況，新加坡還有（我們在推動解決）。

2 為什麼會有資料傾斜

大資料計算依賴多種分散式系統，需要將所有的計算任務和資料經過一定的規則分發到叢集中各個可用的機器和節點上去執行，最後可能還需要進行彙總到少數節點進行最後的聚合操作，以及資料寫到HDFS/S3等分散式儲存系統裡以永儲存。這個過程被設計來應對大多數情況，並不能應對所有的情況。它具有以下幾個特點：

業務資料分佈規律無法預知。比如系統無法不經過計算而提前知道某個表的某個欄位的取值分佈是否大致均勻。
計算結果數量無法預知。比如兩表關聯的結果對於某些key（關聯的一個欄位或者多個欄位組合）的輸出行數無法不經過計算而預知進而針對性處理；又比如對某個欄位的值進行split操作或者explode等操作後產生的結果數量無法預知而進行鍼對性的應對。
某些操作只能由單一節點進行。一切需要維護一個全域性狀態的大多數操作，如排序，Limit，count distinct，全域性聚合等，一般會安排到一個節點來執行。

上述三個主要特點導致單節點處理的資料量有概率出現巨量，造成了所謂的傾斜問題。當然，這些困難並不是不可解決的。隨著時間的推移，越來越多的針對性的優化措施已逐漸出現，也許在不久的將來業務同學不會再被傾斜問題煩惱。

3 解決案例

由於未來在OPPO主推SparkSQL，因此以下案例將主要以SparkSQL的角度來展示。

3.1 事實表關聯事實表資料膨脹

最近有兩個業務同學提出一個比較麻煩的問題，就是事實表關聯事實表，其中有若干個key的輸出達數十億行，資料膨脹嚴重，造成資料計算和輸出的傾斜。

比如以下場景：

我們統計了兩個表的傾斜KEY值分佈：

a表：

b表：

大家可以看出，

只看option_id=7的關聯結果最後是46839*130836=6128227404，即61億行；
option_id=2的關聯結果是71080*125541=8923454280，即89億行。
屬於嚴重傾斜的情況。

這種事實表關聯事實表的情況在非報表類的計算任務偶爾會遇到。平時我們解決資料傾斜主要是計算結果的過程涉及太多資料要處理導致慢，但通常輸出的行數可能並不多，不存在寫的困難，所以類似過濾異常資料或者廣播關聯等方法都不起作用。

這個問題的本質是一個task最多由一個程式來執行，而相同的key也必須在同一個task中處理，因此在無法改變這個機制的前提下，我們只有想辦法減少一個task輸出的行數。

那如何在不影響最終結果的前提下，減少單個task所需要處理資料行數呢？

其實網上也有許多建議，都是單獨處理傾斜的key，通過加字首字尾等方式打散key，再最後合併處理，但這樣做法太麻煩了，不夠優雅。我們要追求對業務同學更友好，程式碼更優雅的方式。

最後我尋遍所有可用的系統函式，發現了collect_set/collect_list這個聚合函式，可以在保證資料關係不丟失的前提下將資料收攏減少行數。比如以下兩行：

可以收攏成一行：

最後我們通過explode+lateral view的方式，可以實現一行展開為多行，從而還原成使用者最後期望的明細結果方式。

上述辦法的核心是將原來傾斜的操作（同一個key關聯），修改為不再相互依賴的操作（一行變多行）。

最終程式碼如下：

注意以上程式碼裡值得注意的地方：

程式碼裡的hint（repartition(1000)）的作用是考慮到經過collect_list聚合後的資料單行攜帶的資料經過一行變多行的展開操作後會膨脹很多倍，因此單個任務處理的資料量必須很小，才能保證處理速度夠快。這個hint的作用是告訴系統將上一階段關聯後的結果分成1000份，交給下游處理；
group by語句裡的ceil(rand()*N)作用是將一個key分成最多N行，這樣可以限制最後按key關聯後生成的行數的上限；
通過spark.sql.files.maxPartitionBytes引數控制單個任務處理的資料量，進一步拆分單個任務需要處理的資料。事實上如果第1點裡檔案足夠小，這個引數可以省略。

經過驗證，20分鐘任務就完成了，生成了近800億行的資料，其中包括了19個超十億行的key。

3.2 避免排序

有一些演算法基礎的同學都知道排序操作在軟體領域是開銷非常大的操作，目前大規模應用的幾大排序演算法的時間複雜度中最好的也是O(nlogn)，即隨著資料量的增長而非線性的增長。這就是說，大規模資料量的排序往往意味著巨大的時間消耗。然而這在大資料SQL中卻是常見的情況，從而引發傾斜。一旦有了排序的需求，什麼優化引數都不好使了，一般來說只有進行改寫程式碼。幸運的是，在絕大多數大資料場景下，排序是不必要的，很多時候只是業務同學不解排序在大資料場景下的開銷很大而信手寫下了排序程式碼。下面介紹2個改寫程式碼從而避免排序的案例。

1）用max函式替換排序。

最近收到一個同事的業務需求，需要對某個業務的埋點資料做一次樣本展示，要在約1200億行資料中，撈出約1萬條資料。很簡單的一個SQL如下：

稍微解釋一下SQL的意思：希望取出上報資料裡針對某個維度組合的一條內容較為豐富的樣本資料，因此以某欄位的size作為降序排序並取結果的第一條。

這個SQL當然跑失敗了。我對partition by的欄位集合（後續簡稱key）進行了統計，最大的key有137億行，另外還有至少10個key的資料量超過20億行。這樣executor的記憶體加得再大都無法跑成功了。

這個問題的本質還是對大資料做了不必要的排序（大資料架構裡對排序暫無非常高效的處理辦法）。因此優化的思路還是想辦法減少這種不必要排序。

既然使用者只需要排序後的最大的一條，本質上不就是取某個key的最大值嘛。取出這個最大值，最後再跟源表進行關聯，就可以取出最大值對應的那一條資料。

這裡有個前提條件，要想在第二步關聯回源表資料的時候幹掉排序，我們只有走一條路：廣播關聯（如果走sort-meger關聯，還是會避免不了sort步驟）。這就要求我們的小表（key-最大值）要足夠小。通常這個條件都會滿足的，因為如果不滿足的話，說明key值非常多，非常稀疏，也不會產生傾斜的困境了。如開始就說明了，最後Key的去重資料量不到1萬條，完全可以走廣播關聯。

最後的程式碼如下：

注意上述SQL有兩點說明：

我們使用了semi join，這在日常程式碼中比較少見。它的意思是，左表去匹配右表，如果一旦發現左表的某條資料的關聯key在右表，便保留此條左表的資料，不再繼續在右表裡查詢了。這樣做有兩個結果：1）速度更快；2）不會把右表的資料放到結果裡）。它等價於 select * from left_table where key in (select key from right_table)。但大資料發展過程中一度不支援in的用法（現在部分支援了），因此有這種語法，從效率上看，一般認為這樣更高效。
因為能匹配到最大值的資料可能有許多條，所以對最後結果再做一次row_number的開窗並取其中一條即可。這個時候由於size(xxxx)的值都是一樣的，因此任意取一條均符合業務需求。

在一般情況下，上述SQL能較好的執行。但我們這次情況出了點意外：經過上述操作後，我們得到的資料還有800多億行。因為max(size(xxxx) = size(xxxx)的資料佔了絕大多數，導致我們匹配回去無法有效的篩選出少量結果。我們必須找到一個能有效區分各行資料的欄位，這個欄位的值必須很鬆散。最後我發現比較好的是userid。因此將 max(size(xxxx))替換成了 max(userid)，任務很快就跑完了。因為不影響我們講述優化的原理，所以不再描述這部分細節。

2）用分位函式替換排序。

在一個畫像任務相關跑得很慢時，業務同學求助於我們，發現慢的程式碼如下：

問題點：上面的程式碼是想做一個全域性排序，然後使用其序號所在位置來進行分類打標。上述程式碼在排序資料小於5億5千萬行的情況下勉強能執行出結果。但在某一天資料量到了5億5千萬行後就跑不出來，加了reducer的記憶體到10G也不行。

新思路：雖然可能還有一些引數能調整，但我認為這不是正確的方向，於是停止了研究，把方向轉為幹掉全域性排序。在和一位前輩溝通的時候，突然意識到，既然業務是想做一個分檔，本質上就並不需要具體的排序號，所以理論上完全的排序是可以省掉的。於是自然想到了分位數函式，立馬想到了新方案。分位函式計算出資料必須大於或者等於某個值才能處於整個資料排序的某個位置。詳情請大家自行搜尋。

改之後程式碼如下：

注意上述程式碼有個小技巧，即與只有一行的子查詢結果進行笛卡爾積關聯，從而變相的實現了引入p2到p8等4個變數的效果，還算實用。

效果：對比了新舊演算法的結果，差異極小，也在預期範圍內。

再對比了任務執行時間，約有87%的降幅：

這個案例的本質在於識別出了費盡資源計算的全域性序號是完全不必要的。類似的情況在我們的業務程式碼裡還存在很多，只是目前尚在業務可接受的範圍內，存在非常大的優化空間。希望未來能開展專項，以節省計算時間和資源。

3）通過廣播關聯徹底避免排序。

SparkSQL目前處理關聯(join)的方法主要有兩種：

a) 廣播關聯。小表（通過引數spark.sql.autoBroadcastJoinThreshold控制，目前我們的預設值是20M）的話會採用廣播關聯，即將小表的全部資料傳輸到各節點的記憶體中，通過直接的記憶體操作快速完成關聯。這種方式最大的好處是避免了對主表的資料進行shuffle，但會增加任務使用的記憶體量。另外特別說明3點：

目前我們的sparksql優化器尚不能非常準確地判斷一個子查詢結果（也被當成一張小表）是否適合進行廣播，因此還在跟進解決中；
左表無論大小都不能被廣播；
某些情況下會有類似：Kryo serialization failed: Buffer overflow 這樣的OOM出現，並 “To avoid this, increase spark.kryoserializer.buffer.max value”。但其實這樣設定會無效。實質原因是：雖然某張表小於32M，但由於高度壓縮後，解壓結果的行數達到了數千萬，造成了節點的OOM。這個時候，只能手動禁掉廣播關聯。

b) Sort-Merge關聯。即先將兩表按連線欄位進行排序，然後在些基礎上進行匹配關聯。由於資料是排序過的，只需要一次性的匹配即可完成最終的關聯，速度較快。但這種方法的弊端是要進行對關聯key的排序，並且每個相同的Key和對應的資料必須分配到一個executor裡，引發大量的shuffle操作；另一方面如果一個executor需要處理一個巨量的key，通常會花費大量的時間以及大量的磁碟IO。

通過上述原理描述可以看出如果採用廣播關聯，引擎完全不用做任何排序，自然也不會有排序帶來的傾斜了，這是效率巨大的提升，當然代價就是會增加記憶體佔用。一般來說這種記憶體使用的增加被認為是划算的。

如果引擎沒有識別出來，我們可以通過主動指示的辦法影響執行計劃。比如以下：

要讓執行計劃改成廣播s子查詢結果，加hint mapjoin （也可以是 broadcast）就可以了。

從實際的結果看，廣播關聯的提速都有翻倍以上的效果。

3.3 寫傾斜的避免

這部分簡要描述一下。在動態分割槽場景下，我們常常很難預料最後每個分割槽將要輸出的資料量會是多少，但分配的task數量對於每個最終分割槽都是固定的。以國家分割槽條件為例，印尼這個分割槽如果是輸出10億行，而新加坡只輸出100萬行，這個時候如果我們只分配2個任務去寫資料，印尼這個分割槽單個任務會承受1億行的任務，會非常慢。而如果設定為100個任務來寫資料，對印尼這個分割槽來說是比較合適的，但新加坡這個分割槽分產生100個小檔案，對後續的檔案操作和未來下游任務的讀取都有消極的影響。最後經過實踐後，找到一個比較好的辦法。即找出傾斜的分割槽key，通過distribute by + case when表示式，讓引擎對不同的分割槽做不同數量的資料分發。具體程式碼(以region為動態分割槽欄位):

目前這種情況在海外任務上還需要應用，未來隨著我們推動AWS解決小檔案自動合併問題，應該不用再操心了。

3.4 非法值過濾

這應該是網上講得比較多的辦法，我也簡略說下。

在優化戰略生態部門的任務dwd_ocloud_dau_info_d任務的時候，我們發現任務的執行時間一直在增長，一度達到7個小時，直到8月1號便再也跑不成功，總是OOM（記憶體不夠），即使將executor的記憶體調高到10G依然解決不了問題。經過仔細診斷，發現任務慢在一個開窗函式階段，程式碼如下：