Index: B-tree, Composite Index, Kapan Index Digunakan

| SQL | sql, tutorial, index

Index: B-tree, Composite Index, Kapan Index Digunakan

Query lambat di tabel jutaan baris hampir selalu masalah yang sama: database membaca terlalu banyak baris. Solusi standarnya: index.

Artikel ini menjelaskan cara kerja index, cara membuatnya, dan — yang paling penting — kapan index justru TIDAK boleh dipakai.

1. Analogi: Daftar Isi Buku

Tanpa index:

Cari kata “optimasi” di kamus 1000 halaman → baca satu per satu (full table scan)

Dengan index:

Buka daftar huruf O → lompat ke halaman 412 (index scan)

Index SQL bekerja persis seperti itu — struktur bantu yang menyimpan nilai kolom + pointer ke baris asli, sudah terurut.


2. B-tree — Index Paling Umum

B-tree (balanced tree) adalah struktur default index di hampir semua DBMS.

Sifat penting:

  • Seimbang → kedalaman seragam, pencarian O(log n)
  • Terurut → mendukung =, <, >, BETWEEN, LIKE 'abc%', ORDER BY
  • Dapat duplikat (kecuali index unik)
          [P:50]
         /      \
    [M:25]      [T:75]
    /    \      /    \
[A..L] [N..S] [U..Z]

Query WHERE harga = 75 → turun 3 langkah, bukan scan jutaan baris.

Jenis index lain (singkat)

Jenis Untuk
B-tree Default, equality + range
Hash Equality = saja (jarang dipilih manual)
GIN/GiST (PostgreSQL) Full-text, array, JSON
BRIN (PostgreSQL) Data fisik sudah urut (log/timestamp besar)
Spatial Geometri

Seri ini fokus B-tree.


3. Membuat Index

-- Index pada satu kolom
CREATE INDEX idx_produk_nama ON produk(nama);

-- Index unik (nilai harus beda + otomatis constraint UNIQUE)
CREATE UNIQUE INDEX idx_users_email ON users(email);

Lihat index yang ada

-- PostgreSQL
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'produk';

-- MySQL
SHOW INDEX FROM produk;

-- SQLite
.schema produk

Hapus index

DROP INDEX IF EXISTS idx_produk_nama;

Index dari constraints

Sering otomatis dibuat DBMS:

Constraint Index otomatis
PRIMARY KEY Ya (umumnya)
UNIQUE Ya
FOREIGN KEY Umumnya (untuk validasi & join)

4. Composite Index — Index Multi-Kolom

CREATE INDEX idx_order_user_date ON orders(user_id, order_date);

Urutan kolom SANGAT penting

Prinsip leftmost prefix:

Query Pakai index?
WHERE user_id = 1 ✅ prefix kolom-1
WHERE user_id = 1 AND order_date > ... ✅ kedua kolom
WHERE order_date > ... (tanpa user_id) ❌ lewati kolom-1 → index tidak kepakai

Bayangkan kamus diurutkan dulu per huruf, baru per kata — mencari “Zebra” tanpa tahu huruf awal tetap harus telusuri.

Panduan urutan

  1. Kolom equality dulu (=) → user_id
  2. Lalu range/order (>, BETWEEN, ORDER BY) → order_date
  3. Kolom yang sering dipakai di banyak query → prioritas
-- Equality lalu range ✅
CREATE INDEX idx ON orders(user_id, order_date);

-- Untuk: WHERE user_id = ? AND order_date BETWEEN ? AND ?
--        ORDER BY order_date

5. Kapan Index MEMBANTU?

Kandidat index yang baik:

Pola query Kandidat
WHERE kolom = ? Index kolom itu
WHERE kolom IN (...) Index kolom itu
WHERE kolom > ? / BETWEEN Index range
ORDER BY kolom Index sesuai urutan
JOIN ON a.id = b.id Sudah ada PK/FK
Kolom di WHERE + ORDER BY Composite sesuai prinsip
Kolom sering di-filter di dashboard Index khusus

Rules of thumb

  1. Foreign key hampir selalu layak di-index (join + ON DELETE check).
  2. Kolom dengan selectivity tinggi (banyak nilai unik): email, sku, username.
  3. WHERE status = 'pending' — lihat distribusi: bila pending hanya 1% dari jutaan baris, index sangat membantu; bila 90% baris pending, index kurang berguna (lebih baik full scan).

6. Kapan Index TIDAK Membantu (atau Merugikan)?

❌ Selectivity rendah

-- Kolom hanya punya 2 nilai (TRUE/FALSE) di jutaan baris
CREATE INDEX idx_aktif ON produk(aktif);   -- biasanya SIA-SIA

Database lebih memilih full scan bila index akan mengarah ke sebagian besar tabel.

❌ Tidak dipakai di query

Index yang tidak pernah disentuh query = membuang-buang:

Biaya index Kapan dibayar
Ruang disk Selalu
INSERT/UPDATE/DELETE lebih lambat Setiap tulis (index harus di-update)
Optimasi lebih kompleks Saat planning

Aturan emas: jangan bikin index “jaga-jaga”. Index harus lahir dari query yang memang lambat.

❌ Fungsi pada kolom (biasanya)

-- ❌ Index di 'nama' tidak kepakai
WHERE LOWER(nama) = 'kopi'

-- ✅ Index expression / functional index (PostgreSQL)
CREATE INDEX idx ON products (LOWER(nama));
-- atau tulis ulang query agar pakai kolom polos

❌ Leading wildcard LIKE

WHERE nama LIKE '%kopi%'   -- ❌ index biasa tidak membantu
WHERE nama LIKE 'Kopi%'    -- ✅ prefix cocok → index bisa dipakai

Untuk %kopi%, pertimbangkan full-text index (PostgreSQL to_tsvector, MySQL FULLTEXT).

❌ Terlalu banyak index di tabel tulis-besar

Tabel dengan ribuan INSERT per detik + 20 index = tulis jadi lambat. Index hanya yang benar-benar dipakai.


7. Index Unik vs Index Biasa

CREATE UNIQUE INDEX idx_users_email ON users(email);
  Index biasa Index unik
Fungsi Mempercepat Mempercepat + jamin keunikan
Duplikat Boleh Tidak boleh
Setara dengan — UNIQUE constraint

8. Covering Index — Query tanpa Kembali ke Tabel

-- Index sudah memuat semua kolom yang diSELECT
CREATE INDEX idx_cover ON orders(user_id, order_date, total);

SELECT order_date, total
FROM orders
WHERE user_id = 1;

Database bisa jawab hanya dari index (index-only scan) — tidak perlu buka row asli.

Di PostgreSQL bisa juga:

CREATE INDEX idx_cover ON orders(user_id) INCLUDE (total);

9. Checklist Sebelum Membuat Index

  1. ✅ Ada query lambat yang terbukti? (bukan asumsi)
  2. ✅ Lihat query plan (EXPLAIN) — memang full scan?
  3. �️ Kolom sering muncul di WHERE/ORDER BY/JOIN?
  4. ✅ Selectivity cukup tinggi?
  5. ✅ Urutan composite sesuai leftmost prefix?
  6. ✅ Tabel tidak terlalu sering di-tulis dengan index sudah banyak?

10. Recap

Konsep Poin utama
B-tree Default, untuk =, range, ORDER BY
CREATE INDEX idx ON t(kolom) Buat index
CREATE UNIQUE INDEX Unik + cepat
Composite Equality dulu, lalu range; leftmost prefix
FK layak di-index Join + referential check
Rendah selectivity Hindari (2 nilai di jutaan baris)
LOWER(kolom) / %x% Index biasa tidak kepakai
Index tulis Tiap INSERT/UPDATE harus update index
Langkah awal EXPLAIN dulu, index sesudah bukti

Penutup

Index adalah pedang bermata dua: query baca jauh lebih cepat, tapi tulis jadi lebih mahal dan ruang disk terpakai. Kuncinya bukan “bikin banyak index”, tapi “bikin index yang tepat untuk query yang terbukti lambat”.

Di artikel selanjutnya, kita lihat bagaimana database berpikir — EXPLAIN & Query Optimization membaca query plan dan mengoptimasi query.


Bagikan via Email Bagikan via Facebook Bagikan via Twitter/X Bagikan via WhatsApp