Sorgu planı ve indeks: büyük tabloda hız farkı

EXPLAIN QUERY PLAN ile tam tarama ve indeks aramasını ayırt etmek, indeks oluşturup süre farkını ölçmek, sütuna fonksiyon uygulamanın indeksi nasıl devre dışı bıraktığını görmek.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Plan neyi söyler?

Veritabanı bir sorguyu çalıştırmadan önce bir plan yapar: tabloyu baştan sona mı tarayacak (SCAN), yoksa bir indeksle doğrudan ilgili satırlara mı gidecek (SEARCH)? SQLite'ta planı EXPLAIN QUERY PLAN gösterir. Diğer SQL türlerinde karşılığı EXPLAIN (PostgreSQL, MySQL), tahmini yürütme planı (SQL Server) ve EXPLAIN PLAN FOR (Oracle) ifadeleridir.

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM bk_trips WHERE start_station = 5;

bk_trips tablosunda yalnızca started_at ve user_id üzerinde indeks var; istasyon sütunlarında yok. Bu yüzden start_station filtresi SCAN bk_trips (tam tarama) üretir. Sonuç kutusunun altındaki süreye dikkat et.

İndeks oluştur, tekrar ölç

CREATE INDEX ix_trips_start ON bk_trips (start_station);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM bk_trips WHERE start_station = 5;

Plan artık SEARCH bk_trips USING COVERING INDEX ix_trips_start der: motor yalnızca indeksi okur. Ders kutuları her çalıştırmada temiz bir veritabanıyla başlar; indeks yalnızca o çalıştırma boyunca vardır.

İndeksi bozan yazımlar

Yavaş (indeks kullanılamaz)	Hızlı (indeks kullanılır)
`WHERE date(started_at) = '2024-07-01'`	`WHERE started_at >= '2024-07-01' AND started_at < '2024-07-02'`
`WHERE strftime('%m', started_at) = '07'`	`WHERE started_at >= '2024-07-01' AND started_at < '2024-08-01'`
`WHERE start_station + 0 = 5`	`WHERE start_station = 5`

Sütuna fonksiyon uygulamak, motorun indeksteki sıralı değerleri kullanmasını engeller (sargable olmayan koşul).

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM bk_trips WHERE date(started_at) = '2024-07-01';
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM bk_trips WHERE started_at >= '2024-07-01' AND started_at < '2024-07-02';

Birleştirmede indeks

Büyük bir tabloyu küçük bir tabloyla birleştirirken eşleşme koşulu küçük tablonun anahtarı üzerindeyse her satır için hızlı bir arama yapılır. Saatlik hava durumunu yolculuklarla birleştirmenin hızlı yolu, yolculuk tarafında saati hesaplayıp hava tablosunun birincil anahtarında aramaktır:

SELECT CASE WHEN w.rain_mm > 0 THEN 'yağmurlu' ELSE 'kuru' END AS hava, COUNT(*) AS yolculuk
FROM bk_trips t
JOIN bk_weather w ON w.obs_hour = strftime('%Y-%m-%d %H:00:00', t.started_at)
GROUP BY CASE WHEN w.rain_mm > 0 THEN 'yağmurlu' ELSE 'kuru' END;

Tersini yapmak, yani hava tablosundan başlayıp her saat için tüm yolculukları taramak, aynı sonucu çok daha uzun sürede verir.

İndeksin bedeliİndeks okumayı hızlandırır ama her INSERT ve UPDATE'te güncellenmesi gerekir ve yer kaplar. Sık filtrelenen ve birleştirilen sütunlara indeks koy; her sütuna değil.