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        |
+-----------+-------------+--------------+