MySQL Sorgu Optimizasyonu ve EXPLAIN Kullanımı

Veritabanı yöneticiliğinin en can sıkıcı anlarından biri, gecenin üçünde bir uygulama geliştiricisinden “sorgu neden bu kadar yavaş çalışıyor?” mesajı almaktır. MySQL’de sorgu optimizasyonu, hem sanat hem de bilim gerektiren bir alan. Doğru araçları kullanmayı öğrendiğinizde ise bu tür gece yarısı krizleri büyük ölçüde azalır. Bu yazıda EXPLAIN komutunu, sorgu optimizasyonunun temel tekniklerini ve gerçek dünya senaryolarını ele alacağız.

MySQL Sorgu Optimizasyonu Neden Bu Kadar Önemli?

Bir sorgunun 0.001 saniye yerine 30 saniye çalışması, kullanıcı deneyimini tamamen mahveder. Ancak daha kritik bir nokta var: yavaş sorgular sadece kendilerini değil, tüm sistemi yavaşlatır. Bağlantı havuzunu tüketir, CPU ve I/O kaynaklarını boşa harcar, ve zamanla domino etkisiyle tüm uygulamayı çöküşe sürükler.

MySQL’de yavaş sorguları tespit etmek için önce slow query log aktif etmeniz gerekir. Bu, optimizasyonun başlangıç noktasıdır.

# MySQL yapılandırma dosyasını düzenle
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Şu satırları ekle veya düzenle:
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log_queries_not_using_indexes = 1

# MySQL'i yeniden başlat
sudo systemctl restart mysql

# Logları canlı izle
sudo tail -f /var/log/mysql/mysql-slow.log

Bu ayarın ardından 2 saniyeden uzun süren sorgular loglanmaya başlar. mysqldumpslow aracıyla bu logları analiz edebilirsiniz:

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

# En çok tekrar eden yavaş sorguları listele
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# Belirli bir veritabanı için filtrele
mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log | grep "mydb"

EXPLAIN Komutunu Anlamak

EXPLAIN, MySQL’in bir sorguyu nasıl çalıştıracağını gösteren en güçlü tanı aracıdır. Sorguyu gerçekten çalıştırmaz, sadece execution plan’i (yürütme planını) döndürür. Ancak çıktıyı okumayı bilmezseniz, size hiçbir şey söylemez.

# MySQL'e bağlan
mysql -u root -p mydb

# Basit bir EXPLAIN örneği
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;

# EXPLAIN FORMAT=JSON ile daha detaylı çıktı
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 1234;

# EXPLAIN ANALYZE ile gerçek çalışma sürelerini gör (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1234;

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

EXPLAIN çıktısında her satır bir tablo veya alt sorguyu temsil eder. Öne çıkan sütunları anlamak işin özüdür:

id: Sorgunun hangi adımda olduğunu gösterir. Alt sorgular farklı id değerleri alır.

select_type: Sorgunun türünü belirtir.

• SIMPLE: Alt sorgu veya UNION içermeyen basit sorgu
• PRIMARY: En dıştaki sorgu
• SUBQUERY: Alt sorgu
• DERIVED: FROM içinde kullanılan alt sorgu (geçici tablo)
• UNION: UNION içindeki ikinci veya sonraki sorgu

type: Bu sütun en kritik olanıdır. Erişim türünü gösterir ve performansı doğrudan yansıtır.

• system: Tabloda sadece bir satır var, en hızlı
• const: Birincil anahtar veya unique index üzerinden tek satır erişimi
• eq_ref: JOIN’lerde unique index kullanımı
• ref: Non-unique index üzerinden erişim
• range: Index üzerinde aralık taraması (BETWEEN, IN, >, < gibi)
• index: Tüm index taranıyor ama tablo değil
• ALL: Tüm tablo taranıyor, en kötüsü, kırmızı alarm

key: Gerçekte kullanılan index. NULL ise index kullanılmıyor demektir.

rows: MySQL’in kaç satır tarayacağını tahmin ettiği değer. Düşük olması iyidir.

Extra: Ek bilgiler. “Using filesort” veya “Using temporary” görünce dikkat edin.

Gerçek Dünya Senaryosu 1: Tam Tablo Taraması Sorunu

Bir e-ticaret platformu hayal edin. orders tablosunda 5 milyon kayıt var ve şu sorgu çalışıyor:

-- Sorunlu sorgu
EXPLAIN SELECT o.order_id, o.total, c.email 
FROM orders o 
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' 
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

EXPLAIN çıktısında type: ALL ve rows: 5000000 görürsünüz. Bu, MySQL’in 5 milyon satırı teker teker taradığı anlamına gelir. Çözüm:

# MySQL'e bağlan ve index ekle
mysql -u root -p mydb

# Bileşik index oluştur (composite index)
# status ve created_at birlikte kullanıldığı için
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

# Index oluşturulduktan sonra tekrar EXPLAIN çalıştır
EXPLAIN SELECT o.order_id, o.total, c.email 
FROM orders o 
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' 
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

# Artık type: range ve rows: ~2000 gibi değerler görmelisiniz

Burada kritik bir nokta var: bileşik indexlerde sütun sırası önemlidir. Yüksek seçicilikli sütun (az tekrar eden değer) öne gelmelidir. status sütununun az sayıda benzersiz değeri olduğu için aslında bu index kötü bir seçim. Gerçekte şu şekilde optimize etmek daha mantıklı:

-- Eğer status değerleri çok az ise (pending, completed, cancelled gibi)
-- created_at'i öne almak daha verimli olabilir
ALTER TABLE orders DROP INDEX idx_status_created;
ALTER TABLE orders ADD INDEX idx_created_status (created_at, status);

-- Veya partial index benzeri bir yaklaşım için
-- status sütununu enum yapıp ayrı tablo partitioning düşünebilirsiniz

Gerçek Dünya Senaryosu 2: N+1 Sorgu Problemi

Bu sorun genellikle uygulama katmanından kaynaklanır ama veritabanı tarafında da çözüm üretebilirsiniz. Bir blog platformunda her post için yazar bilgisini ayrı sorguyla çeken bir yapı düşünün:

-- Yanlış yaklaşım: Her post için ayrı sorgu
-- 100 post varsa 100 + 1 = 101 sorgu çalışır!
SELECT * FROM posts LIMIT 100;
-- Sonra her post için:
SELECT * FROM users WHERE id = ?;

-- Doğru yaklaşım: JOIN kullan
EXPLAIN SELECT 
    p.id, 
    p.title, 
    p.content,
    u.username,
    u.email,
    COUNT(c.id) AS comment_count
FROM posts p
INNER JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = 1
GROUP BY p.id, p.title, p.content, u.username, u.email
ORDER BY p.created_at DESC
LIMIT 100;

Bu birleşik sorgunun EXPLAIN çıktısını analiz etmek için şunu yapın:

# EXPLAIN ANALYZE kullan (MySQL 8.0+)
mysql -u root -p blog_db

EXPLAIN ANALYZE 
SELECT 
    p.id, 
    p.title, 
    u.username,
    COUNT(c.id) AS comment_count
FROM posts p
INNER JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = 1
GROUP BY p.id, p.title, u.username
ORDER BY p.created_at DESC
LIMIT 100;

EXPLAIN ANALYZE size hem tahmini hem de gerçek değerleri gösterir. “actual time=X..Y rows=Z” ifadesindeki Z değeri tahminle çok farklıysa, istatistikleriniz güncel değil demektir.

# Tablo istatistiklerini güncelle
ANALYZE TABLE posts, users, comments;

# Veya tüm tabloları güncelle
mysqlcheck -u root -p --analyze --all-databases

Index Stratejileri: Neyi, Neden, Nasıl?

Index oluşturmak bilim kadar sezgi de gerektirir. Her index, yazma performansını olumsuz etkiler çünkü INSERT, UPDATE ve DELETE işlemlerinde index de güncellenir.

-- Mevcut indexleri görüntüle
SHOW INDEX FROM orders;

-- Kullanılmayan indexleri bul (MySQL 8.0+)
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema = 'mydb'
ORDER BY object_schema, object_name;

-- Tekrar eden indexleri bul
SELECT 
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics
WHERE table_schema = 'mydb'
GROUP BY table_name, index_name
ORDER BY table_name;

Covering Index: Tabloya Hiç Dokunmamak

En güçlü optimizasyon tekniklerinden biri covering index kullanmaktır. Sorgunun ihtiyaç duyduğu tüm sütunlar index içindeyse, MySQL tabloya hiç gitmeden sadece index üzerinden sonucu döndürür.

-- Bu sorguda sadece order_id, status, total, created_at kullanılıyor
SELECT order_id, status, total, created_at 
FROM orders 
WHERE status = 'completed' 
AND created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Covering index: sorgunun tüm sütunlarını index'e dahil et
ALTER TABLE orders ADD INDEX idx_covering 
(status, created_at, order_id, total);

-- EXPLAIN çıktısında Extra sütununda "Using index" görünmeli
-- Bu, tabloya hiç gidilmediği anlamına gelir
EXPLAIN SELECT order_id, status, total, created_at 
FROM orders 
WHERE status = 'completed' 
AND created_at BETWEEN '2024-01-01' AND '2024-12-31';

EXPLAIN Çıktısında Kırmızı Bayraklar

Bazı değerler gördüğünüzde hemen harekete geçmeniz gerekir:

Using filesort: ORDER BY için disk veya bellek üzerinde ek sıralama yapılıyor. Index’i ORDER BY sütunlarını kapsayacak şekilde düzenleyin.

Using temporary: Geçici tablo oluşturuluyor, genellikle GROUP BY ve ORDER BY farklı sütunlarda olduğunda görülür.

Using join buffer (Block Nested Loop): JOIN koşulunda index yok. JOIN edilen sütunlara index ekleyin.

rows değerinin çok yüksek olması: Milyonlarca satır için 100.000+ rows değeri görüyorsanız, ciddi bir tablo taraması sorunu var.

# Performance Schema ile gerçek zamanlı sorgu izleme
mysql -u root -p

-- En çok kaynak tüketen sorguları bul
SELECT 
    digest_text,
    count_star,
    avg_timer_wait/1000000000 AS avg_seconds,
    sum_rows_examined/count_star AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'mydb'
ORDER BY avg_timer_wait DESC
LIMIT 10;

Sorgu Yeniden Yazma Teknikleri

Bazen index eklemek yeterli değildir; sorgunun kendisini yeniden yazmak gerekir.

OR Yerine UNION Kullanmak

-- Yavaş: OR ile sorgu, index kullanımını zorlaştırır
EXPLAIN SELECT * FROM products 
WHERE category_id = 5 OR category_id = 10;

-- Hızlı: UNION ALL ile her koşul ayrı index kullanır
EXPLAIN 
SELECT * FROM products WHERE category_id = 5
UNION ALL
SELECT * FROM products WHERE category_id = 10;

Alt Sorgu Yerine JOIN

-- Yavaş: Correlated subquery her satır için çalışır
EXPLAIN SELECT * FROM orders o
WHERE o.customer_id IN (
    SELECT id FROM customers WHERE country = 'TR'
);

-- Hızlı: JOIN çok daha verimli
EXPLAIN SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'TR';

EXISTS Kullanımı

-- Bazen COUNT(*) yerine EXISTS çok daha hızlı
-- Yavaş yaklaşım:
SELECT * FROM products p
WHERE (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) > 0;

-- Hızlı yaklaşım:
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

Buffer Pool ve Query Cache Ayarları

Sorgu optimizasyonu sadece SQL yazımıyla sınırlı değildir. MySQL yapılandırması da büyük etki yapar.

# MySQL bellek kullanımını kontrol et
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_%';"

# Buffer pool hit ratio hesapla (yüzde 95+ olmalı)
# Hit ratio = (pool_read_requests - pool_reads) / pool_read_requests * 100
mysql -u root -p << 'EOF'
SELECT 
    (1 - (
        (SELECT variable_value FROM performance_schema.global_status 
         WHERE variable_name = 'Innodb_buffer_pool_reads') /
        (SELECT variable_value FROM performance_schema.global_status 
         WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    )) * 100 AS hit_ratio_percent;
EOF

# my.cnf'de buffer pool ayarı (toplam RAM'in %70-80'i)
# innodb_buffer_pool_size = 8G
# innodb_buffer_pool_instances = 8

# Ayarları dinamik olarak değiştir (MySQL 5.7+)
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 8589934592;"

Pratik Bir Optimizasyon Workflow’u

Gerçek bir optimizasyon sürecini adım adım şöyle yürütürsünüz:

#!/bin/bash
# Yavaş sorgu analiz scripti

DB_USER="root"
DB_PASS="yourpassword"
DB_NAME="mydb"
SLOW_LOG="/var/log/mysql/mysql-slow.log"

echo "=== En Yavaş 5 Sorgu ==="
mysqldumpslow -s t -t 5 "$SLOW_LOG"

echo ""
echo "=== Index Kullanmayan Sorgular ==="
mysqldumpslow -s t -t 5 "$SLOW_LOG" | grep -i "no index"

echo ""
echo "=== Tablo Boyutları ve Index İstatistikleri ==="
mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" << 'EOF'
SELECT 
    table_name,
    ROUND(data_length/1024/1024, 2) AS data_mb,
    ROUND(index_length/1024/1024, 2) AS index_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC
LIMIT 10;
EOF

echo ""
echo "=== Uzun Süren Mevcut Sorgular ==="
mysql -u "$DB_USER" -p"$DB_PASS" -e "
SELECT id, user, host, db, command, time, state, LEFT(info, 100) as query
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;"

Sonuç

MySQL sorgu optimizasyonu, tek seferlik bir iş değil, sürekli yapılması gereken bir pratiktir. EXPLAIN komutunu günlük rutininizin parçası haline getirin. Yeni bir sorgu yazdığınızda veya mevcut bir sorgu yavaşladığında ilk refleksiniz EXPLAIN çalıştırmak olsun.

Öğrenmeniz gereken en önemli şeyler şunlar: type: ALL gördüğünüzde paniklemeden önce sistematik düşünün, hangi sütun filtreleniyor ve o sütunda index var mı? Extra sütunundaki “Using filesort” ve “Using temporary” ifadeleri performans düşmanlarıdır, bunları gördüğünüzde sorgu veya index yapısını gözden geçirin. Buffer pool hit ratio’yu düzenli izleyin, yüzde 95 altına düşerse bellek ayarlarına bakın.

Son olarak şunu unutmayın: bazen en iyi optimizasyon, o sorguyu hiç çalıştırmamaktır. Önbellek katmanı (Redis, Memcached), uygulama tarafında veri denormalizasyonu veya iş mantığının yeniden düzenlenmesi, veritabanı katmanında yapılan onlarca ayardan daha etkili olabilir. Ama bunların hiçbiri EXPLAIN’i öğrenmekten geçmez. Araçlarınızı iyi bilin, geri kalanı gelir.