İstatistikler ve Kardinalite

Ö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

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).

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.

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.

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.

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.