Views dan Indexes: Optimasi Database 🚀

Halo SQL Explorers! 👋 Setelah belajar GROUP BY dan HAVING, sekarang kita bakal belajar tentang Views dan Indexes. Ini adalah tools penting untuk optimasi database!

Views: Query yang Disimpan 📝

Views adalah virtual table yang berisi hasil dari query. Sangat berguna untuk:

  • Menyimpan query kompleks
  • Menyederhanakan akses data
  • Meningkatkan security

Membuat View 🛠️

-- View untuk analisis penjualan per kategori
CREATE VIEW sales_by_category AS
SELECT 
    c.nama as kategori,
    COUNT(DISTINCT o.id) as jumlah_order,
    SUM(oi.quantity) as total_quantity,
    SUM(oi.price * oi.quantity) as total_penjualan
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
GROUP BY c.id, c.nama;

-- Menggunakan view
SELECT * FROM sales_by_category;

-- Hasilnya:
+------------+--------------+----------------+------------------+
| kategori   | jumlah_order | total_quantity | total_penjualan  |
+------------+--------------+----------------+------------------+
| Elektronik |            2 |              2 |         20000000 |
| Fashion    |            1 |              2 |           750000 |
| Makanan    |            1 |              5 |           250000 |
+------------+--------------+----------------+------------------+

View untuk Customer Analytics 📊

-- View untuk analisis customer
CREATE VIEW customer_analytics AS
SELECT 
    c.nama,
    c.email,
    c.kota,
    COUNT(DISTINCT o.id) as total_orders,
    COALESCE(SUM(oi.price * oi.quantity), 0) as total_spent,
    MAX(o.tanggal) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.nama, c.email, c.kota;

-- Menggunakan view
SELECT * FROM customer_analytics;

-- Hasilnya:
+------+----------------+---------+--------------+-------------+------------------+
| nama | email          | kota    | total_orders | total_spent | last_order_date  |
+------+----------------+---------+--------------+-------------+------------------+
| Budi | budi@email.com | Jakarta |            2 |    20000000 | 2024-01-02      |
| Ani  | ani@email.com  | Bandung |            1 |      750000 | 2024-01-03      |
| Caca | caca@email.com | Jakarta |            1 |      250000 | 2024-01-04      |
| Dodi | dodi@email.com | Jakarta |            0 |           0 | NULL            |
+------+----------------+---------+--------------+-------------+------------------+

Indexes: Optimasi Query 🚀

Indexes adalah struktur data yang mempercepat pencarian data. Seperti daftar isi buku!

Membuat Index 🛠️

-- Index untuk foreign keys
CREATE INDEX idx_category_id ON products(category_id);
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_product_id ON order_items(product_id);

-- Composite index untuk order_items
CREATE INDEX idx_order_product ON order_items(order_id, product_id);

Tipe-tipe Index 📚

  1. Single Column Index

    -- Index untuk kolom email (unique)
CREATE UNIQUE INDEX idx_email ON customers(email);

-- Index untuk kolom tanggal
CREATE INDEX idx_order_date ON orders(tanggal);

  2. Composite Index

    -- Index untuk pencarian produk
CREATE INDEX idx_product_search 
ON products(category_id, nama, harga);

  3. Partial Index

    -- Index khusus untuk produk aktif
CREATE INDEX idx_active_products 
ON products(nama, harga) 
WHERE stok > 0;

Tips Penggunaan Views dan Indexes 💡

  1. Views Best Practices

    • Gunakan views untuk query yang sering dipakai
    • Update view ketika schema berubah
    • Hindari nested views yang terlalu dalam

  2. Index Best Practices

    • Index kolom yang sering di-query
    • Hindari over-indexing
    • Monitor penggunaan index

  3. Performance Tips

    -- Gunakan EXPLAIN untuk analisis query
EXPLAIN SELECT * FROM products WHERE category_id = 1;

-- Check penggunaan index
SHOW INDEX FROM products;

Contoh Kasus: Optimasi Query 🎯

-- Query sebelum optimasi
SELECT 
    p.nama as produk,
    c.nama as kategori,
    COUNT(oi.order_id) as total_orders
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE p.harga > 1000000
GROUP BY p.id, p.nama, c.nama;

-- Tambah index untuk optimasi
CREATE INDEX idx_product_price ON products(harga);
CREATE INDEX idx_product_orders ON order_items(product_id);

-- Buat view untuk menyederhanakan query
CREATE VIEW product_order_stats AS
SELECT 
    p.nama as produk,
    c.nama as kategori,
    COUNT(oi.order_id) as total_orders
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.nama, c.nama;

-- Query setelah optimasi
SELECT * FROM product_order_stats 
WHERE produk IN (
    SELECT nama 
    FROM products 
    WHERE harga > 1000000
);

-- Hasilnya:
+---------------+------------+--------------+
| produk        | kategori   | total_orders |
+---------------+------------+--------------+
| Laptop Gaming | Elektronik |            1 |
| Smartphone    | Elektronik |            1 |
+---------------+------------+--------------+

Materi Selanjutnya 📚

Di materi selanjutnya, kita bakal belajar:

  • Transaction management
  • ACID properties
  • Concurrency control
  • Dan masih banyak lagi! 🚀
Fatkul Nur Koirudin