MAPJION会把小表全部读入内存中,在map阶段直接拿另外一个表的数据和内存中表数据做匹配,由于在map是进行了join操作,省去了reduce运行的效率也会高很多
使用一个表测试,该表时5分钟表,数据很少,大概60多w。
测试日志里包含多个字段,其中有uid和uip。测试场景为给出2个uid,取uid共同的uip。
三个不同uid
select /*+ MAPJOIN(c) */
distinct c.ip from
(select /*+ MAPJOIN(a) */
a.ip from
(select ip from t where uid=uid1) a
join
(select ip from t where uid=uid2) b
on a.ip=b.ip
) c
join
(select ip from t where uid=uid3) d
on c.ip=d.ip
耗时 79.915 seconds 用4个mr
不适用mapjoin
select
distinct c.ip from
(select
a.ip from
(select ip from t where uid=uid1) a
join
(select ip from t where uid=uid2) b
on a.ip=b.ip
) c
join
(select ip from t where uid=uid3) d
on c.ip=d.ip
4个mr 耗时:90.932 seconds
结果一致.
效率提高了12%