Kriteria Kolom yang Cocok Dijadikan Index

Berikut adalah kriteria lengkap kolom yang sebaiknya dipertimbangkan untuk dijadikan index:

1. Sering Digunakan dalam Klausa WHERE: Kolom yang sering muncul dalam kondisi pencarian (WHERE), baik secara langsung maupun dalam klausa JOIN. Index pada kolom ini akan mempercepat proses penyaringan data.

   • Contoh: Kolom customer_id pada tabel orders sering digunakan untuk mencari pesanan berdasarkan pelanggan tertentu.

2. Sering Digunakan dalam Klausa JOIN: Kolom yang digunakan sebagai kunci penghubung antar tabel dalam operasi JOIN. Index pada kolom-kolom ini secara signifikan mempercepat proses penggabungan data.

   • Contoh: Kolom product_id yang menghubungkan tabel orders dan products.

3. Sering Digunakan dalam Klausa ORDER BY atau GROUP BY: Meskipun tidak secara langsung mempercepat pencarian, index pada kolom yang sering digunakan untuk pengurutan (ORDER BY) atau pengelompokan (GROUP BY) dapat membantu database melakukan operasi ini lebih efisien, terutama jika index sudah terurut.

   • Contoh: Kolom order_date pada tabel orders sering digunakan untuk mengurutkan pesanan berdasarkan tanggal.

4. Kolom dengan Kardinalitas Tinggi: Kardinalitas mengacu pada jumlah nilai unik dalam sebuah kolom. Kolom dengan kardinalitas tinggi (banyak nilai unik) lebih cocok untuk di-index karena index akan lebih efektif dalam mempersempit hasil pencarian.

   • Contoh: Kolom email pada tabel customers cenderung memiliki kardinalitas tinggi karena setiap pelanggan biasanya memiliki alamat email yang berbeda.

5. Kolom yang Digunakan dalam Kondisi Range (>, <, >=, <=, BETWEEN): Index jenis tertentu (seperti B-tree) sangat efektif dalam menangani kueri dengan kondisi rentang.

   • Contoh: Kolom order_amount pada tabel orders sering digunakan dalam pencarian pesanan dengan nilai tertentu (misalnya, pesanan dengan nilai di atas Rp 100.000).

6. Kolom yang Didefinisikan sebagai Kunci Utama (Primary Key) atau Kunci Unik (Unique Key): Sistem database secara otomatis membuat index untuk kolom yang didefinisikan sebagai kunci utama atau kunci unik untuk memastikan keunikan dan mempercepat pencarian berdasarkan kunci tersebut.

7. Kolom yang Sering Digunakan dalam Fungsi Agregasi: Dalam beberapa kasus, index dapat membantu mempercepat kueri yang melibatkan fungsi agregasi seperti MIN(), MAX(), terutama jika dikombinasikan dengan klausa GROUP BY.

Jenis-Jenis Index

Berikut adalah beberapa jenis index yang umum digunakan dalam database:

1. B-tree Index (Balanced Tree): Ini adalah jenis index yang paling umum dan default di banyak sistem database. B-tree sangat efisien untuk berbagai jenis kueri, termasuk pencarian berdasarkan nilai tunggal, rentang, dan awalan (untuk kolom string).

2. Hash Index: Hash index menggunakan fungsi hash untuk memetakan nilai kolom ke lokasi fisik dalam tabel. Mereka sangat cepat untuk pencarian berdasarkan nilai yang tepat (=), tetapi tidak efisien untuk kueri rentang atau pengurutan. Hash index umumnya tidak sepopuler B-tree karena keterbatasannya.

3. Full-Text Index: Dirancang khusus untuk mencari teks dalam kolom teks besar (seperti artikel atau deskripsi produk). Full-text index memungkinkan pencarian kata kunci, frasa, dan operator boolean dalam teks.

4. Spatial Index: Digunakan untuk mengoptimalkan kueri spasial yang melibatkan data geografis (seperti mencari semua restoran dalam radius tertentu). Contoh jenis spatial index adalah R-tree.

5. XML Index: Digunakan untuk mengoptimalkan kueri pada data XML yang disimpan dalam kolom database.

6. Clustered Index: Menentukan urutan fisik data dalam tabel. Hanya ada satu clustered index per tabel. Biasanya, primary key menjadi clustered index secara default. Keuntungan clustered index adalah pengambilan data yang lebih cepat jika kueri mencakup kolom clustered index.

7. Non-Clustered Index (Secondary Index): Index ini menyimpan salinan nilai kolom yang di-index dan pointer ke baris data yang sebenarnya. Sebuah tabel dapat memiliki banyak non-clustered index. Ketika kueri menggunakan non-clustered index, database mungkin perlu melakukan key lookup (mencari data aktual dalam tabel) setelah menemukan pointer index.

Ilustrasi Penyimpanan Index B-tree

Mari kita ilustrasikan bagaimana index B-tree disimpan. Bayangkan kita memiliki tabel users dengan kolom age yang kita index menggunakan B-tree.

Struktur Dasar B-tree:

B-tree adalah struktur pohon seimbang yang memungkinkan pencarian, penyisipan, dan penghapusan data secara efisien. Setiap node dalam B-tree berisi beberapa kunci (nilai kolom yang di-index) dan pointer ke node anak.

Contoh Sederhana:

Misalkan kita memiliki data usia sebagai berikut: 20, 25, 30, 15, 22, 28, 35. Index B-tree untuk kolom age mungkin terlihat seperti ini:

                                   [25]
                                  /    \
                         [15, 20]      [28, 30, 35]
                        /      \        /   |    \
                      [*]      [*]    [*]  [*]   [*]

Penjelasan:

• Root Node: Node paling atas ([25]) berfungsi sebagai titik awal pencarian.

• Internal Nodes: Node-node di tengah ([15, 20] dan [28, 30, 35]) berisi nilai-nilai index dan pointer ke node anak yang berisi rentang nilai yang lebih spesifik.

• Leaf Nodes: Node-node paling bawah ([*]) pada implementasi B+tree (varian B-tree yang umum digunakan dalam database) biasanya menyimpan nilai-nilai index yang sebenarnya dan pointer ke baris data yang sesuai dalam tabel. Pada B+tree, leaf nodes juga seringkali terhubung satu sama lain dalam bentuk linked list untuk memudahkan operasi rentang.

Proses Pencarian (Contoh: Mencari Pengguna dengan Usia 22):

1. Mulai dari root node [25]. Karena 22 < 25, kita bergerak ke pointer anak kiri.

2. Kita berada di node [15, 20]. Karena 22 > 20, kita bergerak ke pointer anak kanan dari 20 (yang akan mengarah ke leaf node yang berisi nilai antara 20 dan 25).

3. Kita mencapai leaf node yang berisi nilai 22 dan pointer ke baris data pengguna dengan usia 22 dalam tabel users.

Penyimpanan pada Disk:

Setiap node dalam B-tree biasanya sesuai dengan satu atau beberapa blok data pada disk. Hal ini dirancang untuk meminimalkan jumlah operasi I/O disk yang mahal selama pencarian. Karena B-tree seimbang, kedalaman pohon tetap relatif kecil bahkan untuk tabel yang sangat besar, sehingga pencarian tetap cepat.

B+tree:

Sebagian besar sistem database menggunakan varian B-tree yang disebut B+tree. Dalam B+tree:

• Semua nilai index disimpan di leaf nodes.

• Internal nodes hanya berisi kunci pemisah untuk mengarahkan pencarian.

• Leaf nodes terhubung dalam linked list, yang sangat efisien untuk kueri rentang (misalnya, mencari semua pengguna dengan usia antara 20 dan 30).

Ilustrasi B+tree:

                                       [25]
                                      /    \
                             [15, 20]      [28, 35]
                            /      \        /      \
           [10, 15] -> [15, 20] -> [20, 22] -> [28, 30] -> [30, 35] -> [35, 40]
             * * * * * * *

Pada ilustrasi B+tree di atas, leaf nodes (yang ditandai dengan *) berisi nilai-nilai index yang sebenarnya dan pointer ke data, serta pointer ke leaf node berikutnya. Internal nodes hanya berisi nilai-nilai pemisah.

Dengan memahami kriteria pemilihan kolom untuk index, jenis-jenis index yang tersedia, dan bagaimana index B-tree (atau B+tree) disimpan, Anda dapat merancang skema index database yang lebih efektif untuk meningkatkan kinerja kueri. Ingatlah bahwa penambahan index juga memiliki biaya (peningkatan ukuran database dan waktu penulisan), jadi penting untuk menyeimbangkan manfaat dan biaya index.