//
Transwarp - 新聞詳情
http://www.transwarp.io/news/detail?id=161
在《Hadoop平臺(tái)中SQL優(yōu)化的四個(gè)思路》一文中，我們對(duì)Hadoop平臺(tái)中的SQL優(yōu)化思路做了簡(jiǎn)單介紹剔桨，為的是讓讀者能對(duì)SQL優(yōu)化有一個(gè)宏觀掌握忌锯。
本文將針對(duì)Transwarp Data Hub（TDH）中的常見SQL的場(chǎng)景，繼續(xù)深入討論如何對(duì)不同類型SQL選擇不同的優(yōu)化策略领炫。根據(jù)SQL特性以及數(shù)據(jù)特性，本文把TDH中涉及的SQL場(chǎng)景分為以下六類张咳，分別對(duì)它們的優(yōu)化方法進(jìn)行分析帝洪。
- 大表與大表的普通Join
- 大表與小表的MapJoin
- 重復(fù)元素多的場(chǎng)景
- Map Task數(shù)量多執(zhí)行時(shí)間短
- Reduce Task數(shù)量多執(zhí)行時(shí)間短
- 利用臨時(shí)表優(yōu)化的混合場(chǎng)景
大表與大表的普通JOIN
這里所說(shuō)的普通JOIN并不單指語(yǔ)句中的JOIN操作，而是指它的實(shí)現(xiàn)方法脚猾。許多SQL處理引擎葱峡，如Inceptor，會(huì)根據(jù)JOIN左右兩表的尺寸龙助、尺寸差距砰奕、數(shù)據(jù)切分方式，采用不同的JOIN執(zhí)行策略提鸟，普通JOIN是其中最基礎(chǔ)的一種军援。
實(shí)現(xiàn)普通JOIN的過(guò)程是這樣的：掃描過(guò)濾兩張表的數(shù)據(jù)（Map Stages），然后通過(guò)Shuffle將Key哈希值相同的數(shù)據(jù)分發(fā)到各個(gè)節(jié)點(diǎn)称勋，在各節(jié)點(diǎn)內(nèi)部執(zhí)行JOIN（Reduce Stages）胸哥，示意如下。
普通JOIN通常在兩表數(shù)據(jù)量都很大的情況下被采用赡鲜。在執(zhí)行它時(shí)應(yīng)重點(diǎn)關(guān)注Shuffle 和JOIN Stage節(jié)點(diǎn)的數(shù)據(jù)量是否過(guò)大空厌，是否有磁盤溢出趨勢(shì)，若有此傾向银酬，請(qǐng)適當(dāng)增加Reduce Task 數(shù)目嘲更。
注意，數(shù)據(jù)量極大的Shuffle對(duì)網(wǎng)絡(luò)通信有很嚴(yán)重的影響揩瞪，因而大表之間的普通JOIN并不被推薦赋朦，只是和其他JOIN方式相比，普通JOIN對(duì)于兩張大表JOIN而言已經(jīng)是性能很好的處理方式了。所以有多表JOIN時(shí)北发，必須盡量避免大表與大表直接JOIN纹因，如果語(yǔ)句中有小表，務(wù)必先用小表過(guò)濾大表琳拨，即盡可能先做與小表有關(guān)的JOIN瞭恰，再加入大表。
大表與小表的MapJoin
MapJoin是一種針對(duì)大表與小表JOIN的特殊實(shí)現(xiàn)方式狱庇，在大小表數(shù)據(jù)量懸殊的情況下能有效的提升JOIN執(zhí)行效率惊畏，一般受優(yōu)化開關(guān)或者Hint控制啟動(dòng)。
MapJoin的基本思想是將小表的數(shù)據(jù)廣播給每個(gè)Executor密任，Executor拿到數(shù)據(jù)后颜启，會(huì)為小表建立Hash表，并讀取本地大表的數(shù)據(jù)塊浪讳，根據(jù)Join Key查小表Hash表缰盏，完成JOIN。其過(guò)程示意如下圖所示：
和普通的JOIN相比淹遵，MapJoin對(duì)大表的操作都是在本地完成口猜，減少了Shuffle，繞過(guò)了大量的大表數(shù)據(jù)傳輸透揣，所以網(wǎng)絡(luò)開銷小济炎，特別適用于大小表的JOIN。
但是它有兩個(gè)主要缺點(diǎn)：小表的Hash表需要常駐內(nèi)存辐真，所以內(nèi)存開銷大须尚；小表的數(shù)據(jù)廣播和Hash表的建立是串行執(zhí)行，會(huì)影響效率侍咱。為了減弱這兩種缺點(diǎn)帶來(lái)的影響耐床，執(zhí)行MapJoin時(shí)須重點(diǎn)關(guān)注JOIN的順序和過(guò)濾后小表的行數(shù)：優(yōu)先執(zhí)行過(guò)濾性較高的MapJoin；且小表的行數(shù)不可過(guò)大楔脯，通常允許的小表上限為20萬(wàn)條咙咽。注意，如果JOIN的大表極大淤年，考慮到大表與小表的大小相對(duì)性钧敞，允許將可接受的小表行數(shù)上限提升至100萬(wàn)條。
重復(fù)元素多
如果某SQL訪問(wèn)的數(shù)據(jù)中重復(fù)元素很多麸粮，而且需要Shuffle溉苛，將容易導(dǎo)致Shuffle有較大數(shù)據(jù)量，對(duì)網(wǎng)絡(luò)I/O和內(nèi)存都會(huì)帶來(lái)很大的負(fù)擔(dān)弄诲。如果我們能夠在Shuffle前消除或者合并這些重復(fù)元素愚战，將緩解這種壓力娇唯。
建議的做法是，執(zhí)行SQL之前檢查數(shù)據(jù)聚合率寂玲，如果重復(fù)元素很多塔插，聚合率高，請(qǐng)考慮在保證查詢語(yǔ)義不變的情況下用GROUP BY去重拓哟，或者改寫為其他形式的實(shí)現(xiàn)方式想许。
例如，如果某語(yǔ)句執(zhí)行兩表的普通JOIN断序，并對(duì)JOIN結(jié)果的JOIN Key去重流纹，如下面這條SQL：
其中，被掃描的兩表student和user數(shù)據(jù)量都很大违诗，而且關(guān)于JOIN Key都有很高的聚合率漱凝，此時(shí)可以先做GROUP BY去重，再JOIN诸迟，即把上面的語(yǔ)句改寫為如下形式茸炒，可以獲得更優(yōu)性能：
Map Task數(shù)量多執(zhí)行時(shí)間短
Map Task的數(shù)量同數(shù)據(jù)塊的數(shù)量大小相關(guān)，比如某表有40萬(wàn)個(gè)數(shù)據(jù)塊阵苇，以它為數(shù)據(jù)掃描源時(shí)扣典，會(huì)對(duì)應(yīng)產(chǎn)生40萬(wàn)個(gè)Map Task。當(dāng)Map Task數(shù)量多且執(zhí)行時(shí)間短時(shí)慎玖，說(shuō)明存在大量小文件。過(guò)多的小文件將對(duì)性能產(chǎn)生巨大的損耗笛粘，因?yàn)槿蝿?wù)本身的啟動(dòng)需要開銷趁怔，一旦Map Task處理的數(shù)量很少，處理能力不飽和薪前，就會(huì)使任務(wù)分配過(guò)程占用過(guò)多不必要的資源润努，引起引擎的執(zhí)行效率下降。
本文建議盡量在不改變數(shù)據(jù)分布的前提下示括，采取措施對(duì)數(shù)據(jù)塊進(jìn)行合并铺浇，從而減少數(shù)據(jù)塊和需啟動(dòng)的Map Task數(shù)目。
注意垛膝，不建議為每個(gè)Task安排過(guò)多的任務(wù)量鳍侣，因?yàn)槿蝿?wù)量過(guò)多會(huì)減少并行工作的Task數(shù)量，同樣影響效率吼拥。在設(shè)計(jì)數(shù)據(jù)塊尺寸下限時(shí)倚聚，盡量保證單個(gè)Task的處理時(shí)間不低于2s，設(shè)計(jì)上限時(shí)凿可，對(duì)應(yīng)Task的GC時(shí)間占總執(zhí)行時(shí)間的比例不應(yīng)超過(guò)20%。
Reduce Task數(shù)量多執(zhí)行時(shí)間短
和上面的場(chǎng)景類似，如果創(chuàng)建過(guò)多Reduce Task惨驶，每個(gè)Task通過(guò)Shuffle僅獲得很少數(shù)據(jù)量白热，任務(wù)啟動(dòng)消耗的資源占據(jù)大量比重，將導(dǎo)致執(zhí)行效率低下粗卜。
對(duì)于這樣的場(chǎng)景，請(qǐng)減少Reduce Task的數(shù)量。
通常(不是絕對(duì))茵烈，大表JOIN或者GROUP BY后存璃，產(chǎn)生的數(shù)據(jù)量相對(duì)原始數(shù)據(jù)小很多粘招。這時(shí)可以減少后面Reduce Task的數(shù)目偎球，使Reduce Task的啟動(dòng)更有價(jià)值。
利用臨時(shí)表優(yōu)化的混合場(chǎng)景
當(dāng)SQL混合了以上多種場(chǎng)景時(shí)，分析和優(yōu)化的難度將增加淌友，此時(shí)創(chuàng)建臨時(shí)表是一種有效的的降低語(yǔ)句復(fù)雜度的手段震庭。臨時(shí)表是一種存放在系統(tǒng)的臨時(shí)文件夾中的表匪蝙，在退出數(shù)據(jù)庫(kù)之后被自動(dòng)釋放苫昌。創(chuàng)建臨時(shí)表的好處是，能夠幫助分析語(yǔ)句各部分影響性能的程度，發(fā)現(xiàn)阻礙性能表現(xiàn)的根源。而且秽澳，對(duì)于實(shí)例中會(huì)被經(jīng)常執(zhí)行的語(yǔ)句塊演痒，為它們創(chuàng)建臨時(shí)表可以避免對(duì)相同的語(yǔ)句的反復(fù)編譯運(yùn)行。
建議在處理復(fù)雜SQL時(shí)采用如下步驟：首先拆解SQL忧吟，對(duì)語(yǔ)句中的子查詢或者中間生成的計(jì)算結(jié)果創(chuàng)建臨時(shí)表溜族；接著構(gòu)造一個(gè)新的語(yǔ)句，將這些臨時(shí)表串聯(lián)起來(lái)用于計(jì)算，達(dá)到和原語(yǔ)句同樣的效果。注意悲靴，在創(chuàng)建臨時(shí)表以及構(gòu)造新的語(yǔ)句時(shí)若遇到上面的五種場(chǎng)景仪壮，應(yīng)遵循對(duì)應(yīng)的優(yōu)化策略。
下面有三種可創(chuàng)建為臨時(shí)表的常見場(chǎng)景：
a. WITH-AS
如果SQL中有WITH-AS短語(yǔ)爪瓜，且短語(yǔ)內(nèi)的部分執(zhí)行耗時(shí)較久，如果你希望避免今后對(duì)這段語(yǔ)句重復(fù)執(zhí)行谅猾，可以將其內(nèi)容提取出來(lái)建為臨時(shí)表。
b. 非關(guān)聯(lián)子查詢
不涉及兩張表之間關(guān)聯(lián)的查詢稱為非關(guān)聯(lián)子查詢税娜。如果語(yǔ)句中有非關(guān)聯(lián)子查詢坐搔，可直接將子查詢建為臨時(shí)表。
**c. **關(guān)聯(lián)子查詢****
對(duì)于不屬于以上兩種情況的SQL語(yǔ)句敬矩，一般會(huì)涉及子查詢與外部查詢之間關(guān)鍵字關(guān)聯(lián)的問(wèn)題概行，由于表之間因條件限制而產(chǎn)生了相互制約，因此此類問(wèn)題分析起來(lái)會(huì)更復(fù)雜一些弧岳，用戶需要通過(guò)分析執(zhí)行計(jì)劃來(lái)決定如何分割原語(yǔ)句凳忙。操作順序通常是业踏，EXPLAIN語(yǔ)句，然后根據(jù)執(zhí)行計(jì)劃的順序消略，手工創(chuàng)建臨時(shí)表堡称。
整體思路
通過(guò)對(duì)以上六種場(chǎng)景的分析，本文推薦了一些TDH平臺(tái)中對(duì)不同情況適用的SQL優(yōu)化方法艺演。最后在這里對(duì)SQL優(yōu)化設(shè)計(jì)的一般流程和整體思路提供一些建議却紧，希望可以幫助讀者把握基本的優(yōu)化方向。
Step1：拿到SQL時(shí)胎撤，請(qǐng)先解決語(yǔ)法不兼容問(wèn)題晓殊。因?yàn)閺S家標(biāo)準(zhǔn)不同，需要在當(dāng)前使用的平臺(tái)上按規(guī)范統(tǒng)一伤提。
Step2：然后抽取若干典型代表的SQL巫俺，運(yùn)行一遍，看兼容問(wèn)題是否解決肿男，語(yǔ)句是否可以實(shí)現(xiàn)介汹。接著對(duì)選出的這幾個(gè)語(yǔ)句進(jìn)行優(yōu)化并運(yùn)行。一定不要直接執(zhí)行所有的語(yǔ)句舶沛，一是因?yàn)榈却Y(jié)果的耗時(shí)很長(zhǎng)嘹承，二是因?yàn)樵黾恿朔治龅睦щy度。
Step3：觀察是否存在這樣兩種異常：1. Task數(shù)目十分龐大如庭，且單個(gè)耗時(shí)短叹卷；2.某（些）Stage運(yùn)行速度很慢。
Step4：針對(duì)Step3發(fā)現(xiàn)的異常坪它，進(jìn)行性能優(yōu)化骤竹。檢查和優(yōu)化的過(guò)程大體分為以下五個(gè)方面：
資源使用。觀察系統(tǒng)資源占用量與使用情況往毡。
分析數(shù)據(jù)蒙揣。獲取每個(gè)表的各行各列的特性，比較分析是否存在記錄行數(shù)很大的表开瞭、表與表的特點(diǎn)差異和記錄行數(shù)量級(jí)的差距懒震。
分析執(zhí)行計(jì)劃。明確是否應(yīng)該用MapJoin惩阶，是否應(yīng)調(diào)整JOIN順序挎狸，是否需要謂詞下推扣汪。
分析過(guò)濾率断楷。計(jì)算過(guò)濾率，核對(duì)JOIN順序并做出調(diào)整崭别，先JOIN數(shù)據(jù)量少過(guò)濾率高的表冬筒。
對(duì)SQL分類恐锣，選擇典型，重點(diǎn)分析舞痰。從最影響性能且容易改善的部分開始土榴，不斷優(yōu)化、迭代响牛，直至得到滿意效果玷禽。
Step5：最終把選用的優(yōu)化手段按類型應(yīng)用到其他語(yǔ)句上。
對(duì)SQL優(yōu)化進(jìn)行實(shí)踐的過(guò)程中呀打，需要讀者不僅僅只是理解這些優(yōu)化思想矢赁，更重要的是在足夠多的案例中去累積經(jīng)驗(yàn)，多嘗試多比較贬丛，提高對(duì)特征SQL的敏銳度撩银，將充裕的理論轉(zhuǎn)化為價(jià)值，將了解的事物變成屬于自己的東西豺憔。
