Teknik Optimasi SQL untuk Dataset Berukuran Besar

Bekerja dengan dataset besar di SQL bisa menjadi mimpi buruk jika query tidak dioptimalkan. Baik Anda seorang analis, engineer, atau DBA, memahami cara mengoptimalkan query SQL sangat penting agar eksekusi lebih cepat, penggunaan sumber daya efisien, dan skalabilitas lebih baik.

Berikut 20 teknik praktis optimasi SQL, lengkap dengan contoh dan kegunaannya:

Gunakan EXISTS daripada IN untuk Subquery

Contoh Buruk:

SELECT name FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

SELECT name FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

Contoh Baik:

SELECT name FROM employees 
WHERE EXISTS (
  SELECT 1 FROM departments 
  WHERE departments.id = employees.department_id AND location = 'NY'
);

SELECT name FROM employees 
WHERE EXISTS (
  SELECT 1 FROM departments 
  WHERE departments.id = employees.department_id AND location = 'NY'
);

Kenapa lebih baik?
EXISTS berhenti mencari setelah menemukan match pertama, jadi lebih cepat pada tabel besar.

Hindari SELECT *

Ambil hanya kolom yang diperlukan.
Buruk:

SELECT * FROM orders;

SELECT * FROM orders;

Baik:

SELECT order_id, customer_id, amount FROM orders;

SELECT order_id, customer_id, amount FROM orders;

Kenapa?
Mengurangi beban I/O dan trafik jaringan.

Gunakan Indeks yang Tepat

Jika sering filter berdasarkan kolom tertentu, buat indeks.
Contoh:

CREATE INDEX idx_orders_customer ON orders(customer_id);
SELECT * FROM orders WHERE customer_id = 123;

CREATE INDEX idx_orders_customer ON orders(customer_id);
SELECT * FROM orders WHERE customer_id = 123;

Manfaat:
Indeks mempercepat pencarian data.

Gunakan Covering Index

Indeks yang mencakup semua kolom yang di-query.
Contoh:

CREATE INDEX idx_covering ON orders(order_id, amount);
SELECT order_id, amount FROM orders WHERE order_id = 101;

CREATE INDEX idx_covering ON orders(order_id, amount);
SELECT order_id, amount FROM orders WHERE order_id = 101;

Manfaat:
Hanya pakai indeks tanpa membaca tabel utama.

Batasi Hasil Sejak Awal

Misalnya untuk pagination.

SELECT name FROM customers WHERE country = 'US' LIMIT 10;

SELECT name FROM customers WHERE country = 'US' LIMIT 10;

Manfaat:
Hindari full scan jika hanya butuh data sebagian.

Gunakan JOIN daripada Subquery Jika Bisa

Buruk:

SELECT name, 
       (SELECT department_name FROM departments WHERE id = employees.department_id) 
FROM employees;

SELECT name, 
       (SELECT department_name FROM departments WHERE id = employees.department_id) 
FROM employees;

Baik:

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Kenapa?
JOIN dioptimalkan lebih baik oleh query planner.

Hindari Fungsi pada Kolom yang Diindeks

Buruk:

SELECT * FROM users WHERE YEAR(created_at) = 2024;

SELECT * FROM users WHERE YEAR(created_at) = 2024;

Baik:

SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Kenapa?
Supaya indeks tetap bisa digunakan.

Gunakan WHERE daripada HAVING

Filter sebelum agregasi.
Buruk:

SELECT COUNT(*) FROM orders GROUP BY customer_id HAVING customer_id = 10;

SELECT COUNT(*) FROM orders GROUP BY customer_id HAVING customer_id = 10;

Baik:

SELECT COUNT(*) FROM orders WHERE customer_id = 10 GROUP BY customer_id;

SELECT COUNT(*) FROM orders WHERE customer_id = 10 GROUP BY customer_id;

Lakukan Batch Update/Insert

Update data besar secara bertahap.
Contoh:

UPDATE sales SET tax = 0.08 WHERE id BETWEEN 1 AND 10000;

UPDATE sales SET tax = 0.08 WHERE id BETWEEN 1 AND 10000;

Kenapa?
Kurangi lock dan beban resource.

Gunakan Partitioning Tabel

Pisahkan tabel besar jadi partisi (misal per tahun).
Contoh:

CREATE TABLE logs_2024 PARTITION OF logs 
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE logs_2024 PARTITION OF logs 
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Manfaat:
Query lebih cepat karena hanya scan partisi terkait.

Optimalkan dengan WITH (NOLOCK) (SQL Server)

Membaca data tanpa lock (hati-hati karena bisa dirty read).

SELECT * FROM orders WITH (NOLOCK);

SELECT * FROM orders WITH (NOLOCK);

Gunakan CTE (Common Table Expressions) untuk Query Kompleks

Contoh:

WITH top_customers AS (
  SELECT customer_id, SUM(amount) as total FROM orders GROUP BY customer_id
)
SELECT * FROM top_customers WHERE total > 10000;

WITH top_customers AS (
  SELECT customer_id, SUM(amount) as total FROM orders GROUP BY customer_id
)
SELECT * FROM top_customers WHERE total > 10000;

Manfaat:
Lebih mudah dibaca dan dikelola.

Filter Sejak Awal dalam JOIN

Filter data sebelum join untuk mengurangi jumlah baris.
Baik:

SELECT * FROM orders o 
JOIN (SELECT * FROM customers WHERE country = 'US') c 
ON o.customer_id = c.id;

SELECT * FROM orders o 
JOIN (SELECT * FROM customers WHERE country = 'US') c 
ON o.customer_id = c.id;

Gunakan Tipe Data yang Tepat

Contoh: Jangan menggunakan tipe VARCHAR(255) untuk angka kecil.
Kenapa?
Ukuran kecil = hemat space = lebih cepat.

Gunakan UNION ALL daripada UNION

UNION menghapus duplikat (lebih lambat), UNION ALL lebih cepat.

Hindari Wildcard di Awal LIKE

Buruk:

WHERE name LIKE '%john';

WHERE name LIKE '%john';

Baik:

WHERE name LIKE 'john%';

WHERE name LIKE 'john%';

Kenapa?
Agar indeks tetap bisa dipakai.

Analisa Execution Plan

Gunakan EXPLAIN atau EXPLAIN ANALYZE untuk tahu apa yang bikin lambat.

Denormalisasi Jika Perlu

Buat tabel ringkasan untuk mengurangi join berulang.

Hindari OR di Kondisi dengan Indeks

Pisahkan query agar indeks bisa bekerja.

Update Statistik dan Rebuild Index

Gunakan perintah seperti:

ANALYZE;
REINDEX TABLE orders;

ANALYZE;
REINDEX TABLE orders;

Manfaat:
Optimizer punya info terbaru tentang distribusi data.

Kesimpulan

Optimasi SQL bukan trik kecil, tapi bagian penting dari rekayasa performa. Gunakan kombinasi teknik ini agar query berjalan lebih cepat dan efisien.