Merhabalar,

İlk yazımda sizlere sql severda index yapısı hakkında bilgi vereceğim. Uzun bir makale olacak sıkı durun

öncelikle index kavramını iredeleyelim.

Index: Kısaca data’ya erişim zamanını kısaltan yapılardır. Root, Non-Leaf Level ,Leaf Level kısımlarından oluşurlar. Index bilgileri her veritabanının sysindexes tablosunda tutulur.

Clustered Index:

• Bir tabloda en fazla bir tane clustered index oluşturabilirsiniz.
• Clustered index tablo içindeki verinin fiziksel sıralamasını tanımlar.
• Clustered index’te gerçek bilgi index in leaf level kısmındadır.
• Tablodaki bilginin fiziksel sırası ile index içindeki bilginin fiziksel sırası aynı olur.
• Tablo boyutunun %5 kadar yer kaplar, yaratılırken bu boyut geçici bir süre için %20 ye çıkar.
• Bir satır silindiği zaman oluşan boşluk yeni bir satır girişi için hazır bekler.
• Tablo ve onun Clustered index’i aynı filegroup larda yer alır, değiştrilemez.

NonClustered Index:

• SQL Server’da bir tabloda en fazla 249 non_clustered index tanımlanabilir.
• Mantıksal sıralama index tarafından belirtilir. Nonclustered index in leaf level sıralaması ile tablodaki bilgilerin fiziksel sıralaması farklıdır.
• Oluşturulan index’in yapısı belirtilmezse non_clustered index oluşturulur.
• Bazı durumlarda nonclustred index yeniden yapılandırılır: 1) Var olan bir clustered index silindiğinde. 2) Bir clustered index oluşturulduğunda
• Non_clustered index’ten önce clustered index oluşturulması performansı arttırır.
• Tablo ve bu tabloya ait nonclustered index ler farklı filegroup larda olabilir. Farklı harddiskler de yer alması performansı artırır.

ps: Clustered index için örnek olarak telefon rehberini verebiliriz. Non_Clustered için ise kitapların index bölümünü.

Page Split: Clustered indexlerde yeni eklenen veya güncellenen bir satır için yeterli yer yoksa  sayfa ikiye bölünür. Non_clustered indexlerde bölünme olmaz, yeni sayfa sona eklenir, ilişkisi olduğu diğer sayfadan oraya yönlendirme yapılır(Page forwarding).

Şimdi indexler hakkında temel bilgilere sahibiz. Birazda işin püf noktalarına bakalım.

Hangi Sütunlara Index konulmalıdır?

• Primary key ve Foreign key sütunları.
• Sorgularda çook sık kullanılan sütunlar.
• Çok sık sıralamaya tabi tutulan sütunlar.
• Gruplama yapılan sütunlar (group by).

Index gerektirmeyen Sütunlar:

• Nadiren sorgulanan sütunlar.
• Az seçeneği olan sütunlar(cinsiyet,ülke gibi).
• Text,ntext ve image sütunları.

Bu kadar anlattık ama indexlerin nasıl oluşturulduğundan bahsetmedik.

Index Oluşturmak:

Create [clustered/nonclustered] index INDEX_ADI

On TABLO_ADI(KOLON_ADI)

Dikkat edilecek noktalar:

• Primary key veya Unique constraint oluşturulduğu zaman index otomatik olarak oluşturulur. Standart index oluşturmak yerine Primary key veya Unique constraint tercih edilmelidir.
• View’larda da index olabilir.
• Bir sütun  üzerinde index oluşturmadan evvel bir index’e sahip olup olmadığı araştırılmalıdır.
• Bir tabloda index oluştumak için tablonun sahibi olmak gerekir.

Create clustered index ix_musteri_no

On Musteriler(MusteriNo)

Create nonclustered index ix_musteri_ad

On Musteriler(MusteriAd)

Index Silmek:

Drop Index TABLO_ADI.INDEX_ADI

• Bir index silindiği zaman disk alanı boş bırakılır.
• Primary key veya unique constraint sonucu oluşan index ler direkt silinemez. Bunun için önce primary key veya unique constraint i silmemiz gerekir.
• Bir tabloyu silmek o tabloya ait index’leri de siler.
• Bir clustred index silindiği zaman bütün non_clustered index’ler yeniden yapılandırılır.
• Drop index ifadesi sistem tablolarında kullanılamaz.

Unique (Benzersiz) Index: Bir kolon üzerinde bu özellikle yaratılan unique indexlerde kolona aynı değerde bir kayıt girilemez.

• Hem clustered hem de nonclustered için kullanılabilinir.
• Primary key ve unique constraint varsa zaten unique index oluşturulmuştur.
• Unique özelliği dolu bir tabloya index oluşturulurken veya daha sonradan ekleniyorsa önce tablodaki o kolona ait bilgilerinden eşsiz olduğuna emin olduktan sonra yaratılır.

create unique nonclustered index ix_plaka_no

on Araclar.PlakaNo

Composite (Birleşik) Index: Birden fazla kolona uygulanabilen indexlerdir.

• Sorgularda sık sık kullanılan birkaç kolona ayrı ayrı index oluşturmaktansa bu kolonları tek bir index altında toplamak tercih edilmelidir. Daha performanslıdır.
• En fazla 16 sütun olabilir.
• View larda kullanılacaksa farklı kolonlar farklı tablolarda olabilir.
• Aynı sütunü birden fazla index içinde kullanmak çok kullanışlı değildir.

Select * from musteriler where musteri_ad=’ali’ and musteri_soyad=’veli’

Create clustered index ix_ad_soyad

On musteriler(musterisoyad,musteriad)

• Telefon rehberinde önce soyadlara, sonra isimlere bakarak arama yapmak gibi.

Index Seçenekleri

FillFactor: Clustered ve nonclustered index lerde kullanılabilinen fillfactor özelliği ile update ve insert işlemlerinde performans artışı sağlanılır. Bir index sayfası dolduğu zaman sql server yeni gelen satırlara yer açmak için leaf level da PageSplit kullanır. FillFactor kullanarak yeni kayıtlar için leaf level da boş satırlar bırakabiliriz.

• Fillfactor sadece index yaratılırken veya yeniden yapılandırırken belirlenebilir, daha sonra dışardan değiştirilemez.
• Fillfactor oranı bilgileriniz ne kadar sıklıkla değiştiğine ve yeni kayıtlar eklendiğine göre belirlenir.
• OLTP lerde düşük fillfactor kullanılması tavsiye edilir.
• 0 veya 100 değeri data page lerin 100% dolu olacağını yani boşluk bırakılmayacağını belirtir.
• 70 gibi bir değer, datapage in %70 nin dolu, %30 nun da ilerisi için boş bırakılacağını belirtir.
• Sp_configure ile default fillfactor değeri belirlenebilir.

Pad_Index: Fillfactor leaf level daki bölümlenme yüzdesini ayarlarken, pad_index non-leaf level da ki bölümlenmeyle ilgilenir.

• Fillfactor de belirtilen oranın aynısı non-leaf level da geçerli olur.
• Kullanılabilmesi için fillfactor ün açık olması yani 1-99 arasında olması gerekir.
• Pad_index i gerektiren en büyük etken veritabanına aşırı miktarda değişiklik ve kayıt girdisi yapılacak olma ihtimalidir.

Create index ix_musteri_no

With pad_index, fillfactor=70

Indexlerin Bakımı: Bir süre sonra tablo içindeki veriler değişeceği için index yapısında da bölünme ve boşluklar oluşacaktır. Bu da performansı olumsuz yönde etkileyecek ve sorgu süresini uzatacaktır. Bakım için iki yöntem var; biri index i silip yeniden oluşturmak, diğeri mevcut index üzerinde ayarlamalar yapmaktır.

DBCC SHOWCONTIG: Bu komut ile bir index in bölünme durumunu öğrenebiliriz.

Dbcc showcontig(musteriler)

veya

Dbcc showcontig(musteriler,ix_musteri_ad)

• Gelen sonuçlardan en önemlisi logical scan fragmention dir. Mümkün olduğunca sıfıra yakın olmalıdır.

DBCC INDEXDEFRAG: Tablo veya bir view üzerindeki clustered veya nonclustered index in leaf level daki index sayfalarını birleştirir. Bu yeniden yapılandırma index tarama performansını arttırır.

Dbcc indexdefrag(testdb,musteriler,ix_musteri_ad)

DROP EXISTING: İndex lerin karakteristik yapısını değiştirmek veya silmeden yeniden yapılandırmak için kullanılır. En önemli yararlarından biri primary key ve unique constraint le oluşan indexleri de yapılandırabilmesidir. Leaf level sayfaları yeniden yapılandırarak sıkıştırabilir, genişletebilir veya bölünmeleri silebilir.

Değiştirilebilecek index karakteristikleri:

Tip:

• Non_clustered index clustered index’e çevrilebilir.
• Clustered index non_clustered index’e çevrilemez.

Index Sütunları:

• Tanımlanmış index sütunu başka bir sütunla değiştirilebilir.
• Composite index te bulunan bir sütun silinebilir veya başka bir sütun eklenebilir.
• Index sütununa unique özelliği kazandırabilir veya iptal edilebilir.

Seçenekler:

• Pad_index veya fillfactor değiştirilebilir.

create nonclustered index ix_musteri_ad

on musteriler(musterino)

with fillfactor=70

create unique clustered index ix_musteri_ad

on musteriler(musteriAd)

with drop_existing,fillfactor=65

Makalemiz burada sona eriyor arkadaşlar, umarım sıkılmadınız

Başka bir makalede görüşmek üzere…