背景

目前市面上大數(shù)據(jù)查詢(xún)分析引擎層出不窮，如Spark，Hive，Presto等，因其友好的SQL語(yǔ)法，被廣泛應(yīng)用于各領(lǐng)域分析，公司內(nèi)部也有優(yōu)秀的ODPS SQL供用戶(hù)使用。筆者所在團(tuán)隊(duì)的項(xiàng)目也借用ODPS SQL去檢測(cè)業(yè)務(wù)中潛在的安全風(fēng)險(xiǎn)。在給業(yè)務(wù)方使用與答疑過(guò)程中，我們發(fā)現(xiàn)大多含有性能瓶頸的SQL，主要集中在數(shù)據(jù)傾斜與數(shù)據(jù)膨脹問(wèn)題中。因此，本文主要基于團(tuán)隊(duì)實(shí)際開(kāi)發(fā)經(jīng)驗(yàn)與積累，并結(jié)合業(yè)界對(duì)大數(shù)據(jù)SQL的使用與優(yōu)化，嘗試給出相對(duì)系統(tǒng)性的解決方案。

本文主要涉及業(yè)務(wù)SQL執(zhí)行層面的優(yōu)化，暫不涉及參數(shù)優(yōu)化。若設(shè)置參數(shù)，首先確定執(zhí)行層面哪個(gè)階段（Map/Reduce/Join）任務(wù)執(zhí)行時(shí)間較長(zhǎng)，從而設(shè)置對(duì)應(yīng)參數(shù)。

本文主要分為以下三個(gè)部分：
一、會(huì)引入數(shù)據(jù)傾斜與數(shù)據(jù)膨脹問(wèn)題
二、介紹當(dāng)數(shù)據(jù)傾斜與數(shù)據(jù)膨脹發(fā)生時(shí)，如何排查與定位
三、會(huì)從系統(tǒng)層面給出常見(jiàn)優(yōu)化思路。

一、問(wèn)題篇

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

數(shù)據(jù)傾斜是指在分布式計(jì)算時(shí)，大量相同的key被分發(fā)到同一個(gè)reduce節(jié)點(diǎn)中。針對(duì)某個(gè)key值的數(shù)據(jù)量比較多，會(huì)導(dǎo)致該節(jié)點(diǎn)的任務(wù)數(shù)據(jù)量遠(yuǎn)大于其他節(jié)點(diǎn)的平均數(shù)據(jù)量，運(yùn)行時(shí)間遠(yuǎn)高于其他節(jié)點(diǎn)的平均運(yùn)行時(shí)間，拖累了整體SQL執(zhí)行時(shí)間。

其主要原因是key值分布不均導(dǎo)致的Reduce處理數(shù)據(jù)不均勻。本文將從Map端優(yōu)化，Reduce端優(yōu)化和Join端優(yōu)化三方面給出相應(yīng)解決方案。

數(shù)據(jù)膨脹

數(shù)據(jù)膨脹是指任務(wù)的輸出條數(shù)/數(shù)據(jù)量級(jí)比輸入條數(shù)/數(shù)據(jù)量級(jí)大很多，如100M的數(shù)據(jù)作為任務(wù)輸入，最后輸出1T的數(shù)據(jù)。這種情況不僅運(yùn)行效率會(huì)降低，部分任務(wù)節(jié)點(diǎn)在運(yùn)行key值量級(jí)過(guò)大時(shí)，有可能發(fā)生資源不足或失敗情況。

二、排查定位篇

本節(jié)主要關(guān)注于業(yè)務(wù)SQL本身引起的長(zhǎng)時(shí)間運(yùn)行或者失敗，對(duì)于集群資源情況，平臺(tái)故障本身暫不考慮在內(nèi)。

1. 首先檢查輸入數(shù)據(jù)量級(jí)。與其他天相比有無(wú)明顯量級(jí)變化，是否因?yàn)閿?shù)據(jù)量級(jí)的問(wèn)題天然引起任務(wù)運(yùn)行時(shí)間過(guò)長(zhǎng)，如雙11，雙十二等大促節(jié)點(diǎn)。
2. 觀察執(zhí)行任務(wù)拆分后各個(gè)階段運(yùn)行時(shí)間。與其他天相比有無(wú)明顯量級(jí)變化；在整個(gè)執(zhí)行任務(wù)中時(shí)間耗時(shí)占比情況。
3. 最耗時(shí)階段中，觀察各個(gè)Task的運(yùn)行情況。Task列表中，觀察是否存在某幾個(gè)Task實(shí)例耗時(shí)明顯比平均耗時(shí)更長(zhǎng)，是否存在某幾個(gè)Task實(shí)例處理輸入/輸出數(shù)據(jù)量級(jí)比平均數(shù)據(jù)量級(jí)消費(fèi)產(chǎn)出更多。
4. 根據(jù)步驟3中定位代碼行數(shù)，定位問(wèn)題業(yè)務(wù)處理邏輯。

三、優(yōu)化篇

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

1. Map端優(yōu)化

• 1.1 讀取數(shù)據(jù)合并

在數(shù)據(jù)源讀取查詢(xún)時(shí)，動(dòng)態(tài)分區(qū)數(shù)過(guò)多可能造成小文件數(shù)過(guò)多，每個(gè)小文件至少都會(huì)作為一個(gè)塊啟動(dòng)一個(gè)Map任務(wù)來(lái)完成。對(duì)于文件數(shù)量而言，等于 map數(shù)量 * 分區(qū)數(shù)。對(duì)于一個(gè)Map任務(wù)而言，其初始化的時(shí)間可能遠(yuǎn)遠(yuǎn)大于邏輯處理時(shí)間，因此通過(guò)調(diào)整Map參數(shù)把小文件合并成大文件進(jìn)行處理，避免造成很大的資源浪費(fèi)。

• 1.2 列裁剪

減少使用select * from table語(yǔ)句，過(guò)多選擇無(wú)用列會(huì)增加數(shù)據(jù)在集群上傳輸?shù)腎O開(kāi)銷(xiāo)；對(duì)于數(shù)據(jù)選擇，需要加上分區(qū)過(guò)濾條件進(jìn)行篩選數(shù)據(jù)。

• 1.3 謂詞下推

在不影響結(jié)果的情況下，盡可能將過(guò)濾條件表達(dá)式靠近數(shù)據(jù)源位置，使之提前執(zhí)行。通過(guò)在map端過(guò)濾減少數(shù)據(jù)輸出，降低集群IO傳輸，從而提升任務(wù)的性能。

• 1.4 數(shù)據(jù)重分布

在Map階段做聚合時(shí)，使用隨機(jī)分布函數(shù)distribute by rand()，控制Map端輸出結(jié)果的分發(fā)，即map端如何拆分?jǐn)?shù)據(jù)給reduce端（默認(rèn)hash算法），打亂數(shù)據(jù)分布，至少不會(huì)在Map端發(fā)生數(shù)據(jù)傾斜。

2. Reduce端優(yōu)化

• 2.1 關(guān)聯(lián)key空值檢驗(yàn)

部分實(shí)例發(fā)生長(zhǎng)尾效應(yīng)，很大程度上由于null值，空值導(dǎo)致，使得Reduce時(shí)含有臟值的數(shù)據(jù)被分發(fā)到同一臺(tái)機(jī)器中。
針對(duì)這種問(wèn)題SQL，首先確認(rèn)包含無(wú)效值的數(shù)據(jù)源表是否可以在Map階段直接過(guò)濾掉這些異常數(shù)據(jù)；如果后續(xù)SQL邏輯仍然需要這些數(shù)據(jù)，可以通過(guò)將空值轉(zhuǎn)變成隨機(jī)值，既不影響關(guān)聯(lián)也可以避免聚集。

SELECT??ta.id
FROM????ta
LEFT?JOIN?tb
ON??????coalesce(ta.id?,?rand())?=?tb.id;

• 2.2 排序優(yōu)化

Order by為全局排序，當(dāng)表數(shù)據(jù)量過(guò)大時(shí)，性能可能會(huì)出現(xiàn)瓶頸；Sort by為局部排序，確保Reduce任務(wù)內(nèi)結(jié)果有序，全局排序不保證；Distribute by按照指定字段進(jìn)行Hash分片，把數(shù)據(jù)劃分到不同的Reducer中；CLUSTER BY：根據(jù)指定的字段進(jìn)行分桶，并在桶內(nèi)進(jìn)行排序，可以認(rèn)為cluster by是distribute by+sort by。

對(duì)于排序而言，嘗試用distribute by+sort by確保reduce中結(jié)果有序，最后在全局有序。

--?原始腳本
select?*
from?user_pay_table
where?dt?=?'20221015'
order?by?amt
limit?500
;

--?改進(jìn)腳本
SELECT??*
FROM????user_pay_table
WHERE???dt?=?'20221015'
DISTRIBUTE?BY?(?CASE
???????????????????WHEN?amt????????????????????WHEN?amt?>=?100?AND?age?<=?2000?THEN?1
???????????????????ELSE?2
?????????????????END?)
?SORT?BY?amt
LIMIT?500
;

3. Join端優(yōu)化

• 3.1 大表join小表

通過(guò)將需要join的小表分發(fā)至map端內(nèi)存中，將Join操作提前至map端執(zhí)行，避免因分發(fā)key值不均勻引發(fā)的長(zhǎng)尾效應(yīng)，復(fù)雜度從（M*N）降至（M+N），從而提高執(zhí)行效率。ODPS SQL與Hive SQL使用mapjoin，SPARK使用broadcast。

• 3.2 大表join大表

長(zhǎng)尾效應(yīng)由熱點(diǎn)數(shù)據(jù)導(dǎo)致，可以將熱點(diǎn)數(shù)據(jù)加入白名單中，通過(guò)對(duì)白名單數(shù)據(jù)和非白名單數(shù)據(jù)分別處理，再合并數(shù)據(jù)。

具體表現(xiàn)為打散傾斜key，進(jìn)行兩端聚合(針對(duì)聚合)或者拆分傾斜key進(jìn)行打散然后再合并數(shù)據(jù)。

數(shù)據(jù)膨脹

1. 避免笛卡爾積

Join關(guān)聯(lián)條件有誤，表Join進(jìn)行笛卡爾積，造成數(shù)據(jù)量爆炸。

2. 關(guān)聯(lián)key區(qū)分度校驗(yàn)

關(guān)注JoinKey區(qū)分度，key值區(qū)分度越低（distinct數(shù)量少），越有可能造成數(shù)據(jù)爆炸情況。如用戶(hù)下的性別列，交易下的省市列等。

3. 聚合操作誤用

部分聚合操作需要將中間結(jié)果記錄下來(lái)，最后再生成最終結(jié)果，這使得在select操作時(shí)，按照不同維度去重Distinct、不同維度開(kāi)窗計(jì)算over Partition By可能會(huì)導(dǎo)致數(shù)據(jù)膨脹。針對(duì)這種業(yè)務(wù)邏輯，可以將一個(gè)SQL拆分成多個(gè)SQL分別進(jìn)行處理操作。

總結(jié)

大數(shù)據(jù)SQL優(yōu)化是一項(xiàng)涉及知識(shí)面較廣的工作，除了分析現(xiàn)有執(zhí)行計(jì)劃之外，還需要學(xué)習(xí)相應(yīng)查詢(xún)分析引擎設(shè)計(jì)原理。針對(duì)我們?nèi)粘Ｓ龅降膯?wèn)題現(xiàn)總結(jié)分享給大家，供大家查閱。