Implementasi Indeks yang tepat di Database Untuk Mempercepat Pencarian Data

Indeks database adalah struktur data yang digunakan untuk mempercepat operasi pencarian pada tabel dalam basis data. Indeks penting dalam basis data karena mereka secara signifikan meningkatkan kinerja query dengan mengurangi jumlah data yang perlu dipindai untuk menemukan hasil yang diinginkan.

Alasan Utama Mengapa Indeks Cepat

Indeks biasanya diimplementasikan menggunakan struktur data seperti B-Tree (Balance Tree) atau Hash. Struktur ini memungkinkan akses data yang efisien karena mereka menjaga data dalam urutan yang terurut atau memungkinkan pencarian langsung ke lokasi data yang tepat.

Gambaran struktur data tanpa indeks:

Dari gambar di atas menunjukan ilustrasi struktur data yang disimpan tanpa menggunakan indeks, ketika kita ingin mencari data dengan id = 7, sistem akan melakukan pencarian sebanyak 7x dari atas ke bawah, bayangkan jika data kita sudah besar dan mencapai jutaan, akan sangat lama dan berat untuk mencari data yang lama atau letaknya di akhir.

Gambaran struktur dengan indeks B-Tree (default indeks):

Gambar di atas menunjukan ilustrasi data yang disimpan menggunakan struktur data B-Tree (Balance Tree).

Penjelasan operasi pencarian data id = 7, menggunakan struktur data B-Tree:

1. Pencarian pertama, sistem akan memeriksa apakah 4 sama dengan id 7? jika tidak, sistem akan memeriksa apakah 7 lebih besar atau lebih kecil dari 4? jika lebih besar, sistem akan memeriksa ke bagian kanan data (angka 6).
2. Pencarian kedua, sistem akan memeriksa apakah 6 sama dengan 7? jika tidak, sistem akan memeriksa apakah 7 lebih besar atau lebih kecil dari 6? jika lebih besar, sistem akan memeriksa ke bagian kanan data (angka 7).
3. Pencarian ketiga, Sistem akan memeriksa apakah 7 sama dengan id 7? jika ya, return data.

Bisa dilihat di atas pencarian hanya membutuhkan 3x, jadi lebih cepat.

Pertimbangan dalam Menggunakan Indeks

• Frekuensi Penggunaan: Indeks sebaiknya dibuat pada kolom yang sering digunakan dalam kondisi WHERE, JOIN, dan ORDER BY.
• Ukuran Tabel: Indeks lebih efektif pada tabel yang lebih besar, di mana pencarian seluruh tabel (full table scan) akan sangat lambat.

Contoh Pembuatan Indeks B-Tree

-- Membuat tabel orders
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    address text,
    email varchar(100),
    status VARCHAR(20)
);

-- Membuat indeks pada kolom customer_id
CREATE INDEX idx_customer_id ON orders(customer_id);

Melihat daftar indeks dalam tabel

show index from orders;

Menghapus indeks

DROP INDEX idx_customer_id ON orders;

Melihat apakah indeks bekerja

EXPLAIN SELECT * FROM orders where customer_id = 1;

Beberapa type indeks yang sering digunakan

1. Indeks B-Tree: Indeks B-Tree adalah jenis indeks yang paling umum digunakan dalam basis data. B-Tree menjaga data dalam urutan terurut dan memungkinkan pencarian, penyisipan, dan penghapusan yang efisien, indeks B-Tree biasanya digunakan untuk operasi query “=”, “!=”, “<”, “>” dan “between”.

• Contoh pembuatan:

CREATE INDEX nama_indeks ON nama_tabel(nama_kolom);

2. Indeks Hash: Indeks Hash digunakan untuk pencarian cepat nilai yang tepat. Ini sangat cepat untuk pencarian nilai tunggal tetapi tidak cocok untuk pencarian rentang atau pengurutan, indeks Hash biasanya digunakan untuk operasi query “=”, “!=”.

• Contoh pembuatan:

CREATE INDEX nama_index ON nama_tabel (nama_kolom) USING HASH;

Contoh penjelasan sederhana struktur data tabel hash / hash map

Struktur data hash index (juga dikenal sebagai tabel hash atau hash map) adalah salah satu jenis struktur data yang digunakan untuk menyimpan pasangan kunci-nilai dan memungkinkan pencarian, penyisipan, dan penghapusan data dengan cepat.

Berikut adalah contoh sederhana dari tabel hash menggunakan array dan linked list untuk penanganan tabrakan (chaining):

Misalkan kita memiliki pasangan kunci-nilai berikut yang ingin kita simpan dalam tabel hash:

• (“Alice”, 25)
• (“Bob”, 30)
• (“Charlie”, 35)
• (“Dave”, 40)

Fungsi Hash

Misalkan fungsi hash kita sangat sederhana: hash(key) = sum(ASCII values of characters in key) % size_of_array.

Inisialisasi

Kita akan menggunakan array dengan ukuran 5 sebagai bucket array:

bucket_array = [None, None, None, None, None]

Langkah Insert Data

1. Insert (“Alice”, 25):

• Hitung hash untuk “Alice”: (65 + 108 + 105 + 99 + 101) % 5 = 478 % 5 = 3
• Letakkan (“Alice”, 25) di bucket indeks 3.

bucket_array = [None, None, None, [("Alice", 25)], None]

2. Insert (“Bob”, 30):

• Hitung hash untuk “Bob”: (66 + 111 + 98) % 5 = 275 % 5 = 0
• Letakkan (“Bob”, 30) di bucket indeks 0.

bucket_array = [[("Bob", 30)], None, None, [("Alice", 25)], None]

dan seterusnya.

Langkah Pencarian Data

Untuk mencari nilai yang terkait dengan kunci “Alice”:

• Hitung hash untuk “Alice”: (65 + 108 + 105 + 99 + 101) % 5 = 478 % 5 = 3
• Periksa bucket pada indeks 3: [("Alice", 25)]
• Temukan pasangan kunci-nilai yang sesuai: ("Alice", 25)
• Kembalikan nilai: 25

Catatan: di mysql engine penyimpanan InnoDB tidak mendukung indeks hash, indeks hash hanya bisa diterapkan di engine penyimpanan MEMORY dan NDBCLUSTER.

3. Indeks Full-Text: Indeks Teks Penuh digunakan untuk pencarian teks lengkap dalam kolom teks besar seperti artikel, deskripsi, dll. Indeks ini memungkinkan pencarian kata atau frase dalam teks, indeks Full-Text biasanya digunakan untuk operasi query “like”. Contoh pembuatan:

CREATE FULLTEXT INDEX nama_index ON nama_tabel(nama_kolom);

Indeks harus tepat sasaran

Indexing harus tepat sasaran karena indeks yang tidak sesuai dengan pola akses data dapat memperlambat kinerja query daripada meningkatkannya. Indeks yang dipilih dengan tepat memungkinkan database mengakses data dengan efisien, sedangkan indeks yang dipilih secara sembarangan dapat menambah overhead tanpa manfaat yang signifikan. Berikut adalah penjelasan tentang mengapa penting untuk memilih indeks yang tepat.

Contoh Situasi di Mana Indexing Harus Tepat Sasaran

1. Mengindeks Kolom yang Sering Diakses Bersamaan

Misalkan kita memiliki tabel promo dengan kolom start_date dan end_date. Query sering kali mencari data berdasarkan kedua kolom ini bersama-sama:

SELECT * FROM promo WHERE start_date >= '2024-01-01' AND end_date <= '2024-12-31';

Jika kita membuat indeks terpisah pada kolom start_date dan end_date:

CREATE INDEX idx_start_date ON promo(start_date);
CREATE INDEX idx_end_date ON promo(end_date);

Query di atas mungkin tidak memanfaatkan indeks secara optimal karena indeks terpisah tidak efisien untuk pencarian dengan dua kondisi tersebut. Indeks gabungan (composite index) akan lebih efisien:

CREATE INDEX idx_promo_dates ON promo(start_date, end_date);

Dengan indeks gabungan ini, database dapat langsung mencari data berdasarkan kombinasi start_date dan end_date, mengurangi jumlah data yang harus dipindai.

2. Menggunakan B-Tree vs. Hash Index

Indeks B-Tree berguna untuk pencarian rentang dan pengurutan, sedangkan indeks Hash berguna untuk pencarian nilai yang tepat. Misalkan kita memiliki tabel customers dan sering mencari berdasarkan email:

SELECT * FROM customers WHERE email = "ganteng@gmail.com";

Jika kita menggunakan indeks B-Tree:

CREATE INDEX idx_customer_email_btree ON customers(email);

Ini mungkin berfungsi dengan baik, tetapi Hash Index bisa lebih efisien untuk pencarian nilai yang tepat:

CREATE INDEX idx_customer_email_hash ON customers USING HASH (email);

Hash Index memungkinkan pencarian langsung ke lokasi data yang relevan berdasarkan nilai hash, yang biasanya lebih cepat untuk pencarian nilai yang tepat.

3. Pencarian Query LIKE

Misalkan kita memiliki tabel articles dengan kolom content yang berisi teks panjang. Kita sering melakukan pencarian teks dengan klausa LIKE:

SELECT * FROM articles WHERE content LIKE '%database%';

Menggunakan indeks B-Tree pada kolom content:

CREATE INDEX idx_content ON articles(content);

Ini mungkin tidak efisien untuk pencarian LIKE karena indeks B-Tree tidak dioptimalkan untuk pencarian substring dalam teks panjang. Sebaliknya, Full-Text Index lebih tepat:

CREATE FULLTEXT INDEX idx_content_fulltext ON articles(content);

Full-Text Index memungkinkan pencarian kata atau frase dalam teks panjang lebih efisien dibandingkan dengan indeks B-Tree.

Kekurangan Indeks

1. Overhead Penyimpanan: Indeks membutuhkan ruang penyimpanan tambahan.
2. Overhead Insert, Delete, dan Update: Operasi insert, delete, dan update data pada tabel yang diindeks bisa lebih lambat karena sistem basis data harus memperbarui indeks juga.