Penjelasan MySQL EXPLAIN ANALYZE: Membaca Rencana Eksekusi & Mengoptimalkan Kueri (Panduan 8.0)

1. Introduction

Execution Plans: Essential for Database Performance Optimization

Dalam aplikasi web dan sistem bisnis, kinerja basis data adalah faktor kritis yang secara langsung memengaruhi waktu respons keseluruhan. Saat menggunakan MySQL khususnya, memahami “rencana eksekusi” penting untuk mengevaluasi efisiensi kueri. Perintah EXPLAIN tradisional menampilkan rencana eksekusi sebelum menjalankan pernyataan SQL dan telah lama memberikan wawasan berharga bagi pengembang.

“EXPLAIN ANALYZE” Introduced in MySQL 8.0

Diperkenalkan di MySQL 8.0.18, EXPLAIN ANALYZE adalah peningkatan kuat dari EXPLAIN tradisional. Sementara EXPLAIN hanya memberikan “rencana teoretis,” EXPLAIN ANALYZE sebenarnya mengeksekusi kueri dan mengembalikan data terukur seperti waktu eksekusi dan jumlah baris yang diproses. Hal ini memungkinkan identifikasi bottleneck yang lebih akurat dan validasi hasil optimasi kueri.

Why EXPLAIN ANALYZE Matters

Misalnya, urutan JOIN, penggunaan indeks, dan kondisi penyaringan secara signifikan memengaruhi waktu eksekusi. Dengan menggunakan EXPLAIN ANALYZE, Anda dapat secara visual mengonfirmasi bagaimana pernyataan SQL berjalan dan menentukan di mana ketidakefisienan terjadi serta apa yang harus dioptimalkan. Ini terutama sangat penting saat bekerja dengan dataset besar atau kueri kompleks.

Purpose of This Article and Target Audience

Artikel ini menjelaskan segala hal mulai dari dasar-dasar EXPLAIN ANALYZE MySQL hingga menafsirkan outputnya dan menerapkan teknik optimasi praktis. Artikel ini ditujukan untuk pengembang dan insinyur infrastruktur yang secara rutin menggunakan MySQL, serta insinyur yang tertarik pada penyetelan kinerja. Untuk memastikan kejelasan bahkan bagi pemula, kami menyertakan penjelasan istilah dan contoh konkret di seluruh artikel.

2. Differences Between EXPLAIN and EXPLAIN ANALYZE

The Role and Basic Usage of EXPLAIN

EXPLAIN MySQL adalah alat analisis yang digunakan untuk memahami sebelumnya bagaimana pernyataan SQL (terutama pernyataan SELECT) akan dieksekusi. Ini memungkinkan Anda mengonfirmasi rencana eksekusi seperti penggunaan indeks, urutan join, dan rentang pencarian.

Sebagai contoh:

EXPLAIN SELECT * FROM users WHERE age > 30;

Ketika perintah ini dijalankan, MySQL tidak benar-benar mengeksekusi kueri, melainkan menampilkan bagaimana ia berencana memprosesnya dalam bentuk tabel. Output mencakup informasi seperti indeks yang digunakan (key), metode akses (type), dan perkiraan jumlah baris (rows).

The Role and Features of EXPLAIN ANALYZE

Sebaliknya, EXPLAIN ANALYZE, yang diperkenalkan di MySQL 8.0.18, mengeksekusi kueri dan menampilkan rencana eksekusi berdasarkan nilai yang diukur secara aktual. Hal ini memungkinkan konfirmasi detail yang tidak terlihat pada EXPLAIN tradisional, seperti waktu pemrosesan aktual dan jumlah baris yang benar-benar diproses.

Contoh:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

Perintah ini mengeksekusi kueri dan mengembalikan output yang mencakup:

• Waktu eksekusi untuk setiap langkah rencana (mis., 0.0022 sec )
• Jumlah baris yang dibaca secara aktual (rows )
• Struktur pemrosesan (dapat dengan mudah divisualisasikan menggunakan format TREE)

Summary of Key Differences

Which One Should You Use?

• Gunakan EXPLAIN ketika Anda ingin dengan cepat memeriksa struktur kueri.
• Gunakan EXPLAIN ANALYZE ketika Anda memerlukan detail konkret tentang waktu eksekusi dan biaya kueri.

Terutama dalam skenario penyetelan kinerja, EXPLAIN ANALYZE memungkinkan optimasi berdasarkan data eksekusi nyata bukan perkiraan, menjadikannya alat yang sangat kuat.

3. Output Formats of EXPLAIN ANALYZE

Three Output Formats: TRADITIONAL, JSON, and TREE

EXPLAIN ANALYZE MySQL dapat menghasilkan hasil dalam format yang berbeda tergantung pada tujuan Anda. Di MySQL 8.0 dan versi selanjutnya, tiga format berikut tersedia.

Mari kita lihat lebih dekat perbedaannya.

TRADITIONAL Format (Default)

TRADITIONAL output is similar to the classic EXPLAIN style and lets you review execution plans in a familiar form. If you run EXPLAIN ANALYZE without specifying a format, the result is generally shown in this format.

Example output (excerpt):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : perkiraan biaya
• actual time : waktu yang diukur
• rows : perkiraan jumlah baris yang diproses (sebelum eksekusi)
• loops : jumlah loop (terutama penting untuk JOIN)

TRADITIONAL format is easy for humans to scan and understand, making it suitable for beginners and quick checks.

Format JSON

JSON format is more detailed and easier to handle programmatically. The output is structured, with each node represented as a nested object.

Perintah:

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

Sebagian output (pretty-printed):

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

This format is less visually readable, but it is extremely convenient when you want to parse the data and feed it into analysis tools or dashboards.

Format TREE (Readable and Great for Visualizing Structure)

TREE format displays the query execution structure as a tree, making it easier to understand JOIN and subquery processing order.

Perintah:

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

Contoh output (simplified):

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

For complex queries, nesting can appear like this:

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

TREE format is especially useful for queries with many JOINs or complex nesting, where you need to grasp the processing flow.

Format Mana yang Harus Anda Gunakan?

Choose the format that best fits your goal, and review the execution plan in the most readable and analyzable style.

4. Cara Menafsirkan Rencana Eksekusi

Mengapa Anda Perlu Membaca Rencana Eksekusi

MySQL query performance can vary greatly depending on data volume and index availability. By correctly interpreting the execution plan output from EXPLAIN ANALYZE, you can objectively identify where work is being wasted and what should be improved. This skill is a cornerstone of performance tuning, especially for queries that handle large datasets or complex joins.

Struktur Dasar dari Rencana Eksekusi

The output of EXPLAIN ANALYZE includes information such as the following (explained here based on TRADITIONAL-style output):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

This single line contains multiple important fields.

Cara Membaca Bidang Kunci

1. cost vs. actual time

• cost adalah perkiraan internal yang dihitung oleh MySQL dan digunakan untuk evaluasi relatif.
• actual time mencerminkan waktu yang sebenarnya berlalu dan lebih penting untuk analisis kinerja.

Sebagai contoh:

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

If estimates and measurements closely match, the execution plan is likely accurate. If the gap is large, table statistics may be inaccurate.

2. rows vs. actual rows

• rows adalah jumlah baris yang diprediksi MySQL akan dibaca.
• actual rows adalah jumlah baris yang sebenarnya dibaca (ditampilkan dalam tanda kurung pada output gaya TRADITIONAL).

If there is a large discrepancy, you may need to refresh statistics or reconsider index design.

3. loops

If loops=1, langkah tersebut dijalankan sekali. Dengan JOIN atau subquery, Anda mungkin melihat loops=10 atau loops=1000. Semakin besar nilainya, semakin besar kemungkinan nested loops menyebabkan pemrosesan berat.

Memahami Struktur Bersarang dari Rencana Eksekusi

Ketika beberapa tabel digabungkan, rencana eksekusi ditampilkan sebagai pohon (terutama jelas dalam format TREE).

Contoh:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

Masalah

• Kedua tabel dipindai sepenuhnya, menghasilkan biaya join yang tinggi.

Tindakan

• Tambahkan indeks pada users.age dan filter lebih awal untuk mengurangi beban kerja join.

Cara Mengidentifikasi Bottleneck Kinerja

Memfokuskan pada poin-poin berikut membuat bottleneck lebih mudah ditemukan:

• Node dengan waktu aktual yang lama dan banyak baris : Ini mengonsumsi sebagian besar waktu eksekusi
• Tempat di mana pemindaian tabel penuh terjadi : Kemungkinan indeks hilang atau tidak digunakan
• Langkah dengan banyak loop : Menunjukkan urutan JOIN yang tidak efisien atau bersarang
• Selisih besar antara baris dan baris aktual : Menunjukkan statistik yang tidak akurat atau akses data yang berlebihan

Gunakan wawasan ini sebagai dasar untuk teknik “Optimisasi Kueri” yang diperkenalkan pada bagian berikutnya.

5. Contoh Praktis Optimisasi Kueri

Apa Itu Optimisasi Kueri?

Optimisasi kueri mengacu pada meninjau dan memperbaiki pernyataan SQL sehingga dapat dieksekusi lebih efisien. Berdasarkan cara MySQL memproses kueri secara internal (rencana eksekusi), Anda menerapkan perbaikan seperti menambahkan indeks, menyesuaikan urutan join, dan menghilangkan pemrosesan yang tidak perlu.

Di sini, kami menunjukkan cara meningkatkan kueri menggunakan EXPLAIN ANALYZE dengan contoh konkret.

Contoh 1: Peningkatan Kecepatan Menggunakan Indeks

Sebelum Optimisasi

SELECT * FROM users WHERE email = 'example@example.com';

Rencana Eksekusi (Kutipan)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

Masalah

• Output menunjukkan Table scan, yang berarti pemindaian tabel penuh dilakukan. Dengan dataset besar, ini menyebabkan penundaan signifikan.

Solusi: Tambahkan Indeks

CREATE INDEX idx_email ON users(email);

Rencana Eksekusi Setelah Optimisasi

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

Hasil

• Waktu eksekusi berkurang secara signifikan.
• Pemindaian tabel penuh dihindari dengan menggunakan indeks.

Contoh 2: Mengoptimalkan Urutan Join

Sebelum Optimisasi

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Rencana Eksekusi (Kutipan)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

Masalah

• Kedua tabel dipindai sepenuhnya, menghasilkan biaya join yang tinggi.

Solusi

• Tambahkan indeks pada users.age dan filter terlebih dahulu untuk mengurangi ukuran target join.

  CREATE INDEX idx_age ON users(age);
  

Rencana Eksekusi Setelah Optimisasi

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

Hasil

• Target JOIN difilter terlebih dahulu, mengurangi beban pemrosesan keseluruhan.

Contoh 3: Meninjau Ulang Subquery

Sebelum Optimisasi

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Masalah

• Subquery mungkin dievaluasi berulang kali, menurunkan kinerja.

Solusi: Tulis Ulang sebagai JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

Hasil

• Rencana eksekusi dioptimalkan untuk pemrosesan JOIN, dan indeks lebih mungkin digunakan.

Pentingnya Perbandingan Sebelum/Setelah

Using EXPLAIN ANALYZE, you can memverifikasi hasil optimasi dengan nilai yang diukur secara aktual. Dengan membandingkan waktu eksekusi dan jumlah baris sebelum dan sesudah perbaikan, Anda memastikan bahwa upaya penyetelan didasarkan pada peningkatan kinerja nyata, bukan asumsi.

Pertimbangan Penting dalam Optimasi

• Menambahkan terlalu banyak indeks dapat menjadi kontraproduktif (kinerja INSERT/UPDATE yang lebih lambat).
• Rencana eksekusi bergantung pada volume data dan statistik, sehingga validasi diperlukan untuk setiap lingkungan.
• Satu optimasi jarang menyelesaikan semuanya. Analisis bottleneck harus dilakukan terlebih dahulu.

6. Pencegahan dan Praktik Terbaik

Catatan Penting Saat Menggunakan EXPLAIN ANALYZE

Meskipun EXPLAIN ANALYZE sangat kuat, penggunaan yang tidak tepat dapat menyebabkan kesalahpahaman atau bahkan risiko operasional. Mengingat poin-poin berikut memastikan analisis kueri yang aman dan efektif.

1. Hindari Menjalankannya Secara Ceroboh di Produksi

Karena EXPLAIN ANALYZE sebenarnya mengeksekusi kueri, penggunaan yang keliru dengan pernyataan modifikasi (INSERT/UPDATE/DELETE) dapat mengubah data.

• Secara umum, hanya gunakan dengan pernyataan SELECT.
• Lebih baik menjalankannya di lingkungan staging atau pengujian daripada di produksi.

2. Pertimbangkan Dampak Caching

MySQL dapat mengembalikan hasil dari cache jika kueri yang sama dijalankan berulang kali. Akibatnya, waktu eksekusi yang dilaporkan oleh EXPLAIN ANALYZE mungkin berbeda dari perilaku dunia nyata.

Tindakan Penanggulangan:

• Bersihkan cache sebelum eksekusi ( RESET QUERY CACHE; ).
• Jalankan beberapa kali dan evaluasi berdasarkan nilai rata‑rata.

3. Pastikan Statistik Selalu Terbaru

MySQL membangun rencana eksekusi berdasarkan statistik tabel dan indeks. Jika statistik sudah usang, baik EXPLAIN maupun EXPLAIN ANALYZE dapat memberikan informasi yang menyesatkan.

Setelah operasi INSERT atau DELETE besar, perbarui statistik menggunakan ANALYZE TABLE.

ANALYZE TABLE users;

4. Indeks Bukanlah Solusi Ajaib

Meskipun indeks sering meningkatkan kinerja, terlalu banyak indeks memperlambat operasi penulisan.

Memilih antara indeks komposit dan indeks satu kolom juga penting. Rancang indeks dengan hati‑hati berdasarkan pola kueri dan frekuensi penggunaan.

5. Jangan Menilai Hanya Berdasarkan Waktu Eksekusi

Hasil dari EXPLAIN ANALYZE hanya mencerminkan kinerja satu kueri. Dalam aplikasi nyata, latensi jaringan atau pemrosesan backend mungkin menjadi bottleneck yang sebenarnya.

Oleh karena itu, analisis kueri dalam konteks keseluruhan arsitektur sistem.

Ringkasan Praktik Terbaik

7. Pertanyaan yang Sering Diajukan (FAQ)

Q1. Dari versi mana EXPLAIN ANALYZE tersedia?

A.
EXPLAIN ANALYZE MySQL diperkenalkan pada versi 8.0.18 dan selanjutnya. Tidak didukung pada versi sebelum 8.0, jadi Anda harus memverifikasi versi MySQL Anda sebelum menggunakannya.

Q2. Apakah menjalankan EXPLAIN ANALYZE dapat mengubah data?

A.
EXPLAIN ANALYZE mengeksekusi kueri secara internal.
Ketika digunakan dengan pernyataan SELECT, ia tidak mengubah data.

Oleh karena itu, ketika digunakan dengan pernyataan SELECT, ia tidak mengubah data.

Namun, jika Anda secara keliru menggunakannya dengan INSERT, UPDATE, atau DELETE, data akan diubah sama seperti kueri normal.

Untuk keamanan, disarankan untuk menjalankan analisis di basis data uji atau staging daripada di produksi.

Q3. Bukankah EXPLAIN saja sudah cukup?

A.
EXPLAIN cukup untuk meninjau rencana eksekusi “perkiraan”. Namun, ia tidak memberikan nilai terukur seperti waktu eksekusi aktual atau jumlah baris aktual.

Jika Anda membutuhkan penyetelan kueri yang serius atau ingin memverifikasi efek optimasi, EXPLAIN ANALYZE lebih berguna.

Q4. Seberapa akurat nilai seperti “loops” dan “actual time”?

A.
Nilai seperti actual time dan loops adalah metrik eksekusi nyata yang diukur secara internal oleh MySQL. Namun, nilai tersebut dapat berfluktuasi sedikit tergantung pada kondisi OS, status cache, dan beban server.

For this reason, do not rely on a single measurement. Instead, run the query multiple times and evaluate trends.

Q5. Apa sebenarnya yang dimaksud dengan “cost”?

A.
cost adalah nilai perkiraan yang dihitung oleh model biaya internal MySQL. Ini mencerminkan evaluasi relatif dari biaya CPU dan I/O. Nilainya tidak dinyatakan dalam detik.

Sebagai contoh, jika Anda melihat (cost=0.3) dan (cost=2.5), yang terakhir diperkirakan lebih mahal dalam istilah relatif.

Q6. Apa manfaat menggunakan format JSON atau TREE?

A.

• Format JSON : Output terstruktur yang mudah diparsing secara programatik. Berguna untuk alat otomatisasi dan dasbor.
• Format TREE : Membuat alur eksekusi dan nesting menjadi jelas secara visual. Ideal untuk memahami kueri kompleks dan urutan JOIN.

Pilih format yang paling sesuai dengan tujuan Anda.

Q7. Apa yang harus saya lakukan jika saya tidak dapat meningkatkan kinerja setelah meninjau rencana eksekusi?

A.
Pertimbangkan pendekatan tambahan seperti:

• Mendesain ulang indeks (indeks komposit atau indeks penutup)
• Menulis ulang kueri (subkueri → JOIN, menghapus kolom SELECT yang tidak diperlukan)
• Menggunakan view atau tabel temporer
• Meninjau konfigurasi MySQL (ukuran buffer, alokasi memori, dll.)

Pengoptimalan kinerja jarang berhasil dengan satu teknik saja. Pendekatan yang komprehensif dan iteratif sangat penting.