Regex portable untuk audit query lambat di log SQL berguna saat Anda perlu memeriksa log produksi atau staging dengan cepat tanpa membangun parser SQL penuh. Pendekatan ini cocok untuk triage: menemukan kandidat masalah lebih dulu, lalu memverifikasi dengan EXPLAIN, metrik database, dan perubahan query atau indeks yang tepat.

Intinya, gunakan pola regex yang sederhana, defensif, dan relatif konsisten di banyak tool seperti grep, sed, awk, ripgrep, editor, atau pipeline shell. Hindari fitur regex yang terlalu spesifik engine jika tujuannya adalah pola yang “bekerja di mana-mana”. Sebagai konteks, gagasan tentang regex yang bisa dipakai lintas lingkungan juga sejalan dengan diskusi praktis seperti di tulisan John D. Cook tentang regex yang berguna di banyak tempat.

Apa yang dimaksud regex portable

Dalam konteks artikel ini, portable bukan berarti 100% identik di semua implementasi, melainkan pola yang cukup aman dipakai pada tool umum dengan perubahan minimal. Prinsipnya:

  • Pilih pola dasar yang mendekati POSIX ERE atau setidaknya mudah disesuaikan antara grep -E dan ripgrep.
  • Hindari fitur yang sering berbeda perilakunya, seperti lookbehind, grup bernama, atau mode multiline yang sangat engine-spesifik.
  • Jangan terlalu bergantung pada parsing struktur SQL yang kompleks. Regex dipakai untuk sinyal awal, bukan interpretasi sintaks penuh.

Contoh karakteristik yang aman:

  • [[:space:]]+ untuk spasi/tab/newline pada engine yang mendukung kelas POSIX.
  • [0-9]+ untuk angka.
  • (select|update|delete) dengan asumsi pencarian case-insensitive diaktifkan melalui opsi tool, misalnya -i.
  • Pola yang tidak mencoba menyeimbangkan tanda kurung atau mengurai subquery bertingkat.

Kenapa audit log SQL bisa dibantu regex

Log SQL biasanya berisi jejak query yang benar-benar dijalankan aplikasi, sering kali lengkap dengan durasi, parameter, sumber request, atau koneksi. Ini berguna untuk menjawab pertanyaan praktis:

  • Apakah ada query yang jelas boros, misalnya SELECT * dari tabel besar?
  • Apakah ada pagination berbasis OFFSET yang makin mahal pada halaman tinggi?
  • Apakah ada pencarian teks dengan LIKE '%kata%' yang cenderung tidak memakai indeks B-tree biasa?
  • Apakah endpoint tertentu memicu pola N+1?
  • Apakah banyak query berjalan tanpa filter yang jelas atau tanpa kolom pengarah indeks?

Regex tidak memberi jawaban final soal rencana eksekusi. Namun untuk menyaring ribuan sampai jutaan baris log, regex sangat efisien sebagai tahap awal.

Aturan praktis: desain pola yang aman dan lintas tool

1. Asumsikan SQL bisa berantakan

Di log nyata, query sering muncul dalam satu baris panjang, terpotong, atau punya spasi/newline tak konsisten. Karena itu:

  • Gunakan [[:space:]]+ alih-alih satu spasi literal.
  • Jangan terlalu mengandalkan urutan format yang kaku.
  • Kalau log multiline, pertimbangkan normalisasi dulu menjadi satu baris per query.

2. Pisahkan pencarian dari validasi

Lebih aman memakai regex untuk menemukan kandidat, lalu validasi manual atau semi-otomatis. Misalnya:

  1. Filter query lambat berdasarkan durasi dari log.
  2. Jalankan regex untuk kelompok pola yang mencurigakan.
  3. Ambil sampel query.
  4. Jalankan EXPLAIN atau cek metrik indeks.

3. Gunakan beberapa pola kecil, bukan satu regex besar

Regex raksasa sulit dibaca, sulit diporting, dan sulit di-debug. Untuk audit, pola kecil biasanya lebih efektif karena tiap pola mewakili hipotesis yang jelas.

Alur audit log SQL yang praktis

1. Saring log ke query lambat

Format log berbeda-beda. Beberapa database menulis durasi sebagai duration=..., yang lain punya kolom waktu sendiri. Jika format log Anda punya durasi yang mudah disaring, lakukan itu lebih dulu.

# Contoh umum: ambil baris yang mengandung query SQL dan durasi besar tertentu
# Sesuaikan pola durasi dengan format log Anda.
grep -Ei 'duration|time|elapsed|query' app-sql.log > sql-candidates.log

Jika durasi ada dalam angka yang bisa diparsing, awk sering lebih cocok daripada regex murni karena bisa membandingkan nilai numerik:

# Contoh pseudo-format: duration=1234 sql="SELECT ..."
awk 'match($0, /duration=([0-9]+)/, m) && m[1] >= 500 { print }' app-sql.log

Gunakan pendekatan ini hanya jika format log konsisten. Jika tidak, tetap lakukan filter berbasis kata kunci lalu audit manual bertahap.

2. Normalisasi bila perlu

Regex portable paling efektif pada satu query per baris. Jika log multiline, Anda bisa:

  • Menyesuaikan logging aplikasi agar satu statement tercetak per baris.
  • Menggunakan awk atau skrip kecil untuk menggabungkan blok query.
  • Menghapus spasi berulang agar pola lebih mudah cocok.
# Normalisasi spasi berulang menjadi satu spasi
sed -E 's/[[:space:]]+/ /g' sql-candidates.log > sql-normalized.log

3. Jalankan set pola audit

Setelah log cukup bersih, jalankan beberapa regex yang mewakili masalah umum berikut.

Pola regex portable untuk query lambat yang umum

1. Mencari SELECT * yang berpotensi boros

SELECT * tidak selalu salah, tetapi sering menjadi sinyal awal query mengambil kolom lebih banyak dari yang dibutuhkan. Ini bisa memperbesar I/O, transfer data, dan kerja ORM saat hydrasi objek.

grep -Ei 'select[[:space:]]+\*' sql-normalized.log

Kenapa pola ini cukup portable:

  • Hanya memakai kata literal, kelas spasi POSIX, dan * yang di-escape.
  • Tidak bergantung pada fitur lanjutan seperti lookaround.

Trade-off:

  • Bisa menangkap query kecil yang tidak bermasalah.
  • Tidak membedakan apakah * dipakai pada tabel kecil atau hasil agregasi tertentu.

Tindak lanjut yang tepat:

  • Pilih kolom eksplisit.
  • Periksa apakah query dipanggil pada path yang sangat sering.
  • Jika perlu, tambahkan indeks penutup (covering index) sesuai pola akses, bukan sekadar mengganti *.

2. Mencari OFFSET besar pada pagination

OFFSET tinggi sering mahal karena database tetap perlu melompati banyak baris sebelum mengembalikan halaman yang diminta.

grep -Ei 'offset[[:space:]]+[0-9]{4,}' sql-normalized.log

Pola ini mencari OFFSET bernilai empat digit atau lebih sebagai heuristik awal. Angka ambangnya bisa Anda sesuaikan. Jika ingin lebih konservatif lintas tool, dan khawatir soal dukungan {4,} di lingkungan tertentu, gunakan:

grep -Ei 'offset[[:space:]]+[0-9][0-9][0-9][0-9]+' sql-normalized.log

Tindak lanjut yang umum:

  • Untuk daftar yang besar dan terurut stabil, pertimbangkan keyset pagination atau seek method.
  • Pastikan kolom pengurutan dan filter didukung indeks yang sesuai.

3. Mencari LIKE yang tidak ramah indeks

Regex berguna untuk membedakan dua pola yang sering disalahpahami:

  • LIKE 'abc%': sering masih bisa memanfaatkan indeks pada banyak mesin database.
  • LIKE '%abc' atau LIKE '%abc%': biasanya tidak ramah terhadap indeks B-tree biasa.

Cari pola yang dimulai dengan wildcard:

grep -Ei "like[[:space:]]+'%" sql-normalized.log

Jika log memakai tanda kutip ganda atau parameterisasi berbeda, sesuaikan pola literalnya. Tujuan audit di sini adalah mengidentifikasi pencarian yang kemungkinan memicu scan besar.

Tindak lanjut:

  • Jika kebutuhan adalah pencarian substring, pertimbangkan indeks/full-text/trigram sesuai kemampuan database yang Anda pakai.
  • Jika kebutuhan sebenarnya prefix search, ubah pola ke 'kata%' agar peluang penggunaan indeks lebih baik.

Catatan penting: hasil akhir tetap harus diverifikasi dengan EXPLAIN, karena perilaku aktual dipengaruhi kolasi, fungsi, jenis indeks, dan optimizer.

4. Mencari query tanpa WHERE yang jelas

Tidak semua query tanpa WHERE salah. Untuk tabel referensi kecil, ini normal. Namun pada tabel transaksi besar, query semacam ini patut diperiksa.

grep -Ei 'select[[:space:]].*[[:space:]]from[[:space:]]+[a-zA-Z0-9_\.]+([[:space:]]|;|$)' sql-normalized.log | grep -Evi '[[:space:]]where[[:space:]]'

Pendekatan dua tahap lebih portable dan lebih mudah dibaca daripada satu regex yang mencoba menyatakan “ada FROM tetapi tidak ada WHERE” dengan fitur yang tidak tersedia di semua engine.

Tindak lanjut:

  • Tanyakan apakah query memang perlu membaca seluruh tabel.
  • Jika tidak, tambahkan filter yang sesuai akses aplikasi.
  • Periksa apakah filter yang ditambahkan nantinya didukung indeks yang tepat.

5. Mencari indikasi full table scan dari log atau explain output

Regex tidak bisa memastikan full table scan hanya dari SQL mentah, tetapi bisa mendeteksi indikasi dari dua sumber:

  1. Log database yang memang menulis plan atau peringatan.
  2. Output EXPLAIN yang Anda dump ke file.

Karena istilah plan berbeda antar database, jangan buat klaim terlalu spesifik. Cari istilah umum di output yang Anda miliki, misalnya:

grep -Ei 'table scan|full scan|seq scan|scan' explain-output.log

Ini sengaja generik. Anda perlu menyesuaikan istilah dengan database yang dipakai. Pada tahap ini regex hanya berfungsi sebagai filter cepat untuk output plan yang panjang.

6. Mencari pola N+1 dari log aplikasi atau SQL

N+1 biasanya lebih mudah dikenali dari pola repetisi query yang sangat mirip dalam jendela waktu sempit, misalnya:

  • Satu query daftar entitas.
  • Diikuti puluhan/ratusan query detail per ID.

Regex sendiri tidak cukup untuk “membuktikan” N+1, tetapi bisa menemukan kandidat query parametrik yang berulang:

grep -Ei 'select[[:space:]].*from[[:space:]]+orders[[:space:]].*where[[:space:]]+user_id[[:space:]]*=' sql-normalized.log

Lalu kelompokkan bentuk query-nya dengan menormalkan angka:

sed -E 's/[0-9]+/?/g' sql-normalized.log | sort | uniq -c | sort -nr | head

Kenapa ini berguna:

  • Jika bentuk query yang sama muncul sangat sering, itu tanda kuat ada loop aplikasi yang memanggil query berulang.
  • Normalisasi angka membantu menggabungkan query yang sama dengan parameter berbeda.

Tindak lanjut:

  • Gunakan eager loading, batch fetch, atau gabungkan query.
  • Periksa ORM yang mungkin diam-diam memicu relasi secara lazy.

7. Mencari filter yang tidak jelas mendukung indeks

Regex tidak tahu indeks mana yang ada. Namun Anda bisa mencari pola yang sering menyulitkan optimizer:

  • Fungsi pada kolom di sisi kiri perbandingan.
  • OR panjang pada banyak kolom.
  • Predikat non-sargable lain yang membuat indeks sulit dipakai.

Contoh kandidat fungsi pada kolom:

grep -Ei 'where[[:space:]]+(lower|upper|date|substring|substr|trim)[[:space:]]*\(' sql-normalized.log

Kenapa ini penting:

  • Ekspresi pada kolom sering menghalangi penggunaan indeks biasa.
  • Solusinya bisa berupa normalisasi data, kolom turunan, indeks fungsional jika didukung, atau perubahan bentuk query.

Contoh pipeline audit yang sederhana

Berikut contoh alur audit yang cukup realistis untuk file log aplikasi yang berisi SQL:

# 1) Ambil kandidat query yang relevan
grep -Ei 'select|update|delete|insert' app-sql.log > sql.log

# 2) Normalisasi spasi
sed -E 's/[[:space:]]+/ /g' sql.log > sql.norm.log

# 3) Cari pola mencurigakan
echo '--- SELECT * ---'
grep -Eni 'select[[:space:]]+\*' sql.norm.log | head -50

echo '--- OFFSET besar ---'
grep -Eni 'offset[[:space:]]+[0-9][0-9][0-9][0-9]+' sql.norm.log | head -50

echo '--- LIKE diawali wildcard ---'
grep -Eni "like[[:space:]]+'%" sql.norm.log | head -50

echo '--- Query tanpa WHERE ---'
grep -Ei 'select[[:space:]].*[[:space:]]from[[:space:]]+[a-zA-Z0-9_\.]+' sql.norm.log \
  | grep -Evi '[[:space:]]where[[:space:]]' \
  | head -50

# 4) Normalisasi literal angka untuk cari query berulang
sed -E 's/[0-9]+/?/g' sql.norm.log | sort | uniq -c | sort -nr | head -30

Pipeline ini tidak sempurna, tetapi cukup berguna untuk audit awal pada log besar. Keunggulannya adalah mudah dipindah ke banyak lingkungan shell dan mudah dimodifikasi.

Kapan hasil regex diterjemahkan menjadi aksi nyata

1. Keputusan indexing

Gunakan hasil regex sebagai pemicu untuk melihat query yang paling sering lambat atau paling sering muncul. Lalu verifikasi:

  1. Kolom filter apa yang dominan di WHERE?
  2. Apakah urutan ORDER BY konsisten?
  3. Apakah indeks yang ada sesuai urutan filter dan sort?

Jangan menambah indeks hanya karena regex menemukan pola. Indeks tambahan punya biaya pada INSERT/UPDATE, penyimpanan, dan perencanaan query.

2. Mengganti OFFSET menjadi keyset pagination

Jika regex menunjukkan banyak query dengan OFFSET besar, terutama pada endpoint daftar yang sering dipakai, itu sinyal kuat untuk mempertimbangkan keyset pagination. Pendekatan ini lebih stabil untuk dataset besar karena memanfaatkan nilai urutan terakhir, bukan menghitung lompatan baris yang makin mahal.

3. Refactor query atau ORM usage

Jika pola N+1 atau SELECT * dominan, perbaikan sering lebih efektif di level aplikasi:

  • Batasi kolom yang diambil.
  • Gunakan eager loading atau prefetch.
  • Gabungkan query yang terlalu granular.
  • Hindari fungsi pada kolom yang mematahkan sargability.

Batasan regex vs parser SQL

Ini bagian yang paling penting: regex bukan parser SQL. Anda sebaiknya menggunakannya untuk deteksi cepat, bukan analisis sintaks yang presisi penuh.

Apa yang regex lakukan dengan baik

  • Menyaring log besar dengan cepat.
  • Menemukan pola tekstual yang jelas.
  • Membantu triage dan prioritisasi.
  • Mudah dipakai di shell, CI, editor, atau incident response.

Apa yang regex sulit lakukan

  • Memahami subquery, CTE, komentar, string literal kompleks, dan SQL multiline yang kacau.
  • Membedakan konteks sintaks secara akurat.
  • Menentukan apakah query benar-benar memakai indeks.
  • Mengukur dampak performa tanpa data runtime.

Jika kebutuhan Anda sudah melampaui triage, pakai parser SQL, fitur observabilitas database, slow query log yang terstruktur, atau analisis plan otomatis.

Kesalahan umum saat memakai regex untuk audit SQL

  • Terlalu agresif: membuat regex rumit yang hanya cocok pada satu format log.
  • Terlalu percaya hasil: menganggap semua LIKE '%...' atau semua query tanpa WHERE pasti buruk.
  • Mengabaikan normalisasi: query multiline atau spasi acak membuat banyak pola gagal cocok.
  • Tidak memisahkan sinyal dan keputusan: regex seharusnya memicu investigasi, bukan langsung memutuskan desain indeks.
  • Tidak mengelompokkan frekuensi: satu query aneh sesekali berbeda prioritasnya dari query yang lambat tetapi terjadi ribuan kali.

Tips debugging regex lintas tool

  • Mulai dari pola paling sederhana, lalu tambah detail sedikit demi sedikit.
  • Uji pada sampel log kecil lebih dulu.
  • Gunakan opsi -n untuk nomor baris dan -i untuk case-insensitive bila perlu.
  • Jika hasil terlalu banyak, pecah menjadi pipeline dua tahap daripada memperumit satu regex.
  • Jika dukungan regex tool Anda berbeda, prioritaskan pola ERE sederhana dan hindari fitur non-portable.

Catatan: ide “regex yang bekerja di mana-mana” sangat berguna untuk pekerjaan operasional sehari-hari. Namun dalam audit SQL, targetnya bukan kesempurnaan sintaks, melainkan menemukan kandidat masalah dengan cepat dan cukup andal.

Penutup

Regex portable untuk audit query lambat di log SQL adalah alat praktis untuk menemukan sinyal awal seperti SELECT *, OFFSET besar, LIKE dengan wildcard di depan, query tanpa filter yang jelas, indikasi N+1, dan bentuk predikat yang tidak ramah indeks. Kuncinya adalah memakai pola yang sederhana, mudah dipindah antar tool, lalu memverifikasi hasilnya dengan EXPLAIN, statistik query, dan pemahaman akses data aplikasi.

Jika dipakai dengan disiplin, regex membantu Anda mempercepat audit log tanpa terjebak membangun parser SQL dari nol. Dari sana, barulah keputusan seperti menambah indeks, beralih ke keyset pagination, atau refactor query dibuat berdasarkan bukti yang lebih kuat.