Audit Query Lambat: Index, EXPLAIN, dan Pagination Saat Data Tumbuh

Ketika tabel masih kecil, banyak query terlihat baik-baik saja. Masalah biasanya baru muncul saat data tumbuh: endpoint daftar jadi lambat, CPU database naik, IOPS membengkak, atau latensi melonjak hanya untuk halaman ke-200. Dalam kondisi seperti ini, audit query lambat tidak bisa mengandalkan tebakan, termasuk tebakan dari alat berbasis AI. Yang dibutuhkan adalah verifikasi teknis: lihat query nyata, baca rencana eksekusi, cek pola akses data, lalu ubah query atau index berdasarkan bukti.

Fokus artikel ini adalah panduan debugging yang bisa langsung dipakai: bagaimana mengenali bottleneck nyata, membaca EXPLAIN dan EXPLAIN ANALYZE, mendeteksi index yang tidak terpakai, memahami dampak OFFSET besar, dan kapan beralih ke cursor pagination. Kita juga bahas kesalahan umum seperti over-indexing, metrik yang harus dipantau, serta cara investigasi yang aman di staging dan production.

Mengenali gejala bottleneck yang nyata

Sebelum mengubah query atau menambah index, pastikan masalahnya memang berasal dari akses database, bukan dari layer lain seperti jaringan, cache, serialisasi JSON, atau N+1 query di aplikasi.

Gejala yang sering terlihat

Latensi endpoint meningkat seiring ukuran data, terutama pada halaman daftar, pencarian, dashboard, dan export.
Query yang sama punya waktu eksekusi sangat berbeda tergantung parameter, misalnya cepat untuk 1 hari data tetapi lambat untuk 6 bulan.
CPU database tinggi saat trafik stabil, menandakan banyak kerja komputasi atau scan besar.
Disk read/IOPS meningkat, sering terkait full table scan atau sorting besar ke disk.
Lock wait atau antrean koneksi meningkat karena query lambat menahan resource lebih lama.
P95/P99 latency memburuk walaupun rata-rata masih tampak normal.

Verifikasi sebelum menyimpulkan

Jangan langsung menambah index untuk semua kolom di WHERE. Mulailah dari pertanyaan berikut:

Query mana yang paling mahal berdasarkan durasi total, bukan hanya sekali jalan?
Apakah query lambat karena membaca terlalu banyak baris?
Apakah query lambat karena sort, join, atau pagination dengan OFFSET besar?
Apakah pola akses berubah saat data tumbuh, sehingga index lama tidak lagi cocok?

Catatan penting: AI dapat membantu menyarankan pola index atau rewrite query, tetapi jangan terima hasilnya tanpa verifikasi. Rencana eksekusi database adalah sumber kebenaran, bukan tebakan model.

Mulai audit query lambat dari bukti, bukan asumsi

Kumpulkan query nyata yang bermasalah

Ambil query dari log aplikasi, slow query log, APM, atau statistik query database. Tujuannya adalah melihat query yang benar-benar dijalankan lengkap dengan parameter yang representatif.

Yang perlu dikumpulkan:

Teks query final, bukan pseudo-code ORM.
Parameter nyata yang sering memicu lambat.
Durasi, jumlah pemanggilan, dan waktu total.
Endpoint atau job yang memanggil query tersebut.

Tanpa parameter nyata, Anda bisa salah optimasi. Query dengan filter selektif sering cepat, tetapi parameter yang terlalu longgar dapat memaksa database memindai jauh lebih banyak baris.

Pilih prioritas audit

Jangan mulai dari query paling lambat satu kali jalan jika jarang dipakai. Prioritaskan kombinasi berikut:

Total waktu eksekusi terbesar dalam periode tertentu.
Frekuensi tinggi di endpoint utama.
Dampak bisnis tinggi, misalnya checkout, daftar transaksi, atau panel admin operasional.

Membaca EXPLAIN dan EXPLAIN ANALYZE dengan benar

EXPLAIN menunjukkan rencana eksekusi yang diperkirakan optimizer. EXPLAIN ANALYZE menjalankan query dan memperlihatkan eksekusi aktual beserta timing dan jumlah baris nyata. Keduanya penting: yang satu untuk memahami keputusan optimizer, yang lain untuk memverifikasi apakah prediksi optimizer sesuai realita.

Hal yang perlu diperhatikan

Access path: apakah memakai index scan, range scan, atau full table scan.
Estimated rows vs actual rows: jika selisih jauh, statistik atau selectivity bisa bermasalah.
Filter placement: apakah filter diterapkan dini atau setelah membaca banyak baris.
Sort dan temporary work: apakah ORDER BY, GROUP BY, atau join memicu sort mahal.
Join strategy: nested loop, hash join, merge join, tergantung mesin database.
Rows examined atau jumlah baris yang dibaca dibanding jumlah baris hasil.

Contoh query yang memburuk saat data tumbuh

Misalkan ada tabel orders dengan jutaan baris:

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

Query ini punya dua masalah umum:

Filter dan urutan mungkin tidak didukung index yang sesuai.
OFFSET 10000 memaksa database melewati banyak baris sebelum mengambil 50 baris berikutnya.

Tanda-tanda plan yang patut dicurigai

Database membaca jauh lebih banyak baris daripada yang dikembalikan.
Terlihat full scan meskipun query tampak sederhana.
Sort terjadi setelah membaca banyak baris karena tidak ada index yang mendukung ORDER BY.
Join dimulai dari tabel besar dengan filter yang lemah.
Perkiraan baris sangat meleset dari eksekusi aktual.

Kapan memakai EXPLAIN ANALYZE dengan hati-hati

EXPLAIN ANALYZE sangat berguna, tetapi karena menjalankan query sungguhan, pakai hati-hati di production, terutama untuk query berat atau query yang memodifikasi data. Untuk query baca yang mahal, lebih aman uji di staging dengan snapshot data yang cukup representatif. Jika harus dilakukan di production, gunakan waktu sepi, batasi scope, dan pastikan query hanya-baca.

Index: kapan membantu, kapan tidak terpakai

Index mempercepat pencarian dengan mengurangi jumlah baris yang harus dibaca. Tetapi index hanya efektif jika cocok dengan pola filter, join, dan sorting query Anda. Menambah index secara membabi buta justru bisa memperlambat INSERT, UPDATE, DELETE, memperbesar storage, dan membingungkan optimizer.

Tanda index tidak terpakai

Fungsi pada kolom terindeks, misalnya WHERE DATE(created_at) = '2025-01-01'. Ini sering membuat database sulit memakai index secara efektif.
Leading wildcard seperti LIKE '%abc', yang umumnya tidak cocok untuk B-tree biasa.
Tipe data tidak konsisten, misalnya membandingkan kolom numerik dengan string dalam cara yang memicu konversi.
Urutan kolom pada composite index tidak sesuai dengan pola filter dan sorting.
Selectivity buruk, misalnya kolom dengan sedikit variasi nilai digunakan sendirian sebagai index.
Query mengambil terlalu banyak kolom sehingga index tidak cukup membantu dan database tetap harus banyak mengakses table heap/data page.

Contoh before: fungsi pada kolom

SELECT id, user_id, created_at
FROM orders
WHERE DATE(created_at) = '2025-01-01'
ORDER BY created_at DESC;

Masalahnya, fungsi DATE(created_at) dapat membuat filter kurang ramah index. Rewrite yang lebih aman biasanya berupa rentang waktu:

SELECT id, user_id, created_at
FROM orders
WHERE created_at >= '2025-01-01 00:00:00'
  AND created_at < '2025-01-02 00:00:00'
ORDER BY created_at DESC;

Dengan bentuk ini, database lebih mudah memanfaatkan index pada created_at.

Contoh before/after untuk composite index

Query awal:

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

Jika hanya ada index terpisah pada user_id dan status, hasilnya belum tentu optimal. Untuk pola query seperti ini, sering kali lebih efektif memakai index gabungan yang mengikuti pola akses utama, misalnya kombinasi kolom filter lalu kolom urutan. Bentuk pastinya tergantung database dan distribusi data, tetapi idenya adalah agar database tidak perlu memfilter dan menyortir terlalu banyak baris setelah scanning.

Prinsip praktis: rancang index berdasarkan query yang paling penting, bukan berdasarkan daftar semua kolom yang sering muncul. Fokus pada urutan filter, selectivity, dan ORDER BY.

Kesalahan umum: over-indexing

Membuat index untuk hampir setiap kolom yang ada di WHERE.
Membuat banyak index yang tumpang tindih.
Tidak mengevaluasi biaya tulis pada tabel yang sangat aktif.
Tidak menghapus index yang sudah tidak dipakai setelah pola query berubah.

Index bukan gratis. Setiap write harus memperbarui semua index yang relevan. Pada tabel transaksi besar, ini bisa menjadi bottleneck tersendiri.

Pagination saat data tumbuh: masalah OFFSET besar

LIMIT ... OFFSET ... mudah dipakai, tetapi biayanya membesar seiring nomor halaman. Untuk mengambil halaman ke-1000, database biasanya tetap harus melewati baris-baris sebelumnya. Hasilnya, latensi naik walaupun jumlah baris yang dikembalikan tetap kecil.

Contoh pagination yang mahal

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

Walaupun hasil akhir hanya 50 baris, kerja database bisa jauh lebih besar karena ia harus menemukan dan melewati 10000 baris lebih dulu.

Kapan OFFSET masih masuk akal

Jumlah data kecil.
Halaman yang diakses dangkal, misalnya halaman 1 sampai 5.
Kebutuhan UI memang memerlukan lompat ke nomor halaman tertentu dan biayanya masih diterima.

Alternatif: cursor pagination

Untuk daftar yang terus bertambah dan diakses berurutan, cursor pagination biasanya lebih stabil. Prinsipnya: alih-alih mengatakan “lewati 10000 baris”, Anda mengatakan “ambil 50 baris setelah posisi terakhir yang saya lihat”.

Contoh:

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

Pola ini membutuhkan kolom pengurutan yang stabil dan unik secara efektif. Karena created_at bisa sama untuk beberapa baris, tambahkan tie-breaker seperti id.

Kapan memilih cursor pagination

Feed, log aktivitas, daftar transaksi, audit trail.
Data besar dan terus bertambah.
Akses pengguna dominan “next page”, bukan lompat ke halaman ke-N secara acak.

Trade-off cursor pagination

Lebih efisien untuk halaman dalam, tetapi implementasinya sedikit lebih kompleks.
Tidak senyaman OFFSET untuk lompat langsung ke halaman tertentu.
Perlu desain cursor yang konsisten, biasanya berbasis kolom sort plus tie-breaker.
Jika urutan data berubah sering, pengalaman pengguna perlu dirancang dengan hati-hati.

Contoh audit before/after yang lebih realistis

Kasus awal

Endpoint daftar order admin:

SELECT id, user_id, status, created_at, total_amount
FROM orders
WHERE status = 'paid'
  AND DATE(created_at) >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Masalah

Fungsi DATE(created_at) menghambat pemakaian index yang efisien.
OFFSET 5000 mahal untuk halaman dalam.
Jika index tidak mendukung filter + order, database bisa membaca dan sort banyak baris.

Perbaikan

SELECT id, user_id, status, created_at, total_amount
FROM orders
WHERE status = 'paid'
  AND created_at >= '2025-01-01 00:00:00'
  AND (created_at, id) < ('2025-01-15 10:30:00', 456789)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Mengapa perbaikan ini membantu

Filter waktu berubah menjadi rentang yang lebih ramah index.
Pagination berpindah dari OFFSET ke cursor, sehingga database tidak perlu melewati ribuan baris sebelumnya.
ORDER BY created_at DESC, id DESC memberi urutan yang stabil untuk cursor pagination.

Setelah rewrite, verifikasi lagi dengan EXPLAIN atau EXPLAIN ANALYZE. Jangan berhenti di level “secara teori lebih baik”. Pastikan jumlah baris yang dibaca, langkah sort, dan durasi aktual memang membaik.

Checklist audit performa query

Identifikasi query prioritas: ambil dari slow query log, APM, atau statistik query.
Kumpulkan parameter nyata: jangan audit query tanpa kondisi riil.
Jalankan EXPLAIN: pahami access path, estimasi baris, join, sort.
Bandingkan dengan EXPLAIN ANALYZE bila aman: cek apakah estimasi berbeda jauh dari aktual.
Cek index yang ada: apakah cocok dengan WHERE, JOIN, dan ORDER BY.
Audit bentuk query: hindari fungsi pada kolom filter, cast yang tidak perlu, dan SELECT * tanpa alasan.
Evaluasi pagination: jika OFFSET membesar, pertimbangkan cursor pagination.
Ukur ulang: bandingkan waktu eksekusi, rows examined, dan dampak ke endpoint.
Periksa dampak write: pastikan index baru tidak merusak throughput tulis.
Dokumentasikan hasil: query lama, query baru, plan, metrik sebelum/sesudah.

Metrik yang perlu dipantau

Optimasi query bukan hanya soal satu angka durasi. Pantau beberapa metrik agar tidak salah membaca perbaikan.

P50, P95, P99 latency per endpoint atau per query.
Rows examined vs rows returned.
QPS/TPS dan total waktu database per query signature.
CPU usage database.
Buffer/cache hit ratio bila tersedia.
Disk read, IOPS, temp file/sort spill.
Connection pool saturation dan wait time.
Lock wait / deadlock untuk query yang berinteraksi dengan transaksi lain.

Jika setelah menambah index durasi baca turun tetapi latensi write naik, Anda belum tentu benar-benar menang. Lihat sistem secara utuh.

Langkah investigasi aman di staging dan production

Di staging

Gunakan data yang representatif, bukan dataset mini.
Uji parameter yang terkenal lambat, bukan hanya contoh mudah.
Bandingkan plan sebelum/sesudah perubahan query atau index.
Simulasikan ukuran halaman dan urutan akses yang umum di aplikasi.

Di production

Mulai dari observasi pasif: log, statistik query, APM, dashboard database.
Hindari menjalankan eksperimen berat di jam sibuk.
Jika perlu EXPLAIN ANALYZE, pastikan query baca dan scope aman.
Deploy perubahan secara bertahap jika memungkinkan.
Monitor latensi, error rate, dan dampak pada write setelah index baru ditambahkan.

Prinsip kehati-hatian

Perubahan index bisa memakan waktu dan resource. Query rewrite yang terlihat kecil juga dapat mengubah plan secara signifikan. Karena itu:

Selalu siapkan cara rollback.
Catat metrik baseline sebelum perubahan.
Uji satu perubahan penting dalam satu waktu agar efeknya jelas.

Kesalahan umum saat mengoptimasi query lambat

Mengandalkan intuisi tanpa EXPLAIN.
Menambah index terlalu banyak tanpa mempertimbangkan biaya write.
Hanya melihat rata-rata latency dan mengabaikan tail latency.
Menguji di dataset kecil lalu menganggap hasilnya valid untuk production.
Tidak memperhatikan pola sorting saat mendesain index.
Memakai OFFSET besar untuk semua daftar data.
Mengoptimasi query yang jarang dipakai sementara bottleneck utama dibiarkan.
Mengubah banyak hal sekaligus sehingga sulit tahu mana yang benar-benar membantu.

Penutup

Audit query lambat yang efektif selalu berbasis bukti: ambil query nyata, baca EXPLAIN, verifikasi dengan EXPLAIN ANALYZE bila aman, lalu cocokan index dan strategi pagination dengan pola akses data. Saat data tumbuh, masalah yang paling sering muncul bukan sekadar “database lambat”, melainkan kombinasi filter yang tidak ramah index, plan yang buruk, dan OFFSET yang makin mahal.

Jika Anda hanya mengingat satu hal dari artikel ini, ingatlah ini: jangan optimasi berdasarkan tebakan, termasuk tebakan yang terdengar meyakinkan dari AI. Verifikasi teknis tetap nomor satu. Lihat baris yang dibaca, cara query disortir, apakah index benar-benar dipakai, dan apakah pagination Anda masih masuk akal pada skala data sekarang.

Quick Search

shopping_cart Keranjang