SQLite EXPLAIN QUERY PLAN: Sorgu Planını Okuma Rehb…

Q: Çıktıdaki SCAN ile SEARCH arasındaki fark nedir?

SCAN, SQLite'ın bir tablonun ya da index'in tüm satırlarını baştan sona okuduğu anlamına gelir — küçük tablolarda sorun değil ama büyük tablolarda maliyetlidir. SEARCH ise bir index ya da primary key üzerinden doğrudan eşleşen satırlara atladığını gösterir. Büyük tablolarda filtreleme yaptığınız sütunlar için neredeyse her zaman SEARCH görmek istersiniz.

Q: Sorgumun index kullanıp kullanmadığını nasıl anlarım?

Sorgunun başına EXPLAIN QUERY PLAN ekleyip çıktıda USING INDEX veya USING COVERING INDEX ifadesini arayın. Yalnızca SCAN görüyor ve hiçbir index adı geçmiyorsa, sorgu full table scan yapıyor demektir; muhtemelen bir index eklemek işinize yarar.

Q: EXPLAIN ile EXPLAIN QUERY PLAN arasındaki fark nedir?

EXPLAIN, SQLite'ın ürettiği düşük seviyeli sanal makine bytecode'unu gösterir; motor iç işleyişini incelemek için faydalı olsa da sorgu optimizasyonu için pek elverişli değildir. EXPLAIN QUERY PLAN ise tablo erişimini ve index kullanımını insan tarafından okunabilir bir şekilde özetler. Performans çalışmasında neredeyse her zaman EXPLAIN QUERY PLAN'ı tercih edersiniz.

Sorgu Planını EXPLAIN QUERY PLAN ile Görmek

Yavaş bir sorguyu optimize etmeden önce, SQLite'ın aslında ne yaptığını anlamanız gerekir. EXPLAIN QUERY PLAN, planlayıcının seçtiği stratejinin kısa bir özetini verir: hangi tablolara dokunuyor, hangi sırayla geziyor ve varsa hangi indeksleri kullanıyor. Sorgu çalıştırılmaz; size yalnızca sorgu planı döner.

Bu anahtar kelimeleri herhangi bir ifadenin başına eklemeniz yeterli:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

The output looks something like:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

Bu tek satır aslında çok şey anlatıyor: SQLite, users tablosunda SCAN değil SEARCH yapıyor; üstelik email için otomatik oluşturulan unique index'i, email alanını arama anahtarı olarak kullanarak değerlendiriyor. Yani tam da beklediğimiz şey.

SCAN vs SEARCH: Sorgu planında ilk bakılacak yer

Sorgu planındaki her satır ya SCAN ya da SEARCH ile başlar. Çıktının tamamında en kritik sinyal bu ayrımdır.

SCAN <table> — SQLite tablodaki (veya bir index'teki) tüm satırları baştan sona okur. Maliyet, tablo büyüdükçe artar.
SEARCH <table> USING ... — SQLite, bir index ya da primary key üzerinden eşleşen satırlara doğrudan atlar. Burada maliyet tablo boyutuyla değil, sonuç kümesinin boyutuyla ölçeklenir.

Aşağıda yan yana bir karşılaştırma var. Bir sütunda index var, diğerinde yok:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

İlk planda SEARCH orders USING INDEX idx_orders_customer yazıyor. İkincisinde ise SCAN orders görünüyor — status üzerinde indeks olmadığı için SQLite tüm satırları okuyor. Küçük bir tabloda bu fark gözle görülmez; ama milyon satırlık bir tabloda milisaniyeler ile saniyeler arasındaki farkı belirler.

SCAN her zaman kötü bir şey değildir. Küçük lookup tabloları ya da gerçekten satırların büyük bir kısmını döndüren sorgular için tarama yapmak doğru plandır. Ancak büyük bir tabloda seçici bir filtre kullanıyorsanız, SCAN size "buraya bir indeks ekle" diyen bir işarettir.

SQLite indeks kullanımı nasıl kontrol edilir?

Aramanız gereken ifade USING INDEX <ad> (ya da USING COVERING INDEX <ad> — buna birazdan değineceğiz). Bir indeks oluşturup planlayıcının onu seçmesini umuyorsanız, doğrulamanın yolu şudur:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

Çıktıda SEARCH events USING INDEX idx_events_user (user_id=?) ifadesini görmeniz gerekir. Eğer onun yerine SCAN events yazıyorsa, planlayıcının indeksi kullanmasını engelleyen bir şey var demektir. Bunun en yaygın sebepleri: kolonu bir fonksiyonla sarmalamak (WHERE lower(user_id) = ...), farklı tipte değerleri karşılaştırmak veya LIKE '%foo%' gibi başında joker karakter olan bir kalıp kullanmaktır.

Hızlı bir örnekle test edelim:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

+ 0 ifadesi indeksi devre dışı bırakıyor — plan yine SCAN events'e dönüyor. İndeksli sütun üzerindeki herhangi bir ifade de aynı sonucu verir.

Kapsayan indeksler planda farklı görünür

Bir indeks, sorgunun ihtiyaç duyduğu tüm sütunları içeriyorsa SQLite tabloya hiç dokunmadan sorguyu yalnızca indeks üzerinden cevaplayabilir. Plan bu durumda USING COVERING INDEX olarak raporlanır:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

Plan şu: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). Sorgu price kolonunu istiyor; indeks zaten hem sku hem de price değerlerini tuttuğu için SQLite asıl tabloya hiç dokunmuyor. Tekil bir lookup için alabileceğiniz en hızlı plan covering index'tir — hangi kolonları birlikte indeksleyeceğinize karar verirken bunu akılda tutmak çok işe yarar.

Join planlarını okumak

İşler asıl join'lerde ilginçleşiyor. Plandaki her satır, join'deki bir tabloya karşılık gelir ve satırların sırası, SQLite'ın tabloları hangi sırayla ziyaret ettiğini gösterir. İlk tablo dış (outer) tablodur; sonraki tablolar ise dış tablodaki her satır için bir kez sorgulanır.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

Tipik bir plan şuna benzer:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Yukarıdan aşağıya okuyalım: SQLite önce primary key üzerinden ilgili müşteriyi buluyor, sonra o müşteriye ait siparişleri customer_id üzerindeki indeks sayesinde çekiyor. İki satır da SEARCH — yani full table scan yok — ki istediğimiz tam olarak bu.

İkinci satırda SCAN o görseydik durum farklı olurdu: her müşteri araması orders tablosunun baştan sona taranmasına yol açardı. Büyük tablolarda bu felakete davetiye çıkarmak demek. Çözüm neredeyse her zaman aynı: join'e giren kolona bir indeks eklemek.

Bileşik sorgular ve alt sorgular

UNION, EXCEPT ve alt sorguların planları iç içe görünür. Her dal, ait olduğu üst ifadenin altında girintili olarak listelenir:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

COMPOUND QUERY başlığının altında her bir kol için bir tane olmak üzere iki alt satır görürsünüz. Subquery'ler ve CTE'ler de benzer şekilde çalışır: her birinin kendi girintili plan düğümü vardır ve her birini aynı SCAN ile SEARCH mantığıyla okursunuz.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

Alt sorgu, kendi erişim stratejisine sahip ayrı bir plan düğümü olarak görünür ("LIST SUBQUERY" veya benzeri). Aynı kontrolleri her seviyede tekrar uygulayın.

EXPLAIN ile EXPLAIN QUERY PLAN arasındaki fark

Bu ikisi birbirinden tamamen farklı şeylerdir ve sık sık karıştırılır.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (yani QUERY PLAN olmadan), SQLite'ın sanal makinesinin çalıştıracağı bytecode'u dökerek OpenRead, SeekRowid, Column, ResultRow gibi onlarca düşük seviyeli opcode'u önünüze serer. Motorun kendisinde hata ayıklıyorsanız işinize yarar; ama performans ayarı için neredeyse hiçbir faydası yoktur.

EXPLAIN QUERY PLAN ise asıl ihtiyacınız olan, insanın okuyabileceği özet çıktıdır. Tereddüt ettiğinizde her zaman EXPLAIN QUERY PLAN tarafına yönelin.

Yavaş sorgular için bir çalışma akışı

Bir sorgu yavaşladığında izleyeceğiniz döngü şöyledir:

Sorguya EXPLAIN QUERY PLAN çalıştırın.
Her tablo satırı için kendinize sorun: bu SCAN mı yoksa SEARCH mü? Büyük bir tabloda asıl şüpheli SCAN'dir.
Bir SCAN belirli bir sütun üzerinden filtreleme yapıyorsa, o sütuna bir index eklemeyi düşünün.
JOIN'lerde, iç döngüdeki tabloların join sütunu üzerinden SEARCH USING INDEX kullandığını doğrulayın.
Index'i ekledikten sonra EXPLAIN QUERY PLAN'i tekrar çalıştırın. Plan değişmiş olmalı. Eğer değişmediyse planlayıcı index'inizi kullanmaya değer bulmamış demektir; bunun nedeni genellikle tablonun küçük olması ya da filtrenin yeterince seçici olmamasıdır.
adım için uygulamalı bir örnek:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

Plan SCAN'den SEARCH'e geçti. İşte indeksin işini yaptığını gösteren sinyal bu. (Yeni oluşturulmuş, neredeyse boş bir tabloda planlayıcı yine de tarama yapmayı tercih edebilir — çünkü indeksi kullanmaya değecek kadar veri yoktur. Tabloyu doldurun ya da ANALYZE çalıştırın; tercih genelde değişir.)

Sorgu Planının Söylemediği Şeyler

EXPLAIN QUERY PLAN size stratejiyi gösterir, maliyeti değil. Sorgunun 800 ms sürdüğünü veya 50.000 satır döndürdüğünü söylemez. Onun için sürelere (CLI'da .timer on) ve satır sayılarına bakmanız gerekir. Plan ile süre ölçümü birbirini tamamlar: plan size sorgunun neden yavaş olduğunu, timer ise gerçekten yavaş olup olmadığını söyler.

Bilmeniz gereken iki sınırlama daha var:

Plan, veri büyüdükçe değişebilir. 100 satırlık bir tabloda gönül rahatlığıyla tarama yapan bir sorgu, tablo bir milyon satıra ulaştığında indekse ihtiyaç duyar. Planları geliştirme ortamındaki örnek verilerde değil, üretim boyutundaki verilerde tekrar kontrol edin.
Planlayıcı, ANALYZE ile toplanan istatistikleri kullanır. İstatistik yoksa, her zaman iyi sonuç vermeyen varsayılan değerlere geri döner. Eski ya da eksik istatistikler, beklenmedik planların en yaygın sebeplerinden biridir.

Sırada: ANALYZE ve VACUUM

Sorgu planlayıcı kararlarını, tablolarınız ve indeksleriniz hakkındaki istatistiklere dayanarak verir. Bu istatistikler eksik veya güncel değilse, kusursuz indekslenmiş bir şema bile kötü bir plan üretebilir. ANALYZE, bu istatistikleri taze tutmanın yoludur. VACUUM ise alan geri kazanmak ve veritabanı dosyasını birleştirmek için onun yol arkadaşıdır. Sırada bu var.

Sıkça Sorulan Sorular

SQLite'ta EXPLAIN QUERY PLAN ne işe yarar?

Sorguyu gerçekten çalıştırmadan, SQLite'ın o sorguyu nasıl yürüteceğini özetlemesini sağlar. Çıktıda hangi tabloların tarandığını, hangi indexlerin kullanıldığını ve join'lerin hangi sırada işlendiğini görürsünüz. Herhangi bir SELECT, INSERT, UPDATE veya DELETE ifadesinin başına EXPLAIN QUERY PLAN eklediğinizde planı görebilirsiniz.

Çıktıdaki SCAN ile SEARCH arasındaki fark nedir?

SCAN, SQLite'ın bir tablonun ya da index'in tüm satırlarını baştan sona okuduğu anlamına gelir — küçük tablolarda sorun değil ama büyük tablolarda maliyetlidir. SEARCH ise bir index ya da primary key üzerinden doğrudan eşleşen satırlara atladığını gösterir. Büyük tablolarda filtreleme yaptığınız sütunlar için neredeyse her zaman SEARCH görmek istersiniz.

Sorgumun index kullanıp kullanmadığını nasıl anlarım?

Sorgunun başına EXPLAIN QUERY PLAN ekleyip çıktıda USING INDEX <ad> veya USING COVERING INDEX <ad> ifadesini arayın. Yalnızca SCAN <tablo> görüyor ve hiçbir index adı geçmiyorsa, sorgu full table scan yapıyor demektir; muhtemelen bir index eklemek işinize yarar.

EXPLAIN ile EXPLAIN QUERY PLAN arasındaki fark nedir?

EXPLAIN, SQLite'ın ürettiği düşük seviyeli sanal makine bytecode'unu gösterir; motor iç işleyişini incelemek için faydalı olsa da sorgu optimizasyonu için pek elverişli değildir. EXPLAIN QUERY PLAN ise tablo erişimini ve index kullanımını insan tarafından okunabilir bir şekilde özetler. Performans çalışmasında neredeyse her zaman EXPLAIN QUERY PLAN'ı tercih edersiniz.

İlgili kavramlar

SQLite EXPLAIN QUERY PLAN: Sorgu Planını Okuma Rehberi