Indexing di MySQL

Index adalah struktur data tambahan yang membuat MySQL bisa menemukan baris tanpa membaca seluruh tabel. Analoginya indeks di belakang buku: daripada membaca 500 halaman untuk mencari kata “deadlock”, kamu cukup lihat indeks lalu loncat ke halaman 312. Catatan ini fokus ke InnoDB di MySQL 8.

1. Cara Kerja: B-tree

Hampir semua index InnoDB berbentuk B+tree: pohon seimbang yang datanya terurut. Mencari satu nilai di antara jutaan baris hanya butuh beberapa lompatan dari akar ke daun (kompleksitas logaritmik).

Karena terurut, B-tree efektif untuk:

Pencocokan persis: WHERE email = '[email protected]'
Rentang: WHERE created_at BETWEEN ... AND ..., >, <
Prefix string: WHERE name LIKE 'Bud%'
ORDER BY dan GROUP BY pada kolom yang ter-index
MIN() / MAX()

Dan tidak efektif untuk:

LIKE '%budi' (wildcard di depan). Gunakan FULLTEXT index untuk pencarian teks.
Kolom yang dibungkus fungsi: WHERE YEAR(created_at) = 2026

2. Jenis Index di InnoDB

Primary key = clustered index

Di InnoDB, data tabel disimpan di dalam index primary key (clustered index). Daun B-tree PK berisi seluruh kolom baris. Konsekuensinya:

Setiap tabel InnoDB selalu punya clustered index. Tanpa PK, InnoDB memakai index UNIQUE NOT NULL pertama, atau membuat kolom tersembunyi.
PK sebaiknya kecil dan naik terus (BIGINT AUTO_INCREMENT). UUID acak (v4) sebagai PK membuat insert menyebar ke seluruh pohon (page split, fragmentasi).

Secondary index

Index selain PK. Daunnya menyimpan nilai kolom index + nilai PK. Saat query butuh kolom lain, MySQL harus melompat lagi ke clustered index memakai PK tersebut (bookmark lookup). Karena itu PK yang besar membuat semua secondary index ikut membengkak.

CREATE INDEX idx_orders_status ON orders (status);
-- atau
ALTER TABLE orders ADD INDEX idx_orders_status (status);

Unique index

Sama seperti secondary index, ditambah jaminan tidak ada nilai duplikat. (Catatan: banyak NULL tetap diizinkan.)

ALTER TABLE users ADD UNIQUE INDEX uq_users_email (email);

Composite index

Index pada beberapa kolom sekaligus. Urutan kolom sangat menentukan.

CREATE INDEX idx_orders_cust_status_date
  ON orders (customer_id, status, created_at);

Lainnya

Jenis	Kegunaan
`FULLTEXT`	Pencarian kata di kolom teks (`MATCH ... AGAINST`)
`SPATIAL`	Data geometri
Prefix index	Index sebagian awal string panjang: `INDEX (url(100))`
Functional index (8.0.13+)	Index atas ekspresi: `INDEX ((YEAR(created_at)))`
Invisible index (8.0+)	Index yang disembunyikan dari optimizer untuk uji coba sebelum di-drop

3. Aturan Leftmost Prefix

Composite index (customer_id, status, created_at) diurutkan seperti buku telepon: pertama berdasarkan customer_id, lalu status di dalamnya, lalu created_at. Index hanya bisa dipakai jika query memakai kolom dari kiri, berurutan.

Kondisi WHERE	Index terpakai?
`customer_id = 5`	Ya (1 kolom)
`customer_id = 5 AND status = 'paid'`	Ya (2 kolom)
`customer_id = 5 AND status = 'paid' AND created_at > '2026-01-01'`	Ya (3 kolom)
`status = 'paid'`	Tidak (kolom pertama dilewati)
`customer_id = 5 AND created_at > '2026-01-01'`	Sebagian: hanya `customer_id` untuk pencarian
`customer_id > 5 AND status = 'paid'`	Sebagian: berhenti setelah kondisi rentang

Aturan praktis menyusun urutan kolom:

Kolom dengan kondisi kesamaan (=) di depan.
Kolom rentang (>, BETWEEN, LIKE 'x%') atau ORDER BY di belakang.
Setelah kolom rentang, kolom berikutnya tidak lagi membantu penyaringan lewat pencarian B-tree.

Satu composite index (a, b) sekaligus melayani query yang hanya memakai a, jadi index terpisah di (a) biasanya redundan.

4. Covering Index

Jika semua kolom yang dibutuhkan query ada di dalam index, MySQL tidak perlu membuka clustered index sama sekali. Ini disebut covering index dan terlihat sebagai Using index di EXPLAIN.

CREATE INDEX idx_orders_cust_status_total ON orders (customer_id, status, total);

-- seluruh kolom ada di index → covering
SELECT status, SUM(total)
FROM orders
WHERE customer_id = 5
GROUP BY status;

Ingat bahwa secondary index InnoDB otomatis membawa kolom PK, jadi SELECT id, status FROM orders WHERE customer_id = 5 juga ter-cover.

5. Membaca EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 5 AND status = 'paid';

Kolom yang paling penting:

Kolom	Arti	Tanda bahaya
`type`	Cara akses tabel	`ALL` = full table scan
`possible_keys`	Index yang dipertimbangkan	`NULL`
`key`	Index yang benar-benar dipilih	`NULL`
`key_len`	Panjang byte index yang dipakai (menunjukkan berapa kolom composite terpakai)	Lebih pendek dari harapan
`rows`	Perkiraan baris yang diperiksa	Angka besar
`Extra`	Info tambahan	`Using filesort`, `Using temporary`

Nilai type dari terbaik ke terburuk (disederhanakan):

system > const > eq_ref > ref > range > index > ALL

const / eq_ref: pencarian via PK atau unique, satu baris.
ref: pencarian via index non-unik.
range: rentang pada index.
index: membaca seluruh index (lebih baik dari ALL, tapi tetap scan).
ALL: membaca seluruh tabel.

Untuk angka nyata (bukan perkiraan), MySQL 8.0.18+ menyediakan EXPLAIN ANALYZE yang benar-benar menjalankan query dan menampilkan waktu tiap langkah:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5 ORDER BY created_at DESC LIMIT 10;

Peringatan: EXPLAIN ANALYZE mengeksekusi query sungguhan. Jangan jalankan di query berat pada server produksi yang sedang sibuk.

Melihat index yang ada di tabel:

SHOW INDEX FROM orders;

6. Kesalahan yang Membuat Index Tidak Terpakai

Query	Masalah	Perbaikan
`WHERE YEAR(created_at) = 2026`	Kolom dibungkus fungsi	`WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'`
`WHERE phone = 81234567` (kolom `VARCHAR`)	Konversi tipe implisit	Bandingkan dengan string: `'081234567'`
`WHERE name LIKE '%budi%'`	Wildcard di depan	`FULLTEXT` atau mesin pencari
`WHERE a = 1 OR b = 2`	`OR` antar kolom berbeda	Index di kedua kolom (index merge) atau `UNION`
`WHERE status = 'paid'` pada index `(customer_id, status)`	Melanggar leftmost prefix	Tambahkan index dengan `status` di depan jika memang perlu
`ORDER BY created_at` tanpa index	`Using filesort`	Index yang mencakup kolom filter + urutan

7. Kapan Index Justru Merugikan

Index bukan gratis:

Tulis lebih lambat. Setiap INSERT, UPDATE kolom ter-index, dan DELETE harus memperbarui semua index terkait.
Makan disk dan memori. Index bersaing dengan data di buffer pool InnoDB.
Selektivitas rendah. Index di kolom seperti is_active (dua nilai) jarang berguna sendirian; optimizer sering memilih full scan karena lebih murah daripada ribuan lookup acak.
Index redundan/duplikat. (a) dan (a, b) sekaligus, atau index yang sama dengan dua nama.
Tabel kecil. Untuk beberapa ratus baris, full scan sudah sangat cepat.

Mencari index yang tidak pernah dipakai (butuh performance_schema aktif, default di MySQL 8):

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'toko';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'toko';

Sebelum men-drop index di produksi, jadikan invisible dulu untuk mengetes dampaknya tanpa kehilangan index:

ALTER TABLE orders ALTER INDEX idx_orders_status INVISIBLE;
-- pantau performa; jika aman:
ALTER TABLE orders DROP INDEX idx_orders_status;
-- jika ternyata dibutuhkan:
ALTER TABLE orders ALTER INDEX idx_orders_status VISIBLE;

8. Checklist Praktis

Aktifkan slow query log untuk menemukan query yang layak dioptimasi, jangan menebak.
Jalankan EXPLAIN pada query tersebut.
Buat index berdasarkan pola WHERE + ORDER BY query nyata, dengan urutan kesamaan → rentang.
Ukur ulang dengan EXPLAIN ANALYZE.
Semua kolom foreign key sebaiknya ter-index (InnoDB membuatnya otomatis saat FK dibuat).
Menambah index di MySQL 8 berjalan online (ALGORITHM=INPLACE, tabel tetap bisa dibaca dan ditulis), tetapi tetap memakan I/O. Lakukan saat trafik rendah untuk tabel besar.

Indexing di MySQL.