Yavaş sorguyu bulmak ve doğru indeksi seçmek: EXPLAIN ile adım adım

Bir uygulama "bazen yavaşlıyor" diye şikâyet geldiğinde ilk bakılacak yer sunucu değil, veritabanı sorguları oluyor. Yavaşlığın büyük kısmı tek bir yerden çıkıyor: tabloda olması gereken bir indeksin eksik olması. Satır sayısı azken bu fark edilmiyor; tablo büyüdükçe aynı sorgu her gün biraz daha pahalıya patlıyor.

Bu yazıda portföyümdeki bir çalışmadan somut bir örnekle gideceğim. Kurgusal bir online eğitim platformu: 15.000 öğrenci, 300 sınav ve submissions tablosunda 400 bin geçmiş sınav gönderimi. Platform sınav saatlerinde çöküyordu ve sebeplerin başında bu tabloda hiç indeks olmaması geliyordu.

Sorun nerede: her gönderimde dört tam tarama

Devralınan sürümde (v1) bir öğrenci sınavı gönderdiğinde sunucu şu sorguları sırayla çalıştırıyordu:

-- 1) Bu öğrenci bu sınavı daha önce göndermiş mi?
SELECT id FROM submissions WHERE exam_id = ? AND student_id = ?;
-- 2) Kayıt
INSERT INTO submissions (exam_id, student_id, score, answers, created_at) VALUES (?, ?, ?, ?, ?);
-- 3) Sıralama: benden yüksek not alan kaç kişi var?
SELECT COUNT(*) FROM submissions WHERE exam_id = ? AND score > ?;
-- 4) Toplam gönderim
SELECT COUNT(*) FROM submissions WHERE exam_id = ?;

Kâğıt üstünde masum görünüyor. Ama submissions tablosunda yalnızca birincil anahtar vardı. Yani 1, 3 ve 4 numaralı sorguların her biri 400 bin satırı baştan sona okuyordu. Buna bir de notlandırmanın soru başına ayrı sorgu atması eklenince (40 soruluk sınavda 40 sorgu, onlar da indekssiz) tek bir gönderim veritabanını saniyelerce meşgul ediyordu.

Bunu tahminle değil, sorgu planıyla görmek gerekiyor.

EXPLAIN çıktısını okumak

Her veritabanında sorgunun nasıl çalıştırılacağını gösteren bir komut var. SQLite'ta EXPLAIN QUERY PLAN, PostgreSQL ve MySQL'de EXPLAIN. Bu platform SQLite kullanıyor, v1 veritabanında mükerrer kontrolünün planı şöyle:

EXPLAIN QUERY PLAN
SELECT id FROM submissions WHERE exam_id = 5 AND student_id = 42;

SCAN submissions

SCAN tam tarama demek: veritabanı tabloyu satır satır okuyup koşula uyanları arıyor. Sıralama ve sayım sorgularında da aynı satır çıktı. İndeksleri ekledikten sonra aynı sorgular:

SEARCH submissions USING COVERING INDEX uq_submissions_exam_student (exam_id=? AND student_id=?)
SEARCH submissions USING COVERING INDEX idx_submissions_exam_score (exam_id=? AND score>?)
SEARCH submissions USING COVERING INDEX idx_submissions_exam_score (exam_id=?)

SEARCH ... USING INDEX indeksle doğrudan ilgili bölgeye atlandığını gösteriyor. COVERING ise daha da iyi bir haber: sorgunun ihtiyaç duyduğu bütün sütunlar indeksin içinde olduğu için tabloya hiç gidilmiyor.

PostgreSQL'de karşılıkları biraz farklı adlarla geliyor. Seq Scan tam tarama, Index Scan ve Bitmap Heap Scan indeks kullanımı, Index Only Scan de SQLite'taki "covering" durumu. EXPLAIN ANALYZE sorguyu gerçekten çalıştırıp tahmini satır sayısıyla gerçek satır sayısını yan yana verir. Bu iki sayı arasında büyük fark varsa istatistikler eskimiş olabilir; ANALYZE ile tazelemek gerekir.

Yavaş sorguyu nereden bulacaksınız?

EXPLAIN'i hangi sorguya çalıştıracağınızı bilmeniz gerekiyor. Birkaç yol var:

• PostgreSQL'de pg_stat_statements eklentisi, sorguları toplam ve ortalama süreye göre sıralamanızı sağlar. İlk bakılacak yer toplam süresi en yüksek olanlar, çünkü sık çalışan orta hızlı bir sorgu, nadir çalışan yavaş bir sorgudan daha çok yük bindirir.
• MySQL'de yavaş sorgu günlüğü (slow query log) belirli bir sürenin üstündeki sorguları dosyaya yazar.
• Uygulama tarafında rota bazlı süre ölçümü. Eğitim platformu çalışmasında /metrics çıktısında hangi rotanın ortalaması yükseldiyse (çoğunlukla gönderim rotası) önce onun sorgularına bakılıyor.

Sonra o sorgular tek tek EXPLAIN'den geçirilip SCAN ya da Seq Scan görenler listeleniyor. Küçük tablolarda tam tarama sorun değil; 120 satırlık ders tablosunu taramak indeks aramaktan bile ucuz olabilir. Asıl dikkat edilecek yer, büyüyen tablolardaki taramalar.

Bileşik indeks: sütun sırası neden önemli

Bu projede submissions için iki indeks yeterli oldu:

CREATE UNIQUE INDEX uq_submissions_exam_student ON submissions(exam_id, student_id);
CREATE INDEX idx_submissions_exam_score ON submissions(exam_id, score);
ANALYZE;

Bileşik indeksi bir telefon rehberi gibi düşünebilirsiniz: önce soyada, sonra ada göre sıralı. Soyadını biliyorsanız hızlı bulursunuz; yalnızca adı biliyorsanız bütün rehberi karıştırmanız gerekir. (exam_id, score) indeksi de önce sınava, sonra nota göre sıralı. Bu yüzden:

• WHERE exam_id = ? AND score > ? sorgusu indeksi tam kullanır. Sınavın bölgesine atlar, oradan belirli notun üstündekileri sayar.
• WHERE exam_id = ? sorgusu da aynı indeksin ilk sütununu kullanır. Toplam sayım için ayrı indeks gerekmedi.
• Sıralama ters olsaydı, yani (score, exam_id), "5 numaralı sınavda 20'nin üstü" sorgusu tüm sınavlardaki 20 üstü notları gezmek zorunda kalırdı.

Pratik kural şu: eşitlikle filtrelenen sütunlar önce, aralıkla (>, <, BETWEEN) ya da sıralamayla kullanılan sütun en sona. Aralık koşulundan sonra gelen sütunlar indeks aramasında pek işe yaramaz.

Bir ayrıntı daha: ilk sütunu atlayan sorgu genelde bileşik indeksi kullanamaz. Ama bu kesin bir yasak değil. SQLite, ANALYZE istatistiklerine bakıp ilk sütunda az farklı değer olduğunu görünce "skip-scan" ile indeksi yine de kullanabiliyor. Eğitim platformunda WHERE student_id = ? sorgusunun planında ANY(exam_id) ifadesi bunu gösteriyor. Buna güvenip tasarım yapmam; öğrenci bazlı sorgu sık çalışacaksa ona ayrı bir indeks eklerim.

Mükerrer kontrolünü benzersizlik kısıtına bırakın

v1'deki "önce SELECT ile var mı bak, yoksa INSERT et" deseni iki sorun taşıyor. Birincisi performans: kontrol sorgusu tam tarama yapıyordu. İkincisi doğruluk: birden fazla süreç aynı anda çalıştığında iki istek aynı anda "yok" cevabını alıp ikisi de kayıt ekleyebilir. v1 tek süreçte, senkron çalıştığı için bu yarışı pratikte yaşamıyordu. Ama v2 çekirdek başına bir işçiyle çalışacaktı, bu varsayım orada geçerliliğini yitiriyordu.

Çözüm, kontrolü uygulamadan alıp veritabanına vermek. (exam_id, student_id) üzerindeki UNIQUE indeks hem aramayı hızlandırıyor hem de aynı çiftin ikinci kez yazılmasını veritabanı seviyesinde engelliyor. Uygulama kodu artık sadece yazmayı deniyor:

try {
  const { rank, total } = submitTx(examId, studentId, score, answers);
  return { status: 201, body: { score, rank, total } };
} catch (err) {
  if (String(err.code).startsWith("SQLITE_CONSTRAINT")) {
    return { status: 409, body: { error: "Bu sınav zaten gönderilmiş" } };
  }
  throw err;
}

Bir sorgu eksildi, yarış durumu ortadan kalktı. PostgreSQL'de aynı fikir INSERT ... ON CONFLICT DO NOTHING ile, MySQL'de INSERT IGNORE ya da hata koduna bakarak uygulanabilir. Canlı tabloya UNIQUE indeks eklemeden önce mevcut mükerrer satırları temizlemeniz gerekiyor, yoksa indeks oluşturma hata verir.

Sonuçlar ve indeksin bedeli

Sınav saati provasında 60 sanal öğrenci aralıksız istek gönderdi. v1'de saniyede 16,3 başarılı istek ve 1.201 ms p95 yanıt süresi ölçülürken v2'de saniyede 4.073 istek ve 43 ms p95 ölçüldü. Hata oranı %39,6'dan sıfıra indi.

Dürüst olmak gerekirse bu farkın tamamı indekslerden gelmiyor. v2'de sınav cevap anahtarı bellekte önbellekleniyor, sorgular bir kez hazırlanıyor ve uygulama birden çok işçiyle çalışıyor. Ama indeksler olmadan bunların hiçbiri yetmezdi: her gönderimde 400 bin satır taranırken önbellek ya da işçi sayısı sorunu sadece erteler.

İndeksin bir de bedeli var, bunu hesaba katmak gerekiyor:

• Her INSERT ve UPDATE ilgili indeksleri de güncelliyor. Yazma ağırlıklı tablolarda gereksiz indeks yazmayı yavaşlatıyor.
• İndeksler diskte ve bellekte yer kaplıyor.
• Aynı sütunla başlayan birbirinin kopyası indeksler kolayca birikiyor. (exam_id) indeksi, (exam_id, score) varken çoğu zaman gereksizdir.

Canlı PostgreSQL veritabanında büyük tabloya indeks eklerken CREATE INDEX CONCURRENTLY kullanıyorum. Normal CREATE INDEX tabloya yazmayı indeks bitene kadar kilitler; eşzamanlı sürüm daha yavaş ama yazmaları durdurmaz.

Kontrol listesi

• Yavaş sorguları ölçün: pg_stat_statements, slow query log ya da rota bazlı metrikler.
• Her şüpheli sorguyu EXPLAIN'den geçirin, büyük tablolarda SCAN / Seq Scan arayan sorguları işaretleyin.
• Bileşik indekste eşitlik sütunları önce, aralık ya da sıralama sütunu sona.
• "Var mı bak, yoksa ekle" yerine UNIQUE kısıt ve çakışma hatasını yakalama.
• İndeksten sonra ANALYZE çalıştırın ve planı tekrar kontrol edin.
• Yeni indeksin yazma maliyetini ve mevcut indekslerle çakışıp çakışmadığını düşünün.
• Bir sonraki dağıtımda eklenen her sorgu için aynı kontrolü yapın. Eğitim platformunun nöbet kılavuzunda da bu adım var: gecikme uyarısı gelince ilk iş, son dağıtımdaki sorguları EXPLAIN QUERY PLAN ile taramak.