Cara menghilangkan operasi DISTINCT yang redundan dalam SQL - PolarDB

Ketika kueri SQL mengandung operator DISTINCT, database menghapus baris duplikat melalui pengurutan atau hashing. Operasi ini dapat mengonsumsi sumber daya CPU dan memori secara signifikan, terutama pada dataset besar. Dalam banyak skenario, hasil kueri sudah unik—misalnya ketika daftar SELECT mencakup primary key. Dalam kasus seperti ini, operasi DISTINCT bersifat redundan. Pengoptimal di PolarDB for MySQL mengidentifikasi dan menghilangkan operasi DISTINCT yang redundan dengan menganalisis ketergantungan fungsional dalam skema tabel dan kondisi kueri. Fitur ini mengurangi perhitungan yang tidak perlu sehingga menurunkan waktu eksekusi, penggunaan CPU, dan penggunaan memori kueri.

Cara kerja

Inti dari optimasi ini adalah konsep ketergantungan fungsional (functional dependency), yaitu nilai-nilai dalam satu atau beberapa kolom tabel secara unik menentukan nilai-nilai dalam satu atau beberapa kolom lainnya. Sebagai contoh, primary key suatu tabel secara unik menentukan semua kolom lain dalam tabel tersebut.

Saat menganalisis kueri yang mengandung DISTINCT, pengoptimal menggunakan metadata tabel—seperti primary key dan unique index—serta kondisi kueri untuk menyimpulkan keunikan. Jika pengoptimal menentukan bahwa kombinasi kolom dalam kolom proyeksi (kolom SELECT) sudah menjamin hasil yang unik, ia menghapus operator DISTINCT dari rencana eksekusi.

Penerapan

Versi kluster:
- Seri produk: Cluster Edition, Standard Edition.
- Versi kernel: MySQL 8.0.2, revisi 8.0.2.2.31.1 atau lebih baru.
Cakupan fitur:
- Kolom proyeksi berisi kunci unik: Ketika kolom-kolom dalam pernyataan SELECT mencakup kunci unik tabel (primary key atau semua kolom yang membentuk unique index), set hasil dijamin unik.
```
-- Asumsikan user_id adalah primary key tabel users
SELECT DISTINCT user_id, user_name FROM users; -- DISTINCT dapat dihilangkan
```
- Kolom proyeksi bergantung secara fungsional pada kunci unik: Ketika semua kolom dalam pernyataan SELECT secara unik ditentukan oleh kunci unik, hasil kueri juga unik. Hal ini biasanya terjadi pada kueri satu tabel atau pada join one-to-one atau many-to-one dengan tabel fakta.
```
-- Asumsikan user_id adalah primary key, dan (user_name, email) secara unik ditentukan oleh user_id
SELECT DISTINCT user_name, email FROM users WHERE user_id = 123; -- DISTINCT dapat dihilangkan
```
- Semua kolom proyeksi adalah konstanta: Ketika semua kolom dalam pernyataan SELECT berupa konstanta, set hasil berisi paling banyak satu baris. Dalam kasus ini, penggunaan DISTINCT bersifat redundan. Pengoptimal menghapus DISTINCT dan menambahkan LIMIT 1.
```
SELECT DISTINCT 'hello', 123 FROM t1 WHERE a = 1; -- DISTINCT dapat dihilangkan dan LIMIT 1 ditambahkan
```
- Kueri UNION DISTINCT: Dalam kueri UNION DISTINCT, jika set hasil dari setiap pernyataan SELECT di dalam UNION sudah unik, pengoptimal dapat menghilangkan operasi DISTINCT pada pernyataan SELECT internal tersebut.
Catatan
Pengoptimal secara ketat mempertimbangkan kendala NOT NULL saat menyimpulkan keunikan. Jika suatu kolom dalam kunci unik dapat bernilai NULL, hal ini dapat memengaruhi penentuan keunikan dan mencegah pengoptimal menghilangkan DISTINCT.

Aktifkan fitur penghapusan DISTINCT

Atur parameter distinct_elimination_mode untuk mengontrol perilaku fitur optimasi 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_.
Dalam 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_distinct_elimination_mode

Global/Sesi

Saklar 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.

Contoh skenario optimasi

Untuk memverifikasi bahwa fitur penghapusan DISTINCT telah diaktifkan, Anda dapat menggunakan perintah EXPLAIN untuk melihat rencana eksekusi kueri. Jika optimasi efektif, rencana eksekusi tidak mengandung operator untuk pemrosesan DISTINCT, seperti sorting atau deduplikasi berbasis hash. Bagian-bagian berikut menjelaskan skenario optimasi khas.

Skenario 1: Kolom proyeksi berupa konstanta

Ketika pengoptimal mengidentifikasi bahwa kolom proyeksi berupa konstanta, ia dapat menghilangkan DISTINCT dan menambahkan LIMIT 1.

Persiapkan data: Buat tabel t1 dan masukkan data.

CREATE TABLE testdb.t1 (a INT, b INT);
INSERT INTO testdb.t1 VALUES (1, 10), (1, 20);

Jalankan kueri asli:

EXPLAIN SELECT DISTINCT a + 1 FROM testdb.t1 WHERE a = 1;

Verifikasi optimasi: Dalam rencana eksekusi yang dioptimalkan, periksa informasi di warnings. Operasi DISTINCT dihilangkan.

SHOW warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select (`testdb`.`t1`.`a` + 1) AS `a + 1` from `testdb`.`t1` where (`testdb`.`t1`.`a` = 1) limit 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------+

Skenario 2: Keunikan disimpulkan dari ketergantungan fungsional

Ketika kolom a secara unik menentukan kolom b, dan b merupakan kunci unik non-NULL, pengoptimal dapat menyimpulkan bahwa a juga unik. Hal ini memungkinkannya menghilangkan DISTINCT.

Persiapkan data: Buat tabel t2, di mana kolom b dihasilkan dari kolom a dan bersifat unik.

CREATE TABLE t2 (
  a INT,
  b INT AS (a + 1) UNIQUE
);
INSERT INTO t2(a) VALUES (10), (20);

Eksekusi kueri mentah:

EXPLAIN SELECT DISTINCT a FROM t2 WHERE b IS NOT NULL;

Verifikasi optimasi: Karena keunikan b menjamin keunikan a dalam hasil, DISTINCT dihapus. Periksa informasi di warnings:

SHOW warnings;
+-------+------+---------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`t2`.`a` AS `a` from `testdb`.`t2` where (`testdb`.`t2`.`b` is not null) |
+-------+------+---------------------------------------------------------------------------------------------------------+

Skenario 3: Hasil subkueri secara alami unik

Ketika subkueri dt secara alami unik karena mengandung kunci unik c, keunikan hasil dapat dipertahankan bahkan setelah operasi JOIN. Hal ini memungkinkan pengoptimal menghilangkan DISTINCT luar.

Persiapkan data: Buat tabel t3, di mana kolom c dihasilkan dari a dan b serta bersifat unik.

CREATE TABLE t3 (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT AS (a + b) UNIQUE
);
INSERT INTO t3(a, b) VALUES (1, 10), (2, 20);

Jalankan kueri asli:

EXPLAIN SELECT DISTINCT a, b FROM (SELECT a, b, c FROM t3) dt, (SELECT a AS d FROM t3 limit 1) dt2 WHERE c IS NOT NULL;

Verifikasi optimasi: Dalam rencana eksekusi yang dioptimalkan, periksa informasi di warnings. Operasi DISTINCT dihilangkan.

SHOW warnings;                                                                                                 
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`t3`.`a` AS `a`,`testdb`.`t3`.`b` AS `b` from `testdb`.`t3` where (`testdb`.`t3`.`c` is not null) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+

Kinerja

Setelah Anda mengaktifkan fitur penghapusan DISTINCT, peningkatan kinerja signifikan dapat diamati dalam skenario khas:

Metrik	Peningkatan kinerja
Waktu eksekusi	Berkurang 10% hingga 60%.
Penggunaan memori	Berkurang 20% hingga 70%.
Pemanfaatan CPU	Mengurangi beban CPU dari operasi hashing atau sorting.