Index Stratejileri: MySQL’de Doğru Index Nasıl Seçilir

yazar

Veritabanı performans sorunlarının büyük çoğunluğu yanlış ya da eksik index kullanımından kaynaklanır. Bir sorgu saniyeler alıyorsa, sunucu yük altında çöküyorsa ya da EXPLAIN çıktısı seni korkutuyorsa, büyük ihtimalle index stratejin gözden geçirilmeye ihtiyaç duyuyor. Bu yazıda MySQL ve MariaDB üzerinde index seçimini, nasıl analiz edileceğini ve gerçek dünya senaryolarında nasıl optimize edileceğini ele alacağız.

Index Nedir ve MySQL Nasıl Kullanır?

Index, MySQL’in tablodaki satırları daha hızlı bulmasını sağlayan bir veri yapısıdır. Bir kitabın arkasındaki dizin gibi düşün: her şeyi tek tek okumak yerine doğrudan ilgili sayfaya atlarsın. MySQL varsayılan olarak InnoDB storage engine kullanır ve bu engine B-Tree index yapısını tercih eder.

Index olmadan MySQL, sorguyu çalıştırmak için tüm tabloyu tarar. Buna full table scan denir ve tabloda milyonlarca satır varsa bu işlem felakete dayanır.

# Tablodaki mevcut indexleri listele
mysql -u root -p veritabani_adi -e "SHOW INDEX FROM kullanicilar;"

# Tablo boyutu ve satır sayısını kontrol et
mysql -u root -p veritabani_adi -e "
SELECT 
    table_name,
    table_rows,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Boyut (MB)'
FROM information_schema.tables 
WHERE table_schema = 'veritabani_adi'
ORDER BY data_length + index_length DESC;"

Index Türleri ve Kullanım Senaryoları

PRIMARY KEY

Her tablonun olması gereken en temel index türüdür. InnoDB’de primary key, verinin fiziksel sıralanma düzenini belirler. Bu yüzden primary key seçimi kritik önem taşır.

  • AUTO_INCREMENT INT: Küçük ve orta ölçekli tablolar için idealdir
  • UUID: Dağıtık sistemlerde tercih edilir ama ekleme performansını düşürür çünkü B-Tree sıralı veri bekler
  • Composite PK: Çok-çok ilişki tablolarında kullanılır

Unique Index

Benzersiz değer garantisi sağlarken aynı zamanda arama hızını artırır. Email, kullanıcı adı gibi alanlar için vazgeçilmezdir.

# Unique index oluşturma
mysql -u root -p veritabani_adi -e "
CREATE UNIQUE INDEX idx_email_unique ON kullanicilar(email);

# Mevcut unique index'i kontrol et
SHOW CREATE TABLE kullanicilarG"

Composite (Bileşik) Index

Birden fazla sütunu kapsayan indexlerdir. Burada en önemli nokta sütun sırasıdır. MySQL composite index’i soldan sağa okur, yani (a, b, c) şeklinde bir index varsa a, a+b ya da a+b+c kombinasyonları için kullanılabilir, ancak sadece b ya da c için kullanılamaz.

Full-Text Index

Metin arama için kullanılır. LIKE '%kelime%' yerine MATCH() AGAINST() kullanımına olanak tanır.

HASH Index

Memory storage engine ile kullanılır. Eşitlik karşılaştırmaları için çok hızlıdır ama range sorguları için kullanılamaz.

EXPLAIN ile Sorgu Analizi

Index stratejisi belirlemeden önce sorgularını analiz etmen şart. EXPLAIN komutu MySQL’in bir sorguyu nasıl çalıştıracağını gösterir.

# Temel EXPLAIN kullanımı
mysql -u root -p veritabani_adi -e "
EXPLAIN SELECT * FROM siparisler 
WHERE kullanici_id = 100 
AND durum = 'tamamlandi' 
AND tarih > '2024-01-01'G"

# EXPLAIN ANALYZE ile gerçek çalışma sürelerini gör (MySQL 8.0+)
mysql -u root -p veritabani_adi -e "
EXPLAIN ANALYZE SELECT * FROM siparisler 
WHERE kullanici_id = 100 
AND durum = 'tamamlandi'G"

EXPLAIN çıktısında dikkat etmen gereken alanlar:

  • type: ALL kötü, const veya ref iyi demektir
  • key: Hangi index kullanıldığını gösterir, NULL ise hiç index kullanılmıyor
  • rows: Taranacak tahmini satır sayısı, ne kadar düşükse o kadar iyi
  • Extra: Using filesort veya Using temporary görüyorsan sorun var demektir
  • possible_keys: MySQL’in kullanabileceği indexler

type değerleri en iyiden en kötüye şu şekilde sıralanır: const, eq_ref, ref, range, index, ALL. Prod ortamında ALL görüyorsan anında müdahale et.

Gerçek Dünya Senaryosu 1: E-Ticaret Sipariş Sorgulama

Diyelim ki bir e-ticaret platformu yönetiyorsun ve siparişler tablosunda 50 milyon satır var. Müşteri hizmetleri ekibi şu sorguyu çalıştırıyor:

SELECT * FROM siparisler 
WHERE kullanici_id = 12345 
AND durum IN ('beklemede', 'islemde') 
AND olusturma_tarihi BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY olusturma_tarihi DESC;
# Önce mevcut durumu analiz et
mysql -u root -p eticaret -e "
EXPLAIN SELECT * FROM siparisler 
WHERE kullanici_id = 12345 
AND durum IN ('beklemede', 'islemde') 
AND olusturma_tarihi BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY olusturma_tarihi DESCG"

# Bu sorgu için optimize index oluştur
# Sıralama önemli: equality önce, range en sona
mysql -u root -p eticaret -e "
CREATE INDEX idx_siparis_optimized 
ON siparisler(kullanici_id, durum, olusturma_tarihi);"

Buradaki mantık şu: önce eşitlik karşılaştırmaları (kullanici_id, durum), sonra range sorgusu (olusturma_tarihi). Eğer range sütununu ortaya koyarsan, ondan sonraki sütunlar index tarafından kullanılamaz.

Gerçek Dünya Senaryosu 2: Slow Query Log Analizi

Prod sunucunda yavaş sorgular yaşıyorsan slow query log’u aktif et ve analiz et.

# Slow query log'u aktif et (my.cnf'e ekle veya runtime'da ayarla)
mysql -u root -p -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';"

# mysqldumpslow ile log'u analiz et
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# pt-query-digest ile daha detaylı analiz (Percona Toolkit)
pt-query-digest /var/log/mysql/slow-query.log | head -100

# En çok indexsiz çalışan sorguları bul
mysql -u root -p -e "
SELECT * FROM sys.statements_with_full_table_scans 
ORDER BY no_index_used_count DESC 
LIMIT 10G"

Index Seçim Kriterleri

Kardinalite (Cardinality) Önemi

Kardinalite, bir sütundaki benzersiz değer sayısıdır. Yüksek kardinalite, index için daha uygundur. Cinsiyet sütunu gibi yalnızca E/K değerleri alan bir sütuna index koymak genellikle anlamsızdır çünkü MySQL yine de tablonun yarısını tarayacaktır.

# Sütun kardinalitesini analiz et
mysql -u root -p veritabani_adi -e "
SELECT 
    COUNT(DISTINCT durum) AS durum_kardinalite,
    COUNT(DISTINCT kullanici_id) AS kullanici_kardinalite,
    COUNT(DISTINCT sehir) AS sehir_kardinalite,
    COUNT(*) AS toplam_satir
FROM siparisler;"

# Index istatistiklerini güncelle
mysql -u root -p veritabani_adi -e "ANALYZE TABLE siparisler;"

Prefix Index Kullanımı

Uzun VARCHAR sütunlara tam index koymak disk alanı ve bellek açısından maliyetlidir. Prefix index bu sorunu çözer.

# Optimum prefix uzunluğunu bul
mysql -u root -p veritabani_adi -e "
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) AS prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) AS prefix_15,
    COUNT(DISTINCT email) AS tam_deger
FROM kullanicilar;"

# Prefix index oluştur
mysql -u root -p veritabani_adi -e "
CREATE INDEX idx_email_prefix ON kullanicilar(email(10));"

Prefix index’in dezavantajı ORDER BY ve GROUP BY işlemlerinde kullanılamamasıdır.

Covering Index

Sorgunun ihtiyaç duyduğu tüm sütunları kapsayan index’e covering index denir. Bu durumda MySQL asıl tabloya hiç dokunmaz, tüm veriyi index’ten okur. EXPLAIN çıktısında Using index görüyorsan bu senaryoyu yakalamışsın demektir.

# Covering index örneği
# Sorgu: SELECT kullanici_id, toplam_tutar, durum FROM siparisler WHERE kullanici_id = 100

mysql -u root -p eticaret -e "
# Bu index sorguyu tamamen karşılar, tabloya dokunmaz
CREATE INDEX idx_covering 
ON siparisler(kullanici_id, toplam_tutar, durum);

# Verify et
EXPLAIN SELECT kullanici_id, toplam_tutar, durum 
FROM siparisler 
WHERE kullanici_id = 100G"

Index Bakımı ve İzleme

Index kullanımı zamanla değişir, tablolar büyür, sorgular değişir. Kullanılmayan indexler ekstra disk alanı kaplar ve INSERT/UPDATE/DELETE işlemlerini yavaşlatır.

# Kullanılmayan indexleri tespit et (MySQL 8.0+)
mysql -u root -p -e "
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;"

# Duplicate ve redundant indexleri bul
mysql -u root -p -e "
SELECT * FROM sys.schema_redundant_indexesG"

# Index kullanım istatistikleri
mysql -u root -p -e "
SELECT 
    object_schema,
    object_name,
    index_name,
    count_star,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'veritabani_adi'
ORDER BY count_star ASC
LIMIT 20;"

Sık Yapılan Hatalar

WHERE Sütununu Fonksiyon İçinde Kullanmak

Bu çok yaygın bir hata. Sütun üzerinde fonksiyon kullandığında MySQL index’i kullanamaz.

# YANLIS: Index kullanilmaz
mysql -u root -p -e "
EXPLAIN SELECT * FROM siparisler 
WHERE YEAR(olusturma_tarihi) = 2024G"

# DOGRU: Range sorgusu ile index kullanilir
mysql -u root -p -e "
EXPLAIN SELECT * FROM siparisler 
WHERE olusturma_tarihi BETWEEN '2024-01-01' AND '2024-12-31'G"

# YANLIS: LIKE ile wildcard basta
mysql -u root -p -e "
EXPLAIN SELECT * FROM urunler WHERE urun_adi LIKE '%laptop%'G"

# DOGRU: Wildcard sonda olursa index kullanilir
mysql -u root -p -e "
EXPLAIN SELECT * FROM urunler WHERE urun_adi LIKE 'laptop%'G"

Implicit Type Conversion

Sütun tipi ile sorgu değeri tipi uyuşmazsa MySQL implicit conversion yapar ve index atlanır.

# Kullanici_id INT ama string olarak sorgulanıyor - YANLIS
mysql -u root -p -e "
EXPLAIN SELECT * FROM kullanicilar WHERE kullanici_id = '12345'G"

# Dogru tip kullanımı - DOGRU
mysql -u root -p -e "
EXPLAIN SELECT * FROM kullanicilar WHERE kullanici_id = 12345G"

OR Kullanımı ve Index

OR koşulları bazen index kullanımını engelleyebilir. UNION ALL ile daha iyi sonuç alabilirsin.

# OR yerine UNION ALL dene
mysql -u root -p -e "
EXPLAIN 
SELECT * FROM siparisler WHERE kullanici_id = 100
UNION ALL
SELECT * FROM siparisler WHERE kargo_id = 200G"

Online Index Oluşturma

Prod ortamında büyük tabloya index eklemek tabloyu kitleyebilir. MySQL 5.6’dan itibaren ALGORITHM=INPLACE ve LOCK=NONE seçenekleri bu sorunu büyük ölçüde çözdü.

# Online index oluşturma - tablo kilitlenmez
mysql -u root -p veritabani_adi -e "
ALTER TABLE siparisler 
ADD INDEX idx_yeni (kullanici_id, durum) 
ALGORITHM=INPLACE, 
LOCK=NONE;"

# Percona Toolkit ile çok daha güvenli online schema değişikliği
pt-online-schema-change 
  --alter "ADD INDEX idx_yeni (kullanici_id, durum)" 
  --host=localhost 
  --user=root 
  --password=sifre 
  D=veritabani_adi,t=siparisler 
  --execute

# İşlem sırasında tablo kilit durumunu izle
watch -n 1 "mysql -u root -p'sifre' -e 'SHOW PROCESSLIST' 2>/dev/null | grep -v Sleep"

Index Tuning Checklist

Gerçek hayatta bir performans sorunu geldiğinde şu adımları takip et:

  • Önce EXPLAIN veya EXPLAIN ANALYZE ile sorguyu analiz et
  • type: ALL görüyorsan acil müdahale gereklidir
  • Slow query log’daki sorguları periyodik olarak incele
  • sys.schema_unused_indexes ile gereksiz indexleri temizle
  • ANALYZE TABLE komutunu periyodik olarak çalıştır, istatistikler güncel kalsın
  • Composite index oluştururken equality sütunları öne al, range sütunlarını sona bırak
  • Fonksiyon içinde sütun kullanmaktan kaçın
  • Covering index fırsatlarını değerlendir
  • Kardinalitesi düşük sütunlara tek başına index koymaktan kaçın
  • Prod ortamında pt-online-schema-change kullan, doğrudan ALTER TABLE tehlikelidir

Sonuç

MySQL’de doğru index stratejisi seçmek tek seferlik bir iş değil, sürekli devam eden bir süreçtir. Tablolar büyür, sorgular değişir, uygulama gereksinimleri evrişir. Haftalık ya da aylık periyotlarla slow query log’u analiz etmek, kullanılmayan indexleri temizlemek ve EXPLAIN çıktılarını yorumlamayı alışkanlık haline getirmek ciddi fark yaratır.

En önemli prensip şu: index eklemeden önce ölç, index ekledikten sonra tekrar ölç. Varsayımlara değil, veriye dayalı karar ver. EXPLAIN ANALYZE ve performance_schema bu konuda sana gerçek sayıları verir. Ayrıca her index, INSERT/UPDATE/DELETE performansını etkiler. Dolayısıyla gereksiz index, hem disk alanı hem de yazma performansı açısından maliyet demektir. Doğru denge, sorgu profiline göre değişir.

Sysadmin olarak veritabanı optimizasyonunu tamamen DBA’ye bırakmak yerine temel analiz becerilerini geliştirmen, sorun anında çok daha hızlı müdahale etmeni sağlar. Bir EXPLAIN çıktısını okuyabilmek seni yarım DBA yapar ve bu hiç küçümsemece bir şey değil.

Yorum yapın