Nonclustered Index Tasarımı: Key Sırası, INCLUDE, Covering Index ve Key Lookup

Sürüm notu: Bu yazıdaki T-SQL (Transact-SQL, Microsoft'un SQL (Structured Query Language) lehçesi) örnekleri SQL Server 2025 CU10 (17.0.5035.5, compatibility level 170) üzerinde, C# örnekleri .NET 10, Entity Framework Core (EF Core) 10.0.14 ve Microsoft.Data.SqlClient 7.1.1 ile denenmiştir.

Önce Tahmin Et

Bookstore'un müşteri panelinde "siparişlerim" ekranı var: müşteri, durum ve başlangıç tarihi seçiliyor, liste en yeniden eskiye sıralanıyor. Sorgu tam olarak şu üç kolonu filtreliyor, biz de tam bu üç kolonda bir index açıyoruz:

CREATE INDEX IX_Orders_Customer_Status_Created ON dbo.Orders (CustomerId, Status, CreatedAt);

EXEC sp_executesql N'SELECT OrderId, OrderNumber, Status, CreatedAt, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId AND Status = @Status AND CreatedAt >= @From
    ORDER BY CreatedAt DESC',
    N'@CustomerId int, @Status varchar(20), @From datetime2(0)',
    @CustomerId = 2, @Status = 'Delivered', @From = '2026-01-01';

Sorgu 3.163 satır döndürüyor. SQL Server yeni index'i kullanır mı? Kullanırsa kaç logical read tutar? Tahmin et:

Table 'Orders'. Scan count 11, logical reads 7707, physical reads 0
  |--Parallelism(Gather Streams, ORDER BY:([Orders].[CreatedAt] DESC))
       |--Sort(ORDER BY:([Orders].[CreatedAt] DESC))
            |--Clustered Index Scan(OBJECT:([Orders].[PK_Orders]), WHERE:(...))

Kullanmadı. Index tam sorgunun kolonlarında, ama optimizer bütün tabloyu taramayı seçti. Üstelik haklı: index'i WITH (INDEX(...)) ile zorladığımda sorgu 9.712 logical read tuttu, taramadan daha pahalı. Bu yazı bunun nedenini ve doğru index'i adım adım nasıl kuracağımızı anlatıyor: seek ve scan, key lookup ve tipping point, key kolonlarının sırası, INCLUDE ile covering index, filtered index, computed column, EF Core'daki karşılıkları ve her index'in yazma tarafındaki bedeli.

Senaryo: Sipariş Arama Endpoint'i

Başlangıç noktası, SCHEMA.sql'in kurduğu ve EF Core convention'larının da kuracağı index'ler: primary key (PK_Orders, clustered), UX_Orders_OrderNumber ve foreign key için IX_Orders_CustomerId. Index eklemeden önce sorguyu çalıştırıp planı SET STATISTICS PROFILE ON ile aldım. Plan satırlarının solundaki sayılar gerçek satır sayısı, çalıştırma sayısı ve tahmini satır sayısı:

    Rows Executes EstimateRows  StmtText
    3163        1     8121.229    |--Parallelism(Gather Streams, ORDER BY:([Orders].[CreatedAt] DESC))
    3163       10     8121.229         |--Sort(ORDER BY:([Orders].[CreatedAt] DESC))
    3163       10     8121.229              |--Clustered Index Scan(OBJECT:([Orders].[PK_Orders]),
                                                 WHERE:([Orders].[CustomerId]=[@CustomerId] AND [Orders].[CreatedAt]>=[@From] AND [Orders].[Status]=[@Status]))

Bir şeye dikkat et: optimizer 8.121 satır bekliyor, gerçekte 3.163 geliyor. Müşteri 2'nin 17.359 siparişi var ve optimizer üç koşulun seçiciliğini birbirinden kısmen bağımsız sayarak tahmin yapıyor. Bu tahminin nereden geldiğini dördüncü yazıda statistics üzerinden açacağız; şimdilik şunu tut: index seçimi gerçek satır sayısına göre değil, tahmine göre yapılır.

Mevcut IX_Orders_CustomerId neden kullanılmadı? Zorlayıp görelim:

Table 'Orders'. Scan count 3, logical reads 53206, physical reads 0
   17359     10    17359.0   |--Index Seek(OBJECT:([Orders].[IX_Orders_CustomerId]), SEEK:([Orders].[CustomerId]=[@CustomerId]) ORDERED FORWARD)
    3163  17359 0.46783966   |--Clustered Index Seek(OBJECT:([Orders].[PK_Orders]), SEEK:([Orders].[OrderId]=[Orders].[OrderId]),
                                  WHERE:([Orders].[CreatedAt]>=[@From] AND [Orders].[Status]=[@Status]) LOOKUP ORDERED FORWARD)

Index seek müşterinin 17.359 satırını buldu. Ama index'te yalnızca CustomerId ve clustering key OrderId var; Status, CreatedAt, OrderNumber ve TotalAmount için her satırda clustered index'e gidilmesi gerekiyor. Plandaki LOOKUP kelimesi bu: key lookup. Executes kolonunda 17.359 yazıyor; lookup her satır için ayrı ayrı çalıştı ve her biri B-tree'yi kökten leaf'e kadar inmek demek. Toplam 53.206 logical read, taramanın yedi katı.

Key Lookup ve Tipping Point

Key lookup satır başına yaklaşık üç page okuması (Orders'ın clustered index'i üç seviyeli) ve bu okumalar rastgele. Tarama ise 7.601 page'i sırayla okur. Bu yüzden belli bir satır sayısının üstünde nonclustered index + key lookup, taramadan pahalı hâle gelir. Buna tipping point denir. Sabit bir yüzde değildir; tablonun page sayısına, satır genişliğine ve tahmine bağlıdır. Bizim veride bulmak için SELECT * sorgusunu sipariş sayısı farklı müşterilerle, her seferinde yeniden compile ederek çalıştırdım:

SELECT * FROM dbo.Orders WHERE CustomerId = 6 OPTION (RECOMPILE);

Geçiş yaklaşık 2.000 ile 2.500 satır arasında, yani bir milyon satırlık tablonun yalnızca %0,2–0,25'inde. Sezgiyle "tablonun yarısını okumuyorsam index kullanılır" diye düşünmek kolay; gerçekte lookup'lı bir index çok küçük bir oranda bile vazgeçilen bir yol. Açılıştaki sorgu da aynı nedenle taramaya düştü: 8.121 satırlık tahmin bu eşiğin çok üstünde. Çözüm index'i zorlamak değil, lookup'ı ortadan kaldırmak.

Key Sırası: Önce Eşitlik, Sonra Aralık

Bileşik index'te kolon sırası, seek'in ne kadar dar bir aralığa inebileceğini belirler. B-tree satırları önce ilk kolona, eşitlerse ikinciye göre sıralar. Eşitlikle aranan kolonlar önde, aralıkla aranan kolon sonda olursa seek doğrudan istenen satırların başına iner. Aralık kolonu öne geçerse ondan sonraki kolonlar seek'e katılamaz, yalnızca okunan satırlara uygulanan bir filtre (residual predicate) olur. Farkı görmek için iki covering index açıp aynı sorguyu müşteri 1 (200.000 sipariş) ve Shipped durumu için zorladım:

CREATE INDEX IX_A ON dbo.Orders (CustomerId, Status, CreatedAt) INCLUDE (OrderNumber, TotalAmount);
CREATE INDEX IX_B ON dbo.Orders (CustomerId, CreatedAt, Status) INCLUDE (OrderNumber, TotalAmount);
/* @CustomerId = 1, @Status = 'Shipped', @From = '2025-01-01' */

IX_A: Table 'Orders'. Scan count 1, logical reads 24
    2969  |--Index Seek(OBJECT:([Orders].[IX_A]),
               SEEK:([Orders].[CustomerId]=[@CustomerId] AND [Orders].[Status]=[@Status] AND [Orders].[CreatedAt] >= [@From]) ORDERED BACKWARD)
IX_B: Table 'Orders'. Scan count 1, logical reads 650
    2969  |--Index Seek(OBJECT:([Orders].[IX_B]),
               SEEK:([Orders].[CustomerId]=[@CustomerId] AND ([Orders].[CreatedAt], [Orders].[Status]) >= ([@From], [@Status])),
               WHERE:([Orders].[Status]=[@Status]) ORDERED BACKWARD)

İkisi de "Index Seek", ikisi de 2.969 satır döndürüyor, ama biri 24, diğeri 650 page okuyor. IX_B'de seek yalnızca müşteri ve tarih aralığına inebildi; müşteri 1'in 2025'ten beri verdiği bütün siparişleri okudu ve Status'u WHERE ile tek tek eledi. Planda "Seek" görmek yetmez; SEEK: ile WHERE: kısımlarını ayrı ayrı oku. Eşitlik kolonlarının kendi arasındaki sırası ise daha az kritik: hangi sorgular hangi kolonları birlikte kullanıyor, ona bak. Örneğin bütün sorgular CustomerId veriyorsa onu başa koymak, index'i "müşterinin siparişleri" sorgularında da kullanılabilir yapar.

INCLUDE ve Covering Index

Lookup'ı kaldırmanın yolu, sorgunun ihtiyaç duyduğu bütün kolonları index'e koymak. Key'de olması gerekmeyen kolonlar INCLUDE ile yalnızca leaf seviyesine eklenir; sıralamaya katılmaz, üst seviyeleri büyütmez:

CREATE INDEX IX_Orders_Customer_Status_Created ON dbo.Orders (CustomerId, Status, CreatedAt)
    INCLUDE (OrderNumber, TotalAmount);

Table 'Orders'. Scan count 1, logical reads 25, physical reads 0
    3163     1   8121.229  |--Index Seek(OBJECT:([Orders].[IX_Orders_Customer_Status_Created]),
                                SEEK:([Orders].[CustomerId]=[@CustomerId] AND [Orders].[Status]=[@Status] AND [Orders].[CreatedAt] >= [@From]) ORDERED BACKWARD)

7.707'den 25'e. Planda tek operator kaldı: Sort da yok, çünkü index zaten CreatedAt sırasında ve ORDERED BACKWARD ile tersten okunuyor; paralellik de yok. OrderId'yi index'e yazmadık ama döndürebiliyoruz, çünkü clustering key her nonclustered index'te zaten var. Tahmin hâlâ 8.121; ama artık önemi yok, çünkü bu index'le tahmin ne olursa olsun en ucuz yol bu.

Bedeli de ölçtüm: bu covering index 6.860 page, yani tablonun kendisi (7.601 page) kadar büyük. Her yeni kolonla covering index tablonun ikinci bir kopyasına dönüşür. INCLUDE listesini "ekranda ne gösteriliyorsa" değil, gerçekten sık çalışan sorguların ihtiyacına göre tut; nadir çalışan bir rapor için lookup'a katlanmak çoğu zaman daha ucuz.

Filtered Index, Computed Column ve EF Core

Operasyon ekibinin "bekleyen siparişler" ekranı yalnızca Pending siparişlere bakıyor ve bunlar bir milyon satırın 4.800'ü. Bütün tabloyu index'lemek yerine yalnızca o satırları index'leyebiliriz:

CREATE INDEX IX_Orders_Pending ON dbo.Orders (CreatedAt)
    INCLUDE (CustomerId, TotalAmount)
    WHERE Status = 'Pending';

Index yalnızca 21 page. Ama filtered index'in kullanılabilmesi için optimizer'ın sorgudaki koşulun index filtresini kapsadığını compile anında kanıtlayabilmesi gerekiyor. EF Core aynı LINQ'i (Language Integrated Query) iki farklı şekilde çevirebiliyor: koddaki sabit SQL'e literal olarak gömülür, değişken ise parameter olur. Bunu kolonun IsUnicode(false) ile işaretlendiği ve işaretlenmediği iki modelle denedim:

var pending1 = await db.Orders.Where(o => o.Status == "Pending")
    .OrderBy(o => o.CreatedAt)
    .Select(o => new { o.OrderId, o.CustomerId, o.CreatedAt, o.TotalAmount })
    .ToListAsync();

string status = "Pending";
var pending2 = await db.Orders.Where(o => o.Status == status)
    .OrderBy(o => o.CreatedAt)
    .Select(o => new { o.OrderId, o.CustomerId, o.CreatedAt, o.TotalAmount })
    .ToListAsync();

IsUnicode(false) on Status: False
  WHERE [o].[Status] = N'Pending'
  constant: 4800 rows, logical reads 7707
  WHERE [o].[Status] = @status
  variable: 4800 rows, logical reads 7707
IsUnicode(false) on Status: True
  WHERE [o].[Status] = 'Pending'
  constant: 4800 rows, logical reads 20
  WHERE [o].[Status] = @status
  variable: 4800 rows, logical reads 7707

Dört denemenin yalnızca birinde index kullanıldı. Parameter'lı sorgu hiç kullanamaz, çünkü cache'teki plan @status'un başka bir değeri için de doğru olmalı. Sabit literal'de ise tip önemli: model kolonu Unicode sandığında EF Core N'Pending' üretiyor, SQL Server varchar kolonu nvarchar'a çevirmek zorunda kalıyor ve filtre artık eşleşmiyor. Modeli kolonun gerçek tipine uydurmak (IsUnicode(false)) hem burada hem de altıncı yazıda göreceğimiz implicit conversion sorunlarında ilk adım. Filtered index'i parameter'lı sorgularda kullanmak istiyorsan sorguya filtreyi sabit olarak yazman gerekiyor.

Computed column üzerindeki index de benzer bir eşleştirmeye dayanır. YEAR(CreatedAt) = 2024 gibi kolonu fonksiyona sokan bir koşul index'le aranamaz ve tarama yapar. Aynı ifadeyi computed column olarak ekleyip index'lediğimde sorguyu hiç değiştirmeden plan değişti:

ALTER TABLE dbo.Orders ADD CreatedYear AS YEAR(CreatedAt);
CREATE INDEX IX_Orders_CreatedYear ON dbo.Orders (CreatedYear);

SELECT COUNT(*) FROM dbo.Orders WHERE YEAR(CreatedAt) = 2024;  /* sorgu aynı */

263520
Table 'Orders'. Scan count 11, logical reads 7720   /* önce */
263520
Table 'Orders'. Scan count 1, logical reads 464     /* sonra */

Optimizer sorgudaki YEAR(CreatedAt) ifadesini computed column'un tanımıyla eşleştirdi. Bunun için ifadenin deterministic olması ve session'da QUOTED_IDENTIFIER, ANSI_NULLS gibi SET seçeneklerinin doğru olması gerekir. Daha temiz çözüm çoğu zaman sorguyu aralık olarak yazmak (CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01'); computed column, sorguyu değiştiremediğin durumlar için.

Unique index ayrıca optimizer'a bilgi verir: UX_Orders_OrderNumber sayesinde OrderNumber eşitliğinin en fazla bir satır döndüreceği bilinir ve join'lerde buna göre plan kurulur. EF Core'da bütün bu index'ler model üzerinde tanımlanır ve migration bunları üretir. GenerateCreateScript() ile ürettirdiğim çıktı:

e.HasIndex(o => new { o.CustomerId, o.Status, o.CreatedAt })
    .HasDatabaseName("IX_Orders_Customer_Status_Created")
    .IncludeProperties(o => new { o.OrderNumber, o.TotalAmount });

e.HasIndex(o => o.CreatedAt)
    .HasDatabaseName("IX_Orders_Pending")
    .IncludeProperties(o => new { o.CustomerId, o.TotalAmount })
    .HasFilter("[Status] = 'Pending'");

e.HasIndex(o => o.OrderNumber).IsUnique();

CREATE INDEX [IX_Orders_Customer_Status_Created] ON [Orders] ([CustomerId], [Status], [CreatedAt]) INCLUDE ([OrderNumber], [TotalAmount]);
CREATE UNIQUE INDEX [IX_Orders_OrderNumber] ON [Orders] ([OrderNumber]);
CREATE INDEX [IX_Orders_Pending] ON [Orders] ([CreatedAt]) INCLUDE ([CustomerId], [TotalAmount]) WHERE [Status] = 'Pending';

HasFilter'a yazdığın metin SQL'e olduğu gibi gider; EF Core onu doğrulamaz. Filtrede kullandığın literal'in tipinin, sorguların ürettiği literal'le aynı olduğunu yukarıdaki deneyle kontrol et.

Yazma Maliyeti ve Missing Index Önerileri

Her nonclustered index, tablonun her INSERT'inde, her DELETE'inde ve kendi kolonlarına dokunan her UPDATE'inde ayrıca güncellenir. 1.000 yeni siparişi bir transaction'da insert edip ürettikleri log'u ölçtüm, sonra rollback yaptım:

BEGIN TRANSACTION;
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
SELECT CONCAT('NEW-', value), 1 + (value * 7919) % 100000, 'Pending',
       DATEADD(SECOND, value, '2026-10-21'), 10, 'EUR'
FROM GENERATE_SERIES(1, 1000);

SELECT dt.database_transaction_log_record_count AS LogRecords,
       dt.database_transaction_log_bytes_used AS LogBytes
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_current_transaction AS ct ON ct.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID();
ROLLBACK;

Config              Indexes LogRecords LogBytes
------------------- ------- ---------- --------
EF Core conventions 3       1367       3455588
+ 3 indexes         6       4170       5226760

Üç index daha (covering, filtered ve CreatedAt üzerinde düz bir index) log kayıtlarını üç katına, log byte'larını %51 artırdı. Bu fark her insert'te, her deploy'da, her index bakımında ve replica'lara giden her log byte'ında tekrar ödenir. Okuma tarafındaki kazancı ölçtüğün gibi yazma tarafındaki bedeli de ölç.

Peki hangi index'leri açacağız? SQL Server optimize ederken eksik gördüğü index'leri sys.dm_db_missing_index_* DMV'lerinde (Dynamic Management View) biriktirir. Index'ler yokken iki sorguyu çalıştırdıktan sonra öneriler:

equality_columns       inequality_columns included_columns                         user_seeks avg_user_impact
---------------------- ------------------ ---------------------------------------- ---------- ---------------
[CustomerId], [Status] [CreatedAt]        NULL                                     1          97.51
[Status]               NULL               [OrderNumber], [CreatedAt], [TotalAmount] 1          80.60

İlk öneri bizim vardığımız key'i buldu ama INCLUDE önermedi; ikincisi CreatedAt'i sıralama için key'e koymak yerine include etti ve filtered index önermedi. Microsoft'un dokümanı sınırları açıkça yazıyor: öneriler tek bir sorgunun optimizasyonundaki tahmine dayanır, key kolonlarının sırasını belirtmez, unique ve filtered index önermez, include edilen kolonların boyutunu tartmaz ve en fazla 600 grup toplanır. Öneriyi bir ipucu olarak al, mevcut index'lerle birleştir, sonra ölç.

SQL Server 2019/2022'de farkı: index yapısı, key lookup, filtered index ve computed column eşleştirmesi üç sürümde de aynı. Bu yazıdaki planlarda görünen option (PLAN PER VALUE(... QueryVariantID ...)) ekleri compatibility level 160 ile gelen PSP (Parameter Sensitive Plan) optimization'dan; 2019'da (level 150) aynı sorgu tek bir planla çalışır. PSP'yi yedinci yazıda ele alacağız.

Üretimde Dikkat

Index'i en sık çalışan sorgular için tasarla. Query Store'dan ya da sys.dm_exec_query_stats'tan toplam logical reads'e göre ilk on sorguyu bul; çoğu zaman üç dört iyi tasarlanmış index iş yükünün büyük kısmını çözer.

Kullanılmayan index'leri temizle. sys.dm_db_index_usage_stats her index için seek, scan, lookup ve update sayısını tutar; hiç okunmayan ama sürekli güncellenen index saf maliyettir. Sayaçların sunucu restart'ında sıfırlandığını unutma, yeterince uzun bir süreyi gözlemle.

Index'i production'da açarken kilitleri düşün. Enterprise'da ONLINE = ON tabloyu açık tutar; Standard'da index oluşturma süre boyunca yazmaları bloklar. Büyük tablolarda bunu bakım penceresine planla.

Sık Düşülen Tuzaklar

Her kolona ayrı index açmak. CustomerId, Status ve CreatedAt üzerindeki üç ayrı index, bileşik tek bir index'in yerini tutmaz; optimizer bunları birleştirmeye nadiren değer bulur ve sen üç kat yazma maliyeti ödersin.

Missing index önerilerini olduğu gibi uygulamak. Aynı tablo için birbirine çok benzeyen beş öneri gelebilir; beşini de açmak beş kat yazma maliyeti demek. Önerileri birleştir, key sırasını kendin belirle.

Planda "Index Seek" görünce rahatlamak. IX_B örneğinde seek 650 page okudu. WHERE: kısmında bir residual predicate varsa seek'in aslında geniş bir aralığı taradığını bil.

Model ile kolon tipinin uyuşmaması. varchar kolon için EF Core'da IsUnicode(false) demezsen filtered index'ler eşleşmez ve, altıncı yazıda göreceğimiz gibi, bazı collation'larda seek tamamen scan'e döner.

Küçük Bir Deneme

★☆☆ IX_Orders_Customer_Status_Created covering index'iyle sorguya Currency kolonunu ekle. Plan ve logical reads nasıl değişti? Key lookup geri geldi mi?

★★☆ Tipping point tablosunu SELECT * yerine yalnızca OrderId, CreatedAt seçen bir sorguyla tekrarla. Eşik değişiyor mu? Neden?

★★★ OrderItems tablosunda "bir kitabın son 90 gündeki satış adedi" sorgusu için index tasarla (BookId, sipariş tarihi Orders'ta). Önce tek tablolu bir çözüm ara, sonra yazma maliyetini 1.000 satırlık insert'le ölç ve tasarımını gerekçelendir.

Bu yazıda optimizer'ın tahminleri hep karşımıza çıktı: 8.121 beklenip 3.163 gelen satırlar, tipping point'te tahmine göre verilen kararlar. Bir sonraki yazıda bu tahminlerin kaynağına iniyoruz: statistics, histogram, density vector ve cardinality estimation'ın yanıldığı yerler.