Bu makalede Clustered-NonClustered Index Komutları ve aynı zamanda aralarındaki farkı görmüş olacağız. SQL Server’da çeşitli index türleri bulunur, ancak en yaygın kullanılanları şunlardır:
Aşağıdaki komut yardımıyla tablo altında bulunan clustered ve nonclustered index yapılarımızı görebilir. Bu sp yardımıyla hangi kolunlara index altıldığı hangi filegroup üzerinde olduğu görülebilir.
EXEC sp_helpindex 'Person.Person'
1.Clustered Index: Her tablo yalnızca bir tane olabilir. Tabloyu fiziksel olarak düzenler ve sıralar. Bütün tablo clustered indexs’e göre diskte mantıksal olarak sıraya tutulur. Bu nedenle bir tablo üzerinde yalnızca bir tane clustered index olabilir. Leaf Level’da verinin tüm sütunlarıyla birlikte kendisi bulunmaktadır. İncluded kolon olmaz. Oluşturduğunuz bir tablo üzerinde eğer id kolonunu “SET PRIMARY KEY” yaparsanız, bu kolon otomatik olarak bir CLUSTERED INDEX oluşturur. Unique te olabilir non unique’te olabilir. Non unique olursa clustered index’e sahip tabloda oluşturacağımız non clustered index’lerin leaf level’indeki row locator’da ki clustered index key’leri unique hale getirmek için uniqueifier isminde 4 byte’lık bir belirleyici koyar. Bu şekilde non clustered index kullanılarak yapılan bir aramada ihtiyacı olan veriyi bulmasını sağlar. Extra maliyet, extra büyüklük. Tabloya primary key koyarsanız sql server otomatik olarak arka tarafta primary key koyduğunuz kolona clustered index koyar. Default olarak bu şekilde clustered index koysa bile siz primary key kolonun non clustered olması için force edebilirsiniz.
Aşağıdaki iki komut kullanılabilir. İkinci komut SSMS arayüzünden alınmıştır.
CREATE CLUSTERED INDEX [ClusteredIndex_Table1_id] ON [dbo].[Table_1] (id);
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex_Table1_id] ON [dbo].[Table_1]
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GONot: Yukarıdaki komutta clustered index önüne non ifadesi getirilerek oluşturduğumuz index nonclustered olarak oluşmaktadır. Bu ifade ise sorgulama işlemlerinde Table scan ifadesi olarak karşımıza çıkmaktadır. Bu şekilde nonclustered yapılırsa leaf levelde sadece ilgili kolon bulunmaktadır. Tablomuzda sadece insert işlemi genelde yapılıyorsa bu yapıda oluşturmak faydalıdır.
SQL Server’da PRIMARY KEY tanımladığında, eğer tablo üzerinde clustered index yoksa, otomatik olarak clustered index oluşturur.. Ama zaten clustered index varsa, PRIMARY KEY eklerken nonclustered olarak oluşturman gerekir. Tablo üzerinde primary key yoksa aşağıdaki resimde görüldüğü gibi clustered index eklenir.
Aşağıdaki komut ile tablo üzerinde ilgili kolona primary key tanımlayacak bir nonclustered index oluşturabiliriz. Non ifadesi olmazsa clustered index olmaktadır.
alter table person.address add constraint PK_Index23 primary key nonclustered
(
AddressID asc
)
İki Yapı Arasında Temel Farklar
| Özellik | Primary Key (PK) | Sadece Clustered Index |
|---|---|---|
| NULL değer | Kesinlikle izin vermez (NOT NULL zorunlu) | NULL değerlere izin verebilir (tanıma bağlı) |
| Tekillik (Unique) | Her zaman UNIQUE zorunlu | UNIQUE olması şart değil (ama genelde önerilir) |
| Referans (Foreign Key) | Diğer tablolar tarafından referans alınabilir | FK olarak kullanılamaz |
| Tanım Netliği | Veri modelinde “ana kimlik” olarak açıkça belirtilir | Sadece performans için bir yapıdır |
2. Non-Clustered Index: Bir tabloya birden fazla non-clustered index ekleyebilirsiniz. Non-clustered index ler, tablonun fiziksel düzenini değiştirmezler, ancak index sütunlarını sıralarlar. Non clustered index koyulan kolonlar tablodan bağımsız olarak diskte ayrı bir şekilde tutulur. Bu yüzden ekstra yer kaplarlar ve tabloda çok fazla non-clustered index varsa her insert,update ve delete işlemi tablonun haricinde bu tablodaki bütün nonclustered index’lere de uygulanacağı için insert,update ve delete performansı yavaşlayacaktır. Bu yüzden çok fazla nonclustered index oluşturmak her zaman iyi bir şey değildir. Bir tabloda 249 adet NONCLUSTERED index oluşturulabilir. Maksimum sayısı SQL Server sürümüne bağlı olarak değişir, ancak sayı örneğin SQL Server 2016’da tablo başına 999’a kadar çıkar.
NonClustered Index’te arama yaparken Leaf Level’e ulaştığımızda verinin kendisi yerine Row Locator bulunur.
Eğer tablo heap ise(tabloda clustered index yoksa) row locator olarak pointer /Row ID(RID) bulunur. Arama yaparken Leaf Level’a gelindiğinde row id kullanılarak ilgili kayda ulaşılır. Bu işlem RID Lookup olarak geçer. İyi bir şey değildir. Tablonuza mutlaka bir clustered index koyun.
Eğer tablo heap değilse(clustered index varsa) row locator olarak Clustered Index Key vardır. Arama yaparken Leaf Level’a gelindiğinde clustered index key kullanılarak ilgili kayda ulaşılır. Bu işlem Key Lookup olarak geçer. Tablo nonclustered index’e göre sıralı olmadığı için, non clustered index tablonun dışından ekstra bir alan kullanılarak oluşturulduğu için bir tabloda birden fazla sayıda olabilir. Unique te olabilir non unique’te olabilir. Unique olursa aramalar daha hızlı sonuç döner. Unique işlemlerinde seek işlemi yapılırken, non unique’te işlemlerinde scan işlemi yapılmaktadır.
Non-Clustered İndeks Çeşitleri:
- B-tree: En yaygın non-clustered indeks türüdür. Dengeli bir ağaç yapısı kullanır ve çok çeşitli sorgu türleri için uygundur.
- Full-text: Metin verilerinde kelime veya ifade aramaları için kullanılır. Kelime kökleri, eş anlamlılar ve yakınlık aramaları gibi gelişmiş arama özellikleri sunar.
- Spatial: Coğrafi ve geometrik veriler için kullanılır. Uzamsal veriler üzerindeki sorguları hızlandırır.
- XML: XML verileri indekslemek için kullanılır. XML verileri üzerinde XPath sorguları yapmanızı sağlar.
- Columnstore: Büyük veri kümeleri için tasarlanmıştır. Veriyi sütun bazında depolar ve sıkıştırır, bu da analitik sorguların performansını önemli ölçüde artırır. Clustered ve non-clustered columnstore indeksleri mevcuttur.
- Filtered: Belirli bir filtreye uyan verileri indeksler. Bu, nadiren kullanılan verileri indekslemekten kaçınarak indeks boyutunu ve bakım maliyetini azaltır.
- Unique: Yinelenen değerlere izin vermez. Birincil anahtar ve benzersiz kısıtlamalar için kullanılır.
Aşağıdaki iki komut kullanılabilir. İkinci komut SSMS arayüzünden alınmıştır.
CREATE INDEX [NonClusteredIndex_Table1_id] ON [dbo].[Table_1] (id);
CREATE NONCLUSTERED INDEX [IX_Person_BusinessEntityID_FirstName_LastName] ON [Person].[Person]
(
[BusinessEntityID] ASC,
[FirstName] ASC,
[LastName] 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, Maxdop=16, fillfactor=90)
GOYukarıdaki komut, bir Non-Clustered Index oluşturmak için kullanılmış ve çeşitli indeks özellikleri tanımlanmıştır. Aşağıda her bir parametre detaylı olarak açıklanmıştır:
- Komutun Temel Yapısı
CREATE NONCLUSTERED INDEX [IX_Person_BusinessEntityID_FirstName_LastName] ON [Person].[Person]
(
[BusinessEntityID] ASC,
[FirstName] ASC,
[LastName] 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, Maxdop=16, fillfactor=90)
CREATE NONCLUSTERED INDEX: Bu komut, bir Non-Clustered (kümeleme olmayan) indeks oluşturur. Non-Clustered indeksler, tablodaki veriyi fiziksel olarak sıralamaz; yalnızca bir referans tablosu oluşturur.
[IX_Person_BusinessEntityID_FirstName_LastName]: İndeksin adıdır. İsimlendirme standartlarına uygun olarak genellikle tablo ve kolon isimlerini içerir.
[Person].[Person]: İndeksin oluşturulacağı tablo.
[BusinessEntityID], [FirstName], [LastName]: İndeksin kapsadığı kolonlar. Burada sıralama (ASC = Ascending) belirtilmiştir.
2. WITH Kısmındaki Parametreler:
Bu kısımda indeks oluşturma işlemine ait çeşitli özellikler tanımlanmıştır.
PAD_INDEX = OFF
Fillfactor değeri leaf node’larda uygulandığını söylemiştik. Bu değerin Intermediate seviyesindeki node’lara da uygulamak istersek fill factor seçeneği ile birlikte pad_index seçeneğini de kullanmalıyız.
STATISTICS_NORECOMPUTE = OFF
SQL Server’ın indeks üzerindeki istatistikleri otomatik olarak yeniden hesaplayıp hesaplamayacağını belirler.
OFF: SQL Server, indeksin kullanımına bağlı olarak otomatik olarak istatistikleri yeniden hesaplayacaktır. (Varsayılan ayardır.)
Performans iyileştirmeleri için önemlidir. Ancak bu işlem devre dışı bırakılırsa istatistikler güncel kalmayabilir.
SORT_IN_TEMPDB = OFF
İndeks oluşturma sırasında sıralama işlemlerinin geçici olarak TempDB’de yapılıp yapılmayacağını belirtir. OFF, Sıralama işlemleri aynı veritabanı üzerinde gerçekleştirilir. Bu yapının avantajı TempDB kullanımı devre dışıdır, ancak bu büyük indekslerde daha fazla I/O kullanımıyla sonuçlanabilir.
SORT_IN_TEMPDB internal database snapshot oluşturur mu:
SORT_IN_TEMPDB=ON ayarı doğrudan snapshot oluşturmaz, ancak farklı bir davranış sergiler:
- Nasıl çalışır?:
- Index oluşturma/rebuild işlemlerinde sıralama işlemleri tempdb’de yapılır
- Ana veritabanının transaction log yerine tempdb’nin log dosyası kullanılır
- Ana veritabanının log büyümesini azaltır
- Snapshot ile ilişkisi:
- Doğrudan snapshot oluşturmaz
- Ancak tempdb kullanımı nedeniyle dolaylı olarak performans etkisi olabilir
- Büyük index işlemlerinde log şişmesini engellemek için kullanışlıdır
DROP_EXISTING = OFF
Eğer mevcut bir indeks varsa, bu indeksin yeniden oluşturulup oluşturulmayacağını belirler.OFF, Mevcut bir indeksin üzerine yazılmaz, yeni bir indeks oluşturulur.Eski bir indeksi performans artırıcı bir şekilde yeniden oluşturmak için DROP_EXISTING = ON kullanılabilir.
ONLINE = OFF
İndeks oluşturma işlemi sırasında tablonun diğer işlemler tarafından erişilebilir olup olmadığını belirler. OFF, Tablodaki diğer işlemler indeks oluşturma sırasında kısıtlanır (kilitlenir).
Online olarak indeks oluşturmak için ONLINE = ON kullanılabilir, bu da sistemin kesintisiz çalışmasını sağlar (daha fazla kaynak gerektirir). Enterprise Edition’da kullanılabilir. (Standard Edition’da sınırlı destek)
SQL Server genellikle okuma ve yazma işlemlerine izin verir, yani işlemler kesintiye uğramadan devam etmelidir. index oluşurken herhangi bir lock olmamasına rağmen index sonlarına doğru lock işlemi gerçekleşmektedir. Bu sebepleri açıklayalım.
Final Schema Modification Lock (Sch-M) ONLINE=ON kullanarak index oluştururken, işlem büyük oranda low impact (düşük etkili) şekilde çalışır, yani tabloya okuma/yazma işlemleri devam edebilir. Ancak işlem son aşamaya geldiğinde (yaklaşık son birkaç dakika içinde), SQL Server final aşamada bir *Schema Modification Lock (Sch-M)* almak zorunda kalır. Sch-M lock, tüm yazma işlemlerini (INSERT, UPDATE, DELETE) ve bazı okuma işlemlerini bile durdurur. Bu nedenle dakikalar boyunca sorun yokken, işlemin sonuna doğru lock’lar oluşmaya başlamış olabilir. Çözüm olarak Index oluşturma işlemini daha az yoğun saatlerde yapmak en iyi çözümdür. Partitioning gibi teknikleri kullanarak indeksleme yükünü azaltabilirsin. Eğer büyük tablolar varsa ve indexleme süreci uzun sürüyorsa, bunu parça parça yapmak mantıklı olabilir.
ONLINE=ON ile index oluştururken, SQL Server arka planda transaction log’u kullanarak değişiklikleri takip eder. Eğer transaction log dolmaya başlarsa, SQL Server log dosyasını temizleyene kadar işlemleri yavaşlatabilir veya durdurabilir. Bu durumda write işlemleri yavaşlamaya başlayabilir ve lock oluşabilir. Büyük tablolar üzerinde indeksleme işlemi SQL Server tarafından *paralel (multi-threaded) olarak yürütülür. Eğer sistemde zaten yüksek CPU tüketimi yapan işlemler varsa, SQL Server bu kaynakları optimize etmek için lock mekanizmasını devreye sokabilir. Özellikle veritabanı sorguları + index oluşturma aynı anda CPU’yu zorladığında bu tarz gecikmeler ve lock’lar oluşabilir. Çözüm olarak Maxdop ayarı veya parallelism ayarı yapılabilir. Bir başka online=on ifadesine lock olacak ifade ise SQL Server, büyük değişiklikler olduğunda istatistikleri otomatik olarak güncelleyebilir (Auto Update Statistics). Eğer bu sırada ONLINE=ON ile indeksleme devam ediyorsa, istatistiklerin güncellenmesiyle indeksleme çakışabilir ve bu da lock oluşturabilir. Özellikle büyük tablolar için auto-update statistics çalışıyorsa, lock süreleri artabilir.
ONLINE=ON parametresi index işlemleri sırasında özel bir snapshot mekanizması kullanır:
- Nasıl çalışır?:
- Online index işlemleri bir internal snapshot benzeri yapı kullanır
- SQL Server, işlem sırasında verinin hem eski hem de yeni versiyonunu tutar
- Kullanıcılar index oluşturulurken verilere erişmeye devam edebilir
- Snapshot farkı:
- Tam bir database snapshot değildir
- Sadece ilgili index için geçici bir versiyonlama mekanizmasıdır
- İşlem tamamlandığında otomatik olarak temizlenir
- Avantajları:
- Uygulama kesintisiz çalışmaya devam eder
- Uzun süren index işlemlerinde kullanışlıdır
- Dezavantajları:
- Daha fazla tempdb kullanımına neden olur
- İşlem normalden daha uzun sürebilir
- Ek kaynak tüketimi yaratır
ALLOW_ROW_LOCKS = ON
İndeks üzerinde satır düzeyinde kilitlemeye izin verir. ON, SQL Server, satır bazında kilitleme yapabilir. Bu, işlem paralelliğini artırır.
ALLOW_PAGE_LOCKS = ON
İndeks üzerinde sayfa düzeyinde kilitlemeye izin verir. ON, SQL Server, sayfa bazında kilitleme yapabilir. Bu, daha büyük veri işlemleri için uygundur.
MAXDOP = 16
İndeks oluşturma işlemi sırasında maksimum paralel iş parçacığı sayısını belirler. İndeks oluşturma sırasında maksimum 16 iş parçacığı kullanılabilir. Daha yüksek iş parçacığı sayısı daha hızlı indeks oluşturma sağlar ancak CPU kullanımını artırabilir.
Not: Sorgularımızın sonuna option(maxdop 1) yazılınca sorgumuz belirtilen maxdop yapısında çalışmaktadır.
FILLFACTOR = 90
Verilerin tutulduğu leaf node’lardaki data page’lerin yoğunluğunu ayarlamak için kullanılır. Gelen yeni kayıtlar page’lere yazılırken doluluk oranı kontrol edilir, yer varsa ilgili page’e yoksa page ikiye bölünür ve yeni gelecek veri için organize edilir. Her indeks sayfasının ne kadar dolu olacağını belirler. Boş bırakılan kısım gelecekteki eklemeler için ayrılır. Sayfa, %90 dolu olacak şekilde oluşturulur. %10’luk alan gelecekteki veri eklemeleri için ayrılır.
Veri ekleme ve güncellemelerin sık olduğu durumlarda daha düşük bir Fill Factor belirlemek faydalı olabilir.
Bu yapılandırma, bir Non-Clustered indeksin performans ve bakım gereksinimlerine uygun şekilde optimize edilmesini sağlamaktadır. Özellikle:
– ALLOW_ROW_LOCKS ve ALLOW_PAGE_LOCKS sayesinde işlemler paralel yürütülebilir.
– FILLFACTOR ile veri eklemeleri için boş alan bırakılmıştır.
– ONLINE = OFF ayarı, işlemi hızlandırabilir ancak erişim kısıtlamasına neden olabilir.
Eğer indeks büyük bir tablo için oluşturuluyorsa ve çevrim içi erişim önemliyse, ONLINE = ON kullanmanız daha uygun olabilir.
NOT: Clustered index olmayan heap tablolara nonclustered index eklenebilir. Heap tablolarda sys.dm_db_index_physical_stats DMV’si sorgulandığında fragmentation değerleri NULL olarak dönebilir, çünkü heap’lerin geleneksel anlamda “fragmentation”ı yoktur. Ancak heap’lerde forwarding pointers (yönlendirme işaretçileri) oluşabilir ve bu da bir tür fragmentation sayılabilir. Genellikle index bakım jobları heap tabloları atlar çünkü geleneksel index fragmentation’ı yoktur. Ancak heap tablolar da satır silme/ekleme işlemleriyle parçalanmaya (fragmentation) uğrayabilir. Forwarding Pointers: Heap’te bir satır güncellendiğinde ve yeni konuma taşınması gerektiğinde oluşur Heap’te Boş Alan: Silinen satırların yarattığı boşluklar.Nonclustered indexler heap tablolarda çalışabilir, ancak bookmark lookup işlemleri clustered indexlere göre daha maliyetli olabilir.
Veritabanı altında herhangi bir sorguda index force edilebilir.
Select*from tableName with (INDEX=IndexName)Bu makalede clustered-nonclustered index yapıları arasındaki farkları ve ilgili index yapıları için komutları görmüş oluyoruz. Başka bir makalede görüşmek dileğiyle..
“Onlar, yaptıkları dünyada ve ahirette boşa gitmiş olanlardır. Ve onların yardımcıları yoktur.“
Al-i İmran Suresi, 22. Ayet
