Genel olarak veritabanlarinda veri miktari arttikca, tum veriye ulasimin maliyeti de benzer oranda artar. Mesela ayni tablo icin 100 kayitla calismak ile 1 milyon kayitla calismak arasinda araginiz veriye ulasim acisindan cok buyuk maliyet farki vardir. Bu maliyet genel olarak CPU ve disk erisimi olarak karsimiza cikacaktir. Eger veritabaniniz baska bir makine uzerinde ise buna ilaveten bir de network kullanimi maliyeti eklenecektir.
Simdi yuksek maliyete bir ornek verelim, mesela asagidaki gibi bir satis tablomuz olsun.
Satis Tablosu:
==============
Alan 1: musteri
Alan 2: satis-tarihi
Alan 3: urun
Alan 4: tutar
==============
Eger tablonuzda birkac yuz hatta birkac bin kayit varsa bir musteriye ait satislari ya da belli bir urunun tum satislarini aramak bir kac saniyeyi gecmeyecektir. Yillar icinde ayni tabloya milyonlarca kayit eklendigini dusunun. Tabi milyonlarca satis yaptiginiz icin artik bir milyonersiniz ama bu veritabani sorgularinizin artik eskisi gibi hizli sonuc dondurmedigi gercegini degistirmiyor. Mesela bir urunun tum satislarindan gelen kari hesaplamak artik dakikalar aliyor. Bunun nedeni sizin tablo uzerinde calistirdiginiz her "WHERE urun=101" benzeri sorgunun tum tabloyu diskten hafizaya yukleyip her kayda tek tek bakip aradiginiz kritere uygun kayitlari size dondurme cabasidir. Buna kisaca ""full table scan" denir, yani Turkcesi "tum tablo taramasi". Genelde tablo boylari buyudugunde full table scan cok problem yaratmaya baslar. Sonucta sunucu kaynaklari paylasimli oldugu icin bu tablo uzerinde bir sorgu calisirken diger sorgularin da ayni sekilde yavasladigini gorursunuz. Isler cigrindan cikmadan bu soruna bir cozum uretmeniz gerekmektedir. Iste bu cozumun adi indekslemedir. Indeksleme bildiginiz kitap indeksleri gibi calisir. Kitapta bir kelimeyi tum kitabi okuyarak aramak yerine son sayfada bulunan indekste arayip sayfa numarasindan ilgili bilgiye ulasirsiniz. Veritabanlari icin de ayni cozum gecerlidir. Milyonlarca kayida tek tek bakmak yerine indeksleri kullanarak aradiginiz kriterde kayitlarin nerede oldugunu bilirsiniz. Bu sayede agir sorgular saatler yerine saniyeler icinde calisir ve cozum dondurur.
Ornegimize geri donecek olursak; en son belli bir urunun tum satislarina ulasmaya calisiyorduk. Eger ayni tablo uzerinde "urun" alanina bir indeks tanimlarsak tablo uzerinde ilk indeksimizi de eklemis olacagiz.
CREATE INDEX urun_ind ON satis(urun);
Bu komutu calistirdiginizda MySQL tum satis tablosunu tarayip indeks tablosunu olusturup diske yazacaktir. Boylece bir sonraki "WHERE urun=101" tipi sorgunuz data tablosu uzerinde degil indeks uzerinde isletilecektir. Bu da disk erisimi acisindan cok buyuk kazanc saglayacak ve sonuc olarak sorgulariniz yuzlerce binlerce kat daha hizli sonuc uretecektir.
Biraz problemi karmasiklastiralim. Yukaridaki ornekte "WHERE urun=101" tipi tek kolonlu bir sorgu icin indeks olusturmayi gorduk. Diyelim sorgumuz "WHERE musteri=1234 AND urun=101" seklinde iki kolonlu. Bu durumda ne yapmamiz gerekiyor? Her iki alani da adresleyen bir indeks eklemelisiniz.
CREATE INDEX musteri_urun_ind ON satis(musteri,urun);
Diyeceksiniz ki her alan icin iki indeksimiz olsa ayni isi gormez mi? Yani asagidaki gibi.
CREATE INDEX musteri_ind ON satis(musteri);
CREATE INDEX urun_ind ON satis(urun);
Tam olarak gormez. Eger sorgunuz "musteri=10" ya da "urun=20" seklinde sadece tek alanli olsaydi musteri_ind ve urun_ind indeksleri devreye girebilecekti. Ama sorgunuz "musteri=10 AND urun=20" seklinde; bu durumda sadece {musteri_ind} indeksiniz devreye girecektir. Baska bir ifadeyle MySQL motoru "musteri=10" sonucunda donen musterinin tum hareketlerinde sirayla "urun=20" sartina uyan kayitlari arayacaktir. Bu tam anlamiyla full table scan olmasa dahi istenen bir calisma sekli degildir. Iste bu durumda indeksinizi (musteri, urun) seklinde iki alan uzerinde tanimlamaniz daha faydali olacaktir.
INDEKS ONCELIGI:
=========================================================================
Gelelim bircok kisinin bilmedigi bir konuya. Yukaridaki ornegimize devam edelim. En son (musteri, urun) uzerine bir indeks eklemistik. Diyelim siz ayni zamanda sadece musteri alani uzerinden ya da sadece urun alani uzerinden sorgular da calistiriyorsunuz. Yani "SELECT * FROM satis WHERE musteri=10" veya "SELECT * FROM satis WHERE urun=20" gibi. Bu durumda ayrica her iki alan icin de indeks tanimlamaya gerek var mi? Soru gayet makul, sonucta yularida tanimladigimiz musteri_urun_ind(musteri,urun) indeksimiz mevcut. Cevap hep evet hem hayir. Bunun nedeni bu tip durumlarda indeks onceliginin devreye girmesidir. Ornekle aciklamak kolay olacak. Mesela (a1,a2,a3) alanlari uzerinde tanimli bir indeksiniz var. Bu durumda rahatlikla sadece "a1", sadece "a1 ve a2" veya "a1 ve a2 ve a3" turu sorgular calistirabilirsiniz. Yani (a1, a2, a3) alanlari uzerinde tanimlanmis indeksimiz ayni zamanda (a1) indeksi ve (a1,a2) indeksi olarak da calisacaktir.
Yine yukaridaki ornekle ilgili sordugumuz soruyu cevaplamaya calisirsak; "SELECT * FROM satis WHERE musteri=10" sorgusu icin {musteri} alani uzerine bir indeks tanimlamamiza gerek yoktur. Cunku {musteri} alani musteri_urun_ind indeksimizin birinci alanidir. Ancak "SELECT * FROM satis WHERE urun=20" sorgusu sadece {urun} alanini sorguladigi icin ve bu alan indeksimizin ikinci alani oldugu icin musteri_urun_ind indeksimiz devreye girmeyecektir bu da full table scane neden olacaktir. Bu durumda {urun} alani icin ayrica bir indeks tanimlamak gerekecektir.
Ozetle eger (musteri,urun) indeksimiz varsa bu indeks ayni zamanda "musteri=10" gibi aramalar icin de devreye girecektir ancak "urun=20" sorgusu icin ise yaramayacaktir.
Son olarak indeksleme konusunda bilmekte fayda gordugum noktalari siralamak istiyorum:
===========================================================================
* Gereksiz indeksler yaratmayin. Indeksler sorgu maliyetlerini dusurmek amacli olsalar dahi her indeksin mutlaka arti maliyeti olacaktir. Indeks maliyetleri genelde ,INSERT UPDATE, DELETE gibi veriyi degistiren erisimlerde on plana cikar. Bu baglamda gereksiz indeksleriniz daha uzun zaman alan guncelleme sorgularina neden olacaktir.
* Eger ongorebiliyorsaniz indekslerinizi uygulamanizin tasarim asamasinda sekillendirin. Ama idealde cogu zaman bu ise yaramaz. Bu yuzden ne kadar da iyi tasarlarsaniz zamanla uygulamanizin bazi parcalarinin veritabani erisimi sirasinda yavasladigini gozlemleyebilirsiniz. Bu asamada en iyisi vakit kaybetmeden indeks iyilestirmelerine baslamaktir.
* MySQL uzun sure alan sorgulari belli bor log dosyasina atar. Bu dosya genelde server-slow.log adindadir ve MySQL log klassorunde bulunur. Bu loglara goz atarak hangi tur sorgularin maliyetli oldugunu belirleyebilirsiniz.
* Eger Cpanel kullaniyorsaniz MUNIN pluginin kurun. MySQL sorgu maliyetlerini MUNIN grafiklerinden izleyebilirsiniz. Gunun hangi saatlerinde MySQL sorgularinin yavasladigini bilmek cok isinize yarayabilir.
* Eger maliyetli sorgular yazacaksiniz mumkun oldugunca hali hazirda varolan indekslerden yararlanin. Hatta gerekirse sorgunuzu revize edin. Eger var olan indeksler isinizi gormuyorsa ve sorgunuzu revize edemiyorsaniz yeni bir indeks tanimlamanin vakti gelmis demektir.
* Her ne kadar bu konuda deginmesem de sorgu sonuclarinin MySQL gecici tablolarinda saklanmasi onemlidir. Eger cok fazla isletilen sorgulariniz varsa MySQL bunlari gecici memory tablolarinda saklayarak disk erisiminiz en aza indirmeye calisacaktir. Burada onemli olan hafizadan yeterli miktarda gecici tablo alani rezerve etmenizdir. Eger bu tur optimizasyonlar yapmak istiyorsaniz TMP_TABLE_SIZE ve MAX_HEAP_TABLE_SIZE MySQL degiskenlerine goz atmanizi oneririm.
NOT: Makaleyi istediginiz gibi alip yayinlayabilirsiniz.
Simdi yuksek maliyete bir ornek verelim, mesela asagidaki gibi bir satis tablomuz olsun.
Satis Tablosu:
==============
Alan 1: musteri
Alan 2: satis-tarihi
Alan 3: urun
Alan 4: tutar
==============
Eger tablonuzda birkac yuz hatta birkac bin kayit varsa bir musteriye ait satislari ya da belli bir urunun tum satislarini aramak bir kac saniyeyi gecmeyecektir. Yillar icinde ayni tabloya milyonlarca kayit eklendigini dusunun. Tabi milyonlarca satis yaptiginiz icin artik bir milyonersiniz ama bu veritabani sorgularinizin artik eskisi gibi hizli sonuc dondurmedigi gercegini degistirmiyor. Mesela bir urunun tum satislarindan gelen kari hesaplamak artik dakikalar aliyor. Bunun nedeni sizin tablo uzerinde calistirdiginiz her "WHERE urun=101" benzeri sorgunun tum tabloyu diskten hafizaya yukleyip her kayda tek tek bakip aradiginiz kritere uygun kayitlari size dondurme cabasidir. Buna kisaca ""full table scan" denir, yani Turkcesi "tum tablo taramasi". Genelde tablo boylari buyudugunde full table scan cok problem yaratmaya baslar. Sonucta sunucu kaynaklari paylasimli oldugu icin bu tablo uzerinde bir sorgu calisirken diger sorgularin da ayni sekilde yavasladigini gorursunuz. Isler cigrindan cikmadan bu soruna bir cozum uretmeniz gerekmektedir. Iste bu cozumun adi indekslemedir. Indeksleme bildiginiz kitap indeksleri gibi calisir. Kitapta bir kelimeyi tum kitabi okuyarak aramak yerine son sayfada bulunan indekste arayip sayfa numarasindan ilgili bilgiye ulasirsiniz. Veritabanlari icin de ayni cozum gecerlidir. Milyonlarca kayida tek tek bakmak yerine indeksleri kullanarak aradiginiz kriterde kayitlarin nerede oldugunu bilirsiniz. Bu sayede agir sorgular saatler yerine saniyeler icinde calisir ve cozum dondurur.
Ornegimize geri donecek olursak; en son belli bir urunun tum satislarina ulasmaya calisiyorduk. Eger ayni tablo uzerinde "urun" alanina bir indeks tanimlarsak tablo uzerinde ilk indeksimizi de eklemis olacagiz.
CREATE INDEX urun_ind ON satis(urun);
Bu komutu calistirdiginizda MySQL tum satis tablosunu tarayip indeks tablosunu olusturup diske yazacaktir. Boylece bir sonraki "WHERE urun=101" tipi sorgunuz data tablosu uzerinde degil indeks uzerinde isletilecektir. Bu da disk erisimi acisindan cok buyuk kazanc saglayacak ve sonuc olarak sorgulariniz yuzlerce binlerce kat daha hizli sonuc uretecektir.
Biraz problemi karmasiklastiralim. Yukaridaki ornekte "WHERE urun=101" tipi tek kolonlu bir sorgu icin indeks olusturmayi gorduk. Diyelim sorgumuz "WHERE musteri=1234 AND urun=101" seklinde iki kolonlu. Bu durumda ne yapmamiz gerekiyor? Her iki alani da adresleyen bir indeks eklemelisiniz.
CREATE INDEX musteri_urun_ind ON satis(musteri,urun);
Diyeceksiniz ki her alan icin iki indeksimiz olsa ayni isi gormez mi? Yani asagidaki gibi.
CREATE INDEX musteri_ind ON satis(musteri);
CREATE INDEX urun_ind ON satis(urun);
Tam olarak gormez. Eger sorgunuz "musteri=10" ya da "urun=20" seklinde sadece tek alanli olsaydi musteri_ind ve urun_ind indeksleri devreye girebilecekti. Ama sorgunuz "musteri=10 AND urun=20" seklinde; bu durumda sadece {musteri_ind} indeksiniz devreye girecektir. Baska bir ifadeyle MySQL motoru "musteri=10" sonucunda donen musterinin tum hareketlerinde sirayla "urun=20" sartina uyan kayitlari arayacaktir. Bu tam anlamiyla full table scan olmasa dahi istenen bir calisma sekli degildir. Iste bu durumda indeksinizi (musteri, urun) seklinde iki alan uzerinde tanimlamaniz daha faydali olacaktir.
INDEKS ONCELIGI:
=========================================================================
Gelelim bircok kisinin bilmedigi bir konuya. Yukaridaki ornegimize devam edelim. En son (musteri, urun) uzerine bir indeks eklemistik. Diyelim siz ayni zamanda sadece musteri alani uzerinden ya da sadece urun alani uzerinden sorgular da calistiriyorsunuz. Yani "SELECT * FROM satis WHERE musteri=10" veya "SELECT * FROM satis WHERE urun=20" gibi. Bu durumda ayrica her iki alan icin de indeks tanimlamaya gerek var mi? Soru gayet makul, sonucta yularida tanimladigimiz musteri_urun_ind(musteri,urun) indeksimiz mevcut. Cevap hep evet hem hayir. Bunun nedeni bu tip durumlarda indeks onceliginin devreye girmesidir. Ornekle aciklamak kolay olacak. Mesela (a1,a2,a3) alanlari uzerinde tanimli bir indeksiniz var. Bu durumda rahatlikla sadece "a1", sadece "a1 ve a2" veya "a1 ve a2 ve a3" turu sorgular calistirabilirsiniz. Yani (a1, a2, a3) alanlari uzerinde tanimlanmis indeksimiz ayni zamanda (a1) indeksi ve (a1,a2) indeksi olarak da calisacaktir.
Yine yukaridaki ornekle ilgili sordugumuz soruyu cevaplamaya calisirsak; "SELECT * FROM satis WHERE musteri=10" sorgusu icin {musteri} alani uzerine bir indeks tanimlamamiza gerek yoktur. Cunku {musteri} alani musteri_urun_ind indeksimizin birinci alanidir. Ancak "SELECT * FROM satis WHERE urun=20" sorgusu sadece {urun} alanini sorguladigi icin ve bu alan indeksimizin ikinci alani oldugu icin musteri_urun_ind indeksimiz devreye girmeyecektir bu da full table scane neden olacaktir. Bu durumda {urun} alani icin ayrica bir indeks tanimlamak gerekecektir.
Ozetle eger (musteri,urun) indeksimiz varsa bu indeks ayni zamanda "musteri=10" gibi aramalar icin de devreye girecektir ancak "urun=20" sorgusu icin ise yaramayacaktir.
Son olarak indeksleme konusunda bilmekte fayda gordugum noktalari siralamak istiyorum:
===========================================================================
* Gereksiz indeksler yaratmayin. Indeksler sorgu maliyetlerini dusurmek amacli olsalar dahi her indeksin mutlaka arti maliyeti olacaktir. Indeks maliyetleri genelde ,INSERT UPDATE, DELETE gibi veriyi degistiren erisimlerde on plana cikar. Bu baglamda gereksiz indeksleriniz daha uzun zaman alan guncelleme sorgularina neden olacaktir.
* Eger ongorebiliyorsaniz indekslerinizi uygulamanizin tasarim asamasinda sekillendirin. Ama idealde cogu zaman bu ise yaramaz. Bu yuzden ne kadar da iyi tasarlarsaniz zamanla uygulamanizin bazi parcalarinin veritabani erisimi sirasinda yavasladigini gozlemleyebilirsiniz. Bu asamada en iyisi vakit kaybetmeden indeks iyilestirmelerine baslamaktir.
* MySQL uzun sure alan sorgulari belli bor log dosyasina atar. Bu dosya genelde server-slow.log adindadir ve MySQL log klassorunde bulunur. Bu loglara goz atarak hangi tur sorgularin maliyetli oldugunu belirleyebilirsiniz.
* Eger Cpanel kullaniyorsaniz MUNIN pluginin kurun. MySQL sorgu maliyetlerini MUNIN grafiklerinden izleyebilirsiniz. Gunun hangi saatlerinde MySQL sorgularinin yavasladigini bilmek cok isinize yarayabilir.
* Eger maliyetli sorgular yazacaksiniz mumkun oldugunca hali hazirda varolan indekslerden yararlanin. Hatta gerekirse sorgunuzu revize edin. Eger var olan indeksler isinizi gormuyorsa ve sorgunuzu revize edemiyorsaniz yeni bir indeks tanimlamanin vakti gelmis demektir.
* Her ne kadar bu konuda deginmesem de sorgu sonuclarinin MySQL gecici tablolarinda saklanmasi onemlidir. Eger cok fazla isletilen sorgulariniz varsa MySQL bunlari gecici memory tablolarinda saklayarak disk erisiminiz en aza indirmeye calisacaktir. Burada onemli olan hafizadan yeterli miktarda gecici tablo alani rezerve etmenizdir. Eger bu tur optimizasyonlar yapmak istiyorsaniz TMP_TABLE_SIZE ve MAX_HEAP_TABLE_SIZE MySQL degiskenlerine goz atmanizi oneririm.
NOT: Makaleyi istediginiz gibi alip yayinlayabilirsiniz.