1、distict去重效率比group by低？

之前大家在網(wǎng)上總能看到hive調(diào)優(yōu)中一定有這么一條，要避免使用distinct去重，代替法是group by。但是 是不是所有的情況下都是如此呢？看下面這個(gè)案例

select count(1) from(
    select s_age
    from student_tb_orc
    group by s_age
) b

這里為了從學(xué)生表中統(tǒng)計(jì)年齡的枚舉值個(gè)數(shù)，但是為什么不用下面的這種distinct呢？

select count(distinct s_age)
from student_tb_orc

我們一般都會(huì)想數(shù)據(jù)量大了第一種能夠避免reduce端的數(shù)據(jù)傾斜，但事實(shí)上，不論數(shù)據(jù)量大小，都是下面的簡(jiǎn)潔SQL效率更高。

「該作者跑的結(jié)果為47s 和 28s。」

這是為什么呢？

1. 因?yàn)槿ブ氐氖莝_age列，實(shí)際上業(yè)務(wù)含義表示年齡，枚舉值個(gè)數(shù)非常有限，在Map階段會(huì)對(duì)s_age去重，因此每個(gè)Map得到的s_age有限，最后到達(dá)Reduce階段的非常有限，根本不會(huì)達(dá)到數(shù)據(jù)傾斜的量。
2. 另外group by在不同版本間變動(dòng)比較大，有的版本會(huì)用構(gòu)建hashtable的形式去重，有的版本會(huì)通過(guò)排序的方式，排序最優(yōu)時(shí)間復(fù)雜度無(wú)法到O(1) 。另外上面寫法轉(zhuǎn)化為兩個(gè)任務(wù)，會(huì)消耗更多的磁盤網(wǎng)絡(luò)I/O資源。
3. 目前Hive 3種新增了count(distinct) 優(yōu)化，通過(guò)配置     「hive.optimize.countdistinct」，即使真的出現(xiàn)數(shù)據(jù)傾斜也可以自動(dòng)優(yōu)化，自動(dòng)改變SQL執(zhí)行的邏輯。

所以，「上面第一種SQL的寫法有點(diǎn)過(guò)度優(yōu)化」。讓我們繼續(xù)看一下他們的執(zhí)行流程圖：

第一種SQL執(zhí)行流程圖如下：第二種SQL的執(zhí)行流程圖如下：所以這2個(gè)SQL執(zhí)行流程的對(duì)比圖如下：

這兩個(gè)SQL執(zhí)行出來(lái)的時(shí)間差主要集中在數(shù)據(jù)傳輸和中間任務(wù)的創(chuàng)建下，就是上圖的虛線框部分，因此通過(guò)distinct關(guān)鍵字比子查詢的方式效率更高。

當(dāng)然如果這里「采用Spark 引擎，就直接省去了Map1落盤和Reduce再去讀取中間數(shù)據(jù)的時(shí)間」，二者的運(yùn)行時(shí)間差可能更短。但是從SQL同等復(fù)雜程度下，簡(jiǎn)潔更優(yōu)的角度來(lái)說(shuō)，還是distinct更優(yōu)。

「那么什么情況下第一種寫法的SQL會(huì)比第二種寫法的SQL效率更高呢？」

在有數(shù)據(jù)傾斜的情況下，第一種寫法的SQL方式更優(yōu)。

當(dāng)數(shù)據(jù)大到一定的量級(jí)時(shí)，第一種寫法的SQL有兩個(gè)作業(yè)，可以把處理邏輯分散到兩個(gè)階段中，即第一個(gè)階段先處理一部分?jǐn)?shù)據(jù)，縮小數(shù)據(jù)量，第二個(gè)階段在已經(jīng)縮小的數(shù)據(jù)集上繼續(xù)處理。

而第二種寫法的SQL，經(jīng)過(guò)Map階段處理的數(shù)據(jù)還非常多時(shí)，所有的數(shù)據(jù)卻都需要交給一個(gè)Reduce節(jié)點(diǎn)去處理，就好比千軍萬(wàn)馬過(guò)獨(dú)木橋一樣，不僅無(wú)法利用到分布式集群的優(yōu)勢(shì)，還要浪費(fèi)大量時(shí)間在等待，而這個(gè)等待的時(shí)間遠(yuǎn)比第一種寫法的SQL多個(gè)MapReduce所延長(zhǎng)的流程導(dǎo)致額外花費(fèi)的時(shí)間還多。

「但是，如前面所說(shuō)，在Hive 3.0中即使遇到數(shù)據(jù)傾斜，第二種寫法的SQL將hive.optimize.countdistinct設(shè)置為true，則整個(gè)寫法也能達(dá)到第一種寫法的SQL的效果?！?/strong>

我嘗試在自己的集群上跑同樣的SQL，用Spark 引擎，可能因?yàn)閿?shù)據(jù)量小的原因，相差不大，都是4s左右。

INSERT into table student_stat partition(tp)
select
 s_age,
 min(s_birth) stat,
 'min' tp
from student_tb_txt
group by s_age
union all
select
 s_age,
 max(s_birth) stat,
 'max' tp
from student_tb_txt
group by s_age;

from student_tb_txt
INSERT into table student_stat partition(tp)
select s_age,min(s_birth) stat,'min' tp
group by s_age
insert into table  student_stat partition(tp)
select s_age,max(s_birth) stat,'max’ tp
group by s_age;