Serial Indexing Cantik : Posisi Menentukan Prestasi….

Posisi Menentukan Prestasi

kalimat yang sering saya dengar saat mahasiswa dulu, dimana sering disebutkan saat mau ujian… banyak yang bilang posisi duduk saat ujian menentukan hasil ujian. Hahaha tentu saja hal itu tidak benar dan sangat tidak berdasar… ya kata gaulnya itu cuman “mitos”. Namun berbeda dengan indexing, hal ini bisa jadi benar lho… “mitos” yang menjadi nyata.

Setelah membaca post saya sebelumnya tentang clustered index vs non clustered index [1],  secara umum anda akan mengetahui bagaimana struktur index dan bagaimana index itu bekerja.

Langsung saja ke contoh nya ya, misalnya saja kita punya tabel dengan definisi sebagai berikut :

 

Dengan query di atas kita akan mendapatkan table bernama “[pt1] [pt2] [pt3] ([ptx])” yang memilki kolom :

  • nim : int : adalah nomor induk mahasiswa yang menjadi primary key (PK)
  • nama : varchar(100) : adalah nama mahasiswa.

 

selain itu juga ada tabel yang bernama “[all_pt]” yang merupakan gabungan dari ke tiga tabel [ptx] tersebut.

setelah itu kita lakukan pengisisan data dengan data dari [2] ke dalam tabel [pt1][pt2][pt3]. dan kita running query penggabungan pada table [all_pt] sebagai berikut :

 

setelah hal itu dilakukan, maka kita akan memiliki seluruh data mahasiswa dari pt1, pt2, pt3 di dalam sebuah tabel yang bernama all_pt. hal itu mengakibatkan kolom nim tidak menjadi hal yang unik lagi melainkan kolom nim dan kolom pt.

mari kita lakukan percobaan,

sebelum menjalankan query tersebut kita terlebih dahulu harus mengaktifkan actual execution plan pada sqlserver management studio [3] [4], seperti pada gambar di bawah ini :

2252_image2

(sumber : https://www.mssqltips.com/sqlservertutorial/2252/estimated-vs-actual-query-plan/ )

 

setelah kita mengaktifkan actual execution plan, maka kita akan dapatkan hasil sebagai berikut :

Screenshot_1.png

dari grafik di atas, dapat kita lihat database melakukan operasi table scan yang dilanjutkan dengan operasi parallelism. Yang menjadi sorot utama kita kali ini adanya operasi table scan yang berarti database melakukan pembacaan pada keseluruhan tabel tanpa menggunakan index sama sekali. Hal tersebut tentu sangat tidak kita inginkan.

Namun hal tersebut juga tidak aneh, karena saat mendefinisikan tabel all_pt, kita sama sekali tidak mendifinisikan index yang ada. Untuk itu mari kita definisikan index berdasarkan query di atas.

Pembuatan index di atas sudah sesuai dengan query yang melakukan query dengan melihat kolom pt terlebih dahulu lalu dilanjutkan dengan kolom nim. Setelah index dibuat mari kita coba query select

Maka akan didapatkan hasil sebagai berikut :

Screenshot_2.png

dari gambar dapat dilihat database telah menggunakan index_1 sebagai objek operasi index seek dan mengkombinasikan dengan RID Lookup. Hal ini tentunya sesuai ekspektasi kita dalam penggunaan index yang harapannya dapat memperbaiki kinerja dari query kita.

Untuk melakukan query berdasarkan Pt dan Nim tentu sudah dapat kita selesaikan, namun bagaimana dengan query berdasarkan Nim saja. Misalnya saja kita ingin melakukan list mahasiswa yang memiliki Nim = 40 seperti query :

maka akan kita dapatkan hasil sebagai berikut :

Screenshot_3.png

database tidak menggunakan index_1 secara maksimal, walaupun tetap menggunakan index tersebut. hal ini Dapat dilihat dari text hijau yang menandakan adanya missing index.

untuk hal ini kita dapat saja membuat index baru khusus berisi kolom Nim saja (seperti yang disarankan pada missing index), namun perlu untuk diingat , bahwa membuat index berarti menambah kompleksitas dalam hal proses insert, delete , update dari tabel. untuk itu jika bisa kita dapat menggunakan index yang telah ada sebaik mungkin.

database tidak menggunakan index_1 dengan maksimal karena dalam index_1 kolom pertama yang terindex adalah kolom Pt lalu diikuti kolom Nim. Yang dapat kita lakukan adalah dengan memperbaiki index_1 dengan mengatur ulang posisi dari kolom kolomnya, kolom Nim akan kita dahulukan lalu diikuti dengan kolom Pt, seperti pada query :

maka akan kita dapatkan hasil untuk query :

Screenshot_4

dapat dilihat bahwa penggunaan index_1 lebih maksimal sekarang, selain indikator missing index yang tidak ada, juga jumlah proses dalam database yang lebih sedikit.

jika kembali kita coba dengan query  :

 

maka akan kita dapatkan hasil :

Screenshot_5.png

sehingga hasilnya tidak berbeda dengan menggunakan index_1 di awal. ini berarti memang membuktikan bahwa

Posisi Menentukan Prestasi

sebelum saya akhiri catatan saya, saya ada sedikit renungan bagi pembaca yang budiman semua…

kita tahu kita punya 2 kondisi index_1, yaitu :

  1. dengan urutan pt,nim
  2. dengan urutan nim, pt

dan dari catatan ini dapat dilihat bahwa untuk query

dan

yang terbaik adalah opsi 2.

nah seandainya kita tetap pada opsi 1 dan melakukan query

mengapa kita mendapatkan hasil seperti di bawah ini ?

Screenshot_6

Referensi

[1]. https://radityo.pw/2016/01/24/clustered-index-vs-non-clustered-index-pada-sqlserver/

[2]. https://github.com/radityopw/blog_files/blob/master/pmp/datagen.txt

[3]. https://msdn.microsoft.com/en-us/library/ms189562.aspx

[4]. https://www.mssqltips.com/sqlservertutorial/2252/estimated-vs-actual-query-plan/

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.

ic157372

 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.

ic88960
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.

fig05_02_index_on_heap_table-x1
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.

fig05_03_secondary_index_on_clustered_index-x1
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 :

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 .

hasil dari query tersebut adalah :

hasil_tes1_cluster

sedangkan untuk query  :

hasilnya adalah :

hasil_tes1_non_cluster.png

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

hasilnya :

hasil_tes_3_cluster

sedangkan untuk Query :

hasilnya :

hasil_tes_3_non_cluster.png

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

hasilnya :

hasil_tes_2_clustered.png

sedangkan untuk query

hasilnya :

hasil_tes_2_non_cluster

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