Basis Data: Optimasi Query SQL

Hai! It's been a long time since laporan praktikum basis data yang terakhir. Kali ini, aku akan membahas mengenai optimasi query. Check this out ya!

Dasar Teori

Optimasi query merupakan sebuah proses meningkatkan efisiensi eksekusi query SQL untuk meminimalkan waktu eksekusi dan konsumsi sumber daya. Beberapa teknik optimasi:

Menggunakan indeks pada kolom yang sering digunakan dalam klausa WHERE atau JOIN.

1. Menggunakan LIMIT untuk mengurangsi jumlah data yang diambil.
2. Menghindari penggunaan SELECT* dan memilih kolom-kolom spesifik.
3. Menggunakan JOIN daripada subquery jika memungkinkan.
4. Menggunakan teknik caching untuk data yang sering diakses.
5. Mengguankan EXPLAIN untuk menganalisis eksekusi query.

Teknik Optimasi:

1. CREATE INDEX, UNIQUE INDEX

Indeks dalam SQL merupakan struktur data khusus yang digunakan untuk meningkatkan kecepatan pengambilan data dari tabel database. Salah satu jenis indeks adalah CREATE INDEX yang digunakan untuk membuat indeks pada satu atau beberapa kolom tertentu. Indeks ini berfungsi seperti daftar isi pada sebuah buku. Selain itu, terdapat juga UNIQUE INDEX yaitu jenis indeks yang memastikan bahwa nilai dalam kolom atau kombinasi kolom tertentu harus unik. UNIQUE INDEX tidak hanya mempercepat proses pencarian data, tetapi juga mencegah adanya duplikasi data pada kolom yang diindeks.

2. Menganalisis query dengan EXPLAIN

EXPLAIN digunakan untuk melihat rencana eksekusi query dan mengidentifikasi area yang dapat dioptimalkan. Dengan menggunakan EXPLAIN, user dapat memahami bagaimana server basis data menjalankan query. EXPLAIN akan menghasilkan output berupa tabel yang berisi informasi mengenai setiap tahap eksekusi query, seperti jenis operasu (SELECT, JOIN, dll), tabel yang digunakan, dan estimasi waktu eksekusi. Analisis ini digunakan untuk mengidentifikasi query yang tidak efisien dan agar dapat dioptimalkan lebih lanjut.

Praktikum

1. Membuat Basis Data dan tabel (Tabel produk, tabel transaksi, tabel pelanggan)

2. Struktur tabel dan isi tabel

3. Mengoptimalkan Query

• Explain tanpa index

EXPLAIN
SELECT * FROM transaksi
WHERE kd_produk = 'P001';

EXPLAIN
SELECT * FROM transaksi
WHERE kd_produk = 'P001';

• Membuat indeks pada kolom kode produk di tabel transaksi

CREATE INDEX idx_kd_produk
ON transaksi(kd_produk);

CREATE INDEX idx_kd_produk
ON transaksi(kd_produk);

• Jalankan kembali explain dan analisa perbedaannya sebelum dan sesudah index

EXPLAIN
SELECT * FROM transaksi
WHERE kd_produk = 'P001';

EXPLAIN
SELECT * FROM transaksi
WHERE kd_produk = 'P001';

• Gunakan Unique Index untuk integritas data

CREATE UNIQUE INDEX idx_kd_produk
ON produk(kd_produk);

CREATE UNIQUE INDEX idx_kd_produk
ON produk(kd_produk);

• Uji Unique menggunakan

INSERT INTO produk
VALUES ('P001', 'Tablet', 3000000);

INSERT INTO produk
VALUES ('P001', 'Tablet', 3000000);

4. Evaluasi hasil

Perbandingan hasil query sebelum dan sesudah menggunakan indeks, menggunakan EXPLAIN untuk mengukur peningkatan kerja.

Sebelum menggunakan indeks, sql harus memeriksa seluruh isi tabel untuk menemukan data. Namun setelah dibuat indeks idx_kd_produk, sql dapat menemukan data yang dicari dengan cepat tanpa harus memeriksa semua tabelnya, karena pencarian dilakukan melalui index.

Tugas

1. Query untuk mendapatkan produk dengan harga di atas rata-rata

SELECT *
FROM produk
WHERE harga > (
    SELECT AVG(harga)
    FROM produk
);

SELECT *
FROM produk
WHERE harga > (
    SELECT AVG(harga)
    FROM produk
);

2. Optimalkan query untuk mencari transaksi di atas nilai tertentu menggunakan indeks

CREATE INDEX idx_total_harga
ON transaksi(total_harga);
SELECT *
FROM transaksi
WHERE total_harga > 500000;

CREATE INDEX idx_total_harga
ON transaksi(total_harga);
SELECT *
FROM transaksi
WHERE total_harga > 500000;

3. Buat query yang menampilkan nama pelanggan beserta jumlah total transaksi mereka menggunakan join

(Karena tabel pelanggan dan transaksi tidak memiliki relasi, maka tidak dapat dilakukan JOIN. Untuk dapat melakukan JOIN, maka harus ditambahkan ALTER TABLE (untuk mengubah struktur tabel yang sudah ada tanpa harus menghapus/membuat ulang tabelnya))

ALTER TABLE transaksi
ADD id_pelanggan INT;
SELECT p.nama_pelanggan,
SUM(t.total_harga) AS total_transaksi
FROM pelanggan p
JOIN transaksi t
ON p.id_pelanggan = t.id_pelanggan
GROUP BY p.id_pelanggan, p.nama_pelanggan

ALTER TABLE transaksi
ADD id_pelanggan INT;
SELECT p.nama_pelanggan,
SUM(t.total_harga) AS total_transaksi
FROM pelanggan p
JOIN transaksi t
ON p.id_pelanggan = t.id_pelanggan
GROUP BY p.id_pelanggan, p.nama_pelanggan

4. Evaluasi query di poin 3 menggunakan EXPLAIN

EXPLAIN
SELECT p.nama_pelanggan,
SUM(t.total_harga) AS total_transaksi
FROM pelanggan p
JOIN transaksi t
ON p.id_pelanggan = t.id_pelanggan
GROUP BY p.id_pelanggan, p.nama_pelanggan;

EXPLAIN
SELECT p.nama_pelanggan,
SUM(t.total_harga) AS total_transaksi
FROM pelanggan p
JOIN transaksi t
ON p.id_pelanggan = t.id_pelanggan
GROUP BY p.id_pelanggan, p.nama_pelanggan;

5. Buat indeks unik pada kolom kd_produk di tabel produk

CREATE UNIQUE INDEX idx_kode_produk
ON produk(kd_produk);

CREATE UNIQUE INDEX idx_kode_produk
ON produk(kd_produk);

6. Menganalisis query SELECT menggunakan EXPLAIN setelah membuat indeks pada poin 5

EXPLAIN
SELECT * FROM produk
WHERE kd_produk = 'P001';

EXPLAIN
SELECT * FROM produk
WHERE kd_produk = 'P001';

Okay……..that's all untuk materi kali ini. Thank you for reading and see u!