Performance Optimization: Optimasi Performa Query SQL 🚀

Halo SQL Explorers! 👋 Selamat datang di SQL Advanced!

Di level intermediate, kita udah belajar:

  • JOIN yang kompleks
  • Subquery dan fungsi agregat
  • Views dan indexes
  • Transaction management
  • Database design

Sekarang kita bakal level up lagi dengan fokus ke PERFORMA! 🚀

Kenapa Performa Penting? 🤔

Di level basic dan intermediate, kita fokus ke “bikin query yang benar”. Sekarang kita bakal belajar “bikin query yang cepat”!

Bayangkan toko online kita udah sukses dan punya:

  • 100,000+ pelanggan
  • 50,000+ produk
  • 1,000,000+ order

Query yang tadinya cepet bisa jadi lambat banget! 😱

Database yang Kita Pake 📊

Kita tetep pake database toko online, tapi dengan data yang lebih banyak:

-- Generate data dummy yang banyak
INSERT INTO customers (nama, email, kota)
SELECT 
    CONCAT('Customer', n),
    CONCAT('customer', n, '@email.com'),
    ELT(1 + MOD(n, 5), 'Jakarta', 'Bandung', 'Surabaya', 'Medan', 'Makassar')
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 AS n
    FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
         (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
         (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
         (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
) numbers
WHERE n <= 100000;

Analisis Query Performance 📊

1. Pake EXPLAIN

EXPLAIN itu tool buat liat gimana database ngejalanin query:

-- Query yang mau dianalisis
EXPLAIN SELECT 
    c.nama AS customer,
    COUNT(DISTINCT o.id) AS total_order,
    SUM(oi.quantity * oi.price) AS total_belanja
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE c.kota = 'Jakarta'
GROUP BY c.id, c.nama;

-- Hasilnya:
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 100000 |    20.00 | Using where |
|  1 | SIMPLE      | o     | NULL       | ref   | customer_id   | cust_id | 4       | c.id |      5 |   100.00 | NULL       |
|  1 | SIMPLE      | oi    | NULL       | ref   | order_id      | ord_id  | 4       | o.id |      2 |   100.00 | NULL       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

2. Optimasi dengan Index

Index itu kayak “daftar isi” buat database:

-- Bikin index buat kolom yang sering dicari
CREATE INDEX idx_customer_city ON customers(kota);
CREATE INDEX idx_order_date ON orders(tanggal);

-- Query jadi lebih cepet!
EXPLAIN SELECT 
    c.nama AS customer,
    COUNT(DISTINCT o.id) AS total_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.kota = 'Jakarta'
GROUP BY c.id, c.nama;

3. Rewrite Query yang Lambat

Kadang perlu tulis ulang query biar lebih cepet:

-- Query yang lambat
SELECT p.nama, 
       p.harga,
       (SELECT AVG(harga) FROM products) as rata_rata_global,
       (SELECT AVG(harga) FROM products WHERE category_id = p.category_id) as rata_rata_kategori
FROM products p;

-- Query yang lebih cepet
SELECT 
    p.nama,
    p.harga,
    avg1.rata_rata_global,
    avg2.rata_rata_kategori
FROM products p
CROSS JOIN (
    SELECT AVG(harga) as rata_rata_global 
    FROM products
) avg1
JOIN (
    SELECT category_id, AVG(harga) as rata_rata_kategori
    FROM products 
    GROUP BY category_id
) avg2 ON p.category_id = avg2.category_id;

Tips Optimasi Query 💡

  1. **Hindari SELECT * **

    -- Jangan gini
SELECT * FROM products;

-- Mending gini
SELECT id, nama, harga FROM products;

  2. Pake Index dengan Tepat

    • Index bikin SELECT cepet
    • Tapi bikin INSERT/UPDATE lambat
    • Jangan kebanyakan index

  3. Batasi Jumlah Rows

    -- Pake LIMIT
SELECT * FROM orders 
ORDER BY tanggal DESC 
LIMIT 100;

  4. Hindari Subquery yang Berat

    • Subquery dijalanin berkali-kali
    • Lebih baik pake JOIN
    • Atau simpan hasil di temporary table

Debugging Query yang Lambat 🔍

  1. Cek Execution Time
-- MySQL
SET profiling = 1;
SELECT ...your query...;
SHOW PROFILES;

-- PostgreSQL
EXPLAIN ANALYZE SELECT ...your query...;
  1. Identifikasi Bottleneck
  • Tabel yang terlalu besar?
  • Index yang kurang?
  • JOIN yang kebanyakan?
  • Subquery yang berat?
  1. Monitor Resource Usage
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS;

Latihan Optimasi 🎯

  1. Optimasi Query Total Penjualan
-- Before optimization
SELECT 
    DATE(o.tanggal) as tanggal,
    SUM(oi.quantity * oi.price) as total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.tanggal >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(o.tanggal);

-- After optimization
SELECT 
    DATE(o.tanggal) as tanggal,
    SUM(oi.quantity * oi.price) as total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.tanggal >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(o.tanggal)
ORDER BY tanggal DESC;
  1. Optimasi Search Customer
-- Before optimization
SELECT *
FROM customers
WHERE nama LIKE '%budi%' OR email LIKE '%budi%';

-- After optimization
CREATE FULLTEXT INDEX idx_customer_search 
ON customers(nama, email);

SELECT *
FROM customers
WHERE MATCH(nama, email) AGAINST('budi' IN BOOLEAN MODE);

Preview Materi Selanjutnya 🎯

Di materi Complex Queries nanti, kita bakal belajar:

  • Common Table Expressions (CTE)
  • Recursive queries
  • Dynamic SQL
  • Dan masih banyak lagi! 🚀
Fatkul Nur Koirudin