Sorgu Planı Okuma

Buraya kadar ölçülen her şey uygulamanın kendi yaptığı işti: kaç sorgu çalıştı, kaç bayt taşındı, kaç deyim gönderildi. Bir sorgu bu ölçütlerin hepsini geçip yine de yavaş olabilir.

Tek bir sorgu, tek bir işlemde, yalnız gereken sütunlarla çalışıp saniyeler sürebilir; çünkü sorgunun nasıl yürütüleceğine veritabanı karar verir. İleri SQL kursunda bu karar sorgu planı olarak tanıtıldı ve doğrudan veritabanı kabuğundan okundu. Bu ders aynı kararı uygulamanın içinden okur ve yavaş sorguyu üretim koşullarında yakalayacak bir düzenek kurar.

Ölçüm Verisi

Tarama ile dizin arasındaki farkın ölçülebilmesi için bağıntının yeterince büyük olması gerekir.

// uret.mjs — 50000 odunc kaydi; tarama ile dizin farkinin olculebilmesi icin
import { DatabaseSync } from "node:sqlite";
import { rmSync } from "node:fs";
rmSync("kutuphane.db", { force: true });

const db = new DatabaseSync("kutuphane.db");
db.exec(`
CREATE TABLE uye (uye_id INTEGER PRIMARY KEY, ad TEXT NOT NULL);
CREATE TABLE kitap (kitap_id INTEGER PRIMARY KEY, baslik TEXT NOT NULL);
CREATE TABLE odunc (odunc_id INTEGER PRIMARY KEY, kitap_id INTEGER NOT NULL,
                    uye_id INTEGER NOT NULL, alis_tarihi TEXT NOT NULL, iade_tarihi TEXT);`);
db.exec("BEGIN");
const u = db.prepare("INSERT INTO uye VALUES (?,?)");
for (let i = 1; i <= 60; i++) u.run(i, `Uye${i}`);
const k = db.prepare("INSERT INTO kitap VALUES (?,?)");
for (let i = 1; i <= 200; i++) k.run(i, `Kitap ${i}`);
const o = db.prepare("INSERT INTO odunc VALUES (?,?,?,?,?)");
let tohum = 20250727;                       // belirlenimci sozde rastgele dizi
const sonraki = (n) => { tohum = (tohum * 48271) % 2147483647; return tohum % n; };
for (let i = 1; i <= 50000; i++) {
  const yil = 2022 + sonraki(4);
  const ay = String(sonraki(12) + 1).padStart(2, "0");
  const gun = String(sonraki(28) + 1).padStart(2, "0");
  o.run(i, sonraki(200) + 1, sonraki(60) + 1, `${yil}-${ay}-${gun}`,
        sonraki(5) === 0 ? null : `${yil}-12-31`);
}
db.exec("COMMIT");
db.exec("ANALYZE");
console.log("odunc:", db.prepare("SELECT count(*) AS n FROM odunc").get().n,
            " acik:", db.prepare("SELECT count(*) AS n FROM odunc WHERE iade_tarihi IS NULL").get().n);

node uret.mjs

odunc: 50000  acik: 10006

Veri sözde rastgele üretiliyor ama tohum sabit olduğu için her çalıştırmada aynı satırlar oluşuyor. ANALYZE çağrısı, İleri SQL kursunda tanıtılan istatistikleri toplar; eniyileyici seçiciliği bu istatistiklerden tahmin eder.

Planı Uygulamadan Okumak

Plan, veritabanı kabuğunda okunabildiği gibi uygulama bağlantısından da okunabilir. Aşağıdaki sarmalayıcı her sorguyu sürer ve eşiği aşan sorguyu planıyla birlikte kütüğe koyar.

// izleyen.mjs — her sorguyu sureler, esigi asani plani ile birlikte kutuge yazar
import { DatabaseSync } from "node:sqlite";

export function izleyenBaglanti(dosya, esikMs) {
  const db = new DatabaseSync(dosya);
  const kutuk = [];
  const imza = (sql) => sql.replace(/\s+/g, " ").trim();
  return {
    kutuk,
    sorgu(sql, ...d) {
      const t = performance.now();
      const satirlar = db.prepare(sql).all(...d);
      const sure = performance.now() - t;
      if (sure >= esikMs) {
        const plan = db.prepare(`EXPLAIN QUERY PLAN ${sql}`).all(...d).map((p) => p.detail);
        kutuk.push({ imza: imza(sql), sure, satir: satirlar.length, plan });
      }
      return satirlar;
    },
    calistir(sql) { db.exec(sql); },
    plan(sql, ...d) { return db.prepare(`EXPLAIN QUERY PLAN ${sql}`).all(...d).map((p) => p.detail); },
    sure(sql, ...d) {
      const t = performance.now();
      const n = db.prepare(sql).all(...d).length;
      return { sure: performance.now() - t, satir: n };
    },
  };
}

Tasarımda iki karar var. Plan yalnız eşiği aşan sorgu için alınır; her sorgu için alınsaydı ölçüm işin kendisinden pahalı olurdu. Ve plan, sorgunun çalıştığı bağlı değerlerle alınır; farklı değerler farklı plan üretebileceği için bu ayrıntı önemlidir.

Aynı Sorgu, İki Plan

// plan-oku.mjs — ayni sorgu dizinsiz ve dizinli, plan ve sure ile
import { izleyenBaglanti } from "./izleyen.mjs";
const db = izleyenBaglanti("kutuphane.db", 0);
const SORGU = "SELECT odunc_id, alis_tarihi FROM odunc WHERE uye_id = ? AND iade_tarihi IS NULL";

const yaz = (etiket) => {
  const { sure, satir } = db.sure(SORGU, 17);
  console.log(`${etiket}`);
  for (const p of db.plan(SORGU, 17)) console.log(`  plan: ${p}`);
  console.log(`  satir=${satir}  sure=${sure.toFixed(2)} ms`);
};

yaz("dizin yokken");
db.calistir("CREATE INDEX odunc_uye_acik ON odunc (uye_id) WHERE iade_tarihi IS NULL");
yaz("kismi dizin eklendikten sonra");
db.calistir("DROP INDEX odunc_uye_acik");

node plan-oku.mjs

dizin yokken
  plan: SCAN odunc
  satir=180  sure=1.27 ms
kismi dizin eklendikten sonra
  plan: SEARCH odunc USING INDEX odunc_uye_acik (uye_id=?)
  satir=180  sure=0.07 ms

Sonuç aynı: 180 satır. Plan farklı. Dizinsiz durumda SCAN yazıyor; elli bin satırın tamamı okunup koşul her satıra uygulanıyor. Dizinli durumda SEARCH yazıyor ve hangi dizinin, hangi koşulla kullanıldığı belirtiliyor.

Plan metinlerinin biçimi motora göre değişir; buradaki SCAN ve SEARCH sözcükleri SQLite’ın yazımıdır. Değişmeyen ayrım, planın bir bağıntının tamamını mı okuduğu yoksa aradığı satırlara doğrudan mı gittiğidir. İlişkisel Veritabanı Yönetimi kursunda tanıtılan kısmi dizin burada kullanıldı: dizin yalnız iade edilmemiş kayıtları kapsıyor, yani elli bin satırın onda ikisi kadar yer tutuyor.

Süredeki fark bu ölçekte yaklaşık on sekiz kat. Bağıntı büyüdükçe fark büyür: tarama satır sayısıyla doğrusal, dizin araması logaritmik artar.

Yazımın Planı Değiştirmesi

Dizin bulunması, kullanılacağı anlamına gelmez. Koşulun yazımı dizini devre dışı bırakabilir.

// yazim-farki.mjs — ayni kosulun iki yazimi, ayni dizin altinda
import { izleyenBaglanti } from "./izleyen.mjs";
const db = izleyenBaglanti("kutuphane.db", 0);
db.calistir("CREATE INDEX IF NOT EXISTS odunc_alis ON odunc (alis_tarihi)");

const yazimlar = {
  "islev uygulanmis": "SELECT count(*) AS n FROM odunc WHERE substr(alis_tarihi,1,4) = '2024'",
  "aralik kosulu   ": "SELECT count(*) AS n FROM odunc WHERE alis_tarihi >= '2024-01-01' AND alis_tarihi < '2025-01-01'",
};

for (const [ad, sql] of Object.entries(yazimlar)) {
  const sonuc = db.sorgu(sql)[0].n;
  const { sure } = db.sure(sql);
  console.log(`${ad}  sonuc=${sonuc}  sure=${sure.toFixed(2)} ms`);
  for (const p of db.plan(sql)) console.log(`  plan: ${p}`);
}
db.calistir("DROP INDEX odunc_alis");

node yazim-farki.mjs

islev uygulanmis  sonuc=12574  sure=1.25 ms
  plan: SCAN odunc USING COVERING INDEX odunc_alis
aralik kosulu     sonuc=12574  sure=0.14 ms
  plan: SEARCH odunc USING COVERING INDEX odunc_alis (alis_tarihi>? AND alis_tarihi<?)

İki sorgu aynı sayıyı döndürdü, aynı dizini kullandı, ama biri dizini tarayarak, öteki arayarak. Nedeni ilk yazımdaki işlev çağrısıdır: dizin alis_tarihi değerlerine göre sıralıdır, substr(alis_tarihi,1,4) değerlerine göre değil. Eniyileyici, dizinin sırasından yararlanamaz ve dizini baştan sona okur.

İkinci yazım aynı koşulu aralık olarak ifade ediyor. İleri SQL kursunda bu tür koşullara dizine uygun koşul dendi. Kural sadedir: dizinli sütun koşulun bir tarafında yalın durmalıdır. Sütuna uygulanan işlev, tür dönüşümü ya da hesaplama dizini devre dışı bırakır.

Her iki planda geçen COVERING INDEX ifadesi ikinci bir bilgidir: sorgunun ihtiyaç duyduğu bütün sütunlar dizinde bulunduğu için bağıntının kendisine hiç dokunulmamış. Bu, İlişkisel Veritabanı Yönetimi kursunda kapsayan dizin olarak geçen durumdur.

Yavaş Sorgu Kütüğü

Tek tek sorgu incelemek geliştirme sırasında işe yarar; üretimde hangi sorgunun yavaş olduğu önceden bilinmez. Bunun için eşiği aşan sorgular toplanır ve sorgu imzasına göre gruplanır. İmza, bağlı değerlerden arındırılmış sorgu metnidir; aynı sorgunun farklı değerlerle çalışan bin çağrısı tek bir satırda toplanır.

// yavas-kutuk.mjs — karisik yuk calistirilir, esigi asan sorgular imzaya gore toplanir
import { izleyenBaglanti } from "./izleyen.mjs";
const db = izleyenBaglanti("kutuphane.db", 0.5);

for (let i = 0; i < 20; i++) {
  db.sorgu("SELECT odunc_id FROM odunc WHERE odunc_id = ?", (i * 37) % 50000 + 1);
  db.sorgu("SELECT odunc_id FROM odunc WHERE uye_id = ? AND iade_tarihi IS NULL", (i % 60) + 1);
  db.sorgu("SELECT count(*) AS n FROM odunc WHERE substr(alis_tarihi,1,4) = ?", String(2022 + (i % 4)));
}

const gruplar = new Map();
for (const k of db.kutuk) {
  const g = gruplar.get(k.imza) ?? { sayi: 0, toplam: 0, enUzun: 0, plan: k.plan };
  g.sayi += 1; g.toplam += k.sure; g.enUzun = Math.max(g.enUzun, k.sure);
  gruplar.set(k.imza, g);
}

console.log(`esigi asan sorgu sayisi: ${db.kutuk.length} / 60`);
for (const [imza, g] of [...gruplar].sort((a, b) => b[1].toplam - a[1].toplam)) {
  console.log(`\ntoplam=${g.toplam.toFixed(1)} ms  cagri=${g.sayi}  en_uzun=${g.enUzun.toFixed(2)} ms`);
  console.log(`  ${imza}`);
  for (const p of g.plan) console.log(`  plan: ${p}`);
}

node yavas-kutuk.mjs

esigi asan sorgu sayisi: 40 / 60

toplam=33.0 ms  cagri=20  en_uzun=1.93 ms
  SELECT count(*) AS n FROM odunc WHERE substr(alis_tarihi,1,4) = ?
  plan: SCAN odunc

toplam=17.8 ms  cagri=20  en_uzun=1.22 ms
  SELECT odunc_id FROM odunc WHERE uye_id = ? AND iade_tarihi IS NULL
  plan: SCAN odunc

Altmış sorgunun kırkı eşiği aştı; birincil anahtarla arayan yirmi sorgu hiç görünmedi. Kütük iki imza üretti ve ikisini de toplam süreye göre sıraladı.

Sıralamanın toplam süreye göre yapılması bilinçlidir. En uzun süren tek çağrıya bakmak yanıltır; bir sorgu iki milisaniye sürüp saniyede bin kez çağrılıyorsa, on milisaniye sürüp dakikada bir çağrılandan daha çok yük üretir. İlişkisel Veritabanı Yönetimi kursunda bu ayrım belirti ile neden arasındaki fark olarak geçmişti.

Planın Söylemedikleri

Plan bir tahmindir, ölçüm değildir. Üç sınırı akılda tutulur.

Plan istatistiklere dayanır. İstatistikler eskimişse eniyileyici seçiciliği yanlış tahmin eder ve yanlış planı seçer; bağıntı büyüklüğü hızla değişen sistemlerde istatistiğin tazelenmesi bakım işidir.

Plan bağlı değere göre değişebilir. Yaygın bir değer için tarama, seyrek bir değer için dizin araması seçilebilir. Bu yüzden plan, sorgunun gerçekten çalıştığı değerlerle alınmalıdır.

Plan süreyi vermez. Yukarıdaki ölçümlerin hepsinde plan ile süre birlikte yazdırıldı; plan neden yavaş olduğunu, süre ne kadar yavaş olduğunu söyler. Bir tanı ancak ikisi birlikte okunduğunda tamamlanır.

Özet

• Sorgu planı uygulama bağlantısından okunabilir; eşiği aşan sorgu için plan almak, her sorgu için almaktan ucuzdur.
• Aynı sorgu dizinsiz SCAN, kısmi dizinle SEARCH planı üretti ve süre 1,27 ms’den 0,07 ms’ye indi.
• Aynı koşulun işlev uygulanmış yazımı dizini taradı, aralık yazımı aradı; dizinli sütun koşulun bir tarafında yalın durmalıdır.
• Yavaş sorgu kütüğü, bağlı değerlerden arındırılmış sorgu imzasına göre gruplanır ve toplam süreye göre sıralanır; en uzun tek çağrı değil, en çok yük üreten imza aranır.
• Plan istatistiklere ve bağlı değere bağlı bir tahmindir; süreyi vermez, tanı plan ile süre birlikte okunarak tamamlanır.

Sonraki Adım

Bu dersteki sorguların hepsi sonuç kümesinin tamamını istedi. Uygulamalar çoğu listeyi parça parça gösterir ve SQL Temelleri kursunda tanıtılan atlama–sınırlama çifti bunun en bilinen yoludur. Yazımı kısa, planı sade, ilk sayfalarda hızlıdır. Derin sayfalarda ise maliyeti atlanan satır sayısıyla birlikte büyür ve bu büyüme sorgu metnine bakarak görülmez. Sonraki ders aynı listeyi artan atlama değerleriyle çekerek maliyeti ölçer, anahtar tabanlı yaklaşımla karşılaştırır ve iki yaklaşımın hangi arayüz kısıtını getirdiğini ele alır.