Data: Database dan SQL · 5/7

Index dan performa query

Kenapa query yang sama terasa instan di 100 baris tapi tidak terpakai di sejuta baris.

Baca 25 menit

Setelah pelajaran ini kamu bisa

Menjelaskan apa itu index dan apa biayanya
Membaca EXPLAIN QUERY PLAN dan mengenali full scan
Menentukan kolom mana yang layak diberi index
Menjelaskan kenapa sebuah index bisa diabaikan

Tanpa index, menemukan sebuah row berarti membaca setiap row sampai ada yang cocok. Itu full scan, dan itu tidak masalah untuk seratus row tapi tidak terpakai untuk sejuta row. Index adalah struktur terpisah yang terurut, yang memungkinkan database melompat langsung ke sana.

sql

CREATE INDEX bookings_venue_id ON bookings(venue_id);

-- Ask the database what it will actually do:
EXPLAIN QUERY PLAN SELECT * FROM bookings WHERE guest = ?;

Dijalankan terhadap SQLite proyek ini, rencana itu melaporkan `SCAN bookings` sebelum ada index, dan `SEARCH bookings USING INDEX bookings_guest (guest=?)` sesudahnya. SCAN berarti setiap row; SEARCH berarti ia melompat.

Besarnya perbedaannya layak dilihat sebagai pengukuran nyata. Pada tabel berisi 200.000 row di SQLite proyek ini, dua puluh pencarian lewat kolom teks tanpa index butuh 110,1 ms. Setelah menambahkan index pada kolom itu, dua puluh pencarian yang sama butuh 0,1 ms — sekitar 1700 kali lebih cepat, untuk satu baris SQL.

Coba sendiri

Kenapa selisihnya sebesar itu: scan bersifat linear, pencarian lewat index tidak. Jalankan dan perhatikan jumlah operasinya.

const SIZE = 200000;
const rows = [];
for (let i = 0; i < SIZE; i++) rows.push({ id: i, sku: "SKU-" + i, qty: i % 100 });

// --- full scan: check every row until it matches ---
function scan(rows, sku) {
  let comparisons = 0;
  for (const row of rows) {
    comparisons++;
    if (row.sku === sku) return { row: row, comparisons: comparisons };
  }
  return { row: null, comparisons: comparisons };
}

// --- an index: a Map from value to row, built once ---
const index = new Map();
for (const row of rows) index.set(row.sku, row);

function indexed(sku) {
  return { row: index.get(sku) ?? null, comparisons: 1 };
}

console.log("looking for the LAST row, 20 times:");

let t = performance.now();
let scanned = 0;
for (let i = 0; i < 20; i++) scanned = scan(rows, "SKU-199999").comparisons;
const scanMs = performance.now() - t;

t = performance.now();
for (let i = 0; i < 20; i++) indexed("SKU-199999");
const indexMs = performance.now() - t;

console.log("   scan:    " + scanMs.toFixed(1).padStart(7) + "ms   " + scanned + " comparisons each");
console.log("   indexed: " + indexMs.toFixed(1).padStart(7) + "ms   1 comparison each");
console.log("");
console.log("The scan does 200,000 comparisons per lookup. The index does one.");
console.log("That gap grows with the table; the index's does not.");
console.log("");
console.log("But the index cost something to build, and it must be kept updated:");
t = performance.now();
const rebuilt = new Map();
for (const row of rows) rebuilt.set(row.sku, row);
console.log("   building it: " + (performance.now() - t).toFixed(1) + "ms, and it holds " + rebuilt.size + " entries in memory");

Hasil

Tekan Jalankan untuk melihat hasilnya.

Ini jalan di browser kamu, di dalam sandbox. Apa pun yang kamu tulis di sini tidak bisa merusak situs.

Apa yang perlu diberi index

Beri index	Karena
Setiap foreign key	Kamu akan terus-menerus melakukan join padanya
Kolom di `WHERE` yang sering dipakai	Itulah gunanya index
Kolom yang sering kamu `ORDER BY`	Index yang terurut menghindari proses sorting
Kolom dengan constraint `UNIQUE`	Database memberi index padanya otomatis

Proyek ini memberi index pada tepat dua hal di luar primary key-nya: sessions(user_id) dan lesson_progress(user_id). Keduanya foreign key yang difilter oleh setiap query dashboard. Tidak ada lagi yang diberi index, karena tidak ada lagi yang di-query seperti itu.

Kapan index diabaikan

sql

-- These prevent the index on `name` from being used:

WHERE LOWER(name) = 'ana'          -- a function on the column
WHERE name LIKE '%ana'             -- a leading wildcard: no sorted prefix
WHERE CAST(id AS TEXT) = '42'      -- a type change

-- These can use it:
WHERE name = 'Ana'
WHERE name LIKE 'Ana%'             -- trailing wildcard is fine
WHERE id = 42

Index diurutkan berdasarkan nilai kolomnya. Bungkus kolomnya dengan sebuah function dan urutan itu tidak berlaku lagi, jadi database tidak punya pilihan selain melakukan scan.

Tugas praktik

Terhadap database proyek ini, jalankan EXPLAIN QUERY PLAN SELECT * FROM lesson_progress WHERE user_id = 1 dan catat apakah hasilnya SCAN atau SEARCH. Lalu jalankan hal yang sama untuk WHERE lesson_id = 'react/state' dan bandingkan. Jelaskan perbedaannya memakai index yang dideklarasikan di features/db/index.ts.

Hasil yang diharapkan

SEARCH untuk user_id, SCAN untuk lesson_id — hanya yang pertama punya index.