Section indexes mengajari kita cara kerja index; chapter ini mengajari cara menempatkan index dengan strategi. Posisi yang tepat mengikuti pertanyaan yang sering diajukan data: kolom mana yang paling sering difilter, dihubungkan, atau diurutkan — dan index yang mengikuti pola itu akan terbayar lunas lewat plan yang sehat. Kita akan bekerja dari sisi pembaca plan: setiap strategi dibuktikan dengan EXPLAIN sebelum dan sesudah, karena index yang bagus di atas kertas tetap harus menang di depan optimizer. Materinya menyambung erat dari composite index dan leftmost principle yang sudah kita pelajari, lalu menutup dengan dua topik yang jarang diajarkan: biaya kelebihan index dan konsistensi tipe dari sisi index.
Menyiapkan Data Latihan #
Tabel detail berisi dua puluh baris item pesanan — cukup untuk eksperimen covering index dan composite — ditambah tabel voucher dan pelanggan_lama untuk percobaan tipe data:
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 detail (
id INT AUTO_INCREMENT PRIMARY KEY,
pesanan_id INT NOT NULL,
produk VARCHAR(60) NOT NULL,
jumlah INT NOT NULL,
harga DECIMAL(10,2) NOT NULL
);
INSERT INTO detail (pesanan_id, produk, jumlah, harga) VALUES
(1, 'Kopi Hitam 200g', 2, 45000.00),
(1, 'Gula Pasir 1kg', 1, 16000.00),
(2, 'Teh Hijau 25 kantong', 3, 12000.00),
(2, 'Kopi Hitam 200g', 1, 45000.00),
(3, 'Beras Premium 5kg', 1, 72000.00),
(4, 'Minyak Goreng 2L', 2, 33000.00),
(4, 'Gula Pasir 1kg', 2, 16000.00),
(5, 'Kopi Hitam 200g', 5, 45000.00),
(6, 'Teh Hijau 25 kantong', 2, 12000.00),
(7, 'Beras Premium 5kg', 2, 72000.00),
(8, 'Gula Pasir 1kg', 1, 16000.00),
(9, 'Kopi Hitam 200g', 3, 45000.00),
(10, 'Minyak Goreng 2L', 1, 33000.00),
(11, 'Teh Hijau 25 kantong', 4, 12000.00),
(12, 'Beras Premium 5kg', 1, 72000.00),
(13, 'Gula Pasir 1kg', 3, 16000.00),
(14, 'Kopi Hitam 200g', 2, 45000.00),
(15, 'Teh Hijau 25 kantong', 1, 12000.00),
(15, 'Gula Pasir 1kg', 2, 16000.00),
(15, 'Beras Premium 5kg', 1, 72000.00);
CREATE INDEX idx_pesanan ON detail(pesanan_id);
CREATE INDEX idx_produk_jumlah ON detail(produk, jumlah);Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Query OK, 20 rows affected (0.00 sec)
Records: 20 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)
Records: 0 Duplicates: 0 Warnings: 0Strategi Memilih Kolom #
Index hidup dari pola akses, bukan dari jumlah. Sebelum menambah index, petakan klausul query dominan ke kolom kandidatnya:
| Klausul query yang dominan | Kandidat index | Contoh di data kita |
|---|---|---|
WHERE kolom = nilai | Index tunggal di kolom itu | produk, pesanan_id |
JOIN a.x = b.y | Index di kolom kedua sisi (minimal sisi yang dicari) | pesanan_id |
ORDER BY kolom | Index agar pengurutan gratis (menghindari filesort) | produk, jumlah |
WHERE a = ? AND b > ? | Composite a di depan, b di belakang | (produk, jumlah) |
Aturan penggarisnya: index membayar dirinya ketika kolomnya muncul berulang kali di query panas aplikasi. Satu query laporan tahunan tidak membenarkan index; ribuan panggilan filter produk = ? per hari membenarkannya.
Covering Index: Extra “Using Index” #
Covering index terjadi ketika seluruh kolom yang dibutuhkan query — filter, join, dan proyeksi — tersedia di dalam index itu sendiri, sehingga baris tabel tidak dibuka. Buktinya di Extra: label Using index. Kita bandingkan dua bentuk query yang filternya sama:
EXPLAIN SELECT * FROM detail WHERE produk = 'Kopi Hitam 200g';
EXPLAIN SELECT id, produk, jumlah FROM detail WHERE produk = 'Kopi Hitam 200g';Output yang Dihasilkan:
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | detail | NULL | ref | idx_produk_jumlah | idx_produk_jumlah | 242 | const | 5 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | detail | NULL | ref | idx_produk_jumlah | idx_produk_jumlah | 242 | const | 5 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Kedua plan sama-sama memakai idx_produk_jumlah (type = ref), tetapi hanya plan kedua yang menampilkan Using index. Index InnoDB menyimpan kolom kunci (produk, jumlah) plus primary key (id) — tiga kolom yang diminta query kedua lengkap ada di index. Query pertama meminta semua kolom termasuk harga, jadi server harus membuka baris tabel untuk setiap kecocokan. Desain index yang sadar covering berarti memilih kolom proyeksi ke dalam index — inilah alasan SELECT * selalu menjadi musuh covering index.
Urutan Kolom Composite dan Leftmost #
Index (produk, jumlah) hanya berguna bila kondisi menyentuh awalannya. Filter pada kolom pertama dijawab dengan lompatan; filter pada kolom kedua saja tidak punya titik lompat:
EXPLAIN SELECT jumlah FROM detail WHERE produk = 'Kopi Hitam 200g';
EXPLAIN SELECT jumlah FROM detail WHERE jumlah = 2;Output yang Dihasilkan:
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | detail | NULL | ref | idx_produk_jumlah | idx_produk_jumlah | 242 | const | 5 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | detail | NULL | index | idx_produk_jumlah | idx_produk_jumlah | 246 | NULL | 20 | 10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)Plan pertama: type = ref dengan key_len = 242 — tepat panjang kolom produk (60 karakter × 4 byte utf8mb4 + 2 byte penanda) — lompat ke kelompok “Kopi Hitam 200g”. Plan kedua: type = index, key = idx_produk_jumlah dipakai penuh sebagai objek sapuan, dan filtered = 10.00 — dua dari dua puluh baris diperkirakan lolos. Index tetap “dipakai”, tetapi sebagai scan, bukan lookup; ini persis perilaku leftmost principle yang kita bahas di chapter composite index, kini terlihat dari sisi plan.
Equality di Depan, Range di Belakang #
Ketika satu kondisi equality dan satu range berbagi index composite, urutannya menentukan seberapa dalam index terpakai. key_len menjadi alat ukurnya:
EXPLAIN SELECT id, harga FROM detail
WHERE produk = 'Kopi Hitam 200g' AND jumlah > 2;Output yang Dihasilkan:
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | detail | NULL | range | idx_produk_jumlah | idx_produk_jumlah | 246 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)key_len = 246 — 242 dari produk plus 4 dari jumlah: kedua kolom terpakai. Equality di depan menyelesaikan lompatan ke kelompok produk, lalu range jumlah > 2 membaca searah di dalam kelompok itu (type = range). Bila urutan index dibalik menjadi (jumlah, produk), range jumlah > 2 di depan memaksa membaca semua kelompok jumlah di atas 2 lalu menyaring produk di dalamnya — index terpakai lebih dangkal. Aturan praktisnya: kolom equality selalu di kiri kolom range.
Terlalu Banyak Index: Biaya Tulis #
Setiap index mempercepat baca dan memperlambat tulis: INSERT, UPDATE, dan DELETE harus memelihara setiap struktur index yang tersangkut tabelnya. Audit sederhana dengan SHOW INDEX memperlihatkan apa yang kita rawat:
-- @check (cardinality = estimasi statistik, bisa berfluktuasi)
SHOW INDEX FROM detail;Output yang Dihasilkan:
+--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| detail | 0 | PRIMARY | 1 | id | A | 19 | NULL | NULL | | BTREE | | | YES | NULL |
| detail | 1 | idx_pesanan | 1 | pesanan_id | A | 15 | NULL | NULL | | BTREE | | | YES | NULL |
| detail | 1 | idx_produk_jumlah | 1 | produk | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| detail | 1 | idx_produk_jumlah | 2 | jumlah | A | 15 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)Empat baris = satu primary key plus dua index (dua baris milik idx_produk_jumlah karena dua kolomnya). Kolom Cardinality adalah perkiraan jumlah nilai unik yang dihitung optimizer dari statistik — nilainya bisa sedikit bergeser antar pemeriksaan (misalnya 19 atau 20 untuk tabel yang sama) karena InnoDB memutakhirkan statistiknya secara berkala; gunakan angka ini sebagai petunjuk selektivitas, bukan hitungan pasti. Skema produksi yang dipelihara bertahun-tahun kerap menumpuk sepuluh index per tabel — sebagian besar melayani query yang sudah tidak dipanggil siapa pun. Prosedur auditnya manual: kumpulkan query panas (slow log, chapter Query yang Lambat), jalankan EXPLAIN, lalu tandai index yang tidak pernah muncul di kolom key mana pun. Kandidat yang sudah aman dihapus bisa dijatuhkan — dan diaktifkan lagi cepat bila salah tebak:
DROP INDEX idx_pesanan ON detail;Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0Konsistensi Tipe dari Sisi Index #
Chapter Index yang Tidak Efektif menunjukkan konversi tipe implisit dari sisi query; sekarang kita lihat sisi pasangannya — index yang berdiri di kolom dengan tipe yang tidak cocok dengan cara kolom itu dicari. Tabel voucher menyimpan kode sebagai teks:
CREATE TABLE voucher (
kode VARCHAR(20) NOT NULL,
potongan DECIMAL(10,2) NOT NULL
);
INSERT INTO voucher (kode, potongan) VALUES
('Diskon2026', 25000.00),
('HematLebaran', 40000.00),
('PelangganBaru', 15000.00);
CREATE INDEX idx_kode ON voucher(kode);
EXPLAIN SELECT * FROM voucher WHERE kode = 'Diskon2026';
EXPLAIN SELECT * FROM voucher WHERE kode = 2026;Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
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 | voucher | NULL | ref | idx_kode | idx_kode | 82 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | voucher | NULL | ALL | idx_kode | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)Index idx_kode sempurna untuk pencarian teks (type = ref), tetapi mati total ketika aplikasi mengirim angka: membandingkan kolom teks dengan angka memaksa konversi per baris, hasil konversi tidak ada di index, dan plan jatuh ke type = ALL. Kali ini akar masalahnya bukan bentuk query — melainkan keputusan desain bahwa kode disimpan sebagai VARCHAR sementara pemanggil mengirim INT.
Varian paling senyap muncul di join lintas tabel. Tabel pelanggan_lama diimpor dari sistem lama yang menyimpan ID sebagai teks:
CREATE TABLE pelanggan_lama (
id_lama VARCHAR(10) NOT NULL,
nama VARCHAR(60) NOT NULL
);
INSERT INTO pelanggan_lama VALUES ('1', 'Budi Santoso'), ('2', 'Siti Rahayu'), ('3', 'Andi Wijaya');
CREATE TABLE pesanan (
id INT AUTO_INCREMENT PRIMARY KEY,
pelanggan_id INT NOT NULL,
total DECIMAL(12,2) NOT NULL
);
INSERT INTO pesanan (pelanggan_id, total) VALUES
(1, 150000.00), (2, 250000.00), (3, 75000.00),
(1, 320000.00), (2, 210000.00), (4, 430000.00);
CREATE INDEX idx_pelanggan ON pesanan(pelanggan_id);
EXPLAIN SELECT pl.nama, o.total
FROM pelanggan_lama pl
JOIN pesanan o ON o.pelanggan_id = pl.id_lama;Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
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 | pl | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ref | idx_pelanggan | idx_pelanggan | 4 | belajar_opt.pl.id_lama | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)Kolom join bertipe berbeda — INT bertemu VARCHAR — dan plan membuktikan harganya. Baris o memakai idx_pelanggan (type = ref), tetapi lihat kolom ref: pembandingnya adalah belajar_opt.pl.id_lama, kolom teks. Pembandingan dua tipe berbeda tidak bisa diselesaikan sebagai kunci index murni, sehingga MySQL melabelinya Using index condition — kondisi diperiksa ulang untuk setiap baris yang dijangkau, bukan dianggap selesai di index. Bandingkan dengan join bertipe sama di chapter Join Optimization: di sana kolom ref merujuk kolom INT dan tidak ada label konversi. Perbaikan yang benar bukan menambah index, melainkan menyamakan tipe kolom — konversi satu kali saat import (CAST ke INT), lalu join kembali bersih.
Membersihkan Database Latihan #
DROP DATABASE belajar_opt;Output yang Dihasilkan:
Query OK, 4 rows affected (0.00 sec)Referensi #
- How MySQL Uses Indexes — MySQL 8.4 Reference Manual
- Multiple-Column Indexes — MySQL 8.4 Reference Manual
- SHOW INDEX Statement — MySQL 8.4 Reference Manual
Strategi index kini lengkap: kolom mengikuti pola query, covering dibuktikan lewat Using index, composite mengikuti leftmost dan aturan equality-dulu, jumlah index diaudit, dan tipe data dijaga konsisten dari kedua sisi. Sisa satu pola penyakit yang belum kita bedah tuntas: tabel yang dipindai seluruhnya padahal query ber-WHERE. Chapter berikutnya mengumpulkan semua penyebab full table scan beserta obatnya, satu per satu.