關(guān)于 sql 語句的執(zhí)行順序網(wǎng)上有很多資料,但是大多都沒進(jìn)行驗(yàn)證,并且很多都有點(diǎn)小錯誤,尤其是對于 select 和 group by 執(zhí)行的先后順序,有說 select 先執(zhí)行,有說 group by 先執(zhí)行,到底它倆誰先執(zhí)行呢?

今天我們通過 explain 來驗(yàn)證下 sql 的執(zhí)行順序。

在驗(yàn)證之前,先說結(jié)論,Hive 中 sql 語句的執(zhí)行順序如下:

from ．． where ．． join ．． on ．． select ．． group by ．． select ．． having ．． distinct ．． order by ．． limit ．． union/union all

可以看到 group by 是在兩個 select 之間,我們知道 Hive 是默認(rèn)開啟 map 端的 group by 分組的,所以在 map 端是 select 先執(zhí)行,在 reduce 端是 group by先執(zhí)行。

下面我們通過一個 sql 語句分析下:

select
 sum(b．order_amount) sum_amount,
 count(a．userkey) count_user
from user_info a
left join user_order b
 on a．idno=b．idno
where a．idno > '112233'
group by a．idno
 having count_user>1
limit 10;

上面這條 sql 語句是可以成功執(zhí)行的,我們看下它在 MR 中的執(zhí)行順序:

Map 階段:

執(zhí)行 from,進(jìn)行表的查找與加載;

執(zhí)行 where,注意:sql 語句中 left join 寫在 where 之前的,但是實(shí)際執(zhí)行先執(zhí)行 where 操作,因?yàn)?Hive 會對語句進(jìn)行優(yōu)化,如果符合謂詞下推規(guī)則,將進(jìn)行謂詞下推;

執(zhí)行 left join 操作,按照 key 進(jìn)行表的關(guān)聯(lián);

執(zhí)行輸出列的操作,注意: select 后面只有兩個字段(order_amount,userkey),此時 Hive 是否只輸出這兩個字段呢,當(dāng)然不是,因?yàn)?group by 的是 idno,如果只輸出 select 的兩個字段,后面 group by 將沒有辦法對 idno 進(jìn)行分組,所以此時輸出的字段有三個:idno,order_amount,userkey;

執(zhí)行 map 端的 group by,此時的分組方式采用的是哈希分組,按照 idno 分組,進(jìn)行order_amount 的 sum 操作和 userkey 的 count 操作,最后按照 idno 進(jìn)行排序(group by 默認(rèn)會附帶排序操作);

Reduce 階段:

執(zhí)行 reduce 端的 group by,此時的分組方式采用的是合并分組,對 map 端發(fā)來的數(shù)據(jù)按照 idno 進(jìn)行分組合并,同時進(jìn)行聚合操作 sum(order_amount)和 count(userkey);

執(zhí)行 select,此時輸出的就只有 select 的兩個字段:sum(order_amount) as sum_amount,count(userkey) as count_user;

執(zhí)行 having,此時才開始執(zhí)行 group by 后的 having 操作,對 count_user 進(jìn)行過濾,注意:因?yàn)樯弦徊捷敵龅闹挥?select 的兩個字段了,所以 having 的過濾字段只能是這兩個字段;

執(zhí)行 limit,限制輸出的行數(shù)為 10。

上面這個執(zhí)行順序到底對不對呢,我們可以通過 explain 執(zhí)行計劃來看下,內(nèi)容過多,我們分階段來看。

首先看下 sql 語句的執(zhí)行依賴:

我們看到 Stage-5 是根,也就是最先執(zhí)行 Stage-5,Stage-2 依賴 Stage-5,Stage-0 依賴 Stage-2。

首先執(zhí)行 Stage-5:

圖中標(biāo) ① 處是表掃描操作,注意先掃描的 b 表,也就是 left join 后面的表,然后進(jìn)行過濾操作(圖中標(biāo) ② 處),我們 sql 語句中是對 a 表進(jìn)行的過濾,但是 Hive 也會自動對 b 表進(jìn)行相同的過濾操作,這樣可以減少關(guān)聯(lián)的數(shù)據(jù)量。

接下來執(zhí)行 Stage-2:首先是 Map 端操作:

先掃描 a 表(圖中標(biāo) ① 處);接下來進(jìn)行過濾操作 idno > '112233'(圖中標(biāo) ② 處);然后進(jìn)行 left join,關(guān)聯(lián)的 key 是 idno(圖中標(biāo) ③ 處);執(zhí)行完關(guān)聯(lián)操作之后會進(jìn)行輸出操作,輸出的是三個字段,包括 select 的兩個字段加 group by 的一個字段(圖中標(biāo) ④ 處);然后進(jìn)行 group by 操作,分組方式是 hash(圖中標(biāo) ⑤ 處);然后進(jìn)行排序操作,按照 idno 進(jìn)行正向排序(圖中標(biāo) ⑥ 處)。

然后是 Reduce 端操作:

首先進(jìn)行 group by 操作,注意此時的分組方式是 mergepartial 合并分組(圖中標(biāo) ① 處);然后進(jìn)行 select 操作,此時輸出的字段只有兩個了,輸出的行數(shù)是 30304 行(圖中標(biāo) ② 處);接下來執(zhí)行 having 的過濾操作,過濾出 count_user>1 的字段,輸出的行數(shù)是 10101 行(圖中標(biāo) ③ 處);然后進(jìn)行 limit 限制輸出的行數(shù)(圖中標(biāo) ④ 處);圖中標(biāo) ⑤ 處表示是否對文件壓縮,false 不壓縮。

執(zhí)行計劃中的數(shù)據(jù)量只是預(yù)測的數(shù)據(jù)量,不是真實(shí)運(yùn)行的,所以數(shù)據(jù)可能不準(zhǔn)!

最后是 Stage-0 階段:

限制最終輸出的行數(shù)為 10 行。

總結(jié)

通過上面對 SQL 執(zhí)行計劃的分析,總結(jié)以下幾點(diǎn):

每個 stage 都是一個獨(dú)立的 MR,復(fù)雜的 hive sql 語句可以產(chǎn)生多個 stage,可以通過執(zhí)行計劃的描述,看看具體步驟是什么。

對于 group by 的 key,必須是表中的字段,對于 having 的 key,必須是 select 的字段。

order by 是在 select 后執(zhí)行的,所以 order by 的 key 必須是 select 的字段。

select 最好指明字段,select * 會增加很多不必要的消耗(CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬)。