MySQL’de Index Eksikliği Kaynaklı Performans Sorunlarını Bulma ve Çözme

Gecen hafta bir müşterinin veritabanı sunucusuna baktım, CPU %90’ın üzerinde seyrediyor, uygulama yavaş, kullanıcılar şikayet ediyor. Sorunu bulmak için log dosyalarına daldım ve klasik bir tabloyla karşılaştım: eksik index. Bu tür sorunlar sinsi olur çünkü uygulama “çalışıyor”, sadece yavaş çalışıyor. Ve yavaşlık zamanla o kadar normalleşiyor ki kimse fark etmiyor ta ki sistem çöküp de “neden bu kadar yavaştı?” sorusu sorulana kadar.

Bu yazıda MySQL’de index eksikliği kaynaklı performans sorunlarını nasıl tespit edeceğinizi, hangi araçları kullanacağınızı ve bulduğunuz sorunları nasıl düzelteceğinizi anlatacağım. Sadece teorik değil, gerçek dünyada karşılaştığım senaryolar üzerinden gideceğiz.

Index Eksikliği Neden Bu Kadar Kritik?

MySQL bir sorgu çalıştırdığında, istenen veriye ulaşmak için iki temel yol izleyebilir: index kullanmak ya da tablonun tamamını taramak (full table scan). Küçük tablolarda bu fark neredeyse hissedilmez. Ama 10 milyon satırlık bir tabloda index olmadan yapılan bir sorgu, index ile yapılan aynı sorgunun binlerce katı daha uzun sürebilir.

Bunu şöyle düşünün: Bir kütüphanede belirli bir kitabı aramak istiyorsunuz. Kütüphanede katalog sistemi varsa (index), saniyeler içinde bulursunuz. Katalog yoksa tüm rafları tek tek gezmek zorundasınız. İşte MySQL’de de tam olarak bu oluyor.

Gerçek dünyada index eksikliğinin belirtileri şunlar:

• Sorgu süreleri beklenmedik şekilde uzun
• CPU kullanımının ani ve sürekli yükselmesi
• SHOW PROCESSLIST çıktısında sürekli “Sending data” veya “Copying to tmp table” görmek
• Disk I/O’sunun olağandışı yüksek seyretmesi
• Uygulama timeout hatalarının artması

Slow Query Log ile Başlayın

Her şeyden önce, yavaş sorguları yakalamanız lazım. MySQL’in slow query log özelliği bu iş için biçilmiş kaftan.

# Slow query log durumunu kontrol et
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'long_query_time';"

Eğer aktif değilse, /etc/mysql/mysql.conf.d/mysqld.cnf veya /etc/my.cnf dosyanıza şunları ekleyin:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

log_queries_not_using_indexes = 1 ayarı kritik. Bu sayede 1 saniyeden hızlı olsa bile index kullanmayan sorgular da log’a düşüyor. Bazı sorgular küçük tablolarda hızlı çalışır ama tablo büyüdükçe kabus olur. Bunu önceden yakalamak için bu ayar şart.

Değişiklikleri runtime’da da uygulayabilirsiniz:

mysql -u root -p -e "SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;"

mysqldumpslow ile Log Analizi

Slow query log’u doğrudan okumak zahmetli olabilir çünkü aynı sorgu yüzlerce kez tekrarlıyor. mysqldumpslow bu sorguları gruplayıp özetliyor.

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

# En çok çalışan 10 sorguyu göster
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# En fazla satır inceleyen 10 sorguyu göster
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

Çıktıda şuna benzer şeyler görürsünüz:

Count: 847  Time=12.34s (10449s)  Lock=0.00s (0s)  Rows=1.0 (847), app[app]@localhost
SELECT * FROM orders WHERE customer_email = 'S'

Bu çıktı çok şey anlatıyor. Aynı sorgu 847 kez çalışmış, her seferinde ortalama 12 saniye sürmüş ve toplamda 10449 saniye harcamış. customer_email kolonu muhtemelen index’siz.

EXPLAIN ile Sorgu Planını Okumak

Sorunlu sorguyu bulduktan sonra asıl iş başlıyor. EXPLAIN komutu MySQL’in o sorguyu nasıl çalıştıracağını gösterir.

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

Çıktıda dikkat etmeniz gereken kolonlar:

• type: Erişim türü. ALL görürseniz alarm zilleri çalmalı, full table scan yapılıyor demektir. ref, range, const gibi değerler iyidir.
• possible_keys: MySQL’in kullanabileceği indexler
• key: Gerçekten kullanılan index. NULL ise index kullanılmıyor.
• rows: MySQL’in taraması gereken tahmini satır sayısı
• Extra: Using filesort veya Using temporary görürseniz sorun var

-- Daha detaylı bilgi için EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT o.*, c.name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'pending' 
AND o.created_at > '2024-01-01';

Gerçek bir örnekle devam edelim. Diyelim ki şöyle bir sorgu var:

EXPLAIN SELECT * FROM user_sessions 
WHERE user_id = 12345 
AND created_at BETWEEN '2024-01-01' AND '2024-12-31'
AND is_active = 1;

Bu sorguda type: ALL ve rows: 8500000 görüyorsanız, 8.5 milyon satır tarıyor demektir. Muhtemelen user_id, created_at ve is_active kolonlarından hiçbirinde index yok ya da yanlış index var.

Performance Schema ile Derinlemesine Analiz

MySQL 5.6+ sürümlerde gelen Performance Schema, index kullanımını çok daha ayrıntılı incelemenizi sağlıyor. Özellikle sys schema’sı bu konuda hayat kurtarıcı.

-- Index hiç kullanılmayan tablolar
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;

-- Full table scan yapan sorgular
SELECT query, exec_count, total_latency, no_index_used_count, no_good_index_used_count
FROM sys.statements_with_full_table_scans
WHERE db NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY no_index_used_count DESC
LIMIT 20;

Bu sorgunun çıktısı direkt olarak hangi sorgularda ve kaç kez full table scan yapıldığını gösteriyor. Prodüksiyon ortamında bunu çalıştırdığınızda genellikle 2-3 tane “suçlu” sorgu hemen göze çarpıyor.

-- Tablo başına full scan istatistikleri
SELECT object_schema, object_name, 
       count_read, count_fetch,
       count_full_scans
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_full_scans > 0
AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY count_full_scans DESC;

Index Kullanımını Tablo Bazında Kontrol Etmek

Bir tablodaki mevcut indexleri ve bunların kullanım durumunu görmek için:

-- Tablodaki indexleri listele
SHOW INDEX FROM orders;

-- Daha okunabilir format için
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
    INDEX_TYPE,
    NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'orders'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE;

Buradan şunları anlayabilirsiniz: Hangi kolonlarda index var, composite index’lerin sıralaması nasıl, unique index mi değil mi.

Gerçek Dünya Senaryosu: E-Ticaret Uygulaması

Somut bir örnek üzerinden gidelim. Bir e-ticaret uygulamasında sipariş listesini çeken şu sorguyu ele alalım:

-- Problematik sorgu
SELECT o.id, o.total_amount, o.status, o.created_at,
       u.email, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status IN ('pending', 'processing')
AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY o.created_at DESC
LIMIT 50;

EXPLAIN çalıştırdığınızda şunu görüyorsunuz: orders tablosunda type: ALL, rows: 2300000. Tabloda 2.3 milyon sipariş var ve hepsini tarıyor.

Çözüm için önce neye index koyacağınızı düşünün. WHERE koşulundaki kolonlar: status ve created_at. ORDER BY için de created_at lazım. Composite index tam burada devreye giriyor:

-- Composite index oluştur
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- Ya da online DDL ile (prodüksiyonda tercih edilir)
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at), ALGORITHM=INPLACE, LOCK=NONE;

Index oluşturduktan sonra EXPLAIN tekrar çalıştırın. Bu sefer type: range, key: idx_status_created, rows: 1247 gibi bir sonuç görmeli siniz. 2.3 milyon satırdan 1247 satıra düşmesi sorguda ciddi bir hız artışı anlamına geliyor.

pt-query-digest ile Profesyonel Analiz

Percona Toolkit’in pt-query-digest aracı, slow query log analizini bir üst seviyeye taşıyor.

# Percona Toolkit kurulumu
sudo apt-get install percona-toolkit
# veya
sudo yum install percona-toolkit

# Temel analiz
pt-query-digest /var/log/mysql/mysql-slow.log

# Son 1 saatin loglarını analiz et
pt-query-digest --since=3600 /var/log/mysql/mysql-slow.log

# Belirli bir veritabanına ait sorguları filtrele
pt-query-digest --filter '$event->{db} eq "myapp_db"' /var/log/mysql/mysql-slow.log > rapor.txt

pt-query-digest çıktısı başlangıçta biraz korkutucu görünüyor ama alıştıktan sonra çok güçlü bir araç. Her sorgu grubu için toplam süre, ortalama süre, satır sayısı ve en önemlisi Query_time distribution grafiği gösteriyor. Bu dağılım grafiği size sorgunun tutarsız davranıp davranmadığını söylüyor, bazen hızlı bazen yavaş ise önbellek veya lock problemi olabilir.

Index Tasarımında Yapılan Yaygın Hatalar

Sadece index eklemek yetmiyor, doğru index eklemek gerekiyor. Yanlış index hem disk israfı hem de yazma performansı kaybı demek.

Düşük kardinaliteli kolonlara tek başına index koymak: Örneğin status kolonunda sadece 3-4 farklı değer varsa (active, inactive, pending), bu kolona tek başına index koymak genellikle işe yaramaz. MySQL optimizer tüm satırların büyük bir kısmını döndüreceğini anlayınca index’i es geçip full scan yapar. Çözüm: Kardinalitesi yüksek başka bir kolonla composite index oluşturmak.

Composite index sırasını yanlış belirlemek: (created_at, status) ile (status, created_at) aynı şey değil. Genel kural şu: Eşitlik koşullarında kullanılan kolonlar öne, aralık koşullarındaki kolonlar sona gelir. WHERE status = 'active' AND created_at > '2024-01-01' sorgusu için (status, created_at) doğru sıralama.

OR koşullarında index’lerin çalışmaması: Şu duruma dikkat:

-- Bu sorgu index kullanmayabilir
SELECT * FROM users WHERE email = '[email protected]' OR phone = '5551234567';

-- Daha iyi alternatif
SELECT * FROM users WHERE email = '[email protected]'
UNION
SELECT * FROM users WHERE phone = '5551234567';

Fonksiyon içindeki kolonlar: Kolona fonksiyon uygulandığında index kullanılmaz:

-- Index kullanılmaz
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Index kullanılır
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Implicit type conversion: Kolondaki tip ile karşılaştırma tipinin uyuşmaması:

-- user_id INT kolonsa bu index kullanmaz
SELECT * FROM orders WHERE user_id = '12345';

-- Doğrusu
SELECT * FROM orders WHERE user_id = 12345;

INFORMATION_SCHEMA ile Kapsamlı Tarama

Tüm veritabanında index olmayan önemli kolonları bulmak için şu sorguyu kullanabilirsiniz:

-- Foreign key kolonları ama index'i olmayan tablolar
SELECT 
    kcu.TABLE_SCHEMA,
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME,
    kcu.CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE kcu
LEFT JOIN information_schema.STATISTICS s 
    ON s.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND s.TABLE_NAME = kcu.TABLE_NAME
    AND s.COLUMN_NAME = kcu.COLUMN_NAME
WHERE kcu.REFERENCED_TABLE_NAME IS NOT NULL
AND s.INDEX_NAME IS NULL
AND kcu.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

Bu sorgu çok kritik bir şeyi buluyor: Foreign key olarak tanımlanmış ama üzerinde index olmayan kolonlar. JOIN operasyonlarında bu kolonlar kullanıldığında tam anlamıyla felaket olur.

Prodüksiyon Ortamında Index Ekleme

Büyük tablolarda index eklemek dikkat gerektiriyor. MySQL 5.6+ ile gelen Online DDL sayesinde tablo kilitlenmeden index eklenebiliyor ama yine de bazı noktalara dikkat etmek lazım:

# Mevcut tablo boyutunu kontrol et
mysql -u root -p -e "SELECT 
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS size_mb,
    TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp_db'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;"

Büyük tablolarda (100GB+) index oluşturma işlemi saatler alabilir. Bu süreçte:

• Replikasyon lag’ı artabilir
• Disk I/O yükselir
• Geçici olarak ilave disk alanı gerekir

Bu nedenle büyük tablolarda pt-online-schema-change kullanmayı düşünün:

# pt-osc ile index ekleme (okuma/yazma etkilenmez)
pt-online-schema-change 
    --alter "ADD INDEX idx_status_created (status, created_at)" 
    --execute 
    D=myapp_db,t=orders 
    --user=root 
    --password=yourpassword 
    --print 
    --progress=time,30

Monitoring ve Proaktif Yaklaşım

Sorunları bulduktan sonra düzeltmek yerine, sorunların oluşmasını önceden fark etmek daha iyidir. Bunun için basit bir monitoring scripti:

#!/bin/bash
# full_scan_check.sh - Cronjob olarak çalıştır

MYSQL_USER="monitor"
MYSQL_PASS="monitorpass"
THRESHOLD=1000
LOG_FILE="/var/log/mysql_index_check.log"

mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "
SELECT CONCAT(object_schema, '.', object_name) as table_name,
       count_full_scans
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_full_scans > $THRESHOLD
AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY count_full_scans DESC
LIMIT 10;" 2>/dev/null | while read line; do
    echo "$(date): Full scan uyarisi - $line" >> $LOG_FILE
    # Buraya email/slack notification ekleyebilirsiniz
done

Bu scripti crontab’a ekleyip her 15 dakikada bir çalıştırabilirsiniz. Full scan sayısı belirli bir eşiği geçtiğinde bildirim alırsınız.

Index Bakımı: ANALYZE ve OPTIMIZE

Index eklemek yetmiyor, mevcut indexlerin de düzenli bakımı lazım. Zaman içinde istatistikler eskiyor ve optimizer yanlış kararlar verebiliyor:

-- Tablo istatistiklerini güncelle
ANALYZE TABLE orders;

-- Tablo fragmentasyonunu kontrol et
SELECT TABLE_NAME, DATA_FREE, 
       ROUND(DATA_FREE/DATA_LENGTH*100, 2) AS fragmentation_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp_db'
AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;

Fragmentasyon %30’un üzerine çıktıysa OPTIMIZE TABLE düşünebilirsiniz ama bu işlem tablo kilidine yol açar, dikkatli kullanın. InnoDB tablolar için alternatif ALTER TABLE orders ENGINE=InnoDB; komutu online mode’da çalıştırılabilir.

Sonuç

Index eksikliği kaynaklı performans sorunları, sysadmin hayatının kaçınılmaz gerçeklerinden biri. Bu sorunların can sıkıcı tarafı, sistemin çökmemesi; sadece yavaş çalışması. Ve yavaşlık alışkanlık haline gelince kimse sorgulamıyor.

Özetlemek gerekirse yapmanız gereken adımlar şunlar: İlk olarak slow query log’u aktifleştirin ve log_queries_not_using_indexes = 1 ayarını kesinlikle açın. İkinci olarak mysqldumpslow veya pt-query-digest ile log analizini düzenli yapın. Üçüncü olarak şüpheli sorgularda EXPLAIN ile sorgu planını inceleyin, type: ALL görürseniz hemen araştırın. Dördüncü olarak sys.statements_with_full_table_scans ve sys.schema_unused_indexes sorgularını periyodik olarak çalıştırın. Son olarak index eklerken kardinaliteye, composite index sırasına ve tip uyumluluğuna dikkat edin.

Bu işin püf noktası şu: Sorun çıktıktan sonra koşturmak yerine proaktif monitoring kurmak. Bir kere doğru monitoring mekanizmalarını kurduğunuzda, index sorunları büyümeden ve kullanıcıları etkilemeden yakalayabiliyorsunuz. Ve müşteri “neden bu kadar yavaştı?” diye sormadan önce siz zaten düzeltmiş oluyorsunuz.