Query Optimization #3: Analisis Eksekusi Query dengan EXPLAIN

1. Latar Belakang

Dalam pengelolaan basis data, kecepatan dan efisiensi dalam menjalankan query menjadi faktor penting agar sistem dapat berjalan dengan optimal dan responsif. Salah satu cara untuk memahami serta meningkatkan performa query adalah dengan menganalisis execution plan menggunakan perintah EXPLAIN. Melalui execution plan, kita dapat mengetahui bagaimana database memproses sebuah query, apakah menggunakan index atau justru melakukan full table scan yang berpotensi memperlambat kinerja sistem.

2. Pembahasan

2.1 Execution Plan

Execution plan merupakan rencana langkah-langkah database untuk menjalankan query atau bisa disebut seperti peta jalan. Misalnya kita ingin mencari sebuah buku di perpustakaan. Terdapat 2 cara yang dapat kita lakukan, yaitu:

Cara #1 : Cek satu-satu semua rak buku

Cara #2 : Pakai katalog komputer, langsung ke rak yang tepat

Nah, tugas Execution Plan adalah merencanakan strategi yang dipilih perpustakaan untuk mencari buku.

2.2 Perintah EXPLAIN

Saat kita menulis query SQL, database tidak akan langsung eksekusi. Ia akan melewati berbagai proses terlebih dahulu, dan EXPLAIN merupakan cara kita untuk mengintip otak dari database ketika berpikir. Jadi, kita bisa menggunakan EXPLAIN untuk memahami cara query kita dijalankan dan dapat memberikan detail yang dapat kita gunakan untuk mengoptimalkan suatu query.

- Contoh Query EXPLAIN:

EXPLAIN SELECT * FROM users WHERE email = 'a@gmail.com';

2.3 Identifikasi Bottleneck Query

Bottleneck merupakan bagian query yang memperlambat, dan EXPLAIN disini berperan untuk melihat apakah database melakukan full table scan, berapa banyak baris atau rows yang dibaca, apakah index (key) dipakai atau tidak, dan lain sebagainya seperti yang dapat dilihat pada contoh dibawah.

id: urutan eksekusi query dimana apabila id semakin besar, maka semakin dulu dijalankan
select_type: merupakan jenis query dan query yang ditulis tersebut termasuk pada query simple atau query normal (tak ada subquery, dan lain-lain)
table: merupakan table yang sedang diproses
type: cara database mengakses data (all merupakan proses full table scan yang membaca seluruh tabel users)
possible_keys: merupakan index yang bisa dipakai jika ada
key: adalah index yang benar-benar dipakai
key_len: adalah panjang index yang digunakan (berapa byte index yang benar-benar digunakan optimizer)
ref: kolom atau konstanta yang dibandingkan dengan index (contoh: users.id, orders.user_id)
rows: perkiraan jumlah baris yang dibaca
extra: informasi tambahan optimasi (using where adalah filter setelah membaca data / scan)

2.4 Analogi Sederhana

EXPLAIN = hasil cek kesehatan query
Bottleneck = penyakitnya
Optimasi = obatnya

2.5 Implementasi EXPLAIN Pada Kasus Toko

- Tujuan Praktikum:

Melihat bagaimana DBMS menjalankan query
Mengidentifikasi query lambat
Melihat perubahan execution plan setelah optimasi

A. Kasus

1. Buat database baru dengan nama "toko"

2. Buat tabel "produk"

3. Buat tabel "transaksi"

4. Isi data dummy untuk tabel produk

5. Buat data dummy untuk tabel transaksi yang banyak

6. Isi tabel transaksi

- Hasil data dummy (terdapat 10.000+)

7. Sekarang, kita cek Execution Plannya menggunakan EXPLAIN

8. Bisa dilihat pada kolom type, key (index), dan rows (baris) yang menunjukkan bahwa query "SELECT * FROM transaksi WHERE produk_id = 3" membuat database melakukan full table scan yang tidak optimal. Dalam kata lain, DBMS telah membaca 10.000 baris satu persatu hanya untuk query tersebut.

B. Optimasi dengan Index

1. Tambahkan index pada kolom produk_id

2. Saat kita jalankan EXPLAIN lagi, maka output sudah terlihat berbeda. Dimulai dari kolom possible_keys dan key yang sekarang memiliki nilai index, hingga kolom rows yang menunjukkan bahwa database tidak perlu membaca seluruh baris.