Cara Kerja Indeks Database (B-Tree) dan Kapan Tidak Membantu

Indeks database adalah struktur data terpisah, biasanya B-Tree, yang menyimpan nilai kolom dalam keadaan terurut beserta penunjuk ke baris aslinya. Tanpa indeks, database harus memeriksa setiap baris (full table scan, O(N)). Dengan indeks, database cukup menelusuri pohon dari root ke leaf dalam beberapa langkah (O(log N)). Harganya adalah ruang disk tambahan dan penulisan yang sedikit lebih lambat, karena indeks harus ikut diperbarui setiap kali data berubah.

Masalah yang diselesaikan indeks

Bayangkan tabel users dengan 10 juta baris dan query berikut:

SELECT * FROM users WHERE email = 'andi@harau.dev';

Tanpa indeks di kolom email, database tidak punya pilihan selain membaca seluruh tabel dan membandingkan setiap baris. Mirip mencari satu nama di buku telepon yang tidak diurutkan.

Bagaimana B-Tree bekerja

Kebanyakan database relasional (PostgreSQL, MySQL/InnoDB, SQL Server) memakai varian B+Tree sebagai tipe indeks default:

• Node internal hanya berisi kunci pembatas yang mengarahkan pencarian: “nilai < 40 ke kiri, 40–69 ke tengah, ≥ 70 ke kanan”.
• Node leaf berisi semua kunci yang terurut beserta penunjuk ke lokasi baris di tabel.
• Leaf saling terhubung seperti linked list, sehingga query rentang (BETWEEN, >, ORDER BY) cukup menemukan titik awal lalu berjalan menyamping.

Kenapa pohonnya tetap pendek

Satu node disimpan dalam satu page disk (8 KB di PostgreSQL, 16 KB di InnoDB) dan bisa memuat ratusan kunci. Dengan fan-out sekitar 300:

Tinggi pohon	Kira-kira jumlah kunci yang bisa dijangkau
2 level	300 × 300 = 90 ribu
3 level	300³ ≈ 27 juta
4 level	300⁴ ≈ 8 miliar

Artinya, mencari satu baris di antara puluhan juta data hanya butuh membaca 3–4 page. Node di level atas hampir selalu sudah ada di memori.

Membuat dan memverifikasi indeks

CREATE INDEX idx_users_email ON users (email);

EXPLAIN SELECT * FROM users WHERE email = 'andi@harau.dev';

Periksa bentuk rencana eksekusinya. Sebelum indeks, Anda akan melihat Seq Scan on users. Setelah indeks dibuat dan statistik diperbarui, rencananya berubah menjadi Index Scan using idx_users_email on users. Gunakan EXPLAIN ANALYZE untuk melihat waktu eksekusi sebenarnya.

Indeks komposit dan aturan prefix paling kiri

Indeks bisa mencakup lebih dari satu kolom:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

Data diurutkan berdasarkan user_id dulu, lalu created_at di dalam setiap user_id. Akibatnya:

• ✅ WHERE user_id = 7 bisa memakai indeks.
• ✅ WHERE user_id = 7 AND created_at > '2026-01-01' bisa memakai indeks sepenuhnya.
• ✅ WHERE user_id = 7 ORDER BY created_at DESC bisa mengambil data yang sudah terurut.
• ⚠️ WHERE created_at > '2026-01-01' saja umumnya tidak efisien, karena kolom pertama tidak dipakai.

Urutkan kolom berdasarkan pola query Anda. Kolom yang dibandingkan dengan = biasanya diletakkan lebih dulu, baru kolom rentang atau pengurutan.

Kapan indeks tidak membantu

Indeks yang ada belum tentu dipakai. Beberapa penyebab yang sering muncul:

1. Fungsi pada kolom. WHERE LOWER(email) = '…' tidak bisa memakai indeks di email. Solusinya adalah expression index: CREATE INDEX … ON users (LOWER(email)).
2. Wildcard di depan. LIKE '%harau' tidak bisa memanfaatkan urutan B-Tree, sedangkan LIKE 'harau%' bisa (dengan catatan soal collation).
3. Selektivitas rendah. Jika kondisi cocok dengan sebagian besar baris (misalnya kolom boolean is_active yang 95% true), membaca tabel secara berurutan justru lebih murah. Planner akan memilih Seq Scan.
4. Tipe data tidak cocok. Membandingkan kolom teks dengan angka bisa memicu konversi implisit yang membuat indeks tidak terpakai.
5. Tabel kecil. Untuk beberapa ratus baris, full scan sering lebih cepat daripada menelusuri indeks.

Biaya indeks

Setiap indeks mempercepat baca, tapi memperlambat tulis:

• Setiap INSERT, UPDATE pada kolom terindeks, dan DELETE juga harus memperbarui setiap indeks terkait.
• Indeks memakan ruang disk dan memori (buffer cache).
• Indeks yang tidak pernah dipakai hanya menambah beban. Di PostgreSQL, periksa pg_stat_user_indexes untuk menemukan indeks dengan idx_scan = 0.

Ringkasan

• Indeks B-Tree menyimpan kunci terurut sehingga pencarian menjadi O(log N), biasanya hanya 3–4 page.
• Leaf yang terhubung membuat query rentang dan ORDER BY efisien.
• Untuk indeks komposit, urutan kolom penting: ikuti aturan prefix paling kiri.
• Selalu verifikasi dengan EXPLAIN, dan jangan menambah indeks tanpa melihat pola query nyata.

• #database
• #postgresql
• #indeks
• #performa