PolarDB:Pendorongan kondisi join

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

• Kluster PolarDB for MySQL 8.0 yang menjalankan revisi versi 8.0.2.2.10 atau lebih baru

Cara kerja

Pada MySQL standar, tabel turunan (juga disebut tampilan inline) yang tidak dapat digabungkan ke dalam kueri luar—misalnya, yang menggunakan GROUP BY, fungsi agregat, atau fungsi jendela—harus dimaterialisasi sepenuhnya sebelum penggabungan luar dijalankan. Ketika tabel turunan berisi data dalam jumlah besar, materialisasi penuh menjadi lambat.

Dengan penurunan kondisi penggabungan, pengoptimal kueri PolarDB mendeteksi saat kondisi penggabungan luar mereferensikan kolom di dalam tabel turunan dan menulis ulang rencana eksekusi agar menerapkan kondisi tersebut lebih awal, di dalam tabel turunan. Tabel turunan kemudian dapat menggunakan indeks untuk memfilter baris sebelum materialisasi, sehingga mengurangi volume data yang diproses oleh kueri luar.

Pengoptimal menggunakan perhitungan biaya untuk menentukan apakah penurunan suatu kondisi menghasilkan rencana yang lebih efisien. Penurunan hanya diterapkan jika pemfilteran berbasis indeks mengeliminasi sebagian besar baris.

Kapan menggunakan penurunan kondisi penggabungan

Penurunan kondisi penggabungan paling efektif ketika kedua kondisi berikut terpenuhi:

• Penggabungan nested-loop dengan kolom dalam yang terindeks: Kueri luar dan tabel turunan digabungkan secara nested-loop, dan kolom penggabungan di dalam tabel turunan memiliki indeks. Penurunan kondisi penggabungan memungkinkan tabel turunan menggunakan indeks tersebut untuk hanya mematerialisasi baris yang cocok.

• Kondisi selektivitas tinggi: Setelah kondisi penggabungan diturunkan, sebagian besar baris difilter. Semakin banyak data yang dieliminasi oleh indeks, semakin besar keuntungan performanya.

Aktifkan penurunan kondisi penggabungan

Gunakan parameter berikut untuk mengontrol penurunan kondisi penggabungan. Untuk petunjuk pengaturan parameter, lihat Konfigurasikan parameter kluster dan node.

Untuk menonaktifkan sepenuhnya penurunan kondisi penggabungan, atur join_predicate_pushdown=OFF dalam loose_polar_optimizer_switch. Untuk mengaktifkannya di semua node alih-alih hanya di node read-only, atur loose_join_predicate_pushdown_opt_mode ke ON.

Contoh

Contoh berikut menunjukkan kueri yang menggabungkan tiga tabel turunan. Tabel turunan os menggunakan fungsi jendela (ROW_NUMBER() OVER), sehingga tidak dapat digabungkan ke dalam kueri luar dan harus dimaterialisasi.

Sebelum pushdown

Tanpa penurunan kondisi penggabungan, os dimaterialisasi sepenuhnya—semua baris yang sesuai filter update_date dipindai dan diagregasi sebelum kondisi penggabungan od.id = os.detail_id diterapkan. Kueri ini membutuhkan waktu sekitar 65 detik.

SELECT *
FROM (
  SELECT *
  FROM sample_table.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM sample_table.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN (
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM sample_table.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
  ) os
  ON od.id = os.detail_id;

Setelah penurunan

Dengan penurunan kondisi penggabungan diaktifkan, pengoptimal menulis ulang rencana untuk mendorong od.id = detail_id ke dalam subkueri os, yang diekspresikan sebagai penggabungan lateral. Tabel os menggunakan indeks detail_id untuk memfilter baris sebelum fungsi jendela dijalankan, sehingga sebagian besar data dieliminasi lebih awal. Kueri ini membutuhkan waktu sekitar 0,5 detik.

SELECT *
FROM (
  SELECT *
  FROM db_order.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM db_order.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN LATERAL((
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM db_order.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
      AND od.id = detail_id
  )) os;

Perubahan utamanya adalah AND od.id = detail_id di dalam subkueri os, dikombinasikan dengan sintaks LEFT JOIN LATERAL. Hal ini memungkinkan pengoptimal menerapkan filter berbasis indeks sebelum mematerialisasi os, bukan setelahnya.

Jika kueri Anda tidak mendapatkan manfaat dari penurunan meskipun memenuhi kriteria umum, pastikan kolom penggabungan di dalam tabel turunan telah terindeks dan kondisi penggabungannya memiliki selektivitas tinggi.