Hive面試，如何優(yōu)化查詢效率！

1，開啟FetchTask

一個簡單的查詢語句，是指一個沒有函數(shù)、排序等功能的語句，當開啟一個Fetch Task功能，就執(zhí)行一個簡單的查詢語句不會生成MapRreduce作業(yè)，而是直接使用FetchTask，從hdfs文件系統(tǒng)中進行查詢輸出數(shù)據(jù)，從而提高效率。

設(shè)置的方式：

Hive.fetch.task.conversion?默認為minimal
?
修改配置文件hive-site.xml

??hive.fetch.task.conversion
??more
??
????Some?select?queries?can?be?converted?to?single?FETCH?task?
????minimizing?latency.Currently?the?query?should?be?single?
????sourced?not?having?any?subquery?and?should?not?have
????any?aggregations?or?distincts?(which?incurrs?RS),?
????lateral?views?and?joins.
????1.?minimal?:?SELECT?STAR,?FILTER?on?partition?columns,?LIMIT?only
????2.?more????:?SELECT,?FILTER,?LIMIT?only?(+TABLESAMPLE,?virtual?columns)
??
?
?
?
或者當前session修改
hive>?set?hive.fetch.task.conversion=more;
執(zhí)行SELECT?id,?money?FROM?m?limit?10;?不走mr

2，合并中間表

在實際中，我們會發(fā)現(xiàn)，有些業(yè)務(wù)處理，會有共同數(shù)據(jù)集用戶表、訂單表、商品表，三個表需要進行join的操作，join 會產(chǎn)生一個結(jié)果集，會有很多的業(yè)務(wù)是針對此jion結(jié)果集進行分析。

優(yōu)化：將眾多的業(yè)務(wù)中相同的中間結(jié)果集，抽取到一個Hive中的表中去。

3，合理使用分區(qū)表

外部表、分區(qū)表，結(jié)合使用，采用多級分區(qū)。數(shù)據(jù)采用存儲格式（textfile、orcfile、parquet）或者數(shù)據(jù)壓縮(snappy)。

明細數(shù)據(jù)我們一般采用按天分區(qū)，對于特別大的表，可以采用子分區(qū)，每個分區(qū)其實對應(yīng)到HDFS上就是一個目錄。數(shù)據(jù)存儲方式我們可以采用parquet列式存儲，同時具有很好的壓縮性能；同時可以減少大量的表掃描和反序列化的時間。在OLAP查詢場景下，我們選擇需要的列信息進行查詢，而不是直接select * 查詢所有字段。

4，jvm重用

JVM重用是hadoop調(diào)優(yōu)參數(shù)的內(nèi)容，對hive的性能具有非常大的影響，特別是對于很難避免小文件的場景或者task特別多的場景，這類場景大多數(shù)執(zhí)行時間都很短。hadoop默認配置是使用派生JVM來執(zhí)行map和reduce任務(wù)的，這是jvm的啟動過程可能會造成相當大的開銷，尤其是執(zhí)行的job包含有成千上萬個task任務(wù)的情況。JVM重用可以使得JVM實例在同一個JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中進行設(shè)置

mapred.job.reuse.jvm.num.tasks? 1

也可在hive的執(zhí)行設(shè)置：

set??mapred.job.reuse.jvm.num.tasks = 10;?

JVM的一個缺點是，開啟JVM重用將會一直占用使用到的task插槽，以便進行重用，直到任務(wù)完成后才能釋放。如果某個“不平衡“的job中有幾個reduce task 執(zhí)行的時間要比其他reduce task消耗的時間多得多的話，那么保留的插槽就會一直空閑著卻無法被其他的job使用，直到所有的task都結(jié)束了才會釋放。

5，speculative execution（推測執(zhí)行）

mapred.map.tasks.speculative.execution=true
mapred.reduce.tasks.speculative.execution=true

7，合理設(shè)置reduce個數(shù)

reduce個數(shù)

參數(shù)1：

hive.exec.reducers.bytes.per.reducer=256000000?//每個reduce任務(wù)處理的數(shù)據(jù)量

參數(shù)2：

hive.exec.reducers.max=1009?//每個任務(wù)最大的reduce數(shù)目

計算公式：reducer個數(shù)=min(參數(shù)2,總輸入數(shù)據(jù)量/參數(shù)1)

set mapred.reduce.tasks =N：

每個任務(wù)默認的reduce數(shù)目。典型為0.99* reduce槽數(shù)，hive默認為-1，即自動確定reduce數(shù)目。

reduce個數(shù)并不是越多越好

同map一樣，啟動和初始化reduce也會消耗時間和資源；另外，有多少個reduce，就會有多少個輸出文件，如果生成了很多個小文件，那么如果這些小文件作為下一個任務(wù)的輸入，則也會出現(xiàn)小文件過多的問題。小文件過多會非常影響查詢效率，文件越多造成的IO就越多，同時還會增加元數(shù)據(jù)（namenode）的壓力。在生產(chǎn)環(huán)境中，一定要避免小文件問題，如果核查發(fā)現(xiàn)，及時合并文件！！

8，開啟并行執(zhí)行

并行執(zhí)行，意思是同步執(zhí)行hive的多個階段，hive在執(zhí)行過程，將一個查詢轉(zhuǎn)化成一個或者多個階段。某個特定的job可能包含眾多的階段，而這些階段可能并非完全相互依賴的，也就是說可以并行執(zhí)行的，這樣可能使得整個job的執(zhí)行時間縮短

hive.exec.parallel.thread.number????8//job并行執(zhí)行的數(shù)目，一個SQL語句可能有很多mapreduce任務(wù)，限制
hive.exec.parallel??false

hive執(zhí)行開啟：

set?hive.exec.parallel=true

9，優(yōu)化sql

• where條件優(yōu)化

優(yōu)化前（關(guān)系數(shù)據(jù)庫不用考慮會自動優(yōu)化）：

select?m.cid,u.id?from?order?m?join?customer?u?on(?m.cid?=u.id?)where?m.dt='20180808';

優(yōu)化后(where條件在map端執(zhí)行而不是在reduce端執(zhí)行）：

select?m.cid,u.id?from?（select?*?from?order?where?dt='20180818'）?m?join?customer?u?on(?m.cid?=u.id);

• union優(yōu)化

盡量不要使用union （union 去掉重復(fù)的記錄）而是使用 union all 然后在用group by 去重

• count distinct優(yōu)化

不要使用count (distinct cloumn) ,使用子查詢。

select?count(1)?from?(select?id?from?tablename?group?by?id)?tmp;

• 用in 來代替join

如果需要根據(jù)一個表的字段來約束另為一個表，盡量用in來代替join 。

select?id,name?from?tb1??a?join?tb2?b?on(a.id?=?b.id);
??
select?id,name?from?tb1?where?id?in(select?id?from?tb2);?

in 要比join 快

• 消滅子查詢內(nèi)的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以減少job的數(shù)量。

• join 優(yōu)化：

Common/shuffle/Reduce JOIN：連接發(fā)生的階段，發(fā)生在reduce 階段，適用于大表連接大表(默認的方式)

Map join ：連接發(fā)生在map階段，適用于小表連接大表 大表的數(shù)據(jù)從文件中讀取；小表的數(shù)據(jù)存放在內(nèi)存中（hive中已經(jīng)自動進行了優(yōu)化，自動判斷小表，然后進行緩存）。

set?hive.auto.convert.join=true;??

SMB join：Sort -Merge -Bucket Join ?對大表連接大表的優(yōu)化，用桶表的概念來進行優(yōu)化。在一個桶內(nèi)發(fā)送生笛卡爾積連接（需要是兩個桶表進行join）

set?hive.auto.convert.sortmerge.join=true;??
set?hive.optimize.bucketmapjoin?=?true;??
set?hive.optimize.bucketmapjoin.sortedmerge?=?true;??
set?hive.auto.convert.sortmerge.join.noconditionaltask=true;?