Dizin Bakımı

Buraya kadarki üç ders dizini bir kazanç olarak ele aldı; bedelini yalnız kurulduğu andaki yerle ölçtü. Kurulduğu andaki yer, bir dizinin ömrü boyunca kapladığı yerin en küçük değeridir.

Ölü Satır Temizliği dersinde tablo için ölçülen şişme dizinlerde de olur ve iki nedenle daha inatçıdır. Bir satır silindiğinde o satırın her dizindeki girdisi de ölür. Bir sütun güncellendiğinde ise o sütunun dizinindeki girdi yerinde kalmaz — eski konumdan silinip yeni konuma yazılır, yani tek bir güncelleme dizinde iki iş yapar. Bu ders o iki işin bıraktığı izi ölçer ve bakımın hangi aracının neyi geri kazandırdığını ayırt eder.

Şişmenin Ölçülmesi

Aşağıdaki koşum kütüphane ödünç kayıtları üzerinde beş aşamayı aynı ölçülerle izler: satır sayısı, tablonun sayfa sayısı, iki dizinin ayrı ayrı sayfa sayısı, veritabanının toplam sayfa sayısı ve dosya boyutu.

rm -f bakim.db
cat > kurulum.sql <<'SQL'
CREATE TABLE uye (
  uye_id       INTEGER PRIMARY KEY,
  ad           TEXT NOT NULL,
  sehir        TEXT NOT NULL,
  kayit_tarihi 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
);
INSERT INTO uye (uye_id, ad, sehir, kayit_tarihi)
WITH RECURSIVE sayac(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM sayac WHERE n < 120000)
SELECT n, 'Uye ' || n,
       CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir'
                  WHEN 3 THEN 'Bursa' ELSE 'Konya' END,
       date('2015-01-01', '+' || (n % 3200) || ' days')
FROM sayac;
INSERT INTO odunc (odunc_id, kitap_id, uye_id, alis_tarihi, iade_tarihi)
WITH RECURSIVE sayac(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM sayac WHERE n < 2000000)
SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000),
       date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'),
       CASE WHEN n % 9 = 0 THEN NULL
            ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END
FROM sayac;
SQL
sqlite3 bakim.db < kurulum.sql

cat > olcu.sql <<'EOF'
SELECT (SELECT count(*) FROM odunc) AS satir,
       (SELECT count(*) FROM dbstat WHERE name='odunc') AS tablo_sayfa,
       (SELECT count(*) FROM dbstat WHERE name='odunc_uye') AS uye_dizin,
       (SELECT count(*) FROM dbstat WHERE name='odunc_alis') AS alis_dizin,
       (SELECT page_count FROM pragma_page_count) AS toplam;
EOF
sqlite3 bakim.db <<'SQL'
.mode column
.headers on
CREATE INDEX odunc_uye  ON odunc(uye_id);
CREATE INDEX odunc_alis ON odunc(alis_tarihi);
.print '--- kurulus ---'
.read olcu.sql
.shell echo "dosya: $(wc -c < bakim.db)"
DELETE FROM odunc WHERE alis_tarihi < '2020-01-01';
.print '--- eski kayitlar silindi ---'
.read olcu.sql
.shell echo "dosya: $(wc -c < bakim.db)"
UPDATE odunc SET alis_tarihi = date(alis_tarihi, '+900 days') WHERE odunc_id % 3 = 0;
.print '--- ucte birinin tarihi guncellendi ---'
.read olcu.sql
.shell echo "dosya: $(wc -c < bakim.db)"
REINDEX;
.print '--- REINDEX sonrasi ---'
.read olcu.sql
.shell echo "dosya: $(wc -c < bakim.db)"
VACUUM;
.print '--- VACUUM sonrasi ---'
.read olcu.sql
.shell echo "dosya: $(wc -c < bakim.db)"
SQL

--- kurulus ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
2000000  18013        5754       9346        34178 
dosya:  139993088
--- eski kayitlar silindi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  18013        5755       6549        34178 
dosya:  139993088
--- ucte birinin tarihi guncellendi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  18013        5755       8157        34178 
dosya:  139993088
--- REINDEX sonrasi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  18013        4034       6547        34178 
dosya:  139993088
--- VACUUM sonrasi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  12617        4034       6547        24263 
dosya:  99381248

Beş satırlık bu tablo dersin bütün bulgularını taşıyor. Aşağıdaki bölümler onu üç soruya ayırarak okur: silme iki dizinde neden farklı davrandı, yeniden oluşturma ile tam yeniden yazma neyi geri kazandırdı ve toplam sayfa sayısı hangi aşamada değişti.

İki Dizin, İki Ayrı Davranış

İkinci aşama tek başına dersin ana bulgusudur. Satırların yaklaşık yüzde otuzu silindi. Tarih dizini 9.346 sayfadan 6.549 sayfaya indi — silinen orana yakın bir düşüş. Üye dizini ise 5.754 sayfadan 5.755 sayfaya çıktı.

İki dizin aynı satırları kaybetti; farkı yaratan, girdilerin nasıl sıralandığıdır. Silme koşulu bir tarih aralığıydı ve tarih dizininde o aralığın girdileri yan yanadır: bütün sayfalar boşaldı ve serbest listeye geçti. Üye dizininde ise aynı satırların girdileri yüz yirmi bin üye arasına dağılmıştır; hiçbir sayfa tamamen boşalmadı, hepsi kısmen boşaldı. Bir sayfanın içi boşalması onu geri vermez.

Kural, tablolar için Ölü Satır Temizliği dersinde kurulan kuralın dizin karşılığıdır: silme ölçütü dizin sırasıyla örtüşüyorsa yer geri gelir, örtüşmüyorsa gelmez. Aynı silme işlemi, bir dizinde tasarruf, diğerinde ölü boşluk üretir.

Üçüncü aşama güncellemenin dizindeki ikinci işini gösteriyor. Satırların üçte birinin tarihi ileri alındı; tablonun sayfa sayısı değişmedi, tarih dizini 6.549’dan 8.157 sayfaya çıktı. Silinen satır yok, eklenen satır yok — yalnız dizin girdileri eski konumlarından yeni konumlarına taşındı ve eski konumların bıraktığı boşluk kapanmadı. Üye dizini bu aşamada hiç değişmedi (5.755), çünkü güncellenen sütun onun anahtarı değil.

Yeniden Oluşturma ve Tam Yeniden Yazma

Dördüncü aşama REINDEX çalıştırıyor: dizinler baştan kuruluyor. Üye dizini 5.755’ten 4.034 sayfaya iniyor — canlı satır oranına (1.400.893 / 2.000.000 ≈ 0,70) tam olarak uyan bir düşüş, çünkü ölü boşluk kalmadı. Tarih dizini 8.157’den 6.547’ye iniyor, yani silme sonrasındaki değerine.

Dikkat edilecek olan, aynı satırdaki toplam sayfa sayısıdır: 34.178, hiç değişmedi. Dosya boyutu da değişmedi. Yeniden oluşturma boşluğu veritabanının içinde serbest bıraktı; işletim sistemine geri vermedi. Bu boşluk yeni veri için kullanılabilir, ama disk doluluğunu düşürmez ve yedek alma süresini kısaltmaz.

Beşinci aşama tam yeniden yazma çalıştırıyor. Toplam 34.178’den 24.263 sayfaya, dosya 139.993.088 bayttan 99.381.248 bayta iniyor. Tablo da sıkışıyor (18.013 → 12.617), çünkü tam yeniden yazma tabloyu ve dizinleri birlikte yeni bir dosyaya kopyalar.

İki aracın ayrımı bir cümlede toplanır: yeniden oluşturma yalnız dizinleri onarır ve boşluğu veritabanının içinde bırakır; tam yeniden yazma her şeyi onarır ve boşluğu dosya sisteminde geri verir. Bedelleri de bu ayrımı izler. Yeniden oluşturma yalnız ilgili dizini kilitler ve tablo boyutundan bağımsız olarak dizinin büyüklüğüyle orantılıdır. Tam yeniden yazma tabloyu kilitler, tablonun boyutu kadar geçici alan ister ve bütün veriyi okur. Çalışan bir sistemde ilki bir bakım penceresine sığar, ikincisi çoğu zaman planlanmış bir kesinti gerektirir.

Motorların bir kısmı yeniden oluşturmayı tabloyu yazmaya kapatmadan yapan bir yol sunar: yeni dizin arka planda kurulur, hazır olduğunda eskisiyle değiştirilir. Bu yolun adı ve sınırları motora özgüdür; kavram, dizinin çift kopya olarak kurulup değiştirilmesidir ve bedeli, geçici olarak iki dizinin birden yer kaplamasıdır.

Yazma Maliyeti Dizin Sayısıyla Artar

Bakımın ikinci yüzü, dizinin var olmasının her yazmaya eklediği maliyettir. Aşağıdaki koşum aynı iki yüz bin satırı, dizin sayısı sıfırdan dörde çıkan beş kopyaya ekler.

rm -f sablon.db y0.db y1.db y2.db y3.db y4.db
sqlite3 sablon.db < kurulum.sql
for k in 0 1 2 3 4; do cp sablon.db "y$k.db"; done
sqlite3 y1.db 'CREATE INDEX i1 ON odunc(uye_id);'
sqlite3 y2.db 'CREATE INDEX i1 ON odunc(uye_id); CREATE INDEX i2 ON odunc(alis_tarihi);'
sqlite3 y3.db 'CREATE INDEX i1 ON odunc(uye_id); CREATE INDEX i2 ON odunc(alis_tarihi);
               CREATE INDEX i3 ON odunc(kitap_id);'
sqlite3 y4.db 'CREATE INDEX i1 ON odunc(uye_id); CREATE INDEX i2 ON odunc(alis_tarihi);
               CREATE INDEX i3 ON odunc(kitap_id); CREATE INDEX i4 ON odunc(iade_tarihi);'
for k in 0 1 2 3 4; do
  printf 'dizin sayisi %s: ' "$k"
  sqlite3 "y$k.db" <<'SQL' | grep 'Run Time'
.timer on
INSERT INTO odunc (kitap_id, uye_id, alis_tarihi, iade_tarihi)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 200000)
SELECT 1 + ((n*7) % 200000), 1 + ((n*13) % 120000),
       date('2024-01-01','+'||(n%300)||' days'),
       CASE WHEN n % 9 = 0 THEN NULL ELSE date('2024-01-20','+'||(n%300)||' days') END
FROM s;
SQL
done

dizin sayisi 0: Run Time: real 0.082 user 0.075646 sys 0.003733
dizin sayisi 1: Run Time: real 0.204 user 0.144997 sys 0.047771
dizin sayisi 2: Run Time: real 0.289 user 0.224285 sys 0.055183
dizin sayisi 3: Run Time: real 0.428 user 0.309820 sys 0.100801
dizin sayisi 4: Run Time: real 0.524 user 0.394852 sys 0.109334

Artış doğrusala yakın: her dizin bu ortamda ekleme süresine yaklaşık 0,11 saniye ekliyor ve dört dizinli kopya dizinsiz kopyanın altı katı sürüyor. Süreler ortama bağlıdır; kararlı olan, maliyetin dizin sayısıyla orantılı artmasıdır. Nedeni doğrudandır: her yeni satır için her dizin ağacına ayrı bir anahtar yazılır.

Güncellemede hesap farklıdır — yalnız güncellenen sütunu içeren dizinler bedel öder. Aşağıdaki koşum ikisi de iki dizinli iki kopyada aynı güncellemeyi çalıştırır; tek fark, güncellenen sütunun bir kopyada dizinli olmasıdır.

rm -f g_disi.db g_ici.db
cp sablon.db g_disi.db
cp sablon.db g_ici.db
sqlite3 g_disi.db 'CREATE INDEX i1 ON odunc(uye_id); CREATE INDEX i2 ON odunc(kitap_id);'
sqlite3 g_ici.db  'CREATE INDEX i1 ON odunc(uye_id); CREATE INDEX i2 ON odunc(alis_tarihi);'
for db in g_disi.db g_ici.db; do
  printf '%-10s ' "$db"
  sqlite3 "$db" <<'SQL' | grep 'Run Time'
.timer on
UPDATE odunc SET alis_tarihi = date(alis_tarihi, '+1 day') WHERE odunc_id % 5 = 0;
SQL
done

g_disi.db  Run Time: real 0.241 user 0.090275 sys 0.129614
g_ici.db   Run Time: real 0.629 user 0.386084 sys 0.216269

Aynı satır sayısı, aynı dizin sayısı, 2,6 kat süre farkı. Ayrımın pratik karşılığı şudur: sık güncellenen bir sütunu dizinlemenin bedeli, hiç güncellenmeyen bir sütunu dizinlemenin bedeliyle aynı değildir. Dizin seçilirken sütunun yalnız nasıl sorgulandığına değil, ne sıklıkta değiştiğine de bakılır.

Ödenmiş Ama Karşılığı Alınmamış Dizinler

Bakımın son işi, var olmaması gereken dizinleri bulmaktır. Sistem Kataloğu dersinde kurulan kalıp burada ikinci kez kullanılır: sütun listesi başka bir dizinin sol ön eki olan dizin, sol ön ek kuralı gereği hiçbir sorguya tek başına gerekli değildir.

rm -f denetim.db
cp sablon.db denetim.db
sqlite3 denetim.db <<'SQL'
.mode column
.headers on
CREATE INDEX odunc_uye       ON odunc(uye_id);
CREATE INDEX odunc_uye_tarih ON odunc(uye_id, alis_tarihi);
CREATE INDEX odunc_tarih     ON odunc(alis_tarihi);
CREATE INDEX odunc_kitap     ON odunc(kitap_id);
-- Sutun listesi baska bir dizinin on eki olan dizinler: yeri bosuna kaplarlar.
WITH d AS (
  SELECT m.name AS tablo, il.name AS dizin,
         (SELECT group_concat(ii.name, ',') FROM pragma_index_info(il.name) ii) AS sutunlar
  FROM sqlite_schema m JOIN pragma_index_list(m.name) il
  WHERE m.type = 'table' AND il.origin = 'c'
)
SELECT a.dizin AS gereksiz, a.sutunlar AS sutunlari, b.dizin AS kapsayan,
       (SELECT count(*) FROM dbstat WHERE name = a.dizin) AS sayfa
FROM d a JOIN d b ON a.tablo = b.tablo AND a.dizin <> b.dizin
WHERE b.sutunlar LIKE a.sutunlar || ',%';
SQL

gereksiz   sutunlari  kapsayan         sayfa
---------  ---------  ---------------  -----
odunc_uye  uye_id     odunc_uye_tarih  5754

Denetim tek bulgu üretti ve bedelini de yazdı: uye_id üzerindeki tek sütunlu dizin, (uye_id, alis_tarihi) dizininin sol ön eki olduğu için gereksizdir ve 5.754 sayfa yer kaplamaktadır. Kaldırılması hem o yeri hem her yazmada ödenen payı geri kazandırır.

Bu denetimin bulamayacağı ikinci bir kategori vardır: sütun listesi eşsiz olduğu hâlde hiçbir sorgunun kullanmadığı dizinler. Onlar katalogdan değil, motorun tuttuğu kullanım sayaçlarından bulunur; sayaçların adı ve varlığı motora özgüdür. Ortak yöntem şudur: sayaç sıfırlanır, bir iş döngüsü boyunca beklenir, sonra hiç artmayan dizinler aday listesine alınır. Karar hemen verilmez — ayda bir çalışan bir rapor, bir haftalık gözlemde görünmez.

Özet

• Silme ve güncelleme dizinlerde boşluk bırakır; güncelleme dizinde iki iş yapar, eski konumdan siler ve yeni konuma yazar.
• Yerin geri gelip gelmeyeceğini dizin sırası belirler: tarih aralığıyla silmede tarih dizini 9.346’dan 6.549 sayfaya indi, aynı silmede üye dizini 5.754’ten 5.755’e çıktı.
• Yeniden oluşturma dizinleri onarır ve boşluğu veritabanının içinde bırakır (toplam 34.178 sayfa değişmedi); tam yeniden yazma dosyayı da küçültür (24.263 sayfa, 99.381.248 bayt).
• Ekleme maliyeti dizin sayısıyla orantılı artar; ölçümde her dizin yaklaşık 0,11 saniye ekledi ve dört dizinli kopya dizinsizin altı katı sürdü.
• Güncellemede yalnız güncellenen sütunu içeren dizinler bedel öder: aynı dizin sayısıyla fark 2,6 kat çıktı; sol ön ek denetimi ise 5.754 sayfalık gereksiz bir dizini buldu.

Sonraki Adım

Bu derste iki kez aynı olguya çarpıldı: silme ölçütü dizin sırasıyla örtüştüğünde yer kendiliğinden geri geldi, örtüşmediğinde bakım gerekti. Aynı olgunun tablo tarafındaki karşılığı Ölü Satır Temizliği dersinde ölçülmüş ve orada bir soru askıda bırakılmıştı: eski verinin silinmesi yerine, eski verinin ayrı bir yerde durması sağlanabilir mi? Sonraki ders bu soruyu yanıtlar. Bir tablo aralığa, listeye ya da karmaya göre bölümlere ayrıldığında sorgunun yalnız ilgili bölüme dokunduğu planla gösterilecek, eski verinin silinmesi bir bölümün kaldırılmasına dönüşecek ve bölüm anahtarının dağılımı sayıyla ölçülecek.