Sql & Veritabanı İle İleri Düzey İndeks Optimizasyonu Nasıl Yapılır?

Gereksinimler ve Ön Hazırlık

Uygulamalı örnekleri takip edebilmek için sisteminizde PostgreSQL 16+ veya MySQL 8.4+ sürümlerinden birinin kurulu olması önerilir. İndeksleme stratejilerini analiz etmek için EXPLAIN ANALYZE komutunu kullanacağız. Bu araç, veritabanı motorunun sorguyu yürütürken hangi indeksleri kullandığını ve ne kadar maliyet çıkardığını görmenizi sağlar.

• PostgreSQL veya MySQL veritabanı sunucusu.
• Veritabanı yönetim aracı (DBeaver, pgAdmin veya MySQL Workbench).
• Test amaçlı en az 100.000 satırlık örnek veri seti.

Adım 1: İndekslerin Çalışma Mantığını Anlamak

İndeksler, veritabanındaki verilerin fiziksel yerini belirleyen birer "içindekiler tablosu" gibidir. İndeksleme yapılmadığında, veritabanı motoru "Full Table Scan" (tüm tabloyu tarama) yöntemini kullanır. Bu, milyonlarca satırlık tabloda kabul edilemez bir yavaşlığa neden olur. B-Tree (Balanced Tree) indeks yapısı, verileri ağaç yapısında tutarak arama karmaşıklığını O(log n) seviyesine indirir.

-- İndeks oluşturmadan önce sorgu planını inceleyelim
EXPLAIN ANALYZE SELECT * FROM siparisler WHERE musteri_id = 4521;

Yukarıdaki komut, sorgunun tabloyu baştan sona mı taradığını (Seq Scan) yoksa bir indeks mi kullandığını size raporlar. Eğer "Seq Scan" ibaresini görüyorsanız, ilgili sütuna indeks ekleme vaktiniz gelmiş demektir.

Adım 2: Çok Sütunlu (Composite) İndeks Optimizasyonu

İleri düzey indekslemede en yaygın hata, her sütuna ayrı ayrı indeks eklemektir. Eğer sorgularınızda genellikle birden fazla sütunu birlikte kullanıyorsanız (örneğin: WHERE kategori = 'elektronik' AND fiyat < 500), çok sütunlu bir indeks oluşturmalısınız. Burada en kritik nokta "sol önek kuralı"dır (left-prefix rule).

-- Çok sütunlu indeks oluşturma
CREATE INDEX idx_kategori_fiyat ON urunler (kategori, fiyat);

Bu indeks, hem sadece kategoriye göre yapılan sorgularda hem de kategori ve fiyatı birlikte içeren sorgularda çalışır. Ancak sadece fiyata göre yapılan sorgularda bu indeks kullanılmaz. İndeks sıralamasını, sorgularınızdaki filtreleme sıklığına göre belirlemelisiniz.

Adım 3: Covering Index (Kapsayan İndeks) Kullanımı

Covering index, sorgunun ihtiyaç duyduğu tüm verileri (SELECT kısmındaki sütunlar) indeksin içinde barındırması durumudur. Bu sayede veritabanı, verinin kendisini içeren ana tabloya (Heap) gitmek zorunda kalmaz. Bu, disk I/O (giriş/çıkış) yükünü ciddi oranda azaltır.

-- İhtiyaç duyulan sütunları kapsayan indeks
CREATE INDEX idx_siparis_ozet ON siparisler (musteri_id, siparis_tarihi) INCLUDE (toplam_tutar);

Yukarıdaki INCLUDE anahtar kelimesi, PostgreSQL gibi sistemlerde veriyi indeks yapısının "yaprak" kısmına ekler. Böylece SELECT toplam_tutar FROM siparisler WHERE musteri_id = 10 sorgusu, sadece indeksi okuyarak sonucu döner.

Adım 4: İndekslerin Performansı Nasıl Ölçülür?

İndeksler sadece okuma işlemlerini hızlandırır, ancak yazma (INSERT, UPDATE, DELETE) işlemlerini yavaşlatır. Her indeks, veritabanı motorunun veriyi güncellerken indeksi de güncellemesini zorunlu kılar. Bu yüzden gereksiz indekslerden kaçınmalıyız. "Kullanılmayan İndeksler" raporlarını periyodik olarak kontrol etmelisiniz.

Adım 5: Partial (Kısmi) İndeksleme Teknikleri

Bazen tablonun sadece küçük bir kısmı üzerinde sorgu yaparsınız. Örneğin, "aktif olmayan" kullanıcıları nadiren sorguluyorsanız, tüm kullanıcılar için indeks oluşturmak kaynak israfıdır. Bunun yerine sadece aktif olanları içeren bir kısmi indeks oluşturabilirsiniz.

-- Sadece aktif olan kullanıcılar için indeks
CREATE INDEX idx_aktif_kullanicilar ON kullanicilar (kayit_tarihi) WHERE durum = 'aktif';

Bu yöntem, indeks boyutunu küçültür ve bellek kullanımını optimize eder. Sorgu motoru, WHERE durum = 'aktif' içeren sorgularda otomatik olarak bu küçük indeksi seçer.

Adım 6: İndeks Bakımı ve Yeniden Oluşturma

Zamanla veritabanındaki veriler değiştikçe indeksler "parçalanabilir" (fragmentation). Bu durum, indekslerin verimliliğini düşürür. PostgreSQL'de REINDEX komutu ile indeksleri yeniden düzenleyebilirsiniz. Ancak bu işlem büyük tablolarda sistemi kilitleyebilir.

-- İndeksi kilitlemeden yeniden oluşturma (PostgreSQL)
REINDEX INDEX CONCURRENTLY idx_kategori_fiyat;

Güvenlik Uyarısı: İndeks optimizasyonu yaparken veritabanı şemasını değiştirmek veya `REINDEX` çalıştırmak, üretim ortamında (production) ciddi performans dalgalanmalarına yol açabilir. Bu işlemleri yoğunluğun az olduğu saatlerde yapın ve mutlaka bir test ortamında doğrulayın.

Sıkça Sorulan Sorular

Bir tabloda kaç tane indeks olmalı?

Bunun kesin bir sayısı yoktur ancak çok fazla indeks, INSERT ve UPDATE işlemlerini ciddi oranda yavaşlatır. İhtiyaç duyulmayan, sorgularda kullanılmayan indeksleri düzenli olarak temizlemek en iyi pratiktir.

İndeksler her zaman sorguyu hızlandırır mı?

Hayır. Çok küçük tablolarda veritabanı motoru, indeksi okumak yerine tabloyu taramanın daha hızlı olduğuna karar verebilir. Ayrıca, "SELECT *" gibi tüm sütunları getiren sorgularda indeks etkisi sınırlı olabilir.

Composite indekslerde sütun sırası önemli mi?

Evet, çok önemlidir. En fazla filtreleme yaptığınız (en düşük seçiciliğe sahip) sütunu indeksin en başına koymalısınız. Örneğin, cinsiyet gibi sadece iki değeri olan bir sütunu indeksin en başına koymak, indeksin verimliliğini düşürür.

Kullanılmayan indeksleri nasıl tespit ederim?

Veritabanı sisteminizin istatistik tablolarını (PostgreSQL için pg_stat_user_indexes) sorgulayarak, hangi indekslerin hiç "scan" edilmediğini görebilirsiniz.

İndeksler disk alanını etkiler mi?

Evet, her indeks fiziksel bir dosyadır ve diskte yer kaplar. Çok büyük tablolarda çok sayıda indeks oluşturmak, veritabanı boyutunun iki katına çıkmasına neden olabilir.

Sorumluluk Reddi: Bu rehberde yer alan SQL komutları genel veritabanı prensiplerine dayanmaktadır. Uygulama yapmadan önce veritabanınızın yedeğini aldığınızdan emin olun. Yanlış indeksleme stratejileri veri kaybına yol açmaz ancak sistem performansını olumsuz etkileyebilir.

İleri Düzey İpucu: İndeksleme Stratejilerinde "Sargable" Sorgu Tasarımı

İndeksleriniz ne kadar mükemmel olursa olsun, sorgu yazım şekliniz indeksin kullanılmasını engelleyebilir. Veritabanı literatüründe Sargable (Search ARGumentable) olarak adlandırılan kavram, sorgunun indeks kullanımına uygunluğunu ifade eder. Eğer bir sütun üzerinde fonksiyon kullanırsanız, veritabanı motoru indeksi taramak yerine tüm tabloyu taramak (Full Table Scan) zorunda kalır.

Aşağıdaki örnekte, indeksli bir sütun üzerinde yapılan hatalı ve doğru sorgulama yöntemlerini görebilirsiniz:

-- Hatalı (İndeksi devre dışı bırakır)
SELECT * FROM siparisler WHERE YEAR(siparis_tarihi) = 2023;

-- Doğru (İndeksi kullanır - Sargable)
SELECT * FROM siparisler 
WHERE siparis_tarihi >= '2023-01-01' AND siparis_tarihi < '2024-01-01';

Bu yaklaşım, özellikle büyük veri setlerinde sorgu süresini milisaniyelere indirebilir. Benzer şekilde, LIKE '%metin' kullanımı da indeksin B-Tree yapısını bozduğu için performans kaybına yol açar. Arama yaparken mutlaka LIKE 'metin%' şeklinde joker karakteri sona koymaya özen gösterin.

Veritabanı Performans Hata Ayıklama (Debugging) Süreçleri

İndekslerin performans üzerindeki etkisini canlı ortamda test etmek riskli olabilir. Bu nedenle, geliştirme aşamasında Execution Plan analizini bir alışkanlık haline getirmelisiniz. PostgreSQL ve MySQL gibi sistemlerde, sorgunun maliyetini (cost) ve hangi indeksin seçildiğini görmek için aşağıdaki komutları kullanabilirsiniz:

-- PostgreSQL için
EXPLAIN ANALYZE SELECT * FROM kullanicilar WHERE eposta = 'test@example.com';

-- MySQL için
EXPLAIN SELECT * FROM kullanicilar WHERE eposta = 'test@example.com';

Analiz çıktısında dikkat etmeniz gereken kritik noktalar şunlardır:

• type: 'ALL' değeri görüyorsanız, indeks kullanılmıyor demektir. 'ref' veya 'range' değerlerini hedeflemelisiniz.
• rows: Sorgunun kaç satıra dokunduğunu gösterir. Bu sayı ne kadar düşükse sorgu o kadar verimlidir.
• key: Sorgu sırasında hangi indeksin kullanıldığını belirtir. Eğer boşsa, indeksleme stratejinizi gözden geçirmelisiniz.

Son olarak, veritabanı sunucunuzun Buffer Pool ayarlarını kontrol edin. İndeksler RAM üzerinde ne kadar çok yer kaplarsa, disk I/O işlemleri o kadar azalır. Ancak çok fazla indeks, yazma (INSERT/UPDATE) işlemlerinde "page split" sorununa yol açarak performansı düşürebilir. Bu dengeyi korumak için sadece en çok kullanılan sorgularınız için indeks oluşturun ve düzenli olarak sys.dm_db_index_usage_stats (SQL Server) veya pg_stat_user_indexes (PostgreSQL) tablolarını kontrol ederek kullanılmayan indeksleri temizleyin.

Sonuç

Sql & veritabanı ile ileri düzey indeks optimizasyonu, sadece birkaç komut bilmek değil, veritabanının veriyi nasıl işlediğini anlamakla ilgilidir. İndekslerinizi sorgu desenlerinize göre tasarlayarak, sisteminizin ölçeklenebilirliğini artırabilirsiniz. Bir sonraki adım olarak, veritabanı "Execution Plan" (Yürütme Planı) okuma konusunda uzmanlaşmanızı ve yavaş sorguları belirlemek için "Slow Query Log" analizlerini öğrenmenizi öneririm.