Constraint dari chapter sebelumnya menjaga kebenaran data; index menjaga kecepatan pengambilannya. Tanpa index, satu-satunya cara database menemukan baris adalah memeriksa tabel baris demi baris — full table scan. Pada sembilan baris latihan, proses itu seketika; pada 200 ribu baris log pesanan, setiap pencarian pelanggan membaca 200 ribu baris untuk menemukan ratusan. Chapter ini membongkar struktur yang membuat pencarian bisa melompat langsung: B-tree index, lengkap dengan pengukuran nyata sebelum dan sesudahnya.

Cara Kerja B-tree #

Index pada SQLite berbentuk B-tree — pohon seimbang yang menyimpan kunci terurut. Setiap simpul pohon adalah satu halaman disk berisi beberapa kunci; kunci-kunci tersebut membagi ruang pencarian sehingga tiap langkah menyingkirkan sebagian besar baris. Ilustrasi B-tree pada kolom bertipe integer:

Mencari kunci 400 dari akar hanya butuh dua keputusan: 400 berada di antara 250 dan 500, lalu halaman daun kedua menampung kunci tersebut. Karena pohon dijaga seimbang, tingginya hanya bertambah satu setiap kali jumlah baris berlipat kali lipat — satu juta baris cukup ditampung pohon tiga sampai empat tingkat, sehingga pencarian hanya menyentuh beberapa halaman, bukan satu juta baris.

Kita bangun tabel besar terlebih dahulu — 200.000 baris log pesanan untuk 500 pelanggan, dibuat dengan recursive CTE:

python 
import sqlite3
import time

koneksi = sqlite3.connect(":memory:")
koneksi.execute("""
    CREATE TABLE log_pesanan (
        id INTEGER PRIMARY KEY,
        pelanggan_id INTEGER NOT NULL,
        status TEXT NOT NULL,
        tanggal TEXT NOT NULL
    )
""")
koneksi.execute("""
    INSERT INTO log_pesanan (pelanggan_id, status, tanggal)
    WITH RECURSIVE seq(i) AS (
        SELECT 1 UNION ALL SELECT i + 1 FROM seq WHERE i < 200000
    )
    SELECT 1 + (i % 500),
           CASE WHEN i % 10 = 0 THEN 'dibatalkan' ELSE 'selesai' END,
           date('2024-01-01', (i % 180) || ' days')
    FROM seq
""")

def plan(query):
    for baris in koneksi.execute("EXPLAIN QUERY PLAN " + query):
        print(baris[-1])

def ukur(query):
    mulai = time.perf_counter()
    for _ in range(5):
        hasil = koneksi.execute(query).fetchone()
    durasi = (time.perf_counter() - mulai) / 5 * 1000
    return hasil[0], round(durasi, 2)

query = "SELECT COUNT(*) FROM log_pesanan WHERE pelanggan_id = 42"
plan(query)
print(ukur(query))

Output yang Dihasilkan:

text 
SCAN log_pesanan
(400, 7.52)

Plan SCAN memastikan engine membaca seluruh 200.000 baris untuk menemukan 400 baris milik pelanggan 42 — rata-rata 7,52 milidetik per query. Sekarang kita buat index pada kolom yang sering difilter tersebut, lalu ukur ulang:

python 
koneksi.execute("CREATE INDEX idx_log_pelanggan ON log_pesanan(pelanggan_id)")
plan(query)
print(ukur(query))

Output yang Dihasilkan:

text 
SEARCH log_pesanan USING COVERING INDEX idx_log_pelanggan (pelanggan_id=?)
(400, 0.02)

Rencana berubah menjadi SEARCH — engine melompat lewat B-tree tepat ke entri pelanggan_id = 42, dan durasi turun dari 7,52 menjadi 0,02 milidetik — ratusan kali lebih cepat. Angka milidetiknya pasti berbeda di mesin Anda karena bergantung perangkat keras dan beban; yang stabil adalah polanya — SCAN membaca seluruh tabel sementara SEARCH hanya menyentuh beberapa halaman index. Tanda (pelanggan_id=?) menunjukkan kolom index yang dipakai, dan kata COVERING berarti index memuat semua data yang dibutuhkan query — COUNT(*) dengan filter pelanggan_id tidak perlu menyentuh tabel sama sekali.

Composite Index dan Aturan Kolom Kiri #

Satu index boleh memuat beberapa kolom. Index (kategori, harga) mengurutkan entri berdasarkan kategori lebih dulu, baru harga di dalam tiap kategori — seperti buku telepon yang mengurutkan nama keluarga dulu, baru nama depan. Konsekuensinya diuji pada tabel 50.000 produk:

python 
koneksi.execute("""
    CREATE TABLE produk_besar (
        id INTEGER PRIMARY KEY, nama TEXT NOT NULL,
        kategori TEXT NOT NULL, harga INTEGER NOT NULL
    )
""")
koneksi.execute("""
    INSERT INTO produk_besar (nama, kategori, harga)
    WITH RECURSIVE seq(i) AS (
        SELECT 1 UNION ALL SELECT i + 1 FROM seq WHERE i < 50000
    )
    SELECT 'Produk ' || i,
           CASE WHEN i % 2 = 0 THEN 'elektronik' ELSE 'fashion' END,
           10000 + (i * 977) % 900000
    FROM seq
""")
koneksi.execute("CREATE INDEX idx_kategori_harga ON produk_besar(kategori, harga)")

plan("SELECT * FROM produk_besar WHERE kategori = 'elektronik' AND harga < 50000")
plan("SELECT * FROM produk_besar WHERE kategori = 'fashion'")
plan("SELECT * FROM produk_besar WHERE harga < 50000")

Output yang Dihasilkan:

text 
SEARCH produk_besar USING INDEX idx_kategori_harga (kategori=? AND harga<?)
SEARCH produk_besar USING INDEX idx_kategori_harga (kategori=?)
SCAN produk_besar

Dua query pertama berhasil SEARCH karena filternya menyertakan kolom paling kiri dari index. Query ketiga — filter harga saja — jatuh kembali ke SCAN: dalam index yang diurutkan per kategori, nilai harga tersebar di dalam tiap kelompok kategori, sehingga rentang harga tidak bisa dilompati. Inilah aturan kolom kiri (leftmost prefix rule): composite index hanya terpakai bila query memfilter kolom-kolomnya berurutan dari kiri. Karena query bisnis toko online hampir selalu memfilter kategori dulu lalu rentang harga, urutan (kategori, harga) tepat.

Kapan Index Justru Mengganggu #

Index bukan hadiah gratis. Setiap INSERT, UPDATE, dan DELETE harus turut memperbarui seluruh B-tree index tabel tersebut. Pengukuran nyata berikut membandingkan pengisian 50.000 baris ke tabel tanpa index versus tabel dengan tiga index:

python 
def isi_tabel(tabel, jumlah_baris):
    mulai = time.perf_counter()
    koneksi.executemany(
        f"INSERT INTO {tabel} (a, b, c) VALUES (?, ?, ?)",
        ((i, i % 100, f"teks-{i}") for i in range(jumlah_baris)),
    )
    return (time.perf_counter() - mulai) * 1000

koneksi.execute("CREATE TABLE tanpa_index (id INTEGER PRIMARY KEY, a INT, b INT, c TEXT)")
koneksi.execute("CREATE TABLE banyak_index (id INTEGER PRIMARY KEY, a INT, b INT, c TEXT)")
koneksi.execute("CREATE INDEX idx_a ON banyak_index(a)")
koneksi.execute("CREATE INDEX idx_b ON banyak_index(b)")
koneksi.execute("CREATE INDEX idx_ab ON banyak_index(a, b)")

print(f"tabel tanpa index : {isi_tabel('tanpa_index', 50000):.0f} ms")
print(f"tabel 3 index     : {isi_tabel('banyak_index', 50000):.0f} ms")

Output yang Dihasilkan:

text 
tabel tanpa index : 31 ms
tabel 3 index     : 89 ms

Tiga index membuat penulisan dua sampai tiga kali lebih lambat — dan tiap index juga menambah ukuran file database karena menyimpan salinan kolomnya sendiri. Dua pertimbangan lain sebelum membuat index: kolom dengan sedikit nilai unik (misalnya status berisi dua kemungkinan) jarang menghasilkan lompatan yang berarti, dan tabel yang hampir selalu dibaca penuh (tabel staging kecil, tabel referensi) tidak membutuhkan index sama sekali.

Index mengubah rencana eksekusi, tetapi keputusan memakainya diambil oleh komponen lain: query planner. Chapter Query Planning membuka isi kepala planner tersebut — membaca plan secara utuh, memahami urutan join, dan memberi planner statistik agar pilihannya makin tepat.