Audit Query Lambat dengan Local Reasoning pada Desain Index SQL

Audit query lambat dengan local reasoning pada desain index SQL berarti kita menilai performa query dari bukti yang terlihat langsung: bentuk filter, urutan sort, jumlah baris yang disentuh, dan index yang benar-benar bisa dipakai mesin database. Jika cost sebuah query hanya “cepat selama data belum besar” atau “aman karena biasanya user membuka halaman awal”, itu tanda ada asumsi tersembunyi yang rapuh.

Prinsip ini sejalan dengan gagasan local reasoning for global properties: properti sistem yang baik seharusnya dapat dipahami dari bagian-bagian lokalnya, bukan dari harapan bahwa kondisi global akan selalu bersahabat. Dalam konteks SQL, targetnya sederhana: setiap query penting harus bisa dijelaskan jalur eksekusinya tanpa bergantung pada keberuntungan distribusi data, ukuran tabel saat ini, atau cache.

Artikel ini fokus pada tiga bottleneck yang sering muncul di produksi: WHERE + ORDER BY tidak selaras dengan index, offset pagination yang makin mahal ketika data bertambah, dan query list+count yang memicu scan besar. Kita akan membahas langkah audit, membaca EXPLAIN, memilih composite index, kapan covering index berguna, kapan beralih ke keyset pagination, serta trade-off write amplification dan storage.

Kerangka pikir: cost query harus bisa dijelaskan secara lokal

Sebuah query layak dipercaya jika Anda bisa menjawab pertanyaan berikut tanpa asumsi samar:

Baris mana yang dicari lebih dulu: lewat index atau scan?
Apakah filter pada WHERE sesuai dengan urutan kolom index?
Apakah ORDER BY dapat dipenuhi oleh index, atau database harus sort ulang?
Berapa banyak baris yang tetap harus dibaca sebelum hasil akhir LIMIT didapat?
Apakah query mengambil kolom di luar index sehingga perlu lookup tambahan ke tabel?

Jika jawaban atas pertanyaan di atas adalah “bergantung”, “biasanya”, atau “nanti optimizer pintar memilih”, maka audit belum selesai. Tujuan local reasoning bukan menebak perilaku optimizer secara detail, melainkan memastikan struktur query dan index memberi jalur yang masuk akal secara konsisten.

Aturan praktis: query yang sehat seharusnya tetap masuk akal ketika jumlah data tumbuh 10x atau 100x. Mungkin latency naik, tetapi pola kerjanya tidak berubah dari lookup/index range scan menjadi full scan dan sort besar.

Gejala query lambat di produksi

Sebelum masuk ke EXPLAIN, kenali gejala yang biasanya menunjukkan masalah desain index atau bentuk query:

Endpoint daftar data cepat di halaman 1, tetapi lambat di halaman 100 atau 1000.
CPU database naik saat ada trafik baca, padahal jumlah row yang dikirim ke aplikasi sedikit.
Latency query melonjak pada jam sibuk meskipun query tampak sederhana.
Sort atau temporary structure sering muncul pada plan.
Query COUNT(*) untuk filter yang sama lebih mahal dari yang diperkirakan.
Penambahan index baru memperbaiki satu query tetapi memperlambat insert/update secara nyata.

Gejala-gejala ini biasanya bukan masalah “database kurang kuat”, tetapi query tidak memberi akses path yang efisien secara lokal.

Langkah audit query lambat

1. Pilih query yang benar-benar penting

Mulailah dari query yang memenuhi salah satu kondisi berikut:

Frekuensinya tinggi.
Latencynya tinggi.
Dampaknya besar ke endpoint utama.
Menjadi sumber load CPU, I/O, atau lock contention.

Jangan audit query secara abstrak. Ambil bentuk query nyata dari log aplikasi, slow query log, APM, atau query capture dari database.

2. Catat bentuk query final, bukan versi ideal

Yang diaudit adalah SQL yang benar-benar dijalankan, termasuk:

Kolom di SELECT
Kondisi WHERE
ORDER BY
LIMIT dan OFFSET
Join, subquery, dan fungsi pada kolom

Masalah performa sering tersembunyi di detail kecil, misalnya menambahkan ORDER BY created_at DESC tanpa index yang sesuai, atau membungkus kolom terindeks dengan fungsi sehingga index sulit dipakai.

3. Jalankan EXPLAIN

Bacalah EXPLAIN untuk menjawab tiga hal:

Apakah database menemukan candidate rows lewat index atau scan?
Apakah sorting dilakukan dari urutan index atau sort terpisah?
Berapa banyak row yang diperkirakan atau benar-benar disentuh sebelum LIMIT terpenuhi?

Nama kolom output EXPLAIN berbeda antar database, tetapi pola berpikirnya sama. Cari tanda-tanda berikut:

Full table scan atau scan besar padahal filter seharusnya selektif.
Sort terpisah karena urutan index tidak cocok dengan ORDER BY.
Rows examined jauh lebih besar dari rows returned.
Lookup tambahan ke tabel karena index tidak menutup kebutuhan kolom.

4. Uji dengan data yang representatif

Query yang tampak cepat di dataset kecil sering menipu. Audit yang baik memakai distribusi data yang mendekati produksi: jumlah row, skew kategori, row terbaru jauh lebih sering dibaca, dan sebagainya.

Bottleneck 1: WHERE + ORDER BY tidak selaras dengan index

Kasus paling umum adalah query daftar data yang memfilter satu hal tetapi mengurutkan hal lain, sementara index tidak mendukung kedua kebutuhan sekaligus.

Contoh sebelum: filter cocok, sort mahal

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

Misalkan schema hanya punya index terpisah:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Secara lokal ini belum cukup. Query butuh tiga hal sekaligus:

Filter customer_id = 42
Filter status = 'paid'
Urutan created_at DESC

Index tunggal yang terpisah sering membuat optimizer harus memilih kompromi: memakai index untuk filter lalu melakukan sort tambahan, atau memakai index urutan lalu menyaring banyak row. Keduanya bisa mahal saat data membesar.

Perbaikan: composite index sesuai pola akses

CREATE INDEX idx_orders_customer_status_created_at
ON orders(customer_id, status, created_at);

Dengan index komposit seperti ini, database punya jalur lokal yang lebih jelas: cari rentang row dengan customer_id dan status, lalu baca dalam urutan created_at. Untuk query daftar terbaru dengan LIMIT, ini sangat membantu karena database tidak perlu menyortir himpunan besar terlebih dulu.

Mengapa ini bekerja? Karena urutan kolom index mengikuti pola pencarian: kolom equality filter diletakkan di depan, lalu kolom untuk pengurutan. Ini membuat range yang dibaca sempit dan hasil sudah mendekati atau tepat pada urutan yang dibutuhkan.

Kesalahan umum saat memilih composite index

Urutan kolom salah. Misalnya menaruh kolom sort di depan padahal filter equality jauh lebih selektif.
Terlalu banyak index tunggal. Banyak orang mengira index terpisah selalu cukup. Untuk query kompleks, sering tidak.
Mengabaikan pola query dominan. Composite index harus mengikuti query yang benar-benar sering dan penting, bukan semua kemungkinan.
Menganggap satu index cocok untuk semua query. Kadang satu tabel butuh beberapa index komposit untuk pola akses yang berbeda, tetapi pilih dengan disiplin agar biaya tulis tetap terkendali.

Kapan covering index berguna

Jika query daftar hanya perlu beberapa kolom kecil, pertimbangkan covering index: index yang memuat semua kolom yang dibaca query, sehingga database tidak perlu mengambil row lengkap dari tabel untuk setiap hasil.

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

Untuk pola seperti ini, index yang mencakup kolom filter, sort, dan kolom hasil bisa mengurangi lookup tambahan. Bentuk persisnya bergantung pada database yang dipakai, tetapi prinsipnya sama: jika query bisa selesai dari index saja, I/O sering lebih rendah.

Namun covering index bukan gratis:

Ukuran index membesar.
Insert/update/delete menjadi lebih mahal.
Pemilihan kolom harus disiplin; jangan memasukkan kolom besar atau jarang dipakai tanpa alasan kuat.

Bottleneck 2: offset pagination makin mahal saat data tumbuh

Offset pagination terlihat sederhana, tetapi cost-nya sering tersembunyi. Query berikut tampak aman:

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

Masalahnya, database biasanya tetap harus melewati banyak row untuk mencapai offset tersebut. Walaupun hasil akhirnya hanya 20 row, cost lokal query ini bukan 20 row, melainkan offset + limit row yang perlu diurutkan atau dilintasi.

Inilah contoh klasik query yang tampak sehat hanya karena user jarang membuka halaman jauh. Asumsi itu tidak stabil: data terus bertambah, admin atau job internal bisa mengakses halaman dalam, dan endpoint tertentu bisa memaksa offset besar.

Gejala di produksi

Halaman awal cepat, halaman jauh lambat drastis.
Latency meningkat seiring umur tabel walau query tidak berubah.
Rows examined sangat besar dibanding row yang dikembalikan.

Kapan offset masih masuk akal

Offset pagination masih layak jika:

Dataset kecil.
Halaman dalam hampir tidak pernah diakses.
Tidak ada kebutuhan latency ketat.
Biaya implementasi keyset belum sepadan.

Tetapi untuk tabel besar atau endpoint penting, gunakan local reasoning yang lebih kuat: jangan andalkan kemampuan database “melewati” banyak row dengan murah.

Beralih ke keyset pagination

Keyset pagination memakai penanda row terakhir dari halaman sebelumnya, bukan offset numerik.

Sebelum:

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

Sesudah:

SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
  AND (created_at, id) < ('2026-08-20 10:15:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Dan index yang mendukung:

CREATE INDEX idx_orders_customer_created_id
ON orders(customer_id, created_at, id);

Mengapa keyset lebih stabil? Karena database tidak perlu menghitung atau melewati ribuan row hanya untuk mencapai posisi tertentu. Ia cukup melanjutkan dari titik terakhir yang sudah diketahui, sehingga cost halaman berikutnya tetap dekat dengan jumlah row yang diminta.

Catatan penting untuk keyset pagination

Gunakan urutan yang stabil dan unik. Jika created_at tidak unik, tambahkan id sebagai tie-breaker.
API perlu mengirim cursor atau pasangan nilai terakhir, bukan nomor halaman murni.
Pengalaman pengguna untuk “lompat ke halaman 500” berbeda; keyset lebih cocok untuk alur next/previous atau infinite scroll.

Bottleneck 3: pola list + count yang memicu scan besar

Banyak endpoint daftar menjalankan dua query:

Mengambil data halaman saat ini.
Menghitung total seluruh hasil filter.

Contoh:

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

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

Query pertama mungkin sudah cukup efisien dengan index yang tepat. Tetapi query count tetap bisa mahal jika filter mencakup bagian besar tabel. Di sinilah local reasoning berguna: jangan menyimpulkan endpoint “cepat” hanya dari query list. Audit seluruh rangkaian query yang dipicu oleh satu request.

Mengapa count sering mahal

Count tetap harus memverifikasi semua row yang cocok.
Jika filter tidak selektif, scan bisa besar walau hasil halaman kecil.
Join dan kondisi tambahan membuat count semakin berat.

Opsi perbaikan

Nilai apakah total exact benar-benar dibutuhkan. Banyak UI cukup menampilkan “lebih banyak hasil tersedia” tanpa total pasti.
Tunda count. Ambil daftar dulu, lalu hitung total hanya jika user benar-benar membutuhkannya.
Gunakan count terpisah dengan SLA berbeda. Misalnya dipanggil asynchronous atau di-cache untuk dashboard.
Pastikan count memiliki index path yang masuk akal. Jika filter sering dipakai, composite index mungkin juga perlu mendukung count.

Contoh perubahan desain API

Alih-alih selalu mengembalikan:

{
  "items": [...],
  "page": 1,
  "page_size": 20,
  "total": 245678
}

Pertimbangkan:

{
  "items": [...],
  "next_cursor": "2026-08-20T10:15:00Z_987654",
  "has_more": true
}

Perubahan ini sering memangkas query count mahal dan sekaligus membuka jalan ke keyset pagination.

Cara membaca EXPLAIN untuk audit index

EXPLAIN berbeda antar vendor, tetapi audit praktis bisa memakai daftar pertanyaan yang konsisten.

1. Apakah query memakai index yang benar-benar sesuai?

Jangan berhenti di fakta bahwa “index dipakai”. Index yang dipakai belum tentu index yang tepat. Jika query tetap menyentuh banyak row atau masih butuh sort besar, desain index belum selaras dengan pola akses.

2. Apakah ada sort terpisah?

Jika plan menunjukkan sorting di luar urutan index, cek apakah ORDER BY bisa disatukan dengan index komposit yang juga melayani filter. Sort terpisah sering menjadi sumber latency yang tidak terlihat pada data kecil.

3. Berapa rows examined dibanding rows returned?

Rasio yang sangat besar adalah sinyal kuat bahwa query bekerja terlalu keras untuk hasil yang sedikit. Untuk endpoint daftar dengan LIMIT 20, membaca ribuan atau jutaan row biasanya perlu dipertanyakan.

4. Apakah ada lookup tambahan ke tabel?

Jika query mengambil banyak kolom yang tidak ada di index, database mungkin perlu mengakses row tabel satu per satu setelah menemukan kandidat dari index. Pada jumlah hasil kecil ini mungkin baik-baik saja; pada hasil lebih besar atau trafik tinggi, covering index bisa layak dipertimbangkan.

5. Apakah fungsi atau ekspresi menghalangi index?

Contoh umum:

SELECT id, created_at
FROM orders
WHERE DATE(created_at) = '2026-08-20'
ORDER BY created_at DESC;

Membungkus kolom dengan fungsi sering membuat pencarian via index menjadi lebih sulit. Lebih aman ubah menjadi range yang eksplisit:

SELECT id, created_at
FROM orders
WHERE created_at >= '2026-08-20 00:00:00'
  AND created_at < '2026-08-21 00:00:00'
ORDER BY created_at DESC;

Contoh audit sebelum dan sesudah

Kasus: halaman daftar invoice admin mulai melambat

Gejala:

Halaman 1 masih cepat.
Filter status tertentu lambat.
Admin membuka halaman dalam dan timeout meningkat.

Query awal:

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

SELECT COUNT(*)
FROM invoices
WHERE status = 'overdue';

Masalah lokal yang bisa dijelaskan:

Offset besar membuat database tetap menelusuri banyak row.
Jika index hanya pada status atau created_at secara terpisah, list query berpotensi filter lalu sort atau sebaliknya.
Count untuk status yang umum tetap mahal.

Perbaikan bertahap:

Tambahkan index komposit untuk pola list.
Ganti offset pagination menjadi keyset pagination.
Hapus count exact dari request utama jika tidak wajib.

Sesudah:

CREATE INDEX idx_invoices_status_created_id
ON invoices(status, created_at, id);

SELECT id, customer_id, status, created_at, total_amount
FROM invoices
WHERE status = 'overdue'
  AND (created_at, id) < ('2026-08-20 10:15:00', 120045)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Dengan bentuk ini, cost query jauh lebih bisa dijelaskan secara lokal: ada filter equality, ada urutan stabil, dan halaman berikutnya melanjutkan dari cursor, bukan melewati ribuan row.

Checklist review schema dan query

Checklist query

Apakah setiap query penting punya WHERE dan ORDER BY yang selaras dengan index?
Apakah query daftar memakai offset besar?
Apakah ada pola list+count yang selalu dijalankan bersama?
Apakah ada fungsi pada kolom terindeks di filter atau sort?
Apakah jumlah kolom pada SELECT terlalu lebar untuk kebutuhan list?
Apakah urutan sort stabil untuk pagination, misalnya ditambah id?

Checklist schema dan index

Apakah composite index mengikuti pola query dominan, bukan urutan kolom yang kebetulan?
Apakah ada terlalu banyak index tunggal yang seharusnya digantikan sebagian oleh index komposit yang lebih relevan?
Apakah covering index dipakai hanya pada query yang benar-benar butuh?
Apakah index baru menambah biaya tulis yang masih bisa diterima?
Apakah ada index yang jarang dipakai dan layak dihapus?

Trade-off: read performance vs write amplification dan storage

Setiap index tambahan mempercepat sebagian query, tetapi juga membawa biaya nyata:

Write amplification: insert, update, dan delete harus memperbarui lebih banyak struktur index.
Storage: index besar memakan ruang disk dan cache memory.
Maintenance: terlalu banyak index mempersulit audit dan membuat optimizer punya lebih banyak pilihan yang tidak selalu ideal.

Karena itu, local reasoning harus diterapkan juga pada schema: setiap index harus punya justifikasi query yang jelas. Jika Anda tidak bisa menyebut query utama yang dibantu sebuah index, kemungkinan index tersebut perlu ditinjau ulang.

Covering index terutama perlu disiplin. Sangat berguna untuk query list panas yang kecil dan sering, tetapi bisa berlebihan jika memasukkan banyak kolom atau dipasang pada tabel dengan volume tulis tinggi.

Debugging tips saat hasil audit tidak sesuai harapan

Bandingkan query plan sebelum dan sesudah index. Jangan berasumsi index baru pasti dipakai.
Uji pada data representatif. Dataset kecil sering menyamarkan masalah sort dan offset.
Lihat query turunan dari ORM. Query builder kadang menambahkan sort, select kolom lebar, atau count otomatis.
Periksa kestabilan sort. Pagination tanpa tie-breaker bisa menghasilkan duplikasi atau row terlewat.
Audit per request, bukan per query tunggal saja. Satu endpoint bisa menembakkan list, count, preload relasi, dan query tambahan lain.

Penutup

Inti audit query lambat dengan local reasoning pada desain index SQL adalah menolak asumsi tersembunyi. Query harus bisa dijelaskan cost-nya dari bentuk lokalnya: filter apa yang dipakai, bagaimana urutannya, berapa banyak row yang disentuh, dan index mana yang benar-benar melayani jalur tersebut.

Dalam praktik, tiga perbaikan yang paling sering memberi hasil nyata adalah:

Menyelaraskan WHERE dan ORDER BY dengan composite index.
Mengganti offset pagination yang mahal dengan keyset pagination saat data besar.
Meninjau ulang pola list+count agar request utama tidak memicu scan besar tanpa kebutuhan yang jelas.

Jika setiap query penting dapat dipertanggungjawabkan secara lokal, performa global sistem menjadi jauh lebih dapat diprediksi saat data dan trafik tumbuh.

Quick Search

shopping_cart Keranjang