SQL Server COMPOSITE INDEX

Reading Time: 4 minutes

Birden fazla kolon üzerine oluşturulan Index ‘lere Composite Index denir.

Birden fazla kolon üzerine tanımlamak ne demektir ?

Yukarıdaki örenğimizdede görüldüğü gibi tablomuzun üzerinde üç adet kolon var ve biz iki adet kolon üzerine bir index tanımlıyoruz. İşte bu tür indexlere yani birden fazla kolon içeren index ‘lere Composite Index diyoruz.

Tablolarımızda Composite index tanımlarken bazı best Practice ‘lerimiz mevcuttur. Bunlara uyulmaması durumunda oluşturduğumuz index ‘ten istediğimiz verimimi alamayız.

Örnekler üzerinden giderek daha net anlayabilrisiniz.

AdwentureWorks2012 Db ‘si üzerinden aşağıdaki gibi bir sorgu çekilmekte.

 

Bu sorgunun Execution Plan ‘ı incelendiğinde ;

1

 

LookUp işlemi yapmakta ve sorgunun maliyeti bu yüzden artmaktadır. Peki bu işlemi Performanslı hale nasıl getirebiliriz. Yani bir Composite Index tanımlayarak query performansımızı arttırabilir miyiz ?

Sorgumuzu incelediğimizde Where Bloğunda 3 adet şartımız yer almaktadır. Fakat Composite index oluşturuken dikkat edilmesi gereken bir kaç önemli nokta mevcuttur.

Composite index oluştururken uyguladığımız ilk best practice Where koşulundaki eşitlik (equality) kolonlarının eşitlik olmayan (inequality) kolonlardan daha önce yazılması gerektiğidir.

İkinci best practice’imiz ise her grubun içinde (eşitlik ve eşitlik değil) selectivity’si yani seçiciliği daha fazla olan kolonun daha önde tanımlanmasıdır. Örneğin kişilerin CV ‘sinin bulunduğu bir tabloda cinsiyet ve yaş kolonları üzerinde index oluşturacağımızı düşünelim.

Cinsiyet te en iyi ihtimalle {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}50 elersiniz.  Ancak yaşı 35 dediniz , 60 tane yaşlı varsa orda 60 da 59 ‘unu elediniz tek seferde .

O yüzden hangisinin DISTINCT kayıdı daha fazla ise onun selectivity’si daha fazladır.

Bu durumda yaş kolonunun seçiciliği cinsiyet kolonundan daha fazladır.

Örneğimize geçecek olursak ;

ilk best practice ‘imizi hatırlayalım eşitlikler en başa getiriliyordu.

Şimdi FisrtName,LastName ve BusinessEntityID kolonlarına sırasıyla index tanımlayalım ve sonuca göz atalım .

 

Tablomuzun üzerinde yukarıda ki index ‘imizi tanımladık ve hemen Execution Plan ile beraber IO istatistiklerini gözden geçirelim.

2

Table ‘Person’. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Planımızda bir değişiklik olmadı. Halbu ki Composite Index ‘imizi tanımaldığımızda Performanslı çalışması gerekmekteydi. Aynı zamanda IO değer 8 ‘dir query ‘mizin.

Oluşturduğumuz bu Composite Index ‘i silip yerine best Practice ‘lerimize uygun bir Composite Index oluşturalım.

 

3

Table ‘Person’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan ‘ı inceleyecek olursak {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}100 oranında Index Seek işlemi yaparken ogical Read oranı ise 4. yni diğer index ‘imizin yarısı kadar okuyarak Index Seek işlemi yapıyor. Yani oluşturduğumuz Composite Index perfromans ‘lı bir şekilde çalışmaktadır.

 

Birinci index ‘imizi inceleyecek olursak ;

BusinessEntityID kolonunu en başa yazdık fakat sorgumuzda selectivity ‘si en yüksek değer başa yazıldığını bildiğimizden dolayı bu kolonun selectivity’si de diğer kolonlara nazaran en yüksek olduğundan başa yazdık ama oluşturduğumuz index performanslı olmadı . Peki Neden ?

Hem Best Practice ‘lerimizde Selectivity ‘si yüksek olan öne yazılacak diyoruz hem de yazdığımızda karşımıza çıkan planda ve IO değerlerinde kötü perfromans ile karşılaşıyoruz.

Eğer Best Practice ‘lerimizi özetleyecek olursak ;

  • Eşitlikler , eşitlik olmayanlardan önce yazılacak.
  • Birden fazla eşitlik sağlanıyorsa , böyle durumda kolonların seçiciliği (selectivity) ‘ne bakılacak ve seçiciliği yüksek kolon öne yazılacaktı.

Bu durumda BusinessEntityID  kolonu diğer kolonlara göre Selectivity ‘si yüksek ama between olarak işlem grdüğünden bir eşitlik sağlamamaktadır. Best Practice ‘ imizin ilki eşitlik sağlayan kolonları öne almaktı. Yani eşitlik sağlamadığından dolayı öne almamalıyız bu kolonu ki ikinci index ‘imizin sonucunda da nedenini görsel olrak görerek netleştirdik.

Arkadaşlar semirlerden birinde karşılaştığım ve bana yöneltilen bir souyuda bu konu ile ilgili olduğundan sizlerle paylaşmak istiyorum.

SORU :

Sorgumun Where koşulunda ve Order ‘ında da birer tane kolon var. Index oluşturmama gerekiyor ve oluşturacağım Composite Index ‘im de ki sıralamayı nasıl belirleyeceğim ?

Şimdi sorgunun yapsını düşünecek olurak ;  sorgumda Order işlemi ne zaman gerçekleştirilir? Bu soruyu kendinize sorduğunuzda direkt cevabına ulaşacaksınız aslın . Where bloğunda ki şartlar sağlanmadan ORDE işlemi yapılamıyacağından dolayı tabi ki WHERE bloğunda ki şartımız öne daha sonra ORDER işlemimizde ki kolonumuz yazılır.

Şunu unutmamak gerekir Index ‘lemenin olması kadar çok fazla olmasıda dezavantaj . Dedik ki her Insert ve her Delete işlemi bütün Index’lere dokunuyor.  Indexlerde de  o bilgileriniz var. Bir kayıdı silecekseniz eğer bunu  bütün indexlerden silinmesi lazım . Yani ne kadar çok indexiniz varsa her DML işlemlerinde etkileniyolar.

 

Teşekkürler.

 

Leave a Reply

Your email address will not be published. Required fields are marked *