Sorgu optimizasyonu ve indeksler

On milyon siparişlik bir tabloda tek bir müşterinin siparişlerini bulan sorgu 8 saniye sürüyor; doğru indeksten sonra aynı sorgu milisaniyeler içinde çalışıyor. SQL bildirimsel bir dildir: ne istediğimizi yazarız, nasıl bulunacağını ise VTYS'nin iyileştiricisi (optimizer) seçer. Birkaç olası planı karşılaştırır ve tablo istatistiklerine dayanarak en ucuzunu alır. Bu derste iyileştiricinin nasıl “düşündüğünü” öğreneceğiz: indeksin iç yapısı, planın okunması ve onun işini kolaylaştıran sorgular. İyi haber: bunun için özel araçlar gerekmez; her şey EXPLAIN komutu ve birkaç basit kuralla başlar.

B-ağacı indeksinin içi

Çoğu VTYS'de varsayılan indeks bir B-ağacıdır (tam söylemek gerekirse B+ ağacı): anahtarlar disk sayfalarında sıralı biçimde tutulur, her iç sayfada yüzlerce anahtar ve alt düzeye işaretçiler bulunur, yapraklar ise satırlara başvurur ve birbirine zincirlenir. Ağaç her zaman dengelidir: kökten herhangi bir yaprağa giden yol aynı uzunluktadır. Bu yüzden bir arama yalnızca birkaç sayfa okumasıyla biter; yaprak zinciri de aralık sorgularını (BETWEEN, >, ORDER BY) ucuzlatır.

Sargable koşullar

Sargable (search argument able), indeksle aramaya uygun bir koşul demektir: sütun, karşılaştırmanın bir tarafında “çıplak” durur. Sütuna bir fonksiyon ya da aritmetik uygulandığında (strftime('%m', order_date) = '03', price * 1.18 > 100) indeksteki sıralı değerler işe yaramaz ve VTYS her satırı denetlemek zorunda kalır. Aynı koşulu bir aralık olarak yeniden yazmak yeterlidir.

CREATE INDEX idx_orders_date ON orders (order_date);

-- not sargable: a function on the column
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE strftime('%m', order_date) = '03';

id | parent | notused | detail
2 | 0 | 214 | SCAN orders USING COVERING INDEX idx_orders_date

CREATE INDEX idx_orders_date ON orders (order_date);

-- sargable: a range on the bare column
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

id | parent | notused | detail
2 | 0 | 154 | SEARCH orders USING COVERING INDEX idx_orders_date (order_date>? AND order_date<?)

Bileşik ve kapsayan indeksler

Bileşik bir indeks birkaç sütuna göre sıralanır: (customer_id, order_date) önce müşteriye, aynı müşteri içinde ise tarihe göre. Bir telefon rehberi gibi çalışır: soyadla ya da soyad ve adla arama yapılabilir, ama yalnızca adla yapılamaz; buna sol önek kuralı denir. İndeks bir sorgunun gerektirdiği tüm sütunları içeriyorsa VTYS tabloya hiç başvurmaz; böyle bir indekse kapsayan (covering) indeks denir. Ayrıca indeksin sırası ORDER BY ile örtüştüğünde ayrı bir sıralama adımı da ortadan kalkar.

-- before: function on the column, no suitable index
EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders
WHERE customer_id = 1 AND strftime('%Y', order_date) = '2025'
ORDER BY order_date;

id | parent | notused | detail
3 | 0 | 216 | SCAN orders
15 | 0 | 0 | USE TEMP B-TREE FOR ORDER BY

-- after: sargable range + composite index
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
ORDER BY order_date;

id | parent | notused | detail
3 | 0 | 46 | SEARCH orders USING COVERING INDEX idx_orders_cust_date (customer_id=? AND order_date>? AND order_date<?)

PostgreSQL'de EXPLAIN planı ve tahmini maliyeti gösterir, EXPLAIN ANALYZE ise sorguyu gerçekten çalıştırıp gerçek süreleri gösterir. Aşağıda 1.000.000 satırlık bir tabloda indeksten önce ve sonra alınan tipik bir sonuç var (sayılar donanıma bağlıdır). cost=başlangıç..toplam keyfî birimlerdedir, rows ise iyileştiricinin tahminidir; tahmin gerçek rows değerinden çok farklıysa istatistikler eskimiştir ve ANALYZE gerekir.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..20834.00 rows=10 width=24) (actual time=0.018..61.922 rows=12 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999988
Planning Time: 0.081 ms
Execution Time: 61.950 ms

Index Scan using idx_orders_customer on orders  (cost=0.42..44.58 rows=10 width=24) (actual time=0.024..0.039 rows=12 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.210 ms
Execution Time: 0.061 ms

İndeks ne zaman gereksizdir

• Küçük tablolar — birkaç sayfalık bir tabloyu taramak, ağaçta inmekten daha ucuzdur.
• Düşük seçicilik — is_active gibi iki değerli bir sütunda koşul satırların yarısını döndürür.
• Yoğun yazılan tablolar — her indeks her INSERT, UPDATE ve DELETE işleminde güncellenir; günlük ve sensör tablolarında bu pahalıdır.
• Yinelenen indeksler — (customer_id, order_date) varsa ayrı bir (customer_id) indeksi gereksizdir; sol önek onun yerini tutar.

CREATE INDEX idx_orders_date ON orders (order_date);

-- slow version: WHERE strftime('%m', order_date) IN ('01', '02', '03')
SELECT id, order_date
FROM orders
-- WHERE: a range on the bare column
ORDER BY order_date, id;

id | order_date
1 | 2025-01-15
2 | 2025-01-15
3 | 2025-02-02
4 | 2025-02-10
5 | 2025-03-05
6 | 2025-03-18

SELECT category,
       COUNT(*) AS rows_count
       -- selectivity_pct = 100 * n / N
FROM products
GROUP BY category
ORDER BY rows_count DESC, category;

category | rows_count | selectivity_pct
Electronics | 4 | 40
Accessories | 2 | 20
Stationery | 2 | 20
Games | 1 | 10
Home | 1 | 10