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
- Menemukan Query Lambat
- Analisis Query dengan
EXPLAIN - Strategi Indexing yang Efektif
- Optimasi Query
- 1. Hindari
SELECT * - 2. Hindari Fungsi pada Kolom di
WHERE - 3. Hindari
LIKE '%...' - 4. Gunakan
EXISTSdaripadaINuntuk Subquery Besar - 5. Hindari
ORyang Menghalangi Index - 6. Batasi Hasil dengan
LIMIT - 7. Optimasi Pagination
- 8. Gunakan
UNION ALLjika Duplikat Tidak Masalah - 9. Hindari
ORDER BY RAND() - 10. Analisis dan Optimasi Tabel
- 1. Hindari
- Konfigurasi
my.cnf - Monitoring Performa
- Checklist Optimasi
- Ringkasan
Daftar isi
- Pendahuluan
- Menemukan Query Lambat
- Analisis Query dengan
EXPLAIN - Strategi Indexing yang Efektif
- Optimasi Query
- 1. Hindari
SELECT * - 2. Hindari Fungsi pada Kolom di
WHERE - 3. Hindari
LIKE '%...' - 4. Gunakan
EXISTSdaripadaINuntuk Subquery Besar - 5. Hindari
ORyang Menghalangi Index - 6. Batasi Hasil dengan
LIMIT - 7. Optimasi Pagination
- 8. Gunakan
UNION ALLjika Duplikat Tidak Masalah - 9. Hindari
ORDER BY RAND() - 10. Analisis dan Optimasi Tabel
- 1. Hindari
- Konfigurasi
my.cnf - Monitoring Performa
- Checklist Optimasi
- Ringkasan
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.cnfyang penting. - Best practice penulisan query.
Asumsi: Anda berada di database
toko_onlinedengan 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
typeminimalrange, idealnyarefatau lebih baik. Jikatype = ALLpada 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_asaja. ✅ - Query yang memfilter
kolom_adankolom_b. ✅ - Query yang memfilter
kolom_bsaja. ❌ (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:
- Identifikasi query lambat — slow query log, Performance Schema.
- Jalankan
EXPLAIN— periksatype,key,rows,Extra. - Periksa index — apakah kolom
WHERE/JOIN/ORDER BYsudah ter-index? - Optimasi query — hindari
SELECT *, fungsi pada kolom,LIKE '%...',ORyang menghalangi index. - Pertimbangkan covering index — jika query hanya butuh beberapa kolom.
- Cek konfigurasi server —
innodb_buffer_pool_sizesudah cukup? - Analisis tabel —
ANALYZE TABLEuntuk memperbarui statistik. - Monitor berkelanjutan — Performance Schema,
sysschema, 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
EXPLAINdan slow query log. - Index adalah alat utama, tetapi index berlebih memperlambat write.
innodb_buffer_pool_sizeadalah parameter konfigurasi paling berpengaruh.- Hindari fungsi pada kolom di
WHERE— menghalangi index. - Keyset pagination lebih cepat daripada
LIMIT ... OFFSETbesar. - Monitor berkelanjutan — performa berubah seiring pertumbuhan data.
Pada artikel berikutnya, kita akan membahas MySQL dengan PHP (PDO/MySQLi) — menghubungkan aplikasi PHP ke MySQL.