Agora Metin2 1
Agora Metin2
berzahx 1
berzahx
Aliyldrim 1
Aliyldrim
-ECD- 1
-ECD-
Reklam vermek için turkmmo@gmail.com

SQLite Partial Index: CREATE INDEX ... WHERE Kullanımı

image1b4bbda66acb6973.jpg


Kısmi İndeksler (Partial Index) Sadece Belirli Satırları Hedefler

Standart bir indeks, veritabanı tablosundaki tüm satırları kapsar. Buna karşılık "partial index" (kısmi indeks), oluşturulma aşamasında tanımladığınız WHERE kriterine uyan sadece belirli satırları barındırır. Bu yapının avantajları şunlardır: indeksin fiziksel boyutu daha küçüktür, sorgu sırasında taranması gereken sayfa sayısı azalır ve veri yazma (INSERT/UPDATE) işlemleri indeks kapsamı dışındaki satırlarda gerçekleştiğinde ek bir işlem yükü yaratmaz.

Söz dizimi, standart CREATE INDEX komutunun sonuna bir WHERE koşulu eklenerek oluşturulur:

SQL:
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

Örneğin idx_orders_pending indeksi, yalnızca status = 'pending' olan sipariş verilerini tutar. Gönderilmiş, iptal edilmiş veya iade edilmiş siparişler bu dizine dahil edilmez. Eğer orders tablonuzun %95'i tamamlanmış işlemlerden oluşuyorsa ve siz aktif siparişler üzerinde işlem yapıyorsanız, aynı performans hızı için standart indeksin 20 katı daha küçük bir indeksle çalışabilirsiniz.

Sorgu Planlayıcısı İndeksi Ne Zaman Tercih Eder?

Bir kısmi indeksi kullanabilmek için SQLite, çalıştırılan sorgunun indeks kapsamındaki veri kümesiyle örtüştüğünden emin olmalıdır. Bunu garantilemenin en kesin yolu, indeks oluştururken kullandığınız WHERE koşulunu sorgunuzda da aynı şekilde ifade etmektir:

SQL:
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Sorguyu çalıştırdığınızda, alınan çıktı planında USING INDEX idx_orders_pending ifadesini görmelisiniz. Sorgudan status = 'pending' filtresini kaldırdığınız anda, sistem otomatik olarak tüm tabloyu taramaya (full table scan) geçer; çünkü SQLite artık sorgunun indekslenmiş alt küme sınırları içinde kaldığını teyit edemez.

Temel kural şudur: Sorgunun WHERE ifadesi, indeksin WHERE koşulunu mutlaka kapsamalıdır. Eşitlik durumları en net senaryodur; ancak OR operatörü veya eşitsizlikler işin içine girdiğinde, EXPLAIN QUERY PLAN kullanarak durumu mutlaka kontrol etmelisiniz.

Neden Kısmi İndeks Kullanmalıyız? Üç Temel Avantaj

SQLite'ta kısmi indeks kullanmanın getirdiği üç ana fayda şunlardır:

1. Daha az disk alanı tüketir. Sadece filtrelenen satırlar kaydedilir. Verinin sadece küçük bir kısmının "sıcak" olduğu durumlarda, indeks boyutu oldukça minimize edilir.
2. Yazma maliyeti daha düşüktür. INSERT ve UPDATE işlemleri, yalnızca filtre koşulunu karşılayan satırlar için güncellenir. Örneğin, status = 'shipped' verisiyle yapılan bir işlem, yukarıdaki idx_orders_pending indeksini hiç meşgul etmez.
3. Yüksek sorgu performansı. B-tree araması indeks boyutuyla logaritmik ilişkilidir. Küçük indeks daha hızlı arama anlamına gelir; ayrıca bellekte daha az yer kapladığı için I/O işlemlerini de optimize eder.

Sütunlardaki veriler dengesiz dağılmışsa ve siz nadir olan değerleri sorguluyorsanız, bu yöntem mükemmel bir optimizasyon çözümüdür.

Partial Unique Index Kullanımının Gücü

Standart UNIQUE kısıtlaması tüm tabloya uygulanır. Ancak "yumuşak silme" (soft delete) gibi işlemlerde bu durum engel teşkil edebilir:

SQL:
-- Başarısız olur: email = 'a@x.com' olan iki satır var, biri silinmiş olsa bile.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Kısmi benzersiz indeks (partial unique index) ile kısıtlamayı yalnızca istediğiniz koşula sahip satırlar üzerinde uygulayabilirsiniz:

SQL:
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- yumuşak silinmiş
    ('a@x.com', '2026-01-05'),  -- tekrar yumuşak silinmiş
    ('a@x.com', NULL);          -- bir aktif satır, izin verilir

SELECT email, deleted_at FROM users;

Aynı e-posta adresine sahip birden fazla satır olabilir; ancak sadece deleted_at IS NULL olan (yani silinmemiş) satırlar için teklik kontrolü yapılır. Aynı e-postaya sahip ikinci bir "aktif" satır eklemeye çalıştığınızda SQLite UNIQUE constraint failed hatası döndürür.

Bu yaklaşım; kullanıcı başına tek birincil adres veya aktif abonelik gibi senaryolarda oldukça etkilidir.

NULL Değerlerin İndekslenmesi

NULL değerler indeksleme süreçlerinde özel bir yere sahiptir. Özellikle "NULL olanları yok say" mantığı çok kullanışlıdır. Seyrek dolan bir external_id sütununuz varsa şu yöntemle arama hızını artırabilirsiniz:

SQL:
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

Bu şekilde NULL değerler indekse eklenmez ve alan kazanılırken, dolu olan external_id değerleri tekil (unique) olarak korunur.

Filtreleme Koşullarında Kullanılabilecek Öğeler

Kısmi indekslerdeki WHERE filtresi için belirli kısıtlamalar mevcuttur:

• Tablo kolonlarına referans verilebilir.
• Sabit değerler kullanılabilir.
• Belirli (deterministik) dahili fonksiyonlar kullanılabilir.

Aşağıdakiler filtrede kullanılamaz:

• Farklı tablolar.
• Alt sorgular.
• random() veya CURRENT_TIMESTAMP gibi dinamik (deterministik olmayan) fonksiyonlar.
• Parametreler veya değişkenler.

SQLite, her veri değişikliğinde filtreyi yeniden değerlendirmek zorundadır; bu yüzden sonucun her seferinde aynı olması şarttır. Bu sebeple date('now') gibi zamanla değişen ifadelerle indeks oluşturulamaz.

Sorgu Planını Doğrulama Adımları

Kısmi indeks tanımladıktan sonra şu süreci

SQL:
CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. Planlayıcı indeksi seçiyor mu?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Filtrenin dışındaki sorguları yok sayıyor mu?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Filtreyi tamamen içermeyen sorguları atlıyor mu?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

Sorgularınızın EXPLAIN QUERY PLAN kullanarak beklendiği gibi ilgili indeksi seçtiğini doğrulayın. Eğer planlayıcı, indeksin dahil olmadığı sorgularda bile kısmi indeksi seçiyorsa, filtre mantığınızı gözden geçirmeniz gerekebilir.

Kısmi İndeks Ne Zaman Kullanılmamalı?**

Bu özellik güçlü bir araçtır ancak bazı durumlarda kaçınmak gerekir:

• Filtre tablonun büyük bir kısmını (örneğin %90) kapsıyorsa, kısmi indeks kullanmak verimsizdir; bunun yerine standart indeks tercih edilmelidir.
• Sorgularınızdaki filtreler değişkenlik gösteriyorsa veya ORM tarafından karmaşık şekilde üretiliyorsa, planlayıcı indeksi her zaman kullanamayabilir.
• İndekslenen "sıcak veri" kümesi zamanla değişiyorsa, deterministik yapı bozulacağı için kısmi indeks uygun olmayabilir.

Filtreniz sabitse ve tablonun küçük bir kesimini hedefliyorsa, kısmi indeksler SQLite veritabanınızdaki en etkili optimizasyon yöntemlerinden biridir.

Bir Sonraki Adım: Sorgu Planlarını Okumak

Bu rehber boyunca EXPLAIN QUERY PLAN aracının önemine değindik. Bir sonraki aşamada bu aracın çıktılarını detaylıca çözümlemeyi, terimlerin anlamlarını ve arama performansını etkileyen faktörleri inceleyeceğiz.

SQL:
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');
 

Şu an konuyu görüntüleyenler (Toplam : 0, Üye: 0, Misafir: 0)

Geri
Üst