Heap, Clustered İndeks ve Nonclustered İndeks Data Yapıları - Bölüm 3 (Nonclustered İndeks Mimarisi)

4. Nonclustered İndeks Mimarisi

Nonclustered indeks aşağıdaki farklılıklar dışında B-tree yapısını clustered indeks gibi kullanır:

• · Tablodaki data satırları, nonclustered anahtarlarına göre sıralanıp depolanmaz.
• · Nonclustered indeksin leaf katmanı data page yerine indeks pagelerle düzenlenir.

Nonclustered indeksler tablo veya view üzerine heap veya clustered indekslerle tanımlanabilirler. Nonclustered indekste her indeks satırı, nonclustered indeks key value ve bir row locator bulundurur. Bu yer bulucu, clustered indeks veya heapteki anahtar değerine sahip satırı işaret eder.

Nonclustered indeksteki satır bulucu, ya bir satır göstergesidir ya da satırın clustered indeks anahtarıdır. Buna göre:

1. Tablo clustered indeks içermeyen bir heap ise; row locator bir satırı işaret eder. Bu pointer, dosya tanıtıcı (ID), sayfa numarası ve page üzerindeki satırların numaralarından oluşur. Bütün olarak bu pointer Row ID (RID) olarak tanımlanır.
2. Eğer tablo bir clustered indekse sahipse veya indeksli bir view ise; row locator satırın clustered indeks anahtarıdır. Eğer clustered indeks unique indeks değilse, SQL Server, uniqueifier denilen kendisinin ürettiği değerleri ekleyerek duplicate keyleri unique yapar. Bu dört byte veri kullanıcıya gösterilmez. Bu sadece nonclustered indekslerde clustered anahtarlarını kullanması gerektiği zaman eklenir. SQL Server data satırını, nonclustered indeksin leaf satırlarında saklanan clustered indeks anahtarlarını kullanıp clustered indeksi arayarak elde eder.
   

Şekil 1:

Included Column Indexes

Nonclustered indeksin işlevselliği, indeksin leaf seviyesine nonkey kolon denilen included kolonlar eklenerek genişletilebilmesidir. Key kolonlar indeksin her seviyesinde saklanabilirken, nonkey kolonlar sadece leaf seviyesinde saklanabilirler.

SQL Server’ın nonclustered indeksi nasıl oluşturduğunu görmek için bir tablo yaratıp kayıt girişi yapalım.

Örnek Sorgular : Örnekler\1.Ornek3.sql

 

Create Table EmpTable1

(  EmpId Int,

   EmpName Varchar(8000)      )

Tablolar ilk yaratıldıklarında ön değer olarak yaratılan bir indeks yoktur. Teknik olarak indeks içermeyen tablolar ‘Heap’ olarak adlandırılır. Oluşturduğumuz tablonun indeks içermediğini sysindexes sistem tablosundaki “Indid” kolon değerinin 0 olup olmadığını sorgulayarak emin olabiliriz. Sysindexes table her vertiabanında bulunan tablo track’lerini ve indeks bilgilerini içeren bir tablodur. Bu tablodaki “Indid” kolonu index kimliğini belirtir ve Indid = 0 tablonun indeks içermediğini yani “heap” olarak saklandığını gösterir.

Tabloya bazı kayıtlar ekleyip sonuçlarına bakalım:

 

Insert Into EmpTable1 Values (4, Replicate ('d',2000))

Insert Into EmpTable1 Values (6, Replicate ('f',2000))

Insert Into EmpTable1 Values (1, Replicate ('a',2000))

Insert Into EmpTable1 Values (3, Replicate ('c',2000))

Select EmpID From EmpTable1

Sonuçta görüldüğü gibi kayıtlar giriş yaptığımız sıra ile görüntüleniyor.

Şimdi bazı komutlarla tablomuzun page bilgilerini inceleyelim:

 

DBCC TRACEON (3604)

GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id('EmpTable1')

DBCC ind(@DBID, @TableID, -1)

Şimdi DBCC PAGE komutuyla data pageleri inceleyelim.

DBCC page(dbid, fileno, pageno, option)

Dbid: Veritabanı ID

FileNo: Page in file numarası. Birden fazla dosya ile çalışmıyorsak 1 olacaktır.

Pageno: DBCC IND ile alacağımız page numarası

Option: 0, 1, 2, 3 olabilir.

 

DBCC TRACEON (3604)

GO

Declare @DBID Int

Select @DBID = db_id()

DBCC page(@DBID, 1, 73, 3)

Yukarıdaki sorgu ile 73 numaralı page içerisindeki dataya ulaşabilirim. Bu sonuç MSSQL’in verilerle kolon adlarının datapage içerisinde nasıl bulunduğunu gösterir.

Sonuç:

Record Type = PRIMARY_RECORD

EmpId = 4

EmpName = ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

dddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

İncelediğimiz sonuçlar indeks içermeyen tablonun SQL içerisinde nasıl depolandığını gösteriyor. Şimdi bu tabloya Empid kolonuna unique non-clustered yaratarak veriye nasıl etki ettiğine ve nasıl depolandığına bakacağız.

 

CREATE UNIQUE NONCLUSTERED INDEX EmpTable1_empid

ON EmpTable1(EmpId)

Non-Clustered index oluşturulduktan sonra, DBCC Ind ile tekrar bakıyoruz sonuca

 

DBCC TRACEON (3604)

GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id('EmpTable1')3

DBCC ind(@DBID, @TableID, -1)

Elde ettiğimiz sonuç indeks page detaylarını da içeriyor. Yeni alanlara bakarsak; page 89 indeks page için tahsis edilmiş page detayını gösterir, page 89 da indeks page detayını gösterir.

MSSQL indeks page için bir page (PageType=10) yaratır ve indeksin kullandığı page numarasını gösterir.

 

DBCC TRACEON (3604)

GO

Declare @DBID Int

Select @DBID = db_id()

DBCC page(@DBID, 1, 89, 3)

Sonuç:

IAM: Single Page Allocations @0x61D7C08E

Slot 0 = (1:80)

 

DBCC TRACEON (3604)

GO

Declare @DBID Int

Select @DBID = db_id()

DBCC page(@DBID, 1, 80, 3)

SQL indeks kolonu sıralayarak yerleştirir. “HEAP RID” kolonu satır göstergesidir.

Satır göstergesi iki şekilde görüntülenir:

0. Tabloda clustered indeks yoksa satır göstergesi fileno, pageno ve pagedeki satır numarası kombinasyonudur.

1. Tabloda clustered indeks varsa, satır göstergesi clustered indeks anahtar değeridir.

Bir tablodan tek satır döndürmek için Non-clustered indeks daha kullanışlıdır.

Örneğin; EmpId kolonuna non-clustered indeks yaratılmış tablodan EmpId vererek sorgu çektiğimizde EmpId ile eşleşen doğru sayfa ve satırdaki girişi bulmak için indekse bakar ve doğrudan bu page ve rowa gider. Bu da kayıt sorgulamayı oldukça hızlandırır.

Şimdi tablomuza satır ekleyelim clustered indeks ile non-clustered indeks yapısına bakalım. Kayıt ekledikten sonraki tablo görüntüsü aşağıdaki gibi olsun:

 

INSERT INTO EmpTable VALUES (10, Replicate ('j',4000))

INSERT INTO EmpTable VALUES (2, Replicate ('b',4000))

INSERT INTO EmpTable VALUES (5, Replicate ('e',4000))

INSERT INTO EmpTable VALUES (8, Replicate ('h',4000))

INSERT INTO EmpTable VALUES (9, Replicate ('i',4000))

INSERT INTO EmpTable VALUES (7, Replicate ('g',4000))

Select EmpID From EmpTable1

Sonuçta görüldüğü gibi veri hala bizim giriş yaptığımız sırada. Çünkü non-clustered indeks eklemek verinin nasıl saklandığını değiştirmez veri pagelerde sıralama yapar.

Şimdi DBCC Ind komutuyla yeni satırların nasıl eklendiğine bakalım:

 

DBCC TRACEON (3604)

GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id('EmpTable1')

DBCC ind(@DBID, @TableID, -1)

GO

· Metot 1:

DBCC TRACEON (3604)

GO

Declare @DBID Int

Select @DBID = db_id()

DBCC page(@DBID, 1, 80, 3)

Yukarıdaki sonuçta görüldüğü gibi SQL Server’da indeks kolonun sıralanarak yerleştiriyor. “HEAP RID” kolonu satır göstergesini belirtiyor.

Satır göstergesinin iki tipi var demiştik. Yukarıdaki örnekte satır göstergesinin birinci metodunu görmüş olduk.

· Metot 2:

Şimdi de clustered ve non-clustered indeks içeren tabloda ikinci metodu inceleyelim. Öncelikle EmpTabl1 tablosuna bir kolon ekleyelim ve buna clustered indeks oluşturalım:

 

Alter Table EmpTable1 Add EmpIndex Int IDENTITY(1,1)

Kolon ekledikten sonra DBCC Ind komutuyla kontrol edersek de görebiliriz, kolon eklemek SQL Server tarafından ayrılan toplam page sayısını değiştirmez.

 

CREATE UNIQUE CLUSTERED INDEX EmpTable1_empindex

ON EmpTable1(EmpIndex)

Clustered Indeks eklendikten sonra page yapısı aşağıdaki gibi değişir:
 

DBCC TRACEON (3604)

GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id('EmpTable1')

DBCC ind(@DBID, @TableID, -1)

90 ve 47 numaralı pageler page yerleşim detaylarını içerir. 73,110 ve 121 data page detaylarıdır.

Şimdi 90 ve 47 pagelerine bakalım:

 

DBCC TRACEON (3604)

GO

DBCC page(12, 1, 115, 3)

Yukarıdaki sonuç, veri page bağlantısı içeren(ChildPageId) clustered indeksi gösteriyor. EmpId kolonu, sayfada satır başlangıcını içeren indeks kolonudur.

 

DBCC TRACEON (3604)

GO

DBCC page(12, 1, 175, 3)

Yukarıdaki sonuç ile Metot 1 ve Metot 2 arasındaki farkı görebiliriz. Metot 1’de pageler satır göstergesini belirtirken Metot 2 deki clustered indeks anahtarlarını gösteriyor.

Metot 1 ve Metot 2 arasındaki en büyük fark datapagedeki satıra bağlanma şeklidir.

Referanslar

1. http://msdn.microsoft.com

Serap PARLAK