摘要： Join是MaxCompute中最基本的語法，但由于數據量和傾斜問題，非常容易出現性能問題。一般情況下，join產生的問題有兩大類： 數據傾斜問題：join會將key相同的數據分發到同一個instance上處理，如果某個key上的數據量特別多則會導致該instance處理時間比其他instance…

原文地址：http://click.aliyun.com/m/43804/

Join是MaxCompute中最基本的語法，但由于數據量和傾斜問題，非常容易出現性能問題。一般情況下，join產生的問題有兩大類：

1.數據傾斜問題：join會將key相同的數據分發到同一個instance上處理，如果某個key上的數據量特別多則會導致該instance處理時間比其他instance處理時間長，這就是我們常說的數據傾斜，這也是join計算性能問題的罪魁禍首；

2.數據量問題：關聯的兩表基本沒有熱點問題，但兩個表數據量都非常大同樣會影響性能，比如記錄數達幾十億條，如商品表、庫存表等；

雖然MaxCompute中提供了一些通用的優化算法，但從業務角度解決性能問題往往更精確，更有效。對于MaxCompute sql優化，在云棲社區上已經有比較多的經驗積累，本文主要對join產生的性能問題以及解法做些總結。

不同數據類型key關聯

例子

瀏覽IPV日志以商品id關聯商品表，假設日志表的商品id字段是string類型，商品表中的商品id是bigint類型，那么在關聯中，關聯key會全部轉換成double類型進行比較，設想由于埋點問題日志表中的商品id存在很多非數值的臟數據，那么轉換成double后值都變為NULL或者截取前面的數值，關聯時就會產生數據傾斜問題，更嚴重的會造成數據錯誤。

解法

關聯時手工進行數據格式轉換，在這種情況下一般將bigint類型key轉換成string類型。

select a.* from ipv_log_table a left outer join item_table b on a.item_id = cast(b.item_id as string)

思考下，假如反過來將string類型轉換成bigint、假如IPV日志表中的商品id大部分為無效值（比如0）、又假如IPV日志表中沒有無效值但是有熱點key會有什么問題呢？下面的例子會解答這些問題。

小表join大表

Join中存在小表，一般這個小表在100M以內，可以用mapjoin，避免分發引起的長尾。拿上面的例子來說，假如商品表數據量只有幾萬條記錄（這里只是打個比方，現實業務中商品表一般都是非常龐大的），但是IPV日志表中的商品id 80%值為0的無效值，且記錄數有幾十億，如果采用上述SQL寫法，數據傾斜是顯而易見的，但利用mapjoin可以有效解決這個問題：

select /*+ MAPJOIN(b) */a.* from ipv_log_table a left outer join item_table b on a.item_id = cast(b.item_id as string)

mapjoin原理

把小表廣播傳遞到所有的Join Task Instance上面，然后直接和大表做Hash Join，簡單的說就是將join操作提前到map端，而非reduce端。

mapjoin使用注意點

小表在left outer join 時只能是右表， right outer join 時只能是左表， inner join 時無限制，full outer join不支持mapjoin；
mapjoin最多只支持8張小表，否則會報語法錯誤；
mapjoin所有小表內存限制不能超過2GB，默認為512M；
mapjoin支持小表為子查詢；
mapjoin支持不等值連接或者使用or連接多個條件；

大表join大表存在無效值

在小表join大表時我們已經了解到通過mapjoin將小表全部加載到map端可以解決傾斜問題，但假如‘小表‘不夠小，mapjoin失效的時候該怎么辦呢？同樣以本文第一個場景為例，IPV日志表中80%商品id都為無效值0（目前MaxCompute底層已經針對NULL值進行優化，已經不存在傾斜問題了），這時關聯十幾億量級商品表那就是個災難。

解法1-分而治之：
我們可以事先知道無效值是不可能關聯出結果的，而且完全不需要參與關聯，所以可以將無效值與有效值數據分開處理：

select a.visitor_id,b.seller_id from (select from ipv_log_tablewhere item_id > 0 ) a left outer join item_table b on a.item_id = b.item_idunion allselect a.visitor_id,cast(null as bigint) seller_id from ipv_log_table where item_id = 0

解法2-隨機值打散：
我們也可以以隨機值代替NULL值作為join的key，這樣就從原來一個reduce來處理傾斜數據變成多reduce并行處理，因為無效值不參與關聯，即使分發到不同reduce，也不會影響最終計算結果：

select a.visitor_id,b.seller_id from ipv_log_table a left outer join item_table b on if(a.item_id > 0, cast(a.item_id as string), concat('rand',cast(rand() as string))) = cast(b.item_id as string)

解法3-轉化為mapjoin：
雖然商品表有十幾億條記錄，不能直接通過mapjoin來處理，但在實際業務中，我們知道一天內用戶訪問的商品數是有限的，在業務中尤為明顯，基于此我們可以通過一些處理轉換成mapjoin：

select /*+ MAPJOIN(b) */a.visitor_id,b.seller_id from ipv_log_table a left outer join (select /*+ MAPJOIN(log) */itm.seller_id ,itm.item_idfrom (select item_id from ipv_log_table where item_id > 0group by item_id) log join item_table itmon log.item_id = itm.item_id ) b on a.item_id = b.item_id

解法對比

解法1和解法2是通用解決方案，對于解法1，日志表被讀取兩次，而解法2中只需讀取一次，另外任務數解法2也是少于解法1的，所以總的來看解法2是優于解法1的。解法3是基于一定的假設，隨著業務發展或者某些特殊情況下假設可能失效（比如一些爬蟲日志，可造成訪問商品數接近全量），這會導致mapjoin失效，所以在使用過程中要根據具體情況來評估。

一個古老的例子

最后要講一個古老的優化case，雖然歷史比較久遠，目前已沒有相關問題，但優化思路值得借鑒。情況是這樣的，歷史上并存過兩套商品維表，一份主鍵是字符串id，新的商品表也就是目前在使用的主鍵是數字id，字符串id和數字id做了映射，存在商品表中的兩個字段中，所以在使用中需要分別過濾數字id、字符串id然后分別和商品表關聯，最后union起來得到最終結果。

思考下如果換成下面的優化思路是不是更優呢？

select ... from ipv_log_table a join (select auction_id as auction_id from auctionsunion allselect auction_string_id as auction_id from auctions where auction_string_id is not null ) b on a.auction_id = b.auction_id

答案是肯定的，可以看到優化后商品表讀取從2次降為1次，IPV日志表同樣，另外MR作業數也從2個變為1個。

總結
對于MaxCompute sql優化最有效的方式是從業務的角度切入，并能夠將MaxCompute sql轉化為mapreduce程序來解讀。本文針對join中各種場景優化都做了一些梳理，現實情況很可能是上述多場景的組合，這時候就需要靈活運用相應的優化方法，舉一反三。

識別以下二維碼，干貨

總結

以上是生活随笔為你收集整理的MaxCompute JOIN优化小结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。