Mengukur Performa Query

Kita sudah bisa memperbaiki query; pertanyaan terakhir section ini adalah bagaimana membuktikan perbaikannya. Pengukuran performa penuh jebakan: run pertama membaca dari disk, run kedua menyentuh cache hangat — dan selisihnya bisa lebih besar daripada perbaikan yang kita klaim. Jam dinding aplikasi berisi waktu jaringan, rendering, dan mungkin antrean koneksi, bukan murni pekerjaan MySQL. Chapter ini merakit kotak alat ukur yang jujur: penghitung internal Handler yang menghitung baris sungguhan yang disentuh engine, EXPLAIN ANALYZE untuk waktu aktual per langkah, BENCHMARK() untuk mengadu ekspresi murni, dan performance_schema untuk melihat riwayat statement di level server — masing-masing dengan kegunaan dan batasnya.

Menyiapkan Tabel Latihan #

Tabel pelanggan sepuluh baris dengan satu index — cukup untuk membandingkan full scan lawan index lookup di atas data yang identik:

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 INDEX idx_kota ON pelanggan(kota);

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)
Records: 0  Duplicates: 0  Warnings: 0

Mengapa Jam Dinding Satu Run Menipu #

Pengukuran paling mudah — jalankan query sekali, lihat (0.34 sec) di klien — adalah yang paling mudah menyesatkan. Run pertama query membaca halaman data dari disk; run kedua menemukan halaman yang sama di buffer pool yang masih hangat, dan “query jadi 40 kali lebih cepat” padahal tidak ada yang berubah. Sebaliknya, run yang kebetulan berbagi server dengan pekerjaan lain bisa tampak lebih lambat tanpa salah satu baris kode berubah. Aturan minimumnya dua: jalankan beberapa kali dan buang run pertama (pemanasan), serta ukur di kondisi yang setara — cache yang sama, data yang sama, beban yang sama — ketika membandingkan dua versi query. Level di bawah jam dinding jauh lebih jujur: hitung berapa baris yang disentuh engine, bukan berapa detik berlalu.

SHOW PROFILE: Alat Lama yang Pensiun #

Dulu ada SHOW PROFILE dan SHOW PROFILES — profil per-fase (opening tables, executing, sending data) untuk statement terakhir di sesi. Perintah ini berstatus deprecated sejak MySQL 5.6 dan isinya dipindahkan ke performance_schema; fungsinya digantikan events statement yang jauh lebih kaya. Kode warisan yang masih memanggil SHOW PROFILE akan terus bekerja untuk beberapa versi ke depan, tetapi pengetahuan yang layak dibawa maju adalah penggantinya.

performance_schema: Riwayat Statement di Server #

Untuk melihat apa yang benar-benar dijalankan server beserta durasinya, performance_schema merekam event statement ke tabel-tabel khusus. Alat utamanya events_statements_history_long — pernyataan terakhir sesi server beserta durasi total dan rincian per-tahap:

Kolom	Isi
`THREAD_ID`	Koneksi mana yang menjalankan statement
`EVENT_NAME`	Jenis statement (misal `statement/sql/select`)
`SQL_TEXT`	Teks statement aslinya
`TIMER_WAIT`	Total durasi, dalam pikodetik (1 detik = 10¹²)
`TIMER_SUM` / `LOCK_TIME`	Rincian tahapan lock vs eksekusi
`ROWS_EXAMINED`, `ROWS_SENT`	Baris diperiksa vs baris dikirim — `Handler` dalam bentuk ringkas

Membaca selisih ROWS_EXAMINED lawan ROWS_SENT adalah diagnosis instan: query yang memeriksa sejuta baris untuk mengirim sepuluh baris pasti punya cerita (index hilang, kondisi lemah). Server latihan kita mematikan performance_schema demi jejak memori kecil, jadi kita periksa statusnya secara jujur alih-alih berpura-pura menjalankan query-nya:

sql
SHOW VARIABLES LIKE 'performance_schema';

Output yang Dihasilkan:

text
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

Nilai OFF menegaskan kenapa tabel events_statements_history_long tidak bisa didemokan di sini — di server produksi nilai ini biasanya ON bawaan, dan tabelnya tinggal ditanyakan. Pengantar konfigurasinya (cara mengaktifkan, menyetel konsumen event) menunggu kita di section administrasi.

EXPLAIN ANALYZE: Waktu Aktual per Langkah #

Kita sudah membedah EXPLAIN ANALYZE di chapter khusus; di kotak alat pengukuran, perannya adalah pengukuran langkah demi langkah dengan waktu aktual — jembatan antara plan (teori) dan Handler (praktik):

sql
-- @check
EXPLAIN ANALYZE SELECT * FROM pelanggan WHERE nama = 'Budi Santoso';

Output yang Dihasilkan:

text
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------+
| -> Filter: (pelanggan.nama = 'Budi Santoso')  (cost=1.25 rows=1) (actual time=0.0248..0.0319 rows=1 loops=1)        |
|     -> Table scan on pelanggan  (cost=1.25 rows=10) (actual time=0.0222..0.0275 rows=10 loops=1)                     |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Pohon itu menunjukkan pengukuran nyata: berapa milidetik rata-rata tiap langkah, berapa baris aktual per loop. Ingat batasannya dari chapter tersebut — angka actual time bervariasi antar-run, jadi bandingkan pola besar (baris, loop, proporsi antar langkah), bukan desimal; dan query dijalankan sungguhan, jadi jangan asal tempel ANALYZE di query produksi yang sedang sakit parah.

Handler Counter: Baris yang Sungguhan Disentuh #

Penghitung Handler_* di SHOW STATUS dicatat oleh storage engine: setiap pembacaan baris lewat index, lewat sapuan, atau lewat tabel sementara menambah satu angka. Dengan FLUSH STATUS mereset penghitung sesi, kita dapat mengukur volume kerja sebuah query secara pasti — tidak terpengaruh cache, beban, atau keberuntungan. Kita adu full scan lawan index lookup atas pertanyaan yang sama:

sql
FLUSH STATUS;
SELECT * FROM pelanggan WHERE nama = 'Budi Santoso';
SHOW STATUS LIKE 'Handler_read%';

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+----+--------------+---------+------------------+----------------+
| id | nama         | kota    | email            | tanggal_daftar |
+----+--------------+---------+------------------+----------------+
|  1 | Budi Santoso | Jakarta | [email protected] | 2026-01-05     |
+----+--------------+---------+------------------+----------------+
1 row in set (0.00 sec)

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 11    |
+-----------------------+-------+
7 rows in set (0.00 sec)

sql
FLUSH STATUS;
SELECT * FROM pelanggan WHERE id = 1;
SHOW STATUS LIKE 'Handler_read%';

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+----+--------------+---------+------------------+----------------+
| id | nama         | kota    | email            | tanggal_daftar |
+----+--------------+---------+------------------+----------------+
|  1 | Budi Santoso | Jakarta | [email protected] | 2026-01-05     |
+----+--------------+---------+------------------+----------------+
1 row in set (0.00 sec)

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

Query pertama menyentuh Handler_read_first = 1 dan Handler_read_rnd_next = 11 — satu baris pembuka sapuan ditambah sepuluh baris tabel (kesembilan lain plus satu penanda akhir): full scan yang jujur tercatat, kolom nama memang tanpa index. Query kedua mencatat Handler_read_key = 1 dan seluruh penghitung lain nol — satu lompatan lewat PRIMARY, selesai. Angka ini deterministik untuk data yang sama: run pertama dan run keseratus menghasilkan penghitung yang persis sama, sehingga selisih sebelum-sesudah optimasi bisa dipercaya sepenuhnya — inilah alat favorit untuk membuktikan perbaikan (kita juga memakainya di chapter paginasi).

BENCHMARK(): Mengadu Ekspresi Murni #

Ketika yang ingin dibandingkan adalah ekspresi — dua fungsi, dua formula, dua cast — BENCHMARK(count, ekspresi) mengeksekusi ekspresi itu berulang kali dan mengembalikan 0:

sql
SELECT BENCHMARK(1000000, MD5('belajar'));
SELECT BENCHMARK(1000000, SHA2('belajar', 256));

Output yang Dihasilkan:

text
+------------------------------------+
| BENCHMARK(1000000, MD5('belajar')) |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

+------------------------------------------+
| BENCHMARK(1000000, SHA2('belajar', 256)) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

Nilai hasilnya selalu nol — yang diukur bukan hasil, melainkan durasi di baris status N rows in set (x.xx sec) pada klien. Satu juta kali MD5() berjalan dalam hitungan per seribu detik; SHA2() yang lebih berat memakan porsi lebih besar pada jumlah eksekusi yang sama. BENCHMARK() deterministik dari sisi hasil (nol) dan berguna untuk mikro-bandingkat ekspresi; batasnya jelas: ini mengukur CPU server untuk ekspresi murni, bukan pengaksesan data — jangan menyimpulkan performa query dari BENCHMARK() semata.

Disiplin Membandingkan: Sebelum dan Sesudah #

Kotak alat lengkap; yang tersisa disiplinnya. Bandingkan sebelum-sesudah di atas data serupa (ukuran dan sebaran setara — tabel 15 baris tidak membuktikan apa pun untuk produksi 15 juta), dengan metrik yang sama (jangan mencampur baris Handler dengan milidetik EXPLAIN ANALYZE dalam satu kalimat kesimpulan), dan unit yang konsisten — tabel berikut merangkum metrik dan unit tiap alat:

Alat	Yang diukur	Unit	Sifat
`Handler` counter	Baris disentuh engine	baris	Deterministik
`EXPLAIN`	Rencana & estimasi	rows, cost	Deterministik (estimasi)
`EXPLAIN ANALYZE`	Waktu aktual per langkah	milidetik	Bervariasi antar-run
`BENCHMARK()`	Ekspresi murni	eksekusi + detik klien	Waktu bervariasi
`performance_schema`	Riwayat statement server	pikodetik, baris	Perlu server ber-instrumentasi

Urutan kerja yang sehat untuk satu optimasi: catat plan dan Handler versi lama → ubah satu hal (index, bentuk query, join order) → ulangi pengukuran yang sama → laporkan keduanya berdampingan. Satu perubahan per pengukuran; perubahan ganda membuat kita tak tahu kontribusi mana yang berharga.

Membersihkan Database Latihan #

sql
DROP DATABASE belajar_opt;

Output yang Dihasilkan:

text

Query OK, 1 row affected (0.00 sec)

Referensi #

Section query optimization ditutup dengan kemampuan penuh: membaca plan sampai sel terakhir, mengenali access type dan penyakit join-index-paginasi, menjalankan diagnosis sistematis, dan mengukur hasilnya dengan alat yang jujur. Kemampuan berikutnya di perjalanan MySQL kita berpindah dari sisi query ke sisi pengguna: mengelola siapa yang boleh masuk, siapa yang boleh membaca, dan siapa yang boleh mengubah — wilayah user management dan keamanan.