Menemukan dan Memperbaiki Kueri Lambat

Ini adalah soal wawancara puncak: pewawancara memberikan kueri yang lambat beserta rencana EXPLAIN ANALYZE, lalu meminta Anda mendiagnosisnya. Yang diuji adalah metode, bukan trik yang dihafalkan.

Jawaban yang kuat mengikuti daftar periksa dan menjelaskannya dengan lantang: ukur, baca rencana, temukan biaya dominan, susun hipotesis, usulkan perbaikan, lalu verifikasi. Pelajaran ini membangun daftar periksa tersebut langkah demi langkah.

Tetaplah sistematis dan jelaskan alur penalaran Anda; itulah yang membuat Anda memperoleh penilaian tingkat senior.

Jangan pernah menebak hanya dari SQL. Dapatkan rencana aktual dengan EXPLAIN (ANALYZE, BUFFERS).

ANALYZE memberikan waktu dan jumlah baris aktual; BUFFERS menunjukkan apakah Anda membaca dari cache atau disk. Keduanya memberi tahu apakah kueri dibatasi oleh CPU, dibatasi oleh I/O, atau hanya melakukan terlalu banyak pekerjaan.

Jalankan beberapa kali; proses pertama mungkin terkena biaya awal karena cache belum terisi sehingga waktunya tidak mencerminkan kondisi normal.

Jangan membaca dari atas ke bawah sambil mencari secara acak. Temukan simpul tempat waktu paling banyak benar-benar digunakan.

Hitung waktu mandiri setiap simpul: total actual time-nya dikurangi waktu anak-anaknya, lalu dikalikan dengan loops. Simpul dengan bagian terbesar adalah sasaran Anda; yang lainnya hanyalah gangguan.

Dalam wawancara, katakan: 80 persen waktu eksekusi dihabiskan pada pemindaian sekuensial ini, jadi di situlah saya berfokus. Mengoptimalkan hal lain hanya akan membuang-buang usaha.

Pada simpul dominan, bandingkan jumlah baris yang diperkirakan dengan jumlah baris aktual. Perbedaan besar berarti perencana bekerja tanpa informasi yang memadai dan kemungkinan besar memilih rencana yang buruk (algoritme penggabungan yang salah, metode akses yang salah).

Contoh ini menunjukkan perkiraan yang terlalu rendah hingga 1000 kali. Sebelum merancang ulang apa pun, perbarui statistik; satu perintah ini sering memperbaiki rencana tanpa biaya tambahan.

ANALYZE menghitung ulang statistik kolom; VACUUM ANALYZE juga membersihkan tupel mati dan memperbarui peta visibilitas.

Bug yang paling sering dan dapat diperbaiki: sebuah fungsi atau konversi tipe membungkus kolom dalam WHERE, sehingga indeks tidak dapat digunakan dan mesin melakukan pemindaian sekuensial.

Contoh ini memaksa pemindaian penuh karena DATE() diterapkan pada setiap baris. Tulis ulang menjadi predikat rentang kolom langsung (bentuk yang dapat memanfaatkan indeks), dan indeks pada created_at akan digunakan.

Gagasan yang sama berlaku untuk WHERE lower(email)=...: simpan data yang sudah dinormalisasi, kueri kolom langsung, atau buat indeks ekspresi.

Jika simpul dominan adalah pemindaian sekuensial dengan penyaring yang sangat selektif, atau perulangan bersarang dengan loops sangat besar pada kunci sisi dalam yang tidak memiliki indeks, perbaikannya biasanya adalah menambahkan indeks.

Tambahkan indeks pada kolom yang disaring atau digabungkan. Contoh ini membuat indeks pada customer_id agar penggabungan dapat beralih dari pemindaian sekuensial ke pemindaian indeks, dan perencana mungkin memilih rencana yang jauh lebih murah.

Verifikasi dengan menjalankan kembali EXPLAIN ANALYZE; jangan berasumsi bahwa indeks tersebut membantu.

SELECT * menarik setiap kolom dari disk dan melalui jaringan, serta mencegah pemindaian yang hanya menggunakan indeks karena indeks jarang mencakup semua kolom.

Pilih hanya kolom yang Anda perlukan. Ini memperkecil lebar baris, mengurangi operasi baca-tulis, dan dapat memungkinkan pemindaian yang hanya menggunakan indeks serta mencakup semua kebutuhan.

Pewawancara yang sengaja menaruh SELECT * ingin Anda menyadarinya. Memangkas daftar kolom sering kali memberikan peningkatan yang cepat dan nyata pada tabel dengan baris lebar.

Jika simpul Sort atau Hash melaporkan penggunaan disk (Sort Method: external merge Disk: 25000kB atau Batches: > 1), operasi tersebut melampaui work_mem dan menulis data sementara ke disk.

Pilihannya: naikkan work_mem untuk sesi tersebut, kurangi jumlah baris yang mencapai pengurutan atau operasi hash (lakukan penyaringan lebih awal), atau tambahkan indeks yang menyediakan urutan terurut sehingga pengurutan sama sekali tidak diperlukan.

Ini adalah diagnosis yang tepat pada tingkat senior dan biasanya dihargai oleh pewawancara.

Perhatikan Rows Removed by Filter: 9500000. Kueri tersebut membaca sepuluh juta baris lalu membuang hampir semuanya; ini merupakan pekerjaan yang sia-sia.

Perbaikannya: tambahkan indeks agar penyaring diterapkan saat akses, bukan setelahnya; buat predikat lebih selektif; atau lakukan penyaringan lebih awal dalam kueri agar lebih sedikit baris mengalir ke atas pohon.

Prinsipnya: lakukan pekerjaan sesedikit mungkin, dan saring sedini serta semurah mungkin.

Ucapkan ini dalam wawancara agar Anda tidak kehilangan arah:

• Ukur dengan EXPLAIN (ANALYZE, BUFFERS).
• Temukan simpul yang menghabiskan waktu paling banyak.
• Bandingkan jumlah baris yang diperkirakan dengan jumlah aktual, dan perbaiki statistik usang terlebih dahulu.
• Periksa kemampuan menggunakan indeks, lalu hapus fungsi dari kolom yang disaring.
• Beri indeks pada penyaring selektif dan kunci penggabungan.
• Pangkas kolom, dan hindari SELECT *.
• Perhatikan penulisan data sementara ke disk dan pengambilan baris berlebih.
• Verifikasi dengan menjalankan kembali rencana tersebut.

Jelaskan satu contoh lengkap secara lisan. Rencana eksekusi menunjukkan pemindaian berurutan pada tabel orders berisi 50 juta baris, penyaringan customer_id = 42, Rows Removed by Filter mendekati 50 juta, dan estimasinya kira-kira sesuai dengan nilai aktual.

Diagnosis: penyaringan selektif, tidak ada indeks, dan biaya dominannya adalah pemindaian. Perbaikan: CREATE INDEX ON orders(customer_id). Jalankan kembali: rencana berubah menjadi pemindaian indeks, dan waktu turun dari beberapa detik menjadi kurang dari satu milidetik.

Putaran ukur-diagnosis-perbaiki-verifikasi ini adalah pola jawaban untuk pertanyaan tentang kueri lambat apa pun.

Anda kini memiliki metode yang dapat diulang untuk menjawab pertanyaan tentang kueri lambat:

• Selalu mengukur dengan EXPLAIN (ANALYZE, BUFFERS) dan berfokus pada simpul dominan.
• Perbaiki statistik yang kedaluwarsa terlebih dahulu ketika estimasi dan nilai aktual berbeda jauh.
• Buat predikat dapat memanfaatkan indeks, tambahkan indeks untuk penyaringan selektif dan kunci penggabungan, serta batasi kolom yang dipilih daripada menggunakan SELECT *.
• Atasi luapan ke penyimpanan dan pengambilan data berlebih, lalu verifikasi rencana yang baru.

Uraikan daftar periksa, usulkan perubahan konkret, dan jalankan kembali rencana untuk membuktikannya; itulah jawaban seorang insinyur berpengalaman.