Rancang indeks

Pertanyaan

Indeks apa yang melayani query ini dengan murah, dan berapa harga yang dibayar penulisan?

Setelah baca ini, kamu bisa memilih urutan kolom indeks komposit dari bentuk query, menjelaskan kapan covering index menghindari akses ke tabel, dan menghitung kasar tambahan beban tulis.

Prasyarat dan contoh

Kamu sudah punya pola akses dengan frekuensi (M04.01) dan tahu bahwa beban dominan baca atau tulis mengubah pilihan (M04.02).

Tabel orders punya 10 juta baris. Query R2 dari M04.01 mengambil 20 order terbaru milik satu user:

SELECT id, status, total_cents, created_at
FROM orders WHERE user_id = 4217
ORDER BY created_at DESC LIMIT 20;

Tanpa indeks, database membaca semua 10 juta baris. Dengan indeks yang tepat, ia membaca sekitar 20 entri. Selisihnya bisa 100.000 kali lipat pada kerja baca, dan itu bedanya p95 200 ms dan p95 2 ms.

Mekanisme

B-tree. Indeks umum menyimpan key terurut dalam halaman, tersusun sebagai pohon. Satu halaman 8 KB bisa memuat beberapa ratus key, jadi percabangan (fanout) sekitar 300. Pohon tiga level sudah menjangkau sekitar 300 × 300 × 300 = 27 juta entri, dan empat level lebih dari 8 miliar. Mencari satu key itu 3-4 pembacaan halaman, dan level atas biasanya sudah di memori. Karena key terurut, pencarian rentang juga murah: cari awal, lalu baca ke samping.

Urutan kolom di indeks komposit. Indeks (user_id, created_at) mengurutkan dulu berdasarkan user_id, lalu dalam tiap user berdasarkan created_at. Akibatnya:

• WHERE user_id = 4217 ORDER BY created_at DESC LIMIT 20: database melompat ke user 4217 dan membaca 20 entri dari ujung. Tidak ada sort.
• WHERE created_at > '2026-10-01' saja: indeks nggak membantu, karena tanggal tersebar di tiap user. Indeks (created_at) terpisah dibutuhkan.

Aturan umum: kolom dengan kondisi kesamaan (=) di depan, lalu kolom rentang atau pengurutan. Urutan sebaliknya, (created_at, user_id), tidak bisa melayani query tadi tanpa nyisir rentang tanggal.

Selektivitas. Indeks berguna kalau mempersempit hasil banyak. Indeks pada status dengan empat nilai, di mana 90% baris bernilai dibayar, hampir nggak membantu untuk status = 'dibayar'. Untuk nilai langka seperti batal (1%), indeks parsial WHERE status = 'batal' kecil dan efektif.

Covering index. Kalau indeks memuat semua kolom yang dibutuhkan query, database tidak perlu membuka tabel. Contoh: CREATE INDEX ON orders (user_id, created_at DESC) INCLUDE (status, total_cents). Query R2 sekarang dijawab dari indeks saja (index-only scan). Harga: indeks lebih besar, dan di PostgreSQL index-only scan tetap perlu visibility map yang segar.

Biaya tulis. Tiap indeks adalah salinan terurut yang harus diperbarui di setiap insert, update kolom terindeks, dan delete. Tabel dengan 6 indeks berarti satu insert menyentuh 7 struktur (tabel plus 6 indeks). Pada 50 insert/detik, itu 350 penulisan struktur per detik, dan semuanya masuk WAL. Indeks juga memakan ruang dan memori buffer.

Saat rancangan ini gagal

• Urutan kolom terbalik. Indeks (created_at, user_id) dibuat "supaya mencakup keduanya", tapi query per user tetap menyisir. Cek urutan terhadap bentuk query, bukan terhadap kolom yang muncul.
• Fungsi di kolom terindeks. WHERE lower(email) = 'a@b.id' nggak memakai indeks pada email. Buat indeks pada ekspresi lower(email) atau simpan nilai ternormalisasi.
• Indeks di mana-mana. Setiap kolom diindeks "jaga-jaga". Tulis jadi lambat, ruang indeks melebihi ruang data, dan tak ada yang tahu mana yang dipakai.
• Tipe tidak cocok. Membandingkan kolom integer dengan string bisa memaksa konversi tiap baris dan mengabaikan indeks.
• Sort tanpa dukungan indeks. ORDER BY pada kolom yang tidak diindeks memaksa sort di memori atau disk untuk seluruh hasil filter.

Contoh tandingan: tabel log yang ditulis 5.000 baris/detik dan hampir nggak dibaca. Tiga indeks tambahan menggandakan kerja tulis untuk query yang jalan seminggu sekali. Lebih murah menjalankan query itu lambat di salinan analitik.

Latihan

Tabel events (200 juta baris) dengan kolom tenant_id, type, created_at, payload. Query utama: "event tipe login milik tenant 9 dalam 7 hari terakhir, terbaru dulu, 50 baris". Tabel menerima 800 insert/detik.

Rancang indeks dan sebutkan biayanya.

Lihat pembahasan+

Indeks: (tenant_id, type, created_at DESC).

Alasan urutan: tenant_id dan type pakai kesamaan, jadi di depan. created_at untuk rentang dan pengurutan, di belakang. Database melompat ke (9, 'login'), lalu membaca dari entri terbaru sampai 50 baris atau sampai melewati batas 7 hari. Tidak ada sort, dan yang dibaca kira- kira 50 entri, bukan jutaan.

Kalau query hanya butuh id dan created_at, tambahkan INCLUDE (id) agar index-only scan. payload tidak dimasukkan karena besar dan membengkakkan indeks.

Biaya: tiap insert sekarang menyentuh tabel dan satu indeks. Pada 800 insert/detik, itu 800 penulisan indeks per detik tambahan. Itu wajar. Ukuran indeks kasar 200 juta entri × sekitar 40 byte = sekitar 8 GB, jadi cek apakah muat di memori yang tersedia, atau setidaknya level atasnya.

Jawaban yang bagus menyebut kenapa (created_at, ...) di depan salah, dan menyebut biaya tulis dengan angka.

Terkait

• M01.02 Jelaskan beban kerja membantu memperkirakan rasio baca dan tulis yang menentukan jumlah indeks yang wajar.
• M04.01 menghasilkan pola akses yang jadi dasar indeks.
• M04.04 bahas cara membuktikan indeks benar-benar dipakai lewat rencana query.
• M09 Mesin penyimpanan bahas B-tree vs LSM lebih dalam.
• Lab L04 melatih memilih indeks untuk query nyata.

Catatan

• Asumsi: B-tree gaya PostgreSQL atau MySQL InnoDB. Fanout 300 itu perkiraan untuk key kecil.
• Yang nggak dibahas: indeks hash, GIN/GiST, indeks spasial (M13), dan indeks teks (M11).
• Angka ukuran indeks perkiraan kasar, bukan hasil ukur.
• Terakhir ditinjau: 26 Oktober 2026.