繼上一篇文章：SQL優(yōu)化之診斷篇：快速定位生產(chǎn)性能問(wèn)題實(shí)踐。本文將從優(yōu)化運(yùn)行時(shí)間和優(yōu)化資源消耗這兩個(gè)方面，介紹可以提升作業(yè)性能的常用方法。

1.優(yōu)化運(yùn)行時(shí)間

在優(yōu)化運(yùn)行時(shí)間這個(gè)維度上，我們重點(diǎn)關(guān)注時(shí)間上的加速，單位時(shí)間內(nèi)可能會(huì)消耗更多的計(jì)算資源。總成本有可能上升，也可能降低。為了縮短作業(yè)運(yùn)行時(shí)間，可以從作業(yè)并行度，數(shù)據(jù)傾斜等角度進(jìn)行優(yōu)化。

1.1?調(diào)整并行度

task 并行度不合理有很多時(shí)候是因?yàn)閿?shù)據(jù)從上游 task 計(jì)算后，數(shù)據(jù)膨脹得很厲害。我們第一步應(yīng)該做的是去檢查自己的業(yè)務(wù)邏輯有沒(méi)有問(wèn)題，是否有數(shù)據(jù)膨脹。

并行度是衡量并行計(jì)算并行程度的一個(gè)指標(biāo)，從執(zhí)行計(jì)劃上來(lái)看，比如 M1，用 1000個(gè) instance 來(lái)執(zhí)行，我們就說(shuō) M1 的并行度是 1000。需要注意的是，調(diào)整并行度不一定是調(diào)高，instance 數(shù)量過(guò)多會(huì)從兩個(gè)方面影響執(zhí)行速度：? ??

1.instance 越多，等待資源需要更長(zhǎng)的時(shí)間，排隊(duì)次數(shù)也更多。

2.每個(gè) instance 初始化需要一定的時(shí)間，并行度越高，初始化占用的總時(shí)間就越多，有效的執(zhí)行時(shí)間占比就越低。

下面列出了非人為干預(yù)情況下，影響單個(gè)task并行度的主要因素：

1.某些操作強(qiáng)制了必須?1 個(gè) instance 來(lái)執(zhí)行，例如：

2.讀表的?task：instance 數(shù)量與源數(shù)據(jù)的大小有關(guān)系。

3.非讀表的 task：instance 數(shù)量根據(jù)輸入 task 的并行度，加 task 內(nèi)部的 operator 來(lái)推算。

強(qiáng)制1個(gè)instance執(zhí)行

針對(duì)這種情況，用戶需要檢查這些操作是否必要，能否去掉，盡量取消掉這些操作。

讀表的task

對(duì)于讀表的 task，一般情況下，一個(gè) instance 讀取 256M（壓縮后的大?。┑臄?shù)據(jù)。絕大多數(shù)情況下沒(méi)有問(wèn)題，這里列出一些常見(jiàn)出問(wèn)題的情況：

1.數(shù)據(jù)壓縮比很高：256M 解壓出來(lái)后可能有好幾十 G 數(shù)據(jù)，保持默認(rèn)設(shè)置讀 256M 數(shù)據(jù)不合理，需要調(diào)小單個(gè) instance 讀取的數(shù)據(jù)量。

2.Task 中執(zhí)行了一些很 heavy 的操作，特別是存在 UDF（包含使用非結(jié)構(gòu)化存儲(chǔ)的時(shí)候用戶自定義的Extractor），有可能讀 256M 的執(zhí)行時(shí)間也不能滿足用戶需求，需要調(diào)小單個(gè) instance 讀取的數(shù)據(jù)量。

3.讀取 256M 數(shù)據(jù)太少，導(dǎo)致 instance 的執(zhí)行時(shí)間太短，而由于輸入數(shù)據(jù)很大（比如在對(duì)一年的數(shù)據(jù)做統(tǒng)計(jì)分析），反而導(dǎo)致了并行度過(guò)大，讓 instance 大多數(shù)時(shí)間在排隊(duì)等資源，需要調(diào)大單個(gè) instance 讀取的數(shù)據(jù)量。

可以通過(guò)調(diào)整split size來(lái)設(shè)置task的實(shí)例數(shù)

非讀表的task，主要有三種方式調(diào)整并行度：

1.調(diào)整 xxx.sql.mapper.split.size:非讀表 task 的并行度會(huì)受到輸入 task 的并行度影響，通過(guò)調(diào)整讀表 task 的并行度間接調(diào)整非讀表 task 的并行度。

2.通過(guò) xxx.sql.reducer.instances 強(qiáng)制設(shè)置 reducer 并行度，但是會(huì)影響所有的相關(guān) task。

3.通過(guò) xxx.sql.joiner.instances 強(qiáng)制設(shè)置 joiner 并行度，但是會(huì)影響所有相關(guān) task。

1.2?優(yōu)化執(zhí)行計(jì)劃

Map Join Hint

在對(duì)大表和一個(gè)或者多個(gè)小表執(zhí)行join操作時(shí)，若因?yàn)榻y(tǒng)計(jì)信息缺失、UDF邏輯黑盒等原因?qū)е聝?yōu)化器沒(méi)有自動(dòng)生成map join的，用戶可以手動(dòng)添加map join hint，使得原本的Sort-Merge Join變成Map Join，避免大表數(shù)據(jù)shuffle從而提升性能。

1.3 數(shù)據(jù)傾斜

在數(shù)據(jù)傾斜的情況下，通常情況下調(diào)整并行度是沒(méi)有用的，并且沒(méi)有萬(wàn)能的解決方案。一般會(huì)有下面幾種傾斜類別：

注意，數(shù)據(jù)傾斜的情況非常多，并沒(méi)有百分之百有效的解決方案。這里也只是提供一些優(yōu)化思路，經(jīng)驗(yàn)之談，大多數(shù)思路都是需要用戶去對(duì)代碼甚至算法做一定的修改。

數(shù)據(jù)Shuffle導(dǎo)致的數(shù)據(jù)傾斜

數(shù)據(jù)傾斜大多數(shù)是由于數(shù)據(jù)的 reshuffle 引起的，因?yàn)榘凑漳硞€(gè) key 來(lái)做 shuffle，同一個(gè) key 值的數(shù)據(jù)會(huì)強(qiáng)制集中在一個(gè) instance 處理。

【如何判斷是否由數(shù)據(jù) Shuffle 導(dǎo)致的傾斜】Shuffle 對(duì)應(yīng)于執(zhí)行計(jì)劃中的 streamline，如果發(fā)現(xiàn)數(shù)據(jù)傾斜的 task 是其他 task 的下游，并且上游的數(shù)據(jù)按照某個(gè) key 來(lái)做shuffle，那么首先分析一下是不是因?yàn)?shuffle 導(dǎo)致的。Shuffle 導(dǎo)致的數(shù)據(jù)傾斜一般有幾種情況：

1.Shuffle 用了區(qū)分度低（值域只包含少量的值）的 key。比如人口數(shù)據(jù)，按照“性別”來(lái)做分區(qū)，由于性別只有“男”和“女”兩種，那么數(shù)據(jù)最終會(huì)集中到兩個(gè) instance 來(lái)處理，如果并行度不是 2，除了這兩個(gè) instance 之外，其他的 instance 都是空跑。

2.Key 的區(qū)分度雖然不低，但是分布不均勻。比如以人的姓作為 shuffle key，雖然姓的值域理論上很多，但是某些大姓占據(jù)了大部分，那么處理這幾個(gè)大姓的 instance 就會(huì)集中了比其他 instance 多得多的數(shù)據(jù)，耗時(shí)也會(huì)長(zhǎng)很多。

3.數(shù)據(jù)中存在一些特殊值，比如使用 null、0 或者 ‘-’ 這類的特殊值來(lái)表示一些異常情況。這類特殊值可能在數(shù)據(jù)中占據(jù)非常大的比例，也會(huì)導(dǎo)致數(shù)據(jù)傾斜。

某些情況下，用戶并沒(méi)有意識(shí)到自己的數(shù)據(jù)里面存在一些特殊值或者熱點(diǎn)值。此時(shí)可以通過(guò)一些簡(jiǎn)單的統(tǒng)計(jì)來(lái)分析。比如下面的?query：

首先可以通過(guò)?explain，看出是按照哪列做 shuffle 的 key

通過(guò)一些簡(jiǎn)單的?aggregation，可以看到哪些 key 屬于熱點(diǎn)數(shù)據(jù)，如：

可以觀察t表a列中，哪個(gè)數(shù)據(jù)出現(xiàn)頻次比較高，比其他數(shù)據(jù)高很多。

【如何優(yōu)化數(shù)據(jù) Shuffle 導(dǎo)致的傾斜】這類數(shù)據(jù)傾斜，調(diào)整并行度是沒(méi)有用的。一般可以從幾個(gè)方面去考慮優(yōu)化：

1.去掉 shuffle，沒(méi)有了 shuffle，問(wèn)題自然就消失了。

a).JOIN導(dǎo)致的shuffle：可以考慮使用?mapjoin?來(lái)取代 mergejoin，mapjoin 下大表是不需要做 reshuffle的，這樣不僅僅消除了數(shù)據(jù)傾斜，還省了一次 shuffle 的時(shí)間。

b).GROUP BY， DISTRIBUTE BY，CLUSTERED BY，?窗口函數(shù)的PARTITION BY?等語(yǔ)法結(jié)構(gòu)導(dǎo)致的shuffle：可以去看看這些操作是否真的必要，能不能去掉，這里并沒(méi)有統(tǒng)一的解法。

2.換別的?shuffle key。在業(yè)務(wù)邏輯的允許下，更換 shuffle key 或者增加 shuffle key 的維度，能解決某些問(wèn)題。比如人口數(shù)據(jù)，能夠按照“姓名”來(lái)做 key，就不要用“姓“來(lái)做 key。

3.將熱點(diǎn)數(shù)據(jù)特殊處理。比如先將出現(xiàn)數(shù)據(jù)傾斜的 key 和其他數(shù)據(jù)分開(kāi)來(lái)，分別處理，最后再 union 起來(lái)。這個(gè)方案有時(shí)候可以簡(jiǎn)化，比如 join 的時(shí)候，特殊值有可能是已知 join 不上的，那么可以將特殊值做一些處理，把特殊值隨機(jī)化。

非Shuffle導(dǎo)致的數(shù)據(jù)傾斜

有些時(shí)候，數(shù)據(jù)傾斜不一定是 shuffle 導(dǎo)致的，也可能是別的問(wèn)題。這里列舉一些常見(jiàn)情況：

1.數(shù)表數(shù)據(jù)不均勻。讀表 instance 讀取的數(shù)據(jù)量一般都差不多，但是也不排除會(huì)出現(xiàn)長(zhǎng)尾的情況：

a).數(shù)據(jù)壓縮率不同導(dǎo)致的數(shù)據(jù)不均勻。同樣是壓縮前的 256M 數(shù)據(jù)，有可能某些 instance 解壓后是幾百M(fèi)，某些 instance 解壓后好幾 G。

b).數(shù)據(jù)文件數(shù)導(dǎo)致的不均勻。讀取數(shù)據(jù)的效率并非和數(shù)據(jù)量成正比，打開(kāi)文件，建立數(shù)據(jù)通道，關(guān)閉文件這些操作都是有消耗的。如果讀取的文件數(shù)量過(guò)多，那么這些額外消耗照成的時(shí)間消耗也會(huì)上升。為了防止由于這個(gè)問(wèn)題導(dǎo)致的數(shù)據(jù)傾斜，ODPS 會(huì)控制每個(gè) instance 讀取的小文件數(shù)，默認(rèn)是 64M。

c).文件大小有區(qū)別，導(dǎo)致讀同一數(shù)目文件，讀入的數(shù)據(jù)量也會(huì)有較大變化。? ?

2.選擇率（Selectivity）不同導(dǎo)致的數(shù)據(jù)不均勻。比如有些 instance 經(jīng)過(guò) filter，只剩下少量的數(shù)據(jù)，某些 instance filter 過(guò)后，還剩下大量的數(shù)據(jù)，從而導(dǎo)致數(shù)據(jù)傾斜。

3.計(jì)算復(fù)雜度不均勻。有些操作，比如UDF，或者case when，對(duì)于某些數(shù)據(jù)值的計(jì)算量會(huì)比其他數(shù)據(jù)值多很多。這種情況下不同?instance 的數(shù)據(jù)量雖然差別也不大，但是可能計(jì)算量差很多，耗時(shí)也會(huì)差很多。

4.數(shù)據(jù)膨脹不均勻。有些操作會(huì)導(dǎo)致數(shù)據(jù)膨脹，比如 join，grouping sets aggregate，UDTF。這類操作可能在不同的?instance 下由于數(shù)據(jù)內(nèi)容不一致，導(dǎo)致了膨脹率差別很大，最終也會(huì)導(dǎo)致數(shù)據(jù)傾斜。

5.繼承的數(shù)據(jù)不均勻。有些情況下，數(shù)據(jù)傾斜并不是 task 本身產(chǎn)生的，而是從上游 task 帶下來(lái)的。比如數(shù)據(jù)膨脹的 task 自身沒(méi)有讓膨脹率大的 instance 執(zhí)行時(shí)間過(guò)長(zhǎng)而受到關(guān)注，下游的 task 反而出現(xiàn)了異常。

【優(yōu)化思路】這些數(shù)據(jù)傾斜，通常能通過(guò)調(diào)整并行度來(lái)進(jìn)行一定的緩解。當(dāng)然還有其他的：

1.如果是小文件太多，導(dǎo)致了讀表 instance 的數(shù)據(jù)傾斜，那么可以考慮先對(duì)輸入數(shù)據(jù)表做小文件合并：

2.Map 端每個(gè) Instance 讀入的數(shù)據(jù)量不均勻時(shí)，可以調(diào)整 ODPS 最多能讀取的小文件數(shù)，進(jìn)行小文件的合并使得讀入數(shù)據(jù)量均勻。這個(gè)參數(shù)一般會(huì)和odps.sql.mapper.split.size 結(jié)合使用。

1.選擇率不同導(dǎo)致的長(zhǎng)尾或者計(jì)算復(fù)雜度不同導(dǎo)致的長(zhǎng)尾，正常情況下不會(huì)太突出，如果出現(xiàn)了，可以先看看自己的程序是不是有?bug，比如 UDF 是不是在某些情況下有死循環(huán)。

2.不僅僅是數(shù)據(jù)傾斜，數(shù)據(jù)膨脹過(guò)大本身就有問(wèn)題。

3.為防止上游的數(shù)據(jù)傾斜對(duì)下游產(chǎn)生影響，一個(gè)可能的做法是，通過(guò)使用distribute by rand()?來(lái)強(qiáng)制上游數(shù)據(jù)做reshuffle。

2.優(yōu)化消耗資源

為了節(jié)省資源消耗，可以通過(guò)使用SQL新語(yǔ)法、新功能，合理設(shè)置資源參數(shù)等方式來(lái)進(jìn)行優(yōu)化。

2.1 MR典型場(chǎng)景用SQL實(shí)現(xiàn)

目前線上很多 MapReduce 程序功能都可以使用 SQL 新功能實(shí)現(xiàn)，MR 作業(yè)修改為 SQL 后表達(dá)更為直觀，升級(jí)維護(hù)更為方便，SQL 執(zhí)行計(jì)劃經(jīng)過(guò)多層優(yōu)化后，基于代碼生成器生成，在深度優(yōu)化執(zhí)行引擎 NATIVE 執(zhí)行，性能和穩(wěn)定性保障性更高。

1.使用?SQL-聚合函數(shù)

適用場(chǎng)景：對(duì)數(shù)據(jù)做匯總和合并操作的?MR 程序。

例如，安全需求統(tǒng)計(jì)每個(gè)電話設(shè)備呼入呼出號(hào)碼及次數(shù)并且要求每個(gè)設(shè)備保存一條記錄；MR 程序通過(guò) MAP 讀取到數(shù)據(jù)后按設(shè)備分組，REDUCE 對(duì)同設(shè)備匯總各類信息打標(biāo)簽統(tǒng)計(jì)合并輸出。SQL 聚合函數(shù)可以表示為：

類似有 COLLECT_LIST、COLLECT_SET等函數(shù)可以表示在給定 GROUP 內(nèi)將 COL 指定的表達(dá)式聚合為數(shù)組。

2.用 SQL-窗口/分析函數(shù)

適用場(chǎng)景：聚合表達(dá)不了的作業(yè)。例如組內(nèi)多條對(duì)比分析的作業(yè)可以考慮使用開(kāi)窗函數(shù)，限制條件是分組后組內(nèi)數(shù)據(jù)量不超過(guò)1億。

例如，通訊軟件如微信如果有需求要對(duì)每一條對(duì)話消息內(nèi)容分析(打標(biāo)簽、回復(fù)類型判斷等)，MR 程序可以通過(guò)在 REDUCE 中對(duì)同組數(shù)據(jù)按時(shí)間排序，然后獲取前后 1 條或 n 條記錄與當(dāng)前記錄在一起分析；類似需求也可以用 SQL-開(kāi)窗函數(shù)提供的 LEAD、LAG 等函數(shù)獲取前后偏移的數(shù)據(jù)進(jìn)行對(duì)比分析。

2.2?合理設(shè)置資源參數(shù)

盡量不要通過(guò)設(shè)置參數(shù)的方式進(jìn)行優(yōu)化，首先要看能否從業(yè)務(wù)上或者算法上減少數(shù)據(jù)和其他方式進(jìn)行優(yōu)化。

ODPS 處理一個(gè)任務(wù)主要分為三個(gè)階段：Map、Reduce、Join。如果處理的數(shù)據(jù)量比較大，導(dǎo)致各個(gè)階段的每個(gè) instance 處理的時(shí)間比較長(zhǎng)，在沒(méi)有數(shù)據(jù)傾斜的情況下，可以通過(guò)設(shè)置下面的資源參數(shù)適當(dāng)增加資源來(lái)加快處理速度。

2.2.1?Map端設(shè)置

作用：設(shè)置處理Map Task每個(gè)Instance的CPU數(shù)目，默認(rèn)為100，在[50,800]之間調(diào)整。

場(chǎng)景：某些任務(wù)如果特別耗計(jì)算資源的話，可以適當(dāng)調(diào)整cpu數(shù)目。對(duì)于大多數(shù)sql任務(wù)來(lái)說(shuō)，一般不需要調(diào)整cpu個(gè)數(shù)的。

作用：設(shè)定Map Task每個(gè)Instance的Memory大小，單位M，默認(rèn)1024M，在[256,12288]之間調(diào)整。

場(chǎng)景：當(dāng)Map階段的Instance有Writer Dumps時(shí)，可以適當(dāng)?shù)脑黾觾?nèi)存大小，減少Dumps所花的時(shí)間。

作用：設(shè)定控制文件被合并的最大閾值，單位M，默認(rèn)64M，在[0,Integer.MAX_VALUE]之間調(diào)整。

場(chǎng)景：當(dāng)Map端每個(gè)Instance讀入的數(shù)據(jù)量不均勻時(shí)，可以通過(guò)設(shè)置這個(gè)變量值進(jìn)行小文件的合并，使得每個(gè)Instance的讀入文件均勻。一般會(huì)和

?這個(gè)參數(shù)結(jié)合使用。

作用：設(shè)定一個(gè)Map的最大數(shù)據(jù)輸入量，可以通過(guò)設(shè)置這個(gè)變量達(dá)到對(duì)Map端輸入的控制，單位M，默認(rèn)256M，在[1,Integer.MAX_VALUE]之間調(diào)整。

場(chǎng)景：當(dāng)每個(gè)Map Instance處理的數(shù)據(jù)量比較大，時(shí)間比較長(zhǎng)，并且沒(méi)有發(fā)生長(zhǎng)尾時(shí)，可以適當(dāng)調(diào)小這個(gè)參數(shù)。如果有發(fā)生長(zhǎng)尾，則結(jié)合

這個(gè)參數(shù)設(shè)置每個(gè)Map的輸入數(shù)量。

2.2.2?Join設(shè)置

作用: 設(shè)定Join Task的Instance數(shù)量，默認(rèn)為-1，在[0,2000]之間調(diào)整。

場(chǎng)景：每個(gè)Join Instance處理的數(shù)據(jù)量比較大，耗時(shí)較長(zhǎng)，沒(méi)有發(fā)生長(zhǎng)尾，可以考慮增大使用這個(gè)參數(shù)。

作用: 設(shè)定Join Task每個(gè)Instance的CPU數(shù)目，默認(rèn)為100，在[50,800]之間調(diào)整。

場(chǎng)景：某些任務(wù)如果特別耗計(jì)算資源的話，可以適當(dāng)調(diào)整CPU數(shù)目。對(duì)于大多數(shù)SQL任務(wù)來(lái)說(shuō)，一般不需要調(diào)整CPU。

作用：設(shè)定Join Task每個(gè)Instance的Memory大小，單位為M，默認(rèn)為1024M,在[256,12288]之間調(diào)整。

場(chǎng)景：當(dāng)Join階段的Instance有Writer Dumps時(shí)，可以適當(dāng)?shù)脑黾觾?nèi)存大小，減少Dumps所花的時(shí)間。

2.2.3?Reduce端設(shè)置

作用: 設(shè)定Reduce Task的Instance數(shù)量，手動(dòng)設(shè)置區(qū)間在[1,99999]之間調(diào)整。

場(chǎng)景：每個(gè)Join Instance處理的數(shù)據(jù)量比較大，耗時(shí)較長(zhǎng)，沒(méi)有發(fā)生長(zhǎng)尾，可以考慮增大使用這個(gè)參數(shù)。

作用：設(shè)定處理Reduce Task每個(gè)Instance的Cpu數(shù)目，默認(rèn)為100，在[50,800]之間調(diào)整。

場(chǎng)景：某些任務(wù)如果特別耗計(jì)算資源的話，可以適當(dāng)調(diào)整Cpu數(shù)目。對(duì)于大多數(shù)Sql任務(wù)來(lái)說(shuō)，一般不需要調(diào)整Cpu。

作用：設(shè)定Reduce Task每個(gè)Instance的Memory大小，單位M，默認(rèn)1024M，在[256,12288]之間調(diào)整。

場(chǎng)景：當(dāng)Reduce階段的Instance有Writer Dumps時(shí)，可以適當(dāng)?shù)脑黾觾?nèi)存的大小，減少Dumps所花的時(shí)間。