ĠNDEKS

ĠNDEKS
İndeks‟ler, tablolardan veri çekmek için sorgular çalıştırılırken, gereken süreyi
azaltmak için kullanılır. Birkaç kayıt için bu süre pek önemli değilken, kayıt sayısı onbinlere,
milyonlara çıktığında hayati öneme sahiptir.
INDEKS KULLANIMI
SQL sorguları çalıştırıldığında veriler tablo üzerinden iki yöntemle alınır.
Birincisi, tablo baştan sona kadar taranır ve istenilen veri alınır. Bu yöntem table scan
(tablo tarama) olarak adlandırılır.
İkinci yöntem ise tablo üzerinde oluşturulan indeksler yardımıyla istenilen verilerin
toplanmasıdır.
Tablomuzdaki kayıt sayısı çok fazla olduğunda birinci yöntem doğal olarak
performans kaybına sebep olacaktır.
KÜTÜPHANE ÖRNEĞĠ
Bir örnek üzerinden indeks yapılarını anlamak daha kolay olacaktır. Bir kütüphaneyi
ele alalım. Kütüphaneyi kitaplara ilişkin bilgilerin saklandığı kitap tablosuna benzetebiliriz.
Kütüphanedeki görevlinin yeni gelen her kitabı bulduğu ilk boş yere koyduğunu
düşünelim. Bu şekilde kitaplar belli bir kurala göre dizilmiyorsa ortaya çıkan yapıya yığıt
(heap) denir.
Bu kütüphanede okurun aradığı kitabı bulabilmek için kütüphanedeki bütün kitapları
tek tek kontrol etmek gerekir. Bu duruma tablo taraması (table scan) denir. Eğer
kütüphanede birkaç yüz kitap varsa bu pek sorun değildir ama yüz binlerce kitabın bulunduğu
bir kütüphanede aranılan kitabı bulabilmek çok büyük bir sorun olacaktır.
Bu sorunu çözebilmek için akla gelen ilk yöntem kitapları raflara alfabetik sırada
koymak olacaktır. Kitapları bu şekilde sıraya konulursa kütüphanecimiz bütün kitapları
gözden geçirmeden okurun istediği kitabı daha hızlı bir şekilde bulabilir. “Ateşten Gömlek”
adlı bir kitabı arayan okur geldiğinde, kütüphaneci hemen rafların en başına yönelir. Bu
şekilde kitap tablosu üstünde kitap adları sütunu için bir Clustered Ġndeks tanımlamış oluruz.
Clustered İndeks, bir tablo üzerinde sadece bir tane tanımlanabilir ve verilerin bir niteliğine
göre fiziksel olarak sıralanmaları durumunu ifade etmek için kullanılır.
Ancak okur bazen de kitap adını bilmeden kitap isteyebilir. Örneğin “Mehmet Akif
Ersoy‟un şiir kitaplarını istiyorum” dediğinde kütüphaneci yeni bir sorunla karşılaşacak.
Kütüphaneci kitapları kitap adına göre dizdiğine göre bu sorun için yeni bir çözüm bulması
gerekecektir. Bu durumda kitap yazarlarından kitap adlarına geçişi sağlayacak bir liste tutmak
aklına gelecektir. Şu şekilde bir liste olsun;
Yazar Adı
…
Halide Edip Adıvar
Halide Edip Adıvar
Halide Edip Adıvar
…
Mehmet Akif Ersoy
Mehmet Akif Ersoy
…
Kitap Adı
…
Vurun Kahpeye
Ateşten Gömlek
Sinekli Bakkal
…
Safahat – I
Safahat – II
…
Bu liste için Non-Clustered İndeks diyeceğiz. Bir yazar adı sorulduğunda kütüphaneci
yazarın kitaplarının her birinin bulunduğu bölge hakkında fikir sahibidir. Nonclustered
İndeks‟ler, bir Clustered İndeks veya Heap üstünden hızlı olarak kayıtlara erişim sağlamak
üzere tanımlanır.
Kütüphaneci, yazar-kitap listesinin benzerini, Konu-Kitap, ISBN-Kitap, YayıneviKitap gibi diğer sütunlar için de gerektiğinde hazırlayabilir. Veritabanında bu şekilde belli
sayıda (MS-SQL 2005 veritabanında 249 adet) Nonclustered İndeks yer alabilir.
Bazı küçük tablolarda, tablo taraması daha hızlı sonuca ulaştırabilir. Veritabanı karzarar analizini yaparak hangi yöntem uygunsa o yöntem ile arama yapar.
HEAP (yığıt) : Kayıtların giriliş sırasına göre diske kaydedildiği yapıya HEAP (yığıt)
denir.
CLUSTERED INDEKS: Clustered İndeks‟te tabloda yer alan kayıtlar fiziksel olarak
indeks‟le tanımlı olan sütuna göre dizilirler. Bir tablo üzerinde en fazla bir tane Clustered
Indeks tanımlanabilir.
Genellikle, clustered indeks için tek satır döndürmeyen sütunlar tercih etmek yararlı
olacaktır. Çünkü clustered indeks özellikle aralık sorguları için yüksek performans sağlar.
Örneğin ürün tablosunda ürün adı bu iş için uygundur. Ama öte yandan mağazamızda
marklalar üzerinden bir gezinti kullanılıyorsa markakod sütununu clustered indeks yapmak
daha akıllıca olur.
(primary key constraint – unique constraint – unique – uniquefier (tekilleştirici)
Tablo üstünde clustered indeksin idğer indekslerden önce oluşturulmasında fayda
vardır. Çünkü diğer indekslerin tamamında bu indeksin oluşturduğu tekil değer kullanılır.
Clustered Indeks tanımlarken;
 Sık sorgulanan sütunlar
En çok hızlanması gereken sorgular göz önüne alınarak tanımlanması
gerekmektedir. WHERE kısmında sıkça geçen sütunlar, JOIN edilecek sütunlar,
BETWEEN ile aralık taraması yapılan sütunlar clustered indeks için tercih edilmesi
gereken sütunlardır.
 Boyutu küçük sütunlar
Çünkü bu sütuna ait bilgiler hem clustered hem de buna bağlı olacak nonclustered
indeksler için kullanılır. Bir indeks sayfasında daha fazla indeks yer alabilmesi için
indeks anahtarının küçük olması gerekmektedir.
 Daha az değişim gösteren sütunlar
Çok sık değişen bir anahtar, indekslerin çabuk yorulmasına (dağınıklaşmasına) yol
açar. Ayrıca her veri değişiminde indekslerin anahtarı değişince clustered indeks
verileri ve dolayısıyla nonclustered indeks verilerinin değişmesi gerekeceğinden sık
değişmeyen sütunların tercih edilmesi yaralı olur.
NONCLUSTERED INDEKS: Bir sütun üzerinden veri erişimi hızlandırılmak
isteniyorsa NONCLLUSTERED İNDEKS oluşturulabilir. Bir tablo üzerinde birden fazla
nonclustered indeks olabilir. Kendisi doğrudan veriye erişemez. Heap üstünden veya
Clustered İndeks üzerinden verilere erişebilir. Dolayısıyla performansı clustered indeks‟lerden
daha düşüktür.
Nonclustered İndeks tanımlarken;
 “Performans Kazanımı” ve “Tamirat Bedeli” dengesi
Tanımladığınız her indeks, bir taraftan veri okuma erişim süresini kısaltırken,
diğer taraftan veri ekleme, silme ve güncelleme esnasında veritabanını yoracaktır.
Bu nedenle çok fazla veri okuyan, az veri eklenen bir sistem üzerinde
çalışmıyorsanız gereksiz indeks tanımlamalarından kaçının.
 Arama argümanları
Clustered indeks dahilinde indeksleyemediğimiz WHERE ve JOIN gibi
sorgularda kriter olan sütunları nonclustered indeks olarak tanımlamak,
performansı arttıracaktır. Nonclustered indeksler özellikle tek satır veya birkaç
satırdan oluşan sonuçlar alan sorguları hızlandırmak için iyi bir yoldur.
 Yeterli Seçicilik
İndekslenecek sütunun çok farklı değerler alması faydalı olacaktır. Mesela
cinsiyet sütununa sadece Kadın ve Erkek değerleri girilecek ise bu sütunu
indekslemek bize bir fayda sağlamayacaktır.
 Yabancı Anahtarlar
Tablolarda yer alan yabancı anahtarlar JOIN işlemlerinin nerelerden olacağı
konusunda çok güçlü ipucudurlar. Özellikle çok satırlı tablolarınızı bağlayan
yabancı anahtarlarınızı mutlaka nonclustred indeks ile indeksleyin.
 Sorgu Kapsama
Tamamı hızlanacak sorgular için kapsamlı indeksler tanımlanabilir.
SELECT faturakod, urunkod, adet FROM SiparisDetay
WHERE FaturaKod = 1 AND urunkod = 2627 AND adet = 1
Şeklinde bir sorguyu ele alalım. Tablo üzerinde (faturaKod , urunkod) sütunlarını
içeren bir indeks tanımlandığında sorgu adet sütununu içerdiği için clustered
indeks kullanılmaya devam edecektir. Ancak (faturaKod , urunkod, adet) şeklinde
bir indeks tanımlandığında sorgunun hızı artacaktır.
Sonuç olarak, bir tablo için şu dört durumdan biri geçerlidir;
1 – Tablo heap‟tir. Yani üstünde fiziksel olarak sıralayıcı kural tanımlanmamıştır.
Kayıtlar giriliş sırası ile tutulmaktadır.
2 – Tablo üstünde bir Clustered İndeks tanımlıdır. Bir sütun veya bazen birden fazla
sütunun birleşimi, verilerin sıralanmasında kullanılmıştır.
3 – Tablo üstünde nonclustered indeks tanımlanmıştır ama clustered indeks
bulunmadığı için bu indeks heap yapısı üstünden çalışmaktadır.
4 - Tablo üstünde nonclustered indeks tanımlanmıştır ve clustered indeks üzerinden
çalışmaktadır.
Veritabanı Yönetim Sistemleri Ġndeksleri Ne Zaman Kullanır?
1. Noktasal sorguları hızlandırmak için: WHERE cümleciğinden sonra uygun indeks
bulunursa tabloyu taramak yerine indeks kullanılır.
SELECT * FROM urun WHERE urunkod = 532
Örneğin yukarıdaki cümlede urunkod sütunu clustered indeks olarak tanımlı ise
sonucu kısa bir sürede getirecektir.
2. Aralık tarayan sorguları hızlandırmak için: Değerleri bilinen bir aralıkta olan
sorguları getirirken indekslerden yararlanılabilir. (BETWEEN, LIKE gibi)
SELECT * FROM urun WHERE adi LIKE „Pan%‟
Örneğin yukarıdaki cümlede adi sütunu indekslenmiş ise veritabanı indeks yardımı
ile sonucu kısa bir sürede getirecektir.
3. JOIN işlemlerinde PRIMARY KEY-FOREIGN KEY (birincil anahtar-yabancı
anahtar) eşleşmesi varsa indekslerden yararlanılır. (Nested Join)
4. JOIN işlemlerinde birleşecek sütunlar üzerinde indeks oluşturulmuş ise bu
indekslerden yararlanabilir. (Merge Join)
İndeks bulunmazsa veya Hash Join „in daha verimli olduğu düşünülürse Hash Join
kullanılır.
5. Veritabanı, PRIMARY KEY veya UNIQUE olarak tanımlanan sütunlar için arka
planda unique indeks‟ler kullanır.
6. ORDER BY kullanıldığında verileri belli bir sırada getirmek için indeks
kullanabilir.
SQL Server Ġndeks Türleri
Unique Index
İndeks‟teki verilerin tekrarlayamaması amacıyla kullanabiliriz.
Karma (composite) indeks
Birden fazla sütunu kapsayan indeksler.
Kapsam (covering) indeksler
Bir sorgunun WHERE kısmında seçilen sütunları birlikte tek bir indeks olarak
tanımlanmasına Covering Indeks denir. Covering İndeks genellikle çok I/O işlemi gerektirir
ama sorgunun çok hızlı sonuçlanmasını sağlar. Ancak covering indeks tanımlarken indeksler
arası bölge kavramına (clustered indeks içerisindeki sütunu tekrar indekse dahil etmek
gerekmez) ve sorgulanan sütunlar ile indekslenen sütunların sırasının aynı olmasına dikkat
ederek tanımlanması gerekir.
Parçalı Ġndeksler
Farklı fiziksel dosya gruplarına dağıtılmış indekslere verilen addır. Paralel I/O
performansını arttırır.
XML Ġndeksler
XML sütunlar üzerinde de sorguların hızlanmasını sağlamak üzere indeks
tanımlamamızı sağlar.
Full-Text Ġndeksler
Metin ifadelerinin hızlı sorgulanmaları amacıyla tercih edilirler. Yapıları diğer
indeksle aynı olmasına rağmen birçok yönü ile diğer indekslerden ayrılır.
Full-text indeksler sadece char, nchar, varchar, nvarchar, varbinary, image, ve XML
türünden veri içeren sütunlar üzerinde ve bir tablo için sadece bir tane tanımlanabilirler.
FILLFACTOR ve PAD_INDEX parametrelerine karar vermek
Kayıtlar eklenip silindikçe, indeks sayfalarının doluluk oranı değişecektir. Doluluk
oranının düşmesi okuma işlemlerinin yavaşlamasına neden olur. Bu durum, OLAP için
dezavantajlı iken OLTP için avantajlı olabilir.
Öte yandan çok dolu sayfalar da yazma ve güncelleme açısından sıkıntı doğuracaktır.
Bu durum da OLTP için dezavantajlı iken OLAP için avantajlı olabilir.
Dolayısıyla bir OLTP sistemde, sürekli olarak INSERT, UPDATE ve DELETE
işlemleri olduğundan, indeks sayfalarında boş alanlar bırakarak gitmek daha akıllıca olur.
OLAN sistemler için ise indeks sayfalarının daha dolu olması tercih edilir. Bu işlemler için
FILLFACTOR ve PAD_INDEX parametreleri kullanılır. FILLFACTOR uç seviye sayfaların
ne kadar doluluk oranında olacağını belirtir. PAD_INDEX ise bu oranın uç sayfalar dışındaki
sayfalarda da geçerli olup olmayacağını ifade etmek için kullanılır. PAD_INDEX
kullanılmazsa ara seviye sayfalar için en az 2 indeks sığacak kadar olmak üzere SQL server
tarafından uygun bulunan miktarda boş yer bırakılır.
Doluluk Oranı
FILLFACTOR
Yüzde Oranı
Uç Seviye
Sayfalar
Ara Seviye Sayfalar
(PAD_INDEX yok)
Ara Seviye Sayfalar
(PAD_INDEX)
Kullanım
Yerleri
n (1-99)
% n dolu
2 indekslik boş
% n dolu
OLTP
100
% 100 dolu
2 indekslik boş
% 100 dolu
OLAP
Tablo 1. FILLFACTOR ve PAD_INDEX parametrelerinin indeks sayfalarındaki doluluk oranına etkisi
ĠNDEKS OLUġTURMAK
İndeks oluşturmak için kullanılan SQL ifadesinin en temel kullanım şekli aşağıda
verilmiştir.
CREATE [UNIQUE] indeks-tipi INDEX indeks-ismi ON tablo-ismi ( sütun-ismi )
İndeks-tipi
: CLUSTERED, NONCLUSTERED
Örnek : Personel tablosunu sicil sütununa Clustered İndeks tanımlayalım.
CREATE CLUSTERED INDEX persicil ON
personel ( sicilno )
Örnek : Personel tablosunun adi sütununa NonClustered İndeks tanımlayalım.
CREATE INDEX peradi ON personel ( adi )
UNIQUE INDEKS
Bir unique indeks, indeks olarak kullanılan sütun içerisinde benzer iki veya daha fazla
satıra ve NULL değere izin vermez.
Not : Primary Key Constraint, kullanıcı bir seçenek belirtmezse SQL Server tarafından
Clustred Unique Indeks olarak tanımlanır.
Unique Constraint ise non-clustered unique indeks olarak tanımlanır.
Örnek : Kitap tablosunun ISBN sütununa Unique (tekil) olarak NonClustered İndeks
tanımlayalım.
CREATE UNIQUE INDEX kitap_ISBN ON kitap
( ISBN )
KARMA ĠNDEKS (Birden fazla sütun üzerinde indeks)
İki veya daha fazla sütun üzerinde arama işleminin performansını arttırmak için karma
indeksler kullanılabilir.
CREATE INDEX peradi ON personel ( adi ,
soyadi )
INDEKS SĠLME
Var olan indeksi silmek için DROP INDEX ifadesi kullanılır. Veritabanından indeks
silindiği zaman kapladığı alan da geri iade edilecektir.
DROP INDEX peradi ON personel
FĠLLFACTOR ve PAD_INDEX parametrelerini ayarlamak
CREATE [UNIQUE] indeks-tipi INDEX indeks-ismi
ON tablo-ismi ( sütun-ismi )
WITH (PAD_INDEX= ON | OFF , FILLFACTOR = yüzde_oranı )
Örnek: tblSiparisDetay tablosu üzerinde, %80 doluluk oranında bir indeks tanımlayalım ve
bu indeksin ara seviyelerde de bu şekilde boşluk bırakmasını sağlayalım. Çünkü sistemimizin
sürekli olarak sipariş almasını bekliyoruz.
CREATE CLUSTERED INDEX cl_SiparisDetay
ON tblSiparisDetay ( faturakod )
WITH (PAD_INDEX = ON , FILLFACTOR = 80)
Ġndeksleri Yeniden Derlemek
Bir indeksi silip yenisini oluşturmak suretiyle kapladığı alanı azaltmak ve iç yapısını
(uç ve ara seviye sayfalar) yeniden inşa etmek amacıyla kullanılır.
ALTER INDEX ALL ON tablo_ismi REBUILD ( seçenekler )
Örnek: ALTER INDEX ALL ON tbl_urun REBUILD WITH (FILLFACTOR=80)
Ġndeksleri Yeniden Düzenlemek
İndeks tanımında yer alan FILLFACTOR değerine eşit olarak uç seviye indeks
sayfalarını yeniden yapılandırır.
ALTER INDEX cv_tblUrun ON tblUrun REORGANIZE