Audit Query SQL: Hapus Index Mubazir, Percepat yang Benar

Audit query SQL yang baik hampir selalu dimulai dari pertanyaan sederhana: query mana yang benar-benar lambat, index mana yang benar-benar dipakai, dan index mana yang justru membebani operasi tulis. Kalau aplikasi terasa melambat lalu respons tim adalah “tambah index lagi”, sering kali masalahnya bukan kekurangan index, melainkan index yang salah, query yang salah bentuk, atau terlalu banyak index.

Prinsip yang berguna di sini sejalan dengan gagasan “kode terbaik adalah yang tidak perlu ditulis”: jangan buru-buru menambah apa pun sebelum diaudit. Pada database, artinya jangan menambah index sebelum memahami pola akses data, rencana eksekusi query, cardinality kolom, dan biaya sampingannya terhadap INSERT, UPDATE, serta DELETE.

Mengapa banyak index belum tentu cepat

Index membantu pencarian, tetapi ada harga yang harus dibayar. Setiap kali baris ditambah, diubah, atau dihapus, database juga perlu memperbarui struktur index terkait. Makin banyak index pada sebuah tabel, makin besar kerja tambahan pada operasi tulis.

Gejala yang sering muncul di sistem nyata:

INSERT/UPDATE melambat setelah tim menambah banyak index “untuk jaga-jaga”.
SELECT tetap lambat walau tabel punya banyak index, karena query tidak cocok dengan bentuk index yang ada.
Offset pagination makin buruk saat jumlah data membesar.
Index duplikat atau tidak terpakai memenuhi tabel tanpa memberi manfaat nyata.

Karena itu, target optimasi bukan “punya banyak index”, tetapi punya sedikit index yang benar-benar relevan dengan query penting.

Mulai dari audit, bukan asumsi

1. Kumpulkan query lambat dari slow query log

Langkah pertama adalah mengambil bukti. Gunakan slow query log atau mekanisme observabilitas dari database dan APM yang Anda pakai. Fokus pada:

query yang paling sering muncul,
query dengan total waktu terbesar,
query yang memindai banyak baris,
query yang sering dipanggil di jalur kritis aplikasi.

Jangan hanya melihat satu query yang sesekali lambat. Query yang “cukup lambat” tetapi dipanggil ribuan kali sering lebih mahal daripada satu query yang sangat lambat tetapi jarang terjadi.

2. Kelompokkan berdasarkan pola, bukan string mentah

Misalnya, query berikut sebenarnya satu pola yang sama:

SELECT id, name FROM users WHERE email = ?;
SELECT id, name FROM users WHERE email = ?;
SELECT id, name FROM users WHERE email = ?;

Yang perlu diaudit adalah pola aksesnya: filter di kolom email. Dari sini Anda bisa memeriksa apakah memang ada index yang sesuai, apakah kolom itu cukup selektif, dan apakah query mengambil kolom terlalu banyak.

3. Verifikasi dengan EXPLAIN atau EXPLAIN ANALYZE

Setelah kandidat query terkumpul, periksa rencana eksekusinya. Gunakan EXPLAIN untuk melihat bagaimana database berniat menjalankan query, dan gunakan EXPLAIN ANALYZE jika tersedia untuk membandingkan estimasi dengan eksekusi aktual.

Catatan: nama kolom keluaran dan detail plan berbeda antar mesin database. Prinsip umumnya sama: lihat apakah query memakai index yang tepat, berapa banyak baris yang dipindai, apakah terjadi sort mahal, dan apakah planner memilih full scan karena index tidak menguntungkan.

Membaca tanda bahaya pada query

SELECT tetap lambat meski index banyak

Misalkan ada query seperti ini:

SELECT *
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Tim mungkin sudah menambah index tunggal di status dan index tunggal di created_at, tetapi query tetap lambat. Kenapa? Karena planner sering membutuhkan index yang sesuai dengan pola filter dan pengurutan sekaligus, bukan dua index terpisah yang kebetulan mengandung kolom terkait.

Dalam banyak kasus, pendekatan yang lebih tepat adalah composite index yang mengikuti pola query:

-- Contoh konsep
(status, created_at)

Mengapa ini bekerja? Karena database dapat mempersempit data berdasarkan status, lalu membaca urutan created_at tanpa sort tambahan yang mahal, tergantung mesin database dan bentuk query.

Kesalahan umum: menambah banyak index tunggal dan berharap planner “menggabungkannya” secara optimal di semua kasus. Itu tidak selalu terjadi, dan meski bisa, hasilnya belum tentu lebih murah dibanding satu composite index yang tepat.

Write melambat karena terlalu banyak index

Misalkan tabel transaksi memiliki banyak index seperti:

INDEX(user_id)
INDEX(status)
INDEX(created_at)
INDEX(updated_at)
INDEX(user_id, status)
INDEX(status, created_at)
INDEX(user_id, created_at)
INDEX(user_id, status, created_at)

Sebagian mungkin berguna, tetapi sebagian lain bisa tumpang tindih. Setiap insert ke tabel ini berarti banyak struktur index harus diperbarui. Jika beban utama aplikasi adalah penulisan data, penambahan index tanpa audit dapat langsung terasa pada latensi write.

Yang perlu ditanya:

Apakah semua index itu dipakai oleh query nyata?
Apakah ada index yang menjadi prefiks dari index lain sehingga fungsinya bertumpang tindih?
Apakah ada index yang dibuat untuk query lama yang sudah tidak dipakai aplikasi?

Dalam banyak situasi, menghapus index yang mubazir lebih efektif daripada menambah index baru.

Offset pagination memburuk saat data tumbuh

Contoh query:

SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Ini terlihat normal saat data kecil. Masalahnya, offset besar sering memaksa database melewati banyak baris sebelum menemukan halaman yang diminta. Meski ada index, biaya membuang puluhan ribu baris tetap nyata.

Pendekatan yang biasanya lebih baik adalah keyset pagination atau seek method:

-- Sebelum
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

-- Sesudah
SELECT id, created_at, total
FROM orders
WHERE created_at < :last_created_at
ORDER BY created_at DESC
LIMIT 50;

Jika created_at tidak unik, tambahkan tie-breaker seperti id:

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Di sini index yang relevan biasanya mengikuti urutan pengurutan dan filter pagination tersebut. Hasilnya lebih stabil ketika data bertambah karena database tidak perlu terus-menerus membuang baris sebanyak nilai offset.

Cardinality dan selectivity: dasar memilih index yang masuk akal

Tidak semua kolom layak di-index. Dua konsep penting:

Cardinality: banyaknya nilai unik dalam kolom.
Selectivity: seberapa efektif kolom menyaring baris.

Kolom seperti status yang hanya berisi beberapa nilai sering punya selectivity rendah. Jika sebagian besar baris berstatus paid, index tunggal pada status mungkin tidak terlalu membantu. Planner bisa saja memilih scan yang lebih murah daripada meloncat melalui index untuk tetap membaca banyak baris.

Sebaliknya, kolom seperti email, user_id pada pola tertentu, atau pasangan kolom yang lebih unik sering lebih bernilai untuk indexing.

Karena itu, saat melihat query seperti ini:

SELECT *
FROM orders
WHERE status = 'paid' AND created_at >= :start_date
ORDER BY created_at DESC;

Index yang lebih berguna sering bukan INDEX(status) sendirian, tetapi composite index yang mencerminkan pola akses nyata. Urutan kolom dalam composite index penting karena memengaruhi seberapa efektif index digunakan untuk filter dan sort.

Prinsip praktis: rancang index berdasarkan query yang benar-benar berjalan di produksi, bukan berdasarkan daftar kolom yang “mungkin nanti dipakai”.

Composite index dan covering index: kapan benar-benar berguna

Composite index

Composite index cocok ketika query sering memfilter atau mengurutkan berdasarkan kombinasi kolom yang sama. Contoh:

SELECT id, user_id, created_at, total
FROM orders
WHERE user_id = :user_id
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Daripada membuat tiga index tunggal terpisah, lebih masuk akal mengevaluasi composite index yang selaras dengan filter dominan dan urutan hasil.

Namun, jangan asal membuat semua kombinasi yang mungkin. Setiap kombinasi baru menambah biaya write, ukuran penyimpanan, dan kompleksitas maintenance.

Covering index

Covering index berguna ketika database bisa memenuhi query langsung dari index tanpa sering membaca tabel utama. Ini sangat membantu untuk query baca yang sempit dan sangat sering dipanggil.

Contoh before:

SELECT id, status, created_at
FROM orders
WHERE user_id = :user_id
ORDER BY created_at DESC
LIMIT 20;

Jika index memuat kolom yang diperlukan untuk filter, sort, dan kolom hasil, planner mungkin dapat mengurangi akses tambahan ke tabel.

Trade-off-nya jelas: covering index cenderung lebih besar. Jika Anda memasukkan terlalu banyak kolom “sekalian saja”, manfaatnya bisa kalah oleh biaya penyimpanan dan write amplification.

Contoh audit before/after yang realistis

Kasus 1: banyak index tunggal, query tetap lambat

Sebelum:

SELECT id, user_id, total, created_at
FROM orders
WHERE user_id = :user_id AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Index yang ada:

INDEX(user_id)
INDEX(status)
INDEX(created_at)

Masalahnya, planner mungkin masih harus menyaring banyak baris lalu melakukan sort tambahan.

Sesudah audit:

query ini ternyata sangat sering dipanggil di halaman dashboard,
status sendiri kurang selektif,
kombinasi user_id + status + urutan created_at lebih sesuai.

Solusi yang dievaluasi:

-- Konsep composite index yang mengikuti pola query
(user_id, status, created_at)

Jika hasil EXPLAIN membaik dan latensi turun, beberapa index tunggal lama mungkin tidak lagi perlu dipertahankan. Di sinilah audit membantu mengurangi, bukan menambah, kompleksitas.

Kasus 2: offset pagination makin mahal

Sebelum:

SELECT id, title, published_at
FROM posts
WHERE author_id = :author_id
ORDER BY published_at DESC
LIMIT 20 OFFSET 20000;

Sesudah:

SELECT id, title, published_at
FROM posts
WHERE author_id = :author_id
  AND (published_at, id) < (:last_published_at, :last_id)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Kenapa lebih baik? Karena query kedua melanjutkan pembacaan dari posisi terakhir, bukan memaksa database melewati ribuan baris yang dibuang.

Kasus 3: index duplikat dan prefiks yang tumpang tindih

Misalkan ada dua index:

INDEX(user_id)
INDEX(user_id, created_at)

Dalam beberapa kasus, index pertama bisa menjadi kandidat untuk dihapus jika seluruh kebutuhan query yang dulu memakainya kini sudah tercakup lebih baik oleh index kedua. Tetapi keputusan ini harus diverifikasi dengan workload nyata dan plan query, bukan asumsi semata.

Hal yang sama berlaku untuk index duplikat yang dibuat tanpa sadar oleh migrasi terpisah atau perubahan fitur bertahap.

Cara audit index yang tidak terpakai atau duplikat

1. Inventaris semua index per tabel

Buat daftar index di tabel yang paling sibuk. Lihat:

nama index,
urutan kolom,
keunikan index,
ukuran relatifnya jika informasi tersedia.

2. Cocokkan dengan query produksi

Tanya untuk setiap index: query mana yang membutuhkannya? Jika tidak ada jawaban jelas dari slow query log, APM, atau analisis kode aplikasi, index itu patut dicurigai.

3. Periksa duplikasi dan overlap

Beberapa pola umum:

index identik dengan nama berbeda,
index tunggal yang fungsinya tertutup oleh composite index,
index lama dari fitur yang sudah tidak aktif.

4. Hapus bertahap, ukur, lalu lanjutkan

Jangan menghapus banyak index sekaligus di sistem produksi tanpa pengukuran. Lakukan bertahap, pantau latensi read dan write, lalu evaluasi ulang. Pendekatan aman lebih penting daripada optimasi agresif yang sulit dibalikkan.

Tips: simpan catatan alasan pembuatan dan penghapusan index. Banyak tim menumpuk index karena tidak ada dokumentasi query yang sebenarnya ingin dilayani.

EXPLAIN dan EXPLAIN ANALYZE: apa yang perlu diperhatikan

Saat membaca output plan, fokus pada pertanyaan berikut:

Apakah query melakukan full table scan atau index scan?
Berapa banyak baris yang diperkirakan akan dibaca?
Apakah ada filter yang diterapkan setelah pembacaan besar, menandakan index kurang tepat?
Apakah ada operasi sort mahal yang seharusnya bisa dihindari dengan index yang sesuai?
Apakah estimasi planner jauh dari realita saat memakai EXPLAIN ANALYZE?

Jika estimasi jauh meleset, masalahnya tidak selalu “kurang index”. Bisa juga statistik database kurang akurat, distribusi data berubah, atau query menggunakan kondisi yang membuat planner sulit memperkirakan selectivity.

Kapan menghapus index lebih efektif daripada menambah

Menghapus index lebih efektif ketika:

latensi INSERT/UPDATE/DELETE meningkat karena terlalu banyak index,
ada index yang tidak pernah dipakai workload nyata,
ada beberapa index overlap yang melayani query yang sama,
ukuran tabel dan index membesar sehingga cache menjadi kurang efisien,
tim menambah index untuk mengobati query yang seharusnya diperbaiki bentuknya.

Ini bukan berarti anti-index. Intinya, index harus punya alasan operasional yang jelas. Jika tidak, dia hanya menambah biaya permanen pada semua operasi tulis.

Trade-off read vs write yang harus dipahami tim

Setiap index adalah kompromi. Untuk workload yang dominan baca, menambah index yang tepat sering masuk akal. Untuk workload yang dominan tulis, terlalu banyak index bisa menjadi bottleneck utama.

Pertimbangkan hal berikut:

Aplikasi read-heavy: composite atau covering index bisa sangat membantu, asal didasarkan pada query penting yang terukur.
Aplikasi write-heavy: pertahankan hanya index yang benar-benar wajib untuk query kritis dan constraint penting.
Tabel besar dengan data terus tumbuh: desain pagination dan pola akses lebih penting daripada sekadar menambah index acak.

Optimasi yang baik selalu mempertimbangkan biaya total sistem, bukan hanya satu query dalam isolasi.

Kesalahan umum tim saat “optimasi” database

Menambah index tanpa melihat slow query log.
Mengandalkan asumsi, bukan EXPLAIN.
Membuat index per kolom untuk query multi-kondisi.
Mengabaikan urutan kolom pada composite index.
Memakai OFFSET besar untuk pagination data yang terus bertambah.
Tidak mengevaluasi dampak index ke INSERT/UPDATE.
Membiarkan index duplikat dari migrasi lama.
Mengoptimasi query yang jarang dipakai, tetapi membiarkan query panas tetap buruk.
Memilih SELECT * padahal yang dibutuhkan hanya beberapa kolom.

Sering kali masalah performa bukan karena database “kurang cepat”, tetapi karena tim tidak disiplin membedakan query yang penting dari yang sekadar terlihat masuk akal.

Checklist audit query SQL yang praktis

Kumpulkan slow query log dan identifikasi query dengan biaya total tertinggi.
Kelompokkan query berdasarkan pola akses yang sama.
Jalankan EXPLAIN atau EXPLAIN ANALYZE pada query kandidat.
Lihat apakah filter, sort, dan limit sudah didukung index yang sesuai.
Evaluasi cardinality dan selectivity kolom filter.
Periksa apakah composite index lebih tepat daripada banyak index tunggal.
Nilai apakah covering index layak untuk query baca yang sangat sering.
Audit index duplikat, overlap, atau tidak terpakai.
Ubah query yang buruk bentuknya, terutama offset pagination besar.
Hapus index bertahap, ukur dampaknya pada read dan write.
Dokumentasikan alasan setiap index yang dipertahankan.

Penutup

Audit query SQL bukan latihan kosmetik. Tujuannya adalah mengurangi kerja database yang tidak perlu: scan yang mubazir, sort yang mahal, offset yang membuang waktu, dan index yang hanya menambah beban tulis. Dalam banyak kasus, langkah paling efektif bukan menambah index baru, melainkan memperbaiki query dan menghapus index yang salah.

Kalau ingin prinsip sederhananya: jangan optimasi dengan menambah objek sampai Anda tahu objek mana yang benar-benar berguna. Pada database, itu berarti ukur dulu, baca plan dulu, lalu pilih index yang melayani query nyata. Sisanya layak dipertanyakan.

Quick Search

shopping_cart Keranjang