Foto oleh panumas nikhomkhai via Pexels

Index adalah struktur data tambahan yang membantu SQL Server menemukan baris tertentu tanpa harus membaca seluruh tabel, mirip seperti daftar isi pada buku. SQL Server menyediakan dua jenis index utama: clustered index dan non-clustered index, yang bekerja dengan cara fundamental berbeda.

Clustered index menentukan urutan fisik penyimpanan data di tabel itu sendiri, sehingga setiap tabel hanya boleh memiliki satu clustered index. Non-clustered index, sebaliknya, disimpan terpisah dari data tabel dan berisi salinan kolom yang di-index beserta pointer menuju baris aslinya, dan satu tabel boleh memiliki banyak non-clustered index.

Memahami perbedaan ini penting karena memilih kolom yang salah untuk clustered index bisa memengaruhi performa seluruh operasi pada tabel tersebut, bukan hanya query yang memakai index itu secara langsung.

Clustered Index Menentukan Urutan Fisik Data

Clustered index pada kolom PRIMARY KEY

CREATE CLUSTERED INDEX IX_Orders_OrderID ON Orders(OrderID); -- Biasanya otomatis dibuat bersama PRIMARY KEY -- Expected: data pada tabel Orders secara fisik -- tersimpan terurut berdasarkan OrderID di disk
Karena clustered index menentukan urutan fisik data, biasanya dibuat pada kolom PRIMARY KEY yang bersifat unik dan jarang berubah, seperti ID auto-increment. Memilih kolom yang sering berubah nilainya sebagai clustered index bisa menyebabkan SQL Server harus memindahkan fisik baris tersebut ke lokasi lain setiap kali nilainya berubah, yang cukup mahal dari sisi performa.

Non-Clustered Index sebagai Struktur Terpisah

Non-clustered index disimpan terpisah dari tabel

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Orders(CustomerID); -- Struktur index ini berisi CustomerID + pointer ke baris asli, -- disimpan TERPISAH dari data tabel Orders itu sendiri
Non-clustered index menyimpan salinan kolom yang di-index beserta pointer (disebut row locator) menuju lokasi baris aslinya di clustered index atau heap. Karena strukturnya yang terpisah, satu tabel bisa memiliki banyak non-clustered index sekaligus, masing-masing dioptimasi untuk pola query yang berbeda-beda.

Key Lookup Saat Index Tidak Mencakup Semua Kolom

Key Lookup terjadi saat kolom yang diminta tidak ada di index

-- Index hanya mencakup CustomerID, namun query butuh TotalAmount juga SELECT OrderID, TotalAmount FROM Orders WHERE CustomerID = 100; -- SQL Server akan melakukan Index Seek pada IX_Orders_CustomerID, -- lalu Key Lookup kembali ke tabel asli untuk mengambil TotalAmount
Ketika query meminta kolom yang tidak tercakup dalam non-clustered index, SQL Server harus melakukan langkah tambahan bernama Key Lookup untuk kembali ke tabel asli dan mengambil kolom yang hilang tersebut. Jika Key Lookup terjadi untuk banyak baris sekaligus, langkah ini bisa menjadi sangat mahal dan kadang membuat SQL Server memilih Table Scan ketimbang memakai index sama sekali.

Memilih Kolom yang Tepat untuk Clustered Index

Kolom sequential lebih ideal untuk clustered index

-- Kurang ideal: GUID acak sebagai clustered index CREATE TABLE Produk (ProductID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, ...); -- Lebih baik: kolom yang terus bertambah (sequential) CREATE TABLE Produk (ProductID INT IDENTITY(1,1) PRIMARY KEY, ...);
Kolom yang nilainya acak seperti GUID (NEWID()) sebagai clustered index bisa menyebabkan fragmentasi tinggi karena baris baru terus disisipkan di tengah-tengah struktur data yang sudah terurut, bukan di akhir. Kolom yang bertambah secara berurutan seperti IDENTITY jauh lebih ramah terhadap performa insert karena baris baru selalu ditambahkan di akhir struktur.

Dampak Fragmentasi Index terhadap Clustered dan Non-Clustered Index

Memeriksa dan memperbaiki fragmentasi index dengan REBUILD/REORGANIZE

-- Memeriksa tingkat fragmentasi seluruh index pada tabel Orders SELECT i.name AS IndexName, s.avg_fragmentation_in_percent, s.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Orders'), NULL, NULL, 'LIMITED') s JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id; -- Contoh hasil: PK_Orders fragmentasi 42%, IX_Orders_CustomerID fragmentasi 8% -- Fragmentasi > 30% umumnya direbuild, 10-30% cukup di-reorganize ALTER INDEX PK_Orders ON Orders REBUILD; ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;

Clustered index menyimpan data tabel itu sendiri terurut berdasarkan kolom key-nya, sehingga ketika baris baru disisipkan di tengah urutan logis (misalnya pada kolom yang bukan selalu bertambah seperti GUID acak), SQL Server harus melakukan page split yang memecah satu halaman data menjadi dua agar baris baru muat, dan proses inilah penyebab utama fragmentasi pada clustered index.

Non-clustered index mengalami mekanisme fragmentasi yang serupa karena strukturnya juga berupa B-tree terpisah yang menyimpan kolom key beserta pointer ke baris data, sehingga pola insert dan update yang acak pada kolom yang di-index akan memicu page split yang sama, meskipun dampaknya terhadap ukuran index non-clustered biasanya lebih kecil dibanding pada clustered index yang memuat seluruh kolom tabel.

Fragmentasi yang tinggi membuat SQL Server harus membaca lebih banyak halaman fisik dari disk untuk jumlah data logis yang sama, karena halaman-halaman tersebut tidak lagi berurutan dan terisi tidak penuh akibat page split; pemeliharaan rutin dengan REORGANIZE untuk fragmentasi ringan atau REBUILD untuk fragmentasi berat membantu menyusun ulang struktur fisik index agar pembacaan sekuensial kembali efisien.

Filtered Index untuk Subset Data yang Sering Diakses

Filtered index hanya menyimpan baris yang memenuhi kondisi WHERE tertentu

-- Tanpa filtered index: index mencakup SELURUH baris termasuk yang jarang dicari CREATE INDEX IX_Orders_Status ON Orders(Status); -- Filtered index: hanya mengindex baris dengan Status tertentu yang sering dicari CREATE INDEX IX_Orders_PendingOnly ON Orders(OrderDate, CustomerID) WHERE Status = 'Pending'; -- Index ini jauh lebih kecil karena hanya memuat baris Status='Pending' -- Query yang cocok memanfaatkan filtered index ini SELECT OrderDate, CustomerID FROM Orders WHERE Status = 'Pending' AND OrderDate > '2026-01-01'; -- Execution plan memakai Index Seek pada IX_Orders_PendingOnly, bukan index penuh

Filtered index adalah non-clustered index yang didefinisikan dengan klausa WHERE, sehingga hanya baris yang memenuhi kondisi tersebut yang dimasukkan ke dalam struktur index, berbeda dari index biasa yang selalu mencakup seluruh baris tabel. Pendekatan ini sangat berguna ketika query yang sering dijalankan hanya peduli pada subset kecil dari keseluruhan data, misalnya hanya order dengan status 'Pending' dari jutaan total baris order.

Karena ukurannya yang jauh lebih kecil dibanding index penuh pada kolom yang sama, filtered index membutuhkan lebih sedikit ruang penyimpanan dan lebih sedikit biaya pemeliharaan saat INSERT atau UPDATE, sebab hanya baris yang memenuhi kondisi filter yang memicu pembaruan index tersebut. Statistik yang dikumpulkan untuk filtered index juga lebih akurat untuk subset datanya dibanding statistik pada index penuh yang mencerminkan keseluruhan distribusi data.

Kondisi WHERE pada filtered index harus cocok atau menjadi subset dari kondisi WHERE pada query agar optimizer mau memakainya, dan kondisi tersebut hanya boleh memakai operator perbandingan sederhana seperti =, <>, >, <, IS NULL, dan IS NOT NULL terhadap kolom yang tidak nullable secara kompleks, sehingga filtered index paling cocok untuk kasus seperti memisahkan baris aktif dari non-aktif, atau baris dengan nilai NULL dari yang tidak NULL pada kolom yang jarang diisi.

Index Intersection Saat Dua Index Sempit Digabung Optimizer

Optimizer menggabungkan hasil dua index sempit lewat index intersection

-- Dua index terpisah, masing-masing sempit pada satu kolom CREATE INDEX IX_Orders_Status ON Orders(Status); CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID); SELECT OrderID FROM Orders WHERE Status = 'Pending' AND CustomerID = 100; -- Pada kondisi tertentu, execution plan menunjukkan Index Seek pada KEDUA index -- lalu digabung lewat operator Hash Match atau Merge Join (index intersection), -- alih-alih memindai salah satu index saja

Index intersection terjadi ketika optimizer memutuskan memakai lebih dari satu non-clustered index yang masing-masing sempit untuk menjawab satu query, lalu menggabungkan baris hasil seek dari tiap index tersebut melalui operator join seperti Hash Match, dibanding hanya mengandalkan satu index dan melakukan filter tambahan di memori atau Key Lookup.

Pendekatan ini jarang dipilih optimizer dibanding satu composite index yang mencakup seluruh kolom filter, karena menggabungkan dua index tetap membutuhkan operasi join tambahan yang punya biaya tersendiri. Munculnya index intersection pada execution plan sering menjadi sinyal bahwa membuat satu composite index pada kombinasi kolom yang sama, misalnya (Status, CustomerID), kemungkinan akan menghasilkan rencana eksekusi yang lebih efisien dibanding dua index terpisah tersebut. Memeriksa tab properti pada operator join di execution plan membantu memastikan apakah index intersection benar-benar sedang terjadi, sebelum memutuskan untuk merancang ulang index yang ada.

Kesalahan Umum yang Sering Terjadi

Membuat Terlalu Banyak Non-Clustered Index

Setiap non-clustered index menambah overhead saat INSERT, UPDATE, dan DELETE karena index tersebut juga harus diperbarui, sehingga tabel dengan terlalu banyak index justru bisa memperlambat operasi tulis secara signifikan.

Memilih Kolom yang Sering Berubah sebagai Clustered Index

Clustered index pada kolom yang nilainya sering di-update akan memaksa SQL Server memindahkan baris secara fisik setiap kali nilai berubah, berbeda dengan update pada kolom biasa yang tidak memengaruhi urutan fisik data.

Tidak Menyertakan Kolom yang Sering Diminta dalam Index

Non-clustered index yang tidak mencakup kolom yang sering diminta bersamaan (melalui INCLUDE) akan terus memicu Key Lookup yang mahal, padahal masalah ini bisa dihindari dengan sedikit penyesuaian definisi index.

Kapan Pendekatan Ini Tidak Cocok Dipakai

Clustered index memberikan performa terbaik untuk query yang mengambil data berdasarkan urutan atau range pada kolomnya, namun hanya bisa ada satu per tabel sehingga pemilihannya harus dipikirkan matang di awal desain.

Non-clustered index memberi fleksibilitas untuk mengoptimasi banyak pola query berbeda sekaligus, namun setiap index tambahan membawa biaya pemeliharaan saat data berubah, sehingga jumlahnya perlu diseimbangkan dengan frekuensi baca dibanding tulis pada tabel tersebut.

Ringkasan

Clustered index menentukan urutan fisik data dan hanya boleh satu per tabel, sementara non-clustered index adalah struktur tambahan terpisah yang bisa dibuat banyak untuk mendukung berbagai pola query. Pilih kolom clustered index yang stabil dan sequential, dan gunakan non-clustered index secukupnya sesuai pola akses data yang benar-benar sering dipakai aplikasi.

Seri Belajar SQL Server:

← Sebelumnya: Membaca Execution Plan untuk Optimasi Query

→ Selanjutnya: Covering Index dan Dampaknya pada Performa