--- title: 'Dizin Bakımı' source: 'https://academia.sh/tr/kurslar/veritabani-yonetimi/dizin-bakimi' course: 'İlişkisel Veritabanı Yönetimi' language: tr updated: '2026-08-17T18:08:56+00:00' license: 'CC BY-SA 4.0' --- # Dizin Bakımı Silme ve güncellemenin dizin sayfalarında bıraktığı boşluk, dizin sırasının bu boşluğun geri gelip gelmemesini belirlemesi, yeniden oluşturma ile tam yeniden yazmanın farkı ve yazma maliyetinin dizin sayısıyla artışı. 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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.