Membaca Rencana EXPLAIN

Begitu Anda mencapai tahap wawancara tingkat lanjut, pewawancara berhenti meminta tuliskan kueri dan mulai bertanya mengapa kueri ini lambat. Alat yang menjawab pertanyaan itu adalah EXPLAIN.

EXPLAIN menampilkan rencana eksekusi basis data: strategi langkah demi langkah yang dipilih perencana untuk menjalankan SQL Anda. Rencana ini menunjukkan tabel mana yang dipindai, dalam urutan apa tabel tersebut digabungkan, dan perkiraan biaya setiap langkah.

Mampu membaca rencana menunjukkan bahwa Anda memahami mesin basis data, bukan hanya sintaksisnya. Itulah tepatnya pembeda yang digunakan pewawancara untuk memisahkan pengembang tingkat menengah dari pengembang berpengalaman.

Ada dua bentuk, dan pewawancara senang menguji perbedaannya.

• EXPLAIN menampilkan rencana perkiraan perencana tanpa menjalankan kueri. Cepat dan aman.
• EXPLAIN ANALYZE benar-benar menjalankan kueri dan melaporkan jumlah baris serta waktu nyata di samping perkiraannya.

Bagian yang paling berharga adalah membandingkan jumlah baris perkiraan dengan jumlah baris nyata. Ketidakcocokan besar berarti perencana memiliki statistik yang buruk dan kemungkinan besar membuat pilihan yang keliru.

Perhatian: EXPLAIN ANALYZE benar-benar menjalankan kueri, sehingga operasi INSERT atau UPDATE akan dilakukan kecuali dibungkus dalam transaksi yang dibatalkan.

Rencana adalah sebuah pohon, bukan daftar. Simpul yang paling menjorok ke dalam adalah daun yang dijalankan lebih dahulu; hasilnya mengalir ke atas menuju akar, yang menghasilkan keluaran akhir.

Bacalah dari bagian dalam ke luar: temukan simpul terdalam karena di situlah eksekusi dimulai. Setiap simpul induk menggunakan baris yang dihasilkan oleh simpul anaknya.

Dalam wawancara, jelaskan dengan cara itu: pertama-tama kita memindai tabel ini, baris-baris tersebut menjadi masukan bagi penggabungan ini, penggabungan tersebut menjadi masukan bagi pengurutan, dan pengurutan tersebut menjadi masukan bagi pembatas. Penjelasan dari bawah ke atas itulah yang ingin mereka dengar.

Setiap simpul dalam rencana Postgres membawa angka utama yang sama:

• biaya=0.00..35.50 biaya awal..biaya total dalam satuan arbitrer perencana
• baris=1000 perkiraan jumlah baris yang dihasilkan
• lebar=64 perkiraan ukuran rata-rata baris dalam byte

Biaya pertama adalah biaya awal (pekerjaan sebelum baris pertama muncul, seperti membangun tabel hash). Biaya kedua adalah biaya total untuk mengembalikan semua baris. Biaya total yang lebih tinggi merupakan perkiraan perencana tentang beban relatif yang lebih besar.

Perhatikan kueri sederhana dengan penyaringan. Rencana di bawah ini menjelaskan alurnya dalam satu baris.

Rencana tersebut melakukan Pemindaian Berurutan (pembacaan seluruh tabel) pada orders, dengan menerapkan penyaring status = 'shipped'. Perencana memperkirakan ada 1000 baris yang cocok.

Jika orders memiliki 10 juta baris dan hanya 1000 yang cocok, pewawancara mengharapkan Anda mengatakan: pemindaian berurutan di sini boros, indeks pada status (atau pada kolom yang lebih selektif) akan memungkinkan kita menghindari pembacaan seluruh tabel.

Dengan EXPLAIN ANALYZE Anda juga mendapatkan angka aktual dalam tanda kurung.

Perhatikan contohnya: perencana memperkirakan 1000 baris, tetapi sebenarnya mendapatkan 480000 baris. Itu berarti perkiraannya terlalu rendah 480 kali. Perencana memilih strateginya dengan mengasumsikan jumlah baris sedikit, sehingga pilihannya mungkin salah untuk data sebenarnya.

Dalam wawancara, kesenjangan ini adalah diagnosis utama Anda: statistiknya sudah usang, jalankan ANALYZE pada tabel, lalu perencana kemungkinan besar akan memilih rencana yang lebih baik.

Nilai loops lebih penting daripada yang diperkirakan banyak kandidat. Nilai ini adalah jumlah kali sebuah simpul dieksekusi.

Nilai ini muncul pada sisi bagian dalam penggabungan perulangan bersarang: simpul bagian dalam berjalan satu kali untuk setiap baris luar. Jika loops=480000, langkah bagian dalam tersebut dieksekusi 480 ribu kali.

Penting: waktu per baris dan jumlah baris yang ditampilkan adalah per perulangan. Untuk mendapatkan total sebenarnya, Anda harus mengalikannya dengan loops. Simpul yang terlihat murah, yaitu 0.004ms per perulangan, menjadi hampir 2 detik setelah 480000 perulangan.

Jebakan yang sering terjadi: kandidat membaca cost=18334 lalu mengatakan itu membutuhkan 18 detik. Salah.

Biaya menggunakan satuan arbitrer perencana, yang dikalibrasi sehingga satu pembacaan halaman secara berurutan bernilai 1.0. Biaya hanya bermakna untuk membandingkan rencana satu sama lain, bukan sebagai angka waktu nyata.

Untuk waktu nyata, Anda memerlukan EXPLAIN ANALYZE dan nilai actual time-nya, yang diukur dalam milidetik. Sampaikan hal ini dengan jelas dalam wawancara; ini menunjukkan bahwa Anda benar-benar memahami metrik tersebut.

Berikut adalah rencana untuk dua tabel. Bacalah dari bawah ke atas.

Dua pemindaian pertama mengumpulkan baris dari orders dan customers. Keduanya memasok data ke Penggabungan Hash: satu sisi diubah menjadi hash, sedangkan sisi lainnya mencari data dalam hash tersebut. Keluaran penggabungan kemudian memasok hasil akhir.

Perhatikan bahwa indentasi menunjukkan strukturnya: kedua pemindaian berada di bawah Penggabungan Hash. Pewawancara ingin Anda mengidentifikasi metode penggabungan (hash dalam contoh ini) dan tabel mana yang diubah menjadi hash (biasanya tabel yang lebih kecil).

Latih mata Anda untuk mengenali tanda-tanda peringatan berikut dalam rencana apa pun:

• Pemindaian Berurutan pada tabel besar dengan penyaring selektif, indeks mungkin dapat membantu.
• Baris perkiraan sangat berbeda dari baris aktual, statistiknya sudah usang.
• Penggabungan Perulangan Bersarang dengan banyak perulangan pada tabel besar, sering kali menunjukkan tidak adanya indeks pada kunci penggabungan bagian dalam.
• Pengurutan atau Hash yang meluber ke cakram (ditunjukkan sebagai penggunaan Disk), work_mem terlalu kecil.
• Baris yang Dihapus oleh Penyaring sangat banyak, artinya Anda membaca lalu membuang sebagian besar tabel.

Rencana tersedia dalam beberapa format. TEXT bawaan adalah format yang Anda bacakan dalam wawancara. Namun, Anda juga dapat meminta keluaran terstruktur.

EXPLAIN (FORMAT JSON) atau FORMAT YAML menghasilkan rencana yang dapat dibaca mesin, lalu diuraikan oleh alat dan dasbor. Anda jarang perlu membacanya secara manual, tetapi mengetahui keberadaannya menunjukkan pemahaman tingkat senior.

Tambahkan opsi dalam tanda kurung: EXPLAIN (ANALYZE, BUFFERS). Opsi BUFFERS melaporkan temuan tembolok dibandingkan pembacaan dari cakram, sehingga sangat berguna untuk mendiagnosis kueri yang terikat I/O.

Pewawancara menunjukkan kepada Anda sebuah simpul EXPLAIN ANALYZE dengan rows=1000 di bagian biaya, tetapi dengan actual ... rows=480000. Apa diagnosis yang paling mungkin?

Sekarang Anda dapat membaca rencana seperti seorang pengembang tingkat senior:

• EXPLAIN membuat perkiraan, sedangkan EXPLAIN ANALYZE menjalankan dan mengukur.
• Bacalah pohonnya dari bawah ke atas; simpul daun berjalan lebih dahulu, sedangkan akar menghasilkan keluaran.
• Setiap simpul menampilkan biaya (satuan relatif), baris, dan lebar; actual time adalah angka sebenarnya dalam milidetik.
• loops mengalikan angka per perulangan, jadi perhatikan penggabungan perulangan bersarang.
• Kesenjangan antara jumlah baris perkiraan dan aktual adalah sinyal diagnosis utama Anda.

Jelaskan rencana tersebut dengan lantang dan sebutkan tanda-tanda bahayanya; itulah perilaku yang memenangkan wawancara.