【大数据基础-数据仓库-02】HIVE/SPARK SQL优化

JOIN优化

--代码片段1
select a.s_no 
from student_orc_partition  a
inner join student_orc_partition_only b
on a.s_no=b.s_no and a.part=b.part and a.part>=1 and a.part<=2
--代码片段2
select a.s_no 
from student_orc_partition  a
inner join student_orc_partition_only b
on a.s_no=b.s_no and a.part=b.part
where a.part>=1 and a.part<=2

上面两个代码块看似一样，但是执行起来会有很大的区别，我们使用 explain dependency 查看执行依赖

--代码片段1的explain dependency打印结果：
{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=0"},
{"partitionName":"default@student_orc_partition@part=1"},
{"partitionName":"default@student_orc_partition@part=2"},
{"partitionName":"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables":
[{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"},
{"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}
--代码片段2的explain dependency打印结果：
{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=1"},
{"partitionName" : "default@student_orc_partition@part=2"},
{"partitionName" :"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables":
[{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"},
{"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

​

通过上面的输出结果可以看到，其实上述的两个SQL并不等价，在内连接（inner join）中的连接条件中加入非等值的过滤条件后，并没有将内连接的左右两个表按照过滤条件进行过滤，内连接在执行时会多读取part=0的分区数据。

可以看到，对左外连接在连接条件中加入非等值过滤的条件，如果过滤条件是作用于右表（b表）有起到过滤的效果，则右表只要扫描两个分区即可，但是左表（a表）会进行全表扫描。如果过滤条件是针对左表，则完全没有起到过滤的作用，那么两个表将进行全表扫描。这时的情况就如同全外连接一样都需要对两个数据进行全表扫描。

如果要使用外连接并需要对左、右两个表进行条件过滤，最好的方式就是将过滤条件放到表的就近处，即如果已经知道表数据过滤筛选条件，那么在使用该表前，就用该过滤条件进行过滤，一些SQL内置优化器也会做上述的优化，例如下sql

select a.s_no 
from （
  select s_no,part
  from student_orc_partition
  --在子查询内部进行过滤
  where part>=1 and part<=2
）  a
left outer join student_orc_partition_only b
on a.s_no=b.s_no and a.part=b.part ;