Somutlaştırılmış Görünümler

Kopya, okumanın çektiği kayıt sayısını düşürdü ama taradığı kayıt sayısına dokunmadı. Satıcının fatura satırı hâlâ üç bin gönderiyi tek tek okuyup topluyor; K01 ölçeğinde bu, dört saatlik pencerede saniyede 833,33 kayıttır. Toplam her istekte yeniden yapılıyor, oysa kapanmış bir günün toplamı bir daha değişmiyor. Aynı aritmetik günde bir kez yapılıp saklanabilir.

Somutlaştırılmış görünüm (materialized view), bir sorgunun sonucunun hesaplanıp saklanmasıdır. SQL Temelleri kursundaki Görünümler dersinde kurulan görünüm veri tutmaz, sorguyu saklar ve her çağrıldığında yeniden çalışır; somutlaştırılmış görünüm sonucu tutar, bu yüzden okuması ucuz, doğruluğu ise zamana bağlıdır. Bu ders sorgu dili öğretmez; ölçtüğü şey sorgunun taradığı kayıt sayısı, tazelemenin maliyeti ve saklanan sonucun ne kadar geride kalabildiğidir.

Taneciğin Belirlediği Şey

Saklanacak sonuç bir seçim gerektirir: hangi düzeyde toplanacak. Tanecik kaba olduğunda satır sayısı düşer, taranan kayıt azalır, ama sonuç daha az soruya yanıt verir. İki tanecik ölçülür: satıcı-gün-tarife ve satıcı-gün. Kaynak, gönderi taneciğidir.

Model ölçeği yine 01. dersin ölçeğidir. Bir satıcının günde 100 gönderisi ve sekiz ayrı tarifesi vardır; sekiz sayısı model seçimidir, gerçek bir ölçüm değil.

// gorunum/tazele.mjs — ayni raporun kaynaktan ve iki ayri tanecikteki somutlastirilmis
// gorunumden okunmasi. node:sqlite ile gercek bir motor kosar; olculen sey taranan kayit,
// saklanan bayt ve tazelemenin tarayip yazdigi satir sayisidir.
import { DatabaseSync } from "node:sqlite";

const SATICI = 20, GUN = 30, GUNLUK = 100, TARIFE = 40, TARIFE_GUN = 8;   // 01. dersin olcegi
const db = new DatabaseSync(":memory:");
db.exec(`CREATE TABLE gonderi(no TEXT PRIMARY KEY, satici INTEGER, gun INTEGER,
  tarife_id INTEGER, tutar REAL);
CREATE INDEX gonderi_satici ON gonderi(satici, gun);
CREATE INDEX gonderi_gun ON gonderi(gun);
CREATE TABLE ozet_gun_tarife(satici INTEGER, gun INTEGER, tarife_id INTEGER,
  kayit INTEGER, tutar REAL, PRIMARY KEY(satici, gun, tarife_id));
CREATE TABLE ozet_gun(satici INTEGER, gun INTEGER, kayit INTEGER, tutar REAL,
  PRIMARY KEY(satici, gun));`);
db.exec("BEGIN");
const g = db.prepare("INSERT INTO gonderi VALUES(?,?,?,?,?)");
for (let s = 0; s < SATICI; s += 1) for (let d = 0; d < GUN; d += 1)
  for (let k = 0; k < GUNLUK; k += 1)
    g.run(`TR-${s}-${d}-${k}`, s, d, (s * 7 + d * 3 + (k % TARIFE_GUN)) % TARIFE, 12 + (k % 30) * 0.5);
db.exec("COMMIT");

let sayac = 0;
db.function("say", (x) => { sayac += 1; return x; });
const olc = (sql, ...a) => { sayac = 0; const r = db.prepare(sql).all(...a); return [sayac, r.length]; };

const TAZELE = {                        // artimli tazeleme: yalniz verilen gunun kayitlari
  ozet_gun_tarife: `INSERT OR REPLACE INTO ozet_gun_tarife
    SELECT satici, gun, tarife_id, count(*), sum(tutar) FROM gonderi
    WHERE gun = ? GROUP BY satici, gun, tarife_id`,
  ozet_gun: `INSERT OR REPLACE INTO ozet_gun
    SELECT satici, gun, count(*), sum(tutar) FROM gonderi WHERE gun = ? GROUP BY satici, gun`,
};
db.exec("BEGIN");
for (let d = 0; d < GUN; d += 1) for (const s of Object.values(TAZELE)) db.prepare(s).run(d);
db.exec("COMMIT");

const RAPOR = {
  gonderi: ["gonderi | tanecik: gonderi",
    `SELECT tarife_id, sum(say(tutar)) FROM gonderi
     WHERE satici = ? AND gun BETWEEN 0 AND 29 GROUP BY tarife_id`],
  ozet_gun_tarife: ["ozet | tanecik: satici-gun-tarife",
    `SELECT tarife_id, sum(say(tutar)) FROM ozet_gun_tarife
     WHERE satici = ? AND gun BETWEEN 0 AND 29 GROUP BY tarife_id`],
  ozet_gun: ["ozet | tanecik: satici-gun",
    `SELECT sum(say(tutar)) FROM ozet_gun WHERE satici = ? AND gun BETWEEN 0 AND 29`],
};
const bayt = (t) => db.prepare("SELECT sum(pgsize) AS b FROM dbstat WHERE name = ?").get(t).b;
const satirSayisi = (t) => db.prepare(`SELECT count(*) AS n FROM ${t}`).get().n;

console.log(`model: ${SATICI * GUN * GUNLUK} gonderi, ${SATICI} satici, ${GUN} gun, ` +
  `${TARIFE} tarife, satici-gun basina ${TARIFE_GUN} tarife`);
console.log(`\n${"okunan tablo".padEnd(34)}${"satir".padStart(8)}${"taranan".padStart(9)}` +
  `${"donen".padStart(7)}${"kat".padStart(8)}${"bayt".padStart(10)}${"bayt/satir".padStart(12)}`);
let taban = 0;
for (const [tablo, [ad, sql]] of Object.entries(RAPOR)) {
  const [taranan, donen] = olc(sql, 10);
  if (taban === 0) taban = taranan;
  const n = satirSayisi(tablo), b = bayt(tablo);
  console.log(ad.padEnd(34) + n.toLocaleString("tr-TR").padStart(8) + String(taranan).padStart(9) +
    String(donen).padStart(7) + (taban / taranan).toFixed(2).padStart(8) +
    b.toLocaleString("tr-TR").padStart(10) + (b / n).toFixed(1).padStart(12));
}

console.log(`\n${"tazeleme".padEnd(18)}${"artimli taranan".padStart(16)}${"artimli yazilan".padStart(16)}` +
  `${"tam taranan".padStart(13)}${"tam yazilan".padStart(13)}`);
for (const [tablo, sql] of Object.entries(TAZELE)) {
  const yazilan = db.prepare(sql).run(GUN - 1).changes;
  console.log(tablo.padEnd(18) + String(SATICI * GUNLUK).padStart(16) + String(yazilan).padStart(16) +
    String(SATICI * GUN * GUNLUK).padStart(13) + String(satirSayisi(tablo)).padStart(13));
}

model: 60000 gonderi, 20 satici, 30 gun, 40 tarife, satici-gun basina 8 tarife

okunan tablo                         satir  taranan  donen     kat      bayt  bayt/satir
gonderi | tanecik: gonderi          60.000     3000     40    1.00 1.761.280        29.4
ozet | tanecik: satici-gun-tarife    4.800      240     40   12.50    94.208        19.6
ozet | tanecik: satici-gun             600       30      1  100.00    16.384        27.3

tazeleme           artimli taranan artimli yazilan  tam taranan  tam yazilan
ozet_gun_tarife               2000             160        60000         4800
ozet_gun                      2000              20        60000          600

Bu sayılar ölçüm sınıfındadır; bayt sütunu sayfa doluluğuna bağlıdır ve satır sayısı küçükken satır başına düşen bayt olduğundan büyük görünür.

Üç satır bir gradyan veriyor. Rapor kaynaktan okunduğunda 3000 kayıt taranıyor; satıcı-gün-tarife taneciğinde 240, satıcı-gün taneciğinde 30. Kabalaştıkça taranan kayıt 12,5 ve 100 kat düşüyor.

Dördüncü sütun bedeli söylüyor. Satıcı-gün-tarife taneciği raporun kırılımını koruyor: kaynakla aynı 40 satır dönüyor. Satıcı-gün taneciğinde dönen satır 1’e iniyor — o özet tarife kırılımını taşımıyor, dolayısıyla “hangi tarifeden ne kadar” sorusu ona sorulamaz. Tanecik seçimi bir başarım ayarı değil, yanıtlanabilir soruların kümesini daraltan bir tasarım kararıdır.

Alt tablo tazelemenin iki biçimini ayırıyor. Artımlı tazeleme yalnız değişen günün kayıtlarını tarar: 2000 kayıt okur, satıcı-gün-tarife için 160, satıcı-gün için 20 satır yazar. Tam yeniden hesap her seferinde 60.000 kaydı tarar ve özetin tamamını yazar. Aradaki oran taranan tarafta otuz kat, yazılan tarafta otuz kat; ve bu oran saklama süresiyle birlikte büyür, çünkü artımlı tazelemenin taradığı kayıt gün sayısına bağlı değildir.

Hesaba Geri Dönüş

Ölçülen tanecik oranları ve satır başına baytlar K01’in satırlarına çevrilir. Ölçekleme doğrudandır: satıcı-gün taneciğinin dönem satır sayısı 4000 satıcı çarpı 30 gün, yani K01’in donem satici-gun olarak zaten hesapladığı 120.000 satırdır.

// gorunum/pencere.mjs — olculen tanecik oranlarinin K01 hesabindaki satirlara etkisi ve
// tazeleme araliginin bayatlik penceresi ile yazma yuku arasindaki degisimi. Hepsi aritmetiktir.
const V3 = 400_000, V10 = 4, V12 = 730, DONEM = 30;          // K01 varsayimlari
const SATICI = 4000, TARIFE_GUN = 8;                         // K01: gunluk fatura satiri; model olcumu
const TARAMA = 833.33, OKUNAN_GB = 10.80, SAKLANAN = 712.48; // K01 hesaplari
const GUN = 86_400, PENCERE = V10 * 3600;
const OKUMA = (V3 * DONEM * 0.1) / GUN, YAZMA = (V3 * 7) / GUN;   // K01 ortalamalari
const BAYT = { "satici-gun-tarife": 19.6, "satici-gun": 27.3 };   // gorunum/tazele.mjs olcumu

const donem = { gonderi: DONEM * V3, "satici-gun-tarife": SATICI * DONEM * TARIFE_GUN,
  "satici-gun": SATICI * DONEM };
console.log(`${"okunan tablo".padEnd(20)}${"donem satiri".padStart(14)}${"tarama kayit/s".padStart(16)}` +
  `${"kat".padStart(8)}${"okunan MB".padStart(12)}${"saklama GB".padStart(12)}${"kat".padStart(8)}`);
for (const [ad, n] of Object.entries(donem)) {
  const bayt = BAYT[ad] ?? (OKUNAN_GB * 1e9) / donem.gonderi;
  const saklama = ad === "gonderi" ? SAKLANAN : SAKLANAN + (n / DONEM) * V12 * bayt / 1e9;
  console.log(ad.padEnd(20) + n.toLocaleString("tr-TR").padStart(14) +
    (n / PENCERE).toFixed(2).padStart(16) + (donem.gonderi / n).toFixed(2).padStart(8) +
    ((n * bayt) / 1e6).toFixed(2).padStart(12) + saklama.toFixed(2).padStart(12) +
    (saklama / SAKLANAN).toFixed(4).padStart(8));
}
console.log(`K01 tabani: toplu is tarama ${TARAMA} kayit/s, okunan ${OKUNAN_GB} GB, ` +
  `saklanan ${SAKLANAN} GB`);

console.log(`\ntazeleme araligi ile bayatlik penceresi ve yazma yuku (artimli tazeleme)`);
console.log(`${"aralik".padEnd(10)}${"bayatlik".padStart(11)}` +
  `${"satici-gun yaz/s".padStart(18)}${"oran".padStart(7)}` +
  `${"satici-gun-tarife yaz/s".padStart(25)}${"oran".padStart(7)}`);
for (const [ad, T] of [["gunde bir", GUN], ["saatte bir", 3600], ["5 dakika", 300], ["1 dakika", 60]]) {
  const hucre = [SATICI, SATICI * TARIFE_GUN].map((satir) => {
    const y = satir / T;
    return [y.toFixed(2), ((YAZMA + y) / OKUMA).toFixed(2)];
  });
  const sure = T >= 3600 ? `${T / 3600} sa` : `${T / 60} dk`;
  console.log(ad.padEnd(10) + sure.padStart(11) + hucre[0][0].padStart(18) +
    hucre[0][1].padStart(7) + hucre[1][0].padStart(25) + hucre[1][1].padStart(7));
}
console.log(`K01 tabani: ortalama yazma ${YAZMA.toFixed(2)}/s, okuma ${OKUMA.toFixed(2)}/s, oran ` +
  `${(YAZMA / OKUMA).toFixed(2)}`);
for (const T of [GUN, 300]) {
  const tam = (GUN / T) * donem.gonderi;
  console.log(`  ${T === GUN ? "gunde bir" : "5 dakika"} tazelemede gunluk taranan kayit: ` +
    `artimli ${V3.toLocaleString("tr-TR")}, tam yeniden hesap ${tam.toLocaleString("tr-TR")}`);
}

okunan tablo          donem satiri  tarama kayit/s     kat   okunan MB  saklama GB     kat
gonderi                 12.000.000          833.33    1.00    10800.00      712.48  1.0000
satici-gun-tarife          960.000           66.67   12.50       18.82      712.94  1.0006
satici-gun                 120.000            8.33  100.00        3.28      712.56  1.0001
K01 tabani: toplu is tarama 833.33 kayit/s, okunan 10.8 GB, saklanan 712.48 GB

tazeleme araligi ile bayatlik penceresi ve yazma yuku (artimli tazeleme)
aralik       bayatlik  satici-gun yaz/s   oran  satici-gun-tarife yaz/s   oran
gunde bir       24 sa              0.05   2.34                     0.37   2.36
saatte bir       1 sa              1.11   2.41                     8.89   2.97
5 dakika         5 dk             13.33   3.29                   106.67  10.01
1 dakika         1 dk             66.67   7.13                   533.33  40.73
K01 tabani: ortalama yazma 32.41/s, okuma 13.89/s, oran 2.33
  gunde bir tazelemede gunluk taranan kayit: artimli 400.000, tam yeniden hesap 12.000.000
  5 dakika tazelemede gunluk taranan kayit: artimli 400.000, tam yeniden hesap 3.456.000.000

Üst tablo K01’in en büyük okuma kalemini dağıtıyor. Gün sonu işi kaynaktan okuduğunda saniyede 833,33 kayıt tarıyor ve 10.800 MB okuyor; satıcı-gün özetinden okuduğunda saniyede 8,33 kayıt tarıyor ve 3,28 MB okuyor. Bant genişliği tarafındaki karşılık K01’in en çarpıcı bulgusunu tersine çeviriyor: orada gün sonu işi tepe okuma çıkışının 3,75 katı bant genişliği istiyordu, burada o kalem üç binde bire iniyor.

Saklama sütunu bedeli neredeyse yok gösteriyor. Yedi yüz otuz gün boyunca tutulan satıcı-gün özeti saklanan veriyi 712,48 GB’den 712,56 GB’ye çıkarıyor, satıcı-gün-tarife özeti 712,94 GB’ye. İkisi de binde birin altında. Önceden hesaplanmış sonucun bedeli diskte değildir.

Bayatlık Penceresi

Alt tablo bedelin nerede olduğunu söylüyor. Saklanan sonuç, tazelenene kadar geride kalır ve bu gecikmeye bayatlık penceresi denir; artımlı tazelemede pencere, tazeleme aralığının kendisidir.

Aralık kısaldıkça pencere küçülüyor ve yazma yükü büyüyor. Satıcı-gün taneciğinde günde bir tazeleme depoda yazma/okuma oraninı 2,33’ten 2,34’e çıkarıyor — ölçülemeyecek kadar küçük bir bedel. Beş dakikada bir tazelemede oran 3,29, dakikada bir tazelemede 7,13. Daha ince tanecikte aynı eğri daha diktir: satıcı-gün-tarife özeti beş dakikada bir tazelendiğinde oran 10,01, dakikada bir tazelendiğinde 40,73. İnce tanecik okumayı az kazandırır, tazelemeyi çok pahalılaştırır.

Karar K01’in bir varsayımından çıkıyor. V10, gün sonu işinin penceresini dört saat olarak koyuyor: satıcı raporu sabah isteniyor. Yirmi dört saatlik bir bayatlık penceresi bu gereksinimi karşılar, çünkü rapor zaten kapanmış günleri kapsar. Ücretlendirme için doğru aralık günde birdir ve bedeli oranın 2,33’ten 2,34’e çıkmasıdır. Aynı özetin satıcı panosunda canlı gösterilmesi istenirse pencere dakikalara iner ve bedel oranın üç ila yedi katına çıkmasıdır; bu, aynı özetin iki ayrı kullanımıdır ve tek bir tazeleme aralığıyla ikisi birden karşılanmaz.

Son iki satır tazeleme biçiminin sınırını çiziyor. Artımlı tazeleme aralık ne olursa olsun günde 400.000 kayıt tarar — her gönderi bir kez. Tam yeniden hesap günde bir çalıştığında 12.000.000, beş dakikada bir çalıştığında 3.456.000.000 kayıt tarar. Tazeleme aralığını kısaltmak ancak tazeleme artımlıysa bir seçenektir.

Özet

• Somutlaştırılmış görünüm sorguyu değil sonucu saklar; saklanan sorgu her çağrıldığında yeniden çalışır, saklanan sonuç ise tazelenene kadar geride kalır.
• Tanecik yanıtlanabilir soruları belirler: satıcı-gün-tarife özeti taranan kaydı 3000’den 240’a indirip kırılımı korudu, satıcı-gün özeti 30’a indirdi ama tarife sorusunu yanıtlayamaz oldu.
• K01’e dönüş: gün sonu işinin taraması 833,33 kayıt/s ve 10.800 MB’den 8,33 kayıt/s ve 3,28 MB’ye iniyor, yani yüz kat.
• Saklama bedeli ihmal edilebilir: 730 günlük özet saklanan veriyi 712,48 GB’den 712,56 GB’ye çıkarıyor.
• Bedel tazelemededir: depoda yazma/okuma orani günde bir tazelemede 2,33’ten 2,34’e, beş dakikada bir tazelemede 3,29’a, dakikada bir tazelemede 7,13’e çıkıyor; ince tanecikte aynı aralıklar 2,36, 10,01 ve 40,73 veriyor.
• Aralığı kısaltmak yalnız artımlı tazelemede seçenektir: artımlı tazeleme günde 400.000 kayıt tararken tam yeniden hesap beş dakikalık aralıkta 3.456.000.000 kayıt tarar.

Sonraki Adım

Bu konunun bütün kararları tek bir varsayımı korudu: veri, satırları ve sütunları olan bir tabloda duruyor. Ölçülen üç örüntü aynı depo biçiminde karşılandı — takip numarasına göre tekil okuma, durum olayının eklenmesi ve dönemsel tarama. Oysa bu üç örüntünün istediği şey birbirinden çok farklıdır: biri tek anahtarla tek kayıt, biri sürekli ekleme, biri geniş bir aralık üzerinde toplam. Sonraki ders aynı üç örüntüyü dört ayrı depo türünde ölçer: anahtar–değer, belge, geniş sütun ve çizge deposunda her örüntünün dokunduğu kayıt sayısı ile gereken sorgu sayısı ne oluyor ve bir örüntüyü ucuzlatan yapı hangi örüntüyü pahalılaştırıyor.