Konsep INDEX

Skema database yang kita rancang di section sebelumnya sudah rapi secara struktur, namun struktur yang rapi belum menjamin pencarian yang cepat. Query seperti WHERE penulis = 'Tania Rustam' pada tabel berisi jutaan baris memaksa MySQL memeriksa baris demi baris dari awal hingga akhir — cara kerja yang disebut full table scan. Index adalah jawaban untuk masalah ini: sebuah struktur data tambahan yang menyimpan nilai kolom dalam bentuk terurut sehingga posisi baris yang dicari bisa ditemukan tanpa membaca seluruh tabel. Pada chapter ini kita membedah konsep index dari tiga sisi: apa sebenarnya struktur itu, bagaimana bentuk pohon terurutnya bekerja, dan harga apa yang harus dibayar sebagai ganti pencarian yang cepat.

Masalah Pencarian Tanpa Index #

Kita mulai dengan tabel kecil supaya perilakunya mudah diamati. Database latihan belajar_index akan menemani seluruh chapter di section ini, lalu dijatuhkan kembali di akhir setiap chapter agar setiap materi selalu mulai dari keadaan bersih:

DROP DATABASE IF EXISTS belajar_index;
CREATE DATABASE belajar_index;
USE belajar_index;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE buku_katalog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(60) NOT NULL,
  harga DECIMAL(10,2) NOT NULL,
  tahun_terbit SMALLINT UNSIGNED NOT NULL
);

INSERT INTO buku_katalog (judul, penulis, harga, tahun_terbit) VALUES
  ('Algoritma dan Struktur Data', 'Tania Rustam', 85000.00, 2021),
  ('Pola Desain Aplikasi',        'Tania Rustam', 92000.00, 2022),
  ('Rekayasa Perangkat Lunak',    'Tania Rustam', 78000.00, 2020),
  ('Basis Data Relasional',       'Budi Santoso', 88000.00, 2019),
  ('Optimasi Query Praktis',      'Budi Santoso', 95000.00, 2023),
  ('Jaringan Komputer Modern',    'Siti Rahayu',  73000.00, 2021),
  ('Keamanan Aplikasi Web',       'Siti Rahayu',  87000.00, 2022),
  ('Pemrograman Fungsional',      'Andi Wijaya',  81000.00, 2020),
  ('Machine Learning Dasar',      'Dewi Lestari', 99000.00, 2023),
  ('Statistika untuk Data',       'Dewi Lestari', 69000.00, 2018),
  ('Arsitektur Mikroservis',      'Rina Kartika', 105000.00, 2024),
  ('Pengujian Perangkat Lunak',   'Rina Kartika', 76000.00, 2019);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Mari kita cari seluruh buku karya Tania Rustam:

SELECT judul, harga, tahun_terbit
FROM buku_katalog
WHERE penulis = 'Tania Rustam'
ORDER BY judul;

Output yang Dihasilkan:

+-----------------------------+----------+--------------+
| judul                       | harga    | tahun_terbit |
+-----------------------------+----------+--------------+
| Algoritma dan Struktur Data | 85000.00 |         2021 |
| Pola Desain Aplikasi        | 92000.00 |         2022 |
| Rekayasa Perangkat Lunak    | 78000.00 |         2020 |
+-----------------------------+----------+--------------+
3 rows in set (0.00 sec)

Tiga baris hasil muncul dengan benar, dan pada dua belas baris waktu pencarian memang tidak terasa. Yang tidak terlihat adalah cara MySQL memperolehnya: kolom penulis belum punya struktur pencarian khusus, sehingga server membaca seluruh dua belas baris dan membandingkan nilainya satu per satu dengan 'Tania Rustam'. Pada tabel dua belas baris perilaku ini tidak masalah; pada tabel transaksi dengan sepuluh juta baris, setiap query filter akan membaca sepuluh juta baris juga — meskipun hasilnya hanya tiga baris. Inilah titik di mana index masuk ke dalam gambar.

Index Adalah Struktur Data Terpisah #

Cara paling cepat memahami index adalah mengingat cara kita mencari istilah teknis di buku tebal. Alih-alih membaca lima ratus halaman satu per satu, kita membuka daftar istilah di bagian belakang buku: istilah tersusun alfabetis, dan di sebelahnya tercantum nomor halaman tempat istilah itu dibahas. Index di MySQL bekerja dengan ide yang sama — kolom yang di-index disimpan dalam struktur terpisah yang terurut, dan setiap nilai di dalamnya menunjuk ke baris tabel aslinya.

Analogi buku ini punya batas yang perlu kita pegang sejak awal. Indeks buku ditulis sekali oleh penyusun dan tidak berubah; index database justru dipelihara secara otomatis — setiap kali baris ditambah, diubah, atau dihapus, MySQL ikut memperbarui struktur index-nya. Konsekuensinya juga terbalik: indeks buku tidak memakan ruang ketika buku diperbarui, sedangkan index database selalu mengikuti ukuran dan perubahan datanya. Batas analogi ini akan terasa nyata ketika kita membahas biaya pemeliharaan di akhir section.

Perintah berikut membuat index pada kolom penulis:

CREATE INDEX idx_penulis ON buku_katalog(penulis);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM buku_katalog;

Output yang Dihasilkan:

+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| buku_katalog |          0 | PRIMARY     |            1 | id          | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| buku_katalog |          1 | idx_penulis |            1 | penulis     | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

Output SHOW INDEX memperlihatkan dua baris. Baris PRIMARY hadir sejak tabel diciptakan — kolom primary key otomatis mendapat index tanpa perintah tambahan. Baris idx_penulis adalah index yang baru saja kita buat: kolom Non_unique bernilai 1 karena nilai penulis boleh kembar, Key_name memuat nama index, dan Column_name menunjuk kolom penulis yang menjadi kuncinya. Nama idx_penulis mengikuti konvensi idx_nama_kolom yang akan kita pakai konsisten sepanjang course. Setelah index ini ada, query pencarian yang sama tetap menghasilkan tiga baris yang identik:

SELECT judul, harga, tahun_terbit
FROM buku_katalog
WHERE penulis = 'Tania Rustam'
ORDER BY judul;

Output yang Dihasilkan:

+-----------------------------+----------+--------------+
| judul                       | harga    | tahun_terbit |
+-----------------------------+----------+--------------+
| Algoritma dan Struktur Data | 85000.00 |         2021 |
| Pola Desain Aplikasi        | 92000.00 |         2022 |
| Rekayasa Perangkat Lunak    | 78000.00 |         2020 |
+-----------------------------+----------+--------------+
3 rows in set (0.00 sec)

Hasil yang tidak berubah ini penting untuk dicatat: index tidak pernah mengubah makna query atau isi tabel. Yang berubah hanyalah jalur yang ditempuh MySQL untuk menemukan barisnya — dari “baca semua baris lalu saring” menjadi “lompat langsung ke posisi nilai yang dicari”.

Bentuk Pohon Terurut: Konsep B-tree #

Struktur yang dipakai index MySQL untuk kolom biasa adalah B-tree — pohon pencarian yang seluruh nilainya terurut. Prinsip kerjanya bisa dipahami lewat satu sifat saja: data yang terurut memungkinkan pencarian seperti mencari kata di kamus. Untuk mencari kata “kompilator”, kita membuka bagian K, lalu membandingkan posisi dan membuang separuh halaman setiap kali membuka — bukan membaca dari halaman satu. Pencarian semacam ini hanya butuh sekitar log₂ N langkah untuk N data: pada satu juta baris terurut, sekitar dua puluh langkah perbandingan sudah cukup untuk menemukan satu nilai.

Bentuk sederhananya dapat kita gambarkan sebagai pohon bertingkat, dengan nilai-nilai kecil di kiri dan besar di kanan:

Mari kita telusuri cara kerjanya untuk mencari nilai 58: dari simpul akar, nilai 58 dibandingkan dengan pemisah 40 dan 80 — karena 58 berada di antara keduanya, pencarian bergerak ke cabang tengah. Di daun tengah yang berisi enam nilai terurut, posisi 58 ditemukan dengan perbandingan singkat, dan dari titik itu index menunjuk baris tabel aslinya. Cabang yang tidak relevan tidak pernah dibaca sama sekali — itulah sumber kecepatannya. Daun-daun juga terhubung satu sama lain secara berurutan, sehingga rentang nilai seperti WHERE harga > 58000 cukup ditemukan posisi awalnya lalu digeret searah daun berikutnya.

Index Bukan Tabel #

Salah paham yang paling umum adalah menganggap index sebagai “tabel cadangan”. Kenyataannya, index hanya menyimpan dua hal: nilai kunci dari kolom yang di-index, dan penunjuk ke baris aslinya. Kolom-kolom lain seperti judul, harga, dan tahun_terbit tetap hanya hidup di tabel buku_katalog — index idx_penulis tidak menduplikasinya. Ketika index menemukan nilai Tania Rustam, MySQL masih harus berpindah ke tabel untuk mengambil kolom-kolom lain yang diminta SELECT; perjalanan tambahan ini akan kita lihat implikasinya pada chapter tentang prefix index.

Konsekuensi praktisnya ada dua. Pertama, satu tabel boleh memiliki banyak index sekaligus — masing-masing melayani kolom atau kombinasi kolom yang berbeda — dan seluruhnya menunjuk ke baris-baris yang sama. Kedua, menghapus sebuah index tidak pernah menghilangkan data: yang hilang hanya jalur pencarian cepatnya, sementara seluruh baris dan kolom tetap utuh. Perintah SHOW INDEX FROM buku_katalog di atas sebenarnya sudah memperlihatkan keadaan ini — dua index, satu tabel.

Trade-off: Baca Menjadi Cepat, Tulis Menjadi Lebih Berat #

Index bukan fasilitas gratis yang sebaiknya dipasang di semua kolom. Setiap kelebihannya dibayar dari dua sisi, dan keseimbangan inilah yang menentukan keputusan desain kita:

Dua baris pertama adalah keuntungan dan harga ruangnya; dua baris berikutnya adalah sisi yang jarang diperhitungkan pemula. Tabel dengan lima index akan menulis lima entri tambahan setiap kali satu baris disisipkan — pekerjaan yang tidak terlihat pada tabel latihan kecil kita, tetapi terasa pada tabel yang menerima ribuan transaksi per detik. Prinsip praktisnya sederhana: index sepadat mungkin pada kolom yang benar-benar menjadi jalur pencarian, dan tidak lebih dari itu.

Dengan konsep yang kini kokoh — struktur terpisah, terurut, menunjuk baris, dengan biaya tulis sebagai gantinya — langkah berikutnya adalah praktik: bagaimana membuat, menamai, menginspeksi, dan menghapus index dengan perintah DDL yang tepat.

Membersihkan Database Latihan #

Database belajar_index kita jatuhkan kembali agar chapter berikutnya bisa memulai dari keadaan yang sama:

DROP DATABASE belajar_index;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

Referensi #

• How MySQL Uses Indexes — MySQL 8.4 Reference Manual
• Column Indexes — MySQL 8.4 Reference Manual
• B-Tree and Hash Indexes — MySQL 8.4 Reference Manual

Pencarian cepat kini punya fondasi konseptualnya: index menyimpan kunci terurut yang menunjuk baris, menukar biaya tulis dengan kecepatan baca. Fondasi ini akan langsung kita pakai di chapter berikutnya, mulai dari perintah CREATE INDEX, pembacaan SHOW INDEX, hingga pembersihan index dengan DROP INDEX.