Sorgu Planı Okuma

Önceki ders plan çıktısına iki kez baktı ve iki kelime ayırt etti: SCAN ve SEARCH. Tek tablolu, tek koşullu bir sorguda plan tek satırdır ve okunması kolaydır.

Gerçek sorgular böyle değildir. Üç tablo birleştiren, süzen, gruplayan ve sıralayan bir sorgunun planı çok düğümlü bir ağaçtır; bu ağaçtaki her düğüm bir karar taşır. Bu ders o ağacı okumayı ele alır: düğümlerin ne anlattığını, hangi tablonun diğerlerini sürdüğünü ve sıralamanın plana nasıl yansıdığını.

Veri Kümesi

Ölçümler önceki dersteki kütüphane veri kümesi üzerinde yapılır. Aşağıdaki blok bu kümeyi sıfırdan kurar; başka hiçbir dosya gerektirmez.

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

Plan Bir Ağaçtır

Belli bir yazarın kitaplarının kimler tarafından ödünç alındığını soran sorgu üç tabloya dokunur. Planı da üç satırdır.

sqlite3 kutuphane.db <<'SQL'
CREATE INDEX odunc_uye   ON odunc(uye_id);
CREATE INDEX odunc_kitap ON odunc(kitap_id);
CREATE INDEX kitap_yazar ON kitap(yazar);
EXPLAIN QUERY PLAN
SELECT u.ad, k.baslik, o.alis_tarihi
FROM odunc o
JOIN uye u   ON u.uye_id   = o.uye_id
JOIN kitap k ON k.kitap_id = o.kitap_id
WHERE k.yazar = 'Yazar 7';
SQL

QUERY PLAN
|--SEARCH k USING INDEX kitap_yazar (yazar=?)
|--SEARCH o USING INDEX odunc_kitap (kitap_id=?)
`--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)

Üç satır, üç tablo erişimidir ve sıraları anlamlıdır. En üstteki düğüm sürücü tablodur (driving table): veritabanı işe oradan başlar. Alttaki düğümler, sürücüden gelen her satır için bir kez çalışır. Burada iş kitap tablosundan başlıyor, çünkü tek süzme koşulu orada; yazar = 'Yazar 7' elli satır bırakıyor. Bu elli kitabın her biri için odunc içinde kitap_id araması yapılıyor, bulunan her ödünç kaydı için de uye tablosundan birincil anahtarla tek satır çekiliyor.

Sorgu metninde odunc önce yazılmıştı. Plan bunu dikkate almadı: FROM yan tümcesindeki sıra bir yürütme sırası değil, bir ad listesidir. Erişim sırasına planlayıcı karar verir.

Tarama ve Arama Düğümleri

Plan düğümlerinin ilk ayrımı, tablonun baştan sona mı okunduğu yoksa bir anahtardan mı girildiğidir.

SCAN, tablodaki ya da dizindeki her kaydın sırayla okunduğunu söyler. SEARCH, bir anahtar üzerinden girildiğini ve yalnız eşleşen bölümün okunduğunu söyler; parantez içindeki (uye_id=?) hangi koşulun anahtar olarak kullanıldığını gösterir. Üçüncü bir ayrım, aranan yapının kendisidir: USING INTEGER PRIMARY KEY (rowid=?) ayrı bir dizin değil, tablonun kendi birincil anahtarıdır.

Dördüncü kelime, tabloya hiç dokunulmadığını söyler.

sqlite3 kutuphane.db <<'SQL'
EXPLAIN QUERY PLAN SELECT count(*) FROM odunc WHERE uye_id = 4242;
EXPLAIN QUERY PLAN SELECT count(iade_tarihi) FROM odunc WHERE uye_id = 4242;
.stats vmstep
SELECT count(*) FROM odunc WHERE uye_id = 4242;
SELECT count(iade_tarihi) FROM odunc WHERE uye_id = 4242;
SQL

QUERY PLAN
`--SEARCH odunc USING COVERING INDEX odunc_uye (uye_id=?)
QUERY PLAN
`--SEARCH odunc USING INDEX odunc_uye (uye_id=?)
17
VM-steps: 62
17
VM-steps: 97

İki sorgu aynı dizini kullanıyor, ancak birincisinin planında COVERING sözcüğü var. Kapsayan dizin (covering index), sorgunun istediği bütün sütunları kendi içinde barındıran dizindir: uye_id dizinde zaten var, sayım için başka sütun gerekmiyor, bu yüzden tabloya hiç gidilmiyor. İkinci sorgu iade_tarihi sütununu istiyor; o sütun dizinde olmadığı için her eşleşen dizin girdisinden tabloya bir dönüş yapılıyor. On yedi satır için fark otuz beş adım; bu fark eşleşen satır sayısıyla doğru orantılı büyür.

Sıralama Düğümü

Bazı düğümler tabloya değil, veri üzerinde yapılan bir işe karşılık gelir. Sıralama bunların en pahalısıdır.

sqlite3 kutuphane.db <<'SQL'
EXPLAIN QUERY PLAN SELECT odunc_id, alis_tarihi FROM odunc ORDER BY alis_tarihi LIMIT 5;
.timer on
.stats vmstep
SELECT odunc_id, alis_tarihi FROM odunc ORDER BY alis_tarihi LIMIT 5;
.timer off
.stats off
CREATE INDEX odunc_alis ON odunc(alis_tarihi);
EXPLAIN QUERY PLAN SELECT odunc_id, alis_tarihi FROM odunc ORDER BY alis_tarihi LIMIT 5;
.timer on
.stats vmstep
SELECT odunc_id, alis_tarihi FROM odunc ORDER BY alis_tarihi LIMIT 5;
SQL

QUERY PLAN
|--SCAN odunc
`--USE TEMP B-TREE FOR ORDER BY
2437|2018-01-01
4874|2018-01-01
7311|2018-01-01
9748|2018-01-01
12185|2018-01-01
VM-steps: 12000130
Run Time: real 0.066 user 0.057176 sys 0.008530
QUERY PLAN
`--SCAN odunc USING COVERING INDEX odunc_alis
2437|2018-01-01
4874|2018-01-01
7311|2018-01-01
9748|2018-01-01
12185|2018-01-01
VM-steps: 32
Run Time: real 0.000 user 0.000014 sys 0.000014

USE TEMP B-TREE FOR ORDER BY düğümü, sıralamanın geçici bir yapı kurularak yapıldığını söyler. Beş satır isteniyor olması bunu ucuzlatmıyor: en küçük beş tarihi bulmak için iki milyon satırın hepsinin görülmesi gerekiyor, çünkü sıra bilinmiyor.

Dizin oluşturulduğunda sıralama düğümü plandan kayboluyor. Dizin zaten tarihe göre sıralıdır; veritabanı baştan beş girdi okuyup duruyor. On iki milyon adım otuz iki adıma iniyor — dizinin ikinci işlevi budur: aramayı ucuzlatmanın yanı sıra hazır bir sıra sunar. Süre ise yine ölçüm sınırının altına düşüyor; ortama bağlı olan süre değil, oranların yönü kararlıdır.

Bileşik Dizinde Sütun Sırası

Bileşik dizin (composite index), birden çok sütun üzerinde tanımlanan dizindir ve girdileri önce birinci sütuna, eşitlik durumunda ikinciye göre sıralar. Sıra bir ayrıntı değil, dizinin ne işe yarayacağını belirleyen karardır. Aşağıdaki ölçüm aynı sorguyu iki farklı sütun sırasıyla çalıştırır.

for sira in "alis_tarihi, uye_id" "uye_id, alis_tarihi"; do
  rm -f sira.db
  sqlite3 sira.db < kurulum.sql
  sqlite3 sira.db "CREATE INDEX odunc_bilesik ON odunc($sira);"
  printf 'dizin: (%s)\n' "$sira"
  sqlite3 sira.db <<'SQL'
EXPLAIN QUERY PLAN
SELECT odunc_id FROM odunc WHERE uye_id = 4242 ORDER BY alis_tarihi;
.stats vmstep
SELECT count(*) FROM
  (SELECT odunc_id FROM odunc WHERE uye_id = 4242 ORDER BY alis_tarihi);
SQL
done

dizin: (alis_tarihi, uye_id)
QUERY PLAN
`--SCAN odunc USING COVERING INDEX odunc_bilesik
17
VM-steps: 6000101
dizin: (uye_id, alis_tarihi)
QUERY PLAN
`--SEARCH odunc USING COVERING INDEX odunc_bilesik (uye_id=?)
17
VM-steps: 135

İki dizin aynı iki sütunu içeriyor, aynı yeri kaplıyor ve aynı sorgu çalışıyor. Fark kırk dört bin kat. Nedeni sıralama düzenidir: telefon rehberi soyada göre sıralıysa ada göre arama yapılamaz. (alis_tarihi, uye_id) dizininde uye_id değerleri tarih tarih dağılmıştır; bir üyeyi bulmak için dizinin tamamının okunması gerekir — plan bunu SCAN diyerek söyler. (uye_id, alis_tarihi) dizininde ise aynı üyenin bütün kayıtları yan yanadır ve tarih sırasındadır; plan SEARCH diyor ve sıralama düğümü hiç görünmüyor.

Kural şudur: eşitlikle süzülen sütunlar dizinin başında, aralık koşulu ve sıralama için kullanılan sütunlar sonrasında yer alır.

Okunacak Olan Biçim Değil Karar

Buradaki çıktılar tek bir motorun biçimidir. Başka motorlarda plan bir ağaç yerine girintili bir liste, bir tablo ya da düğüm başına tahmini satır sayısı ve maliyet taşıyan satırlar olarak gelir; düğüm adları da değişir — sıralı tarama, dizin taraması, karma birleştirme gibi adlar aynı kavramların başka isimleridir.

Değişmeyen şey, plandan okunan kararlardır: tabloya baştan sona mı bakıldı yoksa bir anahtardan mı girildi, hangi tablo diğerlerini sürdü, tabloya dönmek gerekti mi, ayrıca bir sıralama yapıldı mı. Bir plan çıktısını okumak, bu dört sorunun cevabını çıkarmaktır.

Özet

• Plan bir ağaçtır; üstteki düğüm sürücü tablodur ve alttaki düğümler sürücüden gelen her satır için çalışır. FROM yan tümcesindeki sıra yürütme sırasını belirlemez.
• SCAN baştan sona okumayı, SEARCH bir anahtardan girmeyi gösterir; parantez içindeki koşul hangi sütunun anahtar olarak kullanıldığını söyler.
• COVERING sözcüğü sorgunun yalnız dizinden yanıtlandığını, tabloya hiç dönülmediğini bildirir.
• Sıralama düğümü ayrı bir maliyettir; uygun bir dizin sıralamayı plandan tamamen kaldırdı ve aynı sorgu 12.000.130 adımdan 32 adıma indi.
• Bileşik dizinde sütun sırası, dizinin hangi sorguya yarayacağını belirler; aynı iki sütunun ters sırası aynı sorguyu 135 adımdan 6.000.101 adıma çıkardı.

Sonraki Adım

Bu dersteki bütün ölçümlerde dizin vardı ve kullanıldı. Uygulamada sık karşılaşılan durum başkadır: dizin vardır, sorgu o sütunu süzer, plan yine de SCAN der. Sonraki ders bu durumu ele alır — sütunun üzerine uygulanan bir işlev, sol ucu açık bir kalıp eşleme ya da tip uyuşmazlığı dizini nasıl devre dışı bırakır ve aynı sonucu veren hangi yeniden yazım dizini geri getirir.