SQLite Index Oluşturma: CREATE INDEX, Çok Sütunlu ve Covering Index

İndeks olmadan: tam tablo taraması

Sıradan bir SQLite tablosunda satırlar rowid sırasına göre saklanır. Rowid ile arama yaptığınızda SQLite doğru satırı ikili aramayla bulur; ancak başka bir sütunla arama yaptığınızda her satırı okuyup koşulu tek tek denemek zorundadır. Bu işleme tam tablo taraması (full table scan) denir ve plan çıktısında SCAN olarak görünür. Yedi satırlık bir tabloda bu önemsizdir, milyonlarca satırda ise tek bir değeri bulmak için megabaytlarca veri okumak anlamına gelir.

CREATE TABLE meyve(ad TEXT, il TEXT, fiyat INTEGER);
INSERT INTO meyve VALUES ('Portakal', 'Antalya', 30), ('Elma', 'Isparta', 25),
  ('Portakal', 'Mersin', 28), ('Kiraz', 'Manisa', 90), ('Elma', 'Amasya', 27),
  ('İncir', 'Aydın', 60), ('Üzüm', 'Manisa', 45);

EXPLAIN QUERY PLAN SELECT fiyat FROM meyve WHERE ad = 'Portakal';
-- çıktı:
-- QUERY PLAN
-- `--SCAN meyve

CREATE INDEX meyve_ad ON meyve(ad);
EXPLAIN QUERY PLAN SELECT fiyat FROM meyve WHERE ad = 'Portakal';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH meyve USING INDEX meyve_ad (ad=?)

İndeks eklendikten sonra plan SEARCH ... USING INDEX meyve_ad (ad=?) oldu. Belgelerdeki anlatıma göre SQLite önce indekste ikili arama yaparak ad = 'Portakal' olan ilk girdiyi bulur, oradan rowid'i alır ve ikinci bir ikili aramayla tablodaki asıl satıra gider. Birden fazla eşleşme varsa indekste bir sonraki girdiye geçmek ucuzdur, çünkü komşu girdiler çoğunlukla aynı sayfadadır. Belgeler N satırlı bir tabloda K satır döndüren böyle bir sorgunun maliyetini kabaca (K+1)·logN ile orantılı olarak tarif eder; tam taramada maliyet N ile orantılıdır.

İndeksler sorgunun sonucunu asla değiştirmez, yalnızca sonuca ulaşma yolunu kısaltır. Yanlış bir indeks seçimi en kötü ihtimalle sorguyu yavaşlatır, yanlış satır döndürmez.

Çok sütunlu indeks ve sol önek kuralı

WHERE koşulunda AND ile bağlanmış birden fazla eşitlik varsa, en iyi sonucu bu sütunların hepsini içeren çok sütunlu bir indeks verir. Çok sütunlu indekste satırlar önce ilk sütuna göre sıralanır; ikinci sütun yalnızca ilk sütunda eşit olan girdiler arasındaki sırayı belirler, üçüncüsü ilk ikisinde eşit olanlar arasında ve bu böyle sürer. Bu yapı, indeksin hangi sorgulara yarayacağını da belirler:

CREATE INDEX meyve_ad_il ON meyve(ad, il);
EXPLAIN QUERY PLAN SELECT fiyat FROM meyve WHERE ad = 'Portakal' AND il = 'Mersin';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH meyve USING INDEX meyve_ad_il (ad=? AND il=?)

-- Yalnızca ikinci sütunla arama: indeksin sol öneki kullanılmıyor
EXPLAIN QUERY PLAN SELECT fiyat FROM meyve WHERE il = 'Mersin';
-- çıktı:
-- QUERY PLAN
-- `--SCAN meyve

-- Tek sütunlu indeksi silince iki sütunlu indeks onun işini görüyor
DROP INDEX meyve_ad;
EXPLAIN QUERY PLAN SELECT fiyat FROM meyve WHERE ad = 'Elma';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH meyve USING INDEX meyve_ad_il (ad=?)

İki sütunlu indeks, iki koşulu birlikte karşılayan tek bir girdiye doğrudan ulaştı. Yalnızca il ile yapılan aramada ise indeks işe yaramadı, çünkü il değerleri indekste yalnızca aynı ad değerine sahip girdiler içinde sıralıdır; tüm indekse yayılmış bir Mersin grubunu ikili aramayla bulmanın yolu yoktur. Bu yüzden çok sütunlu indekste sütun sırası, sorgularınızın en sık hangi sütunlarla filtrelediğine göre seçilmelidir.

Son sorgu bir başka kuralı gösteriyor: (ad, il) indeksi, yalnızca ad ile yapılan aramaları da karşılar. Belgeler bu nedenle bir indeksin diğerinin öneki olduğu durumda kısa olanın silinmesini önerir; uzun indeks kısa indeksin bütün işini görür ve gereksiz indeks her yazma işleminde boşuna güncellenir.

Kapsayan (covering) indeks

Sorgunun ihtiyaç duyduğu bütün sütunlar indeksin içindeyse SQLite asıl tabloya hiç gitmez. Belgeler buna kapsayan indeks (covering index) der. Yukarıdaki sorgu fiyat sütununu döndürüyordu; bu sütunu da indekse eklediğimizde plan değişir:

CREATE INDEX meyve_kapsayan ON meyve(ad, il, fiyat);
EXPLAIN QUERY PLAN SELECT fiyat FROM meyve WHERE ad = 'Portakal' AND il = 'Mersin';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH meyve USING COVERING INDEX meyve_kapsayan (ad=? AND il=?)

Plan satırındaki COVERING INDEX ifadesi, tablo erişiminin tamamen ortadan kalktığını gösterir. Belgelere göre bu, ikili arama sayısını yarıya indirdiği için kabaca iki kat hız kazandıran sabit bir iyileştirmedir. Bir tabloya ilk indeksi eklemenin sağladığı büyük farkla karşılaştırıldığında küçük bir incelik sayılır; her sorgu için kapsayan indeks kurmak yerine gerçekten sık çalışan ve ölçülebilir biçimde yavaş olan sorgular için kullanın.

Sıralama için indeks kullanımı

Uygun bir indeks yoksa ORDER BY içeren bir sorgunun sonucu ayrı bir adımda sıralanır. Plan çıktısında bu, USE TEMP B-TREE FOR ORDER BY satırı olarak görünür ve sonucun tamamının geçici bellekte veya diskte toplanması gerekir. Sıralama sütunu bir indeksin içinde doğru konumdaysa SQLite indeksi baştan sona (ya da DESC için sondan başa) dolaşarak satırları zaten sıralı üretir:

EXPLAIN QUERY PLAN SELECT * FROM meyve ORDER BY fiyat;
-- çıktı:
-- QUERY PLAN
-- |--SCAN meyve
-- `--USE TEMP B-TREE FOR ORDER BY

EXPLAIN QUERY PLAN SELECT il, fiyat FROM meyve WHERE ad = 'Portakal' ORDER BY il;
-- çıktı:
-- QUERY PLAN
-- `--SEARCH meyve USING COVERING INDEX meyve_kapsayan (ad=?)

İkinci sorguda hem arama hem sıralama aynı indeksle yapıldı. ad = 'Portakal' olan girdiler indekste yan yanadır ve bu grup içinde bir sonraki sütun olan il zaten sıralıdır; ORDER BY bu yüzden ek iş gerektirmedi. Sıralamanın yalnızca bir kısmı indeksle karşılanabiliyorsa SQLite büyük tek bir sıralama yerine birçok küçük sıralama yapar; belgeler bunun daha az bellek ve işlemci kullandığını ve LIMIT varsa tablonun tamamını okumaktan kurtarabileceğini belirtir.

OR koşulları

Çok sütunlu indeksler yalnızca AND ile bağlanmış koşullarda işe yarar. OR ile bağlanmış koşullarda SQLite her terimi ayrı ele alır, her biri için bir indeks kullanmaya çalışır ve bulduğu rowid kümelerini birleştirir. Bunun için OR'un her teriminin kullanılabilir bir indeksi olmalıdır; terimlerden biri bile indekssizse zaten tam tarama gerekeceği için SQLite doğrudan tabloyu tarar:

EXPLAIN QUERY PLAN SELECT * FROM meyve WHERE ad = 'Elma' OR il = 'Manisa';
-- çıktı:
-- QUERY PLAN
-- `--SCAN meyve

CREATE INDEX meyve_il ON meyve(il);
EXPLAIN QUERY PLAN SELECT * FROM meyve WHERE ad = 'Elma' OR il = 'Manisa';
-- çıktı:
-- QUERY PLAN
-- `--MULTI-INDEX OR
--    |--INDEX 1
--    |  `--SEARCH meyve USING COVERING INDEX meyve_kapsayan (ad=?)
--    `--INDEX 2
--       `--SEARCH meyve USING INDEX meyve_il (il=?)

il için bir indeks yokken sorgu tam tarama yaptı. İndeks eklendikten sonra plan MULTI-INDEX OR oldu ve iki terim ayrı indekslerle karşılandı. Belgelere göre SQLite, AND ile bağlı koşullarda birden fazla indeksin kesişimini alan bir teknik şu anda uygulamaz; AND koşulları için doğru çözüm çok sütunlu indekstir.

UNIQUE indeks ve NULL değerler

CREATE UNIQUE INDEX aynı anahtarın iki kez girilmesini engeller. Tablo tanımındaki UNIQUE ve PRIMARY KEY kısıtları da arka planda otomatik indeks oluşturur; bunların adı sqlite_autoindex_TABLO_N biçimindedir ve sqlite_schema tablosundaki sql değerleri NULL'dır. INTEGER PRIMARY KEY için ise ayrı bir indeks oluşturulmaz, çünkü bu sütun zaten rowid'in kendisidir. Ayrıntılar için SQLite PRIMARY KEY ve rowid yazısına bakın.

CREATE TABLE kullanici(id INTEGER PRIMARY KEY, eposta TEXT UNIQUE, tc TEXT, kayit TEXT);
CREATE UNIQUE INDEX kullanici_tc ON kullanici(tc);

INSERT INTO kullanici(eposta, tc) VALUES ('a@ornek.com', NULL), ('b@ornek.com', NULL);
SELECT count(*) FROM kullanici WHERE tc IS NULL;
-- çıktı: 2      (NULL değerler birbirinden farklı sayılır)

INSERT INTO kullanici(eposta, tc) VALUES ('a@ornek.com', '1');
-- hata: UNIQUE constraint failed: kullanici.eposta

SELECT name, tbl_name, sql IS NULL FROM sqlite_schema WHERE type = 'index';
-- çıktı:
-- sqlite_autoindex_kullanici_1|kullanici|1
-- kullanici_tc|kullanici|0

DROP INDEX sqlite_autoindex_kullanici_1;
-- hata: index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped

Benzersiz indekslerde NULL değerler birbirinden farklı sayılır; bu yüzden aynı sütuna birden fazla NULL yazılabildi. Belgeler bunun SQL-92 standardının iki olası yorumundan biri olduğunu ve PostgreSQL, MySQL, Firebird ve Oracle'ın da aynı yorumu izlediğini, Informix ile Microsoft SQL Server'ın ise tüm NULL'ları eşit saydığını not eder. Kısıtla gelen otomatik indeksler DROP INDEX ile silinemez; kısıtı kaldırmak tablo yapısını değiştirmeyi gerektirir.

Var olan veride yinelenen değerler varsa UNIQUE indeks oluşturulamaz. Aşağıdaki tabloda sql değeri iki kez geçtiği için komut başarısız oldu; büyük ve küçük harfli SQL ise varsayılan BINARY karşılaştırmada farklı bir değer sayılır:

CREATE TABLE etiket(id INTEGER PRIMARY KEY, ad TEXT, tarih TEXT);
INSERT INTO etiket(ad, tarih) VALUES ('sql', '2026-01-02'), ('SQL', '2026-03-04'), ('sql', '2026-02-01');

CREATE UNIQUE INDEX etiket_ad ON etiket(ad);
-- hata: UNIQUE constraint failed: etiket.ad

İndeksleri incelemek

Bir tablonun indekslerini ve her indeksin hangi sütunlardan oluştuğunu pragma fonksiyonlarıyla sorgulayabilirsiniz. Komut satırında .indexes komutu da indeks adlarını listeler; 3.53.0 sürüm notlarına göre bu komutun desen argümanı artık tablo adıyla değil indeks adıyla eşleşir ve komuta yeni seçenekler eklendi.

SELECT name, "unique", origin, partial FROM pragma_index_list('kullanici');
-- çıktı:
-- kullanici_tc|1|c|0
-- sqlite_autoindex_kullanici_1|1|u|0

SELECT seqno, cid, name FROM pragma_index_info('kullanici_tc');
-- çıktı: 0|2|tc

SELECT seqno, cid, name, "desc", coll, key FROM pragma_index_xinfo('kullanici_tc');
-- çıktı:
-- 0|2|tc|0|BINARY|1
-- 1|-1||0|BINARY|0

pragma_index_list çıktısındaki origin sütunu indeksin nereden geldiğini gösterir: c CREATE INDEX ile oluşturulduğunu, u bir UNIQUE kısıtından geldiğini, pk ise PRIMARY KEY'den geldiğini belirtir. partial sütunu indeksin kısmi olup olmadığını söyler. pragma_index_xinfo ise anahtar sütunlarına ek olarak indeksin sonuna eklenen rowid'i de gösterir: cid değeri -1 olan ve key değeri 0 olan satır budur. Bu, belgelerdeki "indeks girdisi, sütun değerleri ve ardından gelen rowid'den oluşur" anlatımının somut görüntüsüdür. Diğer şema sorguları için SQLite tabloları listeleme yazısına bakın.

Sıralama yönü ve COLLATE

İndekslenen her sütun veya ifadeden sonra ASC ya da DESC yazılabilir. Bu bilgi, 3.7.10 sürümünden beri varsayılan olan 4 numaralı şema biçiminde dikkate alınır; çok eski "legacy" biçimde yok sayılır. NULLS FIRST ve NULLS LAST ise indekslerde desteklenmez. SQLite sıralamada NULL değeri her şeyden küçük kabul ettiği için NULL'lar ASC indekste başta, DESC indekste sonda yer alır. Belgelerdeki örneklerde görüldüğü gibi SQLite bir indeksi sondan başa da dolaşabilir; bu yüzden tek sütunlu bir indeks ORDER BY sütun DESC için de kullanılabilir (aşağıdaki son örnekte olduğu gibi).

COLLATE ise indeksteki metinlerin hangi karşılaştırma kuralıyla sıralanacağını belirler. Belirtilmezse sütunun CREATE TABLE içindeki kuralı, o da yoksa BINARY kullanılır. Sorgudaki karşılaştırma kuralı indeksinkiyle aynı değilse indeks arama için kullanılamaz:

CREATE INDEX etiket_ad_nocase ON etiket(ad COLLATE NOCASE);

EXPLAIN QUERY PLAN SELECT id FROM etiket WHERE ad = 'sql';
-- çıktı:
-- QUERY PLAN
-- `--SCAN etiket USING COVERING INDEX etiket_ad_nocase

EXPLAIN QUERY PLAN SELECT id FROM etiket WHERE ad = 'sql' COLLATE NOCASE;
-- çıktı:
-- QUERY PLAN
-- `--SEARCH etiket USING COVERING INDEX etiket_ad_nocase (ad=?)

İlk sorgu sütunun varsayılan BINARY kuralıyla karşılaştırdığı için NOCASE indeksinde arama yapılamadı; planlayıcı indeksi yalnızca tablodan daha küçük olduğu için baştan sona taradı. Sorguya COLLATE NOCASE eklendiğinde ikili arama mümkün oldu. Bu örnekte yalnızca ASCII harfler kullandık; NOCASE'in Türkçe harflerdeki sınırları için SQLite COLLATE ve NOCASE yazısına bakın.

Sütunu fonksiyonla sararsanız

İndeks sütunun kendisine kuruluysa, WHERE koşulunda sütunu bir fonksiyonun içine aldığınızda indeks kullanılamaz. Aralık karşılaştırması ise indeksle uyumludur:

CREATE INDEX etiket_tarih ON etiket(tarih);

EXPLAIN QUERY PLAN SELECT * FROM etiket WHERE tarih >= '2026-02-01';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH etiket USING INDEX etiket_tarih (tarih>?)

EXPLAIN QUERY PLAN SELECT * FROM etiket WHERE substr(tarih, 1, 7) = '2026-02';
-- çıktı:
-- QUERY PLAN
-- `--SCAN etiket

EXPLAIN QUERY PLAN SELECT * FROM etiket ORDER BY tarih DESC LIMIT 2;
-- çıktı:
-- QUERY PLAN
-- `--SCAN etiket USING INDEX etiket_tarih

Ay bazlı filtreyi substr() ile yazmak tam taramaya yol açtı. Aynı sonucu çoğu zaman bir aralık koşuluyla, örneğin tarih >= '2026-02-01' AND tarih < '2026-03-01' biçiminde yazabilirsiniz. İfadenin kendisini indekslemek de mümkündür; bunun kuralları partial index ve ifade indeksi yazısında. Son sorgu ise ORDER BY ile LIMIT birlikteyken indeksin tablonun tamamını sıralamaktan kurtardığını gösteriyor: plan, indeksi sondan başlayarak dolaşıyor ve ilk iki satırda durabiliyor.

İndeks oluşturma, IF NOT EXISTS ve DROP INDEX

İndeks adları veritabanı genelinde benzersizdir. IF NOT EXISTS, aynı adlı bir indeks varsa komutu sessizce etkisiz kılar; mevcut indeksin tanımı farklı olsa bile değiştirmez. Bir tabloya eklenebilecek indeks sayısında keyfi bir sınır yoktur; bir indeksteki sütun sayısı ise SQLITE_LIMIT_COLUMN sınırına tabidir. DROP INDEX indeksi diskten tamamen kaldırır; geri getirmenin tek yolu CREATE INDEX komutunu yeniden çalıştırmaktır.

CREATE INDEX IF NOT EXISTS kullanici_kayit ON kullanici(kayit DESC);
CREATE INDEX IF NOT EXISTS kullanici_kayit ON kullanici(kayit);   -- hata yok, hiçbir şey yapmaz
CREATE INDEX kullanici_kayit ON kullanici(kayit);
-- hata: index kullanici_kayit already exists

CREATE INDEX hatali ON kullanici(yok);
-- hata: no such column: yok

DROP INDEX IF EXISTS kullanici_kayit;

İndeks ne zaman zarar verir

Her indeks ayrı bir B-ağacıdır ve tablodaki her INSERT, UPDATE ve DELETE işleminde güncellenmesi gerekir. Bu yüzden indeksler okumayı hızlandırırken yazmayı yavaşlatır ve dosya boyutunu büyütür. Sorgularınızın gerçekten kullanmadığı, başka bir indeksin öneki olan veya çok az ayırt edici değere sahip sütunlara kurulmuş indeksler bu maliyeti boşuna öder.

Birden fazla indeksin aynı işi görebildiği durumlarda hangisinin seçileceğine planlayıcı karar verir. Belgelerdeki örneğe göre ANALYZE çalıştırılmamışsa bu seçim keyfidir; ANALYZE ile toplanan istatistikler planlayıcının daha seçici indeksi tercih etmesini sağlar. İstatistik toplama ve PRAGMA optimize için SQLite ANALYZE yazısına, komut satırının sorgulara göre indeks öneren deneysel .expert komutu için EXPLAIN QUERY PLAN yazısına, genel sorgu iyileştirme ilkeleri için SQL sorgu optimizasyonu yazısına bakın.

WITHOUT ROWID tablolarda da aynı ilkeler geçerlidir; tek fark, indeks girdilerinin sonunda rowid yerine tablonun PRIMARY KEY değerinin bulunmasıdır. Ayrıntılar için SQLite WITHOUT ROWID tablolar yazısına bakın.

Sık yapılan hatalar

• Çok sütunlu indekste sütun sırasını sorgulara bakmadan seçmek; ikinci sütunla yapılan aramalar indeksi kullanamaz.
• Hem (a) hem (a, b) indeksini tutmak; kısa olan gereksizdir.
• WHERE koşulunda indeksli sütunu fonksiyonla sarmak.
• NOCASE indeksi kurup sorguyu varsayılan BINARY karşılaştırmayla yazmak.
• OR koşulunun yalnızca bir terimine indeks ekleyip sorgunun hızlanmasını beklemek.
• UNIQUE indeksin NULL değerleri de tekilleştireceğini sanmak.
• Plan çıktısına bakmadan "indeks ekledim, artık hızlı" diye varsaymak.

Sık Sorulan Sorular

PRIMARY KEY sütunu için ayrıca indeks oluşturmalı mıyım?

Hayır. INTEGER PRIMARY KEY rowid'in kendisidir ve zaten en hızlı erişim yoludur. Diğer PRIMARY KEY ve UNIQUE kısıtları için SQLite kendisi otomatik indeks oluşturur; aynı sütunlara ikinci bir indeks eklemek yalnızca yazmayı yavaşlatır.

Büyük bir tabloda indeks oluşturmak tabloyu kilitler mi?

CREATE INDEX bir yazma işlemidir ve sürdüğü boyunca diğer bağlantıların yazmasını bekletir. Canlı sistemlerde büyük indeksleri trafiğin az olduğu bir zamanda oluşturun ve diğer bağlantılarda PRAGMA busy_timeout ayarlı olsun. Kilitlenme ayrıntıları için database is locked hatası yazısına bakın.

Hangi indekslerin kullanılmadığını nasıl anlarım?

Pratik yol, uygulamanızın sık çalışan sorgularını toplayıp her birini EXPLAIN QUERY PLAN ile kontrol etmektir. Hiçbir planda görünmeyen ve başka bir indeksin öneki olan indeksler silinmeye en güçlü adaylardır. Silmeden önce indeksin CREATE INDEX metnini sqlite_schema tablosundan kaydedin.

İndeks, LIKE sorgularını hızlandırır mı?

Belirli koşullar altında evet; önek aramaları indeksle çalışabilir. Bu koşullar ve case_sensitive_like ayarı SQLite LIKE, GLOB ve REGEXP yazısında ayrıntılı anlatılıyor.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · SQLite EXPLAIN QUERY PLAN · SQLite ANALYZE · SQLite partial index · SQLite PRIMARY KEY · SQL sorgu optimizasyonu