AnalyticDB：Ubah LEFT JOIN menjadi RIGHT JOIN

Informasi latar belakang

Secara default, AnalyticDB for MySQL menggunakan algoritma hash join untuk menggabungkan tabel. Algoritma ini menggunakan tabel kanan untuk membangun tabel hash, yang mengonsumsi sejumlah besar sumber daya. Berbeda dengan inner join, outer join seperti LEFT JOIN dan RIGHT JOIN tidak dapat menukar urutan tabel kiri dan kanan. Dalam skenario di mana tabel kanan berisi sejumlah besar data, eksekusi lambat dan konsumsi memori tinggi mungkin terjadi. Dalam kasus ekstrem, kinerja kluster terpengaruh atau pesan kesalahan Out of Memory Pool size pre cal dapat dikembalikan. Untuk mengurangi konsumsi sumber daya, Anda dapat menggunakan metode optimisasi yang disediakan dalam topik ini.

Skenario

Anda dapat mengubah LEFT JOIN menjadi RIGHT JOIN dengan memodifikasi pernyataan SQL atau menambahkan hint sebelum pernyataan tersebut. Dengan cara ini, tabel kiri asli diubah menjadi tabel kanan untuk membangun tabel hash. Jika tabel kanan baru berisi sejumlah besar data, kinerja akan terpengaruh. Oleh karena itu, kami sarankan Anda mengubah LEFT JOIN menjadi RIGHT JOIN jika tabel kiri lebih kecil dan tabel kanan lebih besar.

Perbandingan ukuran antara tabel terkait dengan kolom yang digabungkan dan sumber daya kluster. Anda dapat menjalankan pernyataan EXPLAIN ANALYZE untuk menanyakan parameter rencana eksekusi, serta menentukan apakah RIGHT JOIN dapat digunakan berdasarkan perubahan parameter seperti PeakMemory dan WallTime. Untuk informasi lebih lanjut, lihat Gunakan perintah EXPLAIN dan EXPLAIN ANALYZE untuk menganalisis rencana eksekusi.

Metode

Contoh

Pada contoh ini, nation adalah tabel kecil dengan 25 baris, sedangkan customer adalah tabel besar dengan 15.000.000 baris. Anda dapat menjalankan pernyataan EXPLAIN ANALYZE untuk menanyakan rencana eksekusi dari pernyataan SQL yang berisi 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 baris (432B), PeakMemory: 516MB, WallTime: 6,52us, Input: 15000025 baris (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 baris (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 baris (200,27MB), PeakMemory: 515MB (93,68%), WallTime: 4,34s (43,05%)
       │   Left (probe) Input avg.: 0,52 baris, Input std.dev.: 379,96%
       │   Right (build) Input avg.: 312500,00 baris, Input std.dev.: 380,00%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [n_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 25 baris (350B), PeakMemory: 64KB (0,01%), WallTime: 63,63us (0,00%)
       │      Input avg.: 0,52 baris, 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 baris (400,54MB), PeakMemory: 10MB (1,84%), WallTime: 1,81s (17,93%)
          └─ RemoteSource[4]
                 Outputs: [c_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 15000000 baris (200,27MB), PeakMemory: 3MB (0,67%), WallTime: 191,32ms (1,90%)
                 Input avg.: 312500,00 baris, Input std.dev.: 380,00%

• Modifikasi Pernyataan SQL untuk Mengubah LEFT JOIN Menjadi RIGHT JOIN

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

• Tambahkan Hint Sebelum Pernyataan SQL untuk Mengubah LEFT JOIN Menjadi RIGHT JOIN

  • Untuk kluster AnalyticDB for MySQL versi V3.1.8 atau lebih baru, jalankan pernyataan berikut:

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

  • Untuk kluster AnalyticDB for MySQL sebelum versi V3.1.8, jalankan pernyataan berikut:

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

Setelah menjalankan pernyataan EXPLAIN ANALYZE pada pernyataan SQL di atas, Anda dapat melihat bahwa LEFT JOIN diubah menjadi RIGHT JOIN dalam rencana eksekusi. Dalam hal ini, hint berlaku. Nilai parameter PeakMemory berkurang dari 515 MB menjadi 889 KB, menunjukkan bahwa RIGHT JOIN tidak mengonsumsi sejumlah besar sumber daya.

Fragment 2 [HASH]
    Output: 96 baris (864B), PeakMemory: 12MB, WallTime: 4,27us, Input: 15000025 baris (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 baris (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 baris (350B), PeakMemory: 889KB (3,31%), WallTime: 3,15s (48,66%)
       │   Left (probe) Input avg.: 312500,00 baris, Input std.dev.: 380,00%
       │   Right (build) Input avg.: 0,52 baris, Input std.dev.: 379,96%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [c_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 15000000 baris (200,27MB), PeakMemory: 3MB (15,07%), WallTime: 634,81ms (9,81%)
       │      Input avg.: 312500,00 baris, 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 baris (700B), PeakMemory: 461KB (1,71%), WallTime: 942,37us (0,01%)
          └─ RemoteSource[4]
                 Outputs: [n_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 25 baris (350B), PeakMemory: 64KB (0,24%), WallTime: 76,34us (0,00%)
                 Input avg.: 0,52 baris, Input std.dev.: 379,96%