Audit Query Lambat: Checklist Praktis untuk Data yang Terus Tumbuh

Audit query lambat tidak cukup dilakukan saat insiden sudah terjadi. Pada aplikasi yang datanya terus tumbuh, query yang dulu terasa ringan bisa berubah menjadi bottleneck karena jumlah baris meningkat, distribusi data berubah, atau pola akses aplikasi bergeser.

Tujuan artikel ini adalah memberi checklist praktis untuk meninjau kualitas query dan desain akses data secara berkala. Fokusnya bukan sekadar “buat index”, tetapi memahami mengapa query melambat, bagaimana membaca rencana eksekusi, kapan index membantu, kapan justru tidak efektif, dan kapan perlu mengubah pola query itu sendiri.

Tanda-Tanda Query Mulai Menjadi Bottleneck

Query lambat jarang muncul tanpa gejala. Biasanya ada pola yang bisa dikenali lebih awal.

Gejala operasional yang umum

Latency API naik pada endpoint tertentu, terutama daftar data, pencarian, atau dashboard.
Penggunaan CPU database meningkat walau trafik tidak naik drastis.
I/O disk atau buffer cache tertekan karena full scan makin sering.
Timeout di aplikasi, worker, atau background job.
Lock contention ikut memburuk karena query baca/tulis memakan waktu lebih lama.

Pola SQL yang patut dicurigai

Query dengan WHERE dan ORDER BY pada kolom yang tidak di-index.
OFFSET besar untuk pagination halaman jauh.
N+1 query dari ORM atau akses data bertingkat.
Join ke tabel besar tanpa filter selektif.
Query agregasi yang tadinya cepat, lalu melambat setelah volume data bertambah.
Penggunaan fungsi pada kolom filter, misalnya LOWER(email) atau DATE(created_at), yang dapat menghambat pemakaian index biasa.

Catatan: query yang lambat di produksi belum tentu terlihat lambat di lokal. Dataset kecil sering menutupi masalah cardinality, distribusi data, dan biaya scan yang baru terasa saat data tumbuh.

Checklist Audit Query Lambat

Gunakan checklist berikut saat meninjau endpoint, job, atau fitur yang dicurigai.

Identifikasi query paling mahal
Ambil dari slow query log, APM, database insights, atau log aplikasi. Prioritaskan berdasarkan kombinasi: frekuensi tinggi, latency tinggi, dan dampak bisnis.
Kelompokkan berdasarkan pola
Pisahkan query yang sama dengan parameter berbeda. Satu pola query yang dipanggil ribuan kali biasanya lebih penting daripada satu query yang sangat lambat tetapi jarang dipakai.
Jalankan EXPLAIN atau EXPLAIN ANALYZE
Lihat apakah database melakukan index scan, range scan, sort mahal, atau full table scan.
Periksa selektivitas filter
Apakah kolom yang dipakai di WHERE benar-benar menyaring data secara efektif? Index pada kolom dengan variasi rendah tidak selalu membantu.
Evaluasi urutan filter dan sort
Apakah query memfilter satu kolom tetapi mengurutkan kolom lain tanpa index yang sesuai?
Tinjau pagination
Jika memakai LIMIT ... OFFSET ... besar, pertimbangkan keyset pagination.
Cek N+1 query
Pastikan satu request tidak menghasilkan puluhan atau ratusan query kecil yang seharusnya bisa digabung.
Bandingkan jumlah baris yang dibaca vs dikembalikan
Jika database membaca jutaan baris untuk mengembalikan puluhan baris, biasanya ada masalah desain query atau index.
Pastikan statistik database mutakhir
Planner bergantung pada statistik. Statistik usang bisa membuat optimizer memilih rencana yang buruk.
Uji ulang setelah perbaikan
Jangan berhenti setelah menambah index. Verifikasi dengan rencana eksekusi baru dan ukur dampaknya pada beban nyata.

Cara Membaca EXPLAIN dan EXPLAIN ANALYZE

EXPLAIN menunjukkan rencana eksekusi yang dipilih optimizer. EXPLAIN ANALYZE menjalankan query dan menampilkan eksekusi aktual. Nama detail setiap database bisa berbeda, tetapi prinsip bacanya serupa.

Hal yang perlu dicari

Jenis scan: full table scan, index scan, range scan, bitmap scan, dan sejenisnya.
Perkiraan baris vs aktual baris: selisih besar sering menandakan statistik kurang akurat atau distribusi data sulit diprediksi.
Biaya sort: ORDER BY yang tidak ditopang index bisa memicu sort mahal.
Filter yang diterapkan terlambat: database mungkin membaca banyak baris dulu, lalu baru menyaring.
Join strategy: nested loop bisa efisien untuk dataset kecil, tetapi buruk jika sisi dalam join tidak di-index dan dipanggil berulang.

Contoh pola bermasalah

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

Jika rencananya menunjukkan scan besar lalu sort, itu tanda database harus membaca banyak baris hanya untuk melewati 50.000 baris pertama sebelum mengambil 50 baris berikutnya.

Pertanyaan saat membaca hasil EXPLAIN

Apakah index yang saya harapkan benar-benar dipakai?
Jika tidak dipakai, apakah karena urutan kolom index tidak cocok?
Apakah WHERE dan ORDER BY bisa dipenuhi oleh index yang sama?
Apakah planner membaca terlalu banyak baris untuk hasil yang sedikit?
Apakah bottleneck utama ada di scan, sort, join, atau lookup berulang?

Salah Pilih Index: Masalah yang Sering Terjadi

Menambah index tidak otomatis menyelesaikan masalah. Banyak query tetap lambat karena index yang dibuat tidak sesuai pola akses.

Kesalahan umum

Index hanya pada kolom filter, tetapi lupa kolom sort.
Composite index dengan urutan kolom yang salah.
Terlalu banyak index sehingga write menjadi lebih mahal.
Index pada kolom berselektivitas rendah yang tidak cukup membantu planner.
Mengandalkan index tunggal terpisah padahal query sering memakai kombinasi kolom yang sama.

Contoh sebelum dan sesudah

Misalnya aplikasi sering menampilkan pesanan per pengguna, hanya yang aktif, diurutkan berdasarkan waktu terbaru.

SELECT id, user_id, status, created_at, total
FROM orders
WHERE user_id = 42 AND status = 'active'
ORDER BY created_at DESC
LIMIT 20;

Index yang kurang tepat:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Index di atas belum tentu ideal. Database mungkin tetap perlu membaca banyak baris lalu menyortir hasilnya.

Pilihan yang biasanya lebih tepat untuk pola ini adalah composite index yang mengikuti pola filter lalu sort:

CREATE INDEX idx_orders_user_status_created_at
ON orders(user_id, status, created_at);

Mengapa ini bekerja? Karena query memfilter user_id dan status, lalu mengurutkan berdasarkan created_at. Dengan index yang sejalan dengan pola akses, database dapat mengambil baris yang relevan dalam urutan yang sudah sesuai, sehingga mengurangi scan dan biaya sort.

Kapan memilih composite index

Ketika kombinasi kolom yang sama dipakai berulang di WHERE dan ORDER BY.
Ketika satu kolom saja tidak cukup selektif.
Ketika Anda ingin menghindari sort tambahan setelah filter.

Kapan composite index belum tentu tepat

Jika pola query sangat bervariasi dan tidak ada kombinasi dominan.
Jika biaya write sangat sensitif dan tabel menerima banyak insert/update.
Jika urutan kolom index hanya cocok untuk satu query langka, bukan jalur panas aplikasi.

OFFSET Besar dan Kapan Beralih ke Keyset Pagination

LIMIT ... OFFSET ... mudah dipakai, tetapi performanya memburuk pada halaman jauh. Database tetap harus melewati banyak baris sebelum sampai ke hasil yang diminta.

Contoh yang melambat seiring pertumbuhan data

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

Walau hanya mengembalikan 50 baris, query ini bisa memaksa database memproses jauh lebih banyak data.

Alternatif: keyset pagination

Gunakan nilai kolom terurut terakhir sebagai penanda halaman berikutnya.

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

Jika ada kemungkinan nilai created_at sama, gunakan pasangan kolom yang stabil, misalnya (created_at, id).

SELECT id, created_at, total
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 < 987654)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Kapan memilih keyset pagination

Daftar data besar yang diakses berurutan: feed, riwayat transaksi, log, pesanan.
API yang harus tetap cepat pada halaman jauh.
Kasus di mana stabilitas urutan lebih penting daripada akses acak ke nomor halaman.

Trade-off keyset pagination

Lebih rumit di sisi aplikasi dibanding offset.
Tidak cocok jika pengguna harus lompat bebas ke halaman 200 dengan presisi nomor halaman.
Perlu kolom urutan yang konsisten dan index yang sesuai.

N+1 Query: Lambat Bukan Karena Satu Query, Tapi Karena Terlalu Banyak Query

Pada ORM, N+1 query adalah penyebab klasik performa buruk. Satu query mengambil daftar induk, lalu setiap baris memicu query tambahan untuk relasi.

Contoh pola N+1

// Pseudocode
orders = load recent orders
for each order in orders:
  customer = load customer by order.customer_id

Jika ada 100 order, aplikasi bisa mengirim 101 query. Masing-masing mungkin cepat, tetapi totalnya mahal karena overhead jaringan, parsing, locking ringan, dan round-trip ke database.

Pendekatan yang lebih baik

Eager loading jika memakai ORM.
Join jika data relasi benar-benar dibutuhkan dalam satu hasil.
Batch fetch dengan IN (...) bila join tidak ideal untuk struktur data tertentu.

SELECT o.id, o.created_at, o.total, c.id AS customer_id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 100;

Cara mendeteksi N+1

Lihat log query per request.
Bandingkan jumlah query terhadap jumlah item hasil.
Gunakan APM atau instrumentation yang menampilkan query breakdown per endpoint.

Filter dan Sort Tanpa Index

Kasus ini sangat sering terjadi pada fitur pencarian daftar, admin panel, dan laporan operasional.

Contoh query bermasalah

SELECT id, email, created_at
FROM users
WHERE organization_id = 10
ORDER BY last_login_at DESC
LIMIT 50;

Jika hanya ada index pada organization_id, database mungkin masih perlu menyortir banyak baris hasil filter. Jika hanya ada index pada last_login_at, filter organisasi bisa menjadi mahal. Untuk pola yang sering dipakai, pertimbangkan composite index:

CREATE INDEX idx_users_org_last_login
ON users(organization_id, last_login_at);

Ini relevan terutama bila endpoint memang selalu menampilkan pengguna per organisasi dengan urutan login terakhir.

Kesalahan lain yang sering tersembunyi

Menggunakan %keyword% pada kolom teks besar dan berharap index biasa membantu.
Mengaplikasikan fungsi pada kolom yang difilter sehingga index biasa sulit dipakai.
Menyortir hasil agregasi besar tanpa pembatasan awal.

Jika kebutuhan pencarian teks menjadi kompleks, solusi yang tepat bisa berbeda: full-text search, mesin pencarian terpisah, atau desain fitur yang diubah. Jangan memaksa index biasa menyelesaikan semua masalah pencarian.

Mengapa Query yang Dulu Cepat Sekarang Melambat

Ini pertanyaan penting dalam sistem yang tumbuh. Penyebabnya jarang satu faktor.

Penyebab umum

Jumlah data naik: scan yang dulu murah kini mahal.
Distribusi data berubah: misalnya status “active” kini mendominasi tabel, membuat filter kurang selektif.
Perubahan fitur: endpoint memuat lebih banyak relasi atau kolom.
Statistik planner usang: optimizer salah memperkirakan cardinality.
Fragmentasi pola akses: query baru memperkenalkan kombinasi filter/sort yang belum ditopang index.
OFFSET makin besar seiring bertambahnya riwayat data.

Implikasi praktis

Karena performa dipengaruhi pertumbuhan data, audit query sebaiknya menjadi kegiatan berkala, bukan tugas sekali selesai. Query yang aman hari ini belum tentu aman tiga bulan lagi.

Contoh Audit Singkat: Sebelum dan Sesudah

Sebelum

SELECT id, user_id, status, created_at
FROM invoices
WHERE account_id = 99
ORDER BY created_at DESC
LIMIT 25 OFFSET 10000;

Masalah yang mungkin muncul:

Offset besar.
Sort berdasarkan kolom yang belum sejalan dengan filter.
Planner membaca terlalu banyak baris dari akun tersebut.

Perbaikan tahap 1: index yang sesuai

CREATE INDEX idx_invoices_account_created_at
ON invoices(account_id, created_at);

Perbaikan tahap 2: beralih ke keyset pagination

SELECT id, user_id, status, created_at
FROM invoices
WHERE account_id = 99
  AND created_at < '2025-01-10 12:00:00'
ORDER BY created_at DESC
LIMIT 25;

Hasil yang diharapkan bukan sekadar latency rata-rata turun, tetapi jumlah baris yang perlu dibaca juga berkurang drastis, sehingga performa lebih stabil saat data terus bertambah.

Metrik yang Perlu Dipantau

Audit yang baik tidak bergantung pada perasaan. Pantau metrik yang bisa menunjukkan pola degradasi.

Metrik tingkat query

p95/p99 latency per pola query atau endpoint.
Calls per second atau frekuensi eksekusi.
Rows examined / rows returned bila tersedia.
Shared buffer hit / disk read atau indikator I/O serupa.
Temporary sort / spill bila query sering menyortir data besar.

Metrik tingkat aplikasi

Jumlah query per request.
Endpoint paling mahal terhadap database.
Antrian job yang melambat karena query baca/tulis makin berat.

Metrik tingkat database

CPU, memori, dan disk I/O.
Koneksi aktif dan antrean koneksi.
Lock wait dan deadlock.
Laju insert/update/delete vs beban read.

Praktik yang berguna: simpan daftar 10 query paling mahal setiap minggu. Dari sana, Anda bisa melihat query mana yang stabil, mana yang memburuk, dan mana yang baru muncul setelah rilis fitur.

Kesalahan Umum Saat Mengoptimalkan Query

Menambah index tanpa melihat EXPLAIN.
Menguji hanya dengan data kecil yang tidak mencerminkan produksi.
Fokus pada satu query lambat padahal akar masalahnya N+1 atau frekuensi query berlebihan.
Mengabaikan trade-off write saat menambah banyak index.
Mengoptimalkan query yang jarang dipakai sementara jalur panas aplikasi tetap bermasalah.

Checklist Ringkas yang Bisa Dipakai Tim

Apakah query ini termasuk jalur panas aplikasi?
Apakah ada data slow query log atau APM yang mendukung?
Sudahkah hasil EXPLAIN atau EXPLAIN ANALYZE dibaca?
Apakah jumlah baris yang dibaca jauh lebih besar daripada hasil yang dikembalikan?
Apakah WHERE dan ORDER BY ditopang index yang cocok?
Apakah composite index diperlukan?
Apakah query ini memakai OFFSET besar?
Apakah keyset pagination lebih masuk akal?
Apakah ada N+1 query dari ORM atau loop aplikasi?
Apakah perubahan ini sudah diuji pada dataset yang representatif?
Apakah dampaknya terhadap write cost dan storage index sudah dipertimbangkan?
Apakah metrik pasca-rilis dipantau untuk memastikan perbaikan benar-benar efektif?

Penutup

Audit query lambat pada sistem yang datanya terus tumbuh adalah proses diagnosis yang disiplin: temukan query mahal, baca rencana eksekusi, pahami pola akses, lalu perbaiki desain query atau index berdasarkan bukti. Banyak masalah performa berasal dari hal yang berulang: index yang tidak sesuai, sort tanpa index, offset besar, dan N+1 query.

Jika Anda ingin hasil yang tahan lama, jangan hanya mengejar satu perbaikan cepat. Bangun kebiasaan audit berkala, ukur query berdasarkan metrik nyata, dan sesuaikan strategi saat data dan pola trafik berubah. Dengan pendekatan itu, query yang hari ini cepat tidak akan diam-diam menjadi masalah besar enam bulan lagi.

Quick Search

shopping_cart Keranjang