了解那些“奇葩”SQL寫法，快速寫出高效率SQL
背景
關(guān)于sql調(diào)參數(shù)、數(shù)據(jù)傾斜可以搜到很多文章，本文主要講解常見的SQL開發(fā)場景、‘奇葩’SQL寫法并深入執(zhí)行計劃，帶你了解如何快速寫出高效率SQL。
高效寫法
union直接使用效率低嗎？
場景介紹
在一些業(yè)務(wù)場景中，需要將多份數(shù)據(jù)合并在一起，比如要取客戶信息，客戶信息存在兩張表中有交叉（假設(shè)兩張表中交叉的客戶信息是一致的），需先將兩份數(shù)據(jù)合并在一起。
寫法&執(zhí)行計劃探查
因為兩張表中數(shù)據(jù)有交叉，所以需要會先將數(shù)據(jù)去重，然后再去join。去重方式常見于：
這種情況下，會理解為先將兩兩份數(shù)據(jù)不做任務(wù)處理就合并在一起，導(dǎo)致shuffle、中間臨時寫入的數(shù)據(jù)量和讀取數(shù)據(jù)量和數(shù)據(jù)源都是一致的，然后再去做去重。因為數(shù)據(jù)量在中間過程沒有沒有減少，所以效率相對來說會低一些?，F(xiàn)在來看一下執(zhí)行計劃：
發(fā)現(xiàn)執(zhí)行計劃是做過的優(yōu)化的，已經(jīng)是最優(yōu)執(zhí)行計劃了。
接下來按照理解中的高效sql寫法來看一下執(zhí)行計劃：
兩種寫法的執(zhí)行計劃一致，如下：
發(fā)現(xiàn)自己另外加的聚合處理，反而增加了復(fù)雜度。
總結(jié)
ODPS已經(jīng)對union做過優(yōu)化，直接使用就可以了。并且對三個及以上的（X張）表做union，執(zhí)行計劃是X個MAP任務(wù)+1個REDUCE任務(wù)；不會像hive是X個MAP任務(wù)+（X-1）個REDUCE任務(wù)，還需要調(diào)整SQL才能實現(xiàn)最優(yōu)的執(zhí)行計劃。
count distinct真的慢嗎？
場景介紹
在開發(fā)過程中，經(jīng)常會遇到一些數(shù)據(jù)探查，比如探查資產(chǎn)信息表中，有多少用戶數(shù)，探查過程中經(jīng)常會用到count distinct，那么它的效率如何？
寫法&執(zhí)行計劃探查
探查資產(chǎn)信息表中近5天的用戶數(shù)，常見的寫法與常規(guī)認為的優(yōu)化寫法：
一般都會認為直接count distinct效率很低，是這樣嗎？接下來看一下兩個執(zhí)行計劃對比
常規(guī)寫法：
優(yōu)化寫法：
從執(zhí)行計劃可以看出，直接count distinct的寫法被優(yōu)化成了兩次去重處理，一次計算總和，并不是直接全量來去重計算。再看優(yōu)化寫法，兩次去重處理，兩次計算總和，反而比count distinct多了一步，不過運行效率還是很快的。最后看一下運行時間和消耗資源，常規(guī)寫法比優(yōu)化寫法快了28%（62s、86s），資源消耗少28%。
那么count distinct可以肆無忌憚的使用了嗎？
接下來看另外一種場景，探查資產(chǎn)信息表中近5天每天的用戶數(shù)，常見的寫法與常規(guī)認為的優(yōu)化寫法：
看一下這種場景下兩種執(zhí)行計劃對比
常規(guī)寫法（此處額外看一下分配的task）：
優(yōu)化寫法：
從執(zhí)行計劃可以看出，直接count distinct的寫法進行了一次去重，就將3億條數(shù)據(jù)給到了5個task進行去重計算總和，每個task的壓力相當(dāng)大。再看優(yōu)化寫法，兩次去重處理，兩次計算總和，每一步都運行的很快，沒有長尾。最后看一下運行時間和消耗資源，常規(guī)寫法比優(yōu)化寫法慢了26倍，資源消耗多出2倍。
總結(jié)
ODPS對count distinct做了執(zhí)行計劃優(yōu)化，但是限于從數(shù)據(jù)源只讀取1個字段的情況下。當(dāng)從數(shù)據(jù)源讀取了多個字段時，應(yīng)將count distinct寫法改為group by count寫法。
多張大表join提速（聚合類型）
場景介紹
在日常的開發(fā)工作中，經(jīng)常會遇到多張表關(guān)聯(lián)取屬性的情況，比如計算用戶在過去一段時間A、B、C...N行為的次數(shù)，或者是在資管領(lǐng)域中，統(tǒng)計一個資產(chǎn)池中的所有資產(chǎn)（日初資產(chǎn)+放款資產(chǎn)+買入資產(chǎn)）。
寫法&執(zhí)行計劃探查
假設(shè)有3份數(shù)據(jù)需要關(guān)聯(lián)得到屬性，常規(guī)的寫法為使用2次full outer join + coalesce來關(guān)聯(lián)取值；或者先將3份數(shù)據(jù)主體合并在一起，再使用3次left join。
接下來看優(yōu)化寫法：
對比join寫法和優(yōu)化寫法的執(zhí)行計劃（這兩個執(zhí)行計劃內(nèi)部做的事情和任務(wù)名稱理解一致，就不展開看了）
join寫法：
優(yōu)化寫法：
從執(zhí)行計劃可以看出，join寫法的執(zhí)行步驟要更多，多次shuffle也會消耗更多的資源，串行運行的時間也會更長。優(yōu)化寫法只需要在讀取所有數(shù)據(jù)之后，做一次reduce就可以完成。最后對比一下運行時間和資源消耗，優(yōu)化寫法運行時間快20%，資源使用減少30%。（場景越復(fù)雜，效果越好）
總結(jié)
由于JOIN是離線數(shù)據(jù)開發(fā)中最常出現(xiàn)低效的環(huán)節(jié)，那么直接干掉JOIN其實是更好的選擇。
當(dāng)多張表的關(guān)聯(lián)鍵相同取int類型、聚合的值的場景下，union all + group by寫法運行更快、更節(jié)省資源、代碼開發(fā)運維更加簡單，并且在表行數(shù)越多、關(guān)聯(lián)表越多、關(guān)聯(lián)鍵越多的場景下，優(yōu)勢會更加突出。
關(guān)于兩種優(yōu)化寫法，優(yōu)化寫法二更加靈活、更好維護、資源占用更少，但是對于需要使用占位數(shù)據(jù)的場景（比如聚合map），方法一更加適合。
多張大表join提速（字符串類型）
場景介紹
日常開發(fā)中，經(jīng)常遇到從一個主體多張表取屬性的情況，比如客戶信息相關(guān)的數(shù)據(jù)，A表取地址、B表取電話號、C表取uv、D表取身份信息、E表取偏好。
寫法&執(zhí)行計劃探查
假設(shè)有3份數(shù)據(jù)需要關(guān)聯(lián)得到屬性，常規(guī)的寫法為使用2次full outer join + coalesce來關(guān)聯(lián)取值；或者先將3份數(shù)據(jù)主體合并在一起，再使用3次left join。
接下來看優(yōu)化寫法：
對比join寫法和優(yōu)化寫法的執(zhí)行計劃
join寫法的執(zhí)行計劃：
優(yōu)化寫法：
對比兩個執(zhí)行計劃，join寫法對于每一張表的數(shù)據(jù)使用了兩次，分別為構(gòu)建主體和取值，所以每一個MAP、JOIN任務(wù)的復(fù)雜度還是比較高的，但是優(yōu)化寫法MAP、REDUCE任務(wù)簡潔明了。并且隨著表的增多，JOIN寫法的JOIN任務(wù)負責(zé)度會更高。對比運行時間和資源消耗，優(yōu)化寫法運行快了20%，資源消耗減少20%。（場景越復(fù)雜，效果越好）
由于使用到collect_set，所以需要考慮該節(jié)點是否存在超內(nèi)存的問題并進行內(nèi)存調(diào)整，該場景一般情況下不會出現(xiàn)。
總結(jié)
同大表join（聚合類型），區(qū)別在于此方法適用于STRING類型。注意collect_set函數(shù)的內(nèi)存占用。
mapjoin為什么快？是否生效了？
場景介紹
日常開發(fā)中，經(jīng)常會遇到大表join小表的情況，mapjoin是老生常談的處理方式，但是也要注意寫法、小表內(nèi)存參數(shù)調(diào)整以保障mapjoin生效。
寫法&執(zhí)行計劃探查
目前ODPS對mapjoin做了優(yōu)化可以自動開啟，不用手動寫/* +mapjoin(a,b)*/來開啟了。inner join，大表left join小表都可以直接使mapjoin生效。
mapjoin生效寫法：
mapjoin未生效寫法：
對比一下執(zhí)行計劃
mapjoin生效執(zhí)行計劃：
mapjoin未生效執(zhí)行計劃：
MapJoin簡單說就是在Map階段將小表讀入內(nèi)存，順序掃描大表完成Join。
對比兩種執(zhí)行計劃，mapjoin生效之后，只有兩個MAP任務(wù)，沒有了JOIN任務(wù)，相當(dāng)于省了一次JOIN。
mapjoin是否生效，可以看是HashJoin還是MergeJoin來判斷。
總結(jié)
mapjoin開啟之后，運行效率提高明顯，但會因為寫法、小表過大不生效，要從執(zhí)行計劃中去判斷并做參數(shù)調(diào)整保障mapjoin生效。
小表大小調(diào)整參數(shù)：set odps.sql.mapjoin.memory.max=2048（單位M）
distmapjoin：加強版mapjoin
場景介紹
對于大小表join的場景，小表經(jīng)常會超出mapjoin的最大內(nèi)存，那么mapjoin就不會生效了。ODPS提供了將中型表放入內(nèi)存的方案，即distmapjoin，用法和mapjoin相似，即在select語句中使用Hint提示/*+distmapjoin(<table_name>(shard_count=<n>,replica_count=<m>))*/才會執(zhí)行distmapjoin。shard_count（分片數(shù)，默認[200M,500M]）和replica_count（副本數(shù)，默認1）共同決定任務(wù)運行的并發(fā)度，即并發(fā)度=shard_count * replica_count。
寫法&執(zhí)行計劃探查
常規(guī)寫法：
優(yōu)化寫法：
對比執(zhí)行計劃
常規(guī)寫法：
優(yōu)化寫法：
對比兩種執(zhí)行計劃和mapjoin執(zhí)行計劃可以發(fā)現(xiàn)，優(yōu)化寫法都省去了JOIN任務(wù)，這個在很大程度上加快了運行速度和降低資源消耗，distmapjoin寫法比mapjoin寫法多了一個REDUCE任務(wù)，即對小表的分片。
distmapjoin是否生效，可以看是DistributedMapJoin1還是MergeJoin來判斷。
總結(jié)
同mapjoin總結(jié)
where限制條件寫在外層會很慢嗎？
場景介紹
日常開發(fā)中，大家都習(xí)慣性將過濾條件緊跟在讀表之后，這樣可以減少數(shù)據(jù)量以減少任務(wù)運行時間。
寫法&執(zhí)行計劃探查
過濾條件在讀表之后的規(guī)范寫法和多表join之后再過濾的非規(guī)范寫法。
印象中，規(guī)范寫法的運行效率肯定會高一些，看一下執(zhí)行計劃會發(fā)現(xiàn)兩種寫法的執(zhí)行計劃是一樣的，都在join之前做了過濾
總結(jié)
ODPS對謂詞前置做了很好的優(yōu)化，但是日常開發(fā)也盡量將過濾條件跟在讀表之后，這樣更加規(guī)范，代碼也會具有更好的可讀性。
總結(jié)
做好SQL開發(fā)、優(yōu)化，得先學(xué)會閱讀執(zhí)行計劃，多動手嘗試可以快速幫助你掌握該技能。
（本篇講到的執(zhí)行計劃，隨著ODPS的優(yōu)化，會發(fā)生改變）
