MAPJOIN HINT - 云原生大数据计算服务 MaxCompute

当您对一个大表和一个或多个小表执行join操作时，可以在select语句中显式指定mapjoin Hint提示以提升查询性能。本文为您介绍如何通过mapjoin hint连接表。

功能介绍

整个JOIN过程包含Map、Shuffle和Reduce三个阶段。通常情况下，join操作在Reduce阶段执行表连接。

mapjoin在Map阶段执行表连接，而非等到Reduce阶段才执行表连接，可以缩短大量数据传输时间，提升系统资源利用率，从而起到优化作业的作用。

在对大表和一个或多个小表执行join操作时，mapjoin会将您指定的小表全部加载到执行join操作的程序的内存中，在Map阶段完成表连接从而加快join的执行速度。

此外，MaxCompute SQL不支持在普通join的on条件中使用不等值表达式、or等逻辑复杂的join条件，但是在mapjoin中可以进行上述操作。

使用限制

mapjoin操作的使用限制如下：

mapjoin在Map阶段会将指定表的数据全部加载在内存中，因此指定的表仅能为小表，且表被加载到内存后占用的总内存不得超过512 MB。由于MaxCompute是压缩存储，因此小表在被加载到内存后，数据大小会急剧膨胀。此处的512 MB是指加载到内存后的空间大小。
mapjoin中join操作的限制如下：
- left outer join的左表必须是大表。
- right outer join的右表必须是大表。
- 不支持full outer join。
- inner join的左表或右表均可以是大表。
mapjoin最多支持指定128张小表，否则报语法错误。

使用方法

您需要在select语句中使用Hint提示/*+ mapjoin(<table_name>) */才会执行mapjoin。需要注意的是：

引用小表或子查询时，需要引用别名。
mapjoin支持小表为子查询。
在mapjoin中，可以使用不等值连接或or连接多个条件。您可以通过不写on语句而通过mapjoin on 1 = 1的形式，实现笛卡尔乘积的计算。例如select /*+ mapjoin(a) */ a.id from shop a join table_name b on 1=1;，但此操作可能带来数据量膨胀问题。
mapjoin中多个小表用英文逗号（,）分隔，例如/*+ mapjoin(a,b,c)*/。

说明

部分子查询（例如SCALAR、IN、NOT IN、EXISTS或NOT EXISTS）在执行过程中会被转换成JOIN进行计算，MAPJOIN是一种高效的JOIN算法，若您确定SUBQUERY的计算结果为小表，可以在子查询SUBQUERY语句中使用HINT来显式地指定使用MAPJOIN算法。详情请参见SUBQUERY_MAPJOIN HINT。

示例数据

为便于理解，本文为您提供源数据，基于源数据提供相关示例。创建表sale_detail和sale_detail_sj，并添加数据，命令示例如下：

--创建一张分区表sale_detail。
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

create table if not exists sale_detail_sj
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

--向源表增加分区。
alter table sale_detail add partition (sale_date='2013', region='china');
alter table sale_detail_sj add partition (sale_date='2013', region='china');

--向源表追加数据。
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
insert into sale_detail_sj partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2),('s2','c2',100.2);

使用示例

对表sale_detail和sale_detail_sj执行join操作，满足sale_detail_sj中的total_price小于sale_detail中的total_price，或sale_detail_sj中的total_price与sale_detail中的total_price之和小于500的条件，命令示例如下：

--允许分区表的全表扫描
SET odps.sql.allow.fullscan=true;

-- 使用mapjoin查询
select /*+ mapjoin(a) */
        a.shop_name,
        a.total_price,
        b.total_price
from sale_detail_sj a join sale_detail b
on a.total_price < b.total_price or a.total_price + b.total_price < 500;

返回结果如下：

+-----------+-------------+--------------+
| shop_name | total_price | total_price2 |
+-----------+-------------+--------------+
| s1        | 100.1       | 100.1        |
| s2        | 100.2       | 100.1        |
| s5        | 100.2       | 100.1        |
| s2        | 100.2       | 100.1        |
| s1        | 100.1       | 100.2        |
| s2        | 100.2       | 100.2        |
| s5        | 100.2       | 100.2        |
| s2        | 100.2       | 100.2        |
| s1        | 100.1       | 100.3        |
| s2        | 100.2       | 100.3        |
| s5        | 100.2       | 100.3        |
| s2        | 100.2       | 100.3        |
+-----------+-------------+--------------+