Clustered Index vs Non Clustered Index pada SQLSERVER

Indexing merupakan salah satu cara untuk mempercepat kinerja dari sistem basis data.  Namun banyak dari kita yang belum memahami dengan baik index tersebut. Maksud saya dari belum memahami, yaitu dengan menggunakan misalnya kondisi default dari index. Misalnya saja, pada pengguna SQLSERVER maka secara default saat anda menggunakan SQLSERVER Management Studio untuk membuat tabel dengan sebuah primary key, maka kolom yang dijadikan primary key tersebut akan dibuat sebuat index clustered.  Padahal, kita mempunyai opsi lain yaitu non-clustered, lalu apa bedanya ? apa efek dari penggunaannya ?

Clustered Index dan Non Clustered Index

dalam situs sqlserver sebenarnya telah dijelaskan dengan umum apa bedanya [1],[2]. Struktur kedua index tersebut sebenarnya mirip , yaitu menggunakan B+Tree Index [5]. hanya saja terdapat perbedaan yang sangat fundamental pada leaf node masing – masing jenis index tersebut.

 sumber (https://msdn.microsoft.com/en-us/library/ms177443.aspx)

pada gambar di atas adalah gambar index clustered yang saya ambil dari situs [1]. Pada gambar tersebut terlihat struktur standard index B+Tree  yang mana leaf node nya mengarah langsung ke data. Di situs yang lain, Pakde Markus Winand [3] memaparkan dengan lebih detail dan lebih jelas menurut saya. Menurut beliau, dengan menggunakan clustered index, sistem akan memaksa leaf node berurutan sesuai dengan urutan dari index nya, bahasa saya sih leaf node akan berjajar rapi. Sehingga jika saya bayangkan, bahwa dengan menggunakan clustered index ini, menjadikan model penyimpanan data kita menjadi tightly coupled dengan index, artinya tidak ada lagi pemisahan antara index dengan leaf node (atau data). Dengan begitu juga dapat dibayangkan bahwa sebuah tabel pasti tidak bisa memiliki lebih dari 1 clustered index.

berbeda dengan non clustered, dari namanya saja sudah dapat dibayangkan kalau itu merupakan lawan dari clustered :D.

Sumber (https://msdn.microsoft.com/en-us/library/ms177484.aspx)

Gambar di atas merupakan gambar struktur index non clustered yang saya ambil dari situs [2]. Dari gambar tersebut terlihat struktur terpisah menjadi 2 yaitu (root node + leaf node) dan data pages. Untuk (root node + leaf node) merupakan struktur non clustered indexnya sendiri dan untuk data pages ini bisa langsung data (heap) atau bahkan clustered index. Artinya bisa saya katakan jika di dalam tabel tersebut tidak ada clusterd index, maka data pages ini akan berupa heap structure yang menyimpan data ( bayangkan saja seperti linked list [4] ). Namun jika dalam tabel tersebut terdapat clustered index, maka data pages nya akan mengacu pada clustered index tersebut. Dari gambar ini dapat kita lihat jika non clustered index lebih bersifat loosly coupled, yang mana keberadaan index tidak akan mempengaruhi bagaimana data tersebut disimpan.

Efek Penggunaan Clustered Index pada Index non Clustered

Pakde Markus Winand [3], memiliki gambar yang lebih ciamik lagi untuk menggambarkan keadaan index yang menggunakan clustered atau tidak, seperti yang terlihat pada gambar – gambar di bawah ini.

Sumber (http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key)

pada gambar di atas , merupakan contoh struktur non clustered index pada tabel yang tidak memiliki clustered index. Sehingga pada leaf node nya akan memiliki referensi RID ke data pages nya. Hal ini merupakan cost tambahan pada pencarian data karena akan melakukan RID lookup ke data untuk mencari kolom – kolom yang dibutuhkan pada query yang diinginkan.

Sumber (http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key)

Sedangkan pada gambar di atas merupakan contoh non clustered index pada sebuah tabel yang memiliki clustered index. Perbedaannya adalah leaf node pada non clustered index tidak mengacu pada RID lagi namun kepada ID primary key dari clustered index. Sehingga tidak terjadi RID lookup namun key lookup. Mengapa keadaan ini terjadi, mengapa tidak tetap menggunakan RID ?. Hal ini karena sifat tightly coupled dari clustered index terhadap data tersebut. Dengan sifat tersebut maka data akan diatur sedemikian rupa untuk mengikuti aturan dari clustered index yang berbasis b+tree. Jika terdapat penambahan data atau pengurangan data, maka sebuah b+ tree pasti akan melakukan penyesuaian – penyesuaian agar tree yang ada tetap balance, karena dengan keadaan balance tree tersebut lebih menjamin kecepatan dalam melakukan pengaksesan data ( tree traversal ). Hal itu berarti juga data yang tersimpan mungkin saja akan mengalami pergerseran tempat disesuaikan dengan keadaan clustered indexnya. Sehingga RID tidak dapat dijadikan patokan tempat bernaung sebuah data, karena data dapat berpindah sesuai dengan keadaan terkini. Hal ini disebut oleh pakde MarkusWinand sebagai Living Creature.  Dan hal tersebut berarti biaya dari non clustered index akan ditambah biaya pencarian clustered index. Hal itu disebut sebagai clustered index penalty oleh pakde MarkusWinand.

Uji Clustered Index vs Non Clustered Index

Setelah membaca beberapa paparan di atas, mungkin anda melihat tendensi saya sedikit memojokkan clustered index, sebenarnya tidak demikian namun memang dikarenakan saya melihat clustered index memiliki kemampuan yang baik namun pada kasus kasus tertentu.

Secara umum , guideline dan kelebihan clustered index dan non clustered index dapat dilihat pada [6], [7]. Namun guildeline itu bagi saya masih sangat ambigu dan tidak menjawab dengan tegas dimana letak kelebihan masing – masing model index. Untuk itu pada post kali ini kita akan menggunakan percobaan kita sendiri. Kita akan membuat 2 buah tabel yang identik secara kolom namun berbeda secara penggunaan clustered index.

persiapan pembuatan tabel

untuk tahap awal mari kita membuat tabel seperti berikut :

	CREATE TABLE [dbo].[person_cluster](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nama] [varchar](50) NOT NULL,
	[umur] [int] NOT NULL,
	CONSTRAINT [PK_person_cluster] PRIMARY KEY CLUSTERED
	(
	[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	CREATE TABLE [dbo].[person_non_cluster](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nama] [varchar](50) NOT NULL,
	[umur] [int] NOT NULL,
	CONSTRAINT [PK_person_non_cluster] PRIMARY KEY NONCLUSTERED
	(
	[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [person_cluster_umur] ON [dbo].[person_cluster]
	(
	[umur] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [person_non_cluster_umur] ON [dbo].[person_non_cluster]
	(
	[umur] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

view rawcreate_tables_clustered_vs_non_clustered.sql hosted with  by GitHub

pada Script di atas, kita akan membuat 2 tabel yang memiliki kolom :

1.    ID : primary Key, auto increment integer

2.    Nama : nama person varchar(50)

3.    Umur : umur person int

kedua tabel memiliki definisi index constraint PK yang berbeda , salah satu menggunakan constraint CLUSTERED yang merupakan settingan default dari sqlserver, sisanya menggunakan constraint PK yang NON CLUSTERED.

selain itu kedua tabel ditambahkan index NON CLUSTERED pada kolom umur.

Setelah itu, data [8] yang berjumlah 700.000 record dimasukkan ke dalam kedua tabel.

Percobaan pada constraint Primary Keys

Percobaan menggunakan keyword  range ” > “

untuk percobaan ini kita melakukan 2 query .

	use clustered_vs_non_clustered
	set statistics IO on
	SET STATISTICS TIME ON
	select top 10000 * from person_cluster
	where id > 21
	order by id

view rawtest_1_cluster.sql hosted with  by GitHub

hasil dari query tersebut adalah :

sedangkan untuk query  :

	use clustered_vs_non_clustered
	set statistics IO on
	SET STATISTICS TIME ON
	select top 10000 * from person_non_cluster
	where id > 21
	order by id

view rawtest_1_non_cluster.sql hosted with  by GitHub

hasilnya adalah :

dari hasil di atas beberapa variabel yang dapat kita amati adalah :

1.    Logical Read : Seberapa banyak data yang akan dibaca secara logic bukan secara real ( karena secara real sangat dipengaruhi oleh cache)

2.    CPU Time : seberapa lama CPU time yang dibutuhkan

3.    Elapsed Time : seberapa lama Waktu yang dibutuhkan

Jika kita melihat hasilnya, dapat dilihat bahwa untuk index non clustered akan menghasilkan logical reads yang lebih banyak dibandingkan dengan index clustered. hal ini dikarenakan adanya biaya RID lookup pada masing  – masing item pada leaf node di index non clustered (silakan lihat lagi gambar index non clustered) . Berbeda dengan index clustered yang datanya telah terintegrasi dengan struktur B+ Tree, maka tidak perlu banyak logical reads untuk mendapatkan data tersebut. Index clustered hanya membutuhkan titik leafnode awal untuk start dan berakhir pada 10000 (select top 10000) data dengan cara direct linked list traversal tidak lagi menggunakan tree traversal. Dari sini dapat kita lihat kelebihan dari index clustered.

Percobaan dengan menggunakan keyword “between” (range)

untuk Query

	use clustered_vs_non_clustered
	set statistics IO on
	SET STATISTICS TIME ON
	select * from person_cluster
	where id between 1 and 54
	order by id

view rawtest_3_cluster.sql hosted with  by GitHub

hasilnya :

sedangkan untuk Query :

	use clustered_vs_non_clustered
	set statistics IO on
	SET STATISTICS TIME ON
	select * from person_non_cluster
	where id between 1 and 54
	order by id

view rawtest_3_non_cluster.sql hosted with  by GitHub

hasilnya :

untuk hasil dengan keyword between, kembali primary keys (PK) dengan index clustered sangat unggul dari PK dengan index non clustered. Hal ini jelas memang keunggulan dari index clustered yang tidak memiliki RID lookup sehingga hanya memerlukan 1 kali tree traversal untuk menemukan data dengan nilai terendah dan dilanjutkan dengan linked list walking pada data tersebut sampai menemukan nilai tertinggi, maka dengan itu memang logical reads nya jadi sangat rendah yaitu 3 , yang mana asumsinya adalah hal ini merupakan tree traversal.  Berbeda dengan index non clustered yang mana memiliki logical reads sampai 57. Hitungan – hitungan kasar merupakan ( jml hop pada tree traversal + jml RID lookup) => (3 (asumsi tree travelsal sama dengan clustered index) + 54 (jml data)). Hal ini berarti memang untuk tiap item data akan dilakukan RID lookup pada data, hal ini yang menyebabkan logical reads menjadi tinggi.

Percobaan pada index non clustered

Percobaan menggunakan keyword  range ” > “

pada percobaan ini kita ingin melihat apa ada efek penggunaan index clustered pada index non clustered dalam 1 buah tabel .

untuk query

	use clustered_vs_non_clustered
	set statistics IO on
	SET STATISTICS TIME ON
	select top 10000 * from person_cluster
	where umur > 25
	order by umur

view rawtest_2_cluster.sql hosted with  by GitHub

hasilnya :

sedangkan untuk query

	use clustered_vs_non_clustered
	set statistics IO on
	SET STATISTICS TIME ON
	select top 10000 * from person_non_cluster
	where umur > 25
	order by umur

view rawtest_2_non_cluster.sql hosted with  by GitHub

hasilnya :

dari hasil tersebut dapat dilihat bahwa clustered index sangat mempengaruhi non clustered index dalam 1 tabel. Pada tabel yang memiliki clustered index, nilai logical reads bisa sampai 3 kali lipat dari tabel yang tidak memiliki clustered index. Hal ini yang bisa disebut sebagai clustered index penalty. Karena leaf node index non clustered tidak langsung mengarah pada physical address dari data (RID) namun leaf node akan berhubungan dengan key dari index clustered. Dengan kata lain kita akan punya 2 kali tree traversal untuk mendapatkan sebuah record.

Diskusi ….

Dari percobaan di atas, dapat dilihat penggunaan clustered index pada tabel sangat powerfull untuk range query pada kolom yang dijadikan index tersebut. Hal ini dikarenakan sifat dari clustered index yang terintegrasi dengan data sehingga data menjadi terurut dan teratur sesuai aturan index tersebut. Di lain sisi , non clustered index menggunakan RID lookup yang menjadi titik lemah saat query range ini.

Namun dengan adanya clustered index pada sebuah tabel, membuat index lain (pada kolom lain) yang pastinya non clustered terpengaruh. Hasil ujicoba memperlihatkan bahwa tabel dengan PK non clustered akan mengungguli tabel dengan PK clustered. Hal ini dikarenakan adanya clustered index penalty yang membuat table dengan PK clustered harus melakukan 2 tree traversal.

Sistem default dari SQLSERVER yang langsung menganggap PK adalah clustered perlu diwaspadai oleh pengguna, karena sangat mempengaruhi kinerja dari sistem selanjutnya. pertanyaan yang mungkin dapat membantu menentukan menurut saya adalah :

1.    Seberapa sering PK akan diquery range ?

2.    dan Seberapa banyak index non cluster lain yang akan diakses oleh sistem ?

jika bobot nya lebih banyak pada no 1, yaitu lebih ke arah PK maka memang lebih baik menggunakan clustered index, namun saya yakin bahwa akan lebih banyak kasus lebih menitik beratkan pada no 2, yang mana non clustered PK lebih prefer untuk digunakan. Mengapa demikian ? karena menurut pengetahuan saya, kebanyakan PK digunakan oleh sistem modern sekarang adalah sebagai surrogate key [9], yang mana sangat jarang untuk kita melakukan query range tersebut.

bagaimana dengan anda ?

Referensi :

[1] https://msdn.microsoft.com/en-us/library/ms177443.aspx

[2] https://msdn.microsoft.com/en-us/library/ms177484.aspx

[3] http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key

[4] https://en.wikipedia.org/wiki/Linked_list

[5] https://en.wikipedia.org/wiki/B%2B_tree

[6] https://msdn.microsoft.com/en-us/library/ms190639.aspx

[7] https://msdn.microsoft.com/en-us/library/ms179325.aspx

[8] https://github.com/radityopw/blog_files/blob/master/clustered%20vs%20non-clustered%20index%20sqlserver/data_person.csv

[9] https://en.wikipedia.org/wiki/Surrogate_key