Optimasi Query Kompleks dan Indexing Tingkat Lanjut pada MariaDB

1. Menu Kita Hari Ini & Alat yang Perlu Disiapkan

Pernahkah Anda menatap layar monitor dengan cemas saat query SQL laporan penjualan e-commerce kesayangan Anda membutuhkan waktu 15 detik penuh hanya untuk menampilkan rekap bulanan? Kemarin malam, saya mendapati klien saya mengalami hal persis seperti itu—sebuah query agregasi penjualan yang melibatkan jutaan baris data mendadak membuat CPU MariaDB melonjak hingga 100% dan memicu antrean panjang di log Nginx-PHP-FPM. Dalam 15 menit ke depan, kita akan merakit strategi optimasi kelas enterprise di atas meja kerja kita, memangkas waktu eksekusi yang tadinya 15 detik menjadi di bawah 100ms menggunakan teknik Composite Index Strategy, pembacaan Execution Plan yang presisi, hingga manipulasi Query Rewriting and Partitioning tanpa harus merombak seluruh arsitektur backend Go atau PHP Anda.

Sebelum kita menyalakan mesin dapur, pastikan Anda telah menyiapkan pisau dan talenan digital kita: sebuah instance MariaDB 10.5+, akses root via terminal Linux (Ubuntu/Debian), klien database favorit seperti DBeaver atau HeidiSQL, serta secangkir kopi hitam hangat karena kita akan menyelami jeroan mesin penyimpanan InnoDB secara langsung.

2. Peta Jalan Singkat: Apa yang Sedang Kita Rakit

Perjalanan kita tidak akan berputar-putar pada teori normalisasi database yang membosankan di bangku kuliah. Kita akan langsung membedah pasien. Pertama, kita akan membedah anatomi query lambat menggunakan instrumen EXPLAIN FORMAT=JSON untuk melihat di mana letak dosa-dosa masa lalu dari query tersebut. Setelah itu, kita masuk ke dapur utama untuk meracik Composite Index yang mematuhi Leftmost Prefix Rule, memastikan mesin penyimpanan tidak lagi melakukan Full Table Scan yang melelahkan.

[!TIP] Jangan pernah membuat index secara asal-asalan hanya karena sebuah kolom sering dipanggil. Setiap index yang Anda buat adalah beban tambahan saat operasi INSERT dan UPDATE dijalankan oleh backend worker Anda.

Sebagai penutup babak, kita akan memecah tabel raksasa dengan teknik Range Partitioning dan menata ulang struktur query agar mesin optimizer MariaDB langsung jatuh cinta pada pandangan pertama, mengantar kita mulus ke sesi pengujian performa akhir.

3. Langkah 1: Mempersiapkan Fondasi & Environment

Mari kita mulai dengan melompat ke terminal server dan memastikan lingkungan kita steril dan siap diajak kompromi. Kita ambil contoh tabel orders yang berisi 15 juta baris data transaksi. Mari kita intip struktur dasarnya dengan menjalankan perintah SQL berikut melalui klien MariaDB Anda:

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(32) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_customer (customer_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

[!WARNING] Perhatikan bahwa tabel di atas memiliki index terpisah (idx_customer dan idx_created). Bagi mata awam, ini terlihat bagus. Namun bagi MariaDB Optimizer, ketika Anda menjalankan query yang memfilter berdasarkan kedua kolom tersebut secara bersamaan, database seringkali kebingungan memilih index mana yang harus dipakai atau malah berujung pada Index Merge yang lambat. Dari fondasi inilah masalah 15 detik itu bermula.

4. Langkah 2: Meracik Konfigurasi Inti Tanpa Basa-basi

Sekarang kita masuk ke inti resep: Composite Index Strategy. Kita ingin memangkas query laporan penjualan yang biasanya terlihat seperti ini:

SELECT store_id, SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed'
  AND created_at >= '2023-01-01 00:00:00'
  AND created_at <= '2023-12-31 23:59:59'
GROUP BY store_id;

Jika Anda menjalankan EXPLAIN pada query di atas dengan index bawaan kita sebelumnya, MariaDB pasti akan menyerah dan melakukan Full Table Scan. Mari kita racik index gabungan yang sempurna berdasarkan urutan kolom penyaringan (equality first, range second):

ALTER TABLE orders DROP INDEX idx_created;
CREATE INDEX idx_composite_report ON orders (status, created_at, store_id, total_amount);

Perhatikan urutan kolom di atas: status (pencarian nilai pasti/equality), created_at (rentang waktu/range), diikuti oleh store_id dan total_amount yang masuk dalam Covering Index. Dengan struktur ini, seluruh data yang dibutuhkan oleh query laporan tersaji langsung di dalam index tree tanpa perlu repot-repot mengakses clustered index di data fisik disk.

Mari kita verifikasi racikan kita dengan mengecek rencana eksekusinya:

EXPLAIN FORMAT=JSON
SELECT store_id, SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed'
  AND created_at >= '2023-01-01 00:00:00'
  AND created_at <= '2023-12-31 23:59:59'
GROUP BY store_id;

Jika output JSON menampilkan "using_index": true atau "access_type": "range" dengan memanfaatkan idx_composite_report, Anda baru saja menyelamatkan server dari lonjakan beban yang sia-sia. Dan saat query ini dieksekusi, rasakan perubahannya: dari 15 detik yang menegangkan menjadi kilatan data instan di bawah 100ms.

5. Langkah 3: Sentuhan Akhir Keamanan & Optimasi

Bagaimana jika data sudah mencapai ratusan juta baris dan index saja tidak lagi cukup? Di sinilah kita memerlukan pisau bedah tingkat lanjut: Query Rewriting and Partitioning. Terkadang, fungsi seperti DATE(created_at) di dalam klausa WHERE menghancurkan kemampuan MariaDB memanfaatkan index.

Ubah kebiasaan buruk penulisan query:

-- SALAH: Menghancurkan fungsi index
SELECT * FROM orders WHERE DATE(created_at) = '2023-10-10';

-- BENAR: Ramah Index Optimizer
SELECT * FROM orders 
WHERE created_at >= '2023-10-10 00:00:00' 
  AND created_at < '2023-10-11 00:00:00';

Selain itu, untuk tabel historis raksasa, terapkan Range Partitioning berbasis tahun pada level penyimpanan InnoDB agar MariaDB bisa melakukan Partition Pruning (mengabaikan partisi bulan/tahun lain yang tidak relevan secara fisik):

ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Konfigurasi ini memastikan mesin penyimpanan kita bekerja layaknya arsiparis profesional yang langsung mengambil map laporan di rak tahun tertentu tanpa harus menggeledah seluruh gudang.

6. Tes Rasa: Memastikan Semuanya Berjalan Sempurna

Saatnya melakukan uji coba nyata di lingkungan staging atau produksi berbeban rendah. Buka terminal MariaDB Anda, aktifkan profil eksekusi, lalu jalankan query laporan maut kita tadi:

SET profiling = 1;

SELECT store_id, SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed'
  AND created_at >= '2023-01-01 00:00:00'
  AND created_at <= '2023-12-31 23:59:59'
GROUP BY store_id;

SHOW PROFILES;

Lihat kolom Duration. Angka yang tadinya berada di kisaran 15.420000 detik kini menyusut drastis menjadi 0.043000 detik. Jika angka ini sudah terpampang manis di layar Anda, silakan bersandar, regangkan bahu, dan nikmati sisa kopi Anda karena optimasi infrastruktur database kita kali ini sukses besar.

7. Pertolongan Pertama: Jika Terjadi Kendala Kecil

Terногда, meskipun racikan index sudah pas, MariaDB optimizer tetap keras kepala menggunakan Full Table Scan. Jangan panik. Ini biasanya terjadi karena statistik tabel yang sudah kedaluwarsa akibat operasi DELETE atau UPDATE massal.

Cukup jalankan perintah penyegaran statistik berikut:

ANALYZE TABLE orders;

Jika itu belum cukup mempan, Anda bisa memaksa optimizer menggunakan index spesifik kita secara eksplisit menggunakan Index Hint:

SELECT store_id, SUM(total_amount) as revenue
FROM orders USE INDEX (idx_composite_report)
WHERE status = 'completed'
  AND created_at >= '2023-01-01 00:00:00'
  AND created_at <= '2023-12-31 23:59:59'
GROUP BY store_id;

Simpan trik ini di saku celana Anda, dan sampai jumpa di resep infrastruktur skala besar berikutnya!