İndeks tasarımı: bileşik ve kapsayan indeks (covering index), sütun sırası, bedel

Senaryo: mobil hesap ekranı

Ilgaz Bank mobil uygulamasında kullanıcı hesabına girince son hareketleri görüyor. Sorgu saniyede çok kez çalışıyor. Tek sütunlu account_id indeksi var ama plan bir sıralama adımı daha yapıyor. Bu derste doğru indeksi seçmeyi öğreneceksin.

Sütun sırası: eşitlik, aralık, sıralama

Bileşik indeks telefon rehberi gibidir: önce soyada, sonra ada göre sıralı. Ada göre aramak için rehberin tamamını okursun. Bu soldan önek kuralıdır. Sıralama kuralı da buradan çıkar:

1. Eşitlikle aranan sütunlar önce. Aranan bölge tek bir dilime iner.
2. Aralıkla aranan sütun sonra. Aralıktan sonraki sütunlar arama için kullanılamaz.
3. ORDER BY eşitlik sütunlarının ardından geliyorsa sıralama adımı kalkar.
4. Yalnız okunan sütunlar en sona ya da INCLUDE içine.

Parantez içindeki yazıyı oku. İkinci planda aramayı yalnız tarih yapıyor, tür okunduktan sonra eleniyor:

CREATE INDEX ix_iyi ON bank_txns (txn_type, txn_time);
EXPLAIN QUERY PLAN SELECT SUM(amount) FROM bank_txns
WHERE txn_type = 'maas' AND txn_time >= '2024-11-01' AND txn_time < '2024-12-01';

CREATE INDEX ix_kotu ON bank_txns (txn_time, txn_type);
EXPLAIN QUERY PLAN SELECT SUM(amount) FROM bank_txns
WHERE txn_type = 'maas' AND txn_time >= '2024-11-01' AND txn_time < '2024-12-01';

Aynı fark PostgreSQL'de

CREATE INDEX ix_a ON bank_txns (txn_type, txn_time);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT COUNT(*) FROM bank_txns WHERE txn_type = 'faiz' AND txn_time >= '2024-11-01' AND txn_time < '2024-12-01';

Gerçek çıktı

Aggregate  (cost=7.09..7.10 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Only Scan using ix_a on bank_txns  (cost=0.41..6.82 rows=107 width=0) (actual rows=118 loops=1)
        Index Cond: ((txn_type = 'faiz'::text) AND (txn_time >= '2024-11-01 00:00:00'::timestamp without time zone) AND (txn_time < '2024-12-01 00:00:00'::timestamp without time zone))
        Heap Fetches: 0
        Buffers: shared hit=4

CREATE INDEX ix_b ON bank_txns (txn_time, txn_type);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT COUNT(*) FROM bank_txns WHERE txn_type = 'faiz' AND txn_time >= '2024-11-01' AND txn_time < '2024-12-01';

Gerçek çıktı

Aggregate  (cost=114.28..114.29 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=19
  ->  Index Only Scan using ix_b on bank_txns  (cost=0.29..114.01 rows=108 width=0) (actual rows=118 loops=1)
        Index Cond: ((txn_time >= '2024-11-01 00:00:00'::timestamp without time zone) AND (txn_time < '2024-12-01 00:00:00'::timestamp without time zone) AND (txn_type = 'faiz'::text))
        Heap Fetches: 0
        Buffers: shared hit=19

İkisi de aynı 118 satırı buluyor. İyi sırada 4 sayfa, ters sırada 19 sayfa okunuyor. Tablo büyüdükçe bu fark açılır, çünkü ters sırada aralıktaki bütün türler taranır.

Kapsayan indeks (covering index)

İndeks, sorgunun istediği bütün sütunları içeriyorsa tabloya gidilmez. SQL Server'da bunun yokluğu Key Lookup olarak görünür.

CREATE INDEX ix_hesap_zaman ON bank_txns (account_id, txn_time);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT SUM(amount) FROM bank_txns WHERE account_id = 100019 AND txn_time >= '2024-01-01';

Gerçek çıktı

Aggregate  (cost=203.69..203.70 rows=1 width=32) (actual rows=1 loops=1)
  Buffers: shared hit=68
  ->  Bitmap Heap Scan on bank_txns  (cost=4.91..203.48 rows=80 width=6) (actual rows=72 loops=1)
        Recheck Cond: (account_id = 100019)
        Filter: (txn_time >= '2024-01-01 00:00:00'::timestamp without time zone)
        Heap Blocks: exact=66
        Buffers: shared hit=68
        ->  Bitmap Index Scan on bank_txns_account_id_idx  (cost=0.00..4.89 rows=80 width=0) (actual rows=72 loops=1)
              Index Cond: (account_id = 100019)
              Buffers: shared hit=2

CREATE INDEX ix_hesap_zaman ON bank_txns (account_id, txn_time) INCLUDE (amount);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT SUM(amount) FROM bank_txns WHERE account_id = 100019 AND txn_time >= '2024-01-01';

Gerçek çıktı

Aggregate  (cost=5.52..5.53 rows=1 width=32) (actual rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Only Scan using ix_hesap_zaman on bank_txns  (cost=0.41..5.40 rows=49 width=6) (actual rows=72 loops=1)
        Index Cond: ((account_id = 100019) AND (txn_time >= '2024-01-01 00:00:00'::timestamp without time zone))
        Heap Fetches: 0
        Buffers: shared hit=4

İlk planda yeni indeks seçilmedi bile. Motor var olan account_id indeksiyle satırlara gidiyor ve 68 sayfa okuyor. INCLUDE eklenince 4 sayfada bitiyor. Index Only Scan, Heap Fetches: 0 "tabloya hiç gidilmedi" demektir.

SQL Server belgesi çok sütunlu kapsayan indeksin yararı azalttığını, yer, giriş çıkış ve bellek maliyetini büyüttüğünü özellikle uyarır. SELECT * kullanan sorgu için kapsayan indeks kurma: önce sütunları daralt.

Filtreli (kısmi) indeks

Çoğu sorgu satırların küçük bir kısmıyla ilgiliyse yalnız onu indeksle. İptal edilen siparişler buna iyi örnektir:

CREATE INDEX ix_acik ON lk_orders (placed_at) WHERE status = 'iptal';
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM lk_orders WHERE status = 'iptal' AND placed_at >= '2024-12-01';

SQLite ve PostgreSQL'de kısmi indeks (partial index), SQL Server'da filtreli indeks (filtered index) olarak geçer. Daha küçüktür ve yalnız ilgili satır değiştiğinde güncellenir. MySQL'de bu özellik yoktur.

İndeksin bedeli

• Her INSERT, DELETE ve indeksli sütuna UPDATE indeksi de değiştirir.
• Disk ve bellek ister. Çalışan sorguların sayfaları önbelleği paylaşır.
• Çakışan indeksler boşa gider: (a) varken (a, b) kurduysan (a) genellikle silinebilir.

Bedeli kendin gör. Kutu indeks kurmadan önceki ve sonraki sayfa sayısını gösterir, yazma süresini kutunun altında okursun:

SELECT page_count FROM pragma_page_count;
CREATE INDEX ix_1 ON bank_txns (txn_time);
CREATE INDEX ix_2 ON bank_txns (txn_type, txn_time);
CREATE INDEX ix_3 ON bank_txns (channel, txn_time, amount);
SELECT page_count FROM pragma_page_count;