Veritabanı ve Sorgu Optimizasyonu

Konu Anlatımı

Sorgular Neden Yavaşlar?

Bir sorgunun süresi, okunan veri miktarı, kullanılan erişim yolu ve veri tabanının o anki yüküyle belirlenir. Aynı sorgu küçük bir tabloda anında, milyonlarca satırlık bir tabloda saniyeler sürebilir. Fark, veri tabanının satırları tek tek okuyup karşılaştırmasından ya da hazırlanmış bir erişim yolunu kullanmasından kaynaklanır.

Optimizasyon iki aşamalıdır: önce sorun ölçülür, sonra müdahale edilir. Hangi tablonun kaç sayfa okuduğu, kaç satırı döndürdüğü ve hangi adımda beklediği bilinmeden yapılan değişikliklerin etkisi ölçülemez.

İndeks Nedir ve Nasıl Çalışır?

İndeks, tablonun belirli sütun veya sütunlarına göre sıralanmış ayrı bir veri yapısıdır. Aramada tam tabloyu taramak yerine indeks ağacında ilerlenir; aranan değere ulaşıldığında o satırların adresi bulunur. Böylece okunan veri miktarı çok azalır.

İndeksin maliyeti de vardır. Veri yazıldığında indeks de güncellenir, ek alan kaplanır ve her indeks ayrı bir erişim yoludur; indeks fazlalığı yazma hızını düşürür. Ayrıca indekslenen sütunlarda arama, sıralama ve aralık sorguları hızlanırken sonuç döndürme oranı çok yüksek filtrelerde indeks işe yaramaz, hatta tam tarama daha hızlı olabilir.

Sorgu Planı ve Yürütme Sırası

Sorgu planı, veri tabanının sorguyu nasıl yürüteceğini adım adım gösterir. Planda hangi adımda tam tarama, hangi adımda indeks erişimi, kaç satırın geçici sonuçta birleştirildiği yazılıdır. Plan okunurken dönen satır sayısının tahmini ile gerçekte dönen sayı karşılaştırılır; tahmin çok düşükse istatistik güncellenmelidir.

Birleştirme sırası önemlidir. Çok satırlı bir tablo önce küçük ve seçici bir tabloda filtrelenip sonra büyük tabloyla birleştirilirse ara sonuç küçük kalır. Ters sırada ara sonuç milyonlarca satıra ulaşabilir. Aynı mantık sayfalama ve sınır uygulamasında da geçerlidir: veri tabanı yalnızca gereken kayıtları üretmelidir.

Birleştirme ve Şema Tasarımı

İç birleştirmede yalnızca iki tarafta eşleşen kayıtlar döner, sol birleştirmede soldaki tüm kayıtlar korunur. İç birleştirmenin sonuç sayısı eşleşen çift sayısına eşittir. Karma (kartezyen) birleştirme ise iki tablonun satır sayılarının çarpımı kadar satır üretir; yanlış bir koşul yazımında sonuç tablonunun çok üstüne çıkar ve sunucu çökenebilir.

Şema tasarımında tekrarı azaltmak, hem veri boyutunu hem de güncelleme maliyetini düşürür. Çok sayıda satırda tekrarlanan bilgiler ayrı bir tabloya taşınır ve bir anahtar üzerinden bağlanır. Ancak her ayırma işlemi birleştirme maliyeti doğurur; bu yüzden denge kurulmalı, karar ölçümle desteklenmelidir.

• Önce ölç, sonra karar ver; tahminle optimizasyon yapılmaz.
• Filtrelenen satır sayısı çok düşükse indeks, çok yüksekse tam tarama daha uygun olabilir.
• Yazma sıklığı yüksek tablolarda gereksiz indeks eklemek yazma hızını düşürür.
• Birleştirme sırası ve ara sonuç boyutu, sorgu planında mutlaka kontrol edilmelidir.

Bekleme, Önbellek ve Sürekli İzleme

Veri tabanının zamanını birçok şey harcar. Bekleme süresi oranı, isteklerin toplam süresinde veri tabanının beklediği kısmı gösterir. Önbellek isabet oranı ise okunan sayfaların ne kadarının bellekten geldiğini ölçer; düşük isabet oranı, sık kullanılan verinin bellekte kalmadığına işaret eder.

Bu ölçütler düzenli izlenmeli ve değişikliklerden sonra yeniden ölçülmelidir. Tablo büyüdükçe bir zamanlar iyi olan bir plan kötüleşebilir. Optimizasyonun kalıcı olması için ölçüm, karar ve doğrulama adımlarının tekrarlanabilir bir düzene bağlanması gerekir.

Ne Öğreneceğiz

İndeksler, sorgu planları, birleştirme stratejileri ve ölçüm metrikleriyle veri tabanı sorgularının süresini ve kaynak tüketimini azaltmayı kapsar. Bu konuda ögrenme çıktıları:

• Tam tarama ile indeksli erişimin maliyet farkını sorgu planından okur.
• Seçicilik, okunan sayfa ve isabet oranı değerlerini hesaplayarak sorunu tanımlar.
• Sorgu yavaşlığının nedenini sıralayarak önceliklendirilmiş çözüm planı üretir.
• Birleştirme türlerini ve yanlış tablo sırasının etkisini karşılaştırır.
• Normalizasyon ile tekrarın azaltılmasının performansa etkisini irdeler.

Ön Bilgi

Bu konuya başlamadan önce temel kavram ve ön koşulları kavramanız yardımcı olacaktır. Konunun çerçevesinde şu kavramlar dikkat ister:

• İndeks okuma hızını artırır, yazma hızını ve depolama alanını ise azaltabilir.
• Tam tarama ile indeks erişimi arasındaki seçim seçiciliğe bağlıdır.
• Sorgu planında tahmin edilen satır sayısı ile gerçek satır sayısı karşılaştırılmalıdır.
• Birleştirmede küçük ve seçici tablo önce filtrelenmelidir.
• Normalizasyon tekrarı azaltır ama birleştirme maliyeti doğurabilir.
• Ölçüm, karar ve yeniden ölçüm döngüsü optimizasyonun temelidir.

Basit Örnek ve Günlük Yaşam

Günlük yaşamda ve mesleki bağlamda bir uygulama:

Soru: Bir milyon satırlık tabloda bir filtre 12000 satır döndürüyor.

1. Dönen satır sayısını yaz.
2. Toplam satır sayısını yaz.
3. Böl ve yüzdeye çevir.

Sonuç/cevap: 12000 / 1000000 = 0,012 çıkar. Seçicilik yüzde 1,2 olarak bulunur; bu kadar düşük seçicilikte indeks erişimi genellikle avantajlıdır.

İleri Örnek

Daha ileri bir uygulama:

Soru: 5000000 satırlık bir tabloda yüzde 0,4'lük filtre 20000 satır döndürüyor.

1. Filtre sonucu satır sayısını hesapla.
2. Tam taramada okunacak satır sayısını bul.
3. Farkı okunmadan kazanılan veri olarak yaz.

Sonuç/cevap: Tam tarama 5000000 satır okurken indeksli erişim yalnızca 20000 satırla ilgilenir. 5000000 - 20000 = 4980000 satır okunmadan tasarruf edilir.

Karşılaştırma Tablosu

Sık Yapılan Hatalar

Her sütuna indeks eklemek: İndeks sayısı arttıkça yazma hızı düşer ve depolama alanı büyür. İndeks yalnızca sık sorgulanan ve seçici sütunlara eklenmelidir.

Ölçmeden karar vermek: Hangi adımın yavaşladığı bilinmeden yapılan değişiklik başka bir adımı yavaşlatabilir. Önce plan ve süre ölçülmelidir.

Birleştirme sırasını gözden geçirmek: Büyük tablonun önce filtrelenmemesi ara sonuç sayısını şişirir ve sorguyu beklediği yerde bekletir.

Satır sayısını tahmin etmek: Seçiciliği kaba tahminlemek, indeksin gerçekten kullanılıp kullanılmayacağını yanlış öngörmeye yol açar.

Sorgu sonuçlarının tamamını sunucudan çekmek: Gereğinden fazla veri taşımak hem ağ hem bellek maliyetini artırır; sınır ve sayfalama sorgu içinde uygulanmalıdır.

Özet

Sorgu süresi okunan veri miktarı ve seçilen erişim yoluna bağlıdır; bu yüzden önce ölçülür, sonra müdahale edilir. İndeks sütunları sıralanmış ayrı bir veri yapısıdır ve arama, sıralama, aralık sorgularını hızlandırır, ama yazma hızını ve depolama alanını olumsuz etkiler. Ölçümler sayısal olarak yapılabilir: 12000 / 1000000 = 0,012 seçicilik yani yüzde 1,2 bulunur; 2000000 satırlık tabloda 200 satırlık sayfalarla tam tarama 2000000 / 200 = 10000 sayfa okur; indeksle 8 saniyeden 0,8 saniyeye inen sorguda iyileşme (8 - 0,8) / 8 = 0,9 yani yüzde 90 olur; 400 sayfadan 320'si bellekten geldiyse isabet oranı 320 / 400 = 0,8 yani yüzde 80 bulunur. Birleştirmede eşleşen çift sayısı iç birleştirme sonucunu belirler, büyük tablo önce filtrelenmezse ara sonuç şişer. Normalizasyon tekrarı azaltır ama birleştirme maliyeti doğurur; karar ölçümle verilmelidir.

Kendini Dene

Soru: Yönerge: Bir okulun öğrenci kayıt veri tabanında liste ekranı 6 saniye sürüyor ve sunucu yüksek. (I) yavaşlığın kaynağını bulmak için izlenecek adımları, (II) ölçülecek dört değeri, (III) uygulanabilecek en az dört önlemi, (IV) her önlemin hangi ölçüde iyileşme beklediğini madde madde yaz.

Örnek cevap: Puanlama anahtarı: Tanı adımlarının sıralanması 6 puan; dört ölçünün belirlenmesi 5 puan; en az dört önlemin yazılması 7 puan; önlemlerin ölçüm değerleriyle ilişkilendirilmesi 7 puan. Toplam 25 puan. Model cevap: Önce yavaş sorgular süreleriyle listelenir, sorgu planı incelenir. Ölçülecek değerler; sorgu süresi, okunan sayfa sayısı, dönen satır sayısı ve bekleme oranıdır. Önlemler; sık kullanılan seçici sütunlara indeks ekleme, filtreyi erken uygulama, sorgu sonucunda sınır ve sayfalama kullanma, gereksiz indeksleri kaldırma. Beklenen iyileşme; indeks tam tarama süresini, sınır ve sayfalama taşınan veriyi ve gecikmeyi, indeks temizliği ise yazma süresini iyileştirir.