HQL如何优化

谓词下推

• 不管是left join 还是是right
  join,执行计划都差不多,job数,mt和rt数都一样,执行时长都差不多可能自己已经优化了(谓词下推)

 select 
 count(1)
 from test1 t1
 inner join test2 t2
 on t1.uuid = t2.uuid and t2.date_id = '2022-10-23'
 where t1.date_id = '2022-10-23'

1. union优化
   尽量不要使用union(union去掉重复的记录)而是使用union all 在使用group by

   • count distinct优化
     在数据量多的时候,cd操作会需要一个rt来完成,这一个reduce需要处理的数据量太大,就会导致整个job很难完成, 一般count distinct 使用先group by在count的方式替换 不要使用count(distinct
     cloumn),使用子查询 select count(1) from (select id from tablename
     group by id) tmp; 虽然会多占用一个job,但是在数据很多的时候,利大于弊

   • 用in代替join

      如果需要根据一个表的字段来约束另一个表,尽量用in代替join,in比join块
     

   • Left semi join 优化in/exists
     Lsj是in/exists子查询一种更高效的实现方式,他的限制是join子句中右边的表只能在on子句中设置过滤提条件,在 where子句,select子句或者其他地方都不行.因为他是in(KeySet)关系,遇到又表重复记录,左表会跳过,而join则会
     一直遍历.这样就会导致右表有重复的情况下Lsj只产生一条,join会产生多条,也会导致Lsj性能更高,并且他只是传 递表的join
     key给map阶段,因此Lsj中最后select的结果只允许出现左表,因为右表只有join key 参与关联计算,而 left
     join on默认是整个关系模型都参与了计算

   • 列裁剪
     只读取需要查询的数据,当列或者数据量很大的时候,select * 不好用

   • 避免笛卡尔积

      join的时候不加on条件或者无效on条件就会产生笛卡尔积
     

   • 查看sql执行计划
     基本语法: EXPLAIN[EXTENDED | DEPENDENCY | AUTHORIZATION] query
     比如 explain select * from wedw_tmp.t_sum_over

   • 大表join小表优化 shuffle阶段代价非常昂贵,因此他需要排序和合并,减少shuffle和reduce可以提高性能. MapJoin用于一个很小的表和一个很大的表进行Join场景,具体多小由hive.mapjoin.smalltable.filesize来决定,
     该参数决定小表的总大小,默认为25M

     Hive 0.7之前,需要使用hint才会执行MapJoin,否则执行Common Join,但在0.7之后会默认转换,
     可以设置参数hive.auto.convert.join来控制,默认true

      假设a为大表,b为小表,在执行过程中自动转换为MJ,Mj简单来说就是在Map阶段将小表数据从HDFS上读取到内存中的哈希表,
     

     读完后将内存中的哈希表序列化为哈希文件,在下一个阶段中,当MR启动时,会将这个哈希表文件上传到HADOOP分布式缓存
     中,该缓存会把这些文件发送到每一个Mapper的本地磁盘.因此,所有Mapper都可以将次持久化的哈希表文件加载回内存,并且
     像之前一样Join

   其中MapJoin分为两个阶段:

   通过MR local task,将小表读入内存,生成哈希表文件上传到Distributed Cache中,这里会对哈希表文件进行压缩
   MR Job 在map阶段,每一个mapper从Distributed
   Cache读取一个哈希表文件到内存中,顺序扫描大表,在map阶段直接进行join,然后把数据给下一个MR任务

   • 大表join大表优化 空key过滤 有时候join超时是因为对应的key太多,而对应相同的key会发送到相同的reducer上导致内存不够.这时我们应该仔细分析这些异常的key,大部分情况下这些数据就是异常数据,我们需要在sql中进行过滤比如key对应的字段为空

   • 空key转换 有时虽然某个key为空对应的数据很多,但是相应的数据不是异常数据,必须要包含在Join的结果中,此时我们可以以a中key为空的字段赋一个随机的值,让数据随机均匀地分不到不同的reducer上,比如

insert overwrite table test
select 
n.*
from test1 n 
full join test2 o
on case when n,id is null then concat('hive',rand()) else n.id end = o.id;