Sql & Veritabanı İle İndeks Optimizasyon Analizi Nasıl Yapılır?

Gereksinimler ve Ortam Hazırlığı

İndeks analizi yapabilmek için öncelikle veritabanınızda yeterli miktarda veriye ve sorgu istatistiklerini izleyebileceğiniz araçlara ihtiyacınız vardır. Test ortamınızda gerçekçi veri dağılımı (cardinality) olduğundan emin olun.

• PostgreSQL 16+ veya MySQL 8.4+ sürümü.
• Sorgu planlarını görüntülemek için EXPLAIN ANALYZE yetkisi.
• Veritabanı metriklerini izlemek için pg_stat_statements (PostgreSQL) veya Performance Schema (MySQL).
• İndeks kullanım oranlarını takip etmek için veritabanı yönetim aracı (DBeaver, pgAdmin veya terminal).

Adım 1: Sorgu Planlarını (Execution Plan) Okuma

İndeks optimizasyonunun ilk adımı, veritabanının bir sorguyu çalıştırırken hangi yolu izlediğini anlamaktır. EXPLAIN ANALYZE komutu, sorgunun gerçekte ne kadar süre harcadığını ve indeks kullanıp kullanmadığını gösterir.

EXPLAIN ANALYZE 
SELECT * FROM siparisler 
WHERE musteri_id = 4523;

Bu komutu çalıştırdığınızda, çıktı içerisinde Seq Scan (Sıralı Tarama) ifadesini görüyorsanız, veritabanı tüm tabloyu baştan sona okuyor demektir. Eğer Index Scan ifadesini görüyorsanız, indeks başarıyla kullanılıyor demektir. Seq Scan, büyük tablolarda performansın en büyük düşmanıdır.

Adım 2: İndeks İhtiyacını Belirleme ve Analiz

Hangi sütunların indekslenmesi gerektiğini belirlemek için WHERE, JOIN ve ORDER BY ifadelerinde sıkça kullanılan sütunları analiz etmelisiniz. Ancak her sütunu indekslemek, INSERT ve UPDATE işlemlerini yavaşlatır.

-- İndeks öncesi analiz: Hangi sütunlar çok sık sorgulanıyor?
SELECT relname, seq_scan, seq_tup_read, idx_scan 
FROM pg_stat_user_tables 
WHERE relname = 'siparisler';

Bu sorgu, tablonuzun ne kadarının indeks üzerinden, ne kadarının sıralı tarama ile okunduğunu gösterir. Eğer seq_scan değeri çok yüksekse, o tablo üzerinde indeks stratejinizi gözden geçirmelisiniz.

Adım 3: İndeks Türlerini Doğru Seçme

Veritabanı sistemleri farklı indeks türleri sunar. Yanlış indeks türü, sorgu performansını artırmayabilir. En yaygın kullanılan B-Tree indeksleri genel amaçlıdır, ancak metin aramaları için farklı çözümler gerekebilir.

Adım 4: Composite (Bileşik) İndeks Oluşturma

Birden fazla sütunu içeren sorgular için tek sütunlu indeksler yetersiz kalabilir. Bileşik indeksler, sorgu içerisindeki sütun sırasına göre optimize edilmelidir.

-- İki sütunlu bileşik indeks oluşturma
CREATE INDEX idx_musteri_tarih 
ON siparisler (musteri_id, siparis_tarihi);

Bileşik indekslerde en soldaki sütun (left-most prefix) kuralı geçerlidir. Sorgunuzda sadece siparis_tarihi kullanıyorsanız, bu indeks verimli çalışmayabilir. Sorgu yapınıza göre sütun sırasını belirlemek kritiktir.

Adım 5: İndekslerin Performansa Etkisini İzleme

İndeks eklemek, veritabanı boyutunu artırır ve yazma işlemlerine ek yük getirir. Kullanılmayan indeksleri tespit edip silmek, veritabanı sağlığı için önemlidir.

-- Kullanılmayan indeksleri bulma (PostgreSQL)
SELECT indexrelname 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
AND relname = 'siparisler';

Bu sorgu, oluşturduğunuz ancak hiçbir sorguda kullanılmayan indeksleri listeler. Gereksiz indeksleri kaldırmak, veritabanı yazma performansını doğrudan iyileştirir.

Adım 6: İndeks Optimizasyonunda Kritik Hatalar

İndekslerin doğru çalışmasını engelleyen bazı yaygın yazılım hataları vardır. Örneğin, indeksli bir sütun üzerinde fonksiyon kullanmak, indeksin devre dışı kalmasına neden olur.

-- İndeksi devre dışı bırakan hatalı sorgu
SELECT * FROM kullanicilar WHERE UPPER(email) = 'TEST@ORNEK.COM';

-- İndeksi kullanan doğru sorgu
SELECT * FROM kullanicilar WHERE email = 'test@ornek.com';

Kritik Uyarı: Üretim ortamında (production) indeks ekleme işlemleri, büyük tablolarda "lock" (kilitleme) sorunlarına yol açabilir. PostgreSQL'de CREATE INDEX CONCURRENTLY komutunu kullanarak tablonun kilitlenmesini önleyebilirsiniz.

Ayrıca, SQL Injection risklerine karşı her zaman parametreli sorgular kullanın. İndeks optimizasyonu yaparken sorgu yapınızı değiştirmek zorunda kalırsanız, güvenlik standartlarınızdan ödün vermeyin.

Sorumluluk Reddi: Bu rehberdeki kod örnekleri genel eğitim amaçlıdır. Veritabanı üzerinde büyük değişiklikler yapmadan önce mutlaka yedeğinizi alın ve değişiklikleri öncelikle geliştirme (staging) ortamında test edin.

Sıkça Sorulan Sorular

Bir tabloda kaç tane indeks olmalı?

Bunun kesin bir sayısı yoktur ancak genel kural, sadece en sık kullanılan sorguları destekleyecek kadar indeks bulundurmaktır. Çok fazla indeks, özellikle yüksek trafikli yazma işlemlerinde (INSERT/UPDATE) ciddi performans kaybına neden olur.

İndeksler veritabanı boyutunu ne kadar artırır?

İndeksler, verinin bir kopyasını farklı bir yapıda sakladığı için disk alanını artırır. Tablo yapınıza ve veri tipine bağlı olarak %10 ile %50 arasında bir disk kullanım artışı bekleyebilirsiniz.

B-Tree indeksi her zaman en iyisi midir?

B-Tree, çoğu durum için standarttır. Ancak JSONB verilerinde arama yapıyorsanız GIN indeksleri, coğrafi verilerde ise GiST indeksleri çok daha performanslı sonuçlar verir.

Sıralı tarama (Seq Scan) her zaman kötü müdür?

Hayır. Eğer tablo çok küçükse (birkaç yüz satır), veritabanı indekse gitmek yerine tüm tabloyu okumayı tercih eder çünkü bu daha hızlıdır. İndeks optimizasyonu büyük veri setleri için gereklidir.

İndeksler veritabanı güvenliğini etkiler mi?

İndeksler doğrudan güvenlik açığı oluşturmaz, ancak veritabanı performansını düşüren (örneğin indekslenmemiş sütunlarda yapılan karmaşık aramalar) sorgular, sistem kaynaklarını tüketerek "Denial of Service" (DoS) benzeri bir etki yaratabilir.

İleri Seviye İndeks Stratejileri: Kapsamlı İndeksleme (Covering Indexes)

Performansı en üst düzeye çıkarmanın en etkili yollarından biri, "Covering Index" (Kapsayıcı İndeks) stratejisini kullanmaktır. Bir sorgu, ihtiyaç duyduğu tüm veriyi sadece indeksin içinden okuyabiliyorsa, veritabanı motoru ana tabloya (Heap veya Clustered Index) erişmek zorunda kalmaz. Bu durum, "Index Only Scan" olarak adlandırılır ve disk G/Ç (I/O) maliyetini dramatik şekilde düşürür.

Aşağıdaki örnekte, sadece kullanıcı adı ve e-posta adresine ihtiyaç duyduğumuz bir senaryoda, bu iki sütunu içeren bir bileşik indeksin nasıl bir avantaj sağladığını görebiliriz:

-- İndeks oluşturma
CREATE INDEX idx_user_covering ON users (username, email);

-- Sorgu çalıştırıldığında veritabanı tabloya gitmeden indeksi okur
EXPLAIN ANALYZE 
SELECT username, email 
FROM users 
WHERE username = 'ahmet_yilmaz';

Bu yöntem, özellikle çok büyük tablolarda "Bookmark Lookup" veya "RID Lookup" maliyetini sıfıra indirdiği için yüksek trafikli sistemlerde kritik bir optimizasyon tekniğidir.

İndeks Bakımı ve Parçalanma (Fragmentation) Analizi

Veritabanı üzerinde sürekli yapılan INSERT, UPDATE ve DELETE işlemleri, zamanla indekslerin fiziksel yapısında parçalanmaya (fragmentation) neden olur. Parçalanmış bir indeks, veritabanı motorunun daha fazla sayfa okumasına ve dolayısıyla performans kaybına yol açar. İndekslerin sağlık durumunu düzenli aralıklarla kontrol etmek ve gerekirse yeniden oluşturmak (rebuild) gerekir.

PostgreSQL üzerinde indeks parçalanma oranını kontrol etmek için aşağıdaki sorguyu kullanabilirsiniz:

SELECT 
    relname AS table_name, 
    indexrelname AS index_name, 
    idx_scan, 
    idx_tup_read, 
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan > 0;

-- İndeksi yeniden oluşturarak parçalanmayı giderme
REINDEX INDEX idx_user_covering;

İpucu: İndeksleri yeniden oluşturmak (REINDEX) veritabanı üzerinde kilitlenmeye (lock) neden olabilir. Bu nedenle, yüksek trafikli üretim ortamlarında REINDEX CONCURRENTLY komutunu kullanarak, tabloyu kilitlemeden indeks optimizasyonu yapmanız önerilir. Bu işlem, veritabanı kaynaklarını daha yoğun kullansa da uygulamanızın kesintisiz çalışmasını sağlar.

İndeksleme Stratejisinde Deployment Öncesi Testler

Yeni bir indeks eklemeden önce mutlaka "Staging" ortamında yük testi yapmalısınız. İndeksler okuma sorgularını hızlandırırken, yazma (INSERT/UPDATE) işlemlerini yavaşlatır. İndeks maliyetini hesaplamak için şu adımları izleyin:

• Yazma Maliyeti: Tablonun günlük aldığı toplam yazma operasyonunu ölçün.
• İndeks Boyutu: pg_relation_size fonksiyonu ile indeksin diskte kapladığı alanı kontrol edin.
• Kullanım Sıklığı: pg_stat_user_indexes tablosunda idx_scan değeri artmayan indeksleri tespit edin ve silin.

Unutmayın, "en iyi indeks" hiç oluşturulmamış olan değil, sorgu planlarını optimize eden ve yazma performansını kabul edilebilir sınırlar içinde tutan indekstir.

Sonuç

Sql & Veritabanı ile indeks optimizasyon analizi, bir defalık bir işlem değil, sürekli bir iyileştirme sürecidir. Uygulamanız büyüdükçe sorgu kalıplarınız değişecek ve indeks stratejilerinizin de buna göre güncellenmesi gerekecektir. EXPLAIN ANALYZE ile sorgu planlarını izlemek, kullanılmayan indeksleri temizlemek ve doğru indeks türlerini seçmek, veritabanı performansınızı optimize etmenin temelidir. Bir sonraki adım olarak, veritabanı "slow query log" (yavaş sorgu günlüğü) takibini otomatize ederek, performans sorunlarını oluşmadan yakalamayı deneyebilirsiniz.