Denormalizasyon

Bölümleme stratejisi verinin nereye konacağını çözdü, hangi biçimde durduğunu sormadı. Dizin tablosunun taradığı 1.500.137 kayıt bir sorunun yanıtını üretmek için okunan kayıt sayısıydı ve o sayının hepsi gönderi tablosundan gelmiyor. Satıcının fatura satırı ağırlığı tarifeyle, tarifeyi bölge katsayısıyla ve sonucu sözleşme indirimiyle çarpar; üç çarpan üç ayrı tabloda durur. Okuma yolu tek bir bölümün içinde bile birden çok tabloya uzanır, bölümler ayrı depolara dağıtılmışsa bölüm sınırını da aşar.

Denormalizasyon (denormalization), normalleştirilmiş bir şemaya okuma başarımı için bilinçli olarak tekrar eklemektir. Tanım, normal biçimler ve türetilmiş sütunun tutarlılık kuralı Veri Modelleme ve İlişkisel Kuram kursundaki Denormalizasyon dersinde kuruldu; kaynağı değiştiren her yazma yolunun kopyayı doldurmak zorunda olduğu ve kaynaktan yeniden hesaplayan bir denetim işinin gerektiği orada gösterildi. Burada o kurallar yeniden anlatılmaz. Bu dersin sorusu ölçekleme sorusudur: kopyanın okumadan kaç kayıt düşürdüğü, gönderi kaydına kaç bayt eklediği ve kaynağı izlemek zorunda kaldığında K01’in yazma/okuma oranını kaça çıkardığı.

Dar Kopya ve Geniş Kopya

Kopyanın genişliği ayrı bir karardır. Dar kopya yalnız hesap için gereken sayıları taşır: tarife oranı, bölge katsayısı, sözleşme indirimi. Geniş kopya raporun gösterdiği adları da taşır: tarife adı ve bölge adı. İkisi de aynı okumayı hedefler, ama sakladıkları ve izlemek zorunda kaldıkları farklıdır.

Ölçüm gerçek bir motorda yapılır. Model ölçeği 01. dersin ölçeğidir — 20 satıcı, 30 gün, günde 100 gönderi — böylece sayılar o dersin sayılarıyla karşılaştırılabilir. Ölçülen üç şey vardır: sorgunun ikincil tablolardan çektiği kayıt sayısı, gönderi tablosunun kapladığı bayt ve bir kaynak alanı değiştiğinde yeniden yazılan satır sayısı.

// sema/kopya.mjs — ayni fatura sorgusunun uc semada cektigi kayit, kapladigi bayt ve
// bir kaynak degistiginde yeniden yazilan satir sayisi. node:sqlite ile gercek bir motor
// kosar; SQL ogretilmez, olculen sey kayit sayisi ile bayttir.
import { DatabaseSync } from "node:sqlite";

const SATICI = 20, GUN = 30, GUNLUK = 100, TARIFE = 40, BOLGE = 12;   // 01. dersin model olcegi
const tarifeAd = (i) => `TARIFE-${String(i).padStart(3, "0")} ekspres yurt ici`;
const bolgeAd = (i) => `BOLGE-${String(i).padStart(2, "0")} bati anadolu`;
const EK = { normal: "", dar: ", oran REAL, katsayi REAL, indirim REAL",
  genis: ", oran REAL, katsayi REAL, indirim REAL, tarife_ad TEXT, bolge_ad TEXT" };

function kur(sema) {
  const db = new DatabaseSync(":memory:");
  db.exec(`CREATE TABLE tarife(id INTEGER PRIMARY KEY, ad TEXT, oran REAL);
CREATE TABLE bolge(id INTEGER PRIMARY KEY, ad TEXT, katsayi REAL);
CREATE TABLE sozlesme(satici INTEGER PRIMARY KEY, indirim REAL);
CREATE TABLE gonderi(no TEXT PRIMARY KEY, satici INTEGER, gun INTEGER,
  tarife_id INTEGER, bolge_id INTEGER, agirlik REAL${EK[sema]});
CREATE INDEX gonderi_satici ON gonderi(satici, gun);`);
  db.exec("BEGIN");
  for (let i = 0; i < TARIFE; i += 1)
    db.prepare("INSERT INTO tarife VALUES(?,?,?)").run(i, tarifeAd(i), 12 + (i % 7) * 0.5);
  for (let i = 0; i < BOLGE; i += 1)
    db.prepare("INSERT INTO bolge VALUES(?,?,?)").run(i, bolgeAd(i), 1 + (i % 5) * 0.1);
  for (let i = 0; i < SATICI; i += 1)
    db.prepare("INSERT INTO sozlesme VALUES(?,?)").run(i, (i % 9) * 0.01);
  const alan = { normal: 6, dar: 9, genis: 11 }[sema];
  const g = db.prepare(`INSERT INTO gonderi VALUES(${"?,".repeat(alan - 1)}?)`);
  for (let s = 0; s < SATICI; s += 1) for (let d = 0; d < GUN; d += 1)
    for (let k = 0; k < GUNLUK; k += 1) {
      const ti = (s * 7 + d + k) % TARIFE, bi = (s + k) % BOLGE;
      const t = [`TR-${s}-${d}-${k}`, s, d, ti, bi, 1 + (k % 30)];
      const n = [12 + (ti % 7) * 0.5, 1 + (bi % 5) * 0.1, (s % 9) * 0.01];
      g.run(...(sema === "normal" ? t : sema === "dar" ? [...t, ...n]
        : [...t, ...n, tarifeAd(ti), bolgeAd(bi)]));
    }
  db.exec("COMMIT");
  return db;
}

const SORGU = {                       // saticinin 30 gunluk fatura satiri, tarife kirilimiyla
  normal: `SELECT t.ad, sum(g.agirlik * say(t.oran) * say(b.katsayi) * (1 - say(s.indirim)))
    FROM gonderi g JOIN tarife t ON t.id = g.tarife_id JOIN bolge b ON b.id = g.bolge_id
    JOIN sozlesme s ON s.satici = g.satici
    WHERE g.satici = ? AND g.gun BETWEEN 0 AND 29 GROUP BY t.ad`,
  dar: `SELECT say(t.ad), x.tutar FROM (SELECT tarife_id,
      sum(agirlik * oran * katsayi * (1 - indirim)) AS tutar FROM gonderi
      WHERE satici = ? AND gun BETWEEN 0 AND 29 GROUP BY tarife_id) x
    JOIN tarife t ON t.id = x.tarife_id`,
  genis: `SELECT tarife_ad, sum(agirlik * oran * katsayi * (1 - indirim)) FROM gonderi
    WHERE satici = ? AND gun BETWEEN 0 AND 29 GROUP BY tarife_ad`,
};

console.log(`model: ${SATICI * GUN * GUNLUK} gonderi, ${SATICI} satici, ${GUN} gun, ` +
  `${TARIFE} tarife, ${BOLGE} bolge; fatura satiri tarife kirilimlidir`);
const olcu = {};
let taban = 0;
for (const sema of ["normal", "dar", "genis"]) {
  const db = kur(sema);
  let sayac = 0;
  db.function("say", (x) => { sayac += 1; return x; });
  db.prepare(SORGU[sema]).all(10);
  const plan = db.prepare(`EXPLAIN QUERY PLAN ${SORGU[sema]}`).all()
    .flatMap((r) => r.detail.match(/(?:SCAN|SEARCH) \w+/g) ?? []);
  const bayt = db.prepare("SELECT sum(pgsize) AS b FROM dbstat WHERE name LIKE 'gonderi%'").get().b;
  if (taban === 0) taban = bayt;
  const say = (s, ...a) => db.prepare(s).run(...a).changes;
  const gosterim = say("UPDATE tarife SET ad = 'TARIFE-003 yeni ad' WHERE id = 3") +
    (sema === "genis" ? say("UPDATE gonderi SET tarife_ad = 'TARIFE-003 yeni ad' WHERE tarife_id = 3") : 0);
  const hesap = say("UPDATE sozlesme SET indirim = 0.05 WHERE satici = 10") +
    (sema === "normal" ? 0 : say("UPDATE gonderi SET indirim = 0.05 WHERE satici = 10"));
  olcu[sema] = { tablo: new Set(plan).size, ikincil: sayac, bayt,
    ek: (bayt - taban) / (SATICI * GUN * GUNLUK), gosterim, hesap };
}
const yaz = (baslik, alanlar) => {
  console.log(`\n${baslik.padEnd(26)}${["normal", "dar", "genis"].map((s) => s.padStart(12)).join("")}`);
  for (const [ad, f, b] of alanlar)
    console.log(ad.padEnd(26) + ["normal", "dar", "genis"]
      .map((s) => (b ? b(olcu[s][f]) : String(olcu[s][f])).padStart(12)).join(""));
};
yaz("okuma yolu", [["dokunulan tablo", "tablo"], ["ikincil tablodan kayit", "ikincil"],
  ["gonderi tablosu bayt", "bayt", (x) => x.toLocaleString("tr-TR")],
  ["gonderi basina ek bayt", "ek", (x) => x.toFixed(2)]]);
yaz("yazma yolu (satir)", [["tarife adi degisimi", "gosterim"], ["sozlesme indirimi degisimi", "hesap"]]);
console.log(`\nana tablodan okunan kayit uc semada da ${GUN * GUNLUK}`);

model: 60000 gonderi, 20 satici, 30 gun, 40 tarife, 12 bolge; fatura satiri tarife kirilimlidir

okuma yolu                      normal         dar       genis
dokunulan tablo                      4           3           1
ikincil tablodan kayit            9000          40           0
gonderi tablosu bayt         2.437.120   3.641.344   6.709.248
gonderi basina ek bayt            0.00       20.07       71.20

yazma yolu (satir)              normal         dar       genis
tarife adi degisimi                  1           1        1498
sozlesme indirimi degisimi           1        3001        3001

ana tablodan okunan kayit uc semada da 3000

Bu sayılar ölçüm sınıfındadır: bu makinede koşan bir motordan gelirler, belirlenimlidirler, ama sayfa doluluğu ve plan seçimi motorun kendi kararıdır.

Dokunulan Tablo Yanlış Ölçü

İlk satır tek başına okunduğunda dar kopya işe yaramaz görünüyor: dokunulan tablo dörtten üçe iniyor, üç hâlâ birden büyük. İkinci satır bunu düzeltiyor. Normalleştirilmiş şemada motor ikincil tablolardan 9000 kayıt çekiyor — üç bin gönderinin her biri için tarife, bölge ve sözleşme satırı ayrı ayrı aranıyor. Dar kopyada aynı sayı 40: hesap tek tablodan bitiyor, tarife adları toplamadan sonra ve tarife kırılımı başına bir kez aranıyor. Yani dar kopya dokunulan tablo sayısını bir azalttı, çekilen kayıt sayısını 225 kat azalttı.

Sonuç bir okuma kuralıdır: bir okumanın maliyeti dokunduğu tablo sayısıyla değil, ikincil tablolardan çektiği kayıt sayısıyla ölçülür. Küçük ve seyrek değişen bir tabloya kırılım başına bir kez bakmak, satır başına bir kez bakmakla aynı şey değildir.

Bu ayrım bölünmüş bir katmanda niteliğini değiştirir. Bölümleme Stratejileri dersinde bölümler ayrı depolara dağıtıldı; normalleştirilmiş şemada o 9000 aramanın her biri, tarife ve bölge tabloları her bölüme kopyalanmadıysa bölüm sınırını aşan bir erişimdir. Dar kopya bu geçişleri sıfıra indirir ve geriye kalan 40 arama, bölüm başına bir kez kopyalanmış küçük bir tablodan karşılanabilir.

Geniş kopya çekilen kaydı 40’tan 0’a indiriyor. Kazanç 40 kayıttır; bedeli iki satır aşağıda duruyor.

Yazma Yolu Nerede Uzuyor

Alt tablo iki kaynak değişimini ayırıyor ve ikisi aynı şey değil.

Tarife adının değişmesi bir gösterim alanıdır. Normalleştirilmiş ve dar şemada tek satır güncellenir. Geniş kopyada 1498 satır güncellenir, çünkü ad altmış bin gönderinin ilgili kırılımına kopyalanmıştır. Geniş kopyanın 40 kayıtlık okuma kazancının bedeli budur.

Sözleşme indiriminin değişmesi bir hesap alanıdır ve iki kopyalı şemada da 3001 satır güncellenir. Fark burada şemadan değil sözleşmeden gelir: indirim geriye dönük mü uygulanacak. Ücretlendirmede geçmiş fatura dondurulur — kesilmiş bir fatura sonradan değişmez — bu yüzden kopya bir anlık görüntü olarak tanımlanabilir ve o tanımla yayılım sıfıra iner. Aynı kopya kaynağı izlemek zorundaysa yayılım kaydın saklandığı süre boyunca sürer. Kopyanın hangisi olduğu şemaya bakarak anlaşılmaz; yazılı bir karardır.

Hesaba Geri Dönüş

Ölçülen ek bayt ve yayılım oranları K01’in satırlarına çevrilir. Yayılımın kapsamı için bir varsayım gerekir ve bu varsayım K01’in tablosuna eklenmez.

D5 — günlük kaynak alanı değişimi: 1 tarife, 5 sözleşme. Gerekçe: tarife ve sözleşme alanları ticari mutabakata bağlıdır ve gün içinde seyrek değişir. Duyarlılığı on katıyla verilir.

// sema/bedel.mjs — olculen ek baytlarin ve yazma yayiliminin K01 hesabindaki satirlara etkisi
const V3 = 400_000, V4 = 7, V6 = 220, V7 = 900, V12 = 730;   // K01 varsayimlari
const ARTIS = 976, SAKLAMA = 712.48, ORAN = 2.33;            // K01 hesaplari
const GUN = 86_400;
const OKUMA = (V3 * 30 * 0.1) / GUN;                         // K01: onbellek arkasi okuma, ortalama
const YAZMA = (V3 * V4) / GUN;                               // K01: durum olayi yazma, ortalama
const EK = { normal: 0, dar: 20.07, genis: 71.20 };          // sema/kopya.mjs olcumu
const TARIFE_PAYI = 1497 / 60_000;                           // sema/kopya.mjs: bir tarifeye bagli oran
const SATICI_GUNLUK = V3 / 4000;                             // K01: gunluk fatura satiri 4000
const D5 = { tarife: 1, sozlesme: 5 };                       // bu dersin varsayimi

const artis = (ek) => (V3 * (V7 + ek) + V3 * V4 * V6) / 1e6;
console.log(`ortalama depo okuma ${OKUMA.toFixed(2)}/s, yazma ${YAZMA.toFixed(2)}/s, ` +
  `oran ${(YAZMA / OKUMA).toFixed(2)} (K01: ${ORAN})`);
console.log(`\n${"sema".padEnd(8)}${"kayit bayt".padStart(12)}${"gunluk MB".padStart(12)}` +
  `${"kat".padStart(7)}${"saklanan GB".padStart(13)}${"kat".padStart(7)}`);
for (const [ad, ek] of Object.entries(EK)) {
  const a = artis(ek), s = (a * V12) / 1000;
  console.log(ad.padEnd(8) + (V7 + ek).toFixed(2).padStart(12) + a.toFixed(2).padStart(12) +
    (a / ARTIS).toFixed(3).padStart(7) + s.toFixed(2).padStart(13) + (s / SAKLAMA).toFixed(3).padStart(7));
}

console.log(`\ngenis kopyada kaynak degisiminin yayilimi (D5: gunde ${D5.tarife} tarife, ` +
  `${D5.sozlesme} sozlesme alani)`);
console.log(`${"yayilim kapsami".padEnd(18)}${"tarife kayit".padStart(14)}${"sozlesme kayit".padStart(16)}` +
  `${"yeniden yazma/s".padStart(17)}${"yazma/okuma".padStart(13)}`);
for (const [ad, g] of [["gecmise, 730 gun", V12], ["doneme, 30 gun", 30], ["acik gonderi, 5 gun", 5]]) {
  const t = g * V3 * TARIFE_PAYI, s = g * SATICI_GUNLUK;
  const yeniden = (D5.tarife * t + D5.sozlesme * s) / GUN;
  console.log(ad.padEnd(18) + Math.round(t).toLocaleString("tr-TR").padStart(14) +
    Math.round(s).toLocaleString("tr-TR").padStart(16) + yeniden.toFixed(2).padStart(17) +
    ((YAZMA + yeniden) / OKUMA).toFixed(2).padStart(13));
}
const kapsam = 30, t = kapsam * V3 * TARIFE_PAYI, s = kapsam * SATICI_GUNLUK;
for (const kat of [1, 10]) {
  const y = kat * (D5.tarife * t + D5.sozlesme * s) / GUN;
  console.log(`D5 duyarliligi (30 gun, ${kat} kat): yeniden yazma ${y.toFixed(2)}/s, ` +
    `yazma/okuma ${((YAZMA + y) / OKUMA).toFixed(2)}`);
}

ortalama depo okuma 13.89/s, yazma 32.41/s, oran 2.33 (K01: 2.33)

sema      kayit bayt   gunluk MB    kat  saklanan GB    kat
normal        900.00      976.00  1.000       712.48  1.000
dar           920.07      984.03  1.008       718.34  1.008
genis         971.20     1004.48  1.029       733.27  1.029

genis kopyada kaynak degisiminin yayilimi (D5: gunde 1 tarife, 5 sozlesme alani)
yayilim kapsami     tarife kayit  sozlesme kayit  yeniden yazma/s  yazma/okuma
gecmise, 730 gun       7.285.400          73.000            88.55         8.71
doneme, 30 gun           299.400           3.000             3.64         2.60
acik gonderi, 5 gun        49.900             500             0.61         2.38
D5 duyarliligi (30 gun, 1 kat): yeniden yazma 3.64/s, yazma/okuma 2.60
D5 duyarliligi (30 gun, 10 kat): yeniden yazma 36.39/s, yazma/okuma 4.95

İlk satır bir denetimdir: ortalama hızlardan çıkan yazma/okuma oranı 2,33, yani K01’in tepe hızlardan çıkardığı oranın aynısı. İki hız da aynı tepe çarpanını taşıdığı için oran değişmiyor ve bu, sonraki satırların K01’le karşılaştırılabilir olduğunu gösteriyor.

Saklama bedeli küçük. Dar kopya gönderi kaydını 900 bayttan 920,07 bayta çıkarıyor: günlük artış 976 MB’den 984,03 MB’ye, saklanan veri 712,48 GB’den 718,34 GB’ye. Geniş kopyada bile oran 1,029. Denormalizasyonun pahalı olduğu yer disk değildir.

Yazma bedeli kapsama bağlı ve aralık çok geniş. Kopya geçmişe yayılıyorsa bir tarife değişimi 7.285.400 kaydı yeniden yazdırır; D5 ile günlük yeniden yazma hızı 88,55 kayıt/s olur ve depoda yazma/okuma orani 2,33’ten 8,71’e çıkar. Aynı kopya yalnız dönem kayıtlarını izliyorsa oran 2,60, yalnız açık gönderileri izliyorsa 2,38. Kopyanın genişliği okuma kazancını belirler, kapsamı yazma bedelini belirler ve ikisi ayrı kararlardır.

Duyarlılık satırları kapsamın sıklıktan daha belirleyici olduğunu söylüyor: D5 on katına çıktığında otuz günlük kapsamda oran 2,60’tan 4,95’e gidiyor, ama geçmişe yayılan kapsam tek katta bile 8,71’de. Sıklığı yanlış tahmin etmek kapsamı yanlış seçmekten daha ucuzdur.

Özet

• Denormalizasyon burada bir ölçekleme kararıdır: ölçüsü kopyanın okumadan düşürdüğü kayıt, kayda eklediği bayt ve kaynağı izlediği kapsamdır.
• Bir okumanın maliyeti dokunulan tablo sayısıyla ölçülmez: dar kopya tablo sayısını dörtten üçe indirirken ikincil tablolardan çekilen kaydı 9000’den 40’a indirdi.
• Bölünmüş bir katmanda o 9000 arama bölüm sınırını aşan erişimdir; dar kopya onları sıfırlar, kalan 40 arama bölüm başına kopyalanmış küçük bir tablodan karşılanır.
• Saklama bedeli küçüktür: gönderi kaydı 900 bayttan 920,07 (dar) ve 971,20 bayta (geniş) çıkıyor, saklanan veri 712,48 GB’den 718,34 ve 733,27 GB’ye.
• Asıl bedel yayılımın kapsamındadır: depoda yazma/okuma orani geçmişe yayılan kopyada 2,33’ten 8,71’e, dönem kapsamında 2,60’a, açık gönderi kapsamında 2,38’e çıkıyor.
• Geniş kopya bir gösterim alanı değiştiğinde 1498 satır yazdırıyor; anlık görüntü olarak tanımlanan kopyada aynı değişim tek satırda kalır.

Sonraki Adım

Kopya, okumanın çektiği kayıt sayısını düşürdü ama okumanın taradığı kayıt sayısını hiç değiştirmedi: 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ın kendisi her istekte yeniden yapılıyor, oysa aynı dönemin aynı toplamı gün içinde değişmiyor. Sonraki ders bu tekrarı ele alır: toplamın önceden hesaplanıp saklanması, sorgunun taradığı kaydın kaça indiği, saklanan sonucun tazelenmesinin kaça mal olduğu ve tazeleme aralığının kabul edilen gerilik penceresini nasıl belirlediği.