Cara Menggunakan EXPLAIN di MySQL untuk Memahami dan Mempercepat Query

Beranda » Ular sanca » Cara Menggunakan EXPLAIN di MySQL untuk Memahami dan Mempercepat Query

Jika Anda bekerja dengan MySQL cukup lama, cepat atau lambat Anda akan menemui kueri lambat yang merusak hari Anda. Mungkin laporan yang tak kunjung selesai, titik akhir API yang waktu penyelesaiannya habis saat dimuat, atau dasbor yang tiba-tiba membutuhkan waktu 20 detik untuk dimuat. Ketika itu terjadi, alat debugging pertama yang harus Anda gunakan adalah EXPLAIN keluarga perintah.

EXPLAIN dan EXPLAIN ANALYZE MySQL menunjukkan kepada Anda bagaimana pengoptimal merencanakan dan benar-benar mengeksekusi suatu kueri. Jika digunakan dengan baik, mereka akan mengungkapkan tabel mana yang dipindai, indeks mana yang digunakan (atau diabaikan), bagaimana penggabungan dilakukan, berapa banyak baris yang diperiksa, dan di mana waktu sebenarnya dihabiskan. Dalam panduan ini, kami akan menguraikan secara sederhana cara menggunakannya, cara membaca outputnya, dan cara mengubah wawasan tersebut menjadi peningkatan kinerja yang konkret.

Apa yang dilakukan EXPLAIN di MySQL (dan kapan menggunakannya)

Dalam MySQL, EXPLAIN Kata kunci adalah alat diagnostik yang menunjukkan strategi eksekusi yang direncanakan untuk suatu pernyataan, bukan menjalankannya secara normal. Kamu tempatkan EXPLAIN di depan kueri Anda, dan MySQL merespons dengan tabel kecil yang menjelaskan cara mendapatkan baris yang diminta.

Anda dapat menggunakan EXPLAIN dengan SELECT, INSERT, UPDATE, DELETE dan REPLACE pernyataan, bukan hanya SELECT. Sebagai contoh:

Contoh:

Contoh pertanyaan: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Alih-alih mengembalikan data karyawan, MySQL mengembalikan penjelasan baris demi baris tentang cara mengeksekusi pernyataan tersebut. Anda akan melihat tabel mana yang dibaca, indeks mana yang dipilih, berapa banyak baris yang diharapkan diperiksa MySQL, dan catatan tambahan seperti apakah tabel sementara atau filesort diperlukan.

Gunakan EXPLAIN setiap kali Anda menduga suatu kueri lebih lambat dari seharusnya, atau saat Anda mendesain indeks dan ingin memverifikasi bahwa MySQL benar-benar akan menggunakannya. Ini juga sangat berguna ketika Anda mewarisi SQL lawas yang kompleks dengan beberapa subkueri dan gabungan dan Anda harus merekayasa ulang apa yang dilakukan pengoptimal.

Variasi dari EXPLAIN: EXTENDED, PARTITIONS, ANALYZE dan format

EXPLAIN dasar hanyalah titik awal; MySQL menawarkan beberapa ekstensi dan format keluaran yang memberikan lebih banyak wawasan. Memahami pilihan ini membantu Anda memilih tingkat detail yang tepat untuk setiap situasi.

EXPLAIN EXTENDED menambahkan lebih banyak informasi pengoptimal, terutama filtered kolom dan teks kueri yang ditulis ulang. Setelah berlari EXPLAIN EXTENDED ... kamu bisa mengeksekusi SHOW WARNINGS; untuk melihat bagaimana pengoptimal menulis ulang kueri Anda secara internal, yang sangat berguna untuk memahami keputusan pengoptimalan.

EXPLAIN PARTITIONS menampilkan partisi mana dari tabel yang dipartisi yang akan diakses oleh kueri. The partitions Kolom ini mencantumkan partisi yang relevan, membantu Anda memverifikasi bahwa pemangkasan partisi benar-benar terjadi dan Anda tidak memengaruhi setiap partisi secara tidak sengaja.

Format keluaran juga fleksibel: Anda bisa mendapatkan keluaran berbentuk tabel, dipisahkan tab, vertikal, atau JSON di CLI, dan rencana visual dalam alat seperti MySQL Workbench. Output JSON sangat berguna untuk otomatisasi dan analisis yang lebih mendalam, karena mencakup estimasi biaya dan struktur rencana bersarang, namun perlu dicatat bahwa EXPLAIN ANALYZE di MySQL saat ini tidak mendukung keluaran JSON mentah.

Alat visual seperti MySQL Workbench dapat menyajikan EXPLAIN sebagai pohon grafis, yang seringkali lebih mudah dipahami untuk gabungan dan subkueri yang kompleks. Alat eksternal seperti Percona Toolkit, EverSQL, atau Releem dapat menggunakan output EXPLAIN atau log kueri yang lambat dan membantu Anda memprioritaskan pernyataan mana yang akan dioptimalkan terlebih dahulu.

Memahami kolom EXPLAIN (format tabel)

Saat Anda menjalankan EXPLAIN klasik tanpa format JSON, MySQL mengembalikan satu baris per tabel atau subquery yang terlibat dalam pernyataan Anda. Urutan baris-baris ini penting: ia menunjukkan urutan di mana tabel diakses selama eksekusi.

Berikut adalah kolom-kolom utama yang akan Anda lihat dan apa yang disampaikannya kepada Anda:

• id: Pengidentifikasi berurutan untuk setiap bagian kueri. Sebuah SELECT sederhana biasanya memiliki id = 1Beberapa id menunjukkan subkueri, tabel turunan, atau bagian UNION. Angka yang lebih tinggi umumnya dieksekusi sebelum angka yang lebih rendah, sehingga memberikan petunjuk urutan eksekusi.
• select_type: Menjelaskan peran SELECT tersebut dalam keseluruhan kueri, dengan nilai seperti SIMPLE (tanpa subquery atau UNION), PRIMARY (SELECT terluar dalam kueri kompleks), UNION, UNION RESULT, DERIVED (subkueri dalam FROM), atau tipe terkait teks lengkap. Ini memungkinkan Anda melihat sekilas apakah Anda berurusan dengan kueri bersarang, gabungan, atau tabel turunan.
• table: Menunjukkan tabel atau hasil internal mana yang dirujuk oleh baris ini. Ini bisa berupa nama tabel asli, atau nama semu seperti <unionM,N> untuk hasil UNION internal, <derivedN> untuk tabel turunan, atau penanda serupa untuk subkueri terwujud.
• partitions: Saat menggunakan tabel partisi bersama dengan EXPLAIN PARTITIONS, ini mencantumkan partisi yang berisi baris yang sesuai dengan kondisi kueri. Jika Anda melihat banyak partisi yang tercantum, Anda mungkin tidak mendapatkan manfaat dari pemangkasan partisi.
• type: Sering disebut tipe gabungan atau akses, ini adalah salah satu indikator kinerja terpenting. Ini menjelaskan bagaimana MySQL mengakses baris: nilai berkisar dari yang sangat efisien (seperti const, eq_ref, ref) menjadi kurang efisien (range) menjadi miskin (index, ALL pemindaian tabel penuh). Jenis khusus seperti index_merge, unique_subquery, dan index_subquery menunjukkan pengoptimalan tertentu.
• possible_keys: Mencantumkan indeks yang menurut MySQL dapat digunakan untuk bagian kueri ini. Jika ini NULL, artinya MySQL tidak melihat indeks yang berguna, yang merupakan sinyal kuat bahwa Anda mungkin perlu membuatnya berdasarkan kondisi WHERE atau JOIN Anda.
• key: Menunjukkan indeks aktual yang dipilih oleh pengoptimal untuk akses ini. Jika NULL sementara possible_keys mencantumkan kandidat, pengoptimal memutuskan bahwa penggunaan indeks tidaklah layak, sering kali karena selektivitas rendah atau ukuran tabel kecil.
• key_len: Menunjukkan jumlah byte indeks yang digunakan. Untuk indeks komposit, ini menunjukkan berapa banyak kolom utama yang aktif. Hal ini penting untuk memahami apakah indeks multi-kolom Anda digunakan sepenuhnya atau hanya sebagian.
• ref: Menunjukkan apa yang dibandingkan dengan kolom indeks yang tercantum di key: ini dapat menunjuk ke kolom tabel lain (untuk gabungan) atau ke suatu konstanta (untuk filter sederhana).
• rows: Perkiraan jumlah baris yang diharapkan diperiksa MySQL untuk langkah ini. Perkiraan ini berdasarkan statistik, tetapi sangat berguna untuk mengukur seberapa mahal sebuah kueri dan apakah indeks mengurangi ruang pencarian secara efektif.
• filtered: Tersedia dengan EXPLAIN EXTENDED, persentase ini menunjukkan berapa banyak baris yang diperiksa yang diharapkan memenuhi kondisi pada tabel tersebut. Persentase rendah dikombinasikan dengan persentase tinggi rows sering kali meminta pengindeksan yang lebih baik atau kondisi yang lebih selektif.
• Extra: Bidang bentuk bebas yang berisi catatan tambahan yang tidak sesuai di tempat lain, seperti Using index, Using where, Using temporary, Using filesort, petunjuk teks lengkap, dan banyak lagi, yang semuanya berisi petunjuk kinerja penting.

Dengan membaca kolom-kolom ini bersama-sama, alih-alih sendiri-sendiri, Anda memperoleh ringkasan yang padat namun kuat tentang bagaimana MySQL bermaksud memenuhi permintaan Anda. Dengan sedikit latihan, Anda akan segera menemukan tanda-tanda seperti pemindaian tabel penuh, indeks yang hilang, atau tabel sementara yang tidak diperlukan.

Bagaimana EXPLAIN mendefinisikan jenis akses ( type kolom)

The type Kolom ini perlu mendapat perhatian khusus karena merupakan salah satu cara tercepat untuk menilai kesehatan suatu kueri. Meskipun dokumentasi menyebutnya sebagai tipe gabungan, lebih akurat untuk menganggapnya sebagai tipe akses yang menjelaskan bagaimana baris ditemukan.

Pada tingkat tinggi, jenis akses berkisar dari “hebat” hingga “buruk” dalam hal kinerja. Meskipun daftar pastinya panjang, beberapa yang penting meliputi:

• const / system: MySQL dapat menyelesaikan tabel hingga maksimal satu baris melalui kunci utama atau indeks unik dengan nilai konstan. Ini sangat efisien.
• eq_ref: Untuk setiap baris dari tabel sebelumnya, MySQL membaca tepat satu baris yang cocok dari tabel ini, biasanya karena referensi kunci utama atau unik dalam gabungan.
• ref: MySQL mengakses baris melalui indeks non-unik yang cocok dengan konstanta atau kolom; beberapa baris mungkin cocok. Secara umum, masih bagus.
• range: MySQL menggunakan indeks untuk mengambil baris dalam rentang nilai tertentu (misalnya, BETWEEN, >=, atau kecocokan awalan dengan LIKE 'abc%'). Hal ini sering kali dapat diterima, terutama untuk rentang tanggal atau angka.
• index: MySQL memindai seluruh indeks, alih-alih data tabel secara keseluruhan. Lebih baik daripada pemindaian tabel secara keseluruhan, tetapi masih seringkali terlalu mahal untuk indeks yang besar.
• ALL: Pemindaian tabel secara menyeluruh. MySQL membaca setiap baris untuk menemukan kecocokan. Pada tabel kecil, hal ini mungkin baik-baik saja, tetapi pada tabel besar, Anda sebaiknya mempertimbangkan pengindeksan atau penulisan ulang kueri untuk menghindarinya.
• index_merge: Menunjukkan bahwa MySQL menggabungkan hasil dari beberapa indeks pada tabel yang sama. Kedengarannya bagus, tetapi dalam praktiknya seringkali kinerjanya lebih buruk daripada indeks komposit tunggal yang baik.
• unique_subquery / index_subquery: Jenis akses khusus yang digunakan untuk mengoptimalkan tertentu IN (SELECT ...) subkueri melalui pencarian indeks keunikan atau non-unik, menggantikan pola pencarian yang kurang efisien.

Saat Anda meninjau keluaran EXPLAIN, Anda biasanya ingin jenis akses berada sejauh mungkin const / eq_ref / ref sebisa mungkin dan hindari ALL di atas meja besar. Jika Anda melihat ALL dengan tinggi rows perkiraan, hampir selalu menjadi kandidat untuk pengindeksan yang lebih baik.

Key, possible_keys dan key_len: apakah indeks Anda benar-benar membantu?

Indeks sering kali merupakan satu-satunya cara paling ampuh untuk mempercepat kueri, dan EXPLAIN membantu Anda memahami dengan tepat bagaimana indeks digunakan. Tiga kolom sangat penting untuk diagnostik indeks: possible_keys, key, dan key_len.

possible_keys mencantumkan semua indeks yang menurut MySQL relevan untuk tabel tersebut berdasarkan kondisi WHERE dan JOIN. Jika ini adalah NULL untuk tabel yang berpartisipasi dalam filter atau gabungan berat, itu merupakan petunjuk kuat bahwa Anda harus mempertimbangkan untuk menambahkan indeks yang mencakup kolom relevan.

key memberitahu Anda indeks mana yang sebenarnya dipilih MySQL dari para kandidat. If key is NULL sementara ada entri di possible_keys, pengoptimal memutuskan pemindaian penuh lebih murah, yang biasanya menunjukkan selektivitas indeks yang buruk, statistik yang ketinggalan zaman, atau pola kueri yang tidak dapat memperoleh manfaat dari desain indeks yang tersedia.

key_len menunjukkan berapa banyak byte dari indeks yang dipilih yang digunakan. Untuk indeks komposit, ini memungkinkan Anda mengonfirmasi berapa banyak kolom terindeks yang berkontribusi pada pencarian. Jika Anda membuat indeks di (last_name, first_name) dan key_len hanya mencakup panjang untuk last_name, Anda tahu bahwa kueri tidak memanfaatkan indeks komposit secara maksimal.

The ref Kolom melengkapi ini dengan memberi tahu Anda apa yang dibandingkan MySQL dengan indeks, apakah itu konstanta (seperti 'Puppo') atau kolom dari tabel yang digabungkan. Jika Anda men-debug gabungan, melihat kolom gabungan yang benar di ref bersama dengan yang baik type nilai seperti eq_ref merupakan tanda bahwa gabungan Anda terindeks dengan baik.

Baris, difilter dan Ekstra: menemukan inefisiensi tersembunyi

The rows dan filtered kolom memberikan gambaran kasar tentang volume pekerjaan, sementara Extra menyoroti operasi khusus yang sering menjelaskan masalah kinerja. Ketiganya harus selalu ditinjau bersama-sama.

rows adalah perkiraan MySQL tentang berapa banyak rekaman yang harus diperiksa pada langkah ini. Hasilnya tidak selalu akurat, tetapi cukup baik untuk mendeteksi kasus-kasus yang jelas-jelas buruk, seperti memindai ratusan ribu baris padahal Anda mengharapkan hanya segelintir baris.

filtered (tersedia melalui EXPLAIN EXTENDED) memberikan perkiraan persentase baris yang diperiksa yang akan memenuhi kondisi untuk tabel tersebut. Tinggi rows dengan sangat rendah filtered Persentase ini biasanya menunjukkan indeks yang hilang atau tidak optimal atau predikat yang kurang selektif.

The Extra kolom menggabungkan catatan tambahan tentang eksekusi yang tidak muat di tempat lain. Beberapa nilai terpenting yang mungkin Anda temui meliputi:

• Using where: Kondisi WHERE diterapkan untuk memfilter baris untuk tabel ini.
• Using index: MySQL dapat menyajikan semua kolom yang diperlukan dari indeks saja tanpa menyentuh data tabel, yang dikenal sebagai indeks penutup dan umumnya sangat efisien.
• Using temporary: MySQL sedang membuat tabel sementara internal untuk menyimpan hasil sementara, misalnya untuk pemrosesan GROUP BY atau subkueri yang kompleks. Pada set data besar, hal ini dapat berdampak signifikan pada kinerja.
• Using filesort: MySQL melakukan langkah pengurutan terpisah, sering kali untuk ORDER BY atau GROUP BY, yang dapat dilakukan dalam memori atau pada disk dan biasanya lebih mahal daripada menggunakan urutan berbasis indeks.
• Catatan terkait teks lengkap: Saat menggunakan indeks FULLTEXT, EXPLAIN dapat mengungkapkan apakah pencarian teks lengkap digabungkan dengan benar dengan gabungan atau filter lainnya.

Berikan perhatian khusus Using temporary dan Using filesort in Extra saat kueri lambat, karena keduanya dapat mengindikasikan pekerjaan penyortiran atau pengelompokan berat yang mungkin dialihkan ke indeks yang lebih baik atau kueri yang direstrukturisasi.

JELASKAN ANALISIS: melihat biaya eksekusi yang sebenarnya

Dimulai dengan MySQL 8.0.18, Anda mendapatkan alat yang lebih hebat: EXPLAIN ANALYZE, yang sebenarnya menjalankan kueri dan melampirkan statistik waktu proses ke rencana. Ini menjembatani kesenjangan antara estimasi biaya teoritis dan kinerja dunia nyata.

Tidak seperti EXPLAIN vanilla, yang hanya memprediksi, EXPLAIN ANALYZE mengeksekusi pernyataan dan mengukur berapa lama setiap iterator (langkah) dalam rencana berlangsung, berapa banyak baris yang dikembalikan, dan berapa banyak perulangan yang dilakukan. Sintaksisnya sederhana:

Menjalankan: EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

Ketika Anda menjalankan EXPLAIN ANALYZE, MySQL menggunakan FORMAT=tree secara otomatis dan menghasilkan rencana berbentuk pohon yang menggabungkan metrik estimasi dan aktual. Untuk setiap node Anda dapat melihat:

• Perkiraan biaya eksekusi: Model pengoptimal tentang seberapa mahal langkah ini seharusnya.
• Perkiraan baris: Berapa banyak baris yang diharapkan dikembalikan dari iterator ini.
• Waktu sebenarnya ke baris pertama: Berapa lama waktu yang dibutuhkan sebelum baris pertama diproduksi.
• Waktu aktual yang dihabiskan per putaran: Waktu rata-rata dalam milidetik untuk mengeksekusi iterator, termasuk anak-anaknya tetapi bukan induknya, di seluruh loop.
• Baris dan loop sebenarnya: Berapa banyak baris yang benar-benar dikembalikan dan berapa kali iterator dijalankan.

Anda dapat menggunakan EXPLAIN ANALYZE dengan pernyataan SELECT, multi-tabel UPDATE, DELETE dan TABLE. Ini terutama berguna ketika EXPLAIN normal menyarankan satu hal, tetapi kueri tetap berperilaku berbeda dalam produksi, karena sekarang Anda dapat membandingkan perkiraan dengan jumlah baris dan biaya aktual.

Ketidakcocokan besar antara estimasi dan aktual mengungkapkan di mana statistik pengoptimal tidak tepat atau di mana predikat kompleks (termasuk fungsi, UDF, atau rutin tersimpan) membuat prediksi biaya tidak akurat. Tempat-tempat tersebut menjadi target utama untuk perubahan skema, indeks baru, atau penulisan ulang kueri.

EXPLAIN Klasik vs EXPLAIN ANALYZE: kekuatan dan keterbatasan

Meskipun EXPLAIN dan EXPLAIN ANALYZE sangat berguna, penting untuk memahami titik butanya sehingga Anda tidak menafsirkan keluaran secara berlebihan.

EXPLAIN reguler pada dasarnya merupakan perkiraan tentang apa yang menurut pengoptimal akan terjadi. Beberapa statistiknya merupakan perkiraan kasar, dan optimasi internal tertentu tidak ditampilkan sama sekali dalam output. Misalnya, statistik ini tidak memberi tahu Anda bagaimana pemicu, fungsi tersimpan, atau fungsi yang ditentukan pengguna akan memengaruhi waktu CPU. Statistik ini juga tidak menunjukkan apa yang terjadi di dalam prosedur tersimpan.

Beberapa label dalam keluaran merupakan penyederhanaan berlebihan dari apa yang sebenarnya terjadi. Misalnya, type Kolom ini disebut tipe gabungan, meskipun sebenarnya mewakili tipe akses. Extra nilai Using temporary tidak membedakan antara tabel sementara dalam memori vs pada disk, dan Using filesort mencakup jenis yang berbasis memori dan disk di bawah label yang sama.

EXPLAIN ANALYZE memecahkan sebagian masalah ini dengan menunjukkan waktu dan jumlah baris yang sebenarnya, tetapi juga memiliki kekurangan. Perintah tersebut harus mengeksekusi kueri, yang mungkin mahal atau bahkan berbahaya dalam proses produksi jika kueri tersebut membutuhkan banyak sumber daya atau mengubah data. Selain itu, keluaran JSON mentah saat ini tidak tersedia langsung untuk EXPLAIN ANALYZE, jadi Anda sebagian besar akan bekerja dengan format pohon di CLI atau alat klien.

Pendekatan yang tepat adalah menggunakan EXPLAIN untuk mendapatkan wawasan yang cepat dan berdampak rendah terhadap rencana, dan EXPLAIN ANALYZE untuk investigasi yang lebih mendalam di mana Anda mampu menjalankan kueri secara nyata.

Menggunakan EXPLAIN dan EXPLAIN ANALYZE untuk mengoptimalkan kueri nyata

Membaca EXPLAIN hanya berguna jika menghasilkan kueri yang lebih baik; kuncinya adalah menerjemahkan apa yang Anda lihat ke dalam indeks spesifik dan perubahan SQL. Mari kita bahas beberapa skenario pengoptimalan umum yang dapat Anda tangani langsung dengan bantuan EXPLAIN.

Bayangkan sebuah pertanyaan sederhana pada employees tabel tanpa indeks yang relevan:

Contoh filter: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Tanpa indeks, EXPLAIN kemungkinan akan ditampilkan type = ALL dan nilai yang sangat besar di rows (misalnya, sekitar 299,000 baris) yang menunjukkan pemindaian tabel penuh. Itu sinyal yang jelas bahwa Anda memerlukan indeks yang mendukung kedua kondisi tersebut.

Salah satu pendekatan naif adalah membuat dua indeks terpisah, satu di last_name dan satu lagi first_name, tetapi itu tetap tidak membuat pencarian kombinasi menjadi efisien. MySQL dapat dengan cepat menemukan semua orang dengan last_name = 'Puppo' atau semua orang dengan first_name = 'Kendra', tetapi mengidentifikasi baris 'Kendra Puppo' secara tepat memerlukan perpotongan set tersebut, yang tidak seefisien yang Anda harapkan.

Solusi yang lebih baik adalah indeks multi-kolom yang mengikuti pola pencarian Anda yang paling selektif dan sering digunakan:

Pembuatan indeks: CREATE INDEX fullnames ON employees(last_name, first_name);

Menjalankan ulang EXPLAIN sekarang, Anda akan melihat bahwa indeks ini digunakan, type membaik menjadi sesuatu seperti ref atau bahkan const tergantung pada keunikan, dan rows turun menjadi 1. Ini mengonfirmasi bahwa hanya satu baris yang perlu disentuh, yang menjelaskan peningkatan kinerja besar-besaran.

Pola serupa muncul dalam optimasi gabungan. Misalkan Anda memiliki dua tabel A dan B dengan kolom bersama X yang digunakan untuk penggabungan, dan Anda awalnya membuatnya tanpa indeks:

Skema: CREATE TABLE A ( X VARCHAR(10), Y VARCHAR(10));
CREATE TABLE B ( X VARCHAR(10), Z VARCHAR(10));

Menjalankan gabungan dengan filter pada AX dapat menampilkan pemindaian penuh pada A dan B, masing-masing dengan sekitar 10,000 baris diperiksa, karena mesin tidak memiliki pilihan yang lebih baik selain membandingkan baris secara brute-force:

Bergabung dengan kueri: EXPLAIN SELECT *
FROM A INNER JOIN B ON A.X = B.X
WHERE A.X = '100';

Setelah Anda menambahkan indeks dan batasan kunci asing yang sesuai, EXPLAIN mencerminkan strategi yang ditingkatkan. Sebagai contoh:

Indeks & FK: CREATE UNIQUE INDEX a_unique_index ON A(X);
CREATE UNIQUE INDEX b_unique_index ON B(X);
ALTER TABLE B ADD CONSTRAINT fk_b_x FOREIGN KEY (X) REFERENCES A(X);

Setelah perubahan ini, EXPLAIN biasanya akan ditampilkan type meningkatkan (misalnya eq_ref pada tabel yang disambung), serta rows turun dari ribuan menjadi sekitar 1, membuktikan bahwa gabungan sekarang menggunakan pencarian indeks yang efisien daripada memindai semuanya.

Pencocokan pola, penyortiran, dan jebakan umum lainnya

EXPLAIN sangat berharga saat menangani pola, pengurutan, dan pengelompokan, di mana SQL yang tampaknya tidak berbahaya dapat menonaktifkan indeks sepenuhnya. Mengenali pola-pola ini sejak dini akan menghemat banyak dugaan Anda.

Salah satu masalah klasik adalah pencocokan pola dengan karakter pengganti utama. Misalnya, sebuah pertanyaan dengan WHERE email LIKE '%yahoo.com' membuat tidak mungkin untuk indeks standar pada email untuk membantu, karena MySQL tidak memiliki cara untuk langsung melompat ke baris yang diakhiri dengan sufiks tersebut. EXPLAIN biasanya akan menampilkan type = ALL dan besar rows menghitung.

Wildcard yang tertinggal, seperti LIKE 'john%', ramah indeks, karena mesin dapat menggunakan indeks untuk menemukan rentang baris yang dimulai dengan 'john'. EXPLAIN akan mengonfirmasi hal ini dengan menunjukkan jenis akses yang baik dan kecil rows perkiraan pada kolom yang diindeks.

Penyortiran dan pengelompokan merupakan fitur kinerja yang lain. Jika EXPLAIN menunjukkan Using filesort in Extra bersama dengan yang besar rows perkiraan, ORDER BY atau GROUP BY Anda mungkin memaksakan langkah pengurutan eksplisit.

Contoh sortir: EXPLAIN SELECT sale_id FROM sales ORDER BY sale_date;

Menambahkan indeks pada sale_date memungkinkan MySQL untuk membaca baris secara langsung dalam urutan yang diurutkan, yang biasanya tercermin dengan menghapus EXPLAIN Using filesort dan mungkin mengubah jenis akses menjadi index atau lebih baik.

Pada saat yang sama, berhati-hatilah untuk tidak membuat indeks yang berlebihan. EXPLAIN dapat menunjukkan indeks mana yang muncul di possible_keys tetapi tidak pernah digunakan sebagai key oleh kueri penting apa pun. Kueri tersebut mungkin bisa dihapus, tetapi Anda harus memeriksa beban kerja keseluruhan terlebih dahulu; hanya karena satu kueri mengabaikan indeks, bukan berarti kueri penting lainnya tidak bergantung padanya.

TAMPILKAN PERINGATAN dan penanda penjelasan yang diperluas

Ketika query EXPLAINed bahkan tidak diurai dengan benar, Anda masih dapat mengekstrak beberapa wawasan menggunakan SHOW WARNINGS;. Perintah ini menampilkan informasi tentang pernyataan non-diagnostik terakhir dan dapat memberi Anda petunjuk sebagian tentang bagaimana MySQL menafsirkan fragmen kueri Anda.

Misalnya, jika Anda menjalankan EXPLAIN pada kueri yang merujuk ke tabel yang tidak ada atau berisi kesalahan sintaksis, EXPLAIN mungkin gagal, tetapi SHOW WARNINGS masih dapat mengungkapkan penanda internal yang menunjukkan bagaimana bagian kueri akan diperlakukan. Anda mungkin melihat penanda seperti:

• <index_lookup>(query fragment): menyarankan bahwa pencarian indeks akan dilakukan untuk fragmen itu jika kueri tersebut valid.
• <primary_index_lookup>(query fragment): menunjukkan bahwa pencarian berbasis kunci utama akan terjadi.
• <if>(condition, expr1, expr2): menunjukkan evaluasi bersyarat di bagian kueri tersebut.
• <temporary table>: menunjukkan pembuatan tabel sementara internal untuk hasil antara, misalnya sebelum penggabungan.

Menggabungkan EXPLAIN EXTENDED dengan SHOW WARNINGS merupakan cara yang ampuh untuk melihat bukan hanya rencana tetapi juga kueri yang ditulis ulang oleh pengoptimal, yang terkadang mengungkap penyederhanaan, penekanan predikat, atau transformasi lain yang mungkin tidak Anda duga.

Lebih dari EXPLAIN: model data, arsitektur, dan perkakas

Meskipun EXPLAIN merupakan mikroskop yang fantastis untuk kueri individual, kinerja berkelanjutan juga bergantung pada model data, arsitektur sistem, dan perangkat keras Anda secara keseluruhan. Anda tidak dapat memperbaiki skema yang pada dasarnya buruk atau instance yang kelebihan beban hanya dengan mengubah klausa where.

Titik awal yang baik adalah model data yang memisahkan beban kerja yang sangat berbeda dan Pemrosesan JSON dalam SQL. Misalnya, menggabungkan log akses bervolume tinggi dengan data transaksional dalam satu instans MySQL dapat menimbulkan masalah jangka panjang: seiring pertumbuhan perusahaan, lalu lintas pencatatan dapat memenuhi I/O dan menurunkan kueri bisnis penting. Memisahkan log ke dalam penyimpanan khusus atau gudang data seringkali lebih skalabel.

Arsitektur perangkat lunak juga memainkan peran utama. Keputusan tentang monolit vs. layanan mikro, gudang data, dan penyimpanan khusus (seperti basis data grafik untuk rekomendasi) akan memengaruhi jenis kueri yang Anda jalankan dan di mana. Jika suatu layanan membutuhkan analitik mendekati waktu nyata pada kumpulan data yang besar, instans MySQL OLTP standar mungkin bukan target terbaik untuk kueri tersebut.

Perangkat keras dan ukuran instans juga penting. I/O disk, memori, CPU, dan throughput jaringan semuanya memengaruhi latensi kueri. Mengidentifikasi periode penggunaan puncak dan menjadwalkan kueri analitis atau administratif yang berat di luar periode tersebut dapat melindungi kinerja yang dihadapi pengguna. Dengan penawaran DBaaS terkelola, Anda sering kali dapat menskalakan instans atau beralih ke kelas yang lebih berkinerja sesuai kebutuhan.

Terakhir, lengkapi EXPLAIN dengan alat pemantauan dan pembuatan profil yang berkelanjutan. Skema kinerja MySQL sendiri dan log kueri lambat, MySQL Workbench, Percona Toolkit pt-query-digest, alat web seperti EverSQL, dan platform seperti Releem dapat secara otomatis menampilkan kesalahan terburuk Anda dan bahkan menyarankan pengoptimalan. EXPLAIN kemudian menjadi alat bedah Anda untuk kueri spesifik tersebut.

Menambahkan EXPLAIN dan EXPLAIN ANALYZE ke kotak peralatan Anda memungkinkan Anda berhenti menebak-nebak tentang kueri yang lambat dan mulai bernalar dengan bukti konkret. Dengan memahami id, memilih tipe, metode akses, penggunaan indeks, estimasi baris, filter, dan tanda tambahan, Anda dapat secara metodis menghapus pemindaian tabel penuh, menjinakkan gabungan yang mahal, merancang indeks yang lebih cerdas, dan menghindari pengurutan yang boros dan tabel sementara, semuanya sambil tetap memperhatikan gambaran yang lebih besar dari desain skema dan infrastruktur sehingga beban kerja MySQL Anda tetap cepat dan dapat diprediksi seiring pertumbuhannya.