PolarDB:Penguraian subkueri tak berkorelasi

Cara kerja

Gambar berikut menunjukkan struktur kueri yang berisi subkueri.

T1, T2, dan T3 masing-masing merepresentasikan kumpulan satu atau beberapa tabel dan tampilan. T2 (di dalam subkueri) bersarang dengan T3, seperti yang ditunjukkan oleh garis putus-putus. T1 berada di kueri luar dan tidak bersarang dengan T2.

Strategi window function berlaku jika semua kondisi berikut terpenuhi:

• Subkueri skalar tidak mengandung klausa LIMIT atau DISTINCT, dan output-nya merupakan fungsi agregasi.

• Tabel dalam subkueri merupakan subset dari tabel-tabel dalam kueri luar.

• Subkueri dihubungkan ke kueri luar melalui equi join. Kueri luar berisi kondisi gabungan dengan semantik yang sama dan kondisi filter untuk tabel-tabel umum dalam subkueri.

• Kolom gabungan dalam subkueri merupakan kolom primary key atau unique key.

• Baik subkueri maupun kueri luar tidak mengandung fungsi kustom atau fungsi acak.

Setelah penguraian, window function menghitung agregasi dalam kelompok dan menyambungkan hasilnya ke setiap baris, sehingga menghilangkan kebutuhan untuk mengeksekusi ulang subkueri.

Contoh

Contoh berikut menggunakan TPC-H Q2 (Minimum Cost Supplier Query), yang mencari pemasok dengan harga terendah di antara semua pemasok di suatu wilayah yang menjual komponen dengan jenis dan ukuran tertentu.

Pada MySQL Community Edition, kueri luar pertama-tama mengambil semua pemasok yang sesuai. Subkueri kemudian dijalankan pada setiap baris untuk menemukan biaya pasok minimum. Untuk set data besar, ini berarti subkueri dieksekusi sekali per baris pemasok.

Kueri asli:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
 s_address, s_phone, s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
   AND s_suppkey = ps_suppkey
   AND p_size = 30
   AND p_type LIKE '%STEEL'
   AND s_nationkey = n_nationkey
   AND n_regionkey = r_regionkey
   AND r_name = 'ASIA'
   AND ps_supplycost = (
       SELECT MIN(ps_supplycost)
       FROM partsupp, supplier, nation, region
       WHERE p_partkey = ps_partkey
           AND s_suppkey = ps_suppkey
           AND s_nationkey = n_nationkey
           AND n_regionkey = r_regionkey
           AND r_name = 'ASIA'
   )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

PolarDB menulis ulang kueri ini menggunakan MIN() OVER(PARTITION BY ps_partkey) untuk menghitung biaya minimum per komponen dalam satu kali pemindaian, lalu memfilter baris-baris di mana biaya pasok cocok dengan minimum kelompok tersebut.

Kueri yang ditulis ulang:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
  s_address, s_phone, s_comment
FROM (
    SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
      ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
      s_phone, s_comment
    FROM part, partsupp, supplier, nation, region
    WHERE p_partkey = ps_partkey
      AND s_suppkey = ps_suppkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND p_size = 30
      AND p_type LIKE '%STEEL'
      AND r_name = 'ASIA') as derived
WHERE ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

Perbaikan performa

Pada faktor skala TPC-H 10:

• Q2: Peningkatan 1,54x

• Q17: Peningkatan 4,91x

Cara kerja

Gambar berikut menunjukkan struktur kueri yang berisi subkueri.

Strategi GROUP BY berlaku jika semua kondisi berikut terpenuhi:

• Subkueri skalar tidak mengandung klausa GROUP BY atau LIMIT, dan output-nya merupakan fungsi agregasi.

• Subkueri skalar muncul dalam kondisi JOIN, WHERE, atau SELECT.

• Subkueri dihubungkan ke kueri luar melalui equi join, dengan kondisi yang digabungkan oleh AND.

• Subkueri skalar tidak mengandung fungsi kustom atau fungsi acak.

Setelah penguraian, subkueri berubah menjadi tabel turunan yang melakukan pre-agregasi hasil yang dikelompokkan berdasarkan kunci gabungan. Kueri luar kemudian melakukan join terhadap tabel turunan ini hanya sekali, menggantikan eksekusi subkueri berulang.

Contoh

Contoh berikut mengambil pesanan di mana kuantitas melebihi 10% dari total jumlah pembelian untuk produk yang sama.

Kueri asli:

SELECT *
FROM sale_lineitem sl
WHERE sl.sl_quantity >
    (SELECT 0.1 * SUM(pl.pl_quantity)
     FROM purchase_lineitem pl
     WHERE pl.pl_objectkey = sl.sl_objectkey);

Tanpa penguraian, database melakukan iterasi pada setiap baris di sale_lineitem, membaca sl_objectkey, dan mengeksekusi ulang subkueri untuk nilai tersebut. Subkueri dijalankan sebanyak jumlah baris di sale_lineitem. Karena sl_objectkey biasanya berisi banyak nilai duplikat, agregasi yang sama pada purchase_lineitem diulang berkali-kali—bahkan ketika indeks tersedia pada pl_objectkey.

PolarDB menulis ulang kueri ini sebagai LEFT JOIN terhadap tabel turunan yang telah dipre-agregasi. Tabel purchase_lineitem hanya dipindai sekali.

Kueri yang ditulis ulang:

SELECT *
FROM sale_lineitem sl
LEFT JOIN
  (SELECT (0.1 * sum(pl.pl_quantity)) AS Name_exp_1,
          pl.pl_objectkey AS Name_exp_2
   FROM purchase_lineitem pl
   GROUP BY pl.pl_objectkey) derived ON derived.Name_exp_2 = sl.sl_objectkey
WHERE sl.sl_quantity > derived.name_exp_1;

Pengoptimal dapat lebih meningkatkan eksekusi dengan mengurutkan ulang join berdasarkan estimasi biaya.