Cara menggunakan fitur penyatuan subkueri - PolarDB - Alibaba Cloud Documentation Center

Penyatuan subkueri mengurangi jumlah subkueri dalam suatu Pernyataan SQL dengan menggabungkan atau menghilangkan subkueri yang redundan, sehingga menurunkan beban eksekusi tanpa mengubah hasil kueri.

Cara kerja

Ketika dua subkueri mengakses set yang tumpang tindih, pengoptimal dapat menghilangkan atau menggabungkannya:

Hapus — satu subkueri dihilangkan sepenuhnya karena hasilnya sudah dijamin oleh subkueri lainnya.
Gabung — kondisi dari dua subkueri digabung menjadi satu subkueri tunggal.

Pengoptimal menerapkan aturan penyatuan berdasarkan dua properti pasangan subkueri: hubungan tipe (tipe yang sama atau saling eksklusif) dan relasi inklusi antara set hasilnya.

Konsep utama

Tipe subkueri yang didukung

Type	Operator	Contoh
EXISTS	`EXISTS`, `NOT EXISTS`	`WHERE EXISTS (SELECT 1 FROM t2)`
IN	`IN`, `NOT IN`	`WHERE a IN (SELECT a FROM t2)`
ANY	`= ANY`, `!= ANY`, `< ANY`, `<= ANY`, `> ANY`, `>= ANY`	`WHERE t.a > ANY (SELECT t2.a FROM t2)`
ALL	`= ALL`, `!= ALL`, `< ALL`, `<= ALL`, `> ALL`, `>= ALL`	`WHERE t.a > ANY (SELECT t2.a FROM t2)`

Subkueri skalar baris-tunggal (misalnya, WHERE t.a < (SELECT MIN(t2.a) ...)) tidak didukung.

Subkueri dengan tipe yang sama dan subkueri yang saling eksklusif

Subkueri dengan tipe yang sama menggunakan operator yang sama. Dua subkueri EXISTS, atau dua subkueri > ANY, merupakan subkueri dengan tipe yang sama.
Subkueri yang saling eksklusif menggunakan operator yang berlawanan. Tabel berikut menunjukkan semua pasangan yang saling eksklusif.

subkueri	Subkueri yang saling eksklusif
`EXISTS`	`NOT EXISTS`
`IN`	`NOT IN`
`= ANY`	`!= ALL`
`!= ANY`	`= ALL`
`< ANY`	`>= ALL` atau `> ALL`
`<= ANY`	`> ALL`
`> ANY`	`<= ALL` atau `< ALL`
`>= ANY`	`< ALL`

Ekuivalensi ini mencerminkan identitas logika dasar: IN setara dengan = ANY, dan NOT IN setara dengan != ALL. Memahami hal ini mempermudah penguasaan pasangan yang saling eksklusif.

Relasi inklusi

Sisi kanan subkueri adalah sebuah set. Dua set dapat memiliki empat jenis relasi:

Relasi	Makna
Subset kiri	Set kiri merupakan subset sejati dari set kanan
Subset kanan	Set kanan merupakan subset sejati dari set kiri
Sama	Kedua set berisi elemen yang sama
Tidak dapat dibandingkan	Tidak ada satu pun set yang memuat set lainnya

Contoh subset kiri: Pada kueri berikut, subq1 menerapkan kondisi tambahan t2.a > 10, sehingga hasilnya selalu merupakan subset dari subq2.

SELECT a FROM t
WHERE EXISTS (SELECT /*+ subq1 */ t2.a FROM t2 WHERE t2.a > 10)   -- subq1
  AND EXISTS (SELECT /*+ subq2 */ t2.a FROM t2);                   -- subq2

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

Kluster PolarDB for MySQL 8.0 dengan versi revisi 8.0.2.2.23 atau lebih baru

Untuk memeriksa versi kluster Anda, lihat Versi engine 5.6, 5.7, dan 8.0.

Aktifkan penyatuan subkueri

Dua parameter mengatur perilaku pelipatan:

Parameter	Ruang lingkup	Bawaan	Deskripsi
`loose_polar_optimizer_switch`	Global	`coalesce_subquery=off`	Mengaktifkan atau menonaktifkan penyatuan subkueri. Atur ke `coalesce_subquery=on` untuk mengaktifkan.
`force_coalesce_subquery`	Global / session	`OFF`	Memaksa operasi penggabungan yang ditandai sebagai tidak selalu optimal. Komponen Transformasi Query Berbasis Biaya (CBQT) pengoptimal biasanya menentukan apakah penggabungan meningkatkan performa; mengatur nilai ini ke `ON` melewati pemeriksaan tersebut.

Aktifkan penyatuan subkueri:

SET loose_polar_optimizer_switch = 'coalesce_subquery=on';

Paksa penggabungan subkueri pada session saat ini (gunakan hanya setelah memastikan penggabungan tersebut bermanfaat):

SET force_coalesce_subquery = ON;

Targetkan subkueri tertentu dengan sintaks HINT — beri nama blok kueri menggunakan QB_NAME dan tentukan pasangan mana yang akan disatukan dengan SUBQUERY_COALESCE:

DESC SELECT /*+ SUBQUERY_COALESCE(qb1, qb2) SUBQUERY_COALESCE(qb3, qb4) */
  *
FROM t1
LEFT JOIN t2 ON t1.a = ANY  (SELECT /*+ QB_NAME(qb1) */ a FROM t2)
           AND t1.a != ALL  (SELECT /*+ QB_NAME(qb2) */ a FROM t2 WHERE a < 100)
HAVING     t1.b  = ANY      (SELECT /*+ QB_NAME(qb3) */ b FROM t2)
       AND t1.b != ALL      (SELECT /*+ QB_NAME(qb4) */ b FROM t2 WHERE b < 1);

Objek yang disatukan dapat muncul di posisi apa pun dalam kondisi WHERE, HAVING, atau JOIN ON, termasuk di bawah operator AND maupun OR.

Aturan Pelipatan

Subkueri dengan tipe yang sama

Operator AND

Tipe subkueri	Relasi inklusi	Hasil
Keduanya: EXISTS, IN, ANY, atau ALL	Subset kiri atau sama	Hapus — subkueri kanan dihilangkan, subkueri kiri dipertahankan
Keduanya: EXISTS, IN, ANY, atau ALL	Subset kanan	Hapus — subkueri kiri dihilangkan, subkueri kanan dipertahankan
Keduanya: NOT EXISTS, NOT IN, atau `!= ALL`	Tidak dapat dibandingkan	Gabung (tidak selalu optimal) — kondisi WHERE atau HAVING digabung menjadi satu subkueri. Memerlukan subkueri SPJ atau subkueri dengan hanya kondisi SPJ dan HAVING. Subkueri dengan hanya kondisi WHERE atau kondisi HAVING yang tidak konsisten juga didukung.

Operator OR

Tipe subkueri	Relasi inklusi	Hasil
Keduanya: EXISTS, IN, ANY, atau ALL	Subset kiri atau sama	Hapus — subkueri kiri dihilangkan, subkueri kanan dipertahankan
Keduanya: EXISTS, IN, ANY, atau ALL	Subset kanan	Hapus — subkueri kanan dihilangkan, subkueri kiri dipertahankan
Keduanya: EXISTS, IN, atau ANY	Tidak dapat dibandingkan	Gabung (tidak selalu optimal) — kondisi WHERE atau HAVING digabung menjadi satu subkueri. Memerlukan subkueri SPJ atau subkueri dengan hanya kondisi SPJ dan HAVING. Subkueri dengan hanya kondisi WHERE atau kondisi HAVING yang tidak konsisten juga didukung.

Subkueri yang saling eksklusif

Operator AND

Tipe subkueri	Relasi inklusi	Kondisi penggabungan	Hasil
EXISTS + NOT EXISTS; IN + NOT IN	Subset kiri atau sama	—	Hapus — kondisi AND ditulis ulang menjadi `FALSE`
EXISTS + NOT EXISTS	Subset kanan	Blok kueri tidak boleh UNION; hanya kondisi WHERE yang berbeda; subkueri bersarang didukung	Gabungkan (tidak selalu optimal) — set digabungkan, dengan penambahan `HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) == 0`
`!= ANY` + `= ALL`; `< ANY` + `>= ALL` atau `> ALL`; `<= ANY` + `> ALL`; `> ANY` + `<= ALL` atau `< ALL`; `>= ANY` + `< ALL`	Subset kiri atau sama	—	Hapus — kondisi AND ditulis ulang menjadi `FALSE`
IN + NOT IN; `= ANY` + `!= ALL`	Subset kanan	Blok kueri tidak boleh UNION; hanya kondisi WHERE atau HAVING yang berbeda; subkueri bersarang didukung	Gabung (selalu optimal) — set digabung, operator LNNVL ditambahkan. Diterapkan secara bawaan tanpa perlu `force_coalesce_subquery`.

Operator OR

Tipe subkueri	Relasi inklusi	Hasil
EXISTS + NOT EXISTS	Subset kanan	Remove — kondisi OR ditulis ulang menjadi `TRUE`

Contoh

Hapus subkueri dengan tipe yang sama

Kondisi AND

-- Sebelum
SELECT * FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)   -- Subkueri 1
  AND EXISTS (SELECT 1 FROM t2);               -- Subkueri 2

Subkueri 1 merupakan subset dari subkueri 2 → subkueri 2 redundan dalam kondisi AND → subkueri 2 dihapus.

-- Setelah
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0);

Kondisi OR

-- Sebelum
SELECT * FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)   -- Subkueri 1
   OR EXISTS (SELECT 1 FROM t2);               -- Subkueri 2

Subkueri 1 merupakan subset dari subkueri 2 → dalam kondisi OR, set yang lebih besar mendominasi → subkueri 1 dihapus.

-- Setelah
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

Gabung subkueri dengan tipe yang sama

Kondisi AND

-- Sebelum
SELECT * FROM t1
WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND b < 10)
  AND NOT EXISTS (SELECT a           FROM t1 WHERE a > 10 AND c < 3);

Kedua subkueri mengakses tabel yang sama dengan kondisi dasar yang sama (a > 10); kondisi tambahannya digabung dengan OR.

-- Setelah
SELECT * FROM t1
WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND (b < 10 OR c < 3));

Kondisi OR

-- Sebelum
SELECT * FROM t1
WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND b < 10)
   OR EXISTS (SELECT a           FROM t1 WHERE a > 10 AND c < 3);

Kedua subkueri mengakses tabel yang sama dengan kondisi dasar yang sama; kondisi tambahannya digabung dengan OR.

-- Setelah
SELECT * FROM t1
WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND (b < 10 OR c < 3));

Hapus subkueri yang saling eksklusif

EXISTS dan NOT EXISTS — kondisi AND

-- Sebelum
SELECT * FROM t1
WHERE     EXISTS (SELECT 1 FROM t2 WHERE c1 = 0)   -- Subkueri 1
  AND NOT EXISTS (SELECT 1 FROM t2);               -- Subkueri 2

Subkueri 1 merupakan subset dari subkueri 2. Suatu baris tidak mungkin memenuhi kedua kondisi EXISTS (subset) dan NOT EXISTS (superset) secara bersamaan → kondisi AND selalu bernilai salah.

-- Setelah
SELECT * FROM t1 WHERE false;

Konflik ANY dan ALL — kondisi AND

Berlaku untuk: > ANY + < ALL atau <= ALL; < ANY + > ALL atau >= ALL.

-- Sebelum
SELECT * FROM t1
WHERE t1.c1 > ANY (SELECT c1 FROM t2 WHERE c1 > 10 AND c2 > 1)   -- Set ANY
  AND t1.c1 < ALL (SELECT c1 FROM t2 WHERE c1 > 10);              -- Set ALL

Set ANY merupakan subset dari set ALL. Tidak ada nilai yang dapat sekaligus lebih besar dari beberapa elemen subset dan lebih kecil dari semua elemen superset → kondisi AND selalu bernilai salah.

-- Setelah
SELECT * FROM t1 WHERE false; //Set ANY merupakan subset dari set ALL.

EXISTS dan NOT EXISTS — kondisi OR

-- Sebelum
SELECT * FROM t1
WHERE     EXISTS (SELECT 1 FROM t2)                    -- Subkueri 1
   OR NOT EXISTS (SELECT 1 FROM t2 WHERE c1 = 0);     -- Subkueri 2

Subkueri 2 merupakan subset dari subkueri 1. Dalam kondisi OR, jika superset tidak kosong maka kondisi bernilai benar; jika kosong, komplemen dari subset selalu bernilai benar → kondisi OR selalu bernilai benar.

-- Setelah
SELECT * FROM t1 WHERE true; // Subkueri 2 merupakan subset dari subkueri 1.

Gabung subkueri yang saling eksklusif

Gabung EXISTS dan NOT EXISTS

-- Sebelum
SELECT * FROM t1
WHERE     EXIST (SELECT 1 FROM t2)                -- Subkueri 1
  AND NOT EXIST (SELECT 1 FROM t2 WHERE c2 = 0); -- Subkueri 2

Set NOT EXISTS merupakan subset kanan dari set EXISTS. Pengoptimal menggabungkan keduanya menjadi satu pemindaian dan menambahkan kondisi HAVING untuk mengecualikan baris yang cocok dengan predikat NOT EXISTS.

-- Setelah
SELECT * FROM t1
WHERE EXIST (
  SELECT 1 FROM t2
  HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0
);

Penggabungan ini tidak selalu optimal. Secara bawaan, komponen CBQT menentukan apakah akan menerapkannya. Untuk memaksa penggabungan, atur force_coalesce_subquery = ON.

Grafik berikut menunjukkan durasi kueri untuk TPCH Q21 sebelum dan sesudah mengaktifkan penyatuan subkueri. Batang yang lebih pendek menunjukkan performa yang lebih baik.

Gabung IN dan NOT IN (atau = ANY dan != ALL)

Berlaku untuk: IN + NOT IN (set NOT IN merupakan subset kiri); = ANY + != ALL (set ALL merupakan subset kiri).

-- Sebelum
SELECT * FROM t1
WHERE  t1.c1  = ANY (SELECT c1 FROM t2 WHERE c1 > 10)     -- Set = ANY
  AND t1.c1 != ALL  (SELECT c1 FROM t2 WHERE c1 > 100);   -- Set != ALL (subset kiri)

Set != ALL (c1 > 100) merupakan subset dari set = ANY (c1 > 10). Pengoptimal menyatukannya dengan menambahkan kondisi LNNVL ke subkueri yang lebih besar, mengecualikan baris yang juga memenuhi subkueri yang lebih kecil.

-- Setelah
SELECT * FROM t1
WHERE t1.c1 = ANY (SELECT c1 FROM t2 WHERE c1 > 10 AND LNNVL(c1 > 100));

Penggabungan ini selalu optimal dan diterapkan secara bawaan tanpa perlu mengatur force_coalesce_subquery.