SQL Index nedir, ne işe yarar, nasıl yapılır?

–Hey future me! Unuttun di mi?

–Bak şimdi hacı, anlatıyorum.

Index : Yani Türkçesi İndeks yada fihristtir.

Bir tabloya SELECT çektiğimizde SQL arama yapmak için en kısa yolu seçecektir. Hatta bazen index’i kullanmak yerine tüm tabloyu satır satır aramayı seçer, çünkü index yapısı hatalı olabilir.

Ne için gerekli bu index?

Az veri olan bir tabloda gerek görmeyebilirsin ancak 1 milyon veri olan bir tablon var ise kesinlikle kullanmalısın. Çünkü yaptığın index’lere göre veriler sıralanacak ve bu sayede sorguların daha hızlı dönecek.

2 tane index tipi var; Clustered ve Non-Clustered.

Eğer veriler fiziksel olarak sıralanıyor ise Clustered, fiziksel değil ise Non-Clustered index’tir. Hemen uyandın değil mi Clustered index’i ID kolonuna yapılması gerektiğini.

Clustered Index (Kümelenmiş); Verileri fiziksel ve alfabetik olarak sıralar. (Örn : Telefon rehberi) Bu tip indexler B-Tree yapısına sahiptir. Verilerin oluştuğu sayfalara pointer(işaretleyici) kullanarak gitmesine gerek kalmadan verinin direk yerine ulaşabilir. Kümelenmiş bir index kullanıldığında ulaşılan yer verinin kendisidir. Eğer tabloda Primary Key (Birincil anahtar) var ise bu kümelenmiş index yapısına sahiptir. Her tabloda en fazla bir tane bulunabilir. Clustered Index yapılan kolon artan veya azalan bir değere sahip olmalıdır.

Non-Clustered Index ;

Örnekle anlatayım; Bir kitabın içindekiler kısmı gibi düşün, kitapta bir bölümü bulmak için bütün sayfaları aramak yerine içindekiler kısmına bakmak hem daha mantıklı hem daha hızlı olacaktır.

Gelecek olan yada gelen sorgulara göre yapılması gereklidir. Non-Clustered index yaparken bir ayrım var, bu index’e included alan ekleyebiliyorsun. Bunu da şöyle bir örnekle anlatayım;

Herhangi bir kargo şirketinden kargo göndereceksin. Adres seçerken ilk başta ülkeden başlatır. (Sıralamamız da şöyle olsun: Ülke>Şehir>İlçe>Mahalle)

Şimdi ilk başta ülkeyi seçtin Türkiye olarak sonra gitti DB’ye (query de SELECT * FROM Adres WHERE Ulke=’Türkiye’) Türkiye’nin illerini getirdi, sonra illerden seçtin yine gitti DB’ye (query de SELECT * FROM Adres WHERE Ulke=’Türkiye’ AND Sehir=’Konya’) getirdi ilçeleri…

Bu şekilde bir tablon varsa ve ilk koşulun sürekli aynıysa Index yaparken Index Key Columns’a Ulke kolonunu ekleyip Included Columns alanına Sehir,Ilce,Mahalle kolonlarını eklediğin de query’lerin daha hızlı yanıt alacaktır. Eğer buna benzer bir tablon varsa Non-Clustered index’te verinin direk kendisine değil de bulunduğu sayfaya ulaştığı için bu seçeneği kullanmak mantıklı olacaktır.

NOT : Örnekteki gibi bir tablo kullanmamaya özen gösterin, tablolarda tekrar eden veriler olması pek hoş olmayacaktır. Bunun yerine Foreign Key ile başka bir tablodan çekmek hızlı ve sağlam olur. Tablo yapıları ve cart curt hakkında yakında yazacağım.

Index’ler aşağıdaki gibi her tablonun altında Indexes klasörü altında gözükürler.

 

screenshot_31

Aşağıdaki sorguyu index yapmadan önce ve sonra çalıştırdım veriler aşağıda.

Index yapılmadan önce ;

notindextablescan

Statistics IO and TIME ON

Index yapıldıktan sonra ;

Estimated Execution Plan

Statistics IO and TIME ON

Görüldüğü gibi süreler, okuduğu satırlar bakımından index olmadan verimsiz bir tablo oluyor.

Fill Factor nedir?

Indexlemeler yapılırken page-page (sayfa-sayfa) yapılmaktadır. Bu değer Fill factor(sayfa doluluğu) olarak adlandırılır. Default olarak 0%(sıfır) gelir, bu da 100% demektir. Yani eğer siz düzeltmezseniz 100% olarak kalacaktır.

Örneğin ;

  • 1.000.000 satırlık bir tablomuz var ve her sayfada 10 değer bulunuyor. (Fill factor=10 olarak ayarlandıysa.)
  • (IO/Page)      1.000.000/10 = 100.000
  • (IO/Saniye)   100.000/100 = 1000 Sn (Query sonuç döndürürken harcanacak zaman)

Örnekte görüldüğü gibi Fill factor oranımız düşük olursa sonuçlar ona oranla yavaş dönecektir. Fill Factor değerini seçerken SQL Server tablonuza ne kadar yeni değer girileceğini göz önünde bulundurmalısın. Eğer tablon sadece read only bir tablo olacaksa, fill factoru 100’e olabildiğince yakın yap. Eğer tablona yüklü miktarda yeni veri girişi yapılacaksa, bu değeri daha düşük tutman mantıklı olacaktır. Aksi takdirde database performansını arttırmaya çalışayım derken sayfa bölünmelerine sebep olarak büyük performans düşüşüne sebep olabilirsin.

Fill factor oranını 80% olarak ayarladığında 20% lik kısmını daha sonraki veriler için boş bırakacaktır. Genel olarak bir tabloya az veri girilecekse bu şekilde ayarlanabilir, böyle tablolara read-only (okumak amaçlı) tablolar da denilir. (Yada ben öyle diyorum)

Page Split nedir?

Bilindiği gibi SQL Server’in dosya tabanında Page denen 8KB’lık alanlar mevcuttur ve bunlardan 8 tanesi (8*8K=64K) bir extenti oluşturur. Data ve Index verileri page’lerde tutulur ve okuma/yazma işlemleri extent bazında gerçekleşir. Her page’de kaç satırın bulunacağı satırın uzunluğuna bağlı olarak değişebilir. Eğer uzun bir row gelecek olursa veya mevcut row çok uzun bir veriyle güncellenecek olursa SQL Server değişikliği yapmak için mevcut satırların yerini değiştirebilir eğer değişiklik sayfaya sığmıyorsa SQL Server yeni bir sayfa (data page) oluşturup satırları buraya taşır. Bu şekilde öndeki veya sonraki sayfada öteleme işlemleri yapmak zorunda kalır. Bu işleme page split denilir.

Fragmentation nedir?

Parçalanma. Fragmentation olan bir tablo, daha çok disk I/O’su yapacaktır. Bozulan index’ler neredeyse işlevsiz olacağından bu parçalanmaları temizlemek için index iki işlem yapılabiliyor. Clustered alanlar her kayıt eklendiğinde otomatik olarak işlem yaptıkları için Fragmentation oranı yoktur. Non-Clustered alanlar ilk düzenleme yapıldıktan sonra diğer kayıtları indexlemenin altına rastgele eklediği için parçalanmalar olmaktadır. Fragmantation oranını sıfırlamak için REBUILD ve REORGANIZE işlemlerine göz atalım.

–Bu göz atalım lafına da bitiyorum çok ukala 🙂

Parçalanma oranını görebileceğin DMV;

SELECT * FROM sys.dm_db_index_physical_stats(db_id('NORTHWIND'),object_id('Cust'),NULL,NULL,NULL)

 

Bunlar;

  • REBUILD (yeniden inşa et) : Data’ları sıfırdan sıralar ve düzenler. Çoğu dokümanda DROP/CREATE yapıyorcasına bahsedilmiş REBUILD işleminden. İşlem yapılırken ekstra disk alanına ihtiyaç duyulmaktadır.
  • REORGANIZE (yeniden organize et) : Düzensiz sıralanan page’lerdeki data’ları tekrar düzenler. İşlem yapılırken ekstra disk alanı gerekmez.

Şimdi bu iki işlemin farklarını anlatacağım;

–Bi DBA abimin verdiği örnek ile anlatacağım, selam olsun sana da buradan Yasin kaptan 🙂

Bir kutu düşünün bunun içinde A’dan Z’ye sıralanmış belgeler var diyelim. (İşte bu index’in ta kendisidir.)  REBUILD işlemi yapıldığında kutunun içindeki belgeler çıkartılıp yerine yeni bir kutu konulur ve belgeler sırayla dizilir. Bu sebeple REBUILD işlemi yapıldığında tablo lock olur. Online yada Allways On (Her zaman aktif, Ör: internet sitesi db’si)  yapılarda tavsiye edilmez. Şimdi gelelim REORGANIZE işlemine, bu işlemi yaptığımızda kutunun içindeki belgeler temiz bir şekilde yeniden sıralanır. Belgeleri çıkarmak gibi bir işlem yapmadığı için tablo lock olmaz.

Hangisini uygulayacağına dair etkenler;

  • Fragmantasyon değeri düşükse REORGANIZE, yüksek ise REBUILD tavsiye edilir,
  • Tablonun data boyutuna göre, (Boyutu yüksek olan tablolarda REORGANIZE tabloyu lock’lamadığı için kullanılabilir ancak tablonun hiç kullanılmadığı saatler var ise o saatlerde REBUILD işlemi de yapabilirsin.)
  • Always On olan veya olmayan yapılar.
Fragmante Oranı İşlem
> 5% and <=30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

(*) Rebuild index operasyonu online ya da offline yapılabilmektedir. Reorganize ise her daim online olarak işletilir.

Index yapmak için aşağıdaki query’leri kullanabilirsiniz.

CREATE CLUSTERED INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE NONCLUSTERED INDEX index_name
ON table_name (column_name)

--Yukarıdaki tanımların sonuna Fill Factor belirlemek istersen
WITH FILLFACTOR = 50

--Bir tablodaki index'leri görmek için
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'

--Rebuild işlemi -- ALL yada indeks adı
ALTER INDEX ALL ON [dbo].[TableName] REBUILD

--Reorganize işlemi -- ALL yada indeks adı
ALTER INDEX ALL ON [dbo].[TableName] REORGANIZE

 

Index kullanımı çok önemliyken, gereksiz,kullanılmayan Index’leri sistemde bulundurmak bir o kadar dezavantajdır. Periyodik olarak kullanılmayan ya da yazma istatistiği okuma istatistiğinden fazla olan index’lerin belirlenip drop edilmesi gerekir.

Ayrıca belirteyim, bir sütuna PK (Primary Key) verdiğinizde otomatik olarak index oluşturacaktır. Tabloda daha önce Clustered tanımlı değil ise Clustered, tanımlı ise Non-Clustered olarak index oluşturur.

ALTER TABLE Cust
ADD PRIMARY KEY (ID)

Not : Estimated Execution Plan; Sorgu çalıştırıldığında SQL bu sorguya cevap döndürmek için ne yaptığını adım adım gösterir.

Not 2 : İstatistikleri kontrol etmek için aşağıdaki query’leri deneyebilirsiniz, işiniz bitince kapatabilirsiniz aynı query’i OFF yaparak

SET STATISTICS IO ON
SET STATISTICS TIME ON

 

Ayrıca;

--Fragmantasyon oranını görebileceğin
SELECT
ps.object_id,
i.name as IndexName,
OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
OBJECT_NAME (ps.object_id) as ObjectName,
ps.avg_fragmentation_in_percent,
ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ps
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
WHERE avg_fragmentation_in_percent = 5 AND ps.index_id = 0
ORDER BY avg_fragmentation_in_percent desc

--SCAN ve SEEK oranlarına göre fazla SCAN yapan indexleri bu query ile bularak drop edebilirsiniz.
SELECTOBJECT_NAME(us.object_id) as tableName,
i.name as indexName,
(us.user_seeks+us.user_scans+us.user_LookUps) as TotalRead,
us.user_updates as TotalWrite,
us.user_updates*100/case (us.user_seeks+us.user_scans+us.user_LookUps) when 0 then 1 else (us.user_seeks+us.user_scans+us.user_LookUps) end as WritePercentage,
us.last_user_seek,
us.user_seeks,
CASE us.user_seeks WHEN 0 THEN 0
ELSE us.user_seeks*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS SeekPercentage,
us.last_user_scan,
us.user_scans,
CASE us.user_scans WHEN 0 THEN 0
ELSE us.user_scans*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS ScanPercentage,
us.last_user_LookUp,
us.user_LookUps,
us.last_user_update,
us.user_updates
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id=us.object_id and i.index_id = us.index_id
WHERE us.database_id = DB_ID('NORTHWND')
ORDER BY 5 DESC
GO

--Kullanılmayan indeksleri bulmak için query
USE NORTHWIND
GO SELECT TOP 10 DB_NAME(id.database_id) AS databaseName, id.statement AS TableName, id.equality_columns, id.inequality_columns, id.included_columns, gs.last_user_seek, gs.user_seeks, gs.last_user_scan, gs.user_scans, gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) AS ImprovementValue FROM sys.dm_db_missing_index_group_stats gs INNER JOIN sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle INNER JOIN sys.dm_db_missing_index_details id on id.index_handle = ig.index_handle ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC GO

--Sorgulara göre SQL tarafından yapılması öngörülen indeksler
SELECT TOP 10 DB_NAME(id.database_id) as databaseName,
id.statement as TableName,
id.equality_columns,
id.inequality_columns,
id.included_columns,
gs.last_user_seek,
gs.user_seeks,
gs.last_user_scan,
gs.user_scans,
gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) as ImprovementValue
FROM sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details id on id.index_handle = ig.index_handle
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC
GO

 

1 Yorum

Leave a Comment.