Audit Query Lambat: Validasi EXPLAIN, Index, dan Pagination

Audit query lambat tidak cukup dilakukan dengan melihat satu screenshot EXPLAIN atau menerima rekomendasi alat secara mentah. Output alat bantu, termasuk reasoning tool atau analyzer otomatis, bisa berguna sebagai petunjuk awal, tetapi keputusan optimasi tetap harus divalidasi dengan eksekusi nyata, pola akses data, ukuran tabel, dan hasil ukur sebelum-sesudah.

Di aplikasi produksi, query yang tadinya aman bisa memburuk saat data tumbuh, distribusi nilai berubah, atau pola pagination tidak lagi cocok. Artikel ini membahas langkah praktis untuk mengaudit query lambat: mengenali gejala, membaca EXPLAIN dan EXPLAIN ANALYZE, menemukan full scan, filesort, dan index yang tidak terpakai, lalu mengevaluasi apakah masalah ada pada index, bentuk query, atau pola pagination.

Mulai dari gejala, bukan dugaan

Sebelum mengubah index atau menulis ulang query, pastikan Anda mengamati gejalanya dengan benar. Query lambat jarang berdiri sendiri; biasanya ia terlihat lewat timeout, lonjakan CPU database, antrean request, atau endpoint tertentu yang semakin berat saat tabel membesar.

Tanda umum query bermasalah

Latency endpoint naik hanya pada halaman daftar, pencarian, atau laporan tertentu.
CPU database tinggi saat trafik normal, terutama ketika banyak query baca berjalan bersamaan.
I/O meningkat karena pembacaan blok data besar akibat scan tabel atau scan index yang luas.
Waktu respon memburuk seiring page number membesar, khas pada OFFSET besar.
Performa tidak stabil: kadang cepat, kadang lambat, tergantung parameter query atau distribusi data.

Data yang perlu dikumpulkan

Saat mengaudit, hindari menebak. Kumpulkan minimal:

SQL lengkap yang dieksekusi, termasuk parameter nyata.
Waktu eksekusi aktual pada kondisi representatif.
Jumlah baris hasil dan perkiraan jumlah baris yang dipindai.
EXPLAIN atau EXPLAIN ANALYZE dari query yang sama.
Index yang ada pada tabel terkait.
Konteks beban: apakah query lambat sendiri atau hanya lambat saat concurrency tinggi.

Catatan: Gunakan output alat analisis sebagai hipotesis awal. Seperti halnya output reasoning tool yang bisa tampak meyakinkan tetapi tetap perlu diverifikasi, hasil EXPLAIN juga harus dibaca bersama eksekusi nyata. Tujuannya bukan percaya pada alat, melainkan menguji apakah rencana dan hasil aktual memang sejalan.

Validasi EXPLAIN dan EXPLAIN ANALYZE

EXPLAIN menunjukkan rencana eksekusi yang dipilih optimizer. EXPLAIN ANALYZE, jika tersedia di sistem Anda, menambahkan pengukuran eksekusi aktual. Ini penting karena rencana yang terlihat baik belum tentu cepat, dan rencana yang tampak buruk kadang masih cukup efisien untuk data kecil.

Apa yang ingin dicari dari EXPLAIN

Bagaimana tabel diakses: apakah melalui index atau scan penuh.
Berapa banyak baris yang diperkirakan diproses.
Apakah sorting dilakukan dengan cara mahal.
Apakah filter dan join memanfaatkan index yang relevan.

Contoh query yang perlu diaudit

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

Query di atas terlihat sederhana, tetapi mengandung dua sumber masalah umum:

Filter pada status mungkin tidak memakai index yang efektif.
OFFSET 20000 memaksa database melewati banyak baris sebelum mengembalikan 50 data.

Contoh penggunaan EXPLAIN

EXPLAIN
SELECT id, user_id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 20000;

Yang perlu diperhatikan dari outputnya, terlepas dari perbedaan format antar database:

Access type atau cara akses: bila mengarah ke scan penuh tabel, itu sinyal investigasi.
Possible keys / key used: index apa yang mungkin dipakai dan mana yang benar-benar dipakai.
Rows estimated: estimasi jumlah baris yang harus diproses.
Extra notes seperti Using filesort, temporary, atau indikasi filter dilakukan setelah banyak baris dibaca.

Nilai EXPLAIN ANALYZE

Bila tersedia, EXPLAIN ANALYZE lebih berguna karena membandingkan estimasi dengan eksekusi aktual. Jika perkiraan jumlah baris sangat meleset dari kenyataan, optimizer bisa memilih rencana yang kurang tepat. Dalam kondisi seperti ini, menambah index belum tentu cukup; Anda perlu memeriksa bentuk query, statistik tabel, atau distribusi datanya.

Prinsipnya sederhana:

EXPLAIN menjawab: database berencana melakukan apa.
EXPLAIN ANALYZE menjawab: database benar-benar melakukan apa.

Mendeteksi full scan, filesort, dan index yang tidak terpakai

Full table scan

Full scan tidak selalu salah. Untuk tabel kecil, scan penuh bisa lebih murah daripada memakai index. Masalah muncul ketika tabel besar, query sering dipanggil, atau filter seharusnya selektif tetapi database tetap membaca hampir seluruh data.

Contoh query yang berpotensi full scan:

SELECT id, email, created_at
FROM users
WHERE LOWER(email) = '[email protected]';

Walaupun ada index pada email, pemanggilan fungsi LOWER(email) dapat membuat index biasa sulit dimanfaatkan. Dalam kasus seperti ini, perbaikannya sering ada pada pola query, bukan langsung menambah index baru.

Alternatif yang lebih ramah index tergantung kebutuhan aplikasi dan desain data, misalnya menyimpan nilai yang sudah dinormalisasi atau mengubah strategi pencarian.

Filesort dan sort mahal

Filesort sering dipahami terlalu harfiah seolah pasti menulis ke disk. Intinya, ini menandakan operasi pengurutan tambahan yang tidak bisa sepenuhnya dipenuhi oleh urutan index. Tidak semua filesort buruk, tetapi pada dataset besar dan query frekuensi tinggi, biaya sort bisa signifikan.

Contoh:

SELECT id, title, published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Jika hanya ada index pada status, database mungkin tetap perlu menyortir hasil yang lolos filter. Dalam banyak kasus, composite index yang selaras dengan filter dan urutan sort lebih cocok, misalnya pada kolom filter lalu kolom pengurutan.

Index ada, tetapi tidak dipakai

Kesalahan umum adalah menganggap keberadaan index otomatis membuat query cepat. Index bisa tidak dipakai karena beberapa alasan:

Urutan kolom pada composite index tidak cocok dengan pola filter dan sort.
Filter terlalu tidak selektif sehingga optimizer memilih scan.
Kolom dibungkus fungsi atau diubah tipenya saat perbandingan.
Query mengambil kolom terlalu banyak sehingga membaca tabel tetap mahal.
Predicate menggunakan bentuk yang sulit dioptimalkan untuk index yang ada.

Contoh query yang sering membuat index tidak efektif:

SELECT *
FROM orders
WHERE created_at >= '2025-01-01'
ORDER BY status, created_at DESC;

Bila index yang ada hanya pada created_at, kebutuhan sort berdasarkan status, created_at bisa membuat optimizer memilih rencana yang tidak ideal. Di sini Anda perlu memutuskan: apakah menambah index yang sesuai, atau justru mengubah kebutuhan query agar lebih realistis.

Kapan menambah composite index, kapan mengubah query

Menambah composite index sering efektif, tetapi tidak selalu menjadi jawaban terbaik. Setiap index tambahan memperlambat operasi tulis, menambah penggunaan storage, dan meningkatkan kompleksitas pemeliharaan.

Composite index cocok ketika

Query penting dijalankan sangat sering.
Pola WHERE dan ORDER BY relatif stabil.
Filter cukup selektif sehingga index benar-benar mengurangi pembacaan.
Anda ingin menghindari sort tambahan atau mengurangi jumlah baris yang dipindai.

Mengubah pola query lebih tepat ketika

Query menggunakan fungsi pada kolom yang diindex.
Predicate terlalu fleksibel dan berubah-ubah, sehingga satu index sulit melayani semua kasus.
Query memakai SELECT * padahal hanya butuh sedikit kolom.
Masalah utamanya ada pada pagination OFFSET besar, bukan pada filter sederhana.
Index tambahan akan terlalu banyak dan membebani write workload.

Contoh keputusan praktis

Misalkan query utama Anda seperti ini:

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

Jika query ini sangat sering dipanggil, tabel terus tumbuh, dan EXPLAIN menunjukkan sort tambahan atau pembacaan baris besar, maka composite index yang mengikuti pola filter lalu sort sering layak dipertimbangkan.

Namun jika query sebenarnya seperti ini:

SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-10';

Masalah utamanya bukan kurang index, melainkan bentuk predicate yang membuat index waktu sulit dimanfaatkan. Biasanya lebih tepat mengubah query menjadi rentang waktu yang eksplisit:

SELECT id, user_id, status, created_at
FROM orders
WHERE created_at >= '2025-01-10 00:00:00'
  AND created_at < '2025-01-11 00:00:00';

Pola rentang seperti ini lebih mudah dioptimalkan oleh database dibanding memanggil fungsi pada kolom.

Masalah klasik: OFFSET besar pada tabel yang terus tumbuh

Banyak query lambat bukan karena filter kompleks, tetapi karena pagination berbasis OFFSET. Pola ini nyaman untuk UI dan mudah dipahami, tetapi biayanya naik seiring nomor halaman membesar.

Mengapa OFFSET mahal

Query berikut harus menemukan, menyusun, atau melewati banyak baris sebelum mengembalikan hasil akhir:

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

Walaupun hasil akhirnya hanya 50 baris, database tetap harus menangani 20050 posisi untuk sampai ke halaman itu. Pada tabel yang terus menerima data baru, halaman tinggi juga cenderung makin tidak stabil untuk pengguna karena isi data bisa bergeser di antara request.

Kapan offset pagination masih masuk akal

Dataset relatif kecil.
Nomor halaman jarang jauh.
UI memang membutuhkan lompat langsung ke halaman tertentu.
Beban baca tidak terlalu tinggi dan hasil ukur masih aman.

Kelemahan offset pagination

Biaya meningkat dengan bertambahnya OFFSET.
Konsistensi hasil bisa buruk saat data baru terus masuk.
Sulit menjaga performa pada tabel besar hanya dengan menambah index.

Membandingkan offset pagination vs keyset pagination

Untuk tabel yang terus tumbuh, keyset pagination biasanya lebih stabil. Alih-alih berkata “ambil halaman ke-401”, aplikasi berkata “ambil 50 baris setelah nilai kursor terakhir”.

Offset pagination

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

Kelebihan:

Mudah diimplementasikan.
Mudah dipakai untuk konsep nomor halaman.

Kekurangan:

Makin lambat saat OFFSET membesar.
Rentan hasil bergeser pada data yang berubah terus.

Keyset pagination

Misalkan halaman pertama diambil seperti ini:

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

Lalu aplikasi menyimpan pasangan nilai terakhir, misalnya created_at dan id. Halaman berikutnya diambil dengan kursor:

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

Kelebihan:

Lebih konsisten untuk data yang terus bertambah.
Tidak perlu melewati ribuan baris seperti OFFSET besar.
Biasanya lebih mudah memanfaatkan index yang sesuai dengan urutan data.

Kekurangan:

Tidak natural untuk lompat ke halaman arbitrer seperti halaman 400.
Implementasi UI dan API lebih kompleks karena memakai cursor.
Butuh urutan yang stabil, sering kali dengan tie-breaker seperti id.

Kapan memilih keyset pagination

Feed, timeline, daftar transaksi, log, event, atau order history.
Tabel tumbuh terus dan halaman dalam sering diakses.
Urutan data jelas dan stabil, misalnya berdasarkan created_at dan id.

Kapan offset pagination masih layak

Data kecil atau terbatas.
Kebutuhan bisnis kuat untuk nomor halaman eksplisit.
Hasil ukur menunjukkan performa masih memadai pada skala nyata.

Checklist investigasi audit query lambat

Ambil query asli dari log aplikasi atau slow query log, bukan versi yang sudah disederhanakan.
Jalankan dengan parameter nyata yang memang lambat di produksi.
Ukur waktu eksekusi aktual beberapa kali untuk melihat konsistensi.
Bandingkan EXPLAIN dengan EXPLAIN ANALYZE jika tersedia.
Cek apakah terjadi full scan dan apakah itu masuk akal untuk ukuran tabel saat ini.
Cek operasi sort dan apakah urutan hasil sebenarnya bisa dibantu oleh index.
Periksa apakah index yang ada benar-benar dipakai, bukan hanya terdaftar.
Evaluasi bentuk query: fungsi pada kolom, predicate tidak sargable, SELECT *, atau join yang terlalu lebar.
Periksa pagination: apakah OFFSET besar menjadi penyebab utama.
Uji perubahan satu per satu: tambah index, ubah query, atau ganti ke keyset pagination.
Bandingkan sebelum-sesudah dengan metrik yang sama, jangan hanya mengandalkan feeling.

Kesalahan umum saat mengaudit query lambat

1. Menganggap EXPLAIN sebagai kebenaran final

EXPLAIN adalah alat bantu, bukan putusan akhir. Rencana yang terlihat bagus belum tentu cepat di data nyata, terutama jika estimasi baris meleset atau kondisi cache berbeda.

2. Menambah index tanpa melihat beban tulis

Setiap index tambahan punya biaya. Pada tabel dengan insert dan update tinggi, terlalu banyak index bisa memindahkan masalah dari query baca ke latensi tulis.

3. Mengoptimalkan query yang salah

Sering kali query yang paling terlihat bukan akar masalahnya. Bisa jadi query dipanggil terlalu sering karena N+1, cache tidak efektif, atau endpoint memuat data yang tidak dibutuhkan.

4. Fokus ke satu query, lupa pola akses

Index yang bagus untuk satu query bisa merusak query lain atau membebani write path. Audit harus melihat workload secara keseluruhan.

5. Memaksa offset pagination bertahan terlalu lama

Pada tabel yang tumbuh terus, menambah index kadang hanya memperlambat kerusakan. Jika masalah utamanya adalah OFFSET besar, perubahan desain pagination sering lebih berdampak daripada tuning kecil.

Pola kerja yang aman di produksi

Optimasi query di produksi sebaiknya dilakukan dengan disiplin eksperimen:

Reproduksi kasus lambat di lingkungan yang cukup mirip.
Ubah satu variabel per langkah agar dampaknya jelas.
Catat metrik: waktu eksekusi, jumlah baris dipindai, dan stabilitas hasil.
Waspadai regresi pada query lain setelah menambah index.
Validasi pada data besar, bukan hanya dataset lokal kecil.

Prinsip utama: ukur, uji, dan bandingkan hasil eksekusi nyata. Alat analisis memberi arah, tetapi keputusan optimasi harus dibuktikan pada workload sesungguhnya.

Penutup

Audit query lambat yang efektif dimulai dari observasi gejala, lalu validasi teknis yang disiplin. Baca EXPLAIN untuk memahami rencana, gunakan EXPLAIN ANALYZE untuk memeriksa perilaku aktual, cari full scan dan sort mahal, lalu pastikan index yang ada benar-benar sesuai dengan pola WHERE dan ORDER BY.

Jika masalah utamanya ada pada OFFSET besar di tabel yang terus tumbuh, jangan terpaku pada penambahan index saja. Bandingkan secara nyata apakah keyset pagination memberi hasil lebih stabil dan lebih ringan. Dalam audit performa database, jawaban terbaik bukan yang paling meyakinkan di layar, tetapi yang paling terbukti saat dieksekusi.

Quick Search

shopping_cart Keranjang