Mengukur dampak indeks komposit pada query transaksi

Latar Belakang & Setup Eksperimen

"Query-nya lambat di production." Keluhan ini hampir selalu berujung pada satu dari dua penyebab: desain query yang suboptimal, atau indeks yang hilang atau salah disusun. Eksperimen ini mendokumentasikan dampak nyata indeks komposit pada workload transaksi ritel — bukan teori, tapi angka dari tabel dengan data sesungguhnya.

Lingkungan Uji

Database: MySQL 8.0.36
Mesin: 4 vCPU, 8 GB RAM, SSD NVMe
Dataset: 2.000.000 baris transaksi ritel (2022–2024)
Buffer pool: didinginkan (cold cache) sebelum setiap pengukuran

SQL — Struktur tabel uji

CREATE TABLE transaksi (
  id          BIGINT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED  NOT NULL,
  toko_id     INT UNSIGNED     NOT NULL,
  status      VARCHAR(20)      NOT NULL,
  total       DECIMAL(15, 2)  NOT NULL,
  created_at  DATETIME         NOT NULL
) ENGINE=InnoDB;

-- Distribusi data:
-- 2.000.000 baris, tahun 2022-2024
-- ~12.000 user_id unik
-- ~500 toko_id unik
-- status: 72% selesai, 18% dibatalkan, 10% proses

Query yang Diuji

Tiga query dipilih untuk mewakili pola akses yang paling umum pada tabel transaksi:

Query A — Ringkasan transaksi per user per tahun

SELECT
    status,
    COUNT(*)       AS jumlah,
    SUM(total)     AS total_nilai
FROM  transaksi
WHERE user_id   = 5823
  AND created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY status;

Query B — 50 transaksi terbaru dari toko tertentu

SELECT id, user_id, total, created_at
FROM  transaksi
WHERE toko_id = 42
  AND status  = 'selesai'
ORDER BY created_at DESC
LIMIT 50;

Query C — Volume harian toko (dashboard analitik)

SELECT
    DATE(created_at)  AS tanggal,
    COUNT(*)          AS jumlah,
    SUM(total)        AS omzet
FROM  transaksi
WHERE toko_id    =  42
  AND status     = 'selesai'
  AND created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY tanggal;

Baseline: Query Tanpa Indeks

Kondisi awal: hanya PK index (id) yang ada. Semua query dipaksa full table scan — membaca seluruh 2 juta baris untuk setiap eksekusi.

Output EXPLAIN untuk Query A menunjukkan:

EXPLAIN — Query A tanpa indeks (full scan)

+----+-------------+-----------+------+------+----------+-----------+
| id | select_type | table     | type | key  | rows     | Extra     |
+----+-------------+-----------+------+------+----------+-----------+
|  1 | SIMPLE      | transaksi | ALL  | NULL | 1,998,742| Using where;
|    |             |           |      |      |          | Using temporary;
|    |             |           |      |      |          | Using filesort |
+----+-------------+-----------+------+------+----------+-----------+

-- type = ALL  → full table scan
-- rows ≈ 2 juta  → seluruh tabel dibaca
-- "Using temporary; Using filesort"  → GROUP BY tidak bisa pakai index

Query	Waktu Eksekusi	Baris Diperiksa
A	4.82 s	2,000,000
B	5.14 s	2,000,000
C	5.31 s	2,000,000

🔴

5 detik per query adalah bencana untuk aplikasi web interaktif. Dengan asumsi 100 request/menit saja, tabel transaksi akan menjadi bottleneck yang memblokir koneksi database.

Membuat Indeks Komposit

Analisis pola query menghasilkan dua indeks komposit. Urutan kolom dipilih berdasarkan aturan leftmost prefix dan prinsip: kolom equality filter mendahului kolom range filter.

SQL — Membuat dua indeks komposit

-- Untuk Query A: filter equality user_id, lalu range created_at
CREATE INDEX idx_trx_user_tgl
    ON transaksi (user_id, created_at);

-- Untuk Query B & C: filter equality toko_id + status, lalu range/sort created_at
CREATE INDEX idx_trx_toko_status_tgl
    ON transaksi (toko_id, status, created_at);

-- Cek index yang terbentuk
SHOW INDEX FROM transaksi;

ℹ️

Pembuatan index pada tabel 2 juta baris memakan waktu ±45 detik di lingkungan uji. Di MySQL, operasi ini berjalan ALGORITHM=INPLACE secara default dan mengizinkan DML concurrently. Pada production, pertimbangkan gh-ost atau pt-online-schema-change untuk tabel yang lebih besar.

Hasil EXPLAIN & Benchmark

Setelah indeks dibuat, EXPLAIN untuk Query A berubah drastis:

EXPLAIN — Query A setelah indeks

+----+-------------+-----------+-------+---------------------+-------+----------+--------------------------+
| id | select_type | table     | type  | key                 | rows  | filtered | Extra                    |
+----+-------------+-----------+-------+---------------------+-------+----------+--------------------------+
|  1 | SIMPLE      | transaksi | range | idx_trx_user_tgl    | 1,247 |  100.00  | Using where; Using index |
+----+-------------+-----------+-------+---------------------+-------+----------+--------------------------+

-- type = range  → hanya membaca range tertentu dari index
-- key = idx_trx_user_tgl  → index dipakai!
-- rows ≈ 1.247  → turun dari 2 JUTA menjadi 1.247 baris
-- "Using index"  → bisa baca langsung dari index (partially covering)

Query	Tanpa Indeks	Dengan Indeks	Speedup	Baris Diperiksa
A (user + tanggal)	4.82 s	0.009 s	535×	1,247
B (toko + status, LIMIT)	5.14 s	0.004 s	1.285×	50
C (toko + status, agregasi)	5.31 s	0.012 s	443×	18,540

Penurunan waktu eksekusi rata-rata 750 kali lipat untuk ketiga query. Query B sangat cepat karena kombinasi indeks + LIMIT 50 — optimizer hanya perlu membaca 50 baris dari index, lalu berhenti.

Aturan Urutan Kolom (Leftmost Prefix Rule)

Ini adalah aturan paling penting dalam desain indeks komposit dan paling sering disalahpahami. Sebuah index (a, b, c) efektif hanya jika query dimulai dari kolom paling kiri.

SQL — Index (toko_id, status, created_at): mana yang pakai?

-- ✓ Digunakan: mulai dari leftmost (toko_id)
WHERE toko_id = 42
WHERE toko_id = 42 AND status = 'selesai'
WHERE toko_id = 42 AND status = 'selesai' AND created_at >= '2024-01-01'

-- ✗ Tidak digunakan: melompati kolom pertama
WHERE status = 'selesai'                  -- skip toko_id → full scan
WHERE created_at >= '2024-01-01'         -- skip toko_id → full scan
WHERE status = 'selesai' AND created_at >= '2024-01-01'  -- skip toko_id → full scan

-- ⚠ Digunakan sebagian: berhenti di kolom range
WHERE toko_id = 42 AND created_at >= '2024-01-01'
-- Index dipakai untuk toko_id (equality), lalu created_at diabaikan index
-- karena ada "gap" di kolom status — MySQL harus filter manual

💡

Aturan praktis untuk urutan kolom:
1. Kolom dengan equality filter (=, IN) didahulukan
2. Kolom dengan range filter (>, <, BETWEEN) diletakkan di akhir
3. Di antara kolom equality, urutkan dari selectivity tertinggi (kolom paling unik) ke terendah

Covering Index: Eliminasi Table Lookup

Sebuah indeks disebut covering index jika semua kolom yang dibutuhkan query (SELECT, WHERE, ORDER BY, GROUP BY) sudah tercakup di dalam index. MySQL tidak perlu membaca tabel sama sekali — cukup baca index. Ini ditandai dengan Using index di kolom Extra EXPLAIN (tanpa "Using where" tambahan).

SQL — Covering index untuk Query B

-- Query B membutuhkan kolom: toko_id, status, created_at, id, user_id, total
-- Tambahkan kolom yang di-SELECT ke dalam index
CREATE INDEX idx_trx_covering
    ON transaksi (toko_id, status, created_at, user_id, total);
-- 'id' (PK) selalu ada di setiap index InnoDB secara implisit

-- EXPLAIN setelah covering index:
-- Extra: "Using index"   → tidak menyentuh tabel sama sekali
-- Query B turun dari 0.004s  →  0.0008s (5× lebih cepat lagi)

⚠️

Covering index yang terlalu lebar (banyak kolom) memperburuk performa INSERT/UPDATE/DELETE dan menggunakan lebih banyak memori buffer pool. Gunakan hanya untuk query yang terbukti menjadi bottleneck.

Kapan Indeks Komposit Justru Merugikan

Indeks bukan obat ajaib. Ada skenario di mana indeks komposit kontraproduktif:

Tabel kecil (<10.000 baris): MySQL optimizer sering memilih full scan yang lebih efisien karena overhead traversal B-tree lebih mahal dari manfaatnya
Tabel sangat heavy-write: Setiap INSERT/UPDATE/DELETE harus memperbarui semua index. Tabel log atau audit dengan jutaan insert per hari bisa terhambat oleh terlalu banyak index
Selectivity rendah pada kolom pertama: Jika kolom pertama hanya punya 2–3 nilai (misalnya status), optimizer mungkin mengabaikan index dan memilih full scan
Kolom dengan fungsi dalam WHERE: WHERE YEAR(created_at) = 2024 tidak bisa menggunakan index pada created_at — gunakan WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

SQL — Pola yang membatalkan penggunaan index

-- ✗ Fungsi pada kolom index → index tidak terpakai
WHERE YEAR(created_at) = 2024
WHERE LOWER(status) = 'selesai'
WHERE user_id + 0 = 5823

-- ✓ Equivalent tapi bisa pakai index
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
WHERE status = 'selesai'        -- pastikan kolasi case-insensitive
WHERE user_id = 5823

Ringkasan

Indeks komposit yang dirancang dengan baik adalah salah satu optimasi dengan rasio manfaat-biaya terbaik dalam ekosistem database relasional. Dari eksperimen ini:

Gunakan EXPLAIN sebelum membuat index — pahami pola akses query terlebih dahulu
Urutan kolom: equality filter di depan, range filter di belakang
Kolom selectivity tinggi umumnya lebih baik di posisi pertama untuk equality filter
Pertimbangkan covering index hanya untuk query bottleneck yang teridentifikasi
Jangan menambahkan fungsi pada kolom yang di-index dalam WHERE clause
Monitor index yang tidak pernah dipakai dengan sys.schema_unused_indexes di MySQL
Gunakan pt-online-schema-change atau gh-ost untuk ALTER TABLE di production

Kembali ke Blog Praktik di SQL Lab