Audit Query SQL Saat Error Spike: Index, COUNT, dan Pagination

Audit query SQL saat error spike perlu dilakukan segera ketika latensi naik, timeout bertambah, atau error 5xx melonjak tanpa perubahan trafik yang terlihat besar. Dalam banyak insiden produksi, akar masalahnya adalah query yang sebelumnya “cukup cepat” lalu gagal skala saat jumlah data tumbuh, pola akses berubah, atau beberapa query berat berjalan bersamaan.

Gejala paling umum adalah endpoint list yang melambat, COUNT(*) yang ikut mahal, pagination berbasis OFFSET makin lambat di halaman belakang, serta index yang tidak lagi cocok dengan pola filter dan sort. Fokus audit bukan hanya mencari query paling lambat, tetapi memahami mengapa database memilih rencana eksekusi tertentu, lalu memperbaiki query, index, dan pola akses dengan risiko operasional yang terukur.

Gejala yang Mengarah ke Bottleneck SQL

Sebelum langsung mengubah kode, identifikasi apakah lonjakan error memang berasal dari database. Beberapa gejala yang sering muncul:

Latency naik bertahap pada endpoint list, search, dashboard, atau admin page.
Timeout aplikasi meningkat, terutama pada request yang memanggil query dengan filter kompleks atau pagination halaman jauh.
CPU database tinggi, disk I/O naik, atau jumlah koneksi aktif menumpuk.
Query yang sama muncul berulang di slow query log.
Error spike saat data bertambah, meski deploy aplikasi tidak signifikan.

Pola ini berbeda dengan bottleneck murni di layer aplikasi. Jika CPU aplikasi tinggi tetapi query time rendah dan database idle, masalah kemungkinan ada di serialisasi JSON, template rendering, lock internal, queue backlog, atau integrasi eksternal. Sebaliknya, jika request time tinggi dan sebagian besar waktu habis di query, audit SQL harus menjadi prioritas.

Membedakan CPU Aplikasi vs Bottleneck SQL

Tanda masalah ada di aplikasi

CPU pod/VM aplikasi tinggi, tetapi CPU database normal.
Profiling menunjukkan waktu habis di transformasi data, enkripsi, template, atau parsing.
Durasi query dari APM/log rendah, tetapi total request tetap lambat.
Jumlah query sedikit, namun payload respons sangat besar atau ada proses sinkron lain.

Tanda masalah ada di SQL

Slow query log memperlihatkan query list atau COUNT(*) dengan durasi tinggi.
EXPLAIN menunjukkan full table scan, filesort, atau penggunaan index yang buruk.
Connection pool menunggu lama karena query aktif terlalu banyak atau terlalu lama.
Latensi meningkat tajam pada halaman pagination tinggi atau filter tertentu.

Prinsip praktis saat insiden: jika request yang gagal semuanya menyentuh endpoint dengan pola query serupa, mulai dari database lebih dulu. Jika semua endpoint terdampak merata termasuk yang minim query, periksa kapasitas aplikasi, jaringan, atau dependency lain.

Langkah Investigasi Saat Insiden

1. Kumpulkan query paling mahal

Mulailah dari sumber data yang paling cepat tersedia:

Slow query log di database.
APM/tracing yang menampilkan durasi SQL per request.
Application log jika query logging sudah tersedia.
Statistik database seperti query yang paling sering dan paling lama.

Tujuannya bukan sekadar mengambil query terlama tunggal, tetapi mencari kombinasi frekuensi × durasi. Query 200 ms yang dipanggil ribuan kali bisa lebih merusak daripada satu query 2 detik yang jarang muncul.

2. Kelompokkan berdasarkan pola

Kelompokkan query menjadi beberapa kategori:

Query list utama dengan ORDER BY dan filter.
COUNT(*) untuk total data pagination.
Pagination dengan LIMIT ... OFFSET ....
Query berulang per item yang mengindikasikan N+1.
Query join besar yang memuat kolom terlalu banyak.

3. Jalankan EXPLAIN pada query kandidat

Jangan menebak. Jalankan EXPLAIN pada query yang paling sering muncul saat insiden. Hal yang perlu diperhatikan secara umum:

Apakah database memakai index yang diharapkan?
Berapa banyak baris yang diperkirakan akan dibaca?
Apakah ada full scan karena filter tidak selektif atau index tidak cocok?
Apakah ORDER BY menyebabkan sort mahal?
Apakah join dilakukan dengan urutan yang masuk akal?

Contoh:

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

Jika hasil EXPLAIN menunjukkan pembacaan baris sangat besar, penggunaan index parsial yang tidak cocok, atau kebutuhan sort terpisah, query ini pantas diperbaiki lebih dulu.

Masalah Nyata yang Sering Muncul

Query list berat karena missing atau composite index yang salah

Banyak endpoint list memakai pola filter + sort seperti:

SELECT id, title, status, created_at
FROM tickets
WHERE project_id = 99
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Query di atas terlihat sederhana, tetapi akan mahal jika hanya ada index pada project_id atau hanya pada status. Database mungkin tetap harus membaca banyak baris lalu menyortirnya.

Pendekatan yang lebih tepat biasanya adalah menyesuaikan index dengan pola akses dominan, misalnya filter dulu lalu sort:

CREATE INDEX idx_tickets_project_status_created_at
ON tickets (project_id, status, created_at);

Mengapa ini membantu? Karena database bisa mencari subset data sesuai project_id dan status, lalu mengambil urutan created_at dari index tanpa menyapu terlalu banyak baris.

Trade-off: setiap index tambahan menambah biaya tulis. Operasi INSERT, UPDATE, dan DELETE akan lebih mahal karena struktur index juga harus diperbarui. Karena itu, tambahkan index berdasarkan query produksi yang nyata, bukan “jaga-jaga”.

COUNT(*) mahal pada tabel besar

Masalah umum lain saat error spike adalah endpoint pagination yang menampilkan total data:

SELECT COUNT(*)
FROM orders
WHERE tenant_id = 42
  AND status = 'paid';

Di dataset kecil, query ini terasa ringan. Di dataset besar, terutama dengan filter, COUNT(*) bisa menjadi bagian termahal dari request. Banyak aplikasi menghitung total pada setiap page load, padahal pengguna belum tentu membutuhkan angka total presisi real-time.

Beberapa opsi perbaikan:

Pastikan ada index yang membantu filter count, misalnya kolom tenant dan status.
Kurangi frekuensi count, misalnya hanya hitung pada halaman pertama atau saat benar-benar diperlukan.
Gunakan cached/approximate count untuk UI yang tidak membutuhkan angka presisi real-time.
Pisahkan endpoint total dari endpoint list agar kegagalan count tidak memblokir pemuatan data utama.

Contoh perubahan arsitektur:

-- Sebelum: setiap request list melakukan dua query
SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;

SELECT COUNT(*)
FROM orders
WHERE tenant_id = 42 AND status = 'paid';

-- Sesudah: list tetap cepat, total opsional atau di-cache
SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Trade-off: approximate count atau cache membuat UI lebih ringan, tetapi angka total bisa sedikit usang. Untuk laporan keuangan atau audit, Anda tetap perlu jalur perhitungan yang akurat.

Offset pagination makin lambat pada halaman belakang

OFFSET sering menjadi penyebab tersembunyi. Query seperti ini akan memburuk seiring nomor halaman:

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

Masalahnya, database biasanya tetap perlu melewati banyak baris sebelum sampai ke data yang diminta. Jadi halaman 1 bisa cepat, tetapi halaman 200 sangat mahal.

Alternatif yang lebih stabil adalah keyset pagination atau seek method. Alih-alih meminta “skip 10000 baris”, aplikasi meminta “ambil data setelah baris terakhir yang sudah saya punya”.

-- Halaman pertama
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Halaman berikutnya, kirim cursor dari item terakhir
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND (
    created_at < '2025-01-10 09:30:00'
    OR (created_at = '2025-01-10 09:30:00' AND id < 123456)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Untuk pola ini, index juga harus mengikuti urutan akses, misalnya:

CREATE INDEX idx_orders_tenant_created_id
ON orders (tenant_id, created_at, id);

Trade-off: keyset pagination sangat baik untuk performa dan konsistensi list besar, tetapi tidak cocok jika UI benar-benar membutuhkan lompat langsung ke halaman 237. Untuk antarmuka admin tertentu, Anda mungkin masih perlu offset pagination secara terbatas.

N+1 query yang meledak saat trafik naik

N+1 sering tidak terlihat di data kecil. Misalnya aplikasi mengambil 50 order, lalu untuk setiap order menjalankan query customer terpisah.

-- Query pertama
SELECT id, customer_id, total_amount
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

-- Lalu berulang 50 kali
SELECT id, name, email
FROM customers
WHERE id = ?;

Pada satu request, pola ini mungkin tampak “masih aman”. Saat trafik naik, jumlah query meledak dan connection pool cepat penuh.

Perbaikannya adalah memuat data terkait sekaligus dengan join yang terkontrol atau batch fetch:

SELECT o.id, o.total_amount, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.tenant_id = 42
ORDER BY o.created_at DESC
LIMIT 50;

Atau dua query terkontrol jika join terlalu berat:

SELECT id, customer_id, total_amount
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

SELECT id, name, email
FROM customers
WHERE id IN (101, 102, 103, 104);

Trade-off: join besar bisa memperlebar payload dan memperumit optimasi. Jika relasi banyak dan kolom besar, strategi batch fetch sering lebih seimbang.

Cara Membaca EXPLAIN Secara Praktis

Format output EXPLAIN berbeda antar database, tetapi prinsip auditnya serupa:

Lihat metode akses: apakah menggunakan index atau scan penuh.
Lihat estimasi baris: jika sangat besar untuk request kecil, index atau filter kemungkinan tidak tepat.
Lihat bagian sort: ORDER BY tanpa index pendukung sering memicu kerja tambahan.
Lihat join: join pada kolom tanpa index bisa memperbesar biaya secara drastis.
Bandingkan sebelum/sesudah: jalankan EXPLAIN setelah perubahan index atau query, bukan hanya sekali.

Kesalahan umum adalah menambah index pada setiap kolom yang muncul di WHERE. Yang dibutuhkan biasanya bukan banyak single-column index, melainkan composite index yang mengikuti pola filter dan urutan sort yang paling dominan.

Contoh Sebelum dan Sesudah Audit Query

Sebelum

SELECT id, title, author_id, published_at
FROM articles
WHERE tenant_id = 7
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 4000;

SELECT COUNT(*)
FROM articles
WHERE tenant_id = 7
  AND status = 'published';

Masalah:

Offset besar membuat halaman belakang makin mahal.
Count dijalankan di setiap request.
Index mungkin tidak cocok dengan kombinasi filter + sort.

Sesudah

CREATE INDEX idx_articles_tenant_status_published_id
ON articles (tenant_id, status, published_at, id);

-- Halaman pertama
SELECT id, title, author_id, published_at
FROM articles
WHERE tenant_id = 7
  AND status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20;

-- Halaman berikutnya dengan cursor
SELECT id, title, author_id, published_at
FROM articles
WHERE tenant_id = 7
  AND status = 'published'
  AND (
    published_at < '2025-01-15 10:00:00'
    OR (published_at = '2025-01-15 10:00:00' AND id < 99881)
  )
ORDER BY published_at DESC, id DESC
LIMIT 20;

Perubahan tambahan:

Total artikel dipindahkan ke endpoint terpisah atau cache periodik.
Kolom yang diambil dibatasi hanya yang dibutuhkan.
Relasi penulis dimuat dengan batch, bukan query per baris.

Perbaikan Skema dan Query yang Paling Berdampak

1. Sesuaikan index dengan pola akses nyata

Buat index berdasarkan query produksi yang paling penting:

Kolom tenant atau organisasi jika sistem multi-tenant.
Kolom filter yang sering dipakai bersama.
Kolom sort yang selalu muncul setelah filter.
Kolom tie-breaker seperti id untuk pagination stabil.

Hindari:

Menambah index tanpa memverifikasi dengan EXPLAIN.
Membuat terlalu banyak index mirip yang meningkatkan biaya write.
Mengandalkan ORM default untuk semua query list besar.

2. Ambil kolom seperlunya

Jangan gunakan SELECT * pada endpoint list yang sering dipanggil. Selain memperbesar I/O, ini sering memaksa pembacaan data yang tidak dibutuhkan aplikasi.

3. Pisahkan jalur list dan jalur total

List harus tetap bisa tampil cepat walau total count gagal, lambat, atau belum segar. Ini sering memberi perbaikan besar saat insiden karena menghilangkan kerja yang tidak selalu wajib.

4. Kurangi fan-out query

Audit endpoint yang memicu banyak query turunan: relasi per item, pengecekan permission per baris, atau lookup metadata yang sama berulang. Gabungkan, batch, atau cache hasil yang aman untuk di-cache.

5. Pertimbangkan dampak pertumbuhan data

Query yang cepat di 100 ribu baris belum tentu aman di 50 juta baris. Saat mendesain endpoint list, pikirkan dari awal:

Apakah sort dan filter bisa memanfaatkan index?
Apakah pagination akan tetap stabil pada dataset besar?
Apakah count wajib real-time?
Apakah retensi data atau arsip diperlukan agar tabel aktif tetap terkendali?

Checklist Mitigasi Cepat Saat Insiden

Saat error spike sedang berlangsung, fokus pada perubahan yang risikonya terkendali dan dampaknya cepat terasa.

Identifikasi 3-5 query paling mahal dari slow query log atau tracing.
Nonaktifkan sementara fitur berat seperti total count real-time, export sinkron, atau filter sekunder yang jarang dipakai.
Turunkan page size jika endpoint list mengambil terlalu banyak data.
Batasi concurrency untuk endpoint yang paling mahal agar database tidak kolaps total.
Tambahkan index yang paling jelas manfaatnya berdasarkan pola WHERE + ORDER BY yang dominan.
Ganti offset pagination pada endpoint kritis menjadi cursor/keyset bila memungkinkan.
Hilangkan N+1 pada jalur request yang paling sering dipanggil.
Validasi dengan EXPLAIN dan pantau lagi query time setelah perubahan.
Amati efek samping write setelah penambahan index, terutama pada tabel dengan traffic tulis tinggi.

Jika insiden sedang aktif, perubahan paling aman biasanya adalah mengurangi kerja query yang tidak wajib lebih dulu: mematikan count real-time, mengecilkan page size, dan menghilangkan N+1. Refactor besar sebaiknya dilakukan setelah sistem stabil.

Kesalahan Umum Saat Audit Query SQL

Hanya melihat rata-rata latency, padahal spike sering dipicu oleh tail latency.
Mengandalkan query di lokal dengan dataset kecil yang tidak merepresentasikan produksi.
Menambah index sembarang tanpa memahami pola akses.
Memperbaiki satu query tetapi melupakan count, join turunan, atau query relasi di belakangnya.
Tidak mengukur ulang setelah perubahan, sehingga sulit membuktikan dampaknya.

Penutup

Audit query SQL saat error spike bukan pekerjaan kosmetik. Fokus utamanya adalah menemukan query yang memburuk karena pertumbuhan data dan pola akses: query list berat, COUNT(*) mahal, OFFSET yang tidak skala, serta index yang tidak cocok. Dengan slow query log, EXPLAIN, dan perubahan terarah pada query serta skema, Anda bisa menurunkan latensi secara nyata tanpa menebak-nebak.

Jika harus memilih urutan prioritas saat insiden, mulai dari: identifikasi slow query, verifikasi dengan EXPLAIN, hentikan count yang tidak wajib, ganti offset pagination pada jalur kritis, lalu tambahkan composite index yang sesuai. Setelah sistem stabil, lanjutkan dengan audit N+1, pengurangan payload, dan evaluasi strategi retensi atau arsip data.

Quick Search

shopping_cart Keranjang