Apakah index dalam SQL? Bila anda patut dan tidak patut menggunakannya?

Question

Accepted Answer

Suatu **index** ialah struktur data (biasanya **B-tree**) yang membolehkan pangkalan data mencari baris dengan cepat **tanpa mengimbas keseluruhan jadual** — seperti indeks dalam buku. Ia mempercepatkan carian, penapisan, join, dan pengisihan secara mendadak pada lajur yang diindeks, dengan kos storan tambahan dan tulisan yang lebih perlahan.

## Bagaimana index membantu

```sql
-- without an index on email, this scans EVERY row (slow on a big table) — O(n)
SELECT * FROM users WHERE email = 'ann@x.com';

-- create an index → the database can find the row directly — O(log n)
CREATE INDEX idx_users_email ON users(email);
-- now the same query uses the index → dramatically faster
```

Tanpa index, pangkalan data melakukan **full table scan** (memeriksa setiap baris). Dengan index, ia menavigasi B-tree untuk mencari baris yang sepadan dalam masa logaritma — perbezaan antara mengimbas berjuta-juta baris dan beberapa carian sahaja.

## Bila menggunakan index

```text
✓ Columns in WHERE clauses (frequently filtered)
✓ Columns used in JOINs (foreign keys especially)
✓ Columns in ORDER BY (sorting can use the index)
✓ Columns with high SELECTIVITY (many distinct values — email, id)
✓ Primary keys (auto-indexed) and unique constraints
```

## Bila TIDAK menggunakan index (pertukaran timbal balas)

```text
✗ Columns rarely queried — wasted storage and write overhead
✗ Small tables — a full scan is already fast; an index adds no benefit
✗ Low-selectivity columns (e.g. a boolean with 2 values) — index barely helps
✗ Write-heavy columns — EVERY INSERT/UPDATE/DELETE must also update the indexes
  → too many indexes SLOW DOWN writes significantly
```

Index tidak percuma: ia **menggunakan storan** dan **memperlahankan tulisan** (setiap pengubahsuaian mesti mengemas kini index). Mengindeks secara berlebihan menjejaskan prestasi tulisan.

## Composite dan jenis index lain

```sql
CREATE INDEX idx_users_country_name ON users(country, name);   -- COMPOSITE (multi-column)
-- helps WHERE country = ? AND name = ?, and WHERE country = ? (leftmost prefix)
-- but NOT WHERE name = ? alone (order matters!)

CREATE UNIQUE INDEX ...      -- enforces uniqueness
-- also: partial indexes, covering indexes, GIN/GiST (Postgres) for special data
```

## Mengapa ia penting

Index ialah salah satu topik paling penting dalam prestasi pangkalan data — ia adalah alat utama untuk menjadikan pertanyaan pantas, dan memahami bila dan bagaimana menggunakannya adalah pengetahuan penting bagi sesiapa yang bekerja dengan pangkalan data pada skala besar.

Nilai terasnya mendadak: tanpa index, pertanyaan yang menapis, join, atau mengisih pada lajur memerlukan **full table scan** (memeriksa setiap baris — sangat perlahan apabila data bertambah), manakala index menukar ini kepada carian logaritma yang pantas — selalunya perbezaan antara pertanyaan yang mengambil milisaat berbanding saat atau minit.

Memahami **bila perlu mengindeks** (lajur WHERE yang kerap ditapis, lajur JOIN/foreign key, lajur ORDER BY, lajur dengan kebolehpilihan tinggi) adalah asas untuk prestasi pertanyaan.

Sama pentingnya ialah memahami **pertukaran timbal balas dan bila TIDAK perlu mengindeks**: index menggunakan storan dan, yang penting, **memperlahankan tulisan** (setiap INSERT/UPDATE/DELETE mesti mengemas kini semua index), jadi mengindeks secara berlebihan menjejaskan prestasi tulisan, dan index menambah sedikit nilai pada jadual kecil atau lajur kebolehpilihan rendah.

Keseimbangan ini — mengindeks apa yang dipertanyakan sambil tidak mengindeks secara berlebihan — ialah kemahiran utama.

Mengetahui tentang **composite index** (berbilang lajur, di mana susunan lajur penting untuk pertanyaan mana yang dibantu) melengkapkan pengetahuan index yang praktikal.

Memandangkan prestasi pertanyaan adalah kritikal untuk aplikasi sebenar, dan memandangkan pengindeksan yang betul (index yang betul pada lajur yang betul, menyeimbangkan kelajuan baca dengan kos tulisan dan storan) ialah pengoptimuman pangkalan data yang paling berkesan, menguasai index — bagaimana ia berfungsi, bila menggunakannya, pertukaran timbal balas tulis/storan, dan composite index — ialah pengetahuan teras bernilai tinggi untuk pangkalan data berprestasi dan salah satu topik pangkalan data yang paling penting dan kerap diuji.