Strategi optimasi query dan analisis execution plan

Tujuan Pembelajaran

Setelah mempelajari modul ini, mahasiswa diharapkan mampu:

• Menganalisis performa query menggunakan EXPLAIN.
• Menyusun strategi optimasi query.
• Menggunakan indeks dan teknik penulisan query untuk mempercepat eksekusi.

Apa Itu Optimasi Query?

Optimasi Query adalah proses penyusunan dan perbaikan perintah SQL agar berjalan lebih efisien, baik dari sisi waktu eksekusi maupun penggunaan sumber daya.

Analisis Query dengan EXPLAIN

EXPLAIN menunjukkan cara MySQL merencanakan dan mengeksekusi query.

EXPLAIN SELECT * FROM mahasiswa WHERE nama = 'Andi';

Contoh Output:

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	mahasiswa	ref	nama	nama	1	Using index

> Gunakan `EXPLAIN ANALYZE` (MySQL 8+) untuk analisis yang lebih rinci.

Strategi Optimasi Query

Gunakan SELECT Kolom Spesifik, Bukan SELECT *:

--- Kurang efisien
SELECT * FROM mahasiswa;

--- Lebih efisien
SELECT nim, nama FROM mahasiswa;

Gunakan Indeks pada Kolom WHERE atau JOIN

--- Menambahkan indeks untuk kolom yang sering dicari
CREATE INDEX idx_nama ON mahasiswa(nama);

Gunakan LIMIT untuk Batasi Data

SELECT * FROM mahasiswa ORDER BY nama LIMIT 10;

Hindari Fungsi di Kolom WHERE

--- Kurang efisien karena fungsi dijalankan per baris
SELECT * FROM mahasiswa WHERE YEAR(tanggal_lahir) = 2000;

--- Lebih efisien
SELECT * FROM mahasiswa WHERE tanggal_lahir BETWEEN '2000-01-01' AND '2000-12-31';

Gunakan JOIN daripada Subquery jika memungkinkan

--- Subquery (kurang efisien jika tidak ada indeks)
SELECT nama FROM mahasiswa WHERE nim IN (
  SELECT nim FROM pengambilan WHERE kode_mk = 'MK001'
);

--- JOIN (lebih efisien jika ada index di pengambilan.nim)
SELECT m.nama
FROM mahasiswa m
JOIN pengambilan p ON m.nim = p.nim
WHERE p.kode_mk = 'MK001';

Studi Kasus: Bandingkan Kinerja Query

Query 1 (Tanpa Indeks)

SELECT * FROM mahasiswa WHERE nama = 'Andi';
--- EXPLAIN menunjukkan "type: ALL" = full table scan

Query 2 (Dengan Indeks)

CREATE INDEX idx_nama ON mahasiswa(nama);

SELECT * FROM mahasiswa WHERE nama = 'Andi';
--- EXPLAIN menunjukkan "type: ref", "key: idx_nama"

Tools Tambahan untuk Optimasi di Ubuntu

• EXPLAIN dan EXPLAIN ANALYZE
• Performance Schema (aktifkan di `/etc/mysql/mysql.conf.d/mysqld.cnf`)
• MySQL Tuner (command-line tool)

sudo apt install mysqltuner
sudo mysqltuner

Kesimpulan

• Gunakan EXPLAIN untuk melihat bagaimana query dijalankan oleh MySQL.
• Gunakan indeks pada kolom yang sering digunakan dalam WHERE, JOIN, atau ORDER BY.
• Hindari `SELECT *`, fungsi di kolom WHERE, dan subquery tidak efisien.
• Kombinasi indeks + penulisan query yang benar bisa meningkatkan performa hingga 10x lipat atau lebih.

Pranala Menarik

• Database: Kuliah