Veritabanı indeksi: Ne zaman oluşturmalı, ne zaman oluşturmamalı

İki milyon satırlı bir tabloda sorgunuz sekiz saniye sürüyor ve sayfa bir şey göstermeden önce zaman aşımına uğruyor. MySQL logunu açıyorsunuz, sorguyu EXPLAIN içine atıyorsunuz ve type sütununda ALL görüyorsunuz; yani motor tüm tabloyu okuyor. Şimdi asıl soru şu: hangi sütuna indeks oluşturmalı ve gerçekten oluşturmalı mıyız?

Veritabanı indeksi, MySQL'in tüm tabloyu taramadan satırlara ulaşabilmesi için ana verinin yanında tuttuğu sıralı bir yapıdır. Ancak her indeks, her INSERT, UPDATE ve DELETE işleminde güncellenmesi gereken ikinci bir tablodur. İşte denklem tam burada bozulur.

EXPLAIN neden type=ALL ile tablonun taranmakta olduğunu gösterir

WHERE koşulu indeksi olmayan bir sütun üzerindeyse, MySQL'in tüm satırları okumaktan başka çaresi yoktur. Bunu basit bir örnekle görelim:

EXPLAIN SELECT id, title FROM posts WHERE author_id = 42;
-- type: ALL, rows: 1843200, key: NULL

rows sayısı tahminidir, kesin değil, ancak milyonlara ulaştığında bu bir felaket demektir. İndeks oluşturulduktan sonra:

CREATE INDEX idx_author ON posts (author_id);
-- type: ref, rows: 312, key: idx_author

1,8 milyon satır ile 312 satır arasındaki fark, kullanıcının hızlı sayfa yüklemesi olarak hissettiği şeydir. WordPress üzerinde çalışıyorsanız ve yazar arşivi sayfaları yavaşsa, muhtemelen tam da bu indeks eksiktir.

Bileşik indeks ve herkesin yanlış anladığı sıralama

Bileşik indeks birden fazla sütun üzerinde oluşturulur ve sütun sıralaması gerçek anlam taşır. Kural basittir: sütunları soldan sağa okuyun; MySQL indeksin koşulların kesintisiz olduğu kısmına kadar kullanır.

CREATE INDEX idx_status_date ON orders (status, created_at);

Bu indeks WHERE status = 'paid' için ve WHERE status = 'paid' AND created_at > '2024-01-01' için çalışır. Ancak tek başına WHERE created_at > '2024-01-01' için işe yaramaz, çünkü ilk sütun koşulda yok. İşte burada hata yapılıyor: bileşik indeks oluşturuluyor, sorgu test ediliyor, yavaş olduğu görülüyor ve "indeks işe yaramıyor" sonucuna varılıyor. Aslında indeks var, sıralaması yanlış.

Sıralama seçimi kuralı: eşitlik koşulunda (=) gelen sütun önce, aralıkta (>, <, BETWEEN) gelen sütun ondan sonra. ORDER BY sütunları da sona eklenebilir, böylece sıralama indeks üzerinden yapılır ve filesort ortadan kalkar.

Kimsenin hesaplamadığı maliyet: yazma hızı

Her ek indeks, her INSERT işlemini yavaşlatır. Beş indeksli bir tabloda bir satır eklemek, bir yerine altı disk yazımı demektir. Tablonuz ağırlıklı olarak okunuyorsa (posts veya products gibi) bu maliyet önemsizdir. Tablo yazma odaklıysa (log, oturum, mesaj kuyruğu, yoğun trafikli wp_options tablosu) her ek indeks bir borçtur.

Pratikte saniyede 2000 ekleme yapılan bir log tablosunda, gereksiz bir indeks eklemek ekleme hızının yaklaşık 1400'e düşmesine ve kuyruğun tıkanmasına neden oldu. Sorun okuma sorgusunda değildi; neden oluşturulduğunu kimsenin hatırlamadığı bir indeksteydi.

O halde doğru karar şudur: indeksi gerçekten çalıştırılan sorgular için oluşturun, "belki bir gün gerekir" diye her sütun için değil. Kullanılmayan indeksleri sys.schema_unused_indexes ile bulun:

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql','sys');

Ne zaman indeks oluşturmamalı

• Yalnızca iki değeri olan is_active gibi düşük kardinaliteli sütunlarda. Böyle bir sütun üzerindeki indeks genellikle reddedilir çünkü optimizasyon yapısı indeks okumanın taramadan daha pahalı olduğunu anlar.
• Birkaç bin satırın altındaki küçük tablolarda. Bunların tam taraması milisaniye düzeyindedir.
• WHERE içinde karmaşık bir fonksiyona sarılmış sütunda: WHERE YEAR(created_at) = 2024, created_at üzerindeki indeksi etkisiz kılar. Koşulu aralığa yeniden yazın.
• Yazma işleminin baskın olduğu ve okuma sorgularının seyrek olduğu tablolarda.

Kapsayıcı indeks ve gizli kalan maliyet

Kapsayıcı indeks (covering index) sorgunun ihtiyaç duyduğu tüm sütunları içinde barındırır, böylece MySQL ana tabloya hiç başvurmaz. EXPLAIN içinde bunu Extra sütununda Using index ile görürsünüz. Normal durumla arasındaki fark birkaç kat olabilir, çünkü tabloya her başvuru diskte rastgele bir okumadır.

CREATE INDEX idx_cover ON orders (status, created_at, total);
SELECT status, created_at, total FROM orders WHERE status = 'paid';

Ancak bu indeks daha büyüktür ve innodb_buffer_pool_size içinde daha fazla bellek kaplar. Tampon küçükse, kapsayıcı indeks bellekten çıkar ve avantajını kaybeder. Burada gerçek bir seçim vardır: tabloya başvuran dar indeks mi, yoksa daha fazla bellek tüketen geniş indeks mi. Yoğun trafikli tablolar için, sorgu gerçekten sıcak olmadıkça dar indeksi tercih ederim.

Pratik yöntem: öncesini ve sonrasını ölçün

1. Yavaş sorguyu slow_query_log içinden alın. long_query_time değerini 1 saniyeye ayarlayın ve bir gün bekleyin.
2. EXPLAIN ile hangi aşamanın pahalı olduğunu görün. type=ALL ise indeks eksiktir.
3. İndeksi oluşturun ve aynı sorguyu tekrar EXPLAIN ve EXPLAIN ANALYZE ile ölçün.
4. Çalışma süresini SHOW PROFILES veya yavaş log ile karşılaştırın. Önceki ve sonraki sayıyı not edin; insan hafızası zayıftır.
5. Bir hafta sonra schema_unused_indexes ile indeksin gerçekten kullanıldığını kontrol edin.

Paylaşımlı sunucularda, büyük bir tabloda indeks oluşturmak kilit alır ve siteyi birkaç dakika erişilemez hale getirir. MySQL 5.6 veya üstü kullanıyorsanız, yazmanın durmaması için ALTER TABLE ... ADD INDEX ... , ALGORITHM=INPLACE, LOCK=NONE kullanın. Daha eski sürümlerde bu seçenek yoktur ve düşük trafikli saatlerde çalışmanız gerekir.

WordPress üzerinde çalışıyorsanız ve yoğun saatlerde tablo kilidiyle uğraşmak istemiyorsanız, bu değişiklikleri kontrol paneli ve doğrudan MySQL erişimi olan bir WordPress hosting üzerinde çalıştırmak işi kolaylaştırır. WordPress'in tekrarlayan sorguları için, tablo yapısına dokunmadan önce WordPress wp-cron; neden çalışmıyor ve gerçek cron ile nasıl değiştirilir yazısına bir göz atın; bazen yavaşlık sorgudan değil, arka arkaya çalışan zamanlanmış görevlerden kaynaklanır.

Sunucu durumunu ve servislerin yanıt verme durumunu hızlıca kontrol etmek için, ServerNet'in ücretsiz araçları hiçbir şey kurmadan kullanılabilir. Tablo yapıları ve sorgularla yeni tanışıyorsanız, dokümantasyon ve bilgi tabanı ana sunucuda deneme yanılma yapmaktan daha iyi bir başlangıç noktasıdır. Ve bağlantı hatası veya zaman aşımı alıyorsanız, veritabanında herhangi bir değişiklik yapmadan önce HTTP durum kodları: yaygın hatalar için pratik rehber yazısını kontrol edin, sorunun başka bir yerden değil veritabanı katmanından geldiğinden emin olun.

Sık sorulan sorular

Veritabanı indeksi yazma hızını düşürür mü?

Evet ve bu gerçek bir maliyettir. Her indeks, her ekleme, güncelleme ve silme işleminde güncellenmelidir, dolayısıyla diskteki yazma işlemi sayısı indeks sayısı kadar artar. Okuma odaklı tablolarda bu maliyet önemsizdir, ancak log veya oturum gibi yazma yoğun tablolarda hissedilir olabilir.

Bileşik indekste sütun sıralaması ne kadar önemli?

Çok önemli ve en büyük hata tam buradan gelir. Eşitlik koşulunda gelen sütun ilk olmalı ve aralık sütunu ondan sonra gelmelidir. Sıralama ters olursa, MySQL indeksin ilk kısmını kullanamaz ve sorgu yine yavaş kalır.

Bir indeksin kullanılmadığını nasıl anlarım?

MySQL 5.7 ve üstünde, sys.schema_unused_indexes tablosu sunucunun son yeniden başlatılmasından bu yana kullanılmayan indeksleri gösterir. Bir indeks haftalarca bu listede kalıyorsa, muhtemelen yalnızca yazma maliyeti vardır ve onu silebilirsiniz.

Uzun metin sütunlarında indeks işe yarar mı?

Uzun VARCHAR veya TEXT üzerinde tam indeks hacimli ve yavaş olur. Çözüm önek indeksidir: CREATE INDEX idx_title ON posts (title(20));. Bu yöntem LIKE 'foo%' araması için çalışır ancak LIKE '%foo%' için çalışmaz.

Her indeksi oluşturmadan önce bir soru sorun: hangi sorgu hızlanacak? Cevap net değilse indeksi oluşturmayın. Kimsenin sorgusu olmayan bir indeks, yalnızca yazma hızını düşürür ve tampon belleği tüketir.