Cara menggunakan fitur penyatuan subquery - PolarDB

Penyatuan subquery adalah metode optimasi subquery yang melipat beberapa subquery dalam sebuah Pernyataan SQL berdasarkan aturan tertentu untuk mengurangi jumlah subquery. Hal ini mempercepat eksekusi Pernyataan SQL. Topik ini menjelaskan latar belakang, prinsip, penggunaan, dan contoh dari penyatuan subquery.

Latar Belakang

Jenis-jenis subquery

Tabel berikut menjelaskan jenis-jenis subquery yang didukung oleh PolarDB untuk MySQL.

Jenis subquery	Kata kunci operator	Operator perbandingan	Catatan
EXISTS	`EXISTS` dan `NOT EXISTS`	Tidak ada	Tidak ada
IN	`IN` dan `NOT IN`	Tidak ada	Tidak ada
ANY	Tidak ada	=, !=, <, <=, <, >=	Contoh: `WHERE t.a > ANY(select t2.a ...)`
ALL	Tidak ada	=, !=, <, <=, <, >=	Contoh: `WHERE t.a > ANY(select t2.a ...)`
Subquery skalar satu baris	Contoh: `WHERE t.a < (SELECT MIN(t2.a) ...)`. Fitur penyatuan subquery tidak mendukung jenis subquery ini.

Subquery dengan tipe yang sama: Subquery yang memiliki kata kunci operator yang sama disebut subquery dengan tipe yang sama. Jika dua subquery keduanya adalah EXISTS atau > ANY, maka subquery tersebut merupakan subquery dengan tipe yang sama.
Subquery yang saling eksklusif: Subquery yang memiliki kata kunci operator yang berlawanan disebut subquery yang saling eksklusif. Sebagai contoh, EXISTS dan NOT EXISTS adalah subquery yang saling eksklusif, serta IN dan NOT IN. Tabel berikut menjelaskan lebih banyak subquery yang saling eksklusif.
Subquery
Subquery 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

Hubungan inklusi subquery

Sisi kanan subquery adalah himpunan. Himpunan memiliki tiga jenis hubungan inklusi: subset kiri, subset kanan, dan sama. Jika dua himpunan tidak memiliki hubungan inklusi, maka himpunan tersebut dianggap tidak dapat dibandingkan. Bagian berikut menggunakan subset kiri sebagai contoh.

Subset Kiri: Jika himpunan di sisi kiri subquery adalah subset dari himpunan di sisi kanan, maka himpunan di sisi kiri adalah subset kiri. Contoh:

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

Dalam contoh sebelumnya, himpunan subq1 di sisi kiri memiliki kondisi yang lebih ketat dan ukuran yang lebih kecil, sehingga merupakan subset dari himpunan subq2 di sisi kanan. Oleh karena itu, subq1 adalah subset kiri.

Ikhtisar

Catatan

Objek yang dilipat dapat muncul di posisi mana pun dari kondisi WHERE, HAVING, atau JOIN ON, dan subquery muncul di bawah operator AND dan OR secara bersamaan.

Subquery bisa berupa subquery EXISTS, IN, atau ALL. Semua operator didukung.

Subquery dengan tipe yang sama

Jika himpunan dari dua subquery memiliki hubungan inklusi, salah satu subquery dihapus. Tabel berikut menjelaskan detailnya.

Operator antara subquery	Tipe subquery kiri atau kanan	Hubungan inklusi subquery	Batasan	Jenis pelipatan	Deskripsi
AND	Tipe-tipe tersebut semuanya adalah EXISTS, IN, ANY, atau ALL.	Subset kiri dan sama	Tidak ada	Hapus	Subset kanan dihapus dan subquery kiri dipertahankan. Untuk informasi lebih lanjut, lihat Contoh 1: Menghapus subquery dalam kondisi AND.
		Subset kanan	Tidak ada	Hapus	Subset kiri dihapus dan subquery kanan dipertahankan.
	Tipe-tipe tersebut semuanya adalah NOT EXISTS, NOT IN, atau != ALL.	Tidak dapat dibandingkan	Subquery SPJ dan subquery yang hanya berisi SPJ dan kondisi HAVING didukung. Subquery yang hanya berisi kondisi WHERE dan subquery yang kondisi HAVING-nya tidak konsisten didukung.	Gabung (tidak selalu optimal) Catatan Tidak selalu optimal berarti bahwa efisiensi eksekusi setelah pelipatan mungkin lebih buruk daripada sebelum pelipatan, dan pelipatan tidak dijamin menjadi optimasi positif. Anda perlu menggunakan komponen Transformasi Query Berbasis Biaya (CBQT) untuk menentukan apakah akan menerapkan aturan tersebut.	Kondisi WHERE atau HAVING dari dua subquery digabungkan menjadi subquery baru. Untuk informasi lebih lanjut, lihat Contoh 1: Menggabungkan subquery dalam kondisi AND.
OR	Tipe-tipe tersebut semuanya adalah EXISTS, IN, ANY, atau ALL.	Subset kiri dan sama	Tidak ada	Hapus	Subset kiri dihapus dan subset kanan dipertahankan. Untuk informasi lebih lanjut, lihat Contoh 2: Menghapus subquery dalam kondisi OR.
		Subset kanan	Tidak ada	Hapus	Subset kanan dihapus dan subset kiri dipertahankan.
	Tipe-tipe tersebut semuanya adalah EXISTS, IN, atau ANY.	Tidak dapat dibandingkan	Subquery SPJ dan subquery yang hanya berisi SPJ dan kondisi HAVING didukung. Subquery yang hanya berisi kondisi WHERE dan subquery yang kondisi HAVING-nya tidak konsisten didukung.	Gabung (tidak selalu optimal)	Kondisi WHERE atau HAVING dari dua subquery digabungkan menjadi subquery baru. Untuk informasi lebih lanjut, lihat Contoh 2: Menggabungkan subquery dalam kondisi OR.

Subquery yang saling eksklusif

Jika himpunan dari dua subquery memiliki hubungan inklusi, subquery dapat ditulis ulang sebagai TRUE atau FALSE berdasarkan konteks operasi logis, atau dua subquery dapat digabungkan menjadi subquery baru. Tabel berikut menjelaskan detailnya.

Operator antara subquery	Tipe subquery kiri atau kanan	Hubungan inklusi subquery	Batasan	Jenis pelipatan	Deskripsi
AND	EXISTS dan NOT EXISTS IN dan NOT IN	Subset kiri dan sama	Tidak ada	Hapus	Kondisi AND ditulis ulang menjadi FALSE. Untuk informasi lebih lanjut, lihat Contoh 1: Konflik tipe eksklusif EXISTS.
	EXISTS dan NOT EXISTS	Subset kanan	Blok query dari subquery tidak boleh UNION. Hanya kondisi WHERE yang berbeda. Subquery mendukung subquery bertingkat.	Gabung (tidak selalu optimal)	Himpunan digabungkan, dan kondisi `HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0` ditambahkan. Untuk informasi lebih lanjut, lihat Contoh 4: Menggabungkan subquery eksklusif EXISTS.
	!=ANY dan =ALL <ANY dan >=ALL atau >ALL <=ANY dan >ALL >ANY dan <=ALL atau <ALL >=ANY dan <ALL	Subset kiri dan sama	Tidak ada	Hapus	Kondisi AND ditulis ulang menjadi FALSE. Untuk informasi lebih lanjut, lihat Contoh 2: Konflik tipe eksklusif ANY atau ALL.
	IN dan NOT IN =ANY dan !=ALL	Subset kanan	Blok query dari subquery tidak boleh UNION. Hanya kondisi WHERE atau HAVING yang berbeda. Subquery mendukung subquery bertingkat.	Gabung (selalu optimal)	Himpunan digabungkan dan operator LNNVL ditambahkan. Pelipatan selalu optimal dan subquery dilipat secara default. Untuk informasi lebih lanjut, lihat Contoh 5: Konflik tipe eksklusif ANY atau ALL.
OR	EXISTS dan NOT EXISTS	Subset kanan	Tidak ada	Hapus	Kondisi OR ditulis ulang menjadi TRUE. Untuk informasi lebih lanjut, lihat Contoh 3: Menghapus query EXISTS dalam kondisi OR.

Prasyarat

Versi kluster harus PolarDB untuk MySQL 8.0 dan versi revisi harus 8.0.2.2.23 atau lebih baru. Untuk informasi tentang cara melihat versi kluster, lihat Versi mesin 5.6, 5.7, dan 8.0.

Penggunaan

Anda dapat mengatur parameter loose_polar_optimizer_switch ke coalesce_subquery=on untuk mengaktifkan fitur penyatuan subquery dan parameter force_coalesce_subquery ke ON untuk mengaktifkan fitur penggabungan subquery. Untuk informasi lebih lanjut tentang cara mengonfigurasi parameter, lihat Konfigurasikan Parameter Kluster dan Node.

Parameter

Tingkat

Deskripsi

loose_polar_optimizer_switch

Global

Mengaktifkan atau menonaktifkan fitur penyatuan subquery. Secara default, subquery tidak digabungkan.

Nilai valid:

coalesce_subquery=on: mengaktifkan fitur penyatuan subquery.
coalesce_subquery=off: menonaktifkan fitur penyatuan subquery.

force_coalesce_subquery

Global

Mengaktifkan atau menonaktifkan fitur penggabungan subquery. Aturan pelipatan tidak selalu optimal dalam tabel aturan pelipatan subquery diberlakukan.

Nilai valid:

OFF (default): menonaktifkan fitur penggabungan subquery.
ON: mengaktifkan fitur penggabungan subquery.

Catatan

Anda dapat mengonfigurasi parameter ini dalam sesi. Contoh:
```
SET force_coalesce_subquery=ON;
```

Anda juga dapat menggunakan sintaks HINT untuk menentukan subquery yang akan dilipat. Contoh:

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);

Contoh

Hapus subquery dengan tipe yang sama

Contoh 1: Menghapus subquery dalam kondisi AND

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

Subquery 1 adalah subset dari subquery 2. Oleh karena itu, subquery 2 dihapus. Pernyataan SQL dengan subquery 2 dihapus:

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

Contoh 2: Menghapus subquery dalam kondisi OR

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)  -- Subquery 1
	or EXISTS (SELECT 1 FROM t2);		               -- Subquery 2

Subquery 1 dihapus, kondisi OR ditulis ulang menjadi EXISTS (SELECT 1 FROM t2), dan himpunan yang lebih besar dipertahankan. Pernyataan SQL dengan subquery 1 dihapus:

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

Gabungkan subquery dengan tipe yang sama

Contoh 1: Menggabungkan subquery dalam kondisi AND

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);

Pernyataan SQL dengan subquery digabungkan:

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

Contoh 2: Menggabungkan subquery dalam kondisi OR

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);

Pernyataan SQL dengan subquery digabungkan:

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

Hapus subquery yang saling eksklusif

Contoh 1: Konflik tipe eksklusif EXISTS

Skenario: EXISTS dan NOT EXISTS, IN atau NOT IN

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

Kondisi AND ditulis ulang menjadi FALSE. Pernyataan SQL dengan kondisi ditulis ulang:

SELECT * FROM t1 WHERE false;

Contoh 2: Konflik tipe eksklusif ANY atau ALL

Skenario

>ANY dan <ALL, <=ALL
<ANY dan >ALL, >=ALL

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

Kondisi AND ditulis ulang menjadi FALSE. Pernyataan SQL dengan kondisi ditulis ulang:

SELECT * FROM t1 WHERE false; //Himpunan ANY adalah subset dari himpunan ALL.

Contoh 3: Menghapus query EXISTS dalam kondisi OR

SELECT * FROM t1 WHERE exists (SELECT 1 FROM t2 )  -- Subquery 1
      OR NOT exists (SELECT 1 FROM t2 WHERE c1 = 0);		-- Subquery 2

Kondisi OR ditulis ulang menjadi TRUE. Pernyataan SQL dengan kondisi ditulis ulang:

SELECT * FROM t1 WHERE true; // Subquery 2 adalah subset dari subquery 1.

Contoh 4: Menggabungkan subquery eksklusif EXISTS

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

Himpunan digabungkan, dan kondisi HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0 ditambahkan. Pernyataan SQL dengan himpunan digabungkan:

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

Catatan

Penggabungan tidak selalu optimal. Anda perlu menggunakan komponen CBQT untuk menentukan apakah akan melipat subquery. Jika Anda yakin bahwa penulisan ulang optimal, Anda perlu mengatur parameter force_coalesce_subquery ke ON untuk mengaktifkan fitur penggabungan subquery.

Berdasarkan data panas TPCH Q21, durasi kueri sebelum dan setelah fitur penyatuan subquery diaktifkan adalah sebagai berikut. Durasi yang lebih pendek menunjukkan penulisan ulang yang lebih baik:

Contoh 5: Konflik tipe eksklusif ANY atau ALL

Skenario

IN dan NOT IN. Himpunan NOT IN lebih kecil dan merupakan subset kiri.
=ANY dan !=ALL. Himpunan ALL lebih kecil dan merupakan subset kiri.

SELECT * FROM t1 WHERE t1.c1 = ANY (SELECT c1 FROM t2 WHERE c1 > 10) AND
t1.c1 != ALL (SELECT c1 FROM t2 WHERE  c1 > 100);

Himpunan digabungkan, dan operator LNNVL ditambahkan. Pernyataan SQL dengan himpunan digabungkan:

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

Subquery	Subquery 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`