EXPLAIN

Membaca rencana eksekusi: kolom type, key, dan baris yang diperiksa.

Daftar isi

Rencana eksekusi adalah urutan langkah yang dipilih optimizer untuk menjawab sebuah query, dan tanpa melihat rencana itu, penilaian atas sebuah query hanyalah dugaan. Topik ini membahas pernyataan EXPLAIN yang menampilkan rencana tersebut tanpa menjalankan querynya, dengan fokus pada tiga kolom yang paling sering dibaca, yaitu type, key, dan rows. Latihan memakai basis data perpustakaan_explain berisi 2500 anggota dan 100000 baris peminjaman, sehingga setiap jenis rencana dapat dibandingkan pada tabel yang sama. Setelah topik ini, keluaran EXPLAIN dapat dibaca untuk menilai apakah sebuah query memakai indeks atau memindai seluruh tabel.

Data latihan#

Berkas berikut membuat ulang basis data perpustakaan_explain dari nol. Tabel anggota berisi 2500 baris, dan tabel peminjaman berisi 100000 baris dengan pembagian yang rapi, yaitu setiap anggota memiliki tepat 40 baris. Satu-satunya indeks secondary adalah idx_anggota pada kolom anggota_id. Opsi STATS_SAMPLE_PAGES menjaga kestabilan angka perkiraan, sedangkan ANALYZE TABLE memperbarui statistik setiap tabel sebelum EXPLAIN dijalankan.

sql skema-explain.sql

-- tidak-dijalankan
-- Skema basis data latihan "perpustakaan_explain" untuk topik EXPLAIN.
-- Menjalankan ulang berkas ini selalu menghasilkan isi yang sama.

DROP DATABASE IF EXISTS perpustakaan_explain;
CREATE DATABASE perpustakaan_explain;
USE perpustakaan_explain;

SET SESSION cte_max_recursion_depth = 1000000;

CREATE TABLE anggota (
  anggota_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nama VARCHAR(30) NOT NULL
);

CREATE TABLE peminjaman (
  peminjaman_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  anggota_id INT UNSIGNED NOT NULL,
  buku_id INT UNSIGNED NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  status VARCHAR(10) NOT NULL
) STATS_SAMPLE_PAGES=1024;

INSERT INTO anggota (nama)
WITH RECURSIVE urut AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM urut WHERE n < 2500
)
SELECT CONCAT('Anggota ', n) FROM urut;

INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, status)
WITH RECURSIVE urut AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM urut WHERE n < 100000
)
SELECT
  ((n - 1) DIV 40) + 1,
  (n MOD 500) + 1,
  DATE_ADD('2024-01-01', INTERVAL (n MOD 730) DAY),
  ELT((n MOD 3) + 1, 'dipinjam', 'kembali', 'terlambat')
FROM urut;

CREATE INDEX idx_anggota ON peminjaman (anggota_id);

ANALYZE TABLE anggota;
ANALYZE TABLE peminjaman;

bash

# membuat ulang basis data latihan beserta statistiknya
mysql -t -u root < skema-explain.sql

text Keluaran

+------------------------------+---------+----------+----------+
| Table                        | Op      | Msg_type | Msg_text |
+------------------------------+---------+----------+----------+
| perpustakaan_explain.anggota | analyze | status   | OK       |
+------------------------------+---------+----------+----------+
+---------------------------------+---------+----------+----------+
| Table                           | Op      | Msg_type | Msg_text |
+---------------------------------+---------+----------+----------+
| perpustakaan_explain.peminjaman | analyze | status   | OK       |
+---------------------------------+---------+----------+----------+

Dua baris status OK menandakan statistik kedua tabel sudah diperbarui. Dengan statistik yang segar, angka perkiraan pada seluruh keluaran EXPLAIN berikutnya dapat dibandingkan secara adil antar eksekusi.

Bentuk keluaran EXPLAIN#

EXPLAIN ditulis di depan pernyataan SELECT dan tidak menjalankan query yang mengikutinya, sehingga rencana dapat dibaca dengan aman pada tabel berukuran besar. Keluarannya berupa satu baris untuk setiap tabel yang dibaca rencana tersebut. Setiap baris memuat kolom-kolom berikut.

Kolom	Isi
`id`	nomor urut `SELECT` di dalam query
`select_type`	jenis `SELECT`, misalnya `SIMPLE` untuk query tanpa subquery
`table`	tabel yang dibaca pada baris rencana ini
`partitions`	partisi yang menyentuh hasil, bernilai `NULL` bila tabel tidak dipartisi
`type`	cara pembacaan tabel, diurutkan pada bagian berikutnya
`possible_keys`	indeks kandidat yang tersedia untuk query ini
`key`	indeks yang benar-benar dipilih, bernilai `NULL` bila tidak ada
`key_len`	panjang bagian kunci indeks yang dipakai, dalam byte
`ref`	kolom atau konstanta yang dibandingkan dengan indeks terpilih
`rows`	perkiraan jumlah baris yang diperiksa
`filtered`	perkiraan persentase baris yang lolos kondisi pada tabel ini
`Extra`	keterangan tambahan dari optimizer

Keluaran pada topik ini ditampilkan vertikal dengan \G agar setiap kolom menempati satu baris dan tidak terpotong. Pemilihan \G hanya menyangkut tampilan klien dan tidak mengubah isi rencananya.

Tangga type#

Kolom type menggolongkan cara pembacaan tabel, dan nilainya membentuk tangga dari yang paling mahal sampai yang paling murah. Diagram berikut menampilkan enam anak tangga yang dibahas pada topik ini.

Tangga nilai type pada EXPLAIN beserta arah kecepatannya

Anak tangga pertama adalah const, yaitu pembacaan satu baris melalui primary key atau indeks unik. Query berikut mengambil satu anggota melalui primary key.

bash

# pembacaan satu baris melalui primary key
mysql -u root perpustakaan_explain -e "EXPLAIN SELECT nama FROM anggota WHERE anggota_id = 42 \G"

text Keluaran

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: anggota
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

Anak tangga berikutnya adalah ref, yaitu pembacaan melalui indeks yang tidak unik, sehingga jumlah barisnya bisa lebih dari satu. Query dengan anggota_id = 42 pada tabel peminjaman jatuh pada golongan ini.

bash

# pembacaan melalui indeks non-unik
mysql -u root perpustakaan_explain -e "EXPLAIN SELECT status FROM peminjaman WHERE anggota_id = 42 \G"

text Keluaran

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: ref
possible_keys: idx_anggota
          key: idx_anggota
      key_len: 4
          ref: const
         rows: 40
     filtered: 100.00
        Extra: NULL

Kedua keluaran sama-sama memakai indeks, tetapi const menjanjikan satu baris dan ref menjanjikan 40 baris. Perbedaan itu berasal dari sifat indeksnya, yaitu unik pada PRIMARY dan biasa pada idx_anggota.

Rentang, indeks penuh, dan pemindaian tabel#

Nilai range muncul apabila indeks dipakai untuk jangkauan nilai, misalnya BETWEEN. Query berikut mengambil peminjaman milik tiga anggota sekaligus.

bash

# jangkauan nilai pada indeks
mysql -u root perpustakaan_explain -e "EXPLAIN SELECT status FROM peminjaman WHERE anggota_id BETWEEN 42 AND 44 \G"

text Keluaran

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: range
possible_keys: idx_anggota
          key: idx_anggota
      key_len: 4
          ref: NULL
         rows: 120
     filtered: 100.00
        Extra: Using index condition

Perkiraan 120 baris cocok dengan tiga kelompok berisi 40 baris. Keterangan Using index condition pada Extra berarti kondisi jangkauan dievaluasi pada lapisan indeks sebelum baris dibaca. Nilai index berarti seluruh urutan sebuah indeks dipindai tanpa menyentuh tabel, dan biasanya muncul pada query agregasi seperti penghitungan baris.

bash

# pemindaian seluruh isi indeks
mysql -u root perpustakaan_explain -e "EXPLAIN SELECT COUNT(*) FROM peminjaman \G"

text Keluaran

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_anggota
      key_len: 4
          ref: NULL
         rows: 100000
     filtered: 100.00
        Extra: Using index

Nilai terbawah adalah ALL, yaitu pemindaian seluruh tabel. Query dengan syarat pada kolom status yang tidak terindeks jatuh ke golongan ini.

bash

# pemindaian seluruh tabel
mysql -u root perpustakaan_explain -e "EXPLAIN SELECT peminjaman_id FROM peminjaman WHERE status = 'terlambat' \G"

text Keluaran

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100000
     filtered: 10.00
        Extra: Using where

Keluaran terakhir ini adalah sinyal yang biasanya dicari pada penalaan, yaitu key: NULL dengan 100000 baris diperiksa untuk sebuah syarat sederhana. Tetap demikian, ALL bukan berarti selalu salah, karena query yang memang mengambil mayoritas baris justru wajar memindai seluruh tabel.

eq_ref pada join#

Untuk query dengan beberapa tabel, EXPLAIN menampilkan satu baris rencana per tabel, dan keluaran berikut menampakkan bentuknya. Query ini menggabungkan lima baris peminjaman dengan nama anggotanya.

bash

# rencana join dua tabel
mysql -u root perpustakaan_explain -e "EXPLAIN SELECT p.peminjaman_id, a.nama FROM peminjaman p JOIN anggota a ON a.anggota_id = p.anggota_id WHERE p.peminjaman_id <= 5 \G"

text Keluaran

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: range
possible_keys: PRIMARY,idx_anggota
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: perpustakaan_explain.p.anggota_id
         rows: 1
     filtered: 100.00
        Extra: NULL

Baris pertama adalah tabel penggerak p yang dibaca dengan range sebanyak lima baris melalui primary key. Baris kedua adalah tabel a yang dibaca dengan eq_ref, yaitu satu pencarian primary key untuk setiap baris penggerak, dan kolom ref menunjukkan sumber perbandingannya, yaitu kolom anggota_id milik p. Bentuk eq_ref adalah rencana join yang paling efisien untuk pencarian unik, sedangkan ref pada tabel kedua berarti pencarian non-unik yang bisa mengembalikan lebih dari satu baris.

Kolom rows dan keterangan Extra#

Kolom rows adalah perkiraan, tetapi ketepatannya berbeda antar jenis rencana. Untuk pencarian kesetaraan, optimizer menakar langsung di dalam indeks, sehingga angka 40 pada keluaran bertipe ref adalah jumlah yang tepat. Untuk ALL dan index, angka 100000 berasal dari statistik tabel yang dihitung pada saat ANALYZE TABLE dijalankan, sehingga angkanya makin mendekati kenyataan apabila statistiknya segar. Kolom Extra melengkapi rencana dengan keterangan berikut, yang semuanya sudah muncul pada keluaran-keluaran di atas.

Nilai `Extra`	Makna
`NULL`	tidak ada keterangan tambahan
`Using where`	kondisi disaring setelah baris dibaca
`Using index condition`	kondisi dievaluasi pada lapisan indeks sebelum baris dibaca
`Using index`	jawaban dirakit dari isi indeks saja tanpa membaca tabel
`Using filesort`	pengurutan tambahan dilakukan di luar urutan indeks

Nilai Using filesort paling mudah dibangkitkan dengan meminta urutan kolom yang tidak terindeks. Query berikut mengurutkan peminjaman anggota 42 menurut tanggal, sedangkan idx_anggota tidak memuat kolom tanggal.

bash

# pengurutan tambahan pada kolom yang tidak terindeks
mysql -u root perpustakaan_explain -e "EXPLAIN SELECT status FROM peminjaman WHERE anggota_id = 42 ORDER BY tanggal_pinjam \G"

text Keluaran

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: ref
possible_keys: idx_anggota
          key: idx_anggota
      key_len: 4
          ref: const
         rows: 40
     filtered: 100.00
        Extra: Using filesort

Kesalahan umum#

Kesalahan pertama adalah membaca rows sebagai jumlah baris yang pasti. Angka itu adalah perkiraan dari statistik, sehingga angka 100000 dapat bergeser beberapa ribu apabila statistiknya basi, dan ANALYZE TABLE menjaganya tetap segar. Kesalahan kedua adalah berhenti pada possible_keys. Kolom itu hanya menyebut kandidat, sedangkan keputusannya ada pada key, sehingga indeks yang tercantum sebagai kandidat belum tentu dipakai. Kesalahan ketiga adalah menganggap type: ALL selalu salah. Apabila query memang mengambil mayoritas baris, pemindaian tabel justru lebih murah daripada perjalanan indeks yang diikuti lompatan ke setiap baris, sehingga penilaian atas ALL selalu bergantung pada berapa baris yang benar-benar dibutuhkan.

Ringkasan#

EXPLAIN menampilkan rencana eksekusi tanpa menjalankan query, dengan satu baris keluaran untuk setiap tabel yang dibaca.
Kolom type menggolongkan cara pembacaan tabel, yaitu const, eq_ref, ref, range, index, dan ALL, dari yang paling murah ke yang paling mahal.
Kolom key menunjukkan indeks yang dipilih, key_len menunjukkan panjang kunci yang benar-benar dipakai, dan possible_keys hanya berupa kandidat.
Kolom rows adalah perkiraan baris yang diperiksa, tepat untuk pencarian kesetaraan dan bersumber dari statistik untuk pemindaian.

Referensi#

EXPLAIN Output Format — MySQL 8.0 Reference Manual — Daftar lengkap kolom keluaran EXPLAIN, nilai type, dan nilai Extra.
EXPLAIN Statement — MySQL 8.0 Reference Manual — Cara memakai EXPLAIN dan perbedaannya dengan EXPLAIN ANALYZE.