MySQL Query Optimizasyonu: Index, Explain ve Slow Query Log

Bir Laravel projesinde "her sey calisiyordu" diyerek production'a cikmistim. Veriler 500 satiri gecene kadar da gercekten calisiyordu. Sonra blog listeleme sayfasi 200ms'den 2 saniyeye firlamaya basladi. O gun ogrendim ki, sorgu yazmak ile iyi sorgu yazmak arasinda daglar kadar fark var. Bu yazida MySQL query optimizasyonunda ogrendigim her seyi, pratik orneklerle paylasiyorum.

N+1 Query Problemi: Sessiz Katil

Laravel'de en sik karsilastigim performans sorunu N+1 query problemi. Sorunu anlamamak icin once nasil ortaya ciktigina bakalim.

Diyelim blog yazilarini listelemek istiyorsunuz:

// KOTU: N+1 problemi
$posts = Post::all();

foreach ($posts as $post) {
    echo $post->author->name; // Her post icin ayri sorgu!
}

Bu kod 100 post icin tam 101 sorgu atar. Biri SELECT * FROM posts, digerleri her post icin SELECT * FROM users WHERE id = ?. Veritabani sunucunuz bu yukun altinda ezilir.

Cozum Eloquent eager loading:

// IYI: Eager loading ile 2 sorgu
$posts = Post::with('author')->get();

// DAHA IYI: Sadece gereken alanlar
$posts = Post::with('author:id,name,avatar')
    ->select('id', 'title', 'slug', 'author_id', 'created_at')
    ->latest()
    ->paginate(20);

Eager loading ile 101 sorgu 2 sorguya duser. Projelerimde bu farki ilk fark ettigimde Laravel Debugbar kurdum ve her sayfadaki sorgu sayisini takip etmeye basladim. Tavsiyem: barryvdh/laravel-debugbar paketini development ortaminizda mutlaka kullanin.

// Nested eager loading
$posts = Post::with([
    'author:id,name',
    'categories:id,name,slug',
    'comments' => function ($query) {
        $query->latest()->limit(5);
    },
    'comments.user:id,name'
])->paginate(20);

N+1 sorunlarini otomatik yakalamak icin beyondcode/laravel-query-detector paketini de kurabilirsiniz. Development ortaminda N+1 tespit edince uyari verir.

MySQL EXPLAIN Komutu: Sorgularinizi Rontgen Cekin

EXPLAIN, MySQL'in bir sorguyu nasil calistiracagini gosteren en degerli aractir. Kullanimi basit:

EXPLAIN SELECT p.*, u.name
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.status = 'published'
ORDER BY p.created_at DESC
LIMIT 20;

EXPLAIN ciktisinda dikkat etmeniz gereken sutunlar:

type: Baglanti tipi. ALL goruyorsaniz full table scan yapiliyor demektir, bu felaket. Idealden kotuge: const > eq_ref > ref > range > index > ALL
possible_keys: MySQL'in kullanabilecegi index'ler
key: Gercekte kullanilan index
rows: Taranan satir sayisi tahmini. Ne kadar dusukse o kadar iyi
Extra: Using filesort veya Using temporary goruyorsaniz optimizasyon gerekli

EXPLAIN ANALYZE SELECT * FROM posts
WHERE status = 'published' AND category_id = 5
ORDER BY created_at DESC;

EXPLAIN ANALYZE (MySQL 8.0+) ise gercek calisma surelerini gosterir. Tahmini degil, gercek veri elde edersiniz.

Index Turleri ve Ne Zaman Kullanilir

Index'ler veritabaninin kitap sonundaki dizin gibidir. Dogru index olmadan MySQL tum tabloyu okumak zorunda kalir.

Primary Key

Her tabloda olmali. Laravel migration'larda $table->id() ile otomatik gelir.

Unique Index

Tekrar etmemesi gereken alanlar icin:

Schema::table('users', function (Blueprint $table) {
    $table->unique('email');
    $table->unique(['provider', 'provider_id']); // Composite unique
});

Composite (Bilesik) Index

Birden fazla kolonu kapsayan index. Burada siralama kritik onem tasir:

// Migration
Schema::table('posts', function (Blueprint $table) {
    $table->index(['status', 'category_id', 'created_at']);
});

MySQL composite index'lerde "soldan saga" kuralini uygular. Yani ['status', 'category_id', 'created_at'] indexi su sorgulari kapsar:

-- Bu calismaz (index kullanilmaz, ilk kolon eksik)
WHERE category_id = 5

-- Bu calisir (ilk kolon var)
WHERE status = 'published'

-- Bu calisir (ilk iki kolon var)
WHERE status = 'published' AND category_id = 5

-- Bu calisir (uc kolon da var, ORDER BY dahil)
WHERE status = 'published' AND category_id = 5 ORDER BY created_at DESC

Altin kural: Esitlik kosullarini once, aralik kosullarini (>, <, BETWEEN, ORDER BY) sona koyun. Bu kurali ogrendikten sonra projelerimde sorgu surelerini dramatik sekilde dusurdum.

Fulltext Index

Metin arama icin:

Schema::table('posts', function (Blueprint $table) {
    $table->fullText(['title', 'content']);
});

// Eloquent ile fulltext arama
$posts = Post::whereFullText(['title', 'content'], 'laravel deploy')
    ->get();

Slow Query Log: Sorunlu Sorgulari Yakalama

MySQL'in slow query log'u, belirlediginiz sureden uzun suren sorgulari kaydeder. Production ortaminda bunu mutlaka aktif edin.

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Servisi yeniden baslatin:

sudo systemctl restart mysql

Log dosyasini analiz etmek icin mysqldumpslow araci kullanilabilir:

# En yavas 10 sorguyu goster
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# En cok tekrar eden 10 sorguyu goster
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

Projelerimde slow query log'u aktif ettikten sonra fark ettigim bir sey: bazi sorgular tek basina hizli ama saniyede yuzlerce kez calisinca toplam yuku artiriyor. Bu tur sorgulari cache ile cozdugum oldu.

Query Cache Stratejileri

MySQL 8.0'da query cache kaldirildi ama uygulama seviyesinde cache stratejileri hala gecerli.

// Laravel cache ile query sonuclari
$posts = Cache::remember('published_posts_page_' . $page, 3600, function () use ($page) {
    return Post::with('author:id,name')
        ->where('status', 'published')
        ->latest()
        ->paginate(20);
});

// Cache invalidation
// Post kaydetme/guncelleme observer'inda
public function saved(Post $post)
{
    Cache::tags(['posts'])->flush();
}

Daha granular cache icin:

// Tek bir post icin cache
$post = Cache::remember("post_{$slug}", 7200, function () use ($slug) {
    return Post::with(['author', 'categories', 'tags'])
        ->where('slug', $slug)
        ->where('status', 'published')
        ->firstOrFail();
});

Pratik Ornek: Blog Listeleme Optimizasyonu

Projemde blog listeleme sorgusunu 200ms'den 15ms'e dusurdugum adimlari paylasiyorum.

Onceki durum (200ms+):

$posts = Post::all();
// View'da: $post->author->name, $post->categories, $post->comments_count

Adim 1 - Eager loading (80ms):

$posts = Post::with(['author', 'categories'])->withCount('comments')->get();

Adim 2 - Select ve paginate (40ms):

$posts = Post::select('id', 'title', 'slug', 'excerpt', 'featured_image', 'author_id', 'created_at')
    ->with(['author:id,name', 'categories:id,name,slug'])
    ->withCount('comments')
    ->where('status', 'published')
    ->latest()
    ->paginate(20);

Adim 3 - Composite index (15ms):

// Migration
Schema::table('posts', function (Blueprint $table) {
    $table->index(['status', 'created_at']);
});

Sonuc: 101 sorgu yerine 3 sorgu, full table scan yerine index kullanimi, 200ms yerine 15ms. Kullanici deneyimi tamamen degisti.

Ek Ipuclari

SELECT * kullanmayin. Sadece ihtiyaciniz olan kolonlari secin. Ozellikle TEXT veya BLOB kolonlari varsa bu fark cok buyuk olur.

COUNT sorgularini optimize edin:

// KOTU: Tum kayitlari cekip sayma
$count = Post::where('status', 'published')->get()->count();

// IYI: Veritabaninda say
$count = Post::where('status', 'published')->count();

Chunk kullanin buyuk islemler icin:

Post::where('status', 'draft')
    ->chunkById(500, function ($posts) {
        foreach ($posts as $post) {
            // Islem yap
        }
    });

Sonuc

MySQL query optimizasyonu bir kerelik is degil, surekli takip gerektiren bir surec. Projelerinizde Debugbar ile sorgu sayisini izleyin, EXPLAIN ile sorgulari analiz edin, slow query log ile sorunlari yakalayip index ve cache stratejileri ile cozun. Bu aliskanliklar, "calisiyor" ile "iyi calisiyor" arasindaki farki olusturuyor. Benim icin bu yolculuk o 200ms'lik blog sayfasiyla basladi. Sizin icin de bugun baslayabilir.