Optimasi pemangkasan kolom untuk tabel turunan dan tampilan - PolarDB

Ketika suatu kueri hanya memilih beberapa kolom dari tabel turunan atau tampilan yang memiliki banyak kolom, performa dapat menurun karena kueri membaca dan memproses data yang tidak diperlukan. Masalah ini umum terjadi saat bekerja dengan tabel lebar atau menggunakan kembali tampilan umum berskala besar. Fitur pemangkasan kolom (column pruning) dari PolarDB for MySQL secara otomatis mengoptimalkan kueri tersebut. Selama fase penguraian (parsing) kueri, pengoptimal (optimizer) mengidentifikasi dan menghapus kolom-kolom dari tabel turunan atau tampilan yang tidak digunakan dalam kueri akhir. Hal ini secara signifikan mengurangi jumlah data yang dipindai, transfer jaringan, dan konsumsi memori, sekaligus menurunkan latensi kueri serta meningkatkan throughput sistem.

Cakupan Penerapan

Seri produk: Cluster Edition, Standard Edition.
Versi kernel: MySQL 8.0.2 dengan versi revisi 8.0.2.2.31.1 atau lebih baru.

Aktifkan pemangkasan kolom

Anda dapat mengontrol perilaku fitur optimasi ini dengan mengatur parameter loose_derived_table_pruning_mode.

Metode untuk mengubah parameter kluster PolarDB berbeda antara Konsol dan sesi database, yaitu sebagai berikut:

Di PolarDB console
- Kompatibilitas: Untuk kompatibilitas dengan berkas konfigurasi MySQL, beberapa parameter kluster di PolarDB console 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_derived_table_pruning_mode

Global/Sesi

Sakelar utama untuk fitur ini. Nilai yang valid adalah:

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

Cara kerja dan contoh

Inti dari optimasi pemangkasan kolom adalah penulisan ulang kueri (query rewrite). Selama fase optimasi logis suatu kueri, pengoptimal menganalisis seluruh pernyataan pencarian dan mengidentifikasi kolom-kolom dalam tabel turunan atau tampilan yang tidak direferensikan oleh daftar SELECT akhir, klausa WHERE, atau klausa JOIN. Jika suatu kolom bersifat redundan, pengoptimal menulis ulang kueri dalam (inner query), sehingga menghindari pembacaan dan pemrosesan data kolom tersebut di sumbernya serta mengurangi konsumsi I/O dan CPU.

Contoh 1: Optimalkan kueri tabel turunan

Contoh ini menunjukkan bagaimana pemangkasan kolom mengoptimalkan kueri pada tabel turunan.

-- Sebelum optimasi
SELECT c1 FROM (SELECT c1 FROM (SELECT c1, c2 FROM t1 GROUP BY c1, c2) v) t;
-- Setelah optimasi
SELECT c1 FROM (SELECT c1 FROM t1 GROUP BY c1, c2) v

Persiapkan data uji: Buat tabel bernama t1 dengan beberapa kolom di database Anda.

CREATE TABLE t1 (id INT PRIMARY KEY, c1 INT, c2 INT, c3 VARCHAR(100));
INSERT INTO t1 VALUES (1, 10, 100, 'data'), (2, 20, 200, 'data');

Jalankan kueri dengan optimasi dinonaktifkan: Pertama, nonaktifkan fitur pemangkasan kolom dalam sesi untuk melihat rencana eksekusi sebelum optimasi.

SET derived_table_pruning_mode = 'OFF';
EXPLAIN SELECT c1 FROM (SELECT c1 FROM (SELECT c1, c2 FROM t1 GROUP BY c1, c2) v) t;

SHOW warnings;

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                         |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `v`.`c1` AS `c1` from (/* select#3 */ select `testdb`.`t1`.`c1` AS `c1`,`testdb`.`t1`.`c2` AS `c2` from `testdb`.`t1` group by `testdb`.`t1`.`c1`,`testdb`.`t1`.`c2`) `v` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Jalankan kueri dengan optimasi diaktifkan: Sekarang, aktifkan fitur pemangkasan kolom dalam sesi dan jalankan kueri yang sama.

SET derived_table_pruning_mode = 'ON';
EXPLAIN SELECT c1 FROM (SELECT c1 FROM (SELECT c1, c2 FROM t1 GROUP BY c1, c2) v) t;

SHOW warnings;

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                              |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `v`.`c1` AS `c1` from (/* select#3 */ select `testdb`.`t1`.`c1` AS `c1` from `testdb`.`t1` group by `testdb`.`t1`.`c1`,`testdb`.`t1`.`c2`) `v` |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Interpretasikan rencana eksekusi: Rencana eksekusi berubah secara signifikan setelah optimasi. Pengoptimal mendeteksi bahwa kueri luar hanya memerlukan kolom c1, sehingga menulis ulang kueri tersebut.

Contoh 2: Optimalkan kueri tampilan

Pemangkasan kolom untuk tampilan mirip dengan yang dilakukan pada tabel turunan dan menghindari perhitungan yang tidak perlu.

CREATE VIEW v1 AS SELECT COUNT(*) AS a, (SELECT a FROM t2 WHERE a=FLOOR(COUNT(t1.a)/2)) AS s FROM t1;

-- Sebelum optimasi
SELECT a FROM v1;
-- Setelah optimasi
SELECT COUNT(*) AS a FROM t1;

Batasan optimasi

Optimasi pemangkasan kolom hanya dipicu jika kueri yang ditulis ulang dijamin ekuivalen secara semantik dengan pernyataan SQL aslinya. Kolom dalam tabel turunan atau tampilan yang tampaknya tidak direferensikan tidak akan dipangkas jika digunakan dalam salah satu operasi berikut:

Digunakan dalam klausa ORDER BY, GROUP BY, HAVING, atau DISTINCT.
Digunakan sebagai kunci partisi (PARTITION BY) atau kunci pengurutan (ORDER BY) dalam fungsi jendela (window function).
Pengoptimal dapat memangkas kolom yang dihasilkan oleh fungsi non-deterministik, seperti RAND() atau UUID(). Dalam kasus ini, fungsi tersebut tidak dieksekusi, sehingga menghindari beban komputasi yang tidak perlu. Perilaku ini merupakan efek optimasi yang disengaja, bukan keterbatasan.

FAQ

Apakah mengubah parameter derived_table_pruning_mode memerlukan restart kluster?

Tidak, tidak diperlukan. Parameter ini dapat diubah secara dinamis dan langsung berlaku tanpa perlu restart.

Mengapa performa kueri tidak meningkat setelah saya mengatur derived_table_pruning_mode=ON pada node primary?

Periksa skenario SQL: Pastikan pernyataan SQL Anda memenuhi kondisi untuk pemangkasan kolom. Kueri luar harus hanya menggunakan subset kolom dari tabel turunan atau tampilan dalam.
Lihat rencana eksekusi: Gunakan EXPLAIN untuk memastikan apakah optimasi telah terjadi. Jika tidak, lihat bagian Batasan optimasi untuk memeriksa skenario di mana pemangkasan tidak diterapkan.