MySQL ve MariaDB’de Index Olmadan ve Index ile Sorgu Hız Karşılaştırması

Tarafındankomut
0 Yorum

Veritabanı performansı söz konusu olduğunda, en sık karşılaşılan sorunların başında yanlış veya eksik index kullanımı gelir. Bir uygulama ilk kurulduğunda her şey yolunda gidebilir, ancak tablo büyüdükçe ve veri miktarı arttıkça sorgular yavaşlamaya başlar. “Neden bu kadar yavaş?” sorusunun cevabı çoğu zaman tek bir kelimede saklıdır: index.

Bu yazıda, index olmadan ve index ile çalışan sorguların gerçek dünyada nasıl davrandığını, performans farklarını elle tutulur örneklerle göreceğiz. Teorik bilginin ötesine geçip gerçek senaryolar üzerinden ilerliyoruz.

Test Ortamını Hazırlayalım

Önce üzerinde çalışacağımız test veritabanını ve tablosunu oluşturalım. E-ticaret sitesi senaryosu kullanacağız çünkü bu tür sistemlerde index sorunları en çok acı vereni.

mysql -u root -p
CREATE DATABASE performans_testi;
USE performans_testi;

CREATE TABLE siparisler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    musteri_id INT NOT NULL,
    urun_adi VARCHAR(200) NOT NULL,
    kategori VARCHAR(100),
    fiyat DECIMAL(10,2),
    siparis_tarihi DATETIME,
    durum VARCHAR(50),
    sehir VARCHAR(100),
    kargo_no VARCHAR(50)
);

Şimdi bu tabloya milyonlarca satır ekleyelim. Gerçekçi bir test için en az 1-2 milyon kayıt olması şart. Bunun için bir stored procedure yazıyoruz:

DELIMITER //
CREATE PROCEDURE veri_doldur()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE tarih DATETIME;
    DECLARE durumlar VARCHAR(200) DEFAULT 'bekliyor,onaylandi,kargolandi,teslim edildi,iptal edildi';
    DECLARE sehirler VARCHAR(500) DEFAULT 'Istanbul,Ankara,Izmir,Bursa,Antalya,Konya,Adana,Gaziantep,Mersin,Kayseri';
    
    WHILE i <= 2000000 DO
        SET tarih = DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 730) DAY);
        
        INSERT INTO siparisler (musteri_id, urun_adi, kategori, fiyat, siparis_tarihi, durum, sehir, kargo_no)
        VALUES (
            FLOOR(RAND() * 100000) + 1,
            CONCAT('Urun_', FLOOR(RAND() * 10000)),
            ELT(FLOOR(RAND() * 5) + 1, 'Elektronik', 'Giyim', 'Kitap', 'Ev Esyasi', 'Spor'),
            ROUND(RAND() * 5000 + 10, 2),
            tarih,
            ELT(FLOOR(RAND() * 5) + 1, 'bekliyor', 'onaylandi', 'kargolandi', 'teslim edildi', 'iptal edildi'),
            ELT(FLOOR(RAND() * 10) + 1, 'Istanbul', 'Ankara', 'Izmir', 'Bursa', 'Antalya', 'Konya', 'Adana', 'Gaziantep', 'Mersin', 'Kayseri'),
            CONCAT('KRG', LPAD(i, 10, '0'))
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL veri_doldur();

Bu işlem birkaç dakika sürecek. Sabırla bekleyin, sonuçlar buna değecek.

EXPLAIN ile Sorgu Analizi

Index testine geçmeden önce EXPLAIN komutunu tanımak şart. Bu komut, MySQL/MariaDB’nin sorguyu nasıl çalıştırdığını gösterir. Hangi tabloyu taradığını, kaç satır incelediğini ve index kullanıp kullanmadığını buradan anlıyoruz.

EXPLAIN SELECT * FROM siparisler WHERE musteri_id = 12345;

Bunu çalıştırdığınızda şu alanlara dikkat edin:

  • type: ALL görürseniz tam tablo taraması yapılıyor demektir, bu kötü haber
  • rows: Kaç satır incelendiğini gösterir, ne kadar yüksekse o kadar yavaş
  • key: Hangi indexin kullanıldığını gösterir, NULL ise hiç index yok
  • Extra: “Using filesort” veya “Using temporary” görürseniz ekstra maliyet var demektir

İndex Olmadan Performans Testi

Şimdi index olmadan bazı sorgular çalıştıralım ve ne kadar sürdüğünü ölçelim. profiling özelliğini açıyoruz:

SET profiling = 1;

-- Sorgu 1: Belirli bir musterinin siparislerini bul
SELECT * FROM siparisler WHERE musteri_id = 12345;

-- Sorgu 2: Belirli bir tarihe gore filtrele
SELECT * FROM siparisler WHERE siparis_tarihi BETWEEN '2023-01-01' AND '2023-06-30';

-- Sorgu 3: Sehir ve duruma gore filtrele
SELECT * FROM siparisler WHERE sehir = 'Istanbul' AND durum = 'kargolandi';

-- Sorgu 4: Kategoriye gore grupla ve say
SELECT kategori, COUNT(*) as adet, AVG(fiyat) as ort_fiyat
FROM siparisler
GROUP BY kategori;

-- Sonuclari goster
SHOW PROFILES;

Tipik sonuçlar 2 milyon satırda şöyle çıkar:

  • musteri_id ile arama: 3-8 saniye
  • tarih aralığı sorgusu: 5-12 saniye
  • sehir + durum filtresi: 4-9 saniye
  • GROUP BY sorgusu: 6-15 saniye

Bu süreler bir web uygulaması için tam bir felaket. Kullanıcılar 3 saniyeden uzun süren sayfalarda geri tuşuna basar.

EXPLAIN çıktısına bakarsak:

EXPLAIN SELECT * FROM siparisler WHERE musteri_id = 12345G

Çıktıda type: ALL ve rows: 2000000 göreceksiniz. Bu, veritabanının 2 milyon satırın tamamını tek tek taradığı anlamına gelir. Full table scan dediğimiz bu durum, performansın düşmanıdır.

İndex Ekleyip Farkı Görelim

Şimdi aynı sorgular için indexler ekleyelim ve dramatik farkı gözlemleyelim.

-- Tek sutun index
CREATE INDEX idx_musteri_id ON siparisler(musteri_id);

-- Tarih sutunu icin index
CREATE INDEX idx_siparis_tarihi ON siparisler(siparis_tarihi);

-- Composite (birlesik) index - sehir ve durum birlikte sorgulandiginda
CREATE INDEX idx_sehir_durum ON siparisler(sehir, durum);

-- Kategori icin index
CREATE INDEX idx_kategori ON siparisler(kategori);

Index oluşturma işlemi büyük tablolarda biraz zaman alır. Bu normal ve beklenen bir durum. Production ortamında bunu dikkatli planlamanız gerekir çünkü index oluşturulurken tablo kilitlenebilir (MariaDB’de online DDL seçeneklerine bakın).

Şimdi aynı sorguları tekrar çalıştıralım:

SET profiling = 1;

SELECT * FROM siparisler WHERE musteri_id = 12345;
SELECT * FROM siparisler WHERE siparis_tarihi BETWEEN '2023-01-01' AND '2023-06-30';
SELECT * FROM siparisler WHERE sehir = 'Istanbul' AND durum = 'kargolandi';
SELECT kategori, COUNT(*) as adet, AVG(fiyat) as ort_fiyat FROM siparisler GROUP BY kategori;

SHOW PROFILES;

Yeni sonuçlar:

  • musteri_id ile arama: 3-8 saniyeden 0.002-0.01 saniyeye indi
  • tarih aralığı sorgusu: 5-12 saniyeden 0.1-0.5 saniyeye indi
  • sehir + durum filtresi: 4-9 saniyeden 0.05-0.2 saniyeye indi
  • GROUP BY sorgusu: 6-15 saniyeden 0.3-1 saniyeye indi

Bu fark binlerce kat olabilir. EXPLAIN çıktısı da artık çok farklı görünür:

EXPLAIN SELECT * FROM siparisler WHERE musteri_id = 12345G
-- type: ref, rows: ~20 (tahmini eslesme sayisi), key: idx_musteri_id

Artık type: ref görüyoruz ve rows değeri 2 milyondan 20’ye düştü. Veritabanı artık sadece ilgili satırlara atlıyor.

Composite Index Kullanımının İncelikleri

Birleşik indexlerde önemli bir kural var: en soldaki sütun kuralı. İndex (sehir, durum) şeklinde oluşturulmuşsa, bu index şu senaryolarda çalışır:

  • WHERE sehir = 'Istanbul' – ÇALIŞIR
  • WHERE sehir = 'Istanbul' AND durum = 'kargolandi' – ÇALIŞIR (en iyi performans)
  • WHERE durum = 'kargolandi' – ÇALIŞMAZ (sadece sağ taraf kullanılamaz)

Bunu test edelim:

-- Bu sorgu idx_sehir_durum indexini KULLANIR
EXPLAIN SELECT * FROM siparisler WHERE sehir = 'Istanbul' AND durum = 'kargolandi'G

-- Bu sorgu idx_sehir_durum indexini KULLANAMAZ (sadece durum filtresi)
EXPLAIN SELECT * FROM siparisler WHERE durum = 'kargolandi'G

-- Bu sorgu kismali index kullanir (sadece sehir kismi)
EXPLAIN SELECT * FROM siparisler WHERE sehir = 'Istanbul'G

Bu yüzden composite index tasarlarken en çok kullanılan ve en seçici olan sütunu sola koymanız gerekir.

Gerçek Dünya Senaryosu: Raporlama Sorgusu

Bir e-ticaret sitesinde tipik bir yönetim paneli sorgusu şöyle olabilir:

-- Aylik sehir bazli gelir raporu - INDEX OLMADAN cok agir calisir
SELECT 
    sehir,
    DATE_FORMAT(siparis_tarihi, '%Y-%m') as ay,
    COUNT(*) as siparis_sayisi,
    SUM(fiyat) as toplam_gelir,
    AVG(fiyat) as ortalama_siparis
FROM siparisler
WHERE 
    durum != 'iptal edildi'
    AND siparis_tarihi >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY sehir, DATE_FORMAT(siparis_tarihi, '%Y-%m')
ORDER BY ay DESC, toplam_gelir DESC;

Bu sorgu için optimal index nedir? Birden fazla kolonu kapsayan bir index gerekli:

-- Covering index - sorgudaki tum sütunlari kapsar
CREATE INDEX idx_rapor_kapsamli ON siparisler(durum, siparis_tarihi, sehir, fiyat);

-- EXPLAIN ile kontrol et
EXPLAIN SELECT 
    sehir,
    DATE_FORMAT(siparis_tarihi, '%Y-%m') as ay,
    COUNT(*) as siparis_sayisi,
    SUM(fiyat) as toplam_gelir
FROM siparisler
WHERE 
    durum != 'iptal edildi'
    AND siparis_tarihi >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY sehir, DATE_FORMAT(siparis_tarihi, '%Y-%m')G

Burada Extra alanında Using index yazıyorsa harika bir haber demektir. Covering index kullanımı, veritabanının asıl tabloya hiç dokunmadan sadece index üzerinden veri okuduğu anlamına gelir. Bu en hızlı senaryo.

Index’in Dezavantajları ve Dikkat Edilmesi Gerekenler

Her şeye index koymak da çözüm değil. Bu önemli bir nokta.

  • Disk alanı: Her index disk üzerinde ek alan kaplar. 2 milyon satırlık bir tabloda her index yaklaşık 30-100 MB yer tutabilir
  • Yazma performansı: INSERT, UPDATE, DELETE işlemlerinde veritabanı hem tabloyu hem de tüm indexleri günceller. Çok fazla index varsa yazma işlemleri yavaşlar
  • Index bakımı: Zamanla indexler parçalanabilir (fragmentation), periyodik olarak OPTIMIZE TABLE çalıştırmak gerekebilir
  • Düşük kardinalite: Az farklı değer içeren sütunlar (örneğin sadece 5 farklı değer olan durum sütunu) için tek başına index o kadar etkili olmaz
-- Mevcut indexlerin boyutunu goster
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS boyut_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'performans_testi'
AND stat_name = 'size'
ORDER BY boyut_mb DESC;

-- Kullanilmayan indexleri tespit et (MariaDB 10.x)
SELECT * FROM information_schema.INDEX_STATISTICS
WHERE table_schema = 'performans_testi';

Slow Query Log ile Problematik Sorguları Bulma

Production ortamında hangi sorguların yavaş olduğunu bulmak için slow query log’u aktif etmek şart:

-- my.cnf veya my.ini dosyasina ekle
-- [mysqld] bolumune:
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = 1

-- Calisan sistemde aktif etmek icin:
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;

-- Durumu kontrol et
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Log dosyası oluştuktan sonra mysqldumpslow aracıyla analiz edin:

# En yavash 10 sorguyu listele
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Index kullanmayan sorguları bul
mysqldumpslow -s c /var/log/mysql/slow.log | head -50

Bu çıktı size hangi sorgulara önce bakmanız gerektiğini gösterir. Genellikle 2-3 kritik sorguya index eklemek performansın büyük kısmını iyileştirir.

Index Türleri ve Kullanım Alanları

MariaDB ve MySQL’de farklı index türleri var, her biri farklı senaryolar için optimize edilmiş:

  • B-Tree Index (varsayılan): Eşitlik, aralık ve LIKE sorguları için uygun. En yaygın kullanılan tür
  • HASH Index: Sadece eşitlik sorgularında çalışır, Memory engine ile kullanılır, aralık sorgularında işe yaramaz
  • FULLTEXT Index: Metin içinde arama için, LIKE ‘%kelime%’ yerine MATCH AGAINST kullanılır
  • SPATIAL Index: Coğrafi veri tipleri için (MyISAM ve InnoDB)
-- FULLTEXT index ornegi - urun_adi sutununda metin aramasi
CREATE FULLTEXT INDEX idx_fulltext_urun ON siparisler(urun_adi);

-- FULLTEXT aramasi - LIKE '%laptop%' yerine cok daha hizli
SELECT * FROM siparisler 
WHERE MATCH(urun_adi) AGAINST('laptop' IN BOOLEAN MODE)
LIMIT 20;

-- LIKE ile karsilastirma (fulltext index kullanamaz)
-- EXPLAIN SELECT * FROM siparisler WHERE urun_adi LIKE '%laptop%'G
-- Bu full table scan yapar!

Performans Testini Otomatize Etme

Bir bash scripti ile index öncesi ve sonrası performansı otomatik karşılaştırabiliriz:

#!/bin/bash
# index_test.sh - Index performans karsilastirma scripti

DB="performans_testi"
USER="root"
PASS="sifreniz"

echo "=== INDEX OLMADAN SORGULAR ==="
echo "musteri_id sorgusu:"
time mysql -u$USER -p$PASS $DB -e "SELECT COUNT(*) FROM siparisler WHERE musteri_id = 12345;" 2>/dev/null

echo "Tarih araligi sorgusu:"
time mysql -u$USER -p$PASS $DB -e "SELECT COUNT(*) FROM siparisler WHERE siparis_tarihi BETWEEN '2023-01-01' AND '2023-06-30';" 2>/dev/null

echo ""
echo "=== INDEX EKLENIYOR ==="
mysql -u$USER -p$PASS $DB -e "CREATE INDEX idx_test_musteri ON siparisler(musteri_id);" 2>/dev/null
mysql -u$USER -p$PASS $DB -e "CREATE INDEX idx_test_tarih ON siparisler(siparis_tarihi);" 2>/dev/null
echo "Indexler olusturuldu."

echo ""
echo "=== INDEX ILE SORGULAR ==="
echo "musteri_id sorgusu:"
time mysql -u$USER -p$PASS $DB -e "SELECT COUNT(*) FROM siparisler WHERE musteri_id = 12345;" 2>/dev/null

echo "Tarih araligi sorgusu:"
time mysql -u$USER -p$PASS $DB -e "SELECT COUNT(*) FROM siparisler WHERE siparis_tarihi BETWEEN '2023-01-01' AND '2023-06-30';" 2>/dev/null

echo ""
echo "=== TEMIZLIK ==="
mysql -u$USER -p$PASS $DB -e "DROP INDEX idx_test_musteri ON siparisler;" 2>/dev/null
mysql -u$USER -p$PASS $DB -e "DROP INDEX idx_test_tarih ON siparisler;" 2>/dev/null
echo "Test indexleri silindi."

Bu script’i çalıştırdığınızda terminal çıktısında gerçek zamanlı farkı göreceksiniz. Geliştirme ortamında yeni bir tablo veya sorgu eklendiğinde bu tür testleri alışkanlık haline getirmek çok faydalı.

Index Analizi ve Bakım

Mevcut bir veritabanında index durumunu düzenli kontrol etmek gerekir:

-- Tablodaki tum indexleri listele
SHOW INDEX FROM siparisler;

-- Index kullanim istatistikleri (MariaDB)
SELECT 
    index_name,
    rows_read,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM information_schema.INDEX_STATISTICS
WHERE table_schema = 'performans_testi'
AND table_name = 'siparisler';

-- Tabloyu optimize et (index defragmentation)
OPTIMIZE TABLE siparisler;

-- Index istatistiklerini guncelle (query planner icin)
ANALYZE TABLE siparisler;

ANALYZE TABLE komutu özellikle önemli. Query optimizer, hangi indexi kullanacağına istatistikler doğrultusunda karar verir. İstatistikler eskimiş olursa optimizer yanlış kararlar verebilir ve daha yavaş bir execution plan seçebilir.

Sonuç

Index kullanımı, veritabanı performansının temel taşı. Yaptığımız testlerde gördüğümüz gibi, aynı sorgu index olmadan 8 saniye, index ile 0.002 saniye sürebiliyor. Bu fark, 4000 katlık bir hız artışı demek.

Önemli çıkarımları özetleyelim:

  • EXPLAIN her sorgunun arkadaşı: Bir sorgu yazarken mutlaka EXPLAIN ile nasıl çalıştığını kontrol edin
  • Full table scan’den kaçının: type: ALL görürseniz o sütuna index eklemeyi düşünün
  • Composite index sıralaması önemli: En seçici ve en çok kullanılan sütunu sola koyun
  • Her şeye index koymayın: Yazma ağırlıklı tablolarda fazla index performansı düşürür
  • Slow query log açık tutun: Production’da index sorunu yaratan sorguları erken tespit edin
  • Düzenli bakım yapın: ANALYZE TABLE ve OPTIMIZE TABLE’ı periyodik çalıştırın
  • Covering index hedefleyin: Sorgunun ihtiyaç duyduğu tüm sütunları kapsayan index en iyi performansı verir

Bir sysadmin olarak veritabanı sorunları genellikle gece 2’de patlak verir. O saatte slow query log’a bakıp hızlıca doğru index’i ekleyebilmek paha biçilemez bir beceri. Bu nedenle EXPLAIN komutunu ve index mantığını içselleştirmenizi tavsiye ederim.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir