Turunkan klausa WHERE ke subquery IN - PolarDB

PolarDB memungkinkan Anda menurunkan WHERE klausa ke IN subquery untuk mengoptimalkan query kompleks dan meningkatkan kinerja, terutama untuk subquery bersarang.

Prasyarat

Fitur ini didukung di kluster yang menggunakan mesin database berikut. Untuk informasi lebih lanjut tentang cara menanyakan versi mesin database, lihat Tanyakan versi mesin.

MySQL 8.0.1 dengan versi revisi 8.0.1.1.42 dan yang lebih baru.
MySQL 8.0.2 dengan versi revisi 8.0.2.19 dan yang lebih baru.

Skenario

Dalam query SQL, subquery yang digunakan dengan GROUP BY sering kali mengalami materialisasi. Pertimbangkan kasus di mana subquery adalah subquery IN, seperti dalam: (FIELD[1],...) IN (SELECT col[1], ... FROM ... GROUP BY ...), dan itu membentuk bagian dari kondisi WHERE dalam klausa AND query utama, contohnya: COND(FIELD[1])... AND (FIELD[1],...) IN (SELECT col[1], ... FROM ... GROUP BY ...). Setiap baris dalam set hasil harus memenuhi FIELD[1] = col[1]. Akibatnya, kondisi COND(FIELD[1]) dapat diturunkan ke dalam subquery.

Batasan

Penurunan kondisi tidak didukung jika subquery memiliki klausa tambahan.

SELECT * FROM t WHERE a > 1 AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c LIMIT 2);

Penurunan kondisi tidak didukung jika kolom dalam kondisi luar dan kolom dalam tabel yang dimaterialisasi tidak memenuhi kriteria tertentu, seperti ketika kolom merujuk pada subquery atau non-deterministik, atau ketika kolom merupakan bagian dari prosedur tersimpan atau fungsi.
- Kolom mungkin merujuk pada subquery atau bersifat non-deterministik, yang berpotensi menghasilkan hasil berbeda di bawah kondisi input yang sama. Sebagai contoh:
```
SELECT * FROM t WHERE a > 5*RAND() AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c);
```
- Kolom bisa dikaitkan dengan prosedur tersimpan atau fungsi. Namun, fungsi tersimpan yang kompleks mungkin tidak mendapat manfaat dari eksekusi berulang dan oleh karena itu tidak dipertimbangkan untuk penurunan. Contoh:
```
CREATE FUNCTION f1() RETURNS INT
BEGIN
...
END;

SELECT * FROM t WHERE a > f1() AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c);
```

Penggunaan

Persiapan

Untuk memanfaatkan penurunan kondisi, konfigurasikan parameter loose_subquery_cond_pushdown_mode sesuai kebutuhan Anda. Untuk informasi lebih lanjut, lihat Konfigurasikan parameter kluster dan node.

Tabel berikut menjelaskan parameter tersebut.

Nama Parameter

Tingkat

Deskripsi

loose_subquery_cond_pushdown_mode

Global

Saklar kontrol untuk fitur penurunan kondisi dari klausa WHERE ke subquery IN. Nilai valid:

REPLICA_ON: mengaktifkan fitur penurunan kondisi hanya pada node baca saja.
ON: mengaktifkan fitur penurunan kondisi.
OFF (default): menonaktifkan fitur penurunan kondisi.

Sebagai alternatif, kendalikan penurunan untuk subquery tertentu menggunakan OPTIMIZE HINT (SUBQUERY_CONDITION_PUSHDOWN atau NO_SUBQUERY_CONDITION_PUSHDOWN) tanpa mengubah loose_subquery_cond_pushdown_mode.

Contoh:

CREATE TABLE t1 (a INT, b INT, c INT, d INT);
CREATE TABLE t2 (e INT, f INT, g INT);

-- Pernyataan query ini menunjukkan bahwa blok query saat ini melarang penurunan kondisi ke subquery @subq1 --
SELECT  /*+ NO_SUBQUERY_CONDITION_PUSHDOWN(@subq1) */ * FROM t1
WHERE t1.c<25 AND
(t1.a,t1.c) IN (SELECT /*+ QB_NAME(subq1) */ t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e);

-- Pernyataan query ini menunjukkan bahwa blok query saat ini akan menurunkan kondisi yang memenuhi aturan ke subquery @subq1 --
SELECT  /*+ SUBQUERY_CONDITION_PUSHDOWN(@subq1) */ * FROM t1
WHERE t1.c<25 AND
(t1.a,t1.c) IN (SELECT /*+ QB_NAME(subq1) */ t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e);

Contoh 1

Untuk membandingkan rencana eksekusi sebelum dan sesudah mengaktifkan fitur untuk menurunkan WHERE klausa ke IN subquery, jalankan kode berikut:

EXPLAIN FORMAT=TREE SELECT * FROM t1
WHERE t1.c<25 AND
(t1.a,t1.c) IN (SELECT t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e);

Hasil sampel:

-- Sebelum mengaktifkan loose_subquery_cond_pushdown_mode
-> Filter: ((t1.c < 25) and <in_optimizer>((t1.a,t1.c),(t1.a,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                    -> Table scan on t2  (cost=*** rows=***)
                                
-- Setelah mengaktifkan loose_subquery_cond_pushdown_mode
-> Filter: ((t1.c < 25) and <in_optimizer>((t1.a,t1.c),(t1.a,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Filter: (max(t2.g) < 25)
                            -> Table scan on <temporary>
                                -> Aggregate using temporary table
                                    -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                        -> Table scan on t2  (cost=*** rows=***)

Catatan

Menganalisis semantik SQL mengungkapkan bahwa set hasil query untuk tabel t1 harus mematuhi hubungan kesetaraan antara t1.c dan kolom proyeksi MAX(t2.g) dalam subquery. Akibatnya, mengingat kondisi WHERE query utama t1.c < 25, subquery juga terikat untuk memenuhi MAX(t2.g)<25. Optimizer memanfaatkan kemampuan untuk menurunkan kondisi WHERE ke dalam subquery, memungkinkan kondisi t1.c<25 untuk turun ke subquery. Namun, karena klausa GROUP BY tidak mencakup kolom g, kondisi ini hanya dapat diterapkan pada klausa HAVING subquery.

Contoh 2

Untuk membandingkan rencana eksekusi sebelum dan sesudah mengaktifkan fitur untuk menurunkan WHERE klausa ke IN subquery, jalankan kode berikut:

EXPLAIN FORMAT=TREE SELECT * FROM t1
WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND
(t1.a,t1.b,t1.c) IN
(
SELECT t2.e,t2.f,MAX(t2.g)
FROM t2
WHERE t2.e<5
GROUP BY t2.e,t2.f
);

Hasil sampel:

-- Sebelum mengaktifkan loose_subquery_cond_pushdown_mode
-> Filter: ((t1.b > 3) and ((t1.a < 2) or (t1.a = 5)) and <in_optimizer>((t1.a,t1.b,t1.c),(t1.a,t1.b,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.b = `<materialized_subquery>`.f) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, f=t1.b, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                    -> Table scan on t2  (cost=*** rows=***)

-- Setelah mengaktifkan loose_subquery_cond_pushdown_mode
-> Filter: ((t1.b > 3) and ((t1.a < 2) or (t1.a = 5)) and <in_optimizer>((t1.a,t1.b,t1.c),(t1.a,t1.b,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.b = `<materialized_subquery>`.f) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, f=t1.b, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: ((t2.e < 5) and (t2.f > 3) and ((t2.e < 2) or (t2.e = 5)))  (cost=*** rows=***)
                                    -> Table scan on t2  (cost=*** rows=***)

Catatan

Dalam query utama dari query SQL, kondisi WHERE sedikit lebih kompleks. Namun, dengan menganalisis prinsip penurunan subquery di bawah kondisi WHERE, dapat ditemukan bahwa kolom t1.a, t1.b, t1.c dalam tabel query utama t1 setara dengan kolom proyeksi t2.e, t2.f, MAX(t2.g) dalam subquery. Oleh karena itu, untuk kondisi WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) yang dihubungkan oleh AND dalam query utama, dapat disimpulkan bahwa kolom yang sesuai dalam subquery harus memenuhi kondisi ((t2.e<2 OR t2.e=5) AND t2.f.b>3). Setelah mengaktifkan fitur penurunan subquery di bawah kondisi WHERE, optimizer dapat menurunkan kondisi WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) ke subquery. Analisis menunjukkan bahwa kolom dalam subquery GROUP BY mencakup t2.e, t2.f, sehingga kondisi tersebut diturunkan ke klausa WHERE subquery.