PolarDB：Penurunan kondisi penggabungan

Prasyarat

Kluster Anda harus menjalankan PolarDB for MySQL 8.0 dengan versi revisi 8.0.2.2.10 atau yang lebih baru.

Latar Belakang

Tabel turunan (atau tampilan inline) sering digunakan dalam query analitik kompleks untuk menyederhanakan pembuatan Pernyataan SQL dan menggambarkan semantik query secara langsung. Di MySQL asli, jika tabel turunan tidak dapat digabungkan ke dalam query luar (misalnya, karena mencakup operasi seperti GROUP BY atau fungsi agregat), tabel turunan harus dieksekusi menggunakan materialisasi. Jika sebuah Pernyataan SQL melibatkan sejumlah besar data (misalnya, diperlukan pemindaian tabel dalam jumlah besar), efisiensi eksekusi bisa sangat rendah. Dengan mendorong kondisi penggabungan luar ke dalam tabel turunan, rencana eksekusi dapat memanfaatkan indeks secara lebih efisien, sehingga meningkatkan kinerja query kompleks secara signifikan.

Skenario

• Dalam query kompleks, tabel turunan dan tabel luar digabungkan menggunakan loop bersarang. Kolom yang digabungkan berada di lapisan dalam tabel turunan. Oleh karena itu, indeks dapat digunakan untuk mempercepat materialisasi tabel dalam. Statistik yang akurat diperlukan untuk memastikan bahwa sejumlah besar data dapat difilter di lapisan dalam setelah kondisi penggabungan didorong ke bawah.

• Setelah kondisi penggabungan didorong ke bawah, jika indeks dapat digunakan secara efektif dan sejumlah besar data difilter, rencana eksekusi yang lebih efisien akan dihasilkan. Fitur penurunan kondisi penggabungan bergantung pada kemampuan perhitungan biaya optimizer untuk menentukan secara cerdas apakah akan mendorong kondisi penggabungan luar ke bawah.

Penggunaan

Anda dapat menggunakan parameter loose_polar_optimizer_switch untuk mengaktifkan penurunan kondisi penggabungan, serta parameter loose_join_predicate_pushdown_opt_mode untuk menentukan node-node tempat Anda ingin mengaktifkan penurunan kondisi penggabungan. Untuk informasi tentang cara mengonfigurasi parameter, lihat Mengonfigurasi Parameter Kluster dan Node.

Contoh

Query Asli

Dalam query asli, tabel turunan os harus dimaterialisasi sepenuhnya tanpa adanya kondisi filter yang memberikan laju seleksi tinggi. Akibatnya, waktu yang lama diperlukan untuk mematerialisasi tabel turunan os. Query ini membutuhkan sekitar 65 detik untuk selesai.

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;

Query Dioptimalkan

Kondisi penggabungan od.id = os.detail_id antara tabel od dan tabel os didorong ke dalam tabel os. Dalam hal ini, tabel os dapat menggunakan indeks detail_id untuk menyaring sejumlah besar data, sehingga meningkatkan efisiensi eksekusi. Query ini membutuhkan sekitar 0,5 detik untuk selesai.

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;