Di episode kedua, query-nya tidak bersalah, 78 milidetik di CLI, sementara endpoint-nya makan 15 detik. Episode ini cermin kebalikannya: kali ini query-nya benar-benar bersalah. Tapi bukan karena rumit atau jelek, dia bersalah karena satu pembungkus fungsi kecil yang membuatnya mendiamkan index yang disiapkan khusus untuknya hehe~
Index yang Dirancang Sempurna (Dikali Tiga) #
Korbannya: endpoint hitungan alert per device. Satu tabel alert berisi puluhan juta baris,1 dan di atasnya ada index komposit: (tanggal, device, tipe alert). Kalau kamu membaca kebutuhan endpoint-nya, “ambil jumlah alert per device per tipe, dalam rentang tanggal”, index itu persis seperti dibuat untuk query ini. Seseorang di masa lalu memahami masalahnya dengan benar.
Dan ini bagian favorit saya, yang baru saya sadari saat menulis episode ini: di skema database, index yang sama itu… ada tiga salinan. Nama berbeda, isi identik. Sepertinya tiga orang di tiga masa berbeda saling menambahkan tanpa membuka skema dulu hahaha. Jadi index yang dibutuhkan itu bukan cuma ada, dia ada berlebih.
Dan tetap saja, query-nya tidak memakainya. Full table scan di puluhan juta baris, endpoint merayap sampai ±10 detik. Luar biasa kan, rasanya saat kebutuhan paling dasar diabaikan di depan mata? hehe~
Buku Telepon yang Dilitari #
Istilah teknisnya non-sargable, query yang membuat index tidak bisa dipakai. Cara paling gampang membayangkannya: index itu buku telepon yang tersusun alfabetis.
Tanya buku telepon: “cari semua nama yang dimulai huruf B”, dia langsung buka halaman B, selesai sepersekian detik. Sekarang tanya: “cari semua nama yang kalau huruf-hurufnya diacak ulang, dimulai huruf B”, bukunya cuma bisa baca dari halaman satu sampai tamat, karena urutan alfabetisnya tidak lagi membantu.
WHERE DATE(kolom) >= DATE(?) itu persis pertanyaan kedua: fungsi DATE() dipanggil pada kolom, artinya setiap baris di tabel puluhan juta baris harus diubah bentuknya dulu, baru dibandingkan. Index yang tersusun rapi tidak berguna untuk nilai yang belum lahir dari fungsinya. Database-nya tidak bodoh; dia cuma tidak punya pilihan.
Pindahkan Fungsinya, Bukan Kolomnya #
Perbaikannya bukan tuning eksotis, cuma memindahkan fungsi dari sisi kolom ke sisi parameter:2
-- sebelum: index didiamkan
WHERE DATE(a.dt) >= DATE(?) AND DATE(a.dt) <= DATE(?)
-- sesudah: index kembali dipakai
WHERE a.dt >= ? AND a.dt <= ?
Konsekuensinya: parameter yang dikirim harus lengkap dengan jamnya. Di kode saya, batas waktunya memang sudah disiapkan lengkap dari usecase, mulai 00:00:00, berakhir 23:59:59, jadi hasilnya tetap identik: semua baris tanggal yang sama, tanpa kehilangan satu detik pun di tengah.
Hasil akhirnya: dari ±10 detik full scan menjadi pencarian index yang sekejap mata. Deploy, dan endpoint itu pulih tanpa satu baris business logic disentuh.
Tersangka yang Tidak Bersalah #
Supaya adil, cerita episode ini punya subplot. Di penyelidikan yang sama, ada satu join yang tampak sangat mencurigakan: perbandingan antar tabel dengan penanda collate eksplisit di tengahnya. Wajahnya persis tersangka klasik query lambat: “beda collation bikin join tidak memakai index”.
Setelah dicek: kedua sisi yang di-join ternyata memakai collation yang sama persis. Penandanya redundan, bukan beracun. Tersangka dibebaskan.
Jadinya dua episode beruntun mengajarkan pelajaran yang sama dari arah berlawanan: di episode dua yang menuduh harus bukti (EXPLAIN menggugurkan tuduhan ke CTE), dan di episode ini yang diduga polos pun harus dicek (pembungkus DATE() ternyata memang pendekat). Intuisi boleh menunjuk tersangka, tapi hanya pengukuran yang boleh menjatuhkan vonis hehe~
Utang yang Diakui #
Satu bagian yang menurut saya penting untuk ditulis jujur: setelah memperbaiki satu kasus DATE(), pola yang sama saya cari di seisi codebase, dan menemukan tiga join lain dengan bentuk identik di modul bahan bakar. DATE() dipanggil pada kolom di sisi kiri dan kanan join.
Statusnya sampai episode ini ditulis: belum saya perbaiki. Endpoint-nya belum pernah dilaporkan lambat, jadi dia belum mempan dapat jatah waktu perbaikan. Tapi dia sudah masuk daftar, dan suatu hari, mungkin di episode lain, mungkin diam-diam, dia akan ketemu dengan yang mulia EXPLAIN juga hahaha.
Migrasi dengan paritas memang meninggalkan utang-utang kecil seperti ini. Yang penting bukan nol utang; yang penting utangnya tercatat dan tidak main sembunyi-sembunyian.
Pelajaran #
- Jangan panggil fungsi pada kolom di
WHERE/JOIN. Taruh fungsinya di sisi parameter, biar kolom tetap dalam bentuk yang dikenali index-nya. - Index terbaik pun bisa sia-sia oleh satu pembungkus kecil. Desain index dan desain query adalah satu paket; salah satunya saja yang melenceng, keduanya gagal.
- Setelah menemukan satu bug pola, cari saudara-saudaranya. Bug yang lahir dari satu kebiasaan menulis biasanya tidak lahir sendirian.
- Utang teknis yang dicatat itu wajar; yang disembunyikan itu berbahaya. Saya mengakuinya di tulisan publik, sekarang kalian semua saksinya, jadi tidak mungkin dilupakan hahaha.
Episode berikutnya kita naik kelas dari satu query ke bentuk kotanya: bagaimana monolith puluhan ribu baris dirombak menjadi 14 modul domain, sambil pesawatnya tetap terbang. Sampai ketemu di sana.
Sekian. Salam.