--- title: 'İstatistikler ve Kardinalite' source: 'https://academia.sh/tr/kurslar/ileri-sql/istatistikler-ve-kardinalite' course: 'İleri SQL' language: tr updated: '2026-08-17T18:08:55+00:00' license: 'CC BY-SA 4.0' --- # İstatistikler ve Kardinalite Planlayıcının satır sayısı tahmini, istatistik toplanmadan kullanılan varsayılan değerler, ANALYZE sonrası tahminin düzelmesi, tahminin plana ölçülen etkisi, eskiyen istatistiğin sonuçları ve ortalamanın çarpık dağılımı anlatamaması. Önceki derslerde planlayıcının kararları hep yerinde çıktı: doğru tabloyu sürdü, doğru dizini seçti, gereksiz sıralamadan kaçındı. Bu kararları neye dayanarak verdiği ise sorulmadı. Planlayıcı, sorgu çalışmadan önce karar verir. Elinde veri yoktur; olsaydı zaten sorgunun kendisini çalıştırmış olurdu. Onun yerine, her adımdan kaç satır döneceğini **tahmin eder** ve seçenekleri bu tahminler üzerinden karşılaştırır. Tahminin kaynağı, tablolar ve dizinler hakkında önceden toplanmış istatistiklerdir. Bu ders o tahmini görünür kılar, ölçer ve bozulduğunda ne olduğunu gösterir. ## Veri Kümesi ```sh rm -f kutuphane.db cat > kurulum.sql <<'SQL' CREATE TABLE sube ( sube_id INTEGER PRIMARY KEY, ad TEXT NOT NULL, sehir TEXT NOT NULL ); CREATE TABLE uye ( uye_id INTEGER PRIMARY KEY, ad TEXT NOT NULL, sehir TEXT NOT NULL, kayit_tarihi TEXT NOT NULL ); CREATE TABLE kitap ( kitap_id INTEGER PRIMARY KEY, baslik TEXT NOT NULL, yazar TEXT NOT NULL, yil INTEGER NOT NULL, sube_id INTEGER 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 sube (sube_id, ad, sehir) VALUES (1,'Merkez','Ankara'),(2,'Bahcelievler','Ankara'),(3,'Kadikoy','Istanbul'), (4,'Beyoglu','Istanbul'),(5,'Konak','Izmir'),(6,'Nilufer','Bursa'), (7,'Selcuklu','Konya'),(8,'Cankaya','Ankara'); 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 kitap (kitap_id, baslik, yazar, yil, sube_id) WITH RECURSIVE sayac(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM sayac WHERE n < 200000) SELECT n, 'Kitap ' || n, 'Yazar ' || (n % 4000), 1950 + (n % 75), 1 + (n % 8) 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 kutuphane.db < kurulum.sql ``` ## Tahmin Görünür Kılınabilir `.scanstats est` komutu, plan düğümlerinin yanına dört sayı ekler: düğümün kaç kez çalıştığı (`loops`), toplam kaç satır ürettiği (`rows`), çalışma başına kaç satır (`rpl`) ve planlayıcının bunun kaç olacağını **tahmin ettiği** (`est`). ```sh sqlite3 kutuphane.db <<'SQL' CREATE INDEX odunc_uye ON odunc(uye_id); CREATE INDEX kitap_yazar ON kitap(yazar); .scanstats est SELECT count(*) FROM odunc WHERE uye_id = 4242; SELECT count(*) FROM kitap WHERE yazar = 'Yazar 7'; SQL ``` ``` 17 QUERY PLAN `--SEARCH odunc USING COVERING INDEX odunc_uye (uye_id=?) (loops=1 rows=17 rpl=17.0 est=10.0) 50 QUERY PLAN `--SEARCH kitap USING COVERING INDEX kitap_yazar (yazar=?) (loops=1 rows=50 rpl=50.0 est=10.0) ``` Her iki düğümde de tahmin aynı: on. İki dizin farklı yoğunlukta, tablolar farklı büyüklükte, gerçekleşen sayılar on yedi ve elli. Tahminin ikisinde de on olması, bunun bir hesap değil bir **varsayılan değer** olduğunu gösterir: istatistik toplanmamış bir dizin için planlayıcı, eşitlik koşulunun sabit sayıda satır döndüreceğini varsayar. Bu varsayım, seçenekler birbirine yakınken zararsızdır. Seçeneklerden biri diğerinden yüz kat pahalıysa ve fark tam da bu sayıdan geliyorsa yanlış plan seçilir. ## İstatistik Toplamak `ANALYZE`, tabloları ve dizinleri tarayıp özet bilgileri bir sistem tablosuna yazar. ```sh sqlite3 kutuphane.db <<'SQL' ANALYZE; SELECT tbl, idx, stat FROM sqlite_stat1 ORDER BY tbl, idx; .scanstats est SELECT count(*) FROM odunc WHERE uye_id = 4242; SELECT count(*) FROM kitap WHERE yazar = 'Yazar 7'; SQL ``` ``` kitap|kitap_yazar|200000 50 odunc|odunc_uye|2000000 17 sube||8 uye||120000 17 QUERY PLAN `--SEARCH odunc USING COVERING INDEX odunc_uye (uye_id=?) (loops=1 rows=17 rpl=17.0 est=16.0) 50 QUERY PLAN `--SEARCH kitap USING COVERING INDEX kitap_yazar (yazar=?) (loops=1 rows=50 rpl=50.0 est=48.0) ``` İstatistik tablosundaki her satır iki sayı taşır: tablonun satır sayısı ve dizin anahtarı başına düşen ortalama satır sayısı. `odunc_uye` için `2000000 17` yazması, iki milyon ödünç kaydının bulunduğunu ve bir üyeye ortalama on yedi kayıt düştüğünü söyler. Bu sayıya **kardinalite tahmini (cardinality estimate)** denir. Tahminler on yerine on altı ve kırk sekize çıktı; gerçekleşen değerler on yedi ve elli. Planlayıcı artık iki dizini birbirinden ayırt edebiliyor. ## Tahminin Plana Etkisi Tahminin doğrulanması yalnız bir sayının düzelmesi değildir; plan da değişir. ```sh rm -f plan.db sqlite3 plan.db < kurulum.sql sqlite3 plan.db <<'SQL' CREATE INDEX kitap_sube ON kitap(sube_id); EXPLAIN QUERY PLAN SELECT count(*) FROM odunc o JOIN kitap k ON k.kitap_id = o.kitap_id WHERE k.sube_id = 3; .timer on .stats vmstep SELECT count(*) FROM odunc o JOIN kitap k ON k.kitap_id = o.kitap_id WHERE k.sube_id = 3; .timer off .stats off ANALYZE; EXPLAIN QUERY PLAN SELECT count(*) FROM odunc o JOIN kitap k ON k.kitap_id = o.kitap_id WHERE k.sube_id = 3; .timer on .stats vmstep SELECT count(*) FROM odunc o JOIN kitap k ON k.kitap_id = o.kitap_id WHERE k.sube_id = 3; SQL ``` ``` QUERY PLAN |--SCAN o `--SEARCH k USING COVERING INDEX kitap_sube (sube_id=? AND rowid=?) 250000 VM-steps: 10250011 Run Time: real 0.367 user 0.356097 sys 0.011025 QUERY PLAN |--SCAN o |--BLOOM FILTER ON k (sube_id=? AND rowid=?) `--SEARCH k USING INDEX kitap_sube (sube_id=? AND rowid=?) 250000 VM-steps: 13425016 Run Time: real 0.102 user 0.093311 sys 0.009379 ``` Sorgu, dizinler ve veri aynı; değişen tek şey istatistiğin varlığı. Plana yeni bir düğüm eklendi: iki milyon ödünç kaydının çoğunun aradığı şubeye ait olmadığı önceden ucuz bir süzgeçle eleniyor, ağaç inişi yalnız kalanlar için yapılıyor. Süre bu ortamda üç buçuk kat düştü. Adım sayısı ise arttı: 10.250.011'den 13.425.016'ya. İki ölçünün ayrıldığı yer burasıdır. Süzgecin kendisi de komut çalıştırır; kazandığı şey komut değil, ağaç inişi ve sayfa okumasıdır. Bu, adım sayısını tek ölçü saymanın sınırıdır — adım sayısı planın yaptığı işi sayar, o işin birim maliyetini değil. ## İstatistik Eskirse İstatistik toplandığı andaki veriyi anlatır. Veri değiştikçe anlattığı şey gerçeklikten uzaklaşır. ```sh rm -f eskiyen.db sqlite3 eskiyen.db < kurulum.sql sqlite3 eskiyen.db <<'SQL' CREATE INDEX odunc_uye ON odunc(uye_id); ANALYZE; 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 < 500000) SELECT 1 + ((n * 7) % 200000), 4242, date('2025-01-01', '+' || (n % 200) || ' days'), NULL FROM s; SELECT tbl, idx, stat FROM sqlite_stat1 WHERE idx = 'odunc_uye'; .scanstats est SELECT count(*) FROM odunc WHERE uye_id = 4242; .scanstats off ANALYZE; SELECT tbl, idx, stat FROM sqlite_stat1 WHERE idx = 'odunc_uye'; .scanstats est SELECT count(*) FROM odunc WHERE uye_id = 4242; SELECT count(*) FROM odunc WHERE uye_id = 5000; SQL ``` ``` odunc|odunc_uye|2000000 17 500017 QUERY PLAN `--SEARCH odunc USING COVERING INDEX odunc_uye (uye_id=?) (loops=1 rows=500017 rpl=500017.0 est=16.0) odunc|odunc_uye|2500000 21 500017 QUERY PLAN `--SEARCH odunc USING COVERING INDEX odunc_uye (uye_id=?) (loops=1 rows=500017 rpl=500017.0 est=20.0) 16 QUERY PLAN `--SEARCH odunc USING COVERING INDEX odunc_uye (uye_id=?) (loops=1 rows=16 rpl=16.0 est=20.0) ``` Kurmaca bir durum kurulmuş durumda: tek bir üyeye beş yüz bin kayıt eklendi. İstatistik eklemeden önce toplandığı için hâlâ `2000000 17` diyor ve tahmin on altıda kalıyor; gerçekleşen sayı 500.017. Tahmin otuz bin kattan fazla sapmış durumda. `ANALYZE` yeniden çalıştırıldığında istatistik `2500000 21` oluyor. Tahmin on altıdan yirmiye çıkıyor — hâlâ 500.017'nin yanına bile yaklaşmıyor. Bu bir hata değil, ölçünün tanımıdır: saklanan sayı bir **ortalamadır** ve iki buçuk milyon kayıt yüz yirmi bin üyeye bölündüğünde ortalama gerçekten yirmi bir çıkar. ## Ortalamanın Anlatamadığı Son çıktının son satırı ayrımı tamamlıyor: 5000 numaralı üye için gerçekleşen değer on altı, tahmin yirmi. Ortalama, sıradan üyeler için gayet iyi çalışıyor; yalnız aykırı üye için tamamen yanlış. Çarpık bir dağılımda tek bir ortalama iki gruba birden hizmet edemez. Bunun karşılığı, değer başına istatistik tutmaktır: en sık geçen değerlerin listesi ve değer aralıklarının dağılımı — yani bir **histogram (histogram)**. Bazı motorlar bu bilgiyi toplar ve `uye_id = 4242` ile `uye_id = 5000` için ayrı tahminler üretir. Toplamayanlarda tek çare, çarpıklığı sorgu yazımıyla ya da kısmi dizinlerle yönetmektir. İkinci sınır, sütunlar arası bağımlılıktır. Planlayıcı `sehir = 'Bursa'` ve `kayit_tarihi >= '2020-01-01'` koşullarını çoğunlukla bağımsız kabul eder ve seçicilikleri çarpar. Koşullar gerçekte bağımlıysa sonuç ciddi biçimde şişer ya da eksilir. Uygulama tarafında çıkarılacak ders kısadır: istatistik toplama, veri hacmi büyük ölçüde değiştikten sonra tekrarlanmalıdır — toplu yükleme, arşivleme ve şema değişikliği sonrası. Bir sorgu açıklanamaz biçimde yavaşladığında bakılacak ilk yerlerden biri, tahmin ile gerçekleşen arasındaki farktır. ## Özet - Planlayıcı kararlarını, her adımdan kaç satır döneceğine dair tahminlere dayandırır; bu tahminler plan çıktısında gerçekleşen sayılarla yan yana görülebilir. - İstatistik toplanmamışken tahmin sabit bir varsayılan değerdir; bu veri kümesinde iki ayrı dizin için de on çıktı. - `ANALYZE`, tablo satır sayısını ve anahtar başına ortalama satır sayısını bir sistem tablosuna yazar; tahminler bunun ardından gerçekleşen değerlere yaklaştı. - İstatistiğin varlığı planı değiştirdi ve süreyi bu ortamda üç buçuk kat düşürdü; aynı sorguda adım sayısı arttığı için iki ölçü aynı yönü göstermedi. - Saklanan sayı bir ortalamadır; çarpık dağılımlarda aykırı değerler için tahmin binlerce kat sapabilir, bu yüzden bazı motorlar değer başına histogram tutar. ## Sonraki Adım Bu konudaki bütün sorgular sabit metinlerdi. Uygulamalar sorguları çoğu zaman çalışma zamanında kurar: arama ekranındaki alanlara göre koşullar eklenir, sıralama sütunu kullanıcıdan gelir. Sorgu metnini dizgi birleştirmeyle kurmak hem doğruluk hem güvenlik hem de başarım tarafında sonuçlar doğurur. Son ders bu üç sonucu ve bağlı değişkenin hepsine birden verdiği yanıtı ele alır.