Trik Query Optimization MySQL di Atas 10 Juta Baris: Partisi Tabel, Covering Index, dan Analisis Slow Log

Di sistem billing ISP dan manajemen log jaringan, tabel transaksi bulanan bisa membengkak hingga puluhan juta baris dalam kurun 1 tahun. Tanda-tanda awal masalah: dashboard keuangan mulai loading lama, CPU database server melonjak ke 100%, dan muncul pesan Lock wait timeout exceeded.

1. Rahasia Covering Index (Using Index)

Jika database harus membaca data baris (row data) dari disk untuk setiap record yang lolos filter, I/O disk akan menjadi leher botol (bottleneck). Covering Index adalah index yang memuat seluruh kolom yang dibutuhkan dalam klausa SELECT, WHERE, dan ORDER BY.

-- Query Laporan Pendapatan:
SELECT customer_id, SUM(amount) FROM billing_invoices 
WHERE status = 'paid' AND payment_date BETWEEN '2026-10-01' AND '2026-10-31' 
GROUP BY customer_id;

-- Index Optimal (Covering Index):
CREATE INDEX idx_perf_invoices ON billing_invoices (status, payment_date, customer_id, amount);

2. Partisi Tabel Berdasarkan Range Tanggal (Table Partitioning)

Alih-alih membiarkan engine InnoDB menyisir satu tabel raksasa 50GB, pecah tabel secara fisik berdasarkan tahun atau bulan menggunakan RANGE Partitioning:

ALTER TABLE billing_invoices PARTITION BY RANGE (YEAR(payment_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);