AnalyticDB:Ubah LEFT JOIN menjadi RIGHT JOIN

Informasi latar belakang

AnalyticDB for MySQL secara default menggunakan hash join untuk menggabungkan tabel. Dalam hash join, tabel kanan digunakan untuk membangun tabel hash, yang membutuhkan banyak sumber daya. Berbeda dengan inner join, outer join (termasuk LEFT JOIN dan RIGHT JOIN) tidak dapat menukar posisi tabel kiri dan kanan tanpa mengubah semantik kueri. Oleh karena itu, jika tabel kanan berukuran besar, eksekusi kueri menjadi lambat dan mengonsumsi banyak memori. Dalam kasus ekstrem di mana tabel kanan sangat besar, kinerja klaster dapat terganggu atau muncul error Out of Memory Pool size pre cal selama eksekusi kueri. Dalam situasi ini, Anda dapat menerapkan metode optimasi yang dijelaskan dalam topik ini untuk mengurangi konsumsi sumber daya.

Skenario

Anda dapat mengubah LEFT JOIN menjadi RIGHT JOIN dengan memodifikasi pernyataan SQL atau menambahkan hint. Pada LEFT JOIN asli, tabel kiri menjadi tabel kanan untuk membangun tabel hash. Jika tabel kanan yang baru tetap berukuran besar, kinerja tetap terpengaruh. Oleh karena itu, optimasi ini disarankan ketika tabel kiri pada LEFT JOIN berukuran kecil dan tabel kanannya berukuran besar.

Klasifikasi suatu tabel sebagai kecil atau besar bersifat relatif dan bergantung pada faktor-faktor seperti kolom join dan sumber daya klaster. Dalam praktiknya, Anda dapat menggunakan EXPLAIN ANALYZE untuk melihat parameter rencana eksekusi. Untuk menentukan apakah akan menggunakan RIGHT JOIN, amati perubahan parameter seperti PeakMemory dan WallTime.

Penggunaan

Contoh

Pada contoh berikut, tabel nation berukuran kecil dengan 25 baris, sedangkan tabel customer berukuran besar dengan 15.000.000 baris. Gunakan EXPLAIN ANALYZE untuk melihat rencana eksekusi kueri yang menggunakan LEFT JOIN.

explain analyze
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

Fragment 2 [HASH]
    Output: 48 rows (432B), PeakMemory: 516MB, WallTime: 6,52us, Input: 15000025 rows (200,27MB); per task: avg.: 2500004,17 std.dev.: 2410891,74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 96 rows (864B), PeakMemory: 96B (0,00%), WallTime: 88,21ms (0,88%)
    │   count_2 := count(*)
    └─ LEFT Join[(`n_nationkey` = `c_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 30000000 rows (200,27MB), PeakMemory: 515MB (93,68%), WallTime: 4,34s (43,05%)
       │   Left (probe) Input avg.: 0,52 rows, Input std.dev.: 379,96%
       │   Right (build) Input avg.: 312500,00 rows, Input std.dev.: 380,00%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [n_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 25 rows (350B), PeakMemory: 64KB (0,01%), WallTime: 63,63us (0,00%)
       │      Input avg.: 0,52 rows, Input std.dev.: 379,96%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("c_nationkey")
          │   Outputs: [c_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 15000000 (57,22MB)}
          │   Output: 30000000 rows (400,54MB), PeakMemory: 10MB (1,84%), WallTime: 1,81s (17,93%)
          └─ RemoteSource[4]
                 Outputs: [c_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 15000000 rows (200,27MB), PeakMemory: 3MB (0,67%), WallTime: 191,32ms (1,90%)
                 Input avg.: 312500,00 rows, Input std.dev.: 380,00%

• Ubah LEFT JOIN menjadi RIGHT JOIN dengan memodifikasi pernyataan SQL:

  SELECT
    COUNT(*)
  FROM
    customer t2
    right JOIN nation t1 ON t1.n_nationkey = t2.c_nationkey

• Ubah LEFT JOIN menjadi RIGHT JOIN dengan menambahkan hint:

  • Untuk klaster versi V3.1.8 atau lebih baru, jalankan pernyataan berikut untuk mengaktifkan fitur ini:

    /*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
    SELECT
      COUNT(*)
    FROM
      nation t1
      left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

  • Untuk klaster versi sebelum V3.1.8, jalankan pernyataan berikut untuk mengaktifkan fitur ini:

    /*+LEFT_TO_RIGHT_ENABLED=true*/
    SELECT
      COUNT(*)
    FROM
      nation t1
      left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

Setelah menjalankan EXPLAIN ANALYZE pada salah satu kueri di atas, rencana eksekusi menunjukkan RIGHT Join alih-alih LEFT Join, yang menandakan bahwa hint tersebut berlaku. Setelah penyesuaian, nilai PeakMemory turun menjadi 889 KB (3,31%), dari sebelumnya 515 MB, sehingga tidak lagi menjadi hotspot kinerja.

Fragment 2 [HASH]
    Output: 96 rows (864B), PeakMemory: 12MB, WallTime: 4,27us, Input: 15000025 rows (200,27MB); per task: avg.: 2500004,17 std.dev.: 2410891,74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 192 rows (1,69kB), PeakMemory: 456B (0,00%), WallTime: 5,31ms (0,08%)
    │   count_2 := count(*)
    └─ RIGHT Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 15000025 rows (350B), PeakMemory: 889KB (3,31%), WallTime: 3,15s (48,66%)
       │   Left (probe) Input avg.: 312500,00 rows, Input std.dev.: 380,00%
       │   Right (build) Input avg.: 0,52 rows, Input std.dev.: 379,96%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [c_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 15000000 rows (200,27MB), PeakMemory: 3MB (15,07%), WallTime: 634,81ms (9,81%)
       │      Input avg.: 312500,00 rows, Input std.dev.: 380,00%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("n_nationkey")
          │   Outputs: [n_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 25 (100B)}
          │   Output: 50 rows (700B), PeakMemory: 461KB (1,71%), WallTime: 942,37us (0,01%)
          └─ RemoteSource[4]
                 Outputs: [n_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 25 rows (350B), PeakMemory: 64KB (0,24%), WallTime: 76,34us (0,00%)
                 Input avg.: 0,52 rows, Input std.dev.: 379,96%