Hive SQL如何调优

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

之前大家在网上总能看到hive调优中一定有这么一条，要避免使用distinct去重，代替法是group by。但是 是不是所有的情况下都是如此呢？看下面这个案例

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

这里为了从学生表中统计年龄的枚举值个数，但是为什么不用下面的这种distinct呢？

select count(distinct s_age)
from student_tb_orc

我们一般都会想数据量大了第一种能够避免reduce端的数据倾斜，但事实上，不论数据量大小，都是下面的简洁SQL效率更高。

「该作者跑的结果为47s 和 28s。」

这是为什么呢？

1. 因为去重的是s_age列，实际上业务含义表示年龄，枚举值个数非常有限，在Map阶段会对s_age去重，因此每个Map得到的s_age有限，最后到达Reduce阶段的非常有限，根本不会达到数据倾斜的量。
2. 另外group by在不同版本间变动比较大，有的版本会用构建hashtable的形式去重，有的版本会通过排序的方式，排序最优时间复杂度无法到O(1) 。另外上面写法转化为两个任务，会消耗更多的磁盘网络I/O资源。
3. 目前Hive 3种新增了count(distinct) 优化，通过配置
       「hive.optimize.countdistinct」，即使真的出现数据倾斜也可以自动优化，自动改变SQL执行的逻辑。

所以，「上面第一种SQL的写法有点过度优化」。让我们继续看一下他们的执行流程图：

第一种SQL执行流程图如下：第二种SQL的执行流程图如下：所以这2个SQL执行流程的对比图如下：

这两个SQL执行出来的时间差主要集中在数据传输和中间任务的创建下，就是上图的虚线框部分，因此通过distinct关键字比子查询的方式效率更高。

当然如果这里「采用Spark 引擎，就直接省去了Map1落盘和Reduce再去读取中间数据的时间」，二者的运行时间差可能更短。但是从SQL同等复杂程度下，简洁更优的角度来说，还是distinct更优。

「那么什么情况下第一种写法的SQL会比第二种写法的SQL效率更高呢？」

在有数据倾斜的情况下，第一种写法的SQL方式更优。

当数据大到一定的量级时，第一种写法的SQL有两个作业，可以把处理逻辑分散到两个阶段中，即第一个阶段先处理一部分数据，缩小数据量，第二个阶段在已经缩小的数据集上继续处理。

而第二种写法的SQL，经过Map阶段处理的数据还非常多时，所有的数据却都需要交给一个Reduce节点去处理，就好比千军万马过独木桥一样，不仅无法利用到分布式集群的优势，还要浪费大量时间在等待，而这个等待的时间远比第一种写法的SQL多个MapReduce所延长的流程导致额外花费的时间还多。

「但是，如前面所说，在Hive 3.0中即使遇到数据倾斜，第二种写法的SQL将hive.optimize.countdistinct设置为true，则整个写法也能达到第一种写法的SQL的效果。」

我尝试在自己的集群上跑同样的SQL，用Spark 引擎，可能因为数据量小的原因，相差不大，都是4s左右。

2、改写SQL实现union 的优化

需求：从学生表中找到每个年龄段最晚出生和最早出生的人的生日日期，写入一个表中；

于是SQL如下：

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;

但是这个SQL其实是5个job对应了4个MR任务，效率是比较低的。那怎么优化呢？那能不能只读一次表，就能都计算出最小值和最大值，然后依次写入最后的结果表，不需要中间并集。看如下SQL

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;

「这种也叫做multi-table-insert语法，多路输出」在如上的SQL执行时，其实也启动了1个Job ，所以效率的提升还是非常显著的。