Biaya Pemeliharaan INDEX

Seluruh section ini kita sibuk membangun index — sekarang saatnya membayar tagihannya. Setiap index yang berdiri adalah struktur yang harus tetap benar setiap saat: begitu baris ditambahkan, nilainya berubah, atau dihapus, MySQL turut menyisipkan, menggeser, dan mencabut entri di dalam index. Pekerjaan pemeliharaan ini tidak pernah muncul di hasil query, tidak menghasilkan error, dan tidak terasa pada tabel latihan sepuluh baris — tetapi pada tabel transaksi yang menerima ribuan penulisan per detik dengan lima index, biaya itu berlipat lima pada setiap barisnya. Chapter penutup ini membuat biaya itu terlihat: kita membedah pekerjaan index pada tiap operasi DML, mengaudit tabel yang over-indexed, mencoba uji coba aman lewat invisible index, lalu menutup section dengan aturan praktis tentang index mana yang benar-benar layak hidup.

Setiap Penulisan Ikut Memelihara Semua Index #

Peta pekerjaannya bisa dirangkum dalam satu tabel. Perhatikan bahwa yang dikalikan bukan satu, melainkan jumlah index pada tabel:

Dua baris UPDATE layak dibedakan lebih tajam. Mengubah kolom biasa seperti stok hanya menyentuh barisnya; sedangkan mengubah kolom terindeks seperti harga memaksa index-nya ikut bekerja: entri dengan nilai lama dicabut dari posisinya, lalu entri dengan nilai baru disisipkan pada posisi terurut yang mungkin jauh berbeda. Kolom yang nilainya sering berubah, karenanya, lebih mahal diindeks daripada kolom yang stabil — pertimbangan yang kerap mengalahkan keinginan mengindeks kolom saldo atau stok yang berubah setiap transaksi.

Menyiapkan Tabel Latihan #

Tabel produk kali ini sengaja kita bebani tiga index sekaligus — satu primary key, satu unique, satu biasa — supaya setiap operasi DML berikutnya dapat kita baca dengan sadar: satu baris berubah, tiga struktur ikut bekerja:

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 produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  harga DECIMAL(10,2) NOT NULL,
  stok INT NOT NULL,
  UNIQUE KEY uq_nama (nama),
  INDEX idx_kategori (kategori),
  INDEX idx_harga (harga)
);

INSERT INTO produk (nama, kategori, harga, stok) VALUES
  ('Kopi Gayo',  'kopi',    55000.00, 10),
  ('Teh Melati', 'teh',     25000.00, 15),
  ('Kopi Toraja','kopi',    62000.00,  8),
  ('Gula Aren',  'pemanis', 15000.00, 20),
  ('Teh Hijau',  'teh',     27000.00, 12),
  ('Susu Kental','pemanis', 18000.00,  9);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

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

SHOW INDEX FROM produk;

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 |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| produk |          0 | PRIMARY      |            1 | id          | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| produk |          0 | uq_nama      |            1 | nama        | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| produk |          1 | idx_kategori |            1 | kategori    | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| produk |          1 | idx_harga    |            1 | harga       | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

Audit pembuka ini mencatat empat index: PRIMARY pada id, uq_nama pada nama, idx_kategori pada kategori, dan idx_harga pada harga. Kolom stok sengaja dibiarkan tanpa index — ia akan menjadi pembanding pada latihan UPDATE di bawah. Enam baris sudah tercatat, dan masing-masing sudah menyisipkan empat entri index pada saat penyisipan.

INSERT di Bawah Empat Index #

Penambahan produk baru terasa seperti satu statement kecil:

INSERT INTO produk (nama, kategori, harga, stok) VALUES
  ('Kopi Luwak', 'kopi', 150000.00, 3);

SELECT nama, kategori, harga, stok FROM produk ORDER BY id;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

+-------------+----------+-----------+------+
| nama        | kategori | harga     | stok |
+-------------+----------+-----------+------+
| Kopi Gayo   | kopi     |  55000.00 |   10 |
| Teh Melati  | teh      |  25000.00 |   15 |
| Kopi Toraja | kopi     |  62000.00 |    8 |
| Gula Aren   | pemanis  |  15000.00 |   20 |
| Teh Hijau   | teh      |  27000.00 |   12 |
| Susu Kental | pemanis  |  18000.00 |    9 |
| Kopi Luwak  | kopi     | 150000.00 |    3 |
+-------------+----------+-----------+------+
7 rows in set (0.00 sec)

Tujuh baris kini tercatat. Di balik layar, satu penulisan baris disertai empat penyisipan entri: nilai 7 masuk ke index PRIMARY, 'Kopi Luwak' ke uq_nama, 'kopi' ke idx_kategori, dan 150000.00 ke idx_harga — masing-masing di posisi terurut yang tepat. Pada tabel dengan sepuluh index, perbandingannya menjadi satu baris ditambah sepuluh penyisipan; itulah arti kalimat “biaya tulis berlipat mengikuti jumlah index”.

UPDATE: Kolom Terindeks versus Kolom Biasa #

Kenaikan harga Kopi Gayo menyentuh kolom yang terindeks idx_harga:

UPDATE produk SET harga = 58000.00 WHERE nama = 'Kopi Gayo';

SELECT nama, harga FROM produk WHERE nama = 'Kopi Gayo';

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+-----------+----------+
| nama      | harga    |
+-----------+----------+
| Kopi Gayo | 58000.00 |
+-----------+----------+
1 row in set (0.00 sec)

Harga berubah menjadi 58000.00, dan pekerjaannya lebih panjang dari sekadar mengganti satu angka: lookup WHERE nama dilayani uq_nama, lalu di sisi index idx_harga, entri 55000.00 milik produk ini dicabut dan entri 58000.00 disisipkan pada posisi urut barunya — di antara Gula Aren dan Teh Hijau. Bandingkan dengan pembaruan stok yang sama-sama rutin terjadi di toko:

UPDATE produk SET stok = 25 WHERE nama = 'Teh Melati';

SELECT nama, stok FROM produk WHERE nama = 'Teh Melati';

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+------------+------+
| nama       | stok |
+------------+------+
| Teh Melati |   25 |
+------------+------+
1 row in set (0.00 sec)

Stok Teh Melati naik menjadi 25 tanpa satu pun pekerjaan index tambahan — kolom stok memang tidak diindeks, sehingga keempat index tidak berubah. Dua latihan ini adalah versi nyata dari tabel pemeliharaan di awal chapter: kolom terindeks membayar pajak pada setiap perubahan, kolom biasa tidak.

DELETE ikut Membersihkan Empat Struktur #

Penghapusan menutup lingkaran pemeliharaan:

DELETE FROM produk WHERE nama = 'Susu Kental';

SELECT COUNT(*) AS jumlah_produk FROM produk;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

+---------------+
| jumlah_produk |
+---------------+
|             6 |
+---------------+
1 row in set (0.00 sec)

Enam baris tersisa. Di sisi index, empat entri yang mengacu pada Susu Kental — di PRIMARY, uq_nama, idx_kategori, dan idx_harga — ikut dicabut supaya struktur tetap akurat. Tidak ada yang bisa ditunda di sini: index yang memuat entri menunjuk baris yang sudah tidak ada adalah index yang berbohong, dan MySQL menjaga kejujuran ini secara atomik bersama penghapusan barisnya.

Audit Index dan Uji Coba Aman Sebelum Menghapus #

Pertanyaan manajerial setelah sadar biaya: index mana yang sebenarnya tidak pernah dipakai? Jawaban ideal datang dari statistik pemakaian index yang dikumpulkan performance_schema beserta skema sys — namun fasilitas itu harus aktif di server dan tidak selalu tersedia, sehingga jalur paling portabel tetap audit manual: petakan seluruh index lewat SHOW INDEX FROM produk, lalu cocokkan satu per satu dengan query nyata yang berjalan di aplikasi. Index yang tidak muncul di kolom key EXPLAIN mana pun dari query dominan kita adalah kandidat kuat penghapusan — ia hanya menagih biaya tanpa pernah dibayar balik.

Sebelum benar-benar menghapus, MySQL menyediakan jaring pengaman yang elegan: invisible index. Index dibuat tidak terlihat optimizer tanpa dibongkar — cukup dibalikkan kembali bila ternyata tersisa query yang membutuhkannya:

ALTER TABLE produk ALTER INDEX idx_harga INVISIBLE;

Output yang Dihasilkan:

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

EXPLAIN SELECT * FROM produk WHERE harga > 50000;

Output yang Dihasilkan:

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | produk | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Selama idx_harga invisible, rencana query rentang harga jatuh ke type = ALL — bukti hidup bahwa query ini memang bergantung pada index tersebut. Jika dalam masa uji semua query dominan tetap sehat tanpa index ini, barulah DROP INDEX menjadi keputusan yang teruji; jika ada yang tersendat, satu statement ALTER TABLE produk ALTER INDEX idx_harga VISIBLE; mengembalikan semuanya tanpa pernah membangun ulang struktur. Uji coba menjadi murah, terbalikkan, dan tanpa risiko kehilangan index.

Aturan Praktis Menutup Section #

Seluruh pembelajaran section indexes akhirnya mengerucut ke sejumlah keputusan sehari-hari:

• Indeks kolom yang benar-benar menjadi jalur query — kolom yang rutin muncul di WHERE, JOIN ... ON, ORDER BY, dan GROUP BY. Kolom yang tak pernah difilter tidak butuh index.
• Dengarkan EXPLAIN, bukan tebakan — index yang tak pernah muncul di kolom key query dominan adalah beban; tulis ulang query yang mematikan index sebelum menambah index baru.
• Hapus index yang tak terpakai setelah diuji invisible — struktur mati hanya menagih biaya tulis dan ruang simpan.
• Jangan mengindeks segalanya — setiap index tambahan melipatgandakan pekerjaan setiap INSERT, UPDATE kolom terindeks, dan DELETE.
• Utamakan kolom stabil untuk index — nilai yang berubah di setiap transaksi (saldo, stok) paling mahal dipelihara; pertimbangkan apakah filter-nya memang perlu index penuh.

Membersihkan Database Latihan #

Database belajar_index kita jatuhkan kembali, menutup pula seluruh section ini:

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
• Secondary Indexes — MySQL 8.4 Reference Manual
• Optimization — MySQL 8.4 Reference Manual

Biaya pemeliharaan kini masuk hitungan: empat entri per baris pada tabel empat index, pajak ganda pada kolom terindeks yang berubah, dan mekanisme audit-uji-hapus untuk index yang menganggur. Dengan itu, perlengkapan index kita lengkap — dari konsep B-tree hingga tagihan pemeliharaannya. Section berikutnya berpindah ke objek database yang sama-sama mempercepat akses data namun hidup di lapisan query: view, tabel virtual yang menyimpan definisi query dan bisa dipakai berulang seperti tabel biasa.