Audit Query SQL Lambat Saat Data Tumbuh Tanpa Tebak-tebakan

Ketika data masih kecil, banyak query terlihat “baik-baik saja”. Masalah baru muncul setelah tabel membengkak: endpoint mulai melambat, CPU database naik, latency p95 memburuk, dan query yang dulu selesai cepat kini tiba-tiba mahal. Audit query SQL lambat yang benar tidak dimulai dari opini seperti “tambahkan index saja”, melainkan dari bukti: query aktual, rencana eksekusi, jumlah baris yang dipindai, pola akses, dan biaya operasi seperti sort, scan, dan join.

Artikel ini membahas cara mengaudit query SQL lambat saat data tumbuh dengan pendekatan yang terukur. Fokusnya adalah diagnosis yang bisa diulang oleh tim backend: gejala apa yang perlu dikumpulkan, cara membaca EXPLAIN dan EXPLAIN ANALYZE, bagaimana mengenali tanda full scan, sorting mahal, offset pagination yang makin buruk, index yang tidak terpakai, dan bottleneck pada join. Di akhir, ada checklist investigasi yang bisa langsung dipakai.

Mulai dari gejala, bukan asumsi

Tujuan audit bukan sekadar mencari query yang terasa lambat, tetapi menemukan mengapa query itu lambat pada kondisi data dan beban nyata. Beberapa gejala yang biasanya muncul saat volume data naik:

Latency API memburuk secara bertahap, terutama pada endpoint daftar, pencarian, atau laporan.
CPU database tinggi saat jam sibuk, meski aplikasi tidak berubah banyak.
I/O meningkat karena banyak halaman data dibaca dari disk atau buffer.
Query timeout atau koneksi menumpuk pada pool.
Performa tidak stabil: kadang cepat, kadang sangat lambat, bergantung pada parameter query.

Gejala-gejala ini penting karena membantu membedakan masalah query dari masalah lain seperti koneksi, lock, konfigurasi pool, atau jaringan. Audit yang baik selalu dimulai dengan mengumpulkan:

Query SQL aktual yang dieksekusi, bukan pseudo-code ORM.
Nilai parameter nyata.
Durasi eksekusi dan frekuensinya.
Jumlah baris hasil dan perkiraan ukuran dataset.
Rencana eksekusi dari EXPLAIN atau EXPLAIN ANALYZE.

Jika memungkinkan, analisis query yang memang paling mahal secara total: durasi × frekuensi. Query 50 ms yang dipanggil ribuan kali sering lebih merusak daripada satu query 2 detik yang jarang dipakai.

Data yang perlu dikumpulkan saat audit query SQL lambat

Sebelum mengubah query atau menambah index, pastikan Anda punya data minimum berikut:

Bentuk SQL final setelah dibangun ORM atau query builder.
Statistik dasar tabel: jumlah baris, distribusi nilai, kolom yang sering difilter, dan rasio nilai unik.
Pola akses: apakah query membaca satu tenant, satu rentang waktu, atau hampir seluruh tabel.
Rencana eksekusi untuk melihat urutan operasi yang dipilih optimizer.
Konteks penggunaan: daftar halaman pertama, halaman ke-1000, pencarian teks, ekspor, agregasi, dan sebagainya.

Kesalahan umum di tahap ini adalah menganalisis query dengan dataset lokal yang terlalu kecil. Query yang tampak cepat pada 10 ribu baris bisa gagal total pada 50 juta baris. Audit harus mencerminkan karakter produksi: bukan hanya schema, tetapi juga pola distribusi datanya.

Cara membaca EXPLAIN dan EXPLAIN ANALYZE

EXPLAIN menunjukkan rencana yang dipilih optimizer. EXPLAIN ANALYZE, pada sistem database yang mendukung, menambahkan eksekusi aktual: berapa lama setiap langkah berjalan dan berapa banyak baris yang benar-benar diproses. Untuk audit performa, EXPLAIN ANALYZE biasanya lebih berguna karena bisa menunjukkan selisih antara estimasi dan kenyataan.

Apa yang perlu diperhatikan

Metode akses: apakah menggunakan index lookup, range scan, atau full table scan.
Jumlah baris yang diperkirakan vs aktual: selisih besar bisa menandakan statistik tidak akurat atau predicate sulit diestimasi.
Filter dan kondisi join: apakah diterapkan lebih awal atau terlambat.
Sort, hash, temporary structure: ini sering mahal jika dataset besar.
Nested loop pada input besar: bisa menjadi bottleneck jika sisi dalam tidak terindeks baik.

Tanda full scan

Full scan tidak selalu salah. Jika tabel kecil, atau query memang butuh sebagian besar baris, scan penuh bisa masuk akal. Masalah muncul saat tabel besar dan query seharusnya selektif tetapi optimizer tetap membaca terlalu banyak baris.

Contoh query:

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

Jika tabel orders sangat besar dan tidak ada index yang mendukung filter dan urutan, database bisa melakukan:

Membaca banyak atau seluruh baris untuk mencari status = 'paid'.
Menyortir hasil yang cocok berdasarkan created_at.
Baru mengambil 50 baris teratas.

Ini mahal karena pekerjaan besar dilakukan sebelum LIMIT benar-benar membantu.

Kenapa estimasi vs aktual penting

Misalnya optimizer memperkirakan sebuah filter hanya mengembalikan 100 baris, padahal aktualnya 500 ribu baris. Akibatnya, optimizer bisa memilih strategi join atau akses index yang salah. Dalam audit, perbedaan ini memberi petunjuk bahwa masalah bukan hanya “query jelek”, tetapi mungkin juga statistik tabel usang, distribusi data timpang, atau kondisi filter yang tidak selektif.

Pola masalah yang paling sering muncul

1. Full scan karena index tidak cocok dengan pola query

Kesalahan paling umum adalah merasa kolom “sudah di-index”, padahal index tersebut tidak cocok dengan kombinasi WHERE, JOIN, dan ORDER BY. Index tunggal pada status belum tentu membantu jika query juga perlu mengurutkan berdasarkan created_at.

Contoh sebelum:

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

Jika hanya ada index di tenant_id atau hanya di status, database mungkin masih harus memproses terlalu banyak baris dan menyortir hasil.

Perbaikan yang sering lebih tepat adalah composite index yang mengikuti pola akses query, misalnya kolom filter yang paling relevan diikuti kolom urutan:

-- Contoh strategi, sesuaikan dengan database dan pola query nyata
CREATE INDEX idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at);

Kenapa ini membantu:

Database bisa mempersempit baris berdasarkan tenant_id dan status.
Urutan created_at sudah tersedia di index, sehingga biaya sort bisa berkurang atau hilang.
LIMIT 50 menjadi efektif lebih cepat karena engine tidak perlu memproses seluruh kandidat.

Namun urutan kolom index tidak boleh asal. Jika query paling sering memfilter tenant_id lalu status, dan mengurutkan created_at, maka susunan itu masuk akal. Jika pola query berbeda, susunan index juga bisa berbeda. Tidak ada satu urutan yang benar untuk semua kasus.

2. Sorting mahal pada dataset besar

Operasi ORDER BY bisa murah jika urutan dapat dipenuhi langsung dari index. Tetapi jika database harus mengumpulkan banyak baris lalu menyortirnya, biaya CPU dan memori naik tajam. Masalah ini sering muncul pada:

Daftar transaksi terbaru per tenant.
Pencarian dengan banyak filter opsional.
Laporan yang mengurutkan hasil agregasi besar.

Tanda sorting mahal biasanya terlihat dari rencana eksekusi yang menampilkan operasi sort terpisah setelah scan atau join besar. Jika hasil perantara sangat besar, operasi ini sering menjadi hotspot utama.

Pertanyaan audit yang perlu diajukan:

Apakah ORDER BY sejalan dengan index yang tersedia?
Apakah query menyortir jauh lebih banyak baris daripada yang dikembalikan?
Apakah sort terjadi setelah join yang memperbesar intermediate result?

3. Offset pagination yang makin memburuk

OFFSET terlihat praktis, tetapi memburuk seiring nomor halaman membesar. Untuk mengambil halaman ke-1000, database sering tetap harus melangkahi banyak baris sebelum menemukan baris yang ingin dikembalikan.

Contoh yang bermasalah saat data besar:

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

Masalahnya bukan hanya angka offset besar, tetapi pekerjaan yang harus dilakukan untuk melewati 50.000 baris lebih dulu.

Alternatif yang biasanya lebih stabil adalah keyset pagination:

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND (created_at, id) < ('2025-01-15 10:30:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Kenapa keyset pagination lebih baik:

Database melanjutkan dari posisi terakhir, bukan menghitung dari awal.
Biaya query cenderung konsisten pada halaman dalam.
Lebih cocok untuk daftar kronologis seperti feed, order, log, atau event.

Trade-off-nya:

Tidak cocok jika Anda benar-benar membutuhkan “lompat ke halaman 1200”.
Perlu cursor atau nilai penanda dari baris terakhir halaman sebelumnya.
Urutan harus stabil; biasanya perlu tie-breaker seperti id.

4. Index ada, tetapi tidak terpakai

Adanya index tidak menjamin optimizer akan menggunakannya. Beberapa penyebab umum:

Predicate tidak selektif: filter mencocokkan terlalu banyak baris, sehingga scan penuh dianggap lebih murah.
Fungsi pada kolom: misalnya membungkus kolom dalam fungsi dapat membuat index sulit dipakai.
Tipe data tidak konsisten: perbandingan lintas tipe dapat menghambat pemakaian index.
Leading wildcard pada pencarian string, misalnya LIKE '%abc', yang sering tidak bisa menggunakan index B-tree biasa secara efektif.
Urutan kolom composite index tidak cocok dengan kondisi query.

Contoh yang sering menurunkan peluang pemakaian index:

SELECT *
FROM users
WHERE DATE(created_at) = '2025-01-15';

Lebih baik, bila sesuai kebutuhan, gunakan rentang yang mempertahankan sifat pencarian indeks:

SELECT *
FROM users
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at <  '2025-01-16 00:00:00';

Prinsipnya: jika Anda mengubah kolom di sisi kiri predicate, optimizer sering kesulitan memanfaatkan index yang tersedia.

5. Bottleneck join

Join menjadi mahal ketika salah satu sisi menghasilkan terlalu banyak baris, atau ketika kolom join tidak terindeks dengan baik. Masalah ini sering tersembunyi karena query tampak sederhana di level aplikasi, tetapi rencana eksekusinya memproses data perantara yang besar.

Contoh:

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

Hal yang perlu diaudit:

Apakah filter pada orders diterapkan lebih dulu sehingga jumlah baris join mengecil?
Apakah orders.customer_id dan kunci pada customers mendukung join dengan baik?
Apakah join dilakukan sebelum sort besar yang sebenarnya bisa dihindari?

Sering kali perbaikannya bukan “tambah semua index”, tetapi memastikan tabel pendorong query adalah yang paling selektif dan memiliki index yang sesuai untuk filter dan urutan utama.

Contoh audit sebelum dan sesudah

Kasus: daftar order terbaru per tenant

Query awal:

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

Gejala:

Cepat saat tabel masih kecil.
Melambat saat jumlah order meningkat tajam.
Rencana eksekusi menunjukkan banyak baris dibaca dan ada operasi sort.

Analisis:

Filter memakai tenant_id dan status.
Urutan memakai created_at DESC.
LIMIT 50 baru efektif jika engine bisa mengambil 50 baris pertama langsung dari jalur akses yang sudah terurut.

Perbaikan:

CREATE INDEX idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at);

Hasil yang diharapkan dari audit ulang:

Jumlah baris yang dibaca turun signifikan.
Operasi sort besar hilang atau jauh berkurang.
Query lebih stabil saat data terus tumbuh.

Catatan penting: jika query memilih banyak kolom besar atau kolom yang tidak ada di jalur akses index, database mungkin tetap perlu membaca row utama setelah menemukan kandidat di index. Ini tetap bisa baik, tetapi perlu diamati pada plan aktual.

Strategi composite index yang masuk akal

Composite index efektif jika dibangun berdasarkan pola query nyata, bukan menebak semua kemungkinan filter. Saat mengaudit, pikirkan urutannya seperti ini:

Kolom dengan filter yang selalu hadir dan mempersempit data secara signifikan.
Kolom lain yang sering dipakai bersama dalam predicate.
Kolom untuk ORDER BY jika Anda ingin menghindari sort tambahan.

Contoh pola yang sering masuk akal:

(tenant_id, status, created_at) untuk daftar order per tenant dan status.
(tenant_id, created_at, id) untuk keyset pagination yang mengurutkan berdasarkan waktu dan tie-breaker id.

Trade-off composite index:

Setiap index tambahan menambah biaya INSERT, UPDATE, dan DELETE.
Index memakan ruang penyimpanan dan bisa memperlambat proses maintenance.
Terlalu banyak index membuat optimizer punya lebih banyak pilihan, tetapi tidak selalu membuat keputusan lebih baik.

Karena itu, index harus dibenarkan oleh query penting yang terukur, bukan ditambahkan hanya karena “mungkin berguna”.

Kapan indexing justru merugikan write

Setiap kali baris ditulis atau diubah, database juga harus memperbarui struktur index yang relevan. Pada sistem dengan trafik tulis tinggi, efeknya bisa nyata:

Latency write meningkat.
Throughput turun karena lebih banyak pekerjaan per transaksi.
Penyimpanan tumbuh lebih cepat.
Vacuum, compaction, atau maintenance index menjadi lebih mahal, tergantung engine yang dipakai.

Tanda bahwa indexing mulai berlebihan:

Banyak index jarang dipakai, tetapi write path melambat.
Kolom yang sering berubah ikut berada di banyak index.
Terdapat beberapa index yang tumpang tindih dengan manfaat kecil.

Prinsip praktisnya: index yang bagus untuk satu query read-heavy bisa buruk untuk workload write-heavy. Audit harus mempertimbangkan biaya total sistem, bukan hanya satu query.

Checklist investigasi yang bisa langsung dipakai

Ambil query asli dari log aplikasi atau slow query log, lengkap dengan parameter nyata.
Ukur frekuensi dan total biaya: prioritaskan query dengan dampak terbesar.
Jalankan EXPLAIN atau EXPLAIN ANALYZE pada data yang representatif.
Lihat metode akses: scan penuh, range scan, lookup, atau kombinasi join tertentu.
Bandingkan estimasi baris vs aktual: cari selisih besar.
Periksa operasi sort: apakah ORDER BY memaksa sort besar.
Periksa pagination: apakah memakai OFFSET besar yang sebenarnya bisa diganti keyset pagination.
Evaluasi index yang ada: apakah cocok dengan pola WHERE + JOIN + ORDER BY.
Periksa fungsi dan casting pada predicate yang bisa menghambat index.
Audit join: apakah tabel pendorong sudah selektif, dan apakah kolom join terdukung index.
Uji perubahan satu per satu: jangan ubah query, schema, dan banyak index sekaligus tanpa baseline.
Bandingkan hasil setelah perubahan menggunakan plan dan metrik yang sama, bukan kesan subjektif.

Kesalahan umum saat mengaudit query SQL lambat

Menambah index tanpa membaca plan. Ini sering menghasilkan index tidak terpakai atau duplikasi.
Mengandalkan data kecil lokal. Hasilnya menyesatkan saat dibawa ke produksi.
Fokus hanya pada durasi satu query tanpa melihat frekuensi.
Mengabaikan urutan hasil. Query dengan ORDER BY sering butuh strategi index berbeda dari query filter biasa.
Menganggap LIMIT pasti murah. Jika engine tetap harus scan dan sort banyak baris, LIMIT tidak menyelamatkan banyak.
Memakai OFFSET untuk semua pagination, termasuk halaman dalam yang sering diakses.

Penutup

Audit query SQL lambat saat data tumbuh harus berbasis bukti, bukan tebakan. Kuncinya adalah memahami bagaimana database benar-benar menjalankan query: berapa baris yang dibaca, apakah terjadi full scan, apakah sort menjadi mahal, apakah join membesar, dan apakah index yang ada benar-benar sesuai dengan pola akses.

Perbaikan yang paling sering berhasil bukanlah trik tunggal, melainkan kombinasi diagnosis yang disiplin: ambil query asli, baca EXPLAIN/EXPLAIN ANALYZE, identifikasi bottleneck utama, lalu pilih tindakan yang sesuai seperti composite index yang tepat, restrukturisasi predicate, atau mengganti offset pagination dengan keyset pagination. Dengan pendekatan ini, tim backend bisa memperbaiki performa secara konsisten tanpa bergantung pada dugaan.

Quick Search

shopping_cart Keranjang