Query lambat jarang datang sendiri-sendiri: aplikasi bertumbuh, data menumpuk, dan suatu hari satu endpoint terasa berat — lalu dua, lalu sepuluh. Saat seperti itu kita butuh prosedur, bukan insting. Optimasi yang didasari tebakan sering salah sasaran: index ditambah di kolom yang tak pernah difilter, query dirombak tanpa tahu bagiannya yang lambat, dan server di-upgrade padahal biayanya di satu filesort. Chapter ini menyusun proses diagnosis lima langkah yang bisa diulang untuk setiap keluhan performa, lalu mengisi lembar referensinya: katalog pola penyakit yang paling sering ditemui di lapangan, masing-masing dengan tanda tangannya di EXPLAIN.
Menyiapkan Data Latihan #
Dua tabel berelasi — pesanan sengaja belum ber-index di kolom pelanggan_id karena join tanpa index menjadi salah satu pasien kita:
DROP DATABASE IF EXISTS belajar_opt;
CREATE DATABASE belajar_opt;
USE belajar_opt;Output yang Dihasilkan:
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changedCREATE TABLE pelanggan (
id INT AUTO_INCREMENT PRIMARY KEY,
nama VARCHAR(60) NOT NULL,
kota VARCHAR(40) NOT NULL,
email VARCHAR(100) NOT NULL,
tanggal_daftar DATE NOT NULL
);
INSERT INTO pelanggan (nama, kota, email, tanggal_daftar) VALUES
('Budi Santoso', 'Jakarta', '[email protected]', '2026-01-05'),
('Siti Rahayu', 'Bandung', '[email protected]', '2026-01-12'),
('Andi Wijaya', 'Surabaya', '[email protected]', '2026-02-01'),
('Rina Kartika', 'Jakarta', '[email protected]', '2026-02-10'),
('Dewi Lestari', 'Yogyakarta', '[email protected]', '2026-03-03'),
('Tono Sutrisno', 'Bandung', '[email protected]', '2026-03-15'),
('Sari Wulandari', 'Medan', '[email protected]', '2026-04-01'),
('Joko Pratama', 'Jakarta', '[email protected]', '2026-04-20'),
('Nurul Hidayah', 'Surabaya', '[email protected]', '2026-05-05'),
('Rizky Maulana', 'Medan', '[email protected]', '2026-05-18');
CREATE INDEX idx_kota ON pelanggan(kota);
CREATE TABLE pesanan (
id INT AUTO_INCREMENT PRIMARY KEY,
pelanggan_id INT NOT NULL,
tanggal DATE NOT NULL,
total DECIMAL(12,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO pesanan (pelanggan_id, tanggal, total, status) VALUES
(1, '2026-01-10', 150000.00, 'selesai'),
(2, '2026-01-15', 250000.00, 'selesai'),
(3, '2026-01-20', 75000.00, 'batal'),
(1, '2026-02-05', 320000.00, 'selesai'),
(4, '2026-02-12', 125000.00, 'diproses'),
(5, '2026-02-18', 480000.00, 'selesai'),
(2, '2026-03-01', 210000.00, 'selesai'),
(6, '2026-03-08', 95000.00, 'batal'),
(1, '2026-03-15', 540000.00, 'diproses'),
(7, '2026-03-22', 175000.00, 'selesai'),
(8, '2026-04-02', 260000.00, 'selesai'),
(3, '2026-04-09', 310000.00, 'selesai'),
(9, '2026-04-16', 88000.00, 'batal'),
(4, '2026-04-25', 430000.00, 'selesai'),
(10, '2026-05-01', 365000.00, 'selesai');
CREATE INDEX idx_tanggal ON pesanan(tanggal);Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 15 rows affected (0.00 sec)
Records: 15 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0Alur Diagnosis Lima Langkah #
Prosedurnya sederhana tapi disiplin: temukan dulu query yang benar-benar lambat, pahami plannya, bentuk hipotesis, perbaiki satu hal, lalu buktikan dengan pengukuran ulang. Setiap langkah punya alatnya masing-masing:
Langkah pertama — identifikasi — jangan dilakukan lewat perasaan. MySQL menyediakan slow query log yang mencatat query yang melewati batas waktu long_query_time:
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';Output yang Dihasilkan:
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)Secara bawaan log ini mati dan batas waktunya 10 detik — terlalu longgar untuk aplikasi web, sehingga praktik umum menyalakannya dengan SET GLOBAL dan menurunkan batas ke misalnya 0.1 detik di server produksi. Pengaturan lengkapnya — lokasi file, format, cara membacanya — kita bahas tuntas di chapter slow query log pada section administrasi; alat baca favorit para DBA untuk hasil log-nya adalah mysqldumpslow yang mengelompokkan query mirip menjadi satu statistik.
Katalog Pola Penyakit Umum #
Sisanya adalah mengenali pola. Tujuh penyakit berikut menutup mayoritas kasus query lambat yang beredar di produksi.
SELECT * Raksasa #
SELECT * pada tabel lebar mengirim semua kolom ke aplikasi dan menutup pintu covering index — kita membuktikan keduanya di chapter Join Optimization dan Index Optimization. Versi terparahnya: SELECT * di join, yang menggandakan lebar baris dengan kolom kedua tabel. Obatnya menuliskan kolom yang benar-benar dipakai — dan biarkan Extra: Using index menjadi hadiahnya.
N+1 Query dari Aplikasi #
Penyakit yang tak terlihat dari satu query, karena letaknya di pola pemanggilan: aplikasi menjalankan satu query daftar, lalu satu query tambahan per baris hasilnya:
-- @notest
-- Ilustrasi pola N+1 dari sisi aplikasi (kode Python semu)
-- for p in ambil("SELECT id, nama FROM pelanggan"):
-- total = ambil("SELECT SUM(total) FROM pesanan WHERE pelanggan_id = " + p["id"])Sepuluh pelanggan berarti sebelas query; seribu pelanggan berarti seribu satu — masing-masing dengan biaya round-trip jaringan dan parse. Querynya masing-masing cepat, plannya mungkin sehat, slow log bahkan tidak mencurigai mereka satu per satu. Obatnya menggabungkan menjadi satu query beragregasi: SELECT p.id, p.nama, SUM(o.total) ... JOIN ... GROUP BY — pola yang sudah kita kuasai di section agregasi — atau memakai JOIN saat menampilkan detail per baris.
Subquery Correlated yang Berat #
Subquery yang merujuk kolom query luar (correlated) dieksekusi ulang untuk setiap baris luar — plan-nya bertanda DEPENDENT SUBQUERY:
EXPLAIN SELECT o.id, o.pelanggan_id, o.total
FROM pesanan o
WHERE o.total > (
SELECT AVG(o2.total) FROM pesanan o2
WHERE o2.pelanggan_id = o.pelanggan_id
);Output yang Dihasilkan:
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | o2 | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)Baris DEPENDENT SUBQUERY adalah alarmnya: subquery rata-rata itu dihitung ulang untuk setiap pesanan luar — dan karena pelanggan_id belum ber-index di tabel ini, setiap penghitungan menyapu seluruh 15 baris pesanan (type = ALL di baris o2). Lima belas pesanan luar × lima belas baris per perhitungan = 225 pemeriksaan, demi menjawab pertanyaan yang sebenarnya bisa dihitung sekali. Obat klasiknya menulis ulang sebagai join agregat — tabel turunan yang menghitung AVG(total) sekali per pelanggan lalu di-join ke query luar — sehingga pekerjaan berat hanya terjadi satu kali.
Fungsi pada Kolom di WHERE #
Pola yang sudah kita operasikan di chapter Menghindari Full Table Scan — YEAR(kolom) = 2026 memaksa full scan walaupun kolomnya ber-index. Tanda tangannya: possible_keys kosong atau diabaikan, type = ALL. Obatnya rentang pada kolom mentah, atau index di atas generated column bila fungsi memang tak terhindarkan.
Join Tanpa Index di Kolom Join #
Pasien paling produktif hari ini — join pesanan–pelanggan dengan kolom relasi yang telanjang:
EXPLAIN SELECT o.id, p.nama, o.total
FROM pesanan o
JOIN pelanggan p ON p.id = o.pelanggan_id;Output yang Dihasilkan:
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | NULL |
| 1 | SIMPLE | p | NULL | eq_ref | PRIMARY | PRIMARY | 4 | belajar_opt.o.pelanggan_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)Plan tanpa index di pelanggan_id memilih pesanan sebagai driving table dan menyapu 15 barisnya, namun perhatikan kolom Extra di baris p… tidak ada join buffer? Memang tidak — karena sisi p dicari lewat PRIMARY yang selalu ada. Biayanya masih oke: 15 lookup eq_ref. Tanda bahaya Using join buffer (hash join) baru muncul bila sisi kedua yang telanjang — kita sudah membuktikannya di chapter Join Optimization dengan STRAIGHT_JOIN dan index yang dicopot. Di produksi, arah join tidak kita kendalikan sepenuhnya, jadi kolom relasi sebaiknya selalu ber-index; kita tambahkan sekarang dan bandingkan:
CREATE INDEX idx_pelanggan ON pesanan(pelanggan_id);
EXPLAIN SELECT STRAIGHT_JOIN p.nama, o.total
FROM pelanggan p
JOIN pesanan o ON p.id = o.pelanggan_id;Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------+------+----------+-------+
| 1 | SIMPLE | p | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ref | idx_pelanggan | idx_pelanggan | 4 | belajar_opt.p.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)Dengan idx_pelanggan, urutan manapun aman: sisi pesanan dicari lewat ref, bukan menyapu tabel — pelanggan pertama langsung menemukan pesanan-pesananannya.
ORDER BY Tanpa Index Plus LIMIT #
Pengurutan yang tak bisa dilayani index dihitung di memori/disk — tanda tangannya Using filesort — dan LIMIT tidak otomatis memperingannya:
EXPLAIN SELECT id, total FROM pesanan ORDER BY total DESC LIMIT 3;Output yang Dihasilkan:
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | pesanan | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)type = ALL plus Using filesort: seluruh 15 baris dibaca lalu diurutkan penuh demi 3 teratas. LIMIT membantu setelah pengurutan selesai — tidak sebelumnya. Obatnya index di kolom pengurutan:
CREATE INDEX idx_total ON pesanan(total);
EXPLAIN SELECT id, total FROM pesanan ORDER BY total DESC LIMIT 3;Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | pesanan | NULL | index | NULL | idx_total | 6 | NULL | 3 | 100.00 | Backward index scan; Using index |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)Dengan idx_total, filesort menghilang dari Extra: plan membaca index dari ujung besar — label Backward index scan — berhenti setelah 3 baris (rows = 3), dan Using index menandakan query sekaligus covering. Pekerjaan jadi konstan: tiga baris dibaca, tidak peduli tabelnya berisi 15 atau 15 juta baris.
GROUP BY dengan Tabel Sementara #
Agregasi per kelompok yang tak terurut oleh index membangun tabel sementara — Extra: Using temporary:
EXPLAIN SELECT status, COUNT(*) AS jumlah FROM pesanan GROUP BY status;Output yang Dihasilkan:
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | pesanan | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)Tiga kelompok status dihitung lewat temporary table. Pada kelompok sedikit ini murah; pada jutaan kelompok berbeda (misalnya GROUP BY user_id di tabel log), tabel sementara bisa membengkak ke disk. Obatnya index pada kolom GROUP BY sehingga baris terbaca sudah berkelompok-terurut — pola yang kita buktikan di section agregasi dengan idx_kota.
Ukur Ulang, Selalu #
Langkah kelima adalah yang paling sering dilewati: setelah perbaikan, jalankan lagi pengukuran yang sama — EXPLAIN untuk pola, Handler counter atau EXPLAIN ANALYZE untuk volume kerja — dan catat angka sebelum-sesudah. Perbaikan yang tidak diukur adalah rumor; kadang index baru justru tak terpakai optimizer, dan kita baru tahu setelah plan yang sama muncul lagi. Chapter penutup section ini melengkapi kotak alat ukurnya.
Membersihkan Database Latihan #
DROP DATABASE belajar_opt;Output yang Dihasilkan:
Query OK, 2 rows affected (0.00 sec)Referensi #
- Slow Query Log — MySQL 8.4 Reference Manual
- WHERE Clause Optimization — MySQL 8.4 Reference Manual
- LIMIT Query Optimization — MySQL 8.4 Reference Manual
Prosedur lima langkah dan katalog tujuh penyakit kini lengkap: dari slow log sampai Using temporary. Tapi langkah kelima — “ukur ulang” — masih meminjam alat yang belum kita bahas tuntas: bagaimana mengukur performa query dengan jujur, tanpa ditipu cache panas, jam dinding satu run, atau unit yang tidak konsisten. Chapter terakhir section ini membedah metodologi pengukurannya.