PolarDB：Optimasi subkueri

Metode modifikasi parameter kluster PolarDB berbeda antara Konsol dan session database, sebagai berikut:

• Di PolarDB console

  • Kompatibilitas: Untuk kompatibilitas dengan file konfigurasi MySQL, beberapa parameter kluster di PolarDB console memiliki awalan loose_.

  • Prosedur: Temukan dan modifikasi parameter yang memiliki awalan loose_.

• Di session database (menggunakan command line atau client)

  • Prosedur: Saat menggunakan perintah SET untuk memodifikasi parameter dalam session database, hapus awalan loose_ dan gunakan nama parameter aslinya.

Skenario 1: Menghilangkan nesting SELECT yang redundan

Jika sebuah subkueri hanya membungkus fungsi agregat atau ekspresi tanpa logika kompleks lainnya—seperti pada SELECT (SELECT SUM(a) FROM t2) FROM dual—pengoptimal akan menghapus SELECT luar dan menjalankan ekspresi intinya secara langsung.

-- Di kolom proyeksi
SELECT (SELECT SUM(a) FROM t2) FROM dual;
-- Setelah optimasi
SELECT SUM(`test`.`t2`.`a`) AS `sum(a)` FROM `test`.`t2`

-- Di klausa HAVING
SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));
-- Setelah optimasi
SELECT SUM(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` HAVING (0 <> count(`testdb`.`t2`.`b`))

1. Persiapkan data uji:

   DROP TABLE IF EXISTS t2;
   CREATE TABLE t2 (
       id INT PRIMARY KEY AUTO_INCREMENT,
       a INT,
       b INT
   );
   INSERT INTO t2 (a, b) VALUES (10, 100), (20, NULL), (50, 200), (120, NULL);

2. Nonaktifkan optimasi dan jalankan kueri: Pertama, nonaktifkan fitur optimasi subkueri dalam session untuk melihat rencana eksekusi sebelum optimasi.

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));

   SHOW warnings;
   
   -- Hasil
   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> (/* select#2 */ select (/* select#3 */ select (/* select#4 */ select count(`testdb`.`t2`.`b`)))))

3. Aktifkan optimasi dan jalankan kueri: Sekarang, aktifkan fitur optimasi subkueri dalam session dan jalankan kueri yang sama.

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));

   SHOW warnings;
   
   -- Hasil
   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> count(`testdb`.`t2`.`b`))

Skenario 2: Mengevaluasi sebelumnya subkueri [NOT] EXISTS

Jika pengoptimal menentukan bahwa subkueri [NOT] EXISTS selalu bernilai true (hasil tidak kosong) atau false (hasil kosong), klausa tersebut akan diganti dengan TRUE atau FALSE. Penggantian ini mencegah subkueri dieksekusi.

-- Set tidak kosong
SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);
-- Setelah optimasi
SELECT * FROM t1

-- Set kosong, klausa WHERE/HAVING=false, atau LIMIT 0
SELECT * FROM t1 WHERE EXISTS(SELECT max(a) FROM t2 HAVING 1=2 );
-- Setelah optimasi
SELECT * FROM t1 WHERE false

1. Persiapkan data uji:

   DROP TABLE IF EXISTS t1;
   DROP TABLE IF EXISTS t2;
   CREATE TABLE t1 (id INT);
   CREATE TABLE t2 (val INT);
   INSERT INTO t1 VALUES (1), (2);

2. Nonaktifkan optimasi dan jalankan kueri: Pertama, nonaktifkan fitur optimasi subkueri dalam session untuk melihat rencana eksekusi sebelum optimasi.

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);

   SHOW warnings;
   
   -- Hasil
   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1` where exists(/* select#2 */ select max(`testdb`.`t1`.`id`) from `testdb`.`t2`)

3. Aktifkan optimasi dan jalankan kueri: Sekarang, aktifkan fitur optimasi subkueri dalam session dan jalankan kueri yang sama.

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);

   SHOW warnings;
   
   -- Hasil
   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1`

Skenario 3: Menambahkan LIMIT 1 ke proyeksi konstan dalam subkueri ANY/ALL

Jika subkueri dalam klausa ANY atau ALL hanya mengambil nilai konstan tanpa melibatkan kolom apa pun, pengambilan beberapa baris identik menjadi tidak perlu. Pengoptimal secara otomatis menambahkan LIMIT 1 ke subkueri tersebut untuk menghindari pemindaian tabel penuh yang tidak perlu pada tabel dalam subkueri.

-- Sebelum optimasi
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2);
-- Setelah optimasi
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2 LIMIT 1);