Audit SQL untuk Hapus Query Bloat dan Index yang Tidak Berguna

Audit SQL perlu dilakukan saat codebase tumbuh cepat dan query mulai “membesar” tanpa terasa. Gejalanya biasanya jelas: SELECT * dipakai di banyak tempat, WHERE dan ORDER BY tidak selaras dengan index, pagination berbasis OFFSET makin lambat, ada JOIN yang sebenarnya tidak diperlukan, dan index terus ditambah tanpa evaluasi.

Masalah ini sering muncul pada codebase yang berkembang lewat kombinasi fitur cepat, copypaste, query builder yang makin kompleks, atau generator/AI yang menghasilkan query valid tetapi tidak efisien. Solusinya bukan sekadar “tambah index”, melainkan audit pola akses data: query mana yang paling sering dipakai, bagian mana yang mahal, index mana yang benar-benar membantu, dan mana yang justru menambah beban tulis.

Mengapa query bloat terjadi saat codebase tumbuh

Query bloat adalah kondisi ketika query tetap benar secara fungsional, tetapi makin jauh dari kebutuhan aktual aplikasi. Ini biasanya terjadi bertahap:

Kolom yang diambil makin banyak karena fitur baru menambahkan kebutuhan baru, lalu query lama dipakai ulang di tempat lain.
Filter bertambah tetapi index tidak diubah mengikuti pola pencarian terbaru.
JOIN menumpuk karena beberapa endpoint memakai basis query yang sama, padahal tidak semua relasi dibutuhkan.
Pagination OFFSET tetap dipertahankan meski ukuran tabel dan page number terus naik.
Index dibuat reaktif setiap ada query lambat, tanpa memeriksa apakah index lain sudah cukup atau apakah index baru benar-benar dipakai.

Dampaknya bukan cuma latency. Query bloat juga meningkatkan konsumsi CPU, I/O, memori, ukuran transfer data, serta memperlambat operasi INSERT, UPDATE, dan DELETE karena terlalu banyak index harus dipelihara.

Gejala nyata yang perlu dicari saat audit SQL

1. SELECT berlebihan

Masalah paling umum adalah mengambil lebih banyak kolom daripada yang benar-benar dipakai oleh aplikasi.

-- Sebelum: mengambil semua kolom, termasuk yang tidak dipakai UI/API
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- Sesudah: ambil hanya kolom yang dibutuhkan
SELECT id, order_number, status, total_amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Mengapa ini penting:

Mengurangi data yang dibaca dari storage dan dikirim ke aplikasi.
Mengurangi kemungkinan akses ke kolom besar seperti JSON, TEXT, atau BLOB yang sebenarnya tidak diperlukan.
Mempermudah desain index yang lebih relevan.

Jika query berasal dari ORM atau query generator, periksa apakah layer aplikasi selalu memetakan model penuh padahal endpoint hanya butuh beberapa field.

2. WHERE dan ORDER BY tidak selaras dengan index

Index bukan aksesoris. Index hanya efektif jika cocok dengan cara query memfilter, mengurutkan, dan membatasi data.

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

Untuk pola seperti ini, composite index sering lebih tepat dibanding beberapa index tunggal yang terpisah. Misalnya index pada kolom yang mencerminkan pola akses utama:

(customer_id, status, created_at)

Alasannya:

WHERE customer_id = ... dan status = ... mempersempit kandidat data lebih awal.
ORDER BY created_at bisa lebih mudah dioptimalkan jika urutan index mendukung.
Database tidak selalu bisa menggabungkan beberapa index tunggal seefisien satu composite index yang sesuai.

Namun composite index juga punya trade-off:

Menambah biaya tulis dan ukuran penyimpanan.
Tidak otomatis berguna untuk semua variasi query.
Urutan kolom penting; index yang baik untuk satu pola bisa kurang berguna untuk pola lain.

3. Pagination OFFSET yang melambat

OFFSET mudah dipakai, tetapi makin mahal untuk page besar. Database tetap harus melewati baris-baris sebelum mencapai posisi yang diminta.

-- Sebelum: makin lambat pada page besar
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

Pada data besar, pendekatan cursor pagination atau keyset pagination biasanya lebih tepat.

-- Sesudah: gunakan nilai terakhir sebagai cursor
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
  AND created_at < '2025-01-15 10:30:00'
ORDER BY created_at DESC
LIMIT 20;

Jika ada kemungkinan nilai created_at duplikat, gunakan tie-breaker yang stabil, misalnya (created_at, id).

SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
  AND (
    created_at < '2025-01-15 10:30:00'
    OR (created_at = '2025-01-15 10:30:00' AND id < 98765)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Kapan OFFSET masih masuk akal:

Dataset kecil.
Halaman yang diakses umumnya hanya page awal.
Kebutuhan UI memang menuntut lompat ke nomor halaman tertentu.

Kapan cursor pagination lebih tepat:

Feed, timeline, log, transaksi, atau daftar besar yang diurutkan berdasarkan kolom monoton atau hampir monoton.
Traffic tinggi dan page dalam sering diakses.
Konsistensi performa lebih penting daripada “nomor halaman”.

4. JOIN duplikat atau tidak perlu

JOIN sering membesar seiring banyak fitur berbagi query dasar yang sama. Masalahnya, tidak semua endpoint butuh semua relasi.

-- Sebelum: relasi ikut semua, padahal tidak dipakai
SELECT o.id, o.order_number, o.total_amount, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN coupons cp ON cp.id = o.coupon_id
LEFT JOIN warehouses w ON w.id = o.warehouse_id
LEFT JOIN sales_reps s ON s.id = o.sales_rep_id
WHERE o.customer_id = 42
ORDER BY o.created_at DESC
LIMIT 20;

Jika endpoint hanya menampilkan daftar order dasar, JOIN tambahan tersebut sebaiknya dihapus.

-- Sesudah: hanya relasi yang benar-benar dibutuhkan
SELECT o.id, o.order_number, o.total_amount
FROM orders o
WHERE o.customer_id = 42
ORDER BY o.created_at DESC
LIMIT 20;

JOIN juga perlu diperiksa jika:

Dipakai hanya untuk validasi keberadaan data yang sebenarnya bisa ditangani dengan kondisi lain.
Menyebabkan duplikasi baris karena relasi one-to-many padahal hasil yang diinginkan one-to-one.
Ditambahkan oleh query builder secara otomatis untuk kebutuhan yang tidak selalu aktif.

5. Index yang dibuat asal

Index yang tidak berguna biasanya lahir dari pola “query ini lambat, tambahkan index” tanpa audit lebih lanjut. Akibatnya:

Ada beberapa index dengan prefix atau kombinasi mirip.
Index ada, tetapi query planner jarang atau tidak pernah memakainya.
Biaya tulis naik karena setiap perubahan baris harus memperbarui banyak index.

Index bukan hanya soal mempercepat baca. Semakin banyak index, semakin mahal operasi tulis dan semakin kompleks pemeliharaan statistik planner.

Langkah audit SQL yang praktis

1. Kumpulkan query nyata, bukan asumsi

Mulailah dari query yang benar-benar berjalan di produksi atau environment mirip produksi. Sumber umumnya:

Slow query log untuk menangkap query dengan durasi tinggi.
Query sampling dari APM, proxy, log database, atau instrumentation aplikasi.
Endpoint tracing untuk melihat endpoint mana yang memicu query terbanyak.

Tujuannya bukan mencari satu query terburuk saja, tetapi mengurutkan berdasarkan kombinasi:

Frekuensi tinggi
Durasi tinggi
Rows scanned tinggi
Payload hasil besar
Dampak ke endpoint penting

Query yang berjalan 5 ms tetapi dieksekusi ratusan ribu kali bisa lebih layak diaudit daripada query 500 ms yang jarang dipakai.

2. Kelompokkan berdasarkan pola akses data

Jangan audit query satu per satu secara terisolasi. Kelompokkan berdasarkan pola yang sama, misalnya:

Mencari order per customer
Feed aktivitas terbaru
Pencarian user berdasarkan email atau status
Laporan berdasarkan rentang tanggal

Dengan begitu, Anda bisa merancang index untuk pola akses, bukan untuk satu string query tertentu. Ini penting karena generator, ORM, dan AI sering menghasilkan variasi query yang secara logika sama tetapi berbeda susunan sintaksnya.

3. Jalankan EXPLAIN atau EXPLAIN ANALYZE

EXPLAIN membantu melihat bagaimana planner berniat mengeksekusi query. EXPLAIN ANALYZE menambahkan hasil eksekusi aktual sehingga Anda bisa membandingkan estimasi dengan kenyataan.

Hal yang perlu diperhatikan secara umum:

Apakah query memakai index yang diharapkan?
Apakah terjadi scan besar pada tabel untuk hasil kecil?
Apakah sort dilakukan mahal karena tidak didukung index?
Apakah join order masuk akal?
Apakah estimasi jumlah baris sangat meleset dari aktual?

Jika estimasi dan aktual berbeda jauh, masalahnya bisa bukan hanya query, tetapi juga statistik database yang tidak akurat atau distribusi data yang berubah.

Gunakan EXPLAIN/EXPLAIN ANALYZE pada query representatif dengan parameter realistis. Query yang cepat untuk customer kecil bisa lambat untuk customer dengan data sangat besar.

4. Bandingkan sebelum-sesudah, jangan hanya menebak

Setelah mengubah query atau menambah/menghapus index, ulangi pengukuran. Periksa:

Rows examined atau rows scanned
Waktu eksekusi
Perubahan execution plan
Dampak ke query lain yang memakai tabel sama

Audit yang baik selalu berbasis bukti. Jangan menghapus index hanya karena “terlihat mirip”, dan jangan menambah composite index hanya karena terdengar masuk akal.

Contoh audit: dari query bloat ke query yang lebih tajam

Kasus 1: daftar order customer

-- Sebelum
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Masalah:

SELECT * mengambil kolom berlebihan.
OFFSET besar membuat database melewati banyak baris.
Belum tentu ada index yang cocok untuk filter dan urutan sekaligus.

Perbaikan:

-- Sesudah
SELECT id, order_number, status, total_amount, created_at
FROM orders
WHERE customer_id = 42
  AND (
    created_at < '2025-01-15 10:30:00'
    OR (created_at = '2025-01-15 10:30:00' AND id < 98765)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang layak dipertimbangkan mengikuti pola ini adalah index komposit yang mendukung filter customer dan urutan pagination. Prinsipnya: sesuaikan dengan kolom pada WHERE dan ORDER BY, lalu validasi dengan EXPLAIN apakah planner benar-benar memakainya.

Kasus 2: filter status dan urutan waktu

-- Sebelum
SELECT id, customer_id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 100;

Jika query ini sering dipakai, index tunggal pada status dan index tunggal pada created_at belum tentu cukup optimal. Query seperti ini sering lebih cocok dengan composite index yang mengikuti pola filter lalu urutan.

-- Pola index yang sering relevan
(status, created_at)

Namun keputusan final tetap harus berdasar data nyata:

Seberapa selektif kolom status?
Apakah query lain lebih dominan memakai urutan berbeda?
Apakah biaya index tambahan sepadan dengan benefit baca?

Kasus 3: JOIN untuk kebutuhan yang bisa dipisah

-- Sebelum
SELECT o.id, o.order_number, c.name, a.city, a.country
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN addresses a ON a.customer_id = c.id
WHERE o.id = 12345;

Jika endpoint hanya butuh ringkasan order, JOIN ke alamat customer bisa jadi tidak perlu. Lebih baik pisahkan query detail hanya saat benar-benar diperlukan. Ini juga menghindari hasil duplikat jika satu customer punya banyak alamat.

Kapan memakai composite index

Composite index layak dipakai ketika ada pola query yang konsisten dan bernilai tinggi, misalnya:

Filter pada beberapa kolom yang sering muncul bersama.
Filter diikuti urutan yang stabil.
Pagination berbasis cursor pada pasangan kolom tertentu.

Gunakan composite index jika:

Query tersebut sering dipanggil atau penting untuk pengalaman pengguna.
Pola filter dan sort relatif stabil.
Anda sudah memverifikasi index tunggal terpisah tidak cukup efisien.

Hindari menambah composite index jika:

Pola query jarang dipakai.
Variasi query terlalu banyak sehingga satu index tidak memberi manfaat nyata.
Tabel memiliki beban tulis tinggi dan biaya pemeliharaan index menjadi signifikan.

Kesalahan umum:

Membuat terlalu banyak composite index yang saling tumpang tindih.
Tidak memikirkan urutan kolom dalam index.
Mengasumsikan semua query dengan kolom yang sama akan diuntungkan secara setara.

Audit index: mana yang berguna, mana yang bisa dipensiunkan

Untuk menilai apakah index berguna, lihat dari tiga sisi:

1. Apakah dipakai oleh query penting?

Periksa execution plan dari query yang paling sering atau paling mahal. Jika index tidak pernah muncul pada pola query inti, nilainya patut dipertanyakan.

2. Apakah tumpang tindih dengan index lain?

Sering ada beberapa index yang sangat mirip, misalnya satu index tunggal dan satu composite index yang prefix-nya sudah mencakup kebutuhan tertentu. Jangan buru-buru menghapus, tetapi evaluasi apakah index yang lebih kecil masih punya use case nyata.

3. Apakah biaya tulisnya sepadan?

Pada tabel transaksi atau event yang sering ditulis, index berlebih dapat menjadi beban. Jika sebuah index hanya membantu query administratif yang jarang dipakai, mungkin lebih baik ditinjau ulang.

Sebelum menghapus index, pastikan Anda meninjau query periodik, job batch, laporan, dan proses maintenance. Index yang jarang dipakai belum tentu tidak penting.

Review pola akses data di level aplikasi

Audit SQL yang efektif tidak berhenti di database. Banyak query bloat berasal dari desain akses data di level aplikasi.

Hal yang perlu direview

Endpoint mana yang mengambil model penuh padahal respons hanya butuh subset field.
Repository atau service yang dipakai ulang untuk banyak skenario meski kebutuhan datanya berbeda.
Query builder bercabang yang menambahkan JOIN, filter, dan sort secara kondisional tetapi akhirnya dipakai sebagai default untuk semua request.
N+1 query yang disamarkan oleh cache atau traffic rendah, lalu meledak saat data bertambah.

Sering kali perbaikan terbaik bukan menambah index, melainkan memecah satu query generik menjadi dua atau tiga query yang lebih spesifik dan lebih mudah dioptimalkan.

Slow query log, sampling, dan debugging yang berguna

Gunakan slow query log untuk prioritas awal

Slow query log membantu menemukan query yang durasinya melewati ambang tertentu. Ini bagus untuk menemukan outlier, tetapi tidak selalu cukup untuk melihat query kecil yang sangat sering.

Tambahkan query sampling

Sampling membantu melihat pola query secara lebih representatif tanpa merekam semuanya. Ini berguna untuk:

Mengelompokkan query serupa.
Menemukan variasi query dari ORM/generator/AI.
Mengukur distribusi parameter nyata.

Debug dengan parameter realistis

Jangan hanya menguji dengan data kecil atau ID yang kebetulan ringan. Pilih contoh yang merepresentasikan kasus terburuk yang masih realistis, misalnya tenant terbesar, customer dengan order terbanyak, atau rentang waktu dengan volume tinggi.

Checklist mencegah query bloat dari generator, AI, dan copypaste

Larangan default untuk SELECT * kecuali benar-benar ada alasan jelas.
Setiap query daftar besar harus punya strategi pagination yang sadar skala; pertimbangkan cursor untuk data yang terus tumbuh.
Pastikan WHERE dan ORDER BY ditinjau bersama saat membuat atau mengubah index.
Hindari base query generik yang membawa semua JOIN ke semua endpoint.
Review query hasil AI/generator dengan EXPLAIN sebelum dianggap final.
Jangan menambah index tanpa bukti query plan dan pola akses nyata.
Audit index tumpang tindih secara berkala, terutama setelah banyak fitur baru dirilis.
Bedakan query untuk list, detail, export, dan reporting; kebutuhan datanya biasanya tidak sama.
Ukur frekuensi, bukan hanya durasi; query kecil yang sangat sering bisa menjadi sumber bloat terbesar.
Simpan contoh query representatif per use case agar review performa tidak bergantung pada asumsi individu.

Penutup

Audit SQL yang efektif bukan sekadar mengejar query lambat satu per satu, tetapi membersihkan akumulasi keputusan kecil yang membuat codebase dan database makin berat. Fokus utama biasanya sederhana: kurangi kolom yang tidak perlu, pastikan filter dan urutan selaras dengan index, hindari OFFSET besar untuk dataset besar, hapus JOIN yang tidak diperlukan, dan evaluasi index berdasarkan pola akses nyata.

Jika codebase tumbuh cepat—terutama dengan bantuan generator, ORM kompleks, atau AI—anggap audit SQL sebagai pekerjaan rutin, bukan proyek darurat. Query yang “masih jalan” belum tentu sehat. Yang perlu dijaga adalah apakah query itu tetap proporsional terhadap data, traffic, dan kebutuhan aplikasi yang terus berubah.

Quick Search

shopping_cart Keranjang