Optimasi Query Lambat: Index, EXPLAIN, dan Pagination Stabil

Optimasi query lambat biasanya dimulai dari tiga sumber masalah yang paling sering muncul di aplikasi produksi: endpoint list makin lambat saat jumlah data bertambah, query filter dan sort sering jatuh ke full scan, dan pagination berbasis OFFSET/LIMIT makin mahal di halaman yang dalam. Solusinya bukan sekadar menambah index secara acak, tetapi memahami pola query, membaca rencana eksekusi dengan EXPLAIN, lalu memilih index dan strategi pagination yang sesuai.

Artikel ini fokus pada langkah praktis yang relevan untuk MySQL maupun PostgreSQL tanpa terlalu bergantung pada detail vendor tertentu. Kita akan membahas cara menganalisis query lambat, memilih index yang tepat, kapan composite index diperlukan, dampak urutan kolom dalam index, serta kapan harus mengganti OFFSET pagination menjadi keyset pagination agar hasil tetap konsisten dan performa lebih stabil.

Gejala Nyata Query Lambat Saat Data Tumbuh

Masalah performa query jarang muncul saat tabel masih kecil. Begitu data bertambah, pola yang sama mulai terasa mahal. Beberapa gejala yang umum:

Endpoint daftar data makin lambat, terutama saat ada filter, sort, dan pagination.
Halaman pagination tinggi makin berat karena query harus melewati banyak baris sebelum mengembalikan hasil.
CPU atau disk usage naik saat endpoint tertentu dipanggil berulang.
Query yang tampak sederhana ternyata full scan karena kolom filter atau sort tidak punya index yang cocok.
Respons API tidak stabil, kadang cepat, kadang lambat, tergantung parameter filter dan ukuran subset data.

Kesalahan umum adalah langsung menambah index untuk semua kolom yang terlihat penting. Pendekatan itu sering menambah biaya tulis tanpa benar-benar mempercepat query yang kritis. Langkah pertama yang benar adalah mengidentifikasi query nyata yang lambat, bukan menebak-nebak.

Mulai dari Query Nyata, Bukan Asumsi

Sebelum mengubah schema atau menambah index, kumpulkan dulu query yang benar-benar menjadi bottleneck. Fokus pada:

Query dari endpoint paling sering dipakai.
Query dengan waktu eksekusi tertinggi.
Query yang jumlah eksekusinya tinggi meski masing-masing tidak terlalu lambat.
Query list dengan kombinasi WHERE, ORDER BY, dan LIMIT/OFFSET.

Pada tahap ini, simpan minimal informasi berikut:

SQL final yang dieksekusi aplikasi.
Parameter nyata saat query lambat terjadi.
Waktu eksekusi rata-rata dan kasus terburuk.
Jumlah baris hasil dan perkiraan ukuran tabel.

Jangan analisis query hasil simplifikasi yang tidak sama dengan produksi. Sedikit perbedaan pada kondisi filter atau urutan sort bisa membuat optimizer memilih rencana yang berbeda.

Cara Membaca EXPLAIN Secara Umum

EXPLAIN membantu melihat bagaimana database berencana menjalankan query. Nama kolom dan detail output bisa berbeda antar database, tetapi prinsip umumnya sama: Anda ingin tahu apakah database bisa menyaring dan mengurutkan data dengan efisien, atau malah memindai terlalu banyak baris.

Hal yang perlu diperhatikan dari EXPLAIN

Metode akses data: apakah memakai index atau melakukan scan besar pada tabel.
Baris yang diperkirakan diproses: makin banyak baris yang harus dibaca, biasanya makin mahal.
Filter yang diterapkan: apakah kondisi WHERE dievaluasi lebih awal atau setelah banyak data terambil.
Sorting: apakah ORDER BY bisa memanfaatkan index atau butuh langkah sort tambahan.
Join order: pada query multi-tabel, urutan join dapat sangat memengaruhi jumlah baris antara.

Secara umum, sinyal yang patut dicurigai adalah:

Query membaca hampir seluruh tabel padahal hasilnya sedikit.
Sorting dilakukan setelah membaca banyak baris.
Predikat filter tidak memanfaatkan index yang sesuai.
Estimasi jumlah baris sangat besar untuk query endpoint list sederhana.

Catatan: EXPLAIN adalah alat diagnosis, bukan jawaban final. Rencana yang terlihat bagus tetap harus divalidasi dengan eksekusi nyata sebelum dan sesudah perubahan.

Contoh query yang tampak sederhana tetapi mahal

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

Query ini punya tiga potensi masalah:

Jika kolom status tidak terindex dengan baik, database bisa membaca banyak baris untuk menemukan yang cocok.
Jika ORDER BY created_at DESC tidak didukung index yang sesuai, database mungkin harus melakukan sort atas subset besar.
OFFSET 5000 membuat database tetap harus melewati ribuan baris sebelum mengambil 50 baris terakhir.

Memilih Index yang Tepat

Index berguna ketika membantu database menemukan baris yang relevan lebih cepat, atau menghindari kerja tambahan saat sorting. Namun index hanya efektif jika sesuai dengan pola query. Pertanyaannya bukan apakah tabel perlu index, melainkan index seperti apa yang cocok untuk query utama.

Index tunggal untuk filter sederhana

Jika query sering memfilter satu kolom dengan selektivitas cukup baik, index tunggal sering sudah membantu.

SELECT id, email, created_at
FROM users
WHERE email = 'user@example.com';

Untuk query seperti ini, index pada email biasanya masuk akal. Tetapi tidak semua kolom filter layak di-index. Misalnya, kolom dengan nilai sangat sedikit seperti status yang hanya berisi 2-3 nilai mungkin tidak selalu efektif sebagai index tunggal, terutama jika sebagian besar data memiliki nilai yang sama.

Kapan composite index diperlukan

Composite index dibutuhkan saat query sering menggunakan beberapa kolom sekaligus, terutama kombinasi filter dan sort.

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

Untuk pola seperti ini, index gabungan pada (status, created_at) sering lebih berguna daripada dua index terpisah pada status dan created_at. Alasannya, database dapat mencari rentang data berdasarkan status lalu membaca hasil dalam urutan created_at tanpa sort tambahan, tergantung rencana yang dipilih optimizer.

Composite index umumnya diperlukan bila:

Query sering memfilter lebih dari satu kolom.
Query memfilter satu kolom lalu mengurutkan kolom lain secara konsisten.
Anda ingin mengurangi jumlah baris yang perlu dibaca sebelum LIMIT terpenuhi.

Dampak urutan kolom pada index

Urutan kolom dalam composite index sangat penting. Index (status, created_at) berbeda dengan (created_at, status). Pilihan urutan harus mengikuti pola akses query.

Sebagai aturan praktis:

Letakkan kolom yang paling sering dipakai untuk penyaringan awal di depan, jika itu membantu mempersempit rentang data.
Letakkan kolom sort setelah kolom filter bila query sering berbentuk WHERE ... ORDER BY ....
Jangan membuat composite index hanya berdasarkan urutan kolom di tabel; urutkan berdasarkan pola query nyata.

Contoh:

SELECT id, title, published_at
FROM articles
WHERE category_id = 10
AND published_at <= '2025-01-01'
ORDER BY published_at DESC
LIMIT 20;

Untuk query seperti ini, index (category_id, published_at) sering lebih masuk akal daripada (published_at, category_id) karena filter awal dimulai dari kategori, lalu hasil dibaca menurut waktu publikasi.

Jebakan umum saat memilih index

Terlalu banyak index: setiap INSERT, UPDATE, dan DELETE harus ikut memelihara index.
Index duplikatif: membuat beberapa index dengan prefix yang hampir sama tanpa kebutuhan jelas.
Index pada kolom yang jarang dipakai query: menambah overhead tanpa dampak nyata.
Mengabaikan pola ORDER BY: filter cepat saja belum cukup jika sorting tetap mahal.
Mengira dua index tunggal selalu setara dengan satu composite index: sering kali tidak.

Kenapa OFFSET Pagination Memburuk

OFFSET/LIMIT pagination mudah dipakai, tetapi biayanya membesar seiring nomor halaman. Database tetap harus melewati baris sebelum offset, meskipun baris itu tidak dikembalikan ke aplikasi.

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

Pada offset besar, query di atas tetap perlu menemukan dan melompati ribuan baris terlebih dahulu. Jika hasil disortir, kerja tambahan ini makin mahal. Selain itu, pagination jenis ini bisa tidak stabil ketika ada data baru masuk di antara dua permintaan halaman: pengguna dapat melihat item yang terduplikasi atau terlewat.

Masalah stabilitas hasil

Sorting hanya dengan kolom yang tidak unik, misalnya ORDER BY created_at DESC, bisa menghasilkan urutan yang ambigu ketika banyak baris punya nilai created_at sama. Akibatnya, hasil antar halaman tidak konsisten. Karena itu, gunakan tie-breaker yang unik, misalnya:

ORDER BY created_at DESC, id DESC

Ini penting baik untuk offset pagination maupun keyset pagination. Tanpa urutan yang deterministik, pagination sulit stabil.

Perbandingan OFFSET/LIMIT vs Keyset Pagination

OFFSET/LIMIT: mudah, tapi mahal di halaman dalam

Kelebihan:

Mudah diimplementasikan.
Cocok untuk kebutuhan admin sederhana atau dataset kecil.
Mudah lompat ke nomor halaman tertentu.

Kekurangan:

Biaya naik seiring offset.
Kurang stabil saat ada data baru atau perubahan data.
Sering memaksa database membaca lebih banyak baris daripada yang dikembalikan.

Keyset pagination: lebih stabil dan efisien

Keyset pagination mengambil halaman berikutnya berdasarkan nilai baris terakhir yang sudah dilihat, bukan berdasarkan nomor halaman. Pendekatan ini jauh lebih cocok untuk feed, daftar transaksi, log, atau daftar data yang terus bertambah.

Contoh query halaman pertama:

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

Ambil pasangan nilai terakhir, misalnya created_at dan id dari baris ke-50, lalu gunakan untuk halaman berikutnya:

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

Mengapa ini lebih baik:

Database tidak perlu melompati ribuan baris.
Hasil lebih stabil selama urutan sort deterministik.
Sangat cocok jika ada composite index yang sejalan dengan filter dan sort, misalnya (status, created_at, id).

Trade-off keyset pagination:

Tidak ideal jika pengguna harus lompat langsung ke halaman ke-100.
Implementasi API sedikit lebih kompleks karena perlu menyimpan cursor atau nilai baris terakhir.
Urutan sort harus jelas dan stabil.

Kapan memilih yang mana

Pilih OFFSET/LIMIT untuk dataset kecil, kebutuhan sederhana, atau antarmuka yang benar-benar perlu lompat ke halaman tertentu.
Pilih keyset pagination untuk endpoint produksi dengan data besar, daftar yang sering diakses, atau aliran data yang terus berubah.

Menggabungkan Filter, Sort, dan Pagination dengan Index yang Cocok

Kasus yang paling sering memakan waktu adalah query list dengan kombinasi filter dan sort:

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

Jika endpoint ini kritis, index yang sering masuk akal adalah:

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

Mengapa urutan ini masuk akal:

status dipakai di WHERE.
created_at dan id dipakai untuk urutan stabil.
Query bisa mengambil subset status = 'paid' lalu membaca sesuai urutan tanpa kerja sort besar tambahan.

Namun ini bukan rumus mutlak. Jika query utama Anda berbeda, misalnya selalu dimulai dari customer_id, maka desain index juga harus mengikuti pola tersebut.

Contoh ketika index salah urutan

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

Index ini belum tentu optimal untuk query yang selalu memulai dari WHERE status = .... Database mungkin tidak bisa memanfaatkan index seefisien saat kolom filter utama berada di depan. Karena itu, jangan hanya memasukkan semua kolom penting ke satu index; urutannya menentukan nilai praktisnya.

Dampak Index Berlebihan pada INSERT dan UPDATE

Setiap index tambahan bukan gratis. Saat aplikasi menulis data, database harus memperbarui struktur index yang terkait. Pada tabel dengan traffic tulis tinggi, terlalu banyak index bisa menimbulkan masalah baru:

INSERT melambat karena setiap baris baru harus ditambahkan ke beberapa index.
UPDATE melambat jika kolom yang diubah ada di dalam index.
Penggunaan storage meningkat.
Vacuum, maintenance, atau reorganization bisa menjadi lebih mahal tergantung database.

Prinsip praktisnya: buat index untuk query yang penting dan terbukti bermasalah, bukan untuk semua kemungkinan query yang mungkin suatu hari dipakai.

Tanda bahwa index Anda terlalu banyak

Tabel punya banyak index, tetapi hanya sedikit query kritis yang benar-benar memanfaatkannya.
Latency tulis meningkat setelah beberapa index baru ditambahkan.
Ada index yang saling tumpang tindih tanpa alasan kuat.

Checklist Investigasi Bottleneck Query Lambat

Gunakan checklist berikut sebelum mengubah schema atau menulis ulang query:

Ambil query nyata dari log aplikasi atau monitoring database.
Ukur baseline: waktu eksekusi, p95 jika ada, jumlah hasil, dan parameter input.
Jalankan EXPLAIN untuk melihat scan, estimasi baris, dan langkah sort.
Cek pola WHERE dan ORDER BY: apakah sudah sejalan dengan index yang ada?
Periksa urutan sort: apakah deterministik, misalnya ada tie-breaker unik seperti id?
Identifikasi OFFSET besar: apakah pagination menjadi lambat di halaman dalam?
Evaluasi composite index bila query memfilter dan mengurutkan beberapa kolom secara konsisten.
Uji dampak ke operasi tulis sebelum menambah banyak index baru.
Bandingkan sebelum-sesudah dengan data dan parameter yang serupa.
Pastikan hasil query tetap benar, bukan hanya lebih cepat.

Langkah Validasi Sebelum dan Sesudah Optimasi

Optimasi yang benar harus dibuktikan. Jangan berhenti setelah menambahkan index dan melihat query sekali jalan terasa cepat. Lakukan validasi berikut:

1. Validasi rencana eksekusi

Bandingkan output EXPLAIN sebelum dan sesudah. Tujuannya bukan mencari output “cantik”, tetapi memastikan jumlah baris yang diproses berkurang, akses data lebih terarah, dan sorting mahal berkurang jika memungkinkan.

2. Uji dengan parameter realistis

Jalankan query memakai filter dan kondisi yang benar-benar umum di produksi, termasuk kasus buruk seperti halaman dalam atau status yang banyak datanya.

3. Verifikasi stabilitas pagination

Jika beralih ke keyset pagination, pastikan:

Urutan hasil selalu deterministik.
Tidak ada duplikasi antar halaman.
Tidak ada data yang terlewat ketika ada banyak nilai sort yang sama.

4. Pantau dampak ke write path

Setelah menambah index, perhatikan endpoint atau job yang sering melakukan insert dan update. Optimasi baca yang agresif bisa memperburuk throughput tulis.

5. Uji beban sederhana

Jika memungkinkan, lakukan pengujian pada volume data yang mendekati produksi. Query yang cepat pada 10 ribu baris belum tentu tetap sehat pada jutaan baris.

Contoh Perbaikan End-to-End

Misalkan endpoint daftar pesanan mengalami keluhan berikut:

Halaman awal masih cukup cepat.
Halaman ke-50 sangat lambat.
Kadang ada item yang muncul dua kali saat pengguna pindah halaman.

Query awal:

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

Masalahnya:

OFFSET besar.
Urutan tidak deterministik jika banyak created_at sama.
Belum tentu ada index yang cocok untuk filter + sort.

Perbaikan:

Ubah urutan menjadi stabil:

ORDER BY created_at DESC, id DESC

Tambahkan index yang sesuai pola akses:

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

Ganti ke keyset pagination:

SELECT id, customer_id, status, created_at, total_amount
FROM orders
WHERE status = 'paid'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Hasil yang diharapkan:

Query tidak perlu lagi melewati ribuan baris karena offset.
Urutan hasil lebih stabil.
Index lebih sejalan dengan pola query utama.

Kesalahan Umum yang Perlu Dihindari

Mengoptimasi tanpa mengukur.
Menambah index berdasarkan intuisi saja.
Mengabaikan ORDER BY saat menganalisis query list.
Menggunakan OFFSET besar untuk data yang terus bertambah.
Tidak memakai tie-breaker unik pada pagination.
Tidak memeriksa dampak optimasi pada INSERT/UPDATE.
Menganggap semua query lambat harus diselesaikan dengan index; kadang query perlu disederhanakan, hasil perlu dibatasi, atau akses pola perlu diubah.

Penutup

Optimasi query lambat pada aplikasi yang datanya terus tumbuh hampir selalu berkaitan dengan tiga hal: memahami rencana eksekusi lewat EXPLAIN, memilih index yang sesuai dengan pola WHERE dan ORDER BY, serta menghindari pagination yang makin mahal seiring pertumbuhan data. Untuk endpoint list yang sering diakses, kombinasi composite index yang tepat dan keyset pagination sering memberikan hasil paling stabil.

Fokuslah pada query nyata, ukur sebelum dan sesudah, lalu validasi bahwa perubahan Anda benar-benar mengurangi baris yang diproses dan menjaga hasil tetap benar. Dengan pendekatan ini, performa tidak hanya membaik sesaat, tetapi juga lebih tahan saat volume data terus bertambah.

Quick Search

shopping_cart Keranjang