Gunakan MAPJOIN HINT untuk mengoptimalkan kinerja join - MaxCompute

Tambahkan petunjuk MAPJOIN ke pernyataan SELECT untuk memaksa operasi join dieksekusi pada tahap map, melewati tahap shuffle dan reduce. Pendekatan ini mengurangi overhead transmisi data dan meningkatkan kinerja kueri saat melakukan join antara tabel besar dengan satu atau beberapa tabel kecil. Gunakan MAPJOIN jika pengoptimal tidak secara otomatis menerapkan join tahap map pada kueri Anda.

Cara kerja

JOIN standar di MaxCompute berjalan melalui tiga tahap: map, shuffle, dan reduce. Logika join yang sebenarnya dieksekusi pada tahap reduce, yang memerlukan pengacakan data antar node.

MAPJOIN mengubah proses ini: seluruh isi tabel kecil yang ditentukan dimuat ke dalam memori selama tahap map. Setiap mapper kemudian melakukan join secara lokal terhadap data yang ada di memori, sehingga menghilangkan tahap shuffle dan reduce sepenuhnya.

Petunjuk tersebut menentukan tabel kecil yang akan dimuat ke dalam memori. Untuk setiap mapper yang membaca baris dari tabel besar, tabel kecil dibaca sepenuhnya dari memori:

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;

Pada contoh ini, a (alias dari sale_detail_sj) adalah tabel kecil. Petunjuk tersebut menentukan a, sehingga a dimuat ke dalam memori pada setiap mapper.

Batasan

Memori dan jumlah tabel

Kendala	Batas	Catatan
Total memori untuk semua tabel kecil	512 MB	Diukur setelah data dimuat ke dalam memori dan didekompresi, bukan ukuran terkompresi yang disimpan di MaxCompute
Jumlah maksimum tabel kecil	128	Menentukan nilai lebih dari 128 akan menghasilkan kesalahan sintaksis.

Jenis JOIN yang didukung

Jenis JOIN	Didukung	Persyaratan
INNER JOIN	Ya	Baik tabel kiri maupun kanan dapat menjadi tabel besar
LEFT OUTER JOIN	Ya	Tabel kiri harus merupakan tabel besar
RIGHT OUTER JOIN	Ya	Tabel kanan harus merupakan tabel besar
FULL OUTER JOIN	Tidak	—

Catatan penggunaan

Tambahkan /*+ mapjoin(<table_name>) */ segera setelah SELECT. Perhatikan hal-hal berikut:

Gunakan alias, bukan nama tabel asli. Jika tabel kecil atau subkueri memiliki alias, gunakan alias tersebut di dalam petunjuk.
Subkueri didukung sebagai tabel kecil. Gunakan subkueri sebagai pengganti referensi tabel, dan rujuk alias-nya dalam petunjuk.
Pisahkan beberapa tabel kecil dengan koma: /*+ mapjoin(a,b,c) */
Join non-equi dan kondisi OR didukung. SQL MaxCompute standar tidak mengizinkan join non-equi atau logika OR dalam kondisi ON, tetapi MAPJOIN mendukungnya.
Produk Kartesius didukung menggunakan ON 1 = 1 (misalnya, SELECT /*+ mapjoin(a) */ a.id FROM shop a JOIN table_name b ON 1=1), tetapi hal ini dapat meningkatkan volume data keluaran secara signifikan.

Catatan

Jenis subkueri seperti SCALAR, IN, NOT IN, EXISTS, dan NOT EXISTS dapat dikonversi menjadi operasi JOIN saat eksekusi. Jika hasil subkueri memenuhi syarat sebagai tabel kecil, tambahkan petunjuk MAPJOIN pada pernyataan subkueri untuk secara eksplisit menerapkan algoritma join tahap map.

Data sampel

Contoh dalam topik ini menggunakan tabel sale_detail dan sale_detail_sj. Jalankan pernyataan berikut untuk membuat tabel dan memasukkan data sampel.

-- Buat tabel partisi bernama 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);

-- Tambahkan partisi.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');
ALTER TABLE sale_detail_sj ADD PARTITION (sale_date='2013', region='china');

-- Masukkan data sampel.
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);

Contoh

Lakukan join antara sale_detail_sj (tabel kecil, dengan alias a) dan sale_detail (tabel besar, dengan alias b) menggunakan kondisi non-equi. Kembalikan baris-baris di mana total_price dari a kurang dari total_price dari b, atau jumlah kedua harga tersebut kurang dari 500.

-- Izinkan pemindaian penuh pada tabel partisi.
SET odps.sql.allow.fullscan=true;

-- Gunakan MAPJOIN dengan kondisi join non-equi.
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;

Kueri tersebut menghasilkan output berikut:

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