Optimasi Performa MySQL

Database yang lambat membuat aplikasi terasa berat. Artikel ini membahas cara menganalisis query dengan EXPLAIN, merancang index yang efektif, mengatur konfigurasi server, dan menerapkan best practice query tuning.

Pendahuluan

Seiring bertambahnya data, query yang dulu cepat bisa menjadi lambat. Optimasi performa adalah proses berkelanjutan: mengidentifikasi query lambat, menganalisis penyebabnya, lalu memperbaikinya — baik melalui index, rewrite query, maupun konfigurasi server.

Artikel ini membahas:

  • Cara menemukan query lambat.
  • Analisis query dengan EXPLAIN.
  • Strategi indexing yang efektif.
  • Konfigurasi my.cnf yang penting.
  • Best practice penulisan query.

Asumsi: Anda berada di database toko_online dengan data yang cukup banyak (minimal ribuan baris) agar perbedaan performa terasa.


Menemukan Query Lambat

Sebelum mengoptimasi, kita harus tahu query mana yang lambat.

1. Slow Query Log

Aktifkan slow query log untuk mencatat query yang melebihi ambang waktu tertentu.

Via SQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- catat query > 1 detik

Via my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Setelah beberapa saat, analisis log:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Perintah ini menampilkan 10 query yang paling lama total waktunya.

Atau gunakan pt-query-digest (Percona Toolkit) untuk analisis lebih mendalam:

pt-query-digest /var/log/mysql/slow.log

2. Performance Schema

MySQL 8 memiliki Performance Schema yang menyimpan statistik query secara real-time:

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS jumlah_eksekusi,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_detik,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS rata_rata_detik,
  SUM_ROWS_EXAMINED AS baris_diperiksa
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Query ini menampilkan 10 query dengan total waktu eksekusi terbesar.

3. SHOW PROCESSLIST

Melihat query yang sedang berjalan:

SHOW FULL PROCESSLIST;

Atau:

SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

Query dengan time besar dan state seperti Sending data atau Copying to tmp table patut dicurigai.


Analisis Query dengan EXPLAIN

EXPLAIN menunjukkan execution plan — bagaimana MySQL akan mengeksekusi query.

Sintaks

EXPLAIN SELECT ...;

Untuk informasi lebih detail (MySQL 8.0.18+):

EXPLAIN ANALYZE SELECT ...;

EXPLAIN ANALYZE benar-benar menjalankan query dan menampilkan waktu aktual per langkah.

Contoh

EXPLAIN SELECT * FROM pesanan WHERE pelanggan_id = 5;

Output contoh:

+----+-------------+---------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys       | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | pesanan | NULL       | ref  | fk_pesanan_pelanggan| fk_...  | 4       | const |    10|   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------------+---------+---------+-------+------+----------+-------+

Kolom Penting di EXPLAIN

Kolom Arti
type Cara akses tabel — paling penting
possible_keys Index yang mungkin dipakai
key Index yang benar-benar dipakai
key_len Panjang index yang dipakai
rows Perkiraan jumlah baris yang diperiksa
filtered Persentase baris yang lolos filter
Extra Informasi tambahan (misalnya Using filesort, Using temporary)

Nilai type (dari terbaik ke terburuk)

type Keterangan Kualitas
system Tabel hanya 1 baris ⭐⭐⭐⭐⭐
const Pencarian via PRIMARY KEY / UNIQUE ⭐⭐⭐⭐⭐
eq_ref Satu baris per baris tabel lain (JOIN via PK) ⭐⭐⭐⭐⭐
ref Pencarian via index non-unique ⭐⭐⭐⭐
range Pencarian rentang via index ⭐⭐⭐
index Full index scan ⭐⭐
ALL Full table scan ⭐ (buruk untuk tabel besar)

Target: Usahakan type minimal range, idealnya ref atau lebih baik. Jika type = ALL pada tabel besar, itu kandidat utama untuk optimasi.

Nilai Extra yang Perlu Diwaspadai

Extra Arti Tindakan
Using filesort Sorting tanpa index Tambah index pada kolom ORDER BY
Using temporary Membuat tabel sementara Optimasi GROUP BY / DISTINCT
Using where Filter setelah baca Normal, tetapi cek apakah index bisa membantu
Using index Covering index — bagus! Tidak perlu tindakan
Using join buffer JOIN tanpa index Tambah index pada kolom JOIN

Contoh: Query Tanpa Index

EXPLAIN SELECT * FROM pelanggan WHERE email = '[email protected]';

Jika email tidak memiliki index, output akan menunjukkan type = ALL dan rows = jumlah seluruh baris tabel.

Setelah menambah index:

CREATE INDEX idx_email ON pelanggan (email);

Jalankan EXPLAIN lagi — type berubah menjadi ref dan rows turun drastis.

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE
SELECT p.nama, COUNT(ps.id) AS jumlah_pesanan
FROM pelanggan p
LEFT JOIN pesanan ps ON ps.pelanggan_id = p.id
GROUP BY p.id, p.nama;

Output menampilkan waktu aktual per langkah, misalnya:

-> Table scan on p  (cost=... rows=1000) (actual time=0.05..2.10 rows=1000 loops=1)
    -> Index lookup on ps using fk_pesanan_pelanggan (pelanggan_id=p.id)  (actual time=0.01..0.02 rows=3 loops=1000)

Gunakan ini untuk menemukan langkah mana yang paling memakan waktu.


Strategi Indexing yang Efektif

Index adalah alat optimasi paling powerful — tetapi harus digunakan dengan tepat.

1. Index Kolom yang Sering di WHERE, JOIN, ORDER BY

-- Sering: WHERE status = 'selesai'
CREATE INDEX idx_status ON pesanan (status);

-- Sering: JOIN ON pelanggan_id
CREATE INDEX idx_pelanggan ON pesanan (pelanggan_id);

-- Sering: ORDER BY dibuat_pada DESC
CREATE INDEX idx_tanggal ON pesanan (dibuat_pada);

2. Index Komposit: Urutan Kolom Penting

Index komposit (kolom_a, kolom_b) bisa dipakai untuk:

  • Query yang memfilter kolom_a saja. ✅
  • Query yang memfilter kolom_a dan kolom_b. ✅
  • Query yang memfilter kolom_b saja. ❌ (tidak dipakai)
CREATE INDEX idx_status_total ON pesanan (status, total);

Dipakai untuk:

SELECT * FROM pesanan WHERE status = 'selesai';           -- ✅
SELECT * FROM pesanan WHERE status = 'selesai' AND total > 100000;  -- ✅
SELECT * FROM pesanan WHERE total > 100000;               -- ❌

Aturan: Kolom dengan cardinality tinggi (banyak nilai unik) sebaiknya di depan, kecuali ada alasan spesifik.

3. Covering Index

Covering index adalah index yang mencakup semua kolom yang dibutuhkan query, sehingga MySQL tidak perlu membaca tabel sama sekali.

-- Query
SELECT status, total FROM pesanan WHERE status = 'selesai';

-- Covering index
CREATE INDEX idx_covering ON pesanan (status, total);

EXPLAIN akan menunjukkan Extra: Using index — tanda covering index dipakai.

4. Index untuk ORDER BY

Jika query sering menggunakan ORDER BY pada kolom tertentu, index bisa menghindari filesort:

-- Query
SELECT * FROM produk ORDER BY harga DESC LIMIT 10;

-- Index
CREATE INDEX idx_harga ON produk (harga);

EXPLAIN akan menghilangkan Using filesort.

5. Index Parsial dengan Prefix

Untuk kolom VARCHAR panjang, index prefix menghemat ruang:

CREATE INDEX idx_nama_prefix ON pelanggan (nama(20));

Index hanya menyimpan 20 karakter pertama. Cocok untuk pencarian LIKE 'Budi%', tetapi tidak untuk pencarian exact pada string panjang.

6. Index untuk LIKE

Index hanya dipakai jika wildcard ada di akhir:

-- ✅ Index dipakai
SELECT * FROM produk WHERE nama LIKE 'Key%';

-- ❌ Index tidak dipakai (full scan)
SELECT * FROM produk WHERE nama LIKE '%board';

-- ❌ Index tidak dipakai
SELECT * FROM produk WHERE nama LIKE '%ard%';

Untuk pencarian teks penuh, gunakan FULLTEXT index:

CREATE FULLTEXT INDEX idx_deskripsi ON produk (deskripsi);
SELECT * FROM produk WHERE MATCH(deskripsi) AGAINST('gaming' IN NATURAL LANGUAGE MODE);

7. Hapus Index yang Tidak Dipakai

Index berlebih memperlambat INSERT/UPDATE/DELETE. Cek index yang jarang dipakai:

SELECT
  object_schema,
  object_name,
  index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'toko_online';

Index dengan count_star = 0 tidak pernah dipakai — kandidat untuk dihapus.

8. Index dan Cardinality

Cek cardinality (jumlah nilai unik) index:

SHOW INDEX FROM pesanan;

Kolom Cardinality menunjukkan perkiraan nilai unik. Index pada kolom dengan cardinality sangat rendah (misalnya status hanya 3 nilai) kurang efektif untuk query yang memilih sebagian besar baris — optimizer mungkin memilih full scan.


Optimasi Query

Selain index, cara menulis query sangat memengaruhi performa.

1. Hindari SELECT *

-- ❌ Mengambil semua kolom, termasuk yang tidak perlu
SELECT * FROM pelanggan;

-- ✅ Ambil kolom yang dibutuhkan saja
SELECT id, nama, email FROM pelanggan;

SELECT * menghalangi covering index dan mengirim data berlebih.

2. Hindari Fungsi pada Kolom di WHERE

-- ❌ Index pada dibuat_pada tidak dipakai
SELECT * FROM pesanan WHERE YEAR(dibuat_pada) = 2022;

-- ✅ Index dipakai
SELECT * FROM pesanan
WHERE dibuat_pada >= '2022-01-01' AND dibuat_pada < '2023-01-01';

3. Hindari LIKE '%...'

Seperti dibahas, wildcard di awal menghalangi index. Gunakan FULLTEXT atau pertimbangkan strategi lain.

4. Gunakan EXISTS daripada IN untuk Subquery Besar

-- ❌ Bisa lambat jika subquery besar
SELECT * FROM pelanggan
WHERE id IN (SELECT pelanggan_id FROM pesanan WHERE total > 1000000);

-- ✅ Lebih cepat
SELECT * FROM pelanggan p
WHERE EXISTS (
  SELECT 1 FROM pesanan ps
  WHERE ps.pelanggan_id = p.id AND ps.total > 1000000
);

5. Hindari OR yang Menghalangi Index

-- ❌ OR pada kolom berbeda bisa menghalangi index
SELECT * FROM produk WHERE harga > 1000000 OR stok = 0;

-- ✅ Pisahkan dengan UNION
SELECT * FROM produk WHERE harga > 1000000
UNION
SELECT * FROM produk WHERE stok = 0;

6. Batasi Hasil dengan LIMIT

SELECT * FROM pesanan ORDER BY dibuat_pada DESC LIMIT 20;

Jangan mengambil seluruh tabel jika hanya butuh sebagian.

7. Optimasi Pagination

LIMIT dengan OFFSET besar menjadi lambat karena MySQL tetap memindai baris yang dilewati:

-- ❌ Lambat untuk offset besar
SELECT * FROM pesanan ORDER BY id LIMIT 10 OFFSET 100000;

-- ✅ Keyset pagination (lebih cepat)
SELECT * FROM pesanan WHERE id > 100000 ORDER BY id LIMIT 10;

Keyset pagination menggunakan nilai terakhir dari halaman sebelumnya.

8. Gunakan UNION ALL jika Duplikat Tidak Masalah

-- UNION menghilangkan duplikat (perlu sorting)
SELECT ... UNION SELECT ...

-- UNION ALL lebih cepat
SELECT ... UNION ALL SELECT ...

9. Hindari ORDER BY RAND()

-- ❌ Sangat lambat untuk tabel besar
SELECT * FROM produk ORDER BY RAND() LIMIT 10;

-- ✅ Alternatif: ambil berdasarkan ID acak
SELECT * FROM produk WHERE id >= (
  SELECT FLOOR(RAND() * (SELECT MAX(id) FROM produk))
) LIMIT 10;

10. Analisis dan Optimasi Tabel

ANALYZE TABLE pesanan;   -- perbarui statistik untuk optimizer
OPTIMIZE TABLE pesanan;  -- defragmentasi tabel (hati-hati: mengunci tabel)

ANALYZE TABLE membantu optimizer memilih index yang tepat.


Konfigurasi my.cnf

Konfigurasi server yang tepat bisa meningkatkan performa secara signifikan. Berikut parameter penting:

innodb_buffer_pool_size

Parameter paling penting untuk performa InnoDB. Menentukan ukuran cache data dan index di memori.

[mysqld]
innodb_buffer_pool_size = 4G

Rekomendasi: 50–70% dari RAM server khusus MySQL. Untuk server 8 GB, gunakan 4–5 GB.

Cek ukuran saat ini:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Cek hit ratio (idealnya > 99%):

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

Rasio = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests).

innodb_log_file_size

Ukuran redo log. Lebih besar = lebih sedikit checkpoint, tetapi recovery lebih lama.

innodb_log_file_size = 512M

Rekomendasi: 256 MB–1 GB untuk workload OLTP.

innodb_flush_log_at_trx_commit

Mengontrol durability vs performa:

Nilai Perilaku Durability
1 (default) Flush ke disk setiap commit Paling aman
2 Flush ke OS cache setiap commit, disk setiap detik Kompromi
0 Flush setiap detik Paling cepat, risiko kehilangan data
innodb_flush_log_at_trx_commit = 1  # untuk data kritis

max_connections

Jumlah koneksi simultan maksimum.

max_connections = 200

Cek penggunaan:

SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

Jika Max_used_connections mendekati max_connections, naikkan — tetapi ingat setiap koneksi memakan memori.

query_cache_size (MySQL 5.7)

⚠️ Catatan: Query cache dihapus di MySQL 8.0 karena sering menjadi bottleneck. Jika Anda masih di MySQL 5.7, pertimbangkan untuk menonaktifkannya (query_cache_type = 0) dan mengandalkan buffer pool.

tmp_table_size dan max_heap_table_size

Ukuran maksimum tabel sementara di memori. Jika terlampaui, MySQL membuat tabel sementara di disk (lambat).

tmp_table_size = 64M
max_heap_table_size = 64M

Keduanya sebaiknya diset sama.

sort_buffer_size dan join_buffer_size

Buffer untuk sorting dan JOIN tanpa index. Jangan diset terlalu besar secara global — lebih baik diatur per sesi jika perlu.

sort_buffer_size = 2M
join_buffer_size = 2M

table_open_cache

Jumlah file tabel yang bisa dibuka bersamaan.

table_open_cache = 4000

Cek:

SHOW STATUS LIKE 'Open_tables';
SHOW VARIABLES LIKE 'table_open_cache';

Melihat Konfigurasi Aktif

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE '%buffer%';

Atau semua:

SHOW VARIABLES;

Konfigurasi per Sesi

Beberapa parameter bisa diubah per sesi tanpa restart:

SET SESSION sort_buffer_size = 4 * 1024 * 1024;
SET SESSION join_buffer_size = 4 * 1024 * 1024;

Monitoring Performa

1. SHOW STATUS

SHOW GLOBAL STATUS LIKE 'Questions';        -- total query
SHOW GLOBAL STATUS LIKE 'Uptime';           -- detik sejak start
SHOW GLOBAL STATUS LIKE 'Threads_connected';-- koneksi aktif
SHOW GLOBAL STATUS LIKE 'Slow_queries';     -- jumlah slow query

Hitung QPS (query per second):

SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Questions') /
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Uptime') AS qps;

2. SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS\G

Menampilkan informasi mendetail tentang InnoDB: lock, deadlock terakhir, buffer pool, I/O, dll.

3. performance_schema

Performance Schema menyediakan metrik mendalam:

-- Query paling boros
SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;
-- Tabel yang paling sering diakses
SELECT OBJECT_NAME, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY COUNT_READ + COUNT_WRITE DESC
LIMIT 10;

4. sys Schema (MySQL 5.7+)

sys schema menyediakan view siap pakai untuk monitoring:

-- Query lambat
SELECT * FROM sys.statement_analysis LIMIT 10;

-- Tabel tanpa primary key
SELECT * FROM sys.schema_tables_with_full_table_scans;

-- Index yang tidak terpakai
SELECT * FROM sys.schema_unused_indexes;

Checklist Optimasi

Gunakan checklist ini saat menghadapi masalah performa:

  1. Identifikasi query lambat — slow query log, Performance Schema.
  2. Jalankan EXPLAIN — periksa type, key, rows, Extra.
  3. Periksa index — apakah kolom WHERE/JOIN/ORDER BY sudah ter-index?
  4. Optimasi query — hindari SELECT *, fungsi pada kolom, LIKE '%...', OR yang menghalangi index.
  5. Pertimbangkan covering index — jika query hanya butuh beberapa kolom.
  6. Cek konfigurasi server — innodb_buffer_pool_size sudah cukup?
  7. Analisis tabel — ANALYZE TABLE untuk memperbarui statistik.
  8. Monitor berkelanjutan — Performance Schema, sys schema, slow query log.

Ringkasan

Teknik Kapan Digunakan
EXPLAIN Menganalisis execution plan query
EXPLAIN ANALYZE Melihat waktu aktual per langkah
Slow query log Menemukan query lambat
Index Mempercepat WHERE, JOIN, ORDER BY
Covering index Menghindari baca tabel
Rewrite query Menghindari fungsi pada kolom, SELECT *, LIKE '%...'
innodb_buffer_pool_size Parameter server paling penting
ANALYZE TABLE Memperbarui statistik optimizer
Performance Schema Monitoring query dan tabel

Poin penting:

  • Ukur sebelum optimasi — jangan menebak, gunakan EXPLAIN dan slow query log.
  • Index adalah alat utama, tetapi index berlebih memperlambat write.
  • innodb_buffer_pool_size adalah parameter konfigurasi paling berpengaruh.
  • Hindari fungsi pada kolom di WHERE — menghalangi index.
  • Keyset pagination lebih cepat daripada LIMIT ... OFFSET besar.
  • Monitor berkelanjutan — performa berubah seiring pertumbuhan data.

Pada artikel berikutnya, kita akan membahas MySQL dengan PHP (PDO/MySQLi) — menghubungkan aplikasi PHP ke MySQL.

Top