优化查询的利器:使用Left Semi Join 和 Left Anti Join 提高Hive查询性能

left join 和 left semi join:

Table A:
id  name
1   a      
2   b
3   c

Table B:    
id   age
1    10      
2    20

首先使用 left join :

select * from a  
left join b
on a.id = b.id;

结果:

1   a  1  10
2   b  2  20
3   c  null null

可以看到 left join 返回:

• 左表(A)所有记录
• 右表(B)有匹配记录则返回,无匹配记录使用null填充

再使用left semi join :

select * from a  
left semi join b
on a.id = b.id;

结果:

1   a   
2   b
3   c

left semi join 只返回:

• 左表(A)所有的记录
• 不返回右表(B)的任何记录

所以通过验证表明:

• left join 返回左右表所有记录
• left semi join 只返回左表记录

left semi join 只返回左表记录，那何必使用它呢?

left semi join 有两个具体的用途:

1. 过滤右表记录:

通过 ON 条件只匹配右表中的部分记录,从而完成对右表的过滤。

这比 directly 选 left table 快,因为不需要全表扫描。

2. 子查询优化:

可以替代使用 right table 在 where 中国 in/not in 的子查询。

这可以有效提高性能。

举个例子:

-- 使用子查询 
SELECT * FROM left_table
WHERE id IN (SELECT id FROM right_table WHERE condition)

-- 等价于使用left semi join
SELECT * 
FROM left_table 
LEFT SEMI JOIN right_table  
ON left_table.id = right_table.id
WHERE right_table.condition

二者效果相同,但 left semi join 的性能更高。

所以总的来说,left semi join 有两个主要用途:

1. 过滤右表记录
2. 替代性能较差的子查询

它看起来多余,但实际上可以提高查询效率。

left anti join

假设我们有以下两个表:

table1:  
id    name
1      a      
2      b     
3      c
4      d

table2:    
id   age  
1    20
2    30

现在我们要查询table1中不在table2中的记录,可以使用left anti join:

select * from table1
left anti join table2
on table1.id = table2.id

执行结果为:

3      c   
4      d

只返回了table1中的id=3 和 id=4的记录。

原因是:

• left anti join会返回左表(table1)所有记录
• 但是排除与 ON条件匹配的右表(table2)的记录

也就是说只返回左表中不在右表中的记录。

而使用普通的left join会得到:

1      a    1    20
2      b    2    30
3      c         
4      d

返回左表所有记录,与右表匹配的用右表值,否则用null填充。

所以通过对比,我们可以看出:

• left anti join只返回左表不在右表中的记录
• left join返回左表所有记录,带上右表匹配与否的内容

总的来说:

• left semi join 会返回左表所有记录,并包含匹配的右表记录
• left anti join 会返回左表所有记录,但排除匹配的右表记录