Veritabanı Optimizasyonu - MySQL, Index, Unsigned Zero Fill, Varchar

İçerisindeki yaklaşık 4,5 milyon satır veri olan bir tabloda optimizasyon yapılması gerekiyordu. Bu tabloda gelir-gider kayıtları tutuluyor ve en sık sorgu yapılan parametre bayi id’si ve id numaraları sıfır ile başlayabilen 6 haneli nümerik bir veri.
Kullanılan framework nedeniyle kolon tipi varchar(250) olarak seçilmiş. Ayrıca işlemin gelir veya gider olduğunun bilgisi de aynı şekilde varchar(250) olan bir yapıda tutuluyor.

ID kolonu max 6 haneli bir veri barındırıyor ve veri nümerik.
YÖN kolonu max 5 haneli bir veri barındırıyor GELIR – GIDER

Yapılan Optimizasyon İşlemleri


1- Veri tipini değiştirmek


İlk yapılması gereken bu iki kolon için veri tiplerini düzeltmek. İlk etapta ID kolonunu varchar(10) olarak değiştirdim (İlerde 10 hane gerekebilir diye hazırlık olması amacıyla 6 yerine 10 yaptım). Değiştirmeden önce yaptığım sorguda cevap süresi 5.785 sn idi. Sonrasında 3.6662 sn’ye düştü, 2 sn lik bir fark tek sorgu için gerçekten büyük bir kazanç

2- Index eklemek


ID kolonu artık varchar(10) tipinde, index ekleyerek veriye daha hızlı ulaşabiliriz elbette. Fakat kesin olarak böyle olmayabiliyor.. Index ekleme işlemi sonrasında sorgu süresi 3.6662 sn’ den 3.1959 a düştü. Burada da 0,5 sn lik bir kazanç elde edebildim.

3- Diğer kolonun veri tipini değiştirmek


ID ile birlikte yaptığım sorguya YÖN bilgisini de ekleyip sorgu yapmak istedim. Bir miktar hızlandırmasını ümit etmiştim. Veri tipini varchar(6) yaptıktan sonra sorgu süresi 3,1959 sn’den 2.9426 sn’ye düştü. Ufak da olsa yine bir kazanç.

4- YÖN kolonu için index eklemek


Yön kolonu en başta bahsettiğim gibi sadece iki farklı veri barındırıyor. Index eklemesi çok fark oluşturmayacaktır ama hızlanmasını bekliyordum… Index eklemesinden sonra yaptığım sorgu 16.8707 sn sürdü. Nedeni aslında çok basit, WHERE ile 2 kolon için koşul belirtmiştim, bunlardan YÖN için girdiğimi referans alarak işlem yaptığı için işlem haddinden fazla sürdü. Yani iki farklı indexlenmiş kolon içeren sorgularda dikkatli olmak gerekiyor. Index sayısı her zaman hız artışı sağlamayabiliyor. YÖN için oluşturduğum index’i sildim ve tekrar 2.94 sn’ye geri döndüm.

Verdiğim süreler her sorgu için sürekli olarak aynı gelmiyor. Birkaç denemeden sonra aldığım ortalama değerlerdir.

Sıra geldi phpMyAdmin deki “Propose table structure” dan kopya çekmeye. Aslında olması gerekenler belli adım adım farkları görerek ilerlemek mantığı kavramak için yardımcı oluyor.

Propose Table Structure


Tıkladığımızda, o tablodaki tüm verileri süzerek optimum çalışma için olması gerekenleri özetliyor.
Örneğin, bu kolon için max değer, min değer, optimal veri tipi vs.. Hem ayrıntılı hem özet diyebiliriz :)

ID tablosu için optimal olarak MEDIUMINT(6) UNSIGNED belirlemiş. Elbette nümerik veri barındıran kolon için seçilmesi gerek veritipi int ailesinden olacaktır. Sıfırın altında olmadığı için UNSIGNED doğru seçim fakat ID lerin başında 0 olduğundan sorun çıkartacaktır. Bunu da çözmek için UNSIGNED ZEROFILL’i seçiyoruz :) Yani soldaki sıfırlara izin verecek. Böylece sorun kalmayacak.

Unsigned Zero Fill


İki tür arasındaki fark burada güzelce özetlenmiş: https://stackoverflow.com/a/5256505/4933464

Bir alıntı yaparak devam edelim..

Unsigned vs Zero Fill


x verileri UNSIGNED ZEROFILL, y verileri UNSIGNED.

5- ID kolonun veri tipini değiştirmek
ID kolonumuz için index’imizi oluşturmuştuk. Şimdi veri tipini mediumint(10) ve UNSIGNED ZEROFILL olarak değiştirdik. Hız inanılmaz miktarda arttı. 2.94 sn’den 0,0004 sn’ye düştü.
Yaklaşık olarak on bin kat hız artışı sağlanmış oldu. Sonuç olarak veri tipini ve index I doğru şekilde yapmak önemli ölçüde hız kazandırmış oldu. Aksi halde ise tam tersi tepki vererek sorgu süresini iyice arttırmıştı.
+8
Yorum ekle

Yorum ekle

    • winksmile
      laughing
      angry
Okunamayan kodu yenilemek için resmin üstüne tıklayınız