Dizin Kullanımını Engelleyen Yazımlar

Önceki iki derste dizin vardı ve kullanıldı. Uygulamada sık karşılaşılan durum bunun tersidir: dizin vardır, sorgu tam da o sütunu süzer, plan yine SCAN der ve süre düşmez.

Bunun nedeni çoğunlukla dizinde ya da veride değil, koşulun yazımındadır. Dizin belli bir sütunun saklandığı biçimdeki değerlerini sıralı tutar. Koşul o değerin kendisini değil, ondan hesaplanmış bir başka değeri sorduğunda dizin işe yaramaz hale gelir. Bu ders bu yazımları ve aynı sonucu veren eşdeğerlerini ele alır.

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

Sütunun Üzerine Uygulanan İşlev

İki sütunda dizin var. Aynı iki soru, biri sütuna işlev uygulayarak, diğeri uygulamadan soruluyor.

sqlite3 kutuphane.db <<'SQL'
CREATE INDEX kitap_yazar ON kitap(yazar);
CREATE INDEX odunc_uye  ON odunc(uye_id);
EXPLAIN QUERY PLAN SELECT count(*) FROM kitap WHERE upper(yazar) = 'YAZAR 7';
EXPLAIN QUERY PLAN SELECT count(*) FROM kitap WHERE yazar = 'Yazar 7';
EXPLAIN QUERY PLAN SELECT count(*) FROM odunc WHERE uye_id + 0 = 4242;
EXPLAIN QUERY PLAN SELECT count(*) FROM odunc WHERE uye_id = 4242;
.timer on
.stats vmstep
SELECT count(*) FROM kitap WHERE upper(yazar) = 'YAZAR 7';
SELECT count(*) FROM kitap WHERE yazar = 'Yazar 7';
SELECT count(*) FROM odunc WHERE uye_id + 0 = 4242;
SELECT count(*) FROM odunc WHERE uye_id = 4242;
SQL

QUERY PLAN
`--SCAN kitap USING COVERING INDEX kitap_yazar
QUERY PLAN
`--SEARCH kitap USING COVERING INDEX kitap_yazar (yazar=?)
QUERY PLAN
`--SCAN odunc USING COVERING INDEX odunc_uye
QUERY PLAN
`--SEARCH odunc USING COVERING INDEX odunc_uye (uye_id=?)
50
VM-steps: 800061
Run Time: real 0.013 user 0.012658 sys 0.000530
50
VM-steps: 161
Run Time: real 0.000 user 0.000019 sys 0.000018
17
VM-steps: 8000029
Run Time: real 0.029 user 0.026334 sys 0.002302
17
VM-steps: 62
Run Time: real 0.000 user 0.000027 sys 0.000018

Dört planın ikisi SCAN, ikisi SEARCH. Sonuçlar aynı: elli ve on yedi. Adım sayıları sırasıyla 800.061’e karşı 161 ve 8.000.029’a karşı 62.

Nedeni tek cümleyle söylenebilir: dizin yazar değerlerini sıralı tutar, upper(yazar) değerlerini değil. Veritabanı upper(yazar) için sıralı bir yapıya sahip olmadığından işlevi her satır için ayrı ayrı hesaplamak ve sonucu karşılaştırmak zorunda kalır. Aynı şey aritmetik için de geçerlidir: uye_id + 0 bir dizin anahtarı değildir. + 0 hiçbir şey değiştirmiyormuş gibi görünse de dizinin devre dışı kalması için yeterlidir.

Aynı Hatanın Tarih Biçimi

Bu yazımın en sık görülen biçimi tarih süzmeleridir: bir yılın kayıtları istendiğinde tarihten yıl çıkarmak doğal görünür.

sqlite3 kutuphane.db <<'SQL'
CREATE INDEX odunc_alis ON odunc(alis_tarihi);
EXPLAIN QUERY PLAN SELECT count(*) FROM odunc WHERE strftime('%Y', alis_tarihi) = '2019';
EXPLAIN QUERY PLAN
SELECT count(*) FROM odunc
WHERE alis_tarihi >= '2019-01-01' AND alis_tarihi < '2020-01-01';
.timer on
.stats vmstep
SELECT count(*) FROM odunc WHERE strftime('%Y', alis_tarihi) = '2019';
SELECT count(*) FROM odunc
WHERE alis_tarihi >= '2019-01-01' AND alis_tarihi < '2020-01-01';
SQL

QUERY PLAN
`--SCAN odunc USING COVERING INDEX odunc_alis
QUERY PLAN
`--SEARCH odunc USING COVERING INDEX odunc_alis (alis_tarihi>? AND alis_tarihi<?)
299551
VM-steps: 8299563
Run Time: real 0.213 user 0.208346 sys 0.004492
299551
VM-steps: 898666
Run Time: real 0.005 user 0.004441 sys 0.000663

İki sorgu da 299.551 döndürüyor: yeniden yazım eşdeğerdir. Adım sayısı dokuz kat, süre bu ortamda kırk kattan fazla düştü.

Yeniden yazımın mantığı şudur: bir yılın kayıtları, sıralı bir tarih dizininde kesintisiz bir aralıktır. Aralığın alt ucu kapalı, üst ucu açık yazılır — < '2020-01-01' biçimi, <= '2019-12-31' yazımının aksine gün içindeki saat bilgisi taşıyan değerleri de doğru kapsar. Aynı dönüşüm ay, hafta ve gün süzmeleri için de kurulabilir; her durumda işlev koşulun sağ tarafına, sabit değerlerin içine taşınır.

Kalıp Eşlemede Sol Uç

Sıralı bir yapıda önekle arama yapılabilir, sonekle yapılamaz.

sqlite3 kutuphane.db <<'SQL'
CREATE INDEX kitap_baslik ON kitap(baslik);
EXPLAIN QUERY PLAN SELECT count(*) FROM kitap WHERE baslik LIKE 'Kitap 1234%';
CREATE INDEX kitap_baslik_nc ON kitap(baslik COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT count(*) FROM kitap WHERE baslik LIKE 'Kitap 1234%';
EXPLAIN QUERY PLAN SELECT count(*) FROM kitap WHERE baslik LIKE '%1234';
.timer on
.stats vmstep
SELECT count(*) FROM kitap WHERE baslik LIKE 'Kitap 1234%';
SELECT count(*) FROM kitap WHERE baslik LIKE '%1234';
SQL

QUERY PLAN
`--SCAN kitap USING COVERING INDEX kitap_baslik
QUERY PLAN
`--SEARCH kitap USING COVERING INDEX kitap_baslik_nc (baslik>? AND baslik<?)
QUERY PLAN
`--SCAN kitap USING COVERING INDEX kitap_baslik_nc
111
VM-steps: 463
Run Time: real 0.000 user 0.000015 sys 0.000014
20
VM-steps: 800031
Run Time: real 0.007 user 0.006536 sys 0.000030

Çıktı iki ayrı şey söylüyor. Birincisi: LIKE '%1234' koşulu, hangi dizin varsa olsun taramayla karşılanıyor. Sol ucu açık bir kalıp için sıralamadan yararlanılamaz; sözlükte “ile biten” kelimeleri bulmanın yolu sözlüğü baştan sona okumaktır. Bu durumda çözüm sorguyu yeniden yazmak değil, yapıyı değiştirmektir — tam metin dizini ya da sütunun ters çevrilmiş bir kopyası üzerinde önek araması.

İkincisi daha incedir: önekli kalıp ilk dizinle de çalışmadı, ancak COLLATE NOCASE tanımlı ikinci dizinle çalıştı. Nedeni, karşılaştırma kuralının uyuşmamasıdır. Bu motorda LIKE varsayılan olarak büyük–küçük harf ayrımı yapmaz, birinci dizin ise ayrım yapan bir karşılaştırmayla sıralanmıştır; iki sıra düzeni aynı olmadığı için dizin kullanılamaz. Kural motordan bağımsızdır: dizinin sıralama kuralı ile koşulun karşılaştırma kuralı aynı olmalıdır. Harf durumu, dil duyarlı sıralama ve boşluk davranışı bu kuralın kapsamına girer.

İşlevden Vazgeçilemiyorsa

Bazı durumlarda hesaplanmış değerin kendisi aranmak zorundadır. Bunun karşılığı, hesabı sorgudan çıkarmak değil, dizine koymaktır: ifade dizini (expression index), bir sütunun değerlerini değil, o sütundan hesaplanan ifadenin değerlerini sıralı tutar.

sqlite3 kutuphane.db <<'SQL'
CREATE INDEX kitap_yazar_buyuk ON kitap(upper(yazar));
EXPLAIN QUERY PLAN SELECT count(*) FROM kitap WHERE upper(yazar) = 'YAZAR 7';
.timer on
.stats vmstep
SELECT count(*) FROM kitap WHERE upper(yazar) = 'YAZAR 7';
SQL

QUERY PLAN
`--SEARCH kitap USING COVERING INDEX kitap_yazar_buyuk (<expr>=?)
50
VM-steps: 212
Run Time: real 0.000 user 0.000014 sys 0.000011

Aynı sorgu, hiç değiştirilmeden, 800.061 adımdan 212 adıma indi. Planda anahtar sütun adı yerine <expr> yazması, dizinin bir sütunu değil bir ifadeyi anahtarladığını gösterir.

İfade dizininin iki koşulu vardır. İfade belirlenimci olmalıdır: aynı satır için her zaman aynı değeri üretmelidir; şimdiki zamanı ya da oturum ayarını okuyan bir ifade dizinlenemez, çünkü dizindeki değer yazıldığı anda eskir. İkincisi, sorgudaki ifade dizindeki ifadeyle eşleşmelidir; upper(yazar) dizini lower(yazar) koşuluna yaramaz.

Kuralın Genel Biçimi

Bu derste dört ayrı yazım incelendi; hepsi tek bir kurala indirgenebilir. Bir koşulun dizinden yararlanabilmesi için dizinlenmiş sütun karşılaştırmanın bir tarafında, üzerine hiçbir hesap uygulanmadan durmalıdır. Literatürde bu biçimdeki koşullara sargable predicate denir.

Uygulamada bu, koşulu şu üç biçimden birine getirmek demektir: sütunu bir sabitle karşılaştırmak, sütunu bir aralığa sokmak, ya da hesabı dizinin tanımına taşımak. Hesabın sorgudan tamamen kaybolması gerekmez — koşulun sağ tarafında, sabit değerlerin üzerinde kalabilir. alis_tarihi >= date('2019-01-01', '-7 days') koşulu dizini kullanır, çünkü hesap sorgu başında bir kez yapılır ve sonucu sabit bir değerdir.

Özet

• Dizin, sütunun saklandığı biçimdeki değerlerini sıralı tutar; sütun üzerine uygulanan her işlev ya da aritmetik bu sırayı kullanılamaz kılar ve plan SCAN olur.
• Bir yılın kayıtlarını isteyen strftime koşulu, aynı sonucu veren yarı açık aralık koşuluna çevrildiğinde bu ortamda dokuz kat az adım ve kırk kat az süre harcadı.
• Sol ucu açık kalıp eşleme hiçbir sıralı dizinden yararlanamaz; çözüm sorguyu yeniden yazmak değil, farklı bir dizin yapısı kurmaktır.
• Dizinin sıralama kuralı ile koşulun karşılaştırma kuralı aynı olmalıdır; harf durumu farkı tek başına dizini devre dışı bırakabilir.
• Hesaplanmış değerin aranması zorunluysa ifade dizini kullanılır; ifade belirlenimci olmalı ve sorgudaki yazımıyla birebir eşleşmelidir.

Sonraki Adım

Buraya kadarki bütün ölçümler tek tablo üzerindeydi ve karar tekti: dizin mi, tarama mı. Birden çok tablo birleştiğinde karar sayısı artar — hangi tablonun önce okunacağı, her adımda hangi erişim yolunun kullanılacağı ve satırların hangi yöntemle eşleştirileceği ayrı ayrı seçilir. Sonraki ders birleştirme sırasının ve algoritmasının plandaki karşılığını ve ölçülen etkisini ele alır.