1. Analogi Sederhana: Buku Tanpa Daftar Indeks
Bayangkan Anda membaca buku ensiklopedia setebal 1.000 halaman dan diminta mencari kata "Kriptografi" tanpa bantuan daftar indeks di halaman belakang. Anda terpaksa membuka dan memeriksa halaman demi halaman dari awal sampai akhir. Dalam dunia database, ini disebut Full Table Scan.
Database Index bekerja persis seperti buku indeks tersebut: struktur data terurut yang menampung nilai kolom tertentu beserta penunjuk alamat fisik (pointer) ke baris data aslinya di hard disk, mengubah pencarian $O(N)$ yang lambat menjadi pencarian $O(\log N)$ yang sangat cepat.
2. Struktur Data di Balik Index: B-Tree
Hampir semua mesin penyimpanan database relasional (seperti InnoDB MySQL dan PostgreSQL) menggunakan varian struktur data B-Tree (Balanced Tree) untuk indeks default mereka.
B-Tree menjaga pohon data selalu seimbang sehingga waktu yang dibutuhkan untuk mencari nilai tunggal (equality check =) maupun rentang nilai (range scan BETWEEN, >, <) tetap konstan dan sangat terprediksi, meskipun tabel menampung puluhan juta baris.
3. Cara Membaca Output Perintah EXPLAIN
Sebelum menambahkan indeks secara membabi buta, selalu awali dengan menganalisis kueri menggunakan perintah EXPLAIN atau EXPLAIN ANALYZE:
EXPLAIN SELECT id, customer_name, total_amount FROM orders WHERE customer_id = 450 AND status = 'completed';
Perhatikan kolom-kolom output berikut:
type: Menunjukkan metode pemindaian data. Nilai terbaik adalahconst,eq_ref, atauref. Jika nilainya adalahALL, berarti database melakukan Full Table Scan!key: Menampilkan nama indeks yang sebenarnya dipilih oleh database optimizer.rows: Estimasi jumlah baris yang harus diperiksa mesin database untuk mendapatkan hasil.
4. Aturan Emas Composite Index (Indeks Multi-Kolom)
Jika query sering memfilter beberapa kolom sekaligus dalam klausul WHERE col_a = ... AND col_b = ..., indeks gabungan (Composite Index) jauh lebih efisien daripada membuat dua indeks tunggal terpisah.
Prinsip Leftmost Prefix: Urutan kolom pada saat mendefinisikan composite index sangat menentukan. Jika Anda membuat indeks pada (status, created_at), query yang hanya memfilter status dapat memanfaatkan indeks, namun query yang hanya memfilter created_at tidak dapat memanfaatkan indeks tersebut!
5. Biaya Tersembunyi (Trade-Off) Penambahan Index
SELECT), namun akan memperlambat operasi tulis (INSERT, UPDATE, DELETE) karena database harus menyusun ulang struktur pohon B-Tree pada setiap transaksi penulisan.