利用分区表优化
场景：在业务环境中，以某个字段为筛选条件的需求增加
解决方法：建立以这个字段为分区的分区表，这样进行查询时只需要指定这个分区就不再需要进行全表扫描
利用分桶表优化
场景：需要频繁进行采样
解决方法：分桶表会使用hash算法将写入的数据随机的分入桶中，如果要进行采样的话，直接选择一个桶即可。
补充三种采样的方法 ：
-- 分桶抽象
select * from tmp_table tablesample(bucket 3 out of 32);
-- 3是抽取的分桶号，分桶号从0开始，32为总分桶数
-- 随机采样
select * from tmp_table order by rand() limit 100;
-- rand会随机打乱排序，然后抽取头100条数据 但是使用了order by效率过低
select * from tmp_table distribute by rand() sort by rand() limit 100;
--使用distribute by rand()分桶打乱来提高效率
-- 数据块抽样
select * from tmp_table tablesample(10 percent) -- 抽10%数据
select * from tmp_table tablesample(5 rows) -- 抽取行数
select * from tmp_table tablesample(5 M) -- 抽取数据块大小文件存储格式
在建表的时候，是可以通过stored as来指定表的存储格式，常见的存储格式有：
- TextFile
- 默认存储结构，行存储。
- 一行就是一条数据，以换行符分割，如果不进行压缩，磁盘开销较大，解析成本较高
- 建议使用可切分的压缩方式压缩
- SequenceFile
- HadoopAPI提供的二进制存储文件，使用方便，可分割，可压缩
- 一般选择block进行压缩
- RCFile
- 按行分块，数据块按列存储
- 比起提高查询速度，更多的是降低存储
- ORC
- 按行分块，数据块按列存储
- RCFile的升级版，在降低存储的情况下，查询性能大幅度提升
- 数据可压缩
- 基于列创建索引
- ParquetFile
- 列式存储
- 对于大型查询、指定列查询都是高效的
- 一般使用snappy压缩
- 支持impala查询引擎
数据进入数仓后，文件格式尽量选择后两者。
如何选择文件压缩格式
压缩格式 | 是否可拆分 | 是否自带 | 压缩率 | 速度 | 是否hadoop自带 |
gzip | 否 | 是 | 很高 | 非常快 | 是 |
lzo | 是 | 是 | 比较高 | 很快 | 否，要安装 |
snappy | 否 | 是 | 比较高 | 很快 | 否，要安装 |
bzip2 | 是 | 否 | 最高 | 慢 | 是 |
选择压缩格式主要从三个方面：
- 压缩比率
- 压缩解压速度
- 是否支持切片
列裁剪
场景：查询的表是一个有数十列的大宽表，但是只需要选择一个列的数据
解决方法：开启列裁剪，这样就可以只选择想要的列
# 列裁剪，只选择需要用到的列，默认开启
set hive.optimize.cp = true;谓词下推
谓词下推会将where谓词逻辑尽可能的提前执行，减少下游所需处理的数据量。
# 谓词下推，默认开启
set hive.optimize.ppd = true;举个例子：
select a.*,b.* from a join b on a.id=b.id where b.age >20;
-- 谓词下推后
select a.*,c.* from a join(select * from b where age > 20) c on a.id=c.id;分区裁剪
和列裁剪一样，就是只读取需要的分区。
# 分区裁剪，只选择需要用到的分区，默认开启
set hive.optimize.pruner = true;合并小文件
在执行MR程序时，一般一个文件都需要一个MapTask来处理，如果文件数过多，那么就会启动大量的MapTask任务，从而浪费大量资源。进行小文件的合并可以减少MapTask任务数量，从而加快执行速度。
# map端输入、合并文件之后按照block大小分割
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
# map端输入、不合并
set hive.input.format = org.apache.hadoop.hive.ql.io.HiveInputFormat;
# 是否合并map端输出，默认为true
set hive.merge.mapfiles=true;
# 是否合并reduce端输出，默认为false
set hive.merge.mapredfiles=true;
# 合并文件的大小，默认为256000000（256M）
set hive.merge.size.per.task = 256000000;
# 每个map最大分割大小
set mapred.max.split.size = 256000000;
# 一个节点上split最小值 比如节点上所有的小文件大小之和不足256M，那么就合并成一个文件
set mapred.min.split.size.per.node = 1;
# 一个机架上split的最小值 假如机架中有四个节点，每个节点只有一个40M的文件，那么就把机架上的文件合并起来组成一个文件
set mapred.min.split.size.per.rack = 1;Join优化
- 优先过滤后在进行join操作，最大限度的减少参与join的数据量
- 小表join大表，最好启动mapjoin，hive自动启用mapjoin，但是小表不能超过25M，可以设置
- join表的顺序从小到大
mapJoin
注意：这个措施，只要能用就用
mapJoin会将小表的数据分发到各个map的内存中，在map进程中进行join操作，从而提高速度。
# 自动将join转换为mapJoin
set hive.auto.convert.join = true;
# 小表大小
set hive.mapjoin.smalltable.filesize = 25000000;
# hive会基于表的size自动将普通join转换为mapjoin
set hive.auto.convert.join.noconditionaltask = true;
#多大的表可以自动触发内层LocalTask，默认大小10M
set hive.auto.convert.join.noconditionaltask.size = 10000000;Sort-Merge-Bucket(SMB) map Join
也是map join的一种优化方式，前提为参与join的表必须是分桶表与分桶排序表。
具体实现：
- 针对参与join的这两张表做相同的hash散列
- 桶中数据要排序
- 这两张表的分桶个数成倍数
- 开启smb的开关
# 当用户执行smb map join时，如果不能执行，则禁止查询
set hive.enforce.sortmergrbucktmapjoin = false;
# 当join的表符合smb条件，join是否会自动转换为smb map join
set hive.auto.convert.sortmerge.join = true;
# 当两个分桶表join时，如果join on的是分桶字段，小表的分桶数是大表的倍数时，可以启用mapjoin来提高效率
set hive.optimize.bucketmapjoin = false;
set hive.optimize.bucketmapjoin.sortedmerge = false;Join导致的数据倾斜
如果确认是因为Join造成的数据倾斜，可以进行如下配置：
# join的键对应的记录条数超过这个值就进行分拆，根据具体修改
set hive.skewjoin.key = 100000;
# 如果是join过程出现倾斜应该设置为true
set hive.optimize.skewjoin = false;开启后，如果job处理的记录条数超过限制，就会重启一个job处理多余的数据。
# 设置后可以控制第二个job的mapper数量。
set hive.skewjoin.map.tasks = 100000;CBO优化
在源码中遇见过这个优化，在hive1.1.0之后这个优化默认开启。
他会选择最优的join算法进行join。
set hive.cho.enable = true;
set hive.compute.query.using.stats = true;
set hive.stas.fetch.column.stats = true;
set hive.stats.fetch.partition.stats = true;怎样做笛卡尔积
什么叫笛卡尔积：两张表做没有连接条件的连接
hive设置为严格模式时，不允许使用笛卡尔积，这说明hive对笛卡尔积的支持本身是较弱的。
原因是因为找不到Join key，做笛卡尔积时只有一个reduce，造成了很大的压力与延迟。
hive做笛卡尔积的精髓在于复制，小表复制了N倍，大表的数据就被随机分为了N份，就会产生N个reduce，就不会出现数据倾斜。
group by
order by
order by只能是在一个reduce进程中进行，所以如果对一个大数据集进行order by，会导致一个reduce进程中处理的数据相当大，造成查询执行缓慢。
1.如果在操作结果上进行order by，不要在中间的大数据集上进行排序，如果结果数据较少，可以在结果排序
2.如果是取排序后的前N条数据，那么可以使用distribute by和sort by在各个reduce上进行排序后取前N条，然后汇总后再取前N条。这样避免了数据全排序，所以执行效率会有很大提高。现在有个需求：
求全年级分数最高的三个人
-- 使用order by
select * from student order by mark desc limit 3;
-- 使用distribute by+sort by
set mapreduce.job.reduces = 3;
create table student_orderby_result as
select
*
from
student
distribute by (
case
when
mark>80
then
0
when
mark<60
then
2
else
1
end)
sort by (mark desc);第二种方式最主要的问题是如何均匀分桶，也就是如何保证数据的均匀分布。
建议使用采样的方式先估计一下数据的分布。
count distinct优化
当要对某一列去重时，如果数据量很大，count(distinct)就会非常慢，原因也是因为reduce数量少。
第一种解决方式是使用group by优化。
-- 原句
select count(distinct age) from student;
-- group by 优化 单个字段推荐
select count(1) from ( select age from student group by age)tmp;
-- 子句优化
select count(1) from (select distinct id from student) tmp;in/exists语句
in/exists语句应该全部换成left semi join 语句
vectorization技术
在计算scan/filter/aggregation的时候，vectorization技术以设置批处理的增量大小为1024行，单次增加处理量来提高效率。
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;多重模式
如果多条SQL对某种表进行扫描，同时做不同的逻辑，那么就可以使用多重扫描，对表进行一次扫描做不同逻辑。
from student
select .....
insert ....
...但是呢，也有些限制：
- 单个SQL最多有128条语句
- 对于分区表，一个分区不能出现两次（读写锁）
- 对于同一张表的不同分区，不能同时有insert overwrite和insert into操作
例子：日志表与用户表做链接
select * from log a left outer join users b on a.user_id = b.user_id;users表中有600w+记录，肯定不能使用mapjoin进行分发，而join又会遇到数据倾斜的问题。
改进方案
-- **sql3_start** 获取活跃用户的日志信息
select
*
from
log a
left outer join(
-- **sql2_start** 获取活跃用户的用户信息
select
d.*
from
-- **sql1_start**获取有活动的用户id信息
(select distinct user_id from log) c
-- **sql1_end**
join
user d
on
c.user_id =d.user_id
) x
-- **sql2_end**
on
a.user_id = x.user_id
;
-- **sql3_end**
