Teknik Optimasi Query SQL

Halo teman-teman! Tidak terasa waktu telah berlalu begitu saja, semoga kita selalu diberi kemudahan dan kesehatan yang melimpah! Pada kesempatan kali ini akan membahas teknik untuk melakukan optimasi query dalam SQL.

Optimasi query merupakan proses untuk meningkatkan eksekusi query SQL guna meminimalkan waktu eksekusi dan konsumsi dumber daya. Berikut teknik optimasi query:

1. Indeks pada kolom yang sering digunakan dalam klausa WHERE atau join.
2. LIMIT untuk mengurangi jumlah data yang diambil.
3. JOIN.
4. Teknik caching untuk akses data.
5. EXPLAIN untuk menganalisis eksekusi query.

Pada kali ini akan dibahas terknik optimasi index yakni CREATE serta UNIQUE juga teknik EXPLAIN.

INDEX

Indeks atau index pada SQL adalah struktur data khusus yang digunakan untuk meningkatkan kecepatan pengambilan data dari tabel database.

• CREATE INDEX

Struktur ini dugunakan untuk membuat indeks pada satu atau beberapa kolom tertentu yang berfungsi seperti daftar isi pada buku, memungkinkan sistem basis data untuk menemukan baris data dengan lebih cepat berdasarkan nilai kolom yang diindeks.

Misalkan pada kolom harga di tabel produk sering digunakan dalam klausa WHERE atau JOIN, membuat indeks pada kolom tersebut akan mempercepat proses pencarian.

Contoh:

CREATE INDEX idx_harga ON produk(harga);

• UNIQUE INDEX

Jenis indeks ini memastikan bahwa nilai dalam kolom atau kombinasi kolom tertentu harus unik yang berguna untuk mencegah duplikasi data pada kolom indeks.

Misalkan untuk mencegah duplikasi kode produk, dapat membuat indeks unik seperti berikut:

CREATE UNIQUE INDEX idx_kode_produk ON produk(kode_produk);

Kedua indeks tersebut berguna untuk mengelola data skala besar, terutama saat melakukan query yang sering mengakses kolom tertentu untuk proses pencarian atau pengurutan data. Namun, penggunaan yang berlebihan akan menyebabkan overhead pemnyimpanan dan memperlambat proses insert, update, dan delete data.

Menganalisis query dengan EXPLAIN

EXPLAIN digunakan untuk melihat rencana eksekusi query dan mengidentifikasi area yang dapat dioptimalkan, memahami server basis data menjalankan query, termasuk langkah-langkah penggunaan indeks, metode join, serta estimasi jumlah baris yang akan diproses. Hasil dari EXPLAIN adalah tabel dengan informasi tahap eksekusi query yang berguna untuk mengoptimalkan sebuah query lebih lanjut. Contohnya menggunakan EXPLAIN guna mengetahui bagaimana server menjalankan query untuk mencari transaksi dengan harga di atas 50.000.

Contoh:

EXPLAIN SELECT * FROM transaksi WHERE harga > 50000;

PRAKTIKUM PENERAPAN SQL BERTINGKAT SUB QUERY

1. Membuat Basis Data

create database db_penjualan;
use db_penjualan;

Penjelasan:

Digunakan untuk membuat database baru dengan nama db_penjualan. Kemudisn menggunakan perintah use untuk memilih database db_penjualan agar semua perintah SQL selanjutnya berlaku di dalam database tersebut.

2. Membuat Tabel

• Tabel produk

 create table produk (
    -> kode_produk varchar(10) primary key,
    -> nama_produk varchar(50),
    -> harga INT);

Penjelasan:

Perintah tersebut digunakan untuk membuat tabel bernama produk di dalam db_penjualan. Tabel memiliki 3 kolom yaitu kode_produk, nama_produk dan harga dengan masing-masing tipe data dan panjang karakter yang dapat dilihat pada gambar dibawah ini.

• Tabel transaksi

create table transaksi (
    -> id_transaksi int auto_increment primary key,
    -> kode_produk varchar(10),
    -> jumlah int,
    -> total_harga int,
    -> foreign key (kode_produk) references produk(kode_produk));

Penjelasan:

Perintahhh tersebut digunakan untuk membuat tabel transaksi yangg menyimpan data transaksi pembelian produk. Tabel memiliki kolom id_transaksi, jumlah, total_harga dan kode_produk yang memiliki relasi dengan kode_produk pada tabel produk, hal ini berguna untuk memastikan integrasi datanya. Berikut dapat dilihat tipe data dan panjang karakter pada struktur tabelnya.

• Tabel pelanggan

create table pelanggan (
    -> id_pelanggan int auto_increment primary key,
    -> nama_pelanggan varchar(50),
    -> alamat varchar(100));

Penjelasan:

Perintah tersebut digunakan untuk membuat tabel pelanggan yangg menyimpan data pelangggan. Tabel terdiri dari 3 kolom yaitu id_pelanggan, nama_pelanggan, dan alamat dengan tipe data yang panjang karakter yang berbeda. Beriut struktur dari tabel pelanggan.

3. Mengisi Data Tabel

• Tabel produk

insert into produk (kode_produk, nama_produk, harga) values
    -> ('P001', 'Laptop', 7000000),
    -> ('P002', 'Mouse', 150000);

Penjelasan:

Perintah tersebut digunakan untuk menambahakn data ke dalam tabel produk. Berikut data yang dimasukkan ke dalam tabel:

• Tabel transaksi

insert into transaksi (kode_produk, jumlah, total_harga) values
    -> ('P001', 2, 14000000),
    ->  ('P002', 5, 750000);

Penjelasan:

Perintah tersebut digunakan untuk menambahakan data ke dalam tabel transaksi. Berikut data yang ditambahakan ke dalamnya:

• Tabel pelanggan

insert into pelanggan (nama_pelanggan, alamat) values
    -> ('Andi', 'Jl. Merdeka'),
    -> ('Budi', 'Jl. Sudirman');

Penjelasan:

Perintah tersebut digunakan untuk menambahakan data ke dalam tabel pelanggan. Berikut data yang dimasukkan ke dalamnya:

4. Mengoptimalkan Query

• Membuat indeks pada kolom harga di tabel produk:

TOTAL HARGA

CREATE INDEX idx_total_harga ON transaksi(total_harga);

Penjelasan:

Perintah tersebut diguanakan untuk membuat index dengan nama idx_total_harga pada kolom total_harga di tabel transaksi. Hal tersebut dilakukan untuk guna mempercepat pencarian yang menggunakan kolom total_harga.

HARGA

CREATE INDEX idx_harga ON produk(harga);

Penjelasan:

Perintah tersebut digunakan untuk membuat sebuah index dengan nama idx_harga pada kolom harga di tabel produk. Bertujuan untuk mempercepat proses pencarian (query) yang menggunakan kolom harga di tabel produk.

• Menganalisis query menggunakan EXPLAIN:

Total Harga

 EXPLAIN SELECT * FROM transaksi WHERE total_harga > 5000000;

Penjelasan:

Perintah explain digunakan untuk menganalisis dan melihat bagaimana query akan dijalankan oleh database. Pada bagian ini berfokus untuk menampilkan semua data dari tabel transaksi di mana nilai total_harga lebih dari 5 juta.

Harga

EXPLAIN SELECT * FROM produk WHERE harga > 5000000;

Penjelasan:

Perintah explain digunakan untuk menganalisis dan melihat bagaimana query akan dijalankan oleh database. Pada bagian ini berfokus untuk menampilkan semua data dari tabel produk di mana nilai harga lebih dari 5 juta.

5. Mengevaluasi hasil

Harga

EXPLAIN SELECT * FROM produk WHERE harga > 5000000;

Penjelasan:

Perintah tersebut digunakan untuk melihat bagaimana MySQL mengeksekusi query tersebut di balik layar untuk mengetahui apakah query sudah optimal atau masih berat dijalankan.

Sebelum index:

Penjelasan:

Query melakukan full table scan karena tidak ada index yang bisa digunkan untuk kolom harga. MySQL harus membaca seluruh baris untuk memfilter hasil, yang kurang efisien, terutama jika jumlah data besar.

Sesudah index:

Penjelasan:

Query menggunakan index idx_harga untuk mencari nilai harga di atas 5 juta. Tipe akses range menunjukkan bahwa hanya rentang waktu tertentu yang dapat dibaca dari index dan jauh lebih efisien.

Using index condition menunjukkan kondisi WHERE difilter langsung melalui index tanpa membaca semua baris tabel.

Total Harga

 EXPLAIN SELECT * FROM transaksi WHERE total_harga > 5000000;

Penjelasan:

Perintah tersebut digunakan untuk menganalisis performa query apakah sudah optimal atau masih berat untuk dijalankan.

Sebelum index:

Penjelasan:

MySQL membaca seluruh isi tabel transaksi karena tidak ada index pada kolom total_harga, sehingga tidak bisa melakukan pencarian langsung ke data yang dibutuhkan.

Sesudah index:

Penjelasan:

MySQL menggunakan index idx_total_harga untuk menyaring data. Tipe akses range menunjukkan bahwa hanya baris dengan nilai dalam rentang tertentu yang diakses menjadi lebih efisien. Using index condition menandakan bahwa filter WHERE dilakukan langsung dari index, mempercepat eksekusi.

TUGAS

1. Buat query untuk mendapatkan produk dengan harga di atas rata-rata.

select * from produk where harga > (select avg(harga) from produk);

Penjelasan:

Query tersebut digunakan untuk menampilkan semua data dari tabel produk yang memiliki harga lebih besar dari rata-rata harga seluruh produk.

• Bagian subquery (select avg(harga) from produk) untuk menghitung nilai rata-rata harga semua produk di tabel produk.
• Bagian utamanya untuk memilih baris di mana kolom harga lebih besar dari hasil subquery tersebut.

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

CREATE INDEX total_harga ON transaksi(total_harga);

Penjelasan:

• CREATE INDEX untuk membuat index yang berfungsi seperti daftar isi di buku yang memperceoat pencarian data di dalam tabel database.
• total_harga merupakan nama index yang dibuat.
• ON transaksi menunjukkan bahwa index dibuat pada tabel transaksi.
• (total_harga) menunjukkan bahwa index diterapkan pada kolom total_harga di tabel transaksi.

select * from transaksi where total_harga > 1000000;

Penjelasan:

• select * untuk mengambil data dari tabel dan yang akan diambil semua kolom yang ada di tabel transaksi.
• from transaksi menunjukkan bahwa data akan diambil dari tabel bernama transaksi.
• where total_harga > 1000000 merupakan kondisi filter atau penyaringan data dengan where digunakan untuk memilih baris yang memenuhi syarat tertentu yaitu nilai total_harga lebih dari 1.000.000 akan ditampilkan.

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

alter table transaksi
    -> add column id_pelanggan int,
    -> add constraint fk_id_pelanggan foreign key (id_pelanggan) references pelanggan(id_pelanggan);

update transaksi set id_pelanggan = 1 where id_transaksi = 1;
update transaksi set id_pelanggan = 2 where id_transaksi = 2;

Penjelasan:

Perintah alter table digunakan untuk menambahakan kolom id_pelanggan ke tabel transaksi, lalu menetapkan foreign key agar terhubung ke kolom id_pelanggan pada tabel pelanggan. Setelah itu, dilakukan update untuk menetapkan bahwa transaksi dengan id_transaksi = 1 dimiliki oleh pelanggan 1, dan transaksi dengan id_transaksi= 2 dimiliki oleh pelanggan 2.

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;

Penjelasan:

• select p.nama_pelanggan, sum(t.total_harga) as total_transaksi

Mengambil nama pelanggan dan jumlah total transaksi mereka.

SUM(t.total_harga) digunakan untuk menjumlahkan total_harga dari semua transaksi milik pelanggan tersebut.

• from pelanggan p untuk mengambil data dari tabel pelanggan, dan disingkat dengan alias p agar penulisan lebih ringkas.
• join transaksi t on p.id_pelanggan = t.id_pelanggan untuk menggabungkan tabel pelanggan (p) dengan tabel transaksi (t) berdasarkan kecocokan id_pelanggan.
• group by p.id_pelanggan, p.nama_pelanggan; untuk mengelompokkan hasil berdasarkan pelanggan, agar fungsi SUM() bisa menghitung total transaksi per orang. Perlu menyertakan p.nama_pelanggan juga disini karena bagian dari select.

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;

Penjelasan:

• Pada baris pertama (tabel pelanggan), MySQL membuat tabel sementara dan melakukan filesort untuk GROUP BY, yang menunjukkan performa kurang optimal karena data dikelompokkan dan diurutkan tanpa bantuan index.
• Baris kedua (tabel transaksi), digunakan join buffer dengan metode BNL (Block Nested Loop Join), artinya MySQL memproses join secara manual baris demi baris. Hal ini terjadi karena index fk_id_pelanggan tidak digunakan, kemungkinan karena belum dioptimalkan atau tidak cocok dengan query.

5. Buat indeks unik pada kolom kode_produk di tabel produk.

create unique index kode_produk_unik on produk(kode_produk);

Penjelasan:

• create unique index, merupakan perintah untuk membuat index unik. Index unik ini bergunan untuk memastikan tidak ada nilai duplikat di kolom yang diindeks.
• create unique index, merupakan nama index yang dibuat.
• on produk(kode_produk), menunjukkan bahwa index ini dibuat pada tabel produk dan kolom kode_produk.

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

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

Penjelasan:

• select_type: SIMPLE artinya query tidak menggunakan subquery atau union dan hanya query sederhana.
• table: produk, tabel yang digunakan adalah produk.
• type: cost, merupakan tipe akses yang paling efisien dan digunakan saat hanya satu baris yang akan diambil, cocok untuk pencarian berdasarkan primary key atau unique.
• possible_key: PRIMARY, kode_produk_unik, MySQL mengidentifikasi dua index yang mungkin digunakan: index bawaan primary key dan index unik buatan (kode_produk_unik).
• key: PRIMARY, menunjukkan index yang digunakan yaitu PRIMARY.
• key_len: 42, menunjukkan panjang index yang digunakan.
• ref: const, menunjukkan bahwa nilai dalam kondisi WHERE adalah konstan.
• rows: 1, memperkirakan hanya satu baris yang cocok atau efisien.
• Extra: (kosong), tidak ada operasi tambahkan yang memberatkan seperti sorting atau temporary table.

Optimasi query dalam SQL merupakan teknik penting untuk meningkatkan kecepatan dan efisiensi pengambilan data dari basis data. Dengan penggunaan indeks seperti CREATE INDEX dan UNIQUE INDEX, pencarian data menjadi lebih cepat karena database dapat mengakses data secara langsung tanpa harus membaca seluruh tabel. Selain itu, perintah EXPLAIN membantu menganalisis cara kerja query sehingga dapat diketahui bagian mana yang perlu dioptimalkan. Penerapan teknik ini sangat berguna terutama pada pengolahan data dalam skala besar agar waktu eksekusi dan beban sistem dapat diminimalkan.

Link github:

[embed]GitHub - Rahma-dwi-kartika-sari/praktikumsemester2_basisdata Contribute to Rahma-dwi-kartika-sari/praktikumsemester2_basisdata development by creating an account on GitHub.github.com

REFERENSI

Nata, I. A. (2025). Modul 7— TEKNIK OPTIMASI QUERY DALAM SQL. Universitas Tidar.