Audit Bottleneck SQL: EXPLAIN, Covering Index, dan Keyset Paging

Saat aplikasi masih kecil, query yang terlihat aman sering terasa cukup cepat. Masalah biasanya muncul ketika jumlah baris bertambah, pola akses berubah, dan halaman daftar mulai melambat meski kode aplikasi hampir tidak berubah. Dalam kondisi ini, cara paling aman bukan langsung menambah cache atau menebak indeks, melainkan mengaudit bottleneck SQL secara sistematis: lihat gejalanya, ukur query yang benar-benar mahal, baca EXPLAIN, lalu perbaiki sumber biaya terbesar.

Pendekatan ini mirip investigasi sistem yang baik: telusuri pipeline, cari titik sempit yang nyata, dan hindari asumsi. Untuk query database, pipeline itu biasanya sederhana tetapi mahal jika salah desain: filter, sort, ambil kolom, lalu kirim hasil. Jika database melakukan full scan, filesort, atau membaca terlalu banyak halaman data, bottleneck akan terlihat jelas di produksi saat data tumbuh. Tiga perbaikan yang paling sering memberi dampak nyata adalah composite index, covering index, dan migrasi dari OFFSET pagination ke keyset paging.

Artikel ini fokus pada audit praktis untuk developer backend dan engineer yang perlu men-debug query lambat di aplikasi produksi tanpa menebak-nebak.

Gejala bottleneck SQL yang biasanya muncul lebih dulu

Sebelum membuka EXPLAIN, kenali dulu pola gejalanya. Query lambat jarang muncul sebagai satu insiden acak; biasanya ada pola yang konsisten.

Endpoint daftar makin lambat saat halaman pagination makin jauh.
CPU database naik ketika traffic stabil, terutama pada endpoint pencarian atau listing.
Latency p95/p99 memburuk, tetapi aplikasi tidak selalu error.
Query timeout mulai muncul ketika tabel tumbuh.
Read I/O meningkat karena database membaca banyak baris yang akhirnya tidak dipakai.

Kesalahan umum pada tahap ini adalah langsung menyimpulkan bahwa penyebabnya pasti kurang indeks. Kadang benar, tetapi kadang bottleneck utama justru ada pada pola query: SELECT *, ORDER BY yang tidak didukung indeks, filter yang tidak selaras dengan urutan indeks, atau pagination berbasis OFFSET yang memaksa database melewati banyak baris.

Prinsip audit: ukur query yang paling mahal di dunia nyata. Jangan mulai dari teori indeks terbaik; mulai dari query yang benar-benar memakan waktu, CPU, atau I/O.

Mulai dari query nyata, bukan dugaan

Langkah pertama adalah mengidentifikasi query yang benar-benar dominan di produksi. Sumbernya bisa dari slow query log, APM, log query aplikasi, atau metrik endpoint yang mengarah ke operasi database tertentu.

Apa yang perlu dicatat saat investigasi

Teks query final, bukan hanya potongan ORM.
Nilai parameter yang mewakili kasus lambat.
Frekuensi eksekusi query.
Waktu rata-rata dan tail latency.
Jumlah baris hasil dibanding jumlah baris yang dibaca.

Poin terakhir penting. Query yang mengembalikan 20 baris tetapi membaca ratusan ribu baris hampir pasti punya peluang optimasi yang besar.

Misalnya ada endpoint untuk daftar order terbaru per tenant:

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

Secara fungsional query ini benar. Namun untuk data besar, ini kandidat bottleneck klasik: ada filter, sorting, dan offset besar sekaligus.

Membaca EXPLAIN: cari full scan, filesort, dan pembacaan baris berlebih

EXPLAIN membantu menjawab satu pertanyaan penting: bagaimana database berencana mengeksekusi query ini? Walau format keluaran berbeda antar mesin database, ide auditnya tetap serupa.

Hal yang perlu diperhatikan dari EXPLAIN

Access path: apakah query memakai indeks atau melakukan scan tabel penuh.
Rows examined / estimated rows: berapa banyak baris yang diperkirakan harus dibaca.
Filter placement: apakah filter dilakukan awal lewat indeks, atau terlambat setelah banyak data dibaca.
Sorting strategy: apakah ORDER BY bisa dipenuhi dari indeks, atau perlu sort tambahan seperti filesort.
Extra lookups: apakah engine harus bolak-balik dari indeks ke tabel utama untuk mengambil kolom yang tidak ada di indeks.

Pada banyak kasus bottleneck, tanda yang muncul biasanya salah satu atau kombinasi berikut:

Full scan: seluruh tabel atau sebagian besar tabel dibaca.
Filesort atau sort eksternal: urutan hasil tidak bisa langsung diambil dari indeks.
Rows examined jauh lebih besar dari jumlah hasil.
Index dipakai tetapi tidak efektif: misalnya hanya cocok pada sebagian kondisi filter.

Misalkan hasil audit menunjukkan gejala seperti ini:

Filter tenant_id dan status ada.
Data diurutkan berdasarkan created_at DESC.
Database masih melakukan sort tambahan.
Jumlah baris yang dibaca besar karena offset tinggi.

Dari sini kita belum langsung menyimpulkan solusi tunggal, tetapi sudah tahu bottleneck-nya ada di dua tempat: jalur akses data dan cara pagination.

Perbaikan pertama: composite index yang mengikuti pola filter dan sort

Composite index berguna ketika query memfilter beberapa kolom dan/atau mengurutkan hasil. Kuncinya bukan sekadar “tambahkan indeks pada semua kolom”, tetapi menyusun urutan kolom indeks sesuai pola akses query.

Untuk query sebelumnya, kandidat indeks yang sering masuk akal adalah:

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

Mengapa urutannya seperti itu?

tenant_id dan status dipakai sebagai filter kesetaraan.
created_at dipakai untuk pengurutan.
id sering berguna sebagai tie-breaker agar urutan stabil jika ada banyak baris dengan timestamp sama.

Dengan indeks seperti ini, database punya peluang lebih baik untuk:

langsung menyempitkan pencarian ke subset baris yang relevan,
menghasilkan data dalam urutan yang benar tanpa sort tambahan,
mengurangi jumlah pembacaan yang tidak perlu.

Before dan after query

Sebelum:

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

Sesudah menambah composite index: teks query bisa tetap sama, tetapi rencana eksekusinya berubah. Jika indeks cocok, database bisa menghindari full scan dan mengurangi atau menghilangkan filesort.

Namun di sini ada hal penting: meskipun composite index memperbaiki filter dan sort, OFFSET besar tetap mahal. Database masih harus melompati banyak entri sebelum mencapai halaman yang diminta.

Kesalahan umum saat membuat composite index

Urutan kolom indeks terbalik dari pola query.
Membuat terlalu banyak indeks mirip sehingga write menjadi mahal.
Mengindeks setiap kolom secara terpisah lalu berharap hasilnya sama dengan composite index.
Mengabaikan kolom ORDER BY sehingga database tetap perlu sort tambahan.
Tidak mempertimbangkan selectivity dan distribusi data.

Indeks tunggal pada tenant_id, status, dan created_at tidak selalu setara dengan satu composite index. Optimizer bisa saja hanya memanfaatkan sebagian jalur dengan baik, sementara sorting tetap mahal.

Perbaikan kedua: covering index untuk mengurangi table lookup

Setelah jalur akses sudah benar, biaya berikutnya sering datang dari fakta bahwa database masih harus membaca tabel utama untuk mengambil kolom hasil. Ini yang sering diperbaiki dengan covering index: indeks yang sudah memuat semua kolom yang dibutuhkan query, sehingga engine bisa memenuhi query dari struktur indeks saja atau dengan lookup minimal, tergantung mesin database.

Contoh query listing ringan:

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

Jika indeks memuat kolom filter, sort, dan kolom hasil yang dibutuhkan, biaya I/O biasanya turun karena database tidak perlu mengakses data row penuh untuk setiap hasil.

CREATE INDEX idx_orders_listing_cover
ON orders (tenant_id, status, created_at, id, total_amount);

Ini bukan aturan mutlak untuk semua kasus. Menambah kolom ke indeks memperbesar ukuran indeks dan memperlambat operasi tulis. Karena itu, covering index paling cocok untuk query baca yang:

sangat sering dieksekusi,
mengambil sedikit kolom,
dominan pada latency atau beban sistem.

Kapan covering index tidak layak

Kolom hasil terlalu banyak atau terlalu lebar.
Query jarang dipakai.
Beban write jauh lebih penting daripada read latency.
Tabel sering di-update dan setiap indeks tambahan menambah biaya pemeliharaan.

Trade-off utama: indeks mempercepat baca dengan menambah biaya tulis, penggunaan storage, dan waktu maintenance. Audit yang baik selalu menilai apakah keuntungan read benar-benar membayar biaya write.

Perbaikan ketiga: migrasi dari OFFSET pagination ke keyset paging

Ini sering menjadi perubahan paling terasa pada tabel besar. OFFSET pagination nyaman dipakai, tetapi secara performa memburuk seiring nomor halaman membesar. Alasannya sederhana: untuk mengambil halaman ke-1001, database biasanya tetap harus melewati banyak baris sebelum menemukan 20 baris yang diminta.

Masalah OFFSET pada data besar

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

Walau indeks bagus, offset besar tetap membuat database berjalan jauh di indeks sebelum mulai mengembalikan hasil. Ini bukan bug optimizer; ini konsekuensi logis dari cara kerja OFFSET.

Keyset paging: ambil halaman berikutnya berdasarkan posisi terakhir

Pada keyset paging atau cursor pagination, klien tidak meminta “halaman ke-1001”, melainkan “20 data setelah item terakhir yang tadi saya lihat”. Query menjadi lebih stabil karena database dapat melanjutkan dari titik tertentu pada indeks.

Halaman pertama:

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

Halaman berikutnya:

SELECT id, tenant_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND (
    created_at < '2025-06-01 10:15:00'
    OR (created_at = '2025-06-01 10:15:00' AND id < 987654)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Pasangan (created_at, id) dipakai sebagai cursor agar urutan tetap deterministik. Jika hanya memakai created_at, baris dengan timestamp sama bisa menyebabkan duplikasi atau item terlewat antar halaman.

Indeks yang mendukung keyset paging

CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at, id);

Dengan struktur ini, filter tenant dan status tetap sempit, dan urutan berdasarkan created_at, id bisa dimanfaatkan langsung dari indeks.

Kelebihan dan trade-off keyset paging

Kelebihan: performa lebih stabil pada data besar, mengurangi kerja sia-sia, cocok untuk feed dan listing kronologis.
Kekurangan: tidak nyaman untuk lompat ke halaman arbitrer seperti “halaman 57”.
Konsekuensi API: respons perlu mengembalikan cursor, bukan hanya nomor halaman.
Konsistensi: perubahan data di antara request tetap bisa memengaruhi hasil, tetapi biasanya lebih aman daripada OFFSET untuk feed aktif.

Jika kebutuhan produk benar-benar butuh “goto page N”, OFFSET mungkin tetap dipakai pada dataset kecil atau untuk backoffice ringan. Tetapi untuk endpoint publik atau daftar besar yang sering dibaca, keyset paging biasanya lebih cocok.

Pola investigasi: telusuri pipeline query, jangan menebak

Cara audit yang efektif bukan sekadar hafal istilah indeks, melainkan memecah query menjadi pipeline kerja database:

Filter: baris mana yang sebenarnya relevan?
Order: apakah urutan hasil bisa langsung diambil dari indeks?
Projection: kolom apa saja yang benar-benar perlu diambil?
Pagination: apakah cara memotong hasil memaksa database tetap membaca banyak baris?

Dengan pipeline ini, Anda bisa menanyakan hal yang lebih tepat:

Apakah bottleneck ada pada scan awal?
Apakah sorting yang mahal justru dominan?
Apakah lookup ke tabel utama terlalu banyak?
Apakah OFFSET yang membuat pekerjaan membengkak?

Pendekatan ini membantu menghindari dua kesalahan klasik: optimasi dini dan optimasi yang salah sasaran. Menambah cache sebelum memperbaiki full scan sering hanya menyembunyikan masalah. Menambah banyak indeks tanpa mengukur write cost juga bisa memindahkan bottleneck ke proses insert/update.

Contoh audit end-to-end singkat

Kasus awal

Endpoint daftar order terbaru per tenant melambat saat data tumbuh.

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

Temuan dari EXPLAIN

Rows examined jauh lebih besar dari hasil.
Sorting tidak sepenuhnya didukung indeks.
Offset besar membuat engine tetap berjalan jauh.

Perbaikan bertahap

Tambahkan composite index yang cocok dengan filter dan sort.
Evaluasi apakah query listing perlu covering index.
Ganti OFFSET dengan keyset paging.

Query akhir yang lebih sehat

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND (
    created_at < :cursor_created_at
    OR (created_at = :cursor_created_at AND id < :cursor_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Perubahan ini bekerja karena database tidak lagi diminta untuk:

memindai terlalu banyak baris yang tidak relevan,
melakukan sort tambahan yang mahal,
melompati ribuan baris demi OFFSET,
membaca kolom yang tidak dibutuhkan.

Checklist audit bottleneck SQL di produksi

Identifikasi query paling mahal dari log atau APM.
Ambil contoh parameter yang mewakili kasus lambat.
Jalankan EXPLAIN dan lihat jalur akses data.
Cek apakah ada full scan atau estimasi pembacaan baris yang terlalu besar.
Cek apakah ORDER BY memicu filesort atau sort tambahan.
Pastikan urutan kolom pada composite index cocok dengan pola filter dan sort.
Kurangi kolom yang diambil; hindari SELECT * pada endpoint listing.
Pertimbangkan covering index hanya untuk query baca yang benar-benar penting.
Audit penggunaan OFFSET pagination pada tabel besar.
Migrasikan ke keyset paging jika pola aksesnya listing berurutan.
Ukur ulang setelah perubahan; jangan menganggap indeks baru otomatis membantu.
Periksa dampak ke operasi tulis, ukuran indeks, dan maintenance.

Kesalahan umum yang sering membuat audit meleset

1. Menambah indeks tanpa memahami query

Indeks yang salah urutan atau tidak selaras dengan WHERE dan ORDER BY bisa terlihat “masuk akal” tetapi tidak banyak membantu.

2. Menganggap semua query lambat butuh cache

Cache berguna, tetapi jika query dasar tetap full scan, Anda hanya menunda masalah dan menambah kompleksitas invalidasi.

3. Memakai OFFSET untuk semua listing

Ini nyaman di level API, tetapi biaya eksekusinya tumbuh seiring kedalaman halaman.

4. Membuat covering index terlalu gemuk

Jika terlalu banyak kolom dimasukkan, ukuran indeks membesar dan keuntungan read bisa kalah oleh biaya write dan storage.

5. Tidak menjaga urutan hasil tetap stabil

Pada keyset paging, selalu gunakan kunci urut yang deterministik, misalnya created_at ditambah id.

6. Tidak mengukur sesudah perubahan

Optimasi yang baik harus diverifikasi. Lihat lagi latency, rows examined, dan pola I/O setelah index atau query diubah.

Penutup

Audit bottleneck SQL yang efektif dimulai dari gejala nyata lalu bergerak turun ke mekanisme eksekusi query. EXPLAIN membantu menemukan apakah masalah utamanya ada pada full scan, filesort, atau pembacaan baris yang berlebihan. Dari sana, composite index memperbaiki jalur akses, covering index mengurangi lookup tambahan, dan keyset paging mengatasi biaya OFFSET pada data besar.

Yang paling penting, jangan menebak. Telusuri pipeline query seperti Anda men-debug subsistem lain: ukur bottleneck sebenarnya, ubah satu hal yang relevan, lalu ukur lagi. Di produksi, disiplin seperti ini jauh lebih berharga daripada menambah indeks secara acak atau berharap cache akan menyelesaikan semuanya.

Quick Search

shopping_cart Keranjang