Salin dan Bagikan
MySQL Query Optimization Performance
Optimasi Query MySQL: Tips Mempercepat SELECT dengan Indexing yang Tepat - Panduan lengkap optimasi query MySQL dengan indexing, analisis EXPLAIN, dan teknik tuning untuk …

Optimasi Query MySQL: Tips Mempercepat SELECT dengan Indexing yang Tepat

Optimasi Query MySQL: Tips Mempercepat SELECT dengan Indexing yang Tepat

Query lambat adalah masalah paling umum yang saya temui dalam konsultasi database. Sebuah query yang tadinya membutuhkan 30 detik bisa dioptimasi menjadi 30 millisecond dengan indexing yang tepat. Artikel ini akan membahas teknik optimasi SELECT secara mendalam.

Mengapa Query Lambat?

Query lambat disebabkan oleh:

  • Missing index pada kolom yang sering dicari
  • Full table scan pada tabel besar
  • Query yang tidak efisien
  • Statistik tidak update
  • Lock contention

Memahami EXPLAIN

1. Syntax Dasar

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

2. Kolom Penting EXPLAIN Output

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE order_date > '2024-01-01';

Output yang perlu diperhatikan:

  • type: ALL (full scan) adalah yang terburuk, const/system adalah yang terbaik
  • possible_keys: Index yang mungkin digunakan
  • key: Index yang sebenarnya digunakan
  • rows: Jumlah baris yang diperkirakan di-scan
  • Extra: Informasi tambahan seperti Using filesort, Using temporary

Jenis-Jenis Index

1. Single Column Index

CREATE INDEX idx_email ON users(email);

Gunakan untuk:

  • Kolom yang sering digunakan di WHERE clause
  • Kolom dengan cardinality tinggi
  • Kolom yang sering di-ORDER BY

2. Composite Index (Multi-Column)

CREATE INDEX idx_name_date ON orders(customer_name, order_date);

Aturan composite index:

  • Kolom dengan cardinality tinggi di depan
  • Kolom yang selalu di WHERE clause
  • Urutan: equality first, range second

3. Covering Index

CREATE INDEX idx_covering ON orders(customer_id, order_date, total_amount);

-- Query yang ter-cover
SELECT customer_id, order_date, total_amount 
FROM orders 
WHERE customer_id = 123;

Covering index memungkinkan query dijawab hanya dari index tanpa akses tabel.

4. Unique Index

CREATE UNIQUE INDEX idx_unique_email ON users(email);

5. Full-Text Index (MyISAM dan InnoDB 5.6+)

CREATE FULLTEXT INDEX idx_content ON articles(content);

-- Penggunaan
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('tutorial mysql' IN NATURAL LANGUAGE MODE);

6. Partial Index (MySQL 8.0.13+)

CREATE INDEX idx_recent_orders ON orders(order_date) 
WHERE order_date > '2024-01-01';

Strategi Indexing

1. Analisis Slow Query Log

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_output = 'TABLE';

-- Lihat slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

2. Identifikasi Query yang Perlu Index

-- Cek query tanpa index
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 AS total_latency_ms,
    AVG_TIMER_WAIT/1000000000 AS avg_latency_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. Cardinality Check

-- Cek cardinality kolom
SELECT 
    COLUMN_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'users'
ORDER BY CARDINALITY DESC;

Contoh Kasus Optimasi

Kasus 1: Pencarian Email

Sebelum:

SELECT * FROM users WHERE email = 'user@example.com';
-- 5.2 detik (full table scan)

Solusi:

CREATE INDEX idx_email ON users(email);

Setelah:

SELECT * FROM users WHERE email = 'user@example.com';
-- 0.001 detik (index lookup)

Kasus 2: Range Query dengan Order

Sebelum:

SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY total_amount DESC;
-- 12 detik + filesort

Solusi:

CREATE INDEX idx_date_amount ON orders(order_date, total_amount);

Setelah:

SELECT order_id, customer_id, total_amount 
FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY total_amount DESC;
-- 0.8 detik, menggunakan index untuk filter dan sort

Kasus 3: JOIN Query Lambat

Sebelum:

SELECT o.*, c.customer_name 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';
-- 15 detik

Analisis:

EXPLAIN SELECT o.*, c.customer_name 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';
-- type: ALL pada kedua tabel

Solusi:

CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_customer_pk ON customers(id);

Setelah:

-- 0.3 detik, menggunakan index pada kedua tabel

Kasus 4: LIKE dengan Wildcard di Awal

Masalah:

SELECT * FROM products WHERE name LIKE '%laptop%';
-- Tidak bisa menggunakan index!

Solusi dengan Full-Text Index:

CREATE FULLTEXT INDEX idx_name ON products(name);

SELECT * FROM products 
WHERE MATCH(name) AGAINST('laptop' IN NATURAL LANGUAGE MODE);
-- Menggunakan full-text index

Best Practices Indexing

1. Jangan Terlalu Banyak Index

-- Index yang tidak perlu justru memperlambat INSERT/UPDATE
-- Maksimal 5-7 index per tabel untuk tabel transaksional

-- Cek index yang tidak digunakan
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_FETCH = 0
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

2. Index untuk Foreign Keys

-- Selalu buat index untuk foreign key
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);
ALTER TABLE orders ADD CONSTRAINT fk_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(id);

3. Index untuk Sorting

-- Jika sering ORDER BY created_at DESC
CREATE INDEX idx_created_sort ON orders(created_at DESC);

4. Index Prefix untuk TEXT/VARCHAR Panjang

-- Untuk VARCHAR(255), index 10-20 karakter cukup
CREATE INDEX idx_email_prefix ON users(email(20));

Maintenance Index

1. Analisis Index Usage

-- Cek index yang paling banyak digunakan
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
ORDER BY COUNT_FETCH DESC
LIMIT 20;

2. Rebuild Fragmented Index

-- Cek fragmentasi
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(DATA_FREE/1024/1024, 2) AS frag_size_mb
FROM information_schema.TABLES
WHERE DATA_FREE > 0 AND ENGINE='InnoDB';

-- Rebuild index
OPTIMIZE TABLE orders;
-- atau
ALTER TABLE orders ENGINE=InnoDB;

3. Update Statistics

-- Update statistik untuk query optimizer
ANALYZE TABLE orders;
ANALYZE TABLE customers;

Query Optimization Lainnya

1. SELECT Hanya Kolom yang Diperlukan

-- Jangan lakukan ini
SELECT * FROM orders WHERE customer_id = 123;

-- Lakukan ini
SELECT order_id, order_date, total FROM orders WHERE customer_id = 123;

2. Gunakan LIMIT

-- Untuk pagination
SELECT * FROM orders 
WHERE customer_id = 123 
ORDER BY order_date DESC 
LIMIT 20 OFFSET 0;

3. Hindari Function pada Kolom

-- Jangan (tidak bisa pakai index)
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- Lakukan (bisa pakai index)
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

4. Gunakan UNION ALL daripada OR

-- Kadang-kadang lebih cepat
SELECT * FROM orders WHERE customer_id = 1
UNION ALL
SELECT * FROM orders WHERE customer_id = 2;

-- Daripada
SELECT * FROM orders WHERE customer_id IN (1, 2);

Monitoring Query Performance

1. Setup Performance Schema

-- Enable query monitoring
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

2. Lihat Query Statistics

SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 AS avg_latency_ms,
    MAX_TIMER_WAIT/1000000000 AS max_latency_ms,
    SUM_LOCK_TIME/1000000000 AS total_lock_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Kesimpulan

Indexing adalah seni dan ilmu. Terlalu sedikit index membuat query lambat, terlalu banyak membuat write operation lambat. Gunakan EXPLAIN, analisis slow query log, dan test untuk menemukan keseimbangan yang tepat.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/optimasi-query-mysql-indexing/

Hendra WIjaya
Tirinfo
5 minutes.
3 February 2026