Cara menghilangkan operasi JOIN yang tidak perlu dalam SQL - PolarDB

Operasi JOIN antar beberapa tabel sering menjadi bottleneck performa dalam kueri analitis kompleks. Fitur JOIN elimination di PolarDB for MySQL mengidentifikasi dan menghapus operasi JOIN yang tidak diperlukan selama fase optimisasi kueri, sehingga menyederhanakan rencana eksekusi, mengurangi I/O dan komputasi, serta meningkatkan performa kueri.

Skenario

Edisi produk: Cluster Edition dan Standard Edition.
Versi MySQL: MySQL 8.0.2, revisi 8.0.2.2.31.1 atau yang lebih baru.

Aktifkan JOIN elimination

Anda dapat mengatur parameter join_elimination_mode untuk mengontrol perilaku fitur optimisasi ini.

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

Di PolarDB console
- Kompatibilitas: Untuk kompatibilitas dengan file konfigurasi MySQL, beberapa parameter kluster di Konsol PolarDB memiliki awalan loose_.
- Prosedur: Temukan dan ubah parameter yang memiliki awalan loose_.
Di sesi database (menggunakan command line atau client)
- Prosedur: Saat menggunakan perintah SET untuk mengubah parameter dalam sesi database, hapus awalan loose_ dan gunakan nama parameter aslinya.

Parameter

Tingkat

Deskripsi

loose_join_elimination_mode

Global/Sesi

Mengontrol sakelar utama untuk fitur ini. Nilai yang valid adalah:

REPLICA_ON (default): Mengaktifkan fitur ini hanya pada node read-only (RO).
ON: Mengaktifkan fitur.
OFF: Menonaktifkan fitur.

Skema optimisasi

Fitur ini mendukung eliminasi JOIN otomatis dalam enam skenario berikut.

Skenario 1: Hilangkan LEFT JOIN dengan kondisi FALSE konstan

Ketika kondisi ON dari LEFT JOIN selalu bernilai FALSE, operasi join tersebut tidak memiliki arti. Pengoptimal menghapus tabel dalam (inner table) dan mengganti semua kolomnya dengan NULL.

-- Sebelum optimisasi
SELECT ..., ti1.*, ti2.*, ... FROM ... LEFT JOIN (ti1, ti2, ...) ON FALSE;
-- Setelah optimisasi
SELECT ..., NULL, NULL, ... FROM ...;

Kondisi untuk eliminasi

Kondisi ON dari LEFT JOIN merupakan ekspresi yang selalu bernilai FALSE, seperti 1=0 atau FALSE.

Contoh

Persiapkan lingkungan.

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);
INSERT INTO orders VALUES (1, 101, '2023-10-01');

Sebelum optimisasi: Nonaktifkan JOIN elimination dan jalankan kueri dengan ON FALSE.

SET SESSION join_elimination_mode = 'OFF';
EXPLAIN SELECT o.*, c.* FROM orders o LEFT JOIN customers c ON FALSE;

SHOW warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`o`.`order_id` AS `order_id`,`testdb`.`o`.`customer_id` AS `customer_id`,`testdb`.`o`.`order_date` AS `order_date`,`testdb`.`c`.`customer_id` AS `customer_id`,`testdb`.`c`.`customer_name` AS `customer_name` from `testdb`.`orders` `o` left join `testdb`.`customers` `c` on(false) where true |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Setelah optimisasi: Aktifkan JOIN elimination dan jalankan kueri yang sama.

SET SESSION join_elimination_mode = 'ON';
EXPLAIN SELECT o.*, c.* FROM orders o LEFT JOIN customers c ON FALSE;

SHOW warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                    |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`o`.`order_id` AS `order_id`,`testdb`.`o`.`customer_id` AS `customer_id`,`testdb`.`o`.`order_date` AS `order_date`,NULL AS `customer_id`,NULL AS `customer_name` from `testdb`.`orders` `o` |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Rencana eksekusi menunjukkan bahwa akses ke tabel customers telah dieliminasi.

Skenario 2: Hilangkan LEFT JOIN pada kunci unik yang tidak memengaruhi hasil

Jika tabel dalam (inner table) dari LEFT JOIN tidak direferensikan di bagian lain kueri dan operasi join tidak mengubah jumlah baris pada tabel luar (outer table), maka join terhadap tabel dalam tersebut dapat dihilangkan secara aman.

-- Sebelum optimisasi
SELECT to1.*, to2.*, ..., tom.* FROM to1, to2, ..., tom LEFT JOIN (ti1, ti2, ..., tin) ON cond_on WHERE cond_where ...
-- Setelah optimisasi
SELECT to1.*, to2.*, ..., tom.* FROM to1, to2, ..., tom WHERE cond_where ...

Kondisi untuk eliminasi

Tabel dalam tidak direferensikan: Tidak ada kolom dari tabel dalam (yaitu ti1, ti2, ..., tin) yang direferensikan di luar LEFT JOIN dan kondisi ON-nya.
Join tidak memengaruhi kardinalitas atau hasil tabel luar: Salah satu kondisi berikut harus terpenuhi.
- Join bersifat unik: Untuk setiap baris di tabel luar, LEFT JOIN mencocokkan tepat satu baris di tabel dalam.
- Join menghasilkan baris duplikat yang tidak memengaruhi hasil: Meskipun join menyebabkan duplikasi baris dari tabel luar, operasi selanjutnya dalam kueri menghilangkan efek duplikasi tersebut. Kasus umum meliputi:
  - Join berada dalam klausa EXISTS atau IN (suatu SEMI JOIN).
  - Subkueri mengandung kondisi seperti GROUP BY, LIMIT, atau window functions.
  - Penggunaan rekursif dari dua kondisi sebelumnya.

Skenario 3: Hilangkan self-join

Ketika sebuah tabel dasar (atau tabel turunannya) di-join dengan dirinya sendiri menggunakan INNER JOIN, hal ini setara dengan mencocokkan dua dataset identik atau mirip. Jika kondisi tertentu terpenuhi, pengoptimal dapat mengidentifikasi salah satu dataset sebagai redundan dan mengeliminasinya dari rencana eksekusi untuk menghindari pembacaan data yang sama dua kali.

Tabel dasar di-join dengan tabel dasar:

-- Sebelum optimisasi
SELECT target.*, source.* FROM t1 as target JOIN t1 as source WHERE target.uk = source.uk;
-- Setelah optimisasi
SELECT source.*, source.* FROM source WHERE source.uk = source.uk;

Tabel turunan di-join dengan tabel turunan:

-- Sebelum optimisasi
SELECT target.*, source.* FROM (SELECT * FROM t1) target JOIN (SELECT * FROM t1 WHERE t1.a > 1) source WHERE target.uk = source.uk;
-- Setelah optimisasi
SELECT source.*, source.* FROM (SELECT * FROM t1 WHERE t1.a > 1) source WHERE source.uk = source.uk;

Tabel dasar di-join dengan tabel turunan:

-- Sebelum optimisasi
SELECT target.*, source.* FROM t1 target JOIN (SELECT * FROM t1 WHERE t1.a > 1) source WHERE target.uk = source.uk;
-- Setelah optimisasi
SELECT source.*, source.* FROM (SELECT * FROM t1 WHERE t1.a > 1) source WHERE source.uk = source.uk;

Kondisi untuk eliminasi

Hubungan subset: Set hasil dari tabel source harus merupakan subset dari set hasil tabel target.
Ketersediaan kolom: Semua kolom yang direferensikan dari tabel target juga harus tersedia di tabel source. Hal ini memastikan bahwa tabel source yang dipertahankan berisi semua kolom yang dibutuhkan oleh kueri.
Join berdasarkan kunci unik: Kondisi JOIN harus berupa perbandingan kesetaraan berdasarkan kunci unik atau primary key pada tabel target.
- Kondisi ini sangat penting karena menjamin bahwa setiap baris di tabel source cocok dengan paling banyak satu baris di tabel target. Hal ini memastikan bahwa mengeliminasi tabel target tidak mengubah jumlah baris atau hasil kueri.
- Kasus khusus: Jika kondisi join tidak memiliki perbandingan kesetaraan berdasarkan kunci unik, eliminasi hanya mungkin jika set hasil dari tabel target itu sendiri berisi nol atau satu baris.

Skenario 4: Hilangkan self-semi-join

Ketika suatu tabel di-semi-join dengan dirinya sendiri (suatu SEMI JOIN, yang biasanya dinyatakan sebagai klausa IN atau EXISTS), jika kondisi tertentu terpenuhi, tabel dalam subkueri dapat dieliminasi dan kondisinya digabungkan ke dalam kueri luar.

Dengan kondisi kesetaraan kolom unik.

-- Sebelum optimisasi
SELECT source.* FROM t1 as source WHERE EXISTS (SELECT * FROM t1 as target WHERE source.uk = target.uk AND target.a > 1);
-- Setelah optimisasi
SELECT source.* FROM t1 as source WHERE source.uk = source.uk AND source.a > 1;

Tanpa kondisi kesetaraan kolom unik.

-- Sebelum optimisasi
SELECT source.* FROM t1 as source WHERE EXISTS (SELECT * FROM t1 as target WHERE source.a = target.a);
-- Setelah optimisasi
SELECT source.* FROM t1 as source WHERE source.a = source.a;

Kondisi untuk eliminasi

Hubungan subset: Set hasil dari tabel source harus merupakan subset dari set hasil tabel target.
Ketersediaan kolom: Semua kolom yang direferensikan dari tabel target juga harus tersedia di tabel source. Hal ini memastikan bahwa tabel source yang dipertahankan berisi semua kolom yang dibutuhkan oleh kueri.
Selain itu, salah satu kondisi berikut harus terpenuhi:
- Join berdasarkan kunci unik: Kondisi JOIN harus berupa perbandingan kesetaraan berdasarkan kunci unik atau primary key pada tabel target.
  - Kondisi ini sangat penting karena menjamin bahwa setiap baris di tabel source cocok dengan paling banyak satu baris di tabel target. Hal ini memastikan bahwa mengeliminasi tabel target tidak mengubah jumlah baris atau hasil kueri.
  - Kasus khusus: Jika kondisi join tidak memiliki perbandingan kesetaraan berdasarkan kunci unik, eliminasi hanya mungkin jika set hasil dari tabel target itu sendiri berisi nol atau satu baris.
- Semua kondisi join merupakan join kesetaraan (yaitu, $t a r g e t . c_{a_{k}} = so u rce . c_{i_{a_{k}}}^{^{'}}$ ) dan dihubungkan dengan kondisi lain menggunakan AND.

Skenario 5: Hilangkan join kunci asing

Jika terdapat kendala kunci asing antara dua tabel, join terhadap tabel induk dapat dieliminasi ketika kondisi JOIN didasarkan pada hubungan kunci asing tersebut dan kueri hanya mereferensikan kolom dari tabel anak (tabel yang memiliki kunci asing). Hal ini karena kendala kunci asing menjamin bahwa setiap baris di tabel anak memiliki baris yang sesuai di tabel induk.

CREATE TABLE target (a int primary key);
CREATE TABLE source (a int, foreign key (a) references target(a));

-- Sebelum optimisasi
SELECT target.a, source.a FROM target, source WHERE target.a = source.a;
-- Setelah optimisasi
SELECT source.a, source.a FROM source WHERE source.a = source.a;

Kondisi untuk eliminasi

Referensi tabel induk dapat digantikan: Semua kolom yang direferensikan dari tabel induk dapat digantikan dengan kolom kunci asing yang sesuai di tabel anak. Biasanya ini berarti hanya kunci gabungan dari tabel induk yang direferensikan.
Kondisi join didasarkan pada hubungan kunci asing: Kondisi JOIN merupakan perbandingan kesetaraan antara kolom kunci asing tabel anak dan kolom primary key atau kunci unik tabel induk.
Kunci asing tidak null: Kolom kunci asing di tabel anak didefinisikan sebagai NOT NULL. Hal ini menjamin bahwa setiap baris di tabel anak memiliki baris yang sesuai di tabel induk.

Skenario 6: Hilangkan semi-join kunci asing

Ketika klausa EXISTS atau IN digunakan untuk memeriksa keberadaan catatan yang sesuai di tabel induk berdasarkan hubungan kunci asing, pemeriksaan ini bersifat redundan. Kendala kunci asing sudah menjamin bahwa catatan tersebut ada. Oleh karena itu, subkueri SEMI JOIN dapat dieliminasi.

CREATE TABLE target (a int primary key);
CREATE TABLE source (a int, foreign key (a) references target(a));

-- Sebelum optimisasi
SELECT source.a FROM source WHERE EXISTS(SELECT * FROM target WHERE target.a = source.a);
-- Setelah optimisasi
SELECT source.a FROM source WHERE source.a = source.a;

Kondisi untuk eliminasi

Referensi tabel induk dapat digantikan: Semua kolom yang direferensikan dari tabel induk dapat digantikan dengan kolom kunci asing yang sesuai di tabel anak. Biasanya ini berarti hanya kunci gabungan dari tabel induk yang direferensikan.
Kondisi join didasarkan pada hubungan kunci asing: Kondisi JOIN merupakan perbandingan kesetaraan antara kolom kunci asing tabel anak dan kolom primary key atau kunci unik tabel induk.
Kunci asing tidak null: Kolom kunci asing di tabel anak didefinisikan sebagai NOT NULL. Hal ini menjamin bahwa setiap baris di tabel anak memiliki baris yang sesuai di tabel induk.

FAQ

Mengapa kueri tidak dioptimalkan setelah join_elimination_mod diaktifkan?

Lakukan pemecahan masalah sebagai berikut:

Periksa versi: Pastikan versi kluster Anda memenuhi persyaratan yang ditentukan di Ketersediaan.
Periksa skenario: Pastikan pernyataan SQL dan skema tabel Anda memenuhi kondisi eliminasi untuk salah satu dari enam skenario optimisasi yang dijelaskan dalam dokumen ini.
Periksa daftar SELECT: Untuk skenario seperti LEFT JOIN dan FOREIGN KEY JOIN, pastikan daftar SELECT kueri tidak mereferensikan kolom apa pun dari tabel yang akan dieliminasi.
Periksa kompleksitas kueri: Beberapa struktur kueri kompleks, seperti subkueri bersarang tertentu, mungkin mencegah pengoptimal melakukan eliminasi. Coba sederhanakan kueri untuk pengujian.

Apakah fitur ini memengaruhi konsistensi data atau kebenaran hasil kueri?

Tidak. JOIN elimination merupakan optimisasi logis yang hanya dilakukan ketika hasil kueri dijamin ekuivalen. Fitur ini hanya mengubah cara kueri dieksekusi, bukan semantik atau hasil kuerinya. Semua optimisasi didasarkan pada pemeriksaan kondisi ketat untuk memastikan hasil yang benar.