Veritabanı index nasıl çalışır: full table scan'den B-Tree'ye

Bir milyon satırlık tabloda tek bir e-posta arıyorsun. Sorgu dört saniye sürüyor. Veritabanı yavaş değil; sen ona bir yol tarif etmedin.

Full table scan

Index yoksa veritabanının başka şansı yok: satırları baştan sona tek tek okur. Aradığın kayıt son satırda olabilir, bu yüzden hepsine bakmak zorunda. Buna full table scan deniyor — bir milyon satırda, bir milyon karşılaştırma.

Telefon rehberi benzetmesi

Bir telefon rehberinde birini ararken sayfa sayfa mı bakarsın? Hayır, alfabetik sıraya gider, direkt harfe atlarsın. Index tam olarak bu: verinin önceden sıralanmış bir kopyası. Bu kopya B-Tree denen bir ağaç yapısında durur ve her adımda arama alanını ikiye böler. Bir milyon karşılaştırma yerine yaklaşık yirmi karşılaştırma yeterli oluyor.

Tek satırlık çözüm

CREATE INDEX idx_users_email ON users (email);

Aynı sorgu dört saniyeden iki milisaniyeye iniyor — yaklaşık iki bin kat daha hızlı. Tek satırlık bir migration için fena bir kazanç değil.

Index her derde deva değil

Ama her kolona index atmak çözüm değil: her yazma işleminde bu index de güncellenmek zorunda, yani INSERT/UPDATE yavaşlıyor. Üstelik bazı sorgu şekilleri index'i hiç kullanmıyor — en klasik ikisi:

• Yüzde işaretiyle başlayan LIKE '%istanbul' (sondan eşleşme, index'i devre dışı bırakır; LIKE 'istanbul%' sorun değil)
• Kolonu bir fonksiyonla sarmak: WHERE LOWER(email) = ... — index email üzerinde, LOWER(email) üzerinde değil

Kapanış

Yavaş bir sorgu gördüğünde önce EXPLAIN ANALYZE çalıştır. Sorun çoğu zaman kod değil, eksik ya da yanlış kurulmuş bir index.