Membaca Execution Plan — EXPLAIN QUERY PLAN

Dataset untuk artikel ini — perf_sales (50.000 baris penjualan)

Ketika kamu ingin tahu apakah sebuah kueri cepat atau lambat, hal pertama yang dilihat adalah execution plan (rencana eksekusi kueri).

Diberikan sebuah pernyataan SQL, database memutuskan secara internal "tabel mana yang dibaca dalam urutan apa, dan bagaimana".

Alat yang mencetak prosedur itu sebagai teks adalah EXPLAIN QUERY PLAN.

Tambahkan keyword ini di depan kueri dan kamu akan melihat langkah-langkah eksekusi alih-alih hasil sebenarnya.

Sebelum menyelam ke latihan, periksa definisi kolom dan sampel data dari tabel perf_sales yang dipakai di sepanjang artikel ini. Generasi data butuh waktu, jadi eksekusi pertama mungkin terhenti beberapa detik.

① Pakai PRAGMA table_info(perf_sales); untuk melihat nama kolom, tipe, dan primary key.

② Pakai SELECT * FROM perf_sales LIMIT 5; untuk mengintip 5 baris pertama.

③ Pakai SELECT COUNT(*) FROM perf_sales; untuk mengonfirmasi bahwa ada 50.000 baris.

SCAN dan SEARCH — full scan dan index scan

Dua kata pertama yang harus dipelajari di sebuah execution plan adalah SCAN dan SEARCH.

SCAN perf_sales berarti membaca setiap baris dari atas ke bawah tabel (full scan).

Ketika kamu memfilter pada kolom yang tidak punya indeks, database tidak tahu baris mana yang cocok, jadi ia memeriksa semua 50.000 baris satu per satu.

Sementara itu SEARCH perf_sales USING INDEX index_name (column=?) berarti mengikuti indeks untuk langsung mendarat di baris yang cocok (index scan).

Buat indeks dengan CREATE INDEX dan filter pada kolom itu berhenti memindai setiap baris — plan berubah dari SCAN menjadi SEARCH ... USING INDEX.

-- Filter berdasarkan region: tanpa indeks, jadi membaca setiap baris
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales

-- Bangun indeks pada region, lalu lihat plan yang sama
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)

Bayangkan skenarionya: "menghitung penjualan untuk sales rep tertentu lambat." Kamu akan membandingkan execution plan sebelum dan sesudah menambahkan indeks. Jalankan pembuatan indeks dan pemeriksaan plan bersama dalam satu eksekusi. (Jalankan dengan benar untuk memunculkan penjelasan.)

① Pertama, tambahkan EXPLAIN QUERY PLAN di depan kueri yang menghitung baris di mana emp_id sama dengan 7 dan periksa plan-nya. Tanpa indeks, kamu akan melihat SCAN.

② Lalu buang indeks yang ada dengan DROP INDEX IF EXISTS, bangun indeks pada emp_id dengan CREATE INDEX, dan jalankan ulang EXPLAIN QUERY PLAN yang sama. Konfirmasi ia berubah menjadi SEARCH ... USING INDEX.

USE TEMP B-TREE — area sementara untuk sorting

USE TEMP B-TREE FOR ORDER BY (B-tree sementara) berarti kolom ORDER BY tidak punya indeks, jadi database membangun area kerja sementara untuk mengurutkan hasil.

Semua 50.000 baris masuk ke area sementara itu untuk diurutkan ulang, jadi biayanya tumbuh dengan jumlah baris.

Kalau ada indeks yang bisa dipakai untuk sorting, baris ini hilang dari plan dan urutan indeks yang sudah ada dipakai apa adanya.

-- Top 10 berdasarkan amount ascending: tanpa indeks, jadi area temp diperlukan untuk sorting
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SCAN perf_sales
--> USE TEMP B-TREE FOR ORDER BY

-- Bangun indeks pada amount dan sorting bisa memakai ulang urutannya langsung
DROP INDEX IF EXISTS ix_amount;
CREATE INDEX ix_amount ON perf_sales(amount);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SEARCH perf_sales USING INDEX ix_amount
-- USE TEMP B-TREE hilang dari plan

Lihat execution plan untuk "top 10 penjualan berdasarkan amount ascending" ketika tidak ada indeks. Tiga latihan yang mengikuti berjalan berurutan supaya kamu bisa membandingkan plan sebelum dan sesudah indeks.

① Di konsol pertama ini, tambahkan EXPLAIN QUERY PLAN di depan kueri yang mengurutkan perf_sales berdasarkan amount ascending dan menarik 10 baris pertama (sale_id, amount).

Konfirmasi bahwa output berisi dua baris: SCAN perf_sales dan USE TEMP B-TREE FOR ORDER BY yang berkaitan dengan sorting. Tanpa indeks pada kolom sort amount, database harus memuat 50.000 baris ke area sementara dan mengurutkannya ulang.

② Di konsol kedua ini, jalankan DROP INDEX IF EXISTS ix_amount; untuk membersihkan yang sebelumnya, lalu buat indeks bernama ix_amount pada kolom amount milik perf_sales.

Pembuatan indeks adalah biaya sekali pakai, jadi tidak masuk dalam pembandingan plan murni. Konsol ketiga menjalankan ulang pemeriksaan plan untuk kueri yang sama.

③ Di konsol ketiga ini, jalankan ulang `EXPLAIN QUERY PLAN` yang sama dengan ①.

Konfirmasi bahwa baris USE TEMP B-TREE FOR ORDER BY hilang dari plan, digantikan oleh sesuatu seperti SEARCH perf_sales USING INDEX ix_amount. Urutan indeks dipakai ulang langsung, jadi sort di area temp tidak lagi diperlukan.

USING COVERING INDEX — indeks yang tidak menyentuh tabel

USING COVERING INDEX (Index-Only Scan, juga disebut covering index dalam bahasa Inggris) berarti setiap kolom yang dibutuhkan kueri ada di dalam indeks, jadi database bisa membangun hasil dari indeks saja tanpa membaca tabelnya sendiri.

Indeks adalah salinan kecil dari hanya kolom yang kamu tarik dari tabel, jadi melewati pembacaan tabel adalah yang membuatnya lebih cepat.

Ini satu langkah di luar SEARCH ... USING INDEX — ketika plan menampilkan USING COVERING INDEX, kamu mendapat pembacaan paling efisien.

-- Indeks hanya pada emp_id -> temukan baris lewat indeks, lalu baca amount dari tabel
DROP INDEX IF EXISTS ix_emp;
CREATE INDEX ix_emp ON perf_sales(emp_id);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING INDEX ix_emp (emp_id=?)
--   (tabel juga dibaca)

-- Indeks komposit pada (emp_id, amount) -> setiap kolom yang diperlukan ada di indeks, tanpa pembacaan tabel
DROP INDEX IF EXISTS ix_emp_amount;
CREATE INDEX ix_emp_amount ON perf_sales(emp_id, amount);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING COVERING INDEX ix_emp_amount (emp_id=?)

Ambil kueri yang "memfilter berdasarkan emp_id dan mengembalikan hanya amount" dan ubah plan-nya menjadi yang selesai di dalam indeks tanpa menyentuh tabel. Jalankan pembuatan indeks dan pemeriksaan plan bersama dalam satu eksekusi.

① Buang indeks yang ada dengan DROP INDEX IF EXISTS, lalu pakai CREATE INDEX untuk membangun indeks komposit pada emp_id dan amount, dengan urutan itu.

② Tambahkan EXPLAIN QUERY PLAN di depan kueri yang memfilter berdasarkan emp_id = 7 dan memilih hanya emp_id dan amount, dan periksa plan-nya. Baca bahwa plan selesai di dalam indeks saja, karena setiap kolom yang diperlukan ada di indeks.

Plan subquery — CORRELATED dan MATERIALIZE

Kueri dengan subquery mendapat kosakata khusus mereka sendiri di plan.

CORRELATED SCALAR SUBQUERY berarti nilai subquery bergantung pada baris luar saat ini, jadi ia dieksekusi ulang sekali per baris luar.

Dengan 50.000 baris luar, subquery bisa berjalan 50.000 kali — ketika kata ini muncul di plan, ia adalah kandidat untuk ditulis ulang.

Sementara itu MATERIALIZE berarti hasil dari subquery atau CTE dihitung sekali, disimpan di tabel sementara, dan dipakai ulang.

Ini optimasi yang membayar biaya komputasi sekali untuk hasil antara yang kamu rujuk beberapa kali.

Mana dari dua kata ini yang muncul memberi tahu kamu apakah subquery "dihitung setiap kali" atau "dihitung sekali dan dipakai ulang".

-- Correlated scalar subquery: ambil ulang "max amount untuk region ini" per baris luar
EXPLAIN QUERY PLAN
SELECT s.sale_id, s.amount
FROM perf_sales s
WHERE s.amount = (
  SELECT MAX(amount) FROM perf_sales x WHERE x.region = s.region
)
LIMIT 10;
--> baris CORRELATED SCALAR SUBQUERY muncul

-- Tabel turunan (subquery di klausa FROM) dihitung sekali
EXPLAIN QUERY PLAN
SELECT t.region, t.s FROM (
  SELECT region, SUM(amount) AS s FROM perf_sales GROUP BY region
) t
WHERE t.s > 100000000;
--> baris MATERIALIZE mungkin muncul

Lihat plan untuk "untuk setiap baris, pilih hanya penjualan yang melebihi rata-rata amount rep itu, top 10" dan baca struktur yang menjalankan ulang subquery per baris.

① Tambahkan EXPLAIN QUERY PLAN di depan kueri yang, untuk setiap baris di perf_sales, memakai correlated subquery untuk menghitung "rata-rata amount untuk emp_id yang sama" dan menarik 10 baris di mana amount melebihi itu.

② Baca, di output, baris yang menunjukkan correlated scalar subquery dan SCAN perf_sales untuk kueri luar dan dalam. Catat bahwa bentuk ini berat di tabel besar karena inner subquery dievaluasi sekali per baris luar.

Bagaimana ini dipetakan ke RDBMS lain — MySQL / PostgreSQL / Oracle

Kosakata plan di artikel ini datang dari EXPLAIN QUERY PLAN SQLite, tapi MySQL / PostgreSQL / Oracle mengangkat konsep yang sama di bawah nama berbeda.

Mulai dengan memeriksa bagaimana setiap database menghasilkan execution plan, lalu bandingkan cheat sheet terminologi plan — kamu akan bisa membaca plan di database lain tanpa banyak pembelajaran tambahan.

Cara mencetak execution plan di setiap database

• SQLite: Tambahkan EXPLAIN QUERY PLAN di depan kueri (misalnya EXPLAIN QUERY PLAN SELECT ...;). Ia tidak menjalankan kueri, hanya mencetak plan. EXPLAIN polos menunjukkan bytecode tingkat rendah, jadi untuk pembacaan manusia kamu ingin EXPLAIN QUERY PLAN
• MySQL: Tambahkan EXPLAIN di depan kueri (misalnya EXPLAIN SELECT ...;). MySQL 8.0+ juga punya EXPLAIN ANALYZE SELECT ...; untuk run time aktual dan jumlah baris aktual. Untuk output JSON, pakai EXPLAIN FORMAT=JSON SELECT ...;
• PostgreSQL: Tambahkan EXPLAIN di depan kueri (misalnya EXPLAIN SELECT ...;). Itu plan estimasi saja. Untuk aktual, pakai EXPLAIN ANALYZE SELECT ...;, dan untuk info buffer I/O detail, pakai EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
• Oracle: Pola dua langkah — EXPLAIN PLAN FOR <query>; menyimpan plan ke tabel internal, dan SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); mencetaknya. Untuk tampilan cepat, opsi sesi SET AUTOTRACE ON juga bekerja

Cheat sheet terminologi plan

Kata-katanya berbeda, tapi apa yang dilakukan database secara internal pada dasarnya sama. Tujuan artikel ini adalah bisa membaca "apakah ia membaca setiap baris, apakah ia mempersempit dengan indeks, apakah ia memerlukan area kerja untuk sorting, apakah ia menjalankan subquery berulang-ulang" dari output EXPLAIN setiap database.