EXPLAIN ANALYZE

EXPLAIN menjawab “apa rencananya”, tetapi semua angkanya tetap perkiraan. Kita pernah berhadapan dengan optimizer yang memperkirakan rows = 5 padahal kenyataannya 50.000 — plan yang tampak sehat di kertas ternyata lambat di produksi. Sejak MySQL 8.0.18 ada alat yang menutup celah antara perkiraan dan kenyataan: EXPLAIN ANALYZE, yang menjalankan query sampai tuntas, mengukur setiap langkah, lalu melaporkan waktu aktual bersama estimasinya. Outputnya bukan tabel dua belas kolom, melainkan tree bertingkat yang kita baca dari atas ke bawah. Chapter ini mengajarkan cara membacanya dan kapan alat ini layak — atau justru berbahaya — dipakai.

Menyiapkan Data Latihan #

Kita pakai pasangan pesanan–pelanggan yang sama dengan chapter sebelumnya, supaya estimasi EXPLAIN dan pengukuran EXPLAIN ANALYZE bisa diadu lewat query yang persis sama:

sql
DROP DATABASE IF EXISTS belajar_opt;
CREATE DATABASE belajar_opt;
USE belajar_opt;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kota VARCHAR(40) NOT NULL,
  email VARCHAR(100) NOT NULL,
  tanggal_daftar DATE NOT NULL
);

INSERT INTO pelanggan (nama, kota, email, tanggal_daftar) VALUES
  ('Budi Santoso',   'Jakarta',    '[email protected]',  '2026-01-05'),
  ('Siti Rahayu',    'Bandung',    '[email protected]',  '2026-01-12'),
  ('Andi Wijaya',    'Surabaya',   '[email protected]',  '2026-02-01'),
  ('Rina Kartika',   'Jakarta',    '[email protected]',  '2026-02-10'),
  ('Dewi Lestari',   'Yogyakarta', '[email protected]',  '2026-03-03'),
  ('Tono Sutrisno',  'Bandung',    '[email protected]',  '2026-03-15'),
  ('Sari Wulandari', 'Medan',      '[email protected]',  '2026-04-01'),
  ('Joko Pratama',   'Jakarta',    '[email protected]',  '2026-04-20'),
  ('Nurul Hidayah',  'Surabaya',   '[email protected]', '2026-05-05'),
  ('Rizky Maulana',  'Medan',      '[email protected]', '2026-05-18');

CREATE TABLE pesanan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan_id INT NOT NULL,
  tanggal DATE NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO pesanan (pelanggan_id, tanggal, total, status) VALUES
  (1,  '2026-01-10', 150000.00, 'selesai'),
  (2,  '2026-01-15', 250000.00, 'selesai'),
  (3,  '2026-01-20',  75000.00, 'batal'),
  (1,  '2026-02-05', 320000.00, 'selesai'),
  (4,  '2026-02-12', 125000.00, 'diproses'),
  (5,  '2026-02-18', 480000.00, 'selesai'),
  (2,  '2026-03-01', 210000.00, 'selesai'),
  (6,  '2026-03-08',  95000.00, 'batal'),
  (1,  '2026-03-15', 540000.00, 'diproses'),
  (7,  '2026-03-22', 175000.00, 'selesai'),
  (8,  '2026-04-02', 260000.00, 'selesai'),
  (3,  '2026-04-09', 310000.00, 'selesai'),
  (9,  '2026-04-16',  88000.00, 'batal'),
  (4,  '2026-04-25', 430000.00, 'selesai'),
  (10, '2026-05-01', 365000.00, 'selesai');

CREATE INDEX idx_tanggal ON pesanan(tanggal);
CREATE INDEX idx_pelanggan ON pesanan(pelanggan_id);

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Estimasi Dulu, Pengukuran Kemudian #

Kita mulai dari sisi estimasi — plan EXPLAIN biasa untuk join dengan filter tanggal:

sql
EXPLAIN SELECT o.id, p.nama, o.total
FROM pesanan o
JOIN pelanggan p ON p.id = o.pelanggan_id
WHERE o.tanggal >= '2026-04-01';

Output yang Dihasilkan:

text
+----+-------------+-------+------------+--------+---------------------------+-------------+---------+----------------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys             | key         | key_len | ref                        | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------------------+-------------+---------+----------------------------+------+----------+-----------------------+
|  1 | SIMPLE      | o     | NULL       | range  | idx_tanggal,idx_pelanggan | idx_tanggal | 3       | NULL                       |    5 |   100.00 | Using index condition |
|  1 | SIMPLE      | p     | NULL       | eq_ref | PRIMARY                   | PRIMARY     | 4       | belajar_opt.o.pelanggan_id |    1 |   100.00 | NULL                  |
+----+-------------+-------+------------+--------+---------------------------+-------------+---------+----------------------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

Plan itu berkata: rentang idx_tanggal memperkirakan 5 baris pesanan, lalu setiap baris di-lookup ke pelanggan lewat PRIMARY dengan rows = 1. Sekarang kita minta MySQL membuktikannya — query yang sama dijalankan sungguhan dan diukur:

sql
-- @check
EXPLAIN ANALYZE SELECT o.id, p.nama, o.total
FROM pesanan o
JOIN pelanggan p ON p.id = o.pelanggan_id
WHERE o.tanggal >= '2026-04-01';

Output yang Dihasilkan:

text
+----------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=4.26 rows=5) (actual time=0.0349..0.0425 rows=5 loops=1)                                 |
|     -> Index range scan on o using idx_tanggal over ('2026-04-01' <= tanggal), with index condition: (o.tanggal >= DATE'2026-04-01')  (cost=2.51 rows=5) (actual time=0.0277..0.0304 rows=5 loops=1) |
|     -> Single-row index lookup on p using PRIMARY (id=o.pelanggan_id)  (cost=0.27 rows=1) (actual time=0.00171..0.00177 rows=1 loops=5) |
 |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Nilai di atas adalah hasil satu eksekusi nyata; pada pemanggilan berikutnya angka actual time bergeser sedikit. Karena waktu aktual memang bervariasi antar-run, blok EXPLAIN ANALYZE dalam chapter ini kita perlakukan sebagai output ilustratif yang tidak dibandingkan ketat — bagian penutup chapter menjelaskan implikasinya untuk pengukuran.

Membaca Format Tree #

Output EXPLAIN ANALYZE adalah pohon langkah yang menjorok ke kanan; setiap baris satu operator. Nilai dalam tanda kurung itulah yang kita bedah. Format umumnya:

text

-> Nama operator (cost=C rows=R) (actual time=T1..T2 rows=A loops=L)

Segmen	Arti
`cost=C rows=R`	Estimasi optimizer — angka yang sama dengan `EXPLAIN` biasa
`actual time=T1..T2`	Pengukuran nyata dalam milidetik, dirata-ratakan seluruh loop: `T1` waktu rata-rata sampai baris pertama, `T2` waktu rata-rata sampai langkah selesai
`rows=A`	Jumlah baris aktual yang dihasilkan per loop (rata-rata)
`loops=L`	Berapa kali operator dijalankan

Kita terapkan pada pohon di atas. Baris teratas Nested loop inner join adalah pembungkus seluruh join. Anak pertama — Index range scan on o using idx_tanggal — dijalankan sekali (loops=1) dan menghasilkan rows=5: perkiraan rows=5 ternyata tepat. Anak kedua — Single-row index lookup on p using PRIMARY — mencantumkan loops=5: lookup ke pelanggan dijalankan lima kali, sekali untuk setiap pesanan, dan setiap kalinya menghasilkan tepat satu baris. Waktu actual time yang kecil pada setiap operator menjumlah ke atas: itulah cara kita menemukan langkah mana yang memakan porsi terbesar dari total.

Perbedaan mendasarnya: EXPLAIN berhenti di rencana, EXPLAIN ANALYZE mengeksekusi lalu melaporkan. Ketika estimasi rows jauh di bawah rows aktual, optimizer bekerja dengan statistik yang keliru — dan itu biasanya petunjuk untuk menjalankan ANALYZE TABLE supaya statistik disegarkan.

Waktu Aktual Bervariasi Antar-Run #

Angka actual time bergantung pada kondisi mesin saat query dijalankan: cache prosesor, beban server, isi buffer pool. Dua eksekusi query yang sama pada data yang sama menghasilkan angka yang mirip, tetapi hampir tidak pernah identik sampai desimal terakhir. Contoh berikut mengukur satu langkah sederhana — menghitung seluruh baris pelanggan:

sql
-- @check
EXPLAIN ANALYZE SELECT COUNT(*) FROM pelanggan;

Output yang Dihasilkan:

text
+--------------------------------------------------------------------+
| EXPLAIN                                                            |
+--------------------------------------------------------------------+
| -> Count rows in pelanggan  (actual time=0.00981..0.00987 rows=1 loops=1) |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

Nilai rows=1 dan loops=1 stabil karena ditentukan oleh data, sedangkan angka actual time berganti sedikit pada setiap pemanggilan. Karena itu, saat kita membandingkan dua versi query dengan EXPLAIN ANALYZE, kesimpulannya harus diambil dari pola besar — jumlah baris aktual, jumlah loop, proporsi waktu antar langkah — bukan dari selisih desimal waktu. Angka waktu yang bergeser 10% ke bawah bukan bukti query memburuk.

Batas EXPLAIN ANALYZE: DML Ditolak #

Karena query dijalankan sungguhan, wajar kita bertanya: apa yang terjadi kalau objeknya UPDATE atau DELETE? MySQL 8.4 menjawab dengan menolak mengeksekusinya — executor yang dipakai EXPLAIN ANALYZE (iterator executor) hanya melayani SELECT:

sql
EXPLAIN ANALYZE UPDATE pesanan SET status = 'batal' WHERE id = 99;

Output yang Dihasilkan:

text
+-------------------------------------------+
| EXPLAIN                                   |
+-------------------------------------------+
| -> <not executable by iterator executor>
 |
+-------------------------------------------+
1 row in set (0.00 sec)

Output <not executable by iterator executor> menegaskan penolakan itu — dan kita bisa memverifikasi sendiri bahwa data tidak berubah. Ini perilaku yang menyenangkan: rencana DML tetap bisa diintip dengan EXPLAIN biasa (chapter sebelumnya), sementara EXPLAIN ANALYZE menolak menjalankan DML secara diam-diam.

Kapan Memakai EXPLAIN ANALYZE #

Alat ini paling tepat dipakai setelah EXPLAIN memberi kecurigaan — plan tampak wajar tetapi query lambat, atau estimasi rows dicurigai meleset. Urutan kerjanya: EXPLAIN dulu (murah, aman), baca type/key/rows, baru EXPLAIN ANALYZE untuk mengonfirmasi angka aktual dan menemukan langkah termahal. Untuk pengukuran performa yang lebih menyeluruh — cache panas atau dingin, perbandingan sebelum-sesudah — kita masih butuh metodologi yang dibahas di chapter penutup section ini.

Membersihkan Database Latihan #

sql
DROP DATABASE belajar_opt;

Output yang Dihasilkan:

text

Query OK, 2 rows affected (0.00 sec)

Referensi #

Estimasi dan pengukuran kini berdiri berdampingan: kita tahu cara membaca cost versus actual time, rows versus rows aktual, dan arti loops. Plan yang dibaca dengan baik mengarahkan kita ke satu pertanyaan berikutnya — dari sekadar “apakah tabel dipindai seluruhnya” menjadi “seberapa buruk cara tabel ini diakses dibanding cara terbaiknya”. Hierarki jawaban itu bernama access types, dari system sampai ALL, dan chapter berikutnya membedahnya satu per satu dengan contoh nyata.