Menjinakkan Query Lambat di Tabel Legacy yang Terus Membesar

Menjinakkan query lambat di tabel legacy yang terus membesar hampir selalu dimulai dari satu hal: berhenti menyalahkan “sistem lama” secara umum, lalu mencari bottleneck yang konkret. Dalam banyak kasus, masalah utama bukan software lama itu sendiri, melainkan pertumbuhan data, pola query yang berubah, index yang tidak lagi relevan, serta storage yang kewalahan melayani akses acak dan full scan.

Analogi yang sering relevan: menjalankan sistem lama di platform baru belum tentu gagal karena sistem lamanya, tetapi karena asumsi lama bertabrakan dengan karakteristik lingkungan modern. Pada database legacy, gejala serupa muncul saat tabel yang dulu berisi ratusan ribu baris kini berisi puluhan atau ratusan juta baris. Query yang dulu “cukup cepat” menjadi lambat karena planner memilih jalur buruk, offset makin mahal, atau index tunggal tidak lagi cukup.

Artikel ini fokus pada langkah praktis: audit slow query, membaca EXPLAIN, memilih composite index, menghindari full scan, memahami trade-off offset pagination vs keyset pagination, dan menambah index dengan risiko downtime seminimal mungkin.

Gejala di produksi yang patut dicurigai

Masalah query lambat di tabel legacy jarang datang sendirian. Biasanya ia terlihat sebagai gejala operasional yang berulang.

CPU database tinggi pada jam sibuk, padahal volume request aplikasi tidak naik drastis.
Latency endpoint meningkat hanya untuk halaman tertentu, misalnya daftar transaksi, pencarian order, atau dashboard admin.
Timeout intermiten pada query yang melibatkan filter tanggal, status, atau user tertentu.
I/O storage tinggi dan banyak pembacaan acak, terutama saat query melakukan scan besar.
Lock menumpuk karena query baca yang terlalu lama memperpanjang kontensi dengan proses tulis.
Pagination halaman belakang sangat lambat, misalnya page 500 jauh lebih lambat daripada page 1.

Jika gejalanya seperti itu, kemungkinan besar Anda menghadapi masalah desain akses data, bukan sekadar “server kurang besar”. Menambah resource bisa menunda masalah, tetapi jarang menyelesaikan akar penyebabnya.

Mulai dari audit slow query, bukan dari tebakan

Kesalahan umum pada sistem legacy adalah langsung menambahkan index secara reaktif tanpa bukti query mana yang sebenarnya mahal. Pendekatan yang lebih aman adalah memulai dari slow query audit.

Apa yang perlu dikumpulkan

Daftar query paling lambat berdasarkan durasi total dan frekuensi.
P95/P99 latency untuk query penting, bukan hanya rata-rata.
Jumlah baris yang dibaca vs baris yang dikembalikan.
Pola parameter query: tanggal lebar, status populer, pencarian prefix, atau offset besar.
Korelasi dengan endpoint aplikasi, job background, atau laporan berkala.

Poin pentingnya: query yang berdurasi 2 detik tetapi dipanggil 2 kali sehari mungkin kurang prioritas dibanding query 200 ms yang dipanggil 10.000 kali per menit.

Contoh query legacy yang sering bermasalah

Bayangkan tabel transaksi lama seperti ini:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  notes TEXT,
  updated_at TIMESTAMP NOT NULL
);

Lalu aplikasi sering menjalankan query daftar order seperti berikut:

SELECT id, customer_id, status, created_at, total_amount
FROM orders
WHERE status = 'paid'
  AND created_at >= '2025-01-01'
  AND created_at < '2025-02-01'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Pada data kecil, query ini tampak normal. Pada tabel yang sangat besar, kombinasi filter, sort, dan offset bisa menjadi mahal jika index tidak sesuai.

Membaca EXPLAIN: cari scan besar, sort mahal, dan estimasi yang meleset

EXPLAIN membantu memahami mengapa query lambat, bukan hanya seberapa lambat. Nama kolom output berbeda antar database engine, tetapi prinsip bacanya mirip.

Hal utama yang perlu diperhatikan

Apakah terjadi full table scan? Jika ya, cek apakah filter memang tidak selektif atau index tidak tersedia.
Apakah sort dilakukan di luar index? Jika planner harus membaca banyak baris lalu mengurutkan, biaya akan naik.
Berapa banyak rows yang diperkirakan vs benar-benar dibaca? Estimasi yang meleset bisa membuat planner memilih jalur buruk.
Apakah ada filter yang tidak sargable? Misalnya fungsi diterapkan pada kolom sehingga index sulit dipakai.
Apakah lookup tambahan terlalu banyak? Pada beberapa engine, index scan diikuti banyak lookup ke tabel utama bisa tetap mahal.

Contoh analisis sederhana

Untuk query sebelumnya, target ideal biasanya adalah:

Engine dapat memfilter berdasarkan status dan rentang created_at.
Urutan ORDER BY created_at DESC sebisa mungkin mengikuti urutan index.
Jumlah baris yang disentuh jauh lebih kecil daripada total ukuran tabel.

Jika hasil EXPLAIN menunjukkan scan hampir seluruh tabel lalu sort besar, maka masalah utamanya hampir pasti di desain index atau bentuk query.

Catatan: Jangan membaca EXPLAIN tanpa konteks data nyata. Query dengan parameter yang berbeda bisa menghasilkan rencana eksekusi berbeda. Ambil contoh dari produksi atau data staging yang distribusinya mirip produksi.

Memilih composite index yang sesuai pola query

Menambah index sering membantu, tetapi index yang salah hanya menambah biaya tulis, storage, dan maintenance. Pada tabel legacy, kuncinya adalah memilih composite index berdasarkan pola filter dan sort yang paling penting.

Mengapa index tunggal sering tidak cukup

Misalnya Anda punya index terpisah pada status dan created_at. Itu belum tentu optimal untuk query berikut:

SELECT id, customer_id, status, created_at, total_amount
FROM orders
WHERE status = 'paid'
  AND created_at >= '2025-01-01'
  AND created_at < '2025-02-01'
ORDER BY created_at DESC
LIMIT 50;

Query tersebut meminta kombinasi filter equality pada status, range pada created_at, lalu sorting berdasarkan created_at. Sering kali index gabungan lebih cocok daripada dua index tunggal terpisah.

Contoh index yang lebih tepat

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

Mengapa urutannya demikian?

status adalah filter equality, cocok ditempatkan di depan.
created_at dipakai untuk range dan order, sehingga membantu mempersempit scan sekaligus menjaga urutan.

Jika query sangat sering mengambil kolom tertentu saja, beberapa engine mendukung strategi agar query menjadi lebih mendekati covering index. Namun jangan memaksakan itu tanpa mengukur dampak storage dan write amplification.

Aturan praktis memilih urutan kolom index

Letakkan kolom filter equality yang paling umum dipakai di depan.
Lanjutkan dengan kolom range atau kolom untuk sorting yang konsisten.
Jangan membuat terlalu banyak variasi index yang mirip; pilih berdasarkan query paling penting.
Evaluasi kardinalitas dan selektivitas, bukan hanya nama kolom yang tampak “sering dipakai”.

Kesalahan umum saat menambah index

Membuat index untuk setiap kolom secara terpisah tanpa memahami query gabungannya.
Menambahkan terlalu banyak index pada tabel dengan write rate tinggi.
Mengabaikan bahwa index juga memperlambat INSERT, UPDATE, dan DELETE.
Menganggap urutan kolom dalam composite index tidak penting.

Menghindari full scan yang tidak perlu

Full scan tidak selalu salah. Untuk tabel kecil atau query analitik tertentu, scan penuh bisa wajar. Masalahnya adalah full scan pada tabel transaksi besar yang dipanggil terus-menerus oleh aplikasi.

Anti-pattern yang sering mematikan index

Berikut beberapa pola query yang sering membuat engine sulit memanfaatkan index secara efektif.

1. Fungsi pada kolom yang difilter

-- Kurang baik
SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-15';

Lebih baik ubah menjadi rentang waktu:

SELECT *
FROM orders
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at < '2025-01-16 00:00:00';

Mengapa? Karena fungsi pada kolom sering membuat engine tidak bisa memakai urutan alami index pada created_at.

2. Leading wildcard pada LIKE

-- Sering mahal
SELECT *
FROM customers
WHERE email LIKE '%@example.com';

Pencarian dengan wildcard di depan umumnya tidak cocok untuk index B-tree biasa. Jika kebutuhan ini penting, pertimbangkan strategi lain: kolom turunan, engine search, atau jenis index yang memang sesuai.

3. SELECT *

Pada tabel lebar dengan kolom teks besar, SELECT * menambah biaya I/O dan transfer data. Ambil hanya kolom yang benar-benar dibutuhkan.

4. OR yang memperluas scan

Kondisi OR tertentu bisa membuat planner memilih jalur mahal. Kadang lebih baik memecah query menjadi beberapa bagian yang lebih terarah, lalu menggabungkan hasilnya di aplikasi atau dengan pendekatan SQL lain yang tetap terbaca dan terukur.

Offset pagination vs keyset pagination

Pada tabel legacy yang terus membesar, offset pagination hampir selalu menjadi sumber degradasi yang terlambat disadari.

Mengapa OFFSET makin mahal

SELECT id, customer_id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

Untuk mengambil 50 baris di halaman jauh, database sering tetap harus melewati ribuan baris sebelumnya. Semakin besar offset, semakin mahal pekerjaannya.

Kapan offset masih masuk akal

Ukuran data kecil.
Halaman yang diakses umumnya dekat awal.
Kebutuhan UI memang mengharuskan lompat ke nomor halaman arbitrer.

Alternatif: keyset pagination

Untuk daftar yang diurutkan stabil, keyset pagination biasanya lebih efisien.

SELECT id, customer_id, status, created_at
FROM orders
WHERE status = 'paid'
  AND (
    created_at < '2025-02-01 10:30:00'
    OR (created_at = '2025-02-01 10:30:00' AND id < 987654)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Query tersebut menggunakan baris terakhir dari halaman sebelumnya sebagai cursor. Dengan index yang sesuai, database tidak perlu membuang ribuan baris hanya untuk mencapai titik awal halaman.

Index yang cocok untuk keyset

CREATE INDEX idx_orders_status_created_id
ON orders (status, created_at, id);

Trade-off keyset pagination:

Pro: lebih stabil pada data besar, latensi cenderung konsisten antar halaman.
Kontra: tidak alami untuk “lompat ke halaman 200”, implementasi UI dan API biasanya perlu cursor, bukan nomor halaman.

Jika endpoint dipakai sistem internal dengan traffic tinggi, keyset hampir selalu layak dipertimbangkan.

Langkah diagnosis yang praktis di sistem legacy

Berikut urutan kerja yang biasanya aman dan efektif.

Ambil daftar slow query nyata dari log atau observability.
Kelompokkan berdasarkan pola, bukan string mentah semata. Parameter berbeda bisa tetap satu jenis query.
Ukur dampak bisnisnya: endpoint mana yang terdampak, seberapa sering, dan apakah memicu timeout.
Jalankan EXPLAIN pada contoh parameter yang realistis.
Cek index yang ada: apakah sudah overlap, salah urutan, atau justru tidak ada.
Ubah query sekecil mungkin: hindari fungsi pada kolom, sempitkan SELECT, ubah filter menjadi range yang bisa di-index.
Tambah atau revisi composite index berdasarkan pola akses paling dominan.
Uji ulang dengan data yang mendekati produksi.
Pantau setelah deploy: latensi, CPU, I/O, lock, dan dampak ke operasi tulis.

Poin pentingnya: optimasi yang baik bersifat iteratif. Satu index yang tepat bisa memperbaiki banyak hal, tetapi index yang salah juga bisa memunculkan masalah baru di jalur tulis.

Migrasi index tanpa downtime: prinsip dan kehati-hatian

Pada database produksi besar, menambah index bukan sekadar menjalankan satu perintah lalu selesai. Pembuatan index dapat memakan waktu lama, membaca banyak data, dan pada sebagian engine atau konfigurasi berpotensi mengganggu operasi normal.

Prinsip umum yang aman

Pahami kemampuan engine Anda untuk pembuatan index online atau dengan locking minimal.
Jadwalkan pada periode traffic lebih rendah jika memungkinkan.
Uji di staging dengan ukuran data representatif, bukan hanya data kecil.
Monitor I/O, replication lag, dan lock selama proses berjalan.
Siapkan rollback plan jika performa tulis menurun tajam setelah index aktif.

Contoh perintah yang perlu disesuaikan dengan engine

Secara umum, bentuk perintah penambahan index tampak seperti ini:

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

Namun cara agar operasi ini minim downtime bergantung pada database engine. Beberapa engine menyediakan mode online atau concurrent build, tetapi sintaks dan perilakunya berbeda. Karena itu, gunakan fitur yang memang didukung engine Anda dan verifikasi dokumentasinya sebelum eksekusi di produksi.

Hal yang sering terlupakan setelah index dibuat

Pastikan planner benar-benar mulai memakai index baru.
Periksa apakah query write menjadi lebih lambat.
Evaluasi index lama yang redundant agar tidak membebani storage dan maintenance.

Kapan optimasi query saja tidak cukup

Ada titik di mana menambah index atau memperbaiki query tidak lagi memadai. Jika volume data terus naik dan kebutuhan akses makin beragam, Anda mungkin perlu strategi struktural seperti archive atau partitioning.

Checklist kapan perlu archive

Mayoritas query operasional hanya butuh data 3-12 bulan terakhir.
Data lama jarang dibaca, tetapi tetap memenuhi kewajiban audit atau pelaporan.
Tabel utama membesar terutama karena histori yang hampir tidak pernah disentuh.
Backup, restore, dan maintenance mulai terlalu lama karena ukuran tabel.

Archive cocok jika Anda bisa memisahkan hot data dan cold data. Dampaknya sering signifikan karena tabel operasional menjadi lebih kecil dan index lebih fokus.

Checklist kapan partitioning layak dipertimbangkan

Query hampir selalu berbasis waktu, misalnya created_at.
Data tumbuh cepat dan retensi panjang tidak bisa dihindari.
Maintenance per rentang waktu diperlukan, misalnya drop partisi lama atau isolasi beban.
Engine dan tim operasional Anda siap menangani kompleksitas partitioning.

Trade-off partitioning:

Pro: pruning partisi dapat mengurangi data yang harus dibaca, maintenance data lama lebih mudah.
Kontra: desain, query plan, dan operasional menjadi lebih kompleks; salah desain partisi bisa tidak banyak membantu.

Jangan lompat ke partitioning hanya karena tabel besar. Jika akar masalahnya adalah query tidak sargable atau index salah, perbaiki itu dulu.

Anti-pattern umum pada database legacy

Menambal dengan hardware saja tanpa audit query.
Index berlebihan karena setiap insiden dibalas dengan menambah index baru.
Tidak memahami query paling mahal secara total, hanya fokus pada query sekali-sekali yang sangat lambat.
Mengoptimalkan di data dev kecil yang tidak mewakili distribusi produksi.
Mengabaikan perubahan pola akses; index yang tepat lima tahun lalu belum tentu relevan sekarang.
Menggunakan offset besar di endpoint inti dan heran mengapa performa memburuk seiring waktu.

Checklist singkat untuk menjinakkan query lambat di tabel legacy

Identifikasi slow query nyata dan urutkan berdasarkan dampak total.
Baca EXPLAIN untuk melihat scan, sort, dan estimasi baris.
Pastikan filter dapat memakai index: hindari fungsi pada kolom dan pola non-sargable.
Rancang composite index sesuai filter + sort yang dominan.
Kurangi SELECT * jika tidak perlu.
Tinjau ulang offset pagination; gunakan keyset jika halaman jauh sering diakses.
Tambah index dengan strategi minim gangguan sesuai engine database.
Pantau dampaknya terhadap latency baca, throughput tulis, I/O, dan lock.
Jika data historis mendominasi, evaluasi archive atau partitioning.

Penutup

Menjinakkan query lambat di tabel legacy yang terus membesar bukan soal mengganti semua yang lama. Sering kali, masalah utamanya adalah asumsi akses data yang tidak lagi cocok dengan skala saat ini. Dengan audit slow query, pembacaan EXPLAIN yang disiplin, pemilihan composite index yang tepat, penghindaran full scan yang tidak perlu, serta migrasi index yang hati-hati, banyak sistem legacy bisa kembali stabil tanpa rewrite besar.

Inspirasi umumnya mirip dengan menjalankan sistem lama di platform baru: bottleneck nyata sering muncul di lapisan modern tempat software itu dijalankan, bukan semata pada usianya. Pada database, lapisan itu adalah storage, query plan, dan pola akses data. Di situlah optimasi yang tepat biasanya memberi hasil paling nyata.

Quick Search

shopping_cart Keranjang