Menganalisis Bottleneck SQL dengan EXPLAIN dan Keyset Pagination

Ketika tabel masih kecil, banyak query terlihat baik-baik saja. Masalah biasanya baru muncul saat jumlah baris bertambah: daftar halaman makin lambat, CPU database naik, I/O membengkak, dan query yang tadinya stabil mulai memiliki latensi yang tidak konsisten. Di titik ini, optimasi tidak cukup dilakukan dari sisi aplikasi saja; Anda perlu melihat bagaimana database benar-benar mengeksekusi query.

EXPLAIN dan EXPLAIN ANALYZE adalah alat utama untuk itu. Keduanya membantu membaca access path, estimasi jumlah baris, urutan operasi, dan tanda-tanda bahwa index tidak efektif. Pada kasus pagination, alat ini sering memperlihatkan penyebab yang sangat umum: offset pagination menjadi makin mahal seiring pertumbuhan data, sementara keyset pagination bisa menjaga biaya query tetap lebih terkendali jika didukung index yang tepat.

Dalam praktiknya, bottleneck SQL juga perlu dilihat end-to-end. Tren zero-config observability berbasis eBPF menunjukkan pendekatan yang berguna: jangan hanya melihat log aplikasi, tetapi korelasikan juga waktu di database, syscall, jaringan, dan resource host. Referensi seperti pendekatan yang diperkenalkan oleh ZeroServe menekankan bahwa latensi request sering merupakan gabungan beberapa lapisan, dan query database hanyalah salah satunya. Namun ketika query memang menjadi sumber utama, audit berbasis EXPLAIN tetap menjadi langkah paling langsung dan dapat ditindaklanjuti.

Gejala Bottleneck SQL Saat Data Tumbuh

Sebelum membuka EXPLAIN, kenali gejalanya. Ini membantu memprioritaskan query yang memang perlu diinvestigasi.

Latensi endpoint meningkat seiring nomor halaman. Halaman 1 cepat, halaman 1000 lambat.
CPU atau disk database tinggi saat trafik baca meningkat.
Query dengan filter sederhana tetap lambat, terutama pada tabel besar.
Lonjakan rows scanned jauh di atas rows returned.
Sorting mahal karena database harus melakukan filesort atau operasi sort di luar urutan index.
Index ada tetapi tidak dipakai, atau dipakai sebagian sehingga hasilnya masih mahal.

Gejala ini biasanya mengarah ke satu atau kombinasi dari masalah berikut:

Full table scan atau full index scan karena filter tidak selektif atau tidak sesuai index.
Urutan ORDER BY tidak didukung index.
OFFSET besar yang memaksa database melewati banyak baris sebelum mengembalikan hasil.
Predikat query tidak sargable, misalnya membungkus kolom dengan fungsi sehingga index sulit dipakai secara efisien.
Statistik query planner tidak akurat sehingga planner memilih rencana yang buruk.

EXPLAIN dan EXPLAIN ANALYZE: Apa yang Harus Dibaca

EXPLAIN menunjukkan rencana eksekusi yang dipilih optimizer. EXPLAIN ANALYZE menjalankan query dan menampilkan eksekusi aktual beserta waktu nyata. Nama field dan format output berbeda antar database, tetapi prinsip membacanya serupa.

Fokus utama saat membaca rencana eksekusi

Access path: apakah database melakukan index seek/range scan, index scan, atau full scan.
Rows estimate: berapa banyak baris yang diperkirakan akan dibaca di setiap langkah.
Rows actual pada EXPLAIN ANALYZE: apakah realisasi jauh berbeda dari estimasi.
Filter dan sort: apakah filter diterapkan sejak awal atau setelah membaca banyak baris; apakah sort bisa dipenuhi oleh index.
Join order dan metode join jika query melibatkan banyak tabel.

Untuk topik bottleneck SQL dan pagination, tiga sinyal paling penting adalah:

Rows scanned jauh lebih besar daripada rows returned.
Sort dilakukan di luar index, sering terlihat sebagai indikasi filesort atau sort node tambahan.
Predikat range atau cursor tidak didukung index yang sejalan dengan ORDER BY.

Tanda access path yang sehat vs bermasalah

Secara umum, access path yang sehat untuk query daftar data adalah membaca subset kecil melalui index, lalu mengembalikan sejumlah row terbatas. Access path yang bermasalah adalah harus membaca banyak row hanya untuk membuang sebagian besar hasil.

Patokan praktis: jika query mengembalikan 20 baris tetapi planner atau runtime menunjukkan pembacaan puluhan ribu baris, biasanya ada ruang optimasi yang nyata.

Contoh query yang mulai bermasalah

Misalkan ada tabel orders:

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

Query ini terlihat sederhana, tetapi saat data tumbuh, ada dua sumber biaya:

Database harus menemukan row dengan status = 'paid'.
Database harus melewati 20.000 row sesuai urutan sebelum mengembalikan 20 row berikutnya.

Jika index yang tersedia tidak mendukung kombinasi filter dan urutan ini, query bisa berubah menjadi scan besar ditambah sort mahal.

Cara Audit Query Lambat dengan EXPLAIN

1. Ambil query nyata, bukan query contoh

Mulailah dari slow query log, query log aplikasi, APM, atau observability tool. Pilih query dengan salah satu ciri berikut:

P95 atau P99 tinggi.
Sering dipanggil.
Mengonsumsi resource besar meski tidak terlalu sering.

Hindari mengoptimalkan query sintetis yang tidak mewakili traffic produksi.

2. Jalankan EXPLAIN lalu EXPLAIN ANALYZE

Gunakan parameter nyata yang mewakili kasus lambat, misalnya halaman tinggi atau rentang tanggal padat. Bandingkan:

Access path yang dipilih.
Estimasi row vs row aktual.
Biaya sort.
Node yang paling memakan waktu.

Jika estimasi jauh meleset, kemungkinan ada masalah statistik atau distribusi data yang tidak merata.

3. Cari tiga pola masalah paling umum

Full scan: planner membaca hampir seluruh tabel atau index.
Filesort atau sort node mahal: ORDER BY tidak didukung urutan index yang cocok.
Index tidak terpakai efektif: index ada, tetapi hanya membantu sedikit karena urutan kolom, selektivitas, atau bentuk predikat tidak tepat.

4. Validasi apakah index selaras dengan query

Index yang baik harus mendukung cara query memfilter dan mengurutkan data. Untuk query daftar, pikirkan urutan ini:

Kolom filter yang paling penting.
Kolom urut yang dipakai konsisten di query.
Kolom tie-breaker agar urutan stabil, biasanya id.

Contoh index yang sering lebih cocok untuk query tadi:

CREATE INDEX idx_orders_status_created_id
ON orders (status, created_at DESC, id DESC);

Dengan index seperti ini, database lebih mudah mengambil row status = 'paid' langsung dalam urutan created_at DESC, id DESC tanpa sort tambahan yang mahal.

5. Uji ulang setelah perubahan

Setelah menambah atau mengubah index, jalankan lagi EXPLAIN ANALYZE. Pastikan perbaikannya nyata, bukan hanya terlihat lebih baik di atas kertas. Perhatikan juga dampak samping: ukuran index bertambah, write cost naik, dan insert/update bisa sedikit lebih mahal.

Mengenali Index yang Kurang Efektif

Index bukan jaminan query cepat. Banyak bottleneck terjadi karena index ada, tetapi tidak cocok dengan bentuk query.

Pola umum index tidak efektif

Urutan kolom salah. Misalnya index pada (created_at, status) untuk query yang selalu filter status lalu urut created_at.
ORDER BY tidak sesuai urutan index. Database tetap perlu sort tambahan.
Predicate tidak sargable. Contoh: WHERE DATE(created_at) = '2025-01-01' sering lebih sulit dioptimalkan dibanding range waktu.
Selektivitas rendah. Kolom seperti status dengan sedikit variasi kadang tidak cukup membantu jika berdiri sendiri.
Query mengambil terlalu banyak kolom sehingga index tidak bisa membantu sebagai covering access, walau ini tergantung engine dan kebutuhan query.

Contoh perbaikan predicate

Daripada:

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

Lebih baik:

SELECT 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;

Bentuk range seperti ini biasanya lebih ramah terhadap index pada created_at.

Offset Pagination vs Keyset Pagination

Salah satu bottleneck SQL yang paling sering di API daftar data adalah pagination berbasis OFFSET. Secara fungsional mudah, tetapi biayanya membesar seiring nomor halaman.

Masalah offset pagination

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

Database tetap harus menemukan dan melewati 20.000 row pertama sesuai urutan. Walaupun index tersedia, biaya kerja masih bertambah seiring offset. Ini sebabnya halaman awal cepat, halaman jauh lambat.

Masalah lain:

Hasil bisa tidak stabil jika data baru masuk di tengah proses navigasi halaman.
Biaya tumbuh linear terhadap offset yang besar.
Lebih berat pada tabel dengan write aktif karena urutan data bisa berubah terus.

Keyset pagination: pendekatan yang lebih stabil

Pada keyset pagination, alih-alih mengatakan “lompat ke halaman ke-N”, Anda mengatakan “lanjutkan setelah row terakhir yang sudah saya lihat”. Query menggunakan cursor berbasis nilai kolom urut.

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

Ide intinya:

created_at adalah kolom urut utama.
id menjadi tie-breaker agar urutan unik dan stabil.
Cursor diambil dari baris terakhir halaman sebelumnya.

Dengan index yang tepat, database bisa langsung melanjutkan pencarian dari posisi cursor, bukan menghitung ulang dan melewati ribuan row.

Index untuk keyset pagination

Query di atas biasanya membutuhkan index yang selaras dengan filter dan urutan:

CREATE INDEX idx_orders_status_created_id
ON orders (status, created_at DESC, id DESC);

Mengapa urutan ini bekerja:

status menyaring subset data yang relevan.
created_at DESC mendukung urutan utama.
id DESC memastikan urutan deterministik saat timestamp sama.

Kapan keyset pagination lebih tepat

Feed, timeline, daftar transaksi, audit log, event log.
API dengan data besar dan urutan konsisten.
Navigasi “next page” atau “load more”.

Kapan offset pagination masih masuk akal

Tabel kecil.
Kebutuhan lompat langsung ke halaman tertentu.
Laporan internal dengan volume terbatas dan akses jarang.

Trade-off penting: keyset pagination unggul untuk performa dan konsistensi, tetapi tidak ideal bila UI benar-benar membutuhkan navigasi bebas ke halaman 127 secara langsung.

Contoh Audit Praktis: Dari Offset ke Keyset

Query awal

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

Saat dijalankan dengan EXPLAIN, Anda mungkin menemukan:

Rows yang harus dibaca sangat besar dibanding 20 row hasil.
Sort tambahan karena index tidak cocok dengan ORDER BY.
Biaya meningkat drastis pada offset besar.

Langkah perbaikan

Pastikan urutan stabil dengan ORDER BY created_at DESC, id DESC.
Buat index komposit yang selaras.
Ubah API agar menerima cursor, bukan nomor halaman besar.
Gunakan kondisi seek berdasarkan row terakhir.

Query hasil perbaikan

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

Setelah itu, jalankan EXPLAIN ANALYZE lagi. Hal yang ingin Anda lihat:

Access path berubah ke range seek atau index range scan yang lebih sempit.
Rows yang dibaca turun signifikan.
Sort node hilang atau jauh lebih murah.
Waktu total lebih stabil antar halaman.

Melihat Bottleneck Secara End-to-End, Bukan Hanya dari Aplikasi

Walau fokus artikel ini adalah menganalisis bottleneck SQL dengan EXPLAIN dan keyset pagination, penting untuk menempatkan hasil analisis dalam konteks sistem penuh. Pendekatan observability modern, termasuk yang mengandalkan eBPF dan minim konfigurasi, berguna untuk menjawab pertanyaan seperti:

Apakah latensi benar-benar dominan di database, atau justru di jaringan dan antrian koneksi?
Apakah satu query lambat memicu kemacetan koneksi sehingga request lain ikut terdampak?
Apakah CPU tinggi berasal dari sort di database, serialisasi respons, atau retry di aplikasi?

Referensi seperti ZeroServe menyoroti bahwa observability yang baik seharusnya mengurangi blind spot antar lapisan. Dalam praktik harian, kombinasi yang efektif adalah:

Gunakan observability atau tracing untuk menemukan endpoint dan query termahal.
Gunakan EXPLAIN atau EXPLAIN ANALYZE untuk membedah query spesifik.
Perbaiki query dan index.
Ukur ulang dari level request end-to-end, bukan hanya waktu query tunggal.

Kesalahan Umum Saat Mengoptimalkan Query

Menambah terlalu banyak index tanpa mengukur dampak ke write path.
Mengandalkan OFFSET untuk semua kasus karena paling mudah diimplementasikan.
Tidak menggunakan tie-breaker pada ORDER BY, sehingga pagination tidak stabil.
Hanya melihat satu query cepat di lokal, padahal pola data produksi berbeda.
Mengabaikan rows estimate yang meleset jauh, padahal ini sering menandakan planner tidak punya informasi yang cukup baik.
Mengoptimalkan sebelum tahu query mana yang paling mahal.

Checklist Implementasi

Checklist audit bottleneck SQL

Identifikasi query lambat dari log, tracing, atau observability.
Ambil parameter nyata yang mewakili kasus lambat.
Jalankan EXPLAIN dan EXPLAIN ANALYZE.
Catat access path, rows estimate, rows actual, dan biaya sort.
Periksa apakah rows scanned jauh lebih besar dari rows returned.
Validasi apakah index mendukung filter dan ORDER BY secara langsung.
Perbaiki predicate yang tidak sargable.
Ukur ulang setelah perubahan.

Checklist migrasi offset ke keyset pagination

Tentukan urutan yang stabil, misalnya created_at DESC, id DESC.
Buat index komposit yang mengikuti filter dan urutan.
Ubah kontrak API dari page/offset ke cursor.
Encode cursor secara aman bila dikirim ke client.
Pastikan query halaman berikut menggunakan kondisi seek, bukan OFFSET.
Uji edge case saat ada row dengan timestamp sama.
Pastikan UI memahami trade-off: lebih cepat untuk next/prev, tidak ideal untuk lompat ke halaman arbitrer.

Penutup

Menganalisis bottleneck SQL dengan EXPLAIN dan keyset pagination pada dasarnya adalah pekerjaan menyelaraskan cara aplikasi meminta data dengan cara database paling efisien menemukannya. EXPLAIN membantu Anda melihat apakah query memakai access path yang masuk akal, berapa banyak row yang sebenarnya dibaca, dan apakah sort atau scan menjadi sumber biaya utama. Dari sana, keputusan perbaikannya biasanya menjadi jelas: ubah bentuk query, rapikan predicate, sesuaikan index, dan bila perlu ganti offset pagination dengan keyset pagination.

Jika data dan trafik terus tumbuh, lakukan evaluasi ini secara berkala. Query yang sehat hari ini belum tentu sehat enam bulan lagi. Dan seperti ditunjukkan oleh pendekatan observability modern, pastikan hasil optimasi database selalu divalidasi kembali pada level sistem secara end-to-end.

Quick Search

shopping_cart Keranjang