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  17948        5738       9313        34056 
dosya: 139493376
--- eski kayitlar silindi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  17948        5738       6525        34056 
dosya: 139493376
--- ucte birinin tarihi guncellendi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  17948        5738       8102        34056 
dosya: 139493376
--- REINDEX sonrasi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  17948        4022       6524        34056 
dosya: 139493376
--- VACUUM sonrasi ---
satir    tablo_sayfa  uye_dizin  alis_dizin  toplam
-------  -----------  ---------  ----------  ------
1400893  12565        4022       6524        24168 
dosya: 98992128

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.313 sayfadan 6.525 sayfaya indi — silinen orana yakın bir düşüş. Üye dizini ise 5.738 sayfada kaldı; tek bir sayfa bile geri gelmedi.

İ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.525’ten 8.102 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.738), çü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.738’den 4.022 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.102’den 6.524’e iniyor, yani silme sonrasındaki değerine (6.525) bir sayfa farkla.

Dikkat edilecek olan, aynı satırdaki toplam sayfa sayısıdır: 34.056, 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.056’dan 24.168 sayfaya, dosya 139.493.376 bayttan 98.992.128 bayta iniyor. Tablo da sıkışıyor (17.948 → 12.565), çü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

{/* mask: (?<=real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */}

dizin sayisi 0: Run Time: real 0.086 user 0.085793 sys 0.000638
dizin sayisi 1: Run Time: real 0.319 user 0.196078 sys 0.122217
dizin sayisi 2: Run Time: real 0.644 user 0.327066 sys 0.317115
dizin sayisi 3: Run Time: real 0.854 user 0.457828 sys 0.395828
dizin sayisi 4: Run Time: real 1.152 user 0.574279 sys 0.577455

Artış doğrusala yakın: her dizin bu koşumda ekleme süresine yaklaşık 0,27 saniye ekliyor ve dört dizinli kopya dizinsiz kopyanın on üç katından fazla 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

{/* mask: (?<=real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */}

g_disi.db  Run Time: real 0.159 user 0.100926 sys 0.057693
g_ici.db   Run Time: real 1.765 user 0.741978 sys 1.022017

Aynı satır sayısı, aynı dizin sayısı, on bir 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  5738

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.738 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.313’ten 6.525 sayfaya indi, aynı silmede üye dizini 5.738 sayfada kaldı.
• Yeniden oluşturma dizinleri onarır ve boşluğu veritabanının içinde bırakır (toplam 34.056 sayfa değişmedi); tam yeniden yazma dosyayı da küçültür (24.168 sayfa, 98.992.128 bayt).
• Ekleme maliyeti dizin sayısıyla orantılı artar; ölçümde her dizin yaklaşık 0,27 saniye ekledi ve dört dizinli kopya dizinsizin on üç katından fazla sürdü.
• Güncellemede yalnız güncellenen sütunu içeren dizinler bedel öder: aynı dizin sayısıyla fark on bir kat çıktı; sol ön ek denetimi ise 5.738 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 okunan sayfa sayısıyla 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.