Index: clustered, non-clustered, composite, covering
Ringkasan satu halaman. Dari Lesson 0022. Skrip lab: dotnet/lab/0022-index.sql.
Clustered vs non-clustered [2]
| Clustered | Non-clustered | |
|---|---|---|
| Analogi | buku telepon: isinya sendiri urut | indeks di belakang buku → nomor halaman |
| Leaf | data pages, semua kolom | key + row locator (+ kolom INCLUDE) |
| Per tabel | 1 | banyak |
| Row locator | — | clustered key (heap: RID) |
| Default dari | PRIMARY KEY | UNIQUE, CREATE INDEX |
- Tanpa clustered index = heap (tidak berurutan).
- Clustered key ideal: sempit, unik, selalu naik, tidak berubah, NOT NULL →
int IDENTITY. GUID acak: 16 byte, tidak naik. Clustered key ikut di setiap non-clustered index. [1] - FOREIGN KEY tidak otomatis ber-index.
Membaca plan [3]
| Operator | Artinya |
|---|---|
| Clustered Index Scan | baca seluruh tabel |
| Index Seek | lompat langsung ke rentang key |
| Key Lookup (+ Nested Loops) | ambil kolom yang tidak ada di index dari clustered index, sekali per baris |
| RID Lookup | sama, untuk heap |
| Sort | urutan index tidak cocok dengan ORDER BY |
Banyak baris × key lookup > satu scan → optimizer memilih scan (tipping point). Obatnya covering index.
Covering + composite [1] [7]
-- SELECT Name, Price FROM dbo.Product WHERE CategoryId = @c ORDER BY Price DESC
CREATE INDEX IX_Product_CategoryId_Price
ON dbo.Product (CategoryId, Price DESC) -- equality dulu, lalu range/ORDER BY
INCLUDE (Name); -- hanya diambil → INCLUDE
-- Id tidak perlu: clustered key otomatis ikut- Key: kolom WHERE / JOIN / ORDER BY / GROUP BY. INCLUDE: kolom yang hanya di SELECT (leaf saja, tidak diurutkan, tidak dihitung ke batas 16 kolom / 900 byte).
- Leftmost prefix:
(A, B)bisa seek untukA,A + B; tidak untukBsaja. - Covering = semua kolom query ada di index → Index Seek tanpa Key Lookup. Jangan memasukkan terlalu banyak kolom.
Kapan index merugikan [1]
- Setiap INSERT/UPDATE/DELETE/MERGE ikut memperbarui index → tabel yang sering ditulis: index sedikit dan sempit.
- Jangan membuat index spekulatif "supaya optimizer punya pilihan"; pantau
sys.dm_db_index_usage_stats, hapus yang tidak dipakai. - Selektivitas rendah (sedikit nilai berbeda, mis. 90% baris di satu Category) → scan tetap lebih murah.
- Tabel kecil → scan bisa lebih cepat daripada menelusuri index.
- Storage: setiap index = salinan kolomnya (
sys.dm_db_partition_stats.used_page_count).
Mengukur [4] [5]
SET STATISTICS IO, TIME ON;
-- query sebelum index → CREATE INDEX → query yang sama lagi
SET STATISTICS IO, TIME OFF;Bandingkan logical reads (page dari cache, 1 page = 8 KiB [6]), bukan milidetik. Lihat juga actual execution plan.
Jawaban 60–90 detik
Clustered index = tabelnya sendiri, urut menurut key, satu per tabel, default dari PK (idealnya int IDENTITY).
Non-clustered = struktur terpisah berisi key + clustered key sebagai penunjuk; kolom lain diambil lewat key lookup.
Untuk query lambat: ukur logical reads + plan, buat composite index equality dulu lalu kolom ORDER BY, kolom yang hanya diambil di INCLUDE (covering, tanpa key lookup), ukur lagi.
Risiko: biaya tulis dan storage; jangan index spekulatif, jangan index kolom berselektivitas rendah, hapus yang tak terpakai.
Sumber: [1] Index architecture and design guide · [2] Clustered and nonclustered indexes described · [3] Showplan operators · [4] SET STATISTICS IO · [5] SET STATISTICS TIME · [6] Pages and extents · [7] CREATE INDEX