EXPLAIN ile Sorgu Performansını Analiz Etme

Bir veritabanı yöneticisinin en sık karşılaştığı sorunların başında yavaş çalışan sorgular gelir. Uygulama birden yavaşlar, kullanıcılar şikayet etmeye başlar, log dosyaları slow query kayıtlarıyla dolmaya başlar. İşte tam bu noktada EXPLAIN komutu devreye girer. MySQL ve MariaDB’nin bize sunduğu bu güçlü araç, bir sorgunun veritabanı motoru tarafından nasıl yürütüldüğünü adım adım gösterir. Hangi indekslerin kullanıldığını, kaç satırın tarandığını, birleştirme işlemlerinin nasıl gerçekleştiğini öğrenmek için tek yapmanız gereken sorgunuzun önüne EXPLAIN yazmak.

EXPLAIN Komutunun Temel Mantığı

EXPLAIN, sorgunuzu çalıştırmaz. Sadece sorgu planını, yani veritabanı motorunun o sorguyu çalıştırmak için kullanacağı stratejiyi gösterir. Bu sayede sorguyu gerçekten çalıştırmadan önce performans sorunlarını tespit edebilirsiniz. Özellikle milyonlarca satır içeren tablolarda bu yaklaşım hayat kurtarır.

Temel kullanımı son derece basittir:

EXPLAIN SELECT * FROM kullanicilar WHERE email = '[email protected]';

Bu komut çalıştırıldığında MySQL/MariaDB size birden fazla sütun içeren bir çıktı döner. Bu sütunların her biri sorgunun farklı bir yönünü açıklar. Şimdi bu sütunları tek tek ele alalım.

EXPLAIN Çıktısındaki Temel Sütunlar

• id: Sorgu içindeki SELECT’in sırası. Alt sorgularda birden fazla id değeri görebilirsiniz
• select_type: SELECT işleminin türü. SIMPLE, PRIMARY, SUBQUERY, DERIVED gibi değerler alır
• table: İşlem yapılan tablo adı
• type: Birleştirme türü. Performans açısından en kritik sütundur
• possible_keys: Kullanılabilecek indeksler
• key: Gerçekte kullanılan indeks
• key_len: Kullanılan indeksin uzunluğu
• ref: İndeksle karşılaştırılan sütun veya sabit değer
• rows: Sonuca ulaşmak için taranması gereken tahmini satır sayısı
• filtered: WHERE koşulu tarafından filtrelenecek satır yüzdesi
• Extra: Ek bilgiler. Using index, Using filesort, Using temporary gibi önemli bilgiler burada çıkar

type Sütununun Önemi

type sütunu, performans analizi açısından en kritik değerdir. En iyiden en kötüye doğru sıralarsak:

• system: Tabloda yalnızca bir satır var. En hızlı durum
• const: Birincil anahtar veya benzersiz indeksle tek satır eşleşmesi
• eq_ref: Birleştirme işlemlerinde her satır için tek bir eşleşme
• ref: İndeks kullanılıyor ancak birden fazla satır eşleşebilir
• range: İndeks üzerinde aralık taraması yapılıyor
• index: Tüm indeks taranıyor, tablo taranmıyor
• ALL: Tam tablo taraması. Kötü performansın işareti

ALL değerini gördüğünüzde alarm zilleri çalmalı. Bu, veritabanının sorguyu yanıtlamak için tablodaki her satırı okuduğu anlamına gelir.

Gerçek Dünya Senaryo 1: İndeks Eksikliğini Tespit Etmek

Diyelim ki bir e-ticaret uygulamasında sipariş sorgularınız yavaşlamaya başladı. Siparişler tablosunda müşteri ID’sine göre arama yapıyorsunuz:

EXPLAIN SELECT * FROM siparisler 
WHERE musteri_id = 1234 
ORDER BY olusturma_tarihi DESC;

Çıktıda type: ALL ve rows: 850000 gibi değerler görüyorsanız, tablonun tamamı taranıyor demektir. key: NULL değeri de herhangi bir indeks kullanılmadığını doğrular. Bu noktada yapmanız gereken indeks oluşturmak:

-- Önce mevcut indeksleri kontrol edelim
SHOW INDEX FROM siparisler;

-- Eksik indeksi ekleyelim
CREATE INDEX idx_musteri_tarih ON siparisler (musteri_id, olusturma_tarihi);

-- Şimdi tekrar EXPLAIN çalıştıralım
EXPLAIN SELECT * FROM siparisler 
WHERE musteri_id = 1234 
ORDER BY olusturma_tarihi DESC;

İndeksi ekledikten sonra type: ref, key: idx_musteri_tarih ve rows: 47 gibi değerler görürsünüz. Aynı sorgu artık 850.000 satır yerine yalnızca 47 satır tarıyor. Bu fark üretim ortamında saniyeler ile milisaniyeler arasındaki fark anlamına gelir.

Gerçek Dünya Senaryo 2: JOIN Sorgularını Analiz Etmek

Birden fazla tablo içeren sorgular analiz edilirken EXPLAIN çok daha değerli hale gelir. Her tablo için ayrı bir satır döner:

EXPLAIN SELECT 
    u.ad,
    u.soyad,
    s.siparis_no,
    s.toplam_tutar,
    u.email
FROM kullanicilar u
INNER JOIN siparisler s ON u.id = s.musteri_id
INNER JOIN siparis_detaylari sd ON s.id = sd.siparis_id
WHERE s.durum = 'tamamlandi'
AND s.olusturma_tarihi >= '2024-01-01';

Bu tür bir sorguda dikkat etmeniz gereken noktalar şunlardır. Her tablonun type değerine bakın. Büyük tablolardan birinde ALL görüyorsanız sorun oradadır. rows değerlerini çarpın. Üç tabloda sırasıyla 1000, 500 ve 200 satır taranıyorsa teorik olarak 100 milyon satır kombinasyonu işlenebilir. Bu da ciddi performans sorunlarına yol açar.

EXPLAIN EXTENDED ve EXPLAIN FORMAT=JSON

Standart EXPLAIN çıktısı bazen yeterli olmaz. Daha detaylı bilgi için iki ek seçenek mevcuttur.

EXPLAIN EXTENDED kullanıldıktan sonra SHOW WARNINGS komutuyla optimize edilmiş sorgu metnini görebilirsiniz:

EXPLAIN EXTENDED SELECT 
    u.ad, 
    COUNT(s.id) as siparis_sayisi
FROM kullanicilar u
LEFT JOIN siparisler s ON u.id = s.musteri_id
GROUP BY u.id;

SHOW WARNINGS;

SHOW WARNINGS çıktısında MySQL’in sorgunuzu nasıl yeniden yazdığını görürsünüz. Bu, optimize edicinin arka planda ne yaptığını anlamanın en iyi yoludur.

JSON formatı ise çok daha fazla detay sunar:

EXPLAIN FORMAT=JSON SELECT 
    p.urun_adi,
    k.kategori_adi,
    p.fiyat
FROM urunler p
INNER JOIN kategoriler k ON p.kategori_id = k.id
WHERE p.fiyat BETWEEN 100 AND 500
AND k.aktif = 1;

JSON formatında cost_info bölümü altında her adımın maliyetini görebilirsiniz. read_cost, eval_cost, prefix_cost gibi değerler optimize edicinin hangi yolu daha verimli bulduğunu anlamanızı sağlar.

Gerçek Dünya Senaryo 3: Alt Sorgu Sorunlarını Tespit Etmek

Alt sorgular (subquery) genellikle performans sorunlarının gizli kaynağıdır. Çoğu geliştirici farkında olmadan her satır için tekrar tekrar çalışan alt sorgular yazar:

EXPLAIN SELECT 
    u.ad,
    u.email,
    (SELECT COUNT(*) FROM siparisler WHERE musteri_id = u.id) as siparis_sayisi,
    (SELECT MAX(toplam_tutar) FROM siparisler WHERE musteri_id = u.id) as max_siparis
FROM kullanicilar u
WHERE u.aktif = 1;

Bu sorguda select_type: DEPENDENT SUBQUERY değerini görürsünüz. Bu, alt sorgunun dış sorgunun her satırı için ayrı ayrı çalıştırıldığı anlamına gelir. 10.000 aktif kullanıcınız varsa bu alt sorgu 20.000 kez çalışır.

Çözüm, alt sorguyu JOIN ile değiştirmektir:

EXPLAIN SELECT 
    u.ad,
    u.email,
    COUNT(s.id) as siparis_sayisi,
    MAX(s.toplam_tutar) as max_siparis
FROM kullanicilar u
LEFT JOIN siparisler s ON u.id = s.musteri_id
WHERE u.aktif = 1
GROUP BY u.id, u.ad, u.email;

Şimdi EXPLAIN çıktısında select_type: SIMPLE değerini görürsünüz. Tek bir tarama ile aynı sonuca ulaşılır.

Extra Sütunundaki Kritik Mesajlar

Extra sütunu sizi rahatsız etmesi gereken birkaç önemli mesaj içerebilir.

Using filesort: ORDER BY işlemi indeks üzerinden yapılamıyor, dosya sistemi üzerinde sıralama yapılıyor. Bu özellikle büyük veri setlerinde çok maliyetlidir. Çözüm genellikle ORDER BY sütunlarını içeren bileşik bir indeks oluşturmaktır.

Using temporary: Sorgu sonuçları bir geçici tabloya yazılıyor. GROUP BY veya DISTINCT işlemlerinde sıkça karşılaşılır. GROUP BY ile ORDER BY sütunları uyuşmadığında da bu mesaj çıkar.

Using index: Harika bir işaret. Sorgu yalnızca indeksi okuyarak sonuca ulaşıyor, asıl tabloya hiç dokunmuyor. Buna “covering index” denir.

Using where: WHERE koşulu tablo satırlarına uygulanıyor. Normal bir durum ama “Using index” ile birlikte görünmesi daha iyidir.

Bu durumları pratikte görmek için birkaç örnek:

-- Using filesort örneği
EXPLAIN SELECT id, ad, olusturma_tarihi 
FROM urunler 
WHERE kategori_id = 5 
ORDER BY fiyat DESC;

-- Bu sorguda kategori_id indeksi olsa bile fiyat sütununda 
-- filesort yaşanabilir. Çözüm:
CREATE INDEX idx_kategori_fiyat ON urunler (kategori_id, fiyat);

-- Şimdi tekrar kontrol edelim
EXPLAIN SELECT id, ad, olusturma_tarihi 
FROM urunler 
WHERE kategori_id = 5 
ORDER BY fiyat DESC;

Gerçek Dünya Senaryo 4: Slow Query Log ile EXPLAIN Entegrasyonu

Üretim ortamında hangi sorguların yavaş çalıştığını bulmak için slow query log’u etkinleştirip ardından bu sorguları EXPLAIN ile analiz etmek en etkili yaklaşımdır:

-- Slow query log ayarları
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- mysqldumpslow ile slow query log analizi
-- Bu komutu terminal'de çalıştırın:
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- En yavaş 10 sorguyu bulduktan sonra bunları EXPLAIN ile analiz edin
EXPLAIN SELECT DISTINCT 
    u.id,
    u.ad,
    u.email
FROM kullanicilar u
INNER JOIN siparisler s ON u.id = s.musteri_id
INNER JOIN urunler p ON s.urun_id = p.id
WHERE p.kategori_id IN (1, 2, 3)
AND s.durum != 'iptal'
AND u.kayit_tarihi >= DATE_SUB(NOW(), INTERVAL 1 YEAR);

Bu sorguyu analiz ederken şu noktalara odaklanın. Her tablonun rows değerini not alın. type: ALL olan tablolar var mı? Extra sütununda Using temporary veya Using filesort var mı? key sütunu NULL olan tablolar var mı?

ANALYZE TABLE ve EXPLAIN İlişkisi

EXPLAIN‘in doğru sonuçlar vermesi için istatistiklerin güncel olması şarttır. MySQL/MariaDB, tablo istatistiklerini kullanarak sorgu planını oluşturur. Tabloda çok fazla veri değişikliği olduysa istatistikler güncelliğini yitirmiş olabilir:

-- Tablo istatistiklerini güncelleyelim
ANALYZE TABLE kullanicilar;
ANALYZE TABLE siparisler;
ANALYZE TABLE urunler;

-- Şimdi EXPLAIN daha doğru tahminler yapabilir
EXPLAIN SELECT 
    u.ad,
    u.email,
    s.siparis_no,
    s.toplam_tutar
FROM kullanicilar u
INNER JOIN siparisler s ON u.id = s.musteri_id
WHERE s.toplam_tutar > 1000
ORDER BY s.olusturma_tarihi DESC
LIMIT 50;

ANALYZE TABLE özellikle toplu veri yüklemelerinden sonra mutlaka çalıştırılmalıdır. Aksi takdirde optimize edici yanlış kararlar verebilir ve performanslı bir sorgu bile kötü bir plan seçebilir.

Pratik EXPLAIN Analiz Süreci

Yıllar içinde edindiğim deneyimle bir sorguyu analiz ederken izlediğim süreci şu şekilde özetleyebilirim.

İlk adım olarak type sütunundaki ALL değerlerini belirleyin. Bunlar öncelikli müdahale noktalarınızdır. İkinci adımda rows değerlerini çarparak toplam işlem yükünü hesaplayın. Üçüncü adımda key: NULL olan tablolar için uygun indeksler oluşturun. Dördüncü adımda Extra sütunundaki Using filesort ve Using temporary mesajlarını giderin. Son olarak değişikliklerinizi yaptıktan sonra tekrar EXPLAIN çalıştırarak sonuçları karşılaştırın.

Bir önceki ve sonraki durumu karşılaştırmak için şu yaklaşımı kullanabilirsiniz:

-- Optimizasyon öncesi - Sorunlu sorgu
EXPLAIN SELECT 
    p.urun_adi,
    p.fiyat,
    p.stok_miktari,
    k.kategori_adi,
    m.marka_adi
FROM urunler p
LEFT JOIN kategoriler k ON p.kategori_id = k.id
LEFT JOIN markalar m ON p.marka_id = m.id
WHERE p.aktif = 1
AND p.stok_miktari > 0
AND k.aktif = 1
ORDER BY p.satis_adedi DESC
LIMIT 20;

-- Gerekli indeksleri ekleyelim
ALTER TABLE urunler ADD INDEX idx_aktif_stok_satis (aktif, stok_miktari, satis_adedi);
ALTER TABLE kategoriler ADD INDEX idx_aktif (aktif);

-- Optimizasyon sonrası - Aynı sorguyu tekrar analiz edelim
EXPLAIN SELECT 
    p.urun_adi,
    p.fiyat,
    p.stok_miktari,
    k.kategori_adi,
    m.marka_adi
FROM urunler p
LEFT JOIN kategoriler k ON p.kategori_id = k.id
LEFT JOIN markalar m ON p.marka_id = m.id
WHERE p.aktif = 1
AND p.stok_miktari > 0
AND k.aktif = 1
ORDER BY p.satis_adedi DESC
LIMIT 20;

MariaDB’ye Özgü: EXPLAIN ile Paralel Sorgu Analizi

MariaDB 10.5 ve üzeri sürümlerde EXPLAIN çıktısı bazı ek bilgiler içerebilir. Özellikle MariaDB’nin sorgu optimize edicisine has bazı özellikler vardır:

-- MariaDB'de EXPLAIN ANALYZE (10.9+)
-- Bu komut sorguyu gerçekten çalıştırır ve gerçek değerleri gösterir
EXPLAIN ANALYZE SELECT 
    DATE(olusturma_tarihi) as gun,
    COUNT(*) as siparis_sayisi,
    SUM(toplam_tutar) as gunluk_ciro
FROM siparisler
WHERE olusturma_tarihi >= '2024-01-01'
AND durum = 'tamamlandi'
GROUP BY DATE(olusturma_tarihi)
ORDER BY gun DESC;

EXPLAIN ANALYZE komutu, tahmini değerler yerine gerçek değerleri gösterir. rows: 150 tahminine karşın gerçekte 45.000 satır tarandıysa bu büyük bir uyarı işaretidir. Optimize edicinin tablo istatistiklerini güncellemesi gerektiğini gösterir.

Yaygın Hatalar ve Çözümleri

Sysadmin olarak en sık karşılaştığım EXPLAIN analiz hatalarını şu şekilde sıralayabilirim.

Fonksiyon içinde sütun kullanmak: WHERE koşulunda sütunlar üzerinde fonksiyon kullanmak indeksi devre dışı bırakır. WHERE YEAR(olusturma_tarihi) = 2024 yerine WHERE olusturma_tarihi >= '2024-01-01' AND olusturma_tarihi < '2025-01-01' kullanın.

Örtük tip dönüşümü: WHERE musteri_id = '1234' gibi string-integer karşılaştırmaları indeksi etkisiz kılar. Tip uyumuna dikkat edin.

Wildcard başında yüzde işareti: WHERE ad LIKE '%ahmet' ifadesi indeksi kullanamazken WHERE ad LIKE 'ahmet%' indeksi kullanabilir. Bu küçük fark büyük performans etkisi yaratır.

Çok fazla indeks oluşturmak: Her sorgu için indeks oluşturmak yazma performansını düşürür. EXPLAIN analizleriyle gerçekten ihtiyaç duyulan indeksleri belirleyin.

Sonuç

EXPLAIN komutu, veritabanı performans sorunlarını çözmenin temel taşıdır. Yavaş bir sorguyla karşılaştığınızda önce EXPLAIN çalıştırma alışkanlığı edinmek, sizi gereksiz tahminlerden ve kör çözüm denemelerinden kurtarır. type sütununu takip edin, rows değerlerini değerlendirin, Extra sütunundaki uyarıları dikkate alın.

Üretim ortamında slow query log aktif olsun, düzenli olarak ANALYZE TABLE çalıştırın ve önemli sorgu değişikliklerinden önce mutlaka EXPLAIN analizi yapın. Bu üç alışkanlık, veritabanı katmanında yaşanacak pek çok performans krizini başlamadan önce engeller. Veritabanı optimizasyonu bir seferlik iş değil, sürekli bir süreçtir. EXPLAIN bu sürecin vazgeçilmez yoldaşınızdır.