hive sql 调优主要分为三块：数据结构调优;sql 语句调优;hive 参数调优
1.数据结构调优：设计合理的表和字段，避免较多的jOin,数据倾斜(空字段，热点数据等)；
2.sql 语句调优： 优化原则：尽早尽量过滤数据，减少每个阶段的数据量;减少job数;解决数据倾斜问题
2.1 列裁剪 select a,b from table 代替 select * from table；
2.2 分区裁剪：指定分区查询
2.3 如果join的key相同，不管有多少表，都会合并为一个MapReduce任务 select a.val,b.val,c.val from a JOIN b ON (a.key = b.key1) JOIN c ON (c.key2 = b.key1) ----一个job select a.val,b.val,c.val from a JOIN b ON (a.key = b.key1) JOIN c ON (c.key2 = b.key2) ----两个job
2.4 善用multi-insert with tmp_a as ( select name from tmp_test3 ) from tmp_a insert overwrite table tmp_test1 select name where name = 'test123' insert overwrite table tmp_test2 select name where name = 'test123'
2.5 善用union all 不同表的union all相当于multi inputs，同一表的union all相当于map一次输出多条; 不同表太多的union all，不推荐使用。可以写在中间表的不同分区里，然后再进行union all, union all 优化写法： select * from( select key,value from a group by key,value union all select key,value from b group by key,value ) ---- 三个job
select key,value from( select key,value from a union all select key,value from b ) as tmp group by tmp.key,tmp.value ---- 一个job，用于小表
2.6 join前过滤掉不需要的数据
2.7 避免笛卡尔积：关联的时候一定要写关联条件
2.8 把重复关联键少的表放在join前面做关联可以提高join的效率 在编写带有join的代码语句时，应该将条目少的表/子查询放在join操作符的前面;Reduce阶段，位于join操作符左边的表会先被加载到内存，载入条目较少的表可以有效的防止内存溢出(OOM)
2.9 在map阶段进行join join阶段有两种，一种是在map阶段进行的，一种是在reduce阶段进行的。当小表和大表进行join时，尽量采用mapjoin，即在map端完成，尽早结合数据，使reduce端接收数据量减少。同时可以避免小表与大表join产生的数据倾斜。如果一个表特别小，推荐用mapjoin；如果不是，我们一般用reduce join. MAPJOIN写法（启用Map join时，大表left join小表,加载从右向左）： select /*+ MAPJOIN(b) */ a.value,b.value from a join b on a.key=b.key
2.10 使用动态分区 set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;
2.11 LEFT SEMI JOIN是IN和EXISTS的一种高效实现,LEFT SEMI JOIN产生的数据不会重复 select a.key,a.value from a left semi join b on a.key=b.key
2.12 尽量避免使用distinct 尽量避免使用distinct进行重排，特别是大表，容易产生数据倾斜(key一样在一个reduce处理)。使用group by替代： select distinct key from a select key from a group by key
2.13 排序优化 只有order by产生的结果是全局有序的，可以根据实际场景进行选择排序； order by实现全局排序，一个reduce实现，由于不能并发执行，所以效率低； sort by实现部分有序，单个reduce的输出结果是有序的，效率高，通常与distribute by一起使用(distribute by 关键词可以指定map到reduce的key分发)； cluster by col1等价于distribute by col1 sort by col1，但不能指定排序规则
2.14 使用explain dependency查看sql实际扫描多少分区
3.参数调优
3.1 并行执行job 一个hql可能会转化为多个MR job来执行，如果有些job间没有依赖关系，可以让多个job同时运行，提升执行效率，但如果集群资源较少，不建议开启，否则会导致多个job相互抢占资源，降低整体运行效率 set hive.exec.parallel=true; set hive.exec.parallel.thread.number=12;
3.2 控制Mapper读入文件块大小
map执行前先对小文件进行合并： set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; 每个map读入数据块的最大值，这个值决定了合并后文件的数量： set mapred.max.split.size=256000000; 一个节点上文件块进行split切分的最小值，会对不同节点数据进行传输合并： set mapred.min.split.size.per.node=128000000; 一个交换机上文件块进行split切分的最大值，会对不同交换机上的数据文件进行合并: set mapred.min.split.size.per.rack=128000000;
3.3 设置Map和Reduce阶段结束时进行文件合并 设置map端合并，在map only结束时进行文件合并: set hive.merge.mapfiles=true; 设置reduce端合并，在MapReduce任务结束时合并小文件: set hive.merge.mapredfiles=true; 设置合并文件的大小： set hive.merge.size.per.task=256000000; 当输出文件的平均大小小于该值时，启动一个MR任务进行文件合并： set hive.merge.smallfiles.avgsize=16000000;
3.4 矢量查询 默认是单行数据处理，矢量查询可以一次性批量执行1024行数据 set hive.vectorized.execution.enabled=true; set hive.vectorized.execution.reduce.enabled=true;
3.5 CBO(基于成本优化) 开启此功能后会自动优化sql中多个join的顺序，并根据算法优化每个查询的执行逻辑和物理执行计划，确定join顺序和任务并行度等： set hive.cbo.enable=true; set hive.compute.query.using.stats=true; set hive.stats.fetch.column.stats=true; set hive.stats.fetch.partition.stats=true;
3.6 Map端聚合 默认情况下，在MR过程中，map阶段的相同key会分发到相同reduce，如果一个key数据量较大就很可能产生数据倾斜，所以在reduce端进行聚合操作前可以先在map端先进行一次聚合
设置在map端进行聚合操作，默认为true(提升效率的同时也会占用较多内存)： set hive.map.aggr=true;
在map端聚合的数据条数： set hive.groupby.mapaggr.checkinterval=100000;
3.7 自动Map join 开启自动选择mapjoin，默认开启 set hive.auto.convert.join=true; 设置小表的大小，默认25m以下为小表 set hive.mapjoin.smalltable.filesize=25000000;
3.8 负载均衡 当产生数据倾斜时启动负载均衡，默认为false set hive.groupby.skewindata=true;
3.9 使用索引 开启自动索引 set hive.optimize.index.filter=true; 使用聚合索引优化group by操作： set hive.optimize.index.groupby=true;
create index idx_table on table table(aa) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild IN TABLE tablen_index;
3.10 简单查询不走MR set hive.fetch.task.conversion=more;
3.11 控制Mapper数 切片大小计算公式： splitSize=Math.max(minSize,Math.min(maxSize,blockSize)) 其中： minSize可由mapreduce.input.fileinputformat.split.minsize设定，默认值为1 maxSize可由mapreduce.input.fileinputformat.split.maxsize设定，默认值为Long.MAXValue 所以默认情况下切片大小为blockSize
