大表关联小表
当一个大表和一个或多个小表做join时,可以使用mapjoin,性能比普通的join要快很多。
mapjoin的基本原理是:
在小数据量情况下,SQL会将用户指定的小表全部加载到执行join操作的程序的内存中,
从而加快join的执行速度。需要注意,使用mapjoin时:
left outer join的左表必须是大表;
right outer join的右表必须是大表;
inner join左表或右表均可以作为大表;
full outer join不能使用mapjoin;
mapjoin支持小表为子查询;
使用mapjoin时需要引用小表或是子查询时,需要引用别名;
在mapjoin中,可以使用不等值连接或者使用or连接多个条件;
目前ODPS在mapjoin中最多支持指定6张小表,否则报语法错误;
如果使用mapjoin,则所有小表占用的内存总和不得超过512MB;
多个表join时,最左边的两个表不能同时是mapjoin的表。
下面是一个简单的示例:
select /* + mapjoin(a) */
a.shop_name,
b.customer_id,
b.total_price
from shop a join sale_detail b
on a.shop_name = b.shop_name;
ODPS SQL不支持支持在普通join的on条件中使用不等值表达式、or 逻辑等复杂的join条件,但是在mapjoin中可以进行如上操作,例如:
select /*+ mapjoin(a) */
a.total_price,
b.total_price
from shop a join sale_detail b
on a.total_price < b.total_price or a.total_price + b.total_price < 500;