→ MySQL sorgu optimizasyonu

MySQLçeşitli türler için çok sayıda işleve sahiptir ( TARAFINDAN SİPARİŞ), gruplamalar ( GRUP TARAFINDAN), sendikalar ( SOL YÖNDEN KATILIM veya DOĞRU BİRLEŞTİR) vesaire. Hepsi kesinlikle uygundur, ancak bir kerelik talepler açısından. Örneğin, bir grup tablo ve bağlantı kullanarak kişisel olarak veritabanında bir şey kazmanız gerekiyorsa, yukarıdaki işlevlere ek olarak koşullu operatörleri kullanabilirsiniz ve hatta kullanmanız gerekir. EĞER... Acemi programcıların ana hatası, bu tür sorguları sitenin çalışma koduna uygulama arzusudur. Bu durumda, karmaşık bir sorgu kesinlikle güzel ama zararlıdır. Buradaki nokta, herhangi bir sıralama, gruplama, birleştirme veya alt sorgu işleçlerinin RAM'de çalıştırılamaması ve geçici tablolar oluşturmak için sabit diski kullanmasıdır. Ve zor, bildiğiniz gibi, sunucunun darboğazı.

MySQL sorguları için optimizasyon kuralları

1. İç içe sorgulardan kaçının

Bu en ciddi hatadır. Ana işlem her zaman çocuğun tamamlanmasını bekler ve bu sırada veritabanıyla bağlantıyı sürdürür, diski kullanır ve iowait'i yükler. Veritabanına iki paralel sorgu yapılması ve sunucu yorumlayıcısında gerekli filtrelemenin yapılması ( Perl, PHP, vb.), iç içe geçmiş olandan daha hızlı yürütülecektir.

Perl örnekleri nasıl yapılmaz:

$ sth = $ dbh-> hazırla ("elementID, elementNAME, groupID FROM tbl WHERE groupID IN (2,3,7)"); $ sth-> yürüt (); while (my @row = $ sth-> fetchrow_array()) (my $ groupNAME = $ dbh-> selectrow_array ("GrupADI SEÇİN WHERE groupID = $ satır"); ### Diyelim ki grup adlarını toplamanız gerekiyor # ## ve bunları veri dizisinin sonuna ekleyin push @row => $ groupNAME; ### Başka bir şey yapın ...)

veya hiçbir durumda böyle:

$ sth = $ dbh-> hazırla ("SELECT elementID, elementNAME, groupID FROM tbl WHERE groupID IN (SELECT groupID FROM WHERE groupNAME =" First "VEYA groupNAME =" İkinci "VEYA groupNAME =" Yedinci ")");

Bu tür eylemlere ihtiyaç varsa, her durumda filtreleme için bir karma, dizi veya başka bir yol kullanmak daha iyidir.

Genelde yaptığım gibi Perl'de bir örnek:

% gruplarım; benim $ sth = $ dbh-> hazırla ("Grup Kimliğini SEÇ, grupADI NEREDE groupID IN (2,3,7)"; $ sth-> yürüt (); while (my @row = $ sth-> fetchrow_array ()) ($ grupları ($ satır) = $ satır;) ### Şimdi ana getirmeyi iç içe bir sorgu olmadan yürütelim my $ sth2 = $ dbh-> hazırla (" SELECT elementID , elementNAME, groupID FROM tbl WHERE groupID IN (2,3,7) "); $ sth2-> yürüt (); while (benim @row = $ sth2-> fetchrow_array ()) (push @row => $ grupları ($ satır); ### Başka bir şey yap ...)

2. Veritabanında sıralama yapmayın, gruplamayın veya filtrelemeyin

Mümkün olduğunda, sorgularınızda ORDER BY, GROUP BY, JOIN deyimlerini kullanmayın. Hepsi geçici tablolar kullanır. Sıralama veya gruplama yalnızca öğeleri görüntülemek için gerekliyse, örneğin alfabetik olarak, bu eylemleri yorumlayıcı değişkenlerde gerçekleştirmek daha iyidir.

Nasıl sıralanmayacağına dair Perl örnekleri:

$ sth = $ dbh-> hazırla ("elementID'yi SEÇ, elementNAME FROM tbl WHERE groupID IN (2,3,7) ORDER BY elementNAME"); $ sth-> yürüt (); while (benim @satırım = $ sth-> fetchrow_array()) (qq yazdır ($ satır => $ satır);)

Genelde sıraladığım gibi Perl'de bir örnek:

$ list = $ dbh-> selectall_arrayref ("elemankimliğini SEÇ, elementNAME FROM tbl WHERE groupID IN (2,3,7)"); foreach (sıralama ($ a-> cmp $ b->) @ $ list) (print qq ($ _-> => $ _->);)

Bu şekilde çok daha hızlı. Fark, özellikle çok fazla veri varsa fark edilir. Sıralamanız gerekirse perl birden fazla alanda Schwartz sıralama uygulayabilirsiniz. Rastgele bir ORDER BY RAND () sıralamaya ihtiyacınız varsa, perl'de rasgele sıralamayı kullanın.

3. Endeksleri kullanın

Veritabanında sıralama bazı durumlarda bırakılabilirse, NEREDE başarılı olması pek olası değildir. Bu nedenle karşılaştırılacak alanlar için indeksler oluşturulmalıdır. Yapmaları kolay.

Böyle bir istekle:

TABLO DEĞİŞTİR `any_db`.`any_tbl` DİZİN EKLE` text_index` (`text_fld` (255));

255, anahtarın uzunluğudur. Bazı veri türleri için gerekli değildir. Ayrıntılar için MySQL belgelerine bakın.

Veritabanlarının kullanımı, bir kişinin hayatını büyük ölçüde kolaylaştırır, verilerle çalışır, gerekli bilgileri veritabanından hızlı bir şekilde almanıza veya ona yazmanıza olanak tanır. Bununla birlikte, verilerle çalışmak uygun bir yaklaşım gerektirir, programcı veritabanlarıyla etkileşimin bazı yönlerini hesaba katmalıdır. Özellikle MySQL'den bahsediyoruz. Şimdi, MySQL veritabanlarıyla iletişimi optimize etmeye yönelik ipuçlarına bir göz atalım.

MySQL sorgularını önbelleğe alınabilir hale getirin

MySQL sunucusundaki yerleşik sorgu önbelleğe alma mekanizması, performansı önemli ölçüde artırabilir. Çoğu MySQL veritabanı sunucusu bir önbelleğe alma mekanizması içerir. Kısa bir süre içinde veritabanına yapılan birçok özdeş sorgu, performansta önemli kayıplara neden olabilir, önbellekleme mekanizması bu tür sorguları önbelleğe alabilir ve verileri zaten önbellekten verir. MySQL'in önbelleğe alamadığı sorgular vardır ve bu sorguları biraz farklı yapmanız önerilir.

// MySQL bu sorguyu önbelleğe alamıyor $ res = mysql_query ("SEÇ kullanıcı adı WHERE signup_date> = CURDATE ()"); // farklı şekilde yapabilirsiniz $ bugün = tarih ("Y-m-d"); $ res = mysql_query ("NEREDE kayıt_tarihi kullanıcısından kullanıcı adını SEÇ> =" $ bugün "");

Gerçek şu ki, ilk istekte CURDATE () işlevi kullanıldı, işleminin özelliği, sorgu sonuçlarının önbelleğe alınmasına izin vermiyor. Tarih değeri sorgu dizesine önceden yazılabilir, bu, sorguda CURDATE () işlevinin kullanımını ortadan kaldıracaktır.
Benzer şekilde, MySQL sunucusunun kendisi tarafından önbelleğe alınmayan başka işlevler de vardır; bunlar arasında RAND (), ŞİMDİ () ve sonucu deterministik olmayan diğer işlevler vardır.

EXPLAIN sözdizimini kullanarak sorgunuzun nasıl yürütüldüğünü görüntüleyin

EXPLAIN sözdizimini kullanarak MySQL'in sorgunuzu nasıl yürüttüğünü görebilirsiniz. Kullanımı, sorgu performansındaki ve tablo yapısındaki zayıflıkları belirlemeye yardımcı olabilir. Sorgu sonucunda EXPLAIN, hangi dizinlerin kullanıldığını, verilerin tablolardan nasıl getirildiğini, nasıl sıralandığını vb. gösteren verileri döndürür. Bunu yapmak için, SELECT sorgusunun başına EXPLAIN anahtar sözcüğünü eklemek yeterlidir, ardından verileri içeren bir tablo gösterilecektir.

Bir kayda ihtiyacınız olduğunda, LIMIT 1'i ayarlayın

Tablodan en az bir kaydın olup olmadığını kontrol etmeniz gereken birçok durum vardır, bu durumda sorguya LIMIT 1 parametresini eklemeniz önerilir.Bu onu daha optimal hale getirecektir, çünkü veritabanı motoru, ilk kaydı bulduktan sonra, tüm verileri getirmek yerine veri almayı durduracaktır. Kaynaklardan tasarruf edersiniz.

// Shymkent kodu ile bir şehir talebi veritabanından $ res = mysql_query ("SELECT * FROM location WHERE city =" Shymkent ""); if (mysql_num_rows ($ res)> 0) () // sorguyu optimize etmek için LIMIT 1 ekleyin $ res = mysql_query ("SELECT * FROM location WHERE city =" Shymkent "LIMIT 1"); if (mysql_num_rows ($ res)> 0) ()

Aranan alanları indeksleyin

Özel bir durumda dizin, arama yaptığınız alanların dizini anlamına gelir, bu arama hızını artıracaktır. Bu arada, normal bir dizin, normal ifadeler biçimindeki koşullarla çalışamaz:

// şehir LIKE indeksi burada tetiklenecek 'shym%' // indeks hemen kullanılmayacak şehir LIKE '% shymkent%'

Normal ifadelere sahip koşullar için bir dizin oluşturmak için, kendi dizin sisteminizi kullanmalı veya düşünmelisiniz.

Tabloların birleştiği alanları indeksleyin

Birden çok tablo birleşimleri kullanıyorsanız, birleşmeye dahil olan alanların her iki tabloda da dizine eklenip eklenmediğini göz önünde bulundurmalısınız. Bu konu, MySQL'in tablo alanlarının birleşimlerini dahili olarak nasıl optimize edeceğini etkiler. Birleşim alanları aynı türde ve aynı kodlamada olmalıdır. Onlar. örneğin, bir alan DECIMAL türünde ve diğeri INT ise, MySQL dizini kullanamaz.

ORDER BY RAND () yerine bir alternatif bulun

Rastgele sıralamanın kullanımı gerçekten de oldukça uygundur ve birçok acemi programcı aynı fikirdedir. Ancak, tuzaklar var ve çok önemli, sorgularınızda benzer bir örnekleme yöntemini kullanarak bir performans darboğazı bırakıyorsunuz. Burada veri miktarı arttıkça size kendini hatırlatacak performans darboğazından kurtulmak için alternatif olarak ORDER BY RAND() kullanmak yerine ek koda başvurmanız önerilir.

SEÇ * yerine belirli alanları seçin

"*" - tüm alanların seçimi yerine seçim yaparken sorguda belirli zorunlu alanları belirtmek için tembel olmayın, gerçek şu ki tablodan ne kadar fazla veri okunursa, sorgunuz o kadar yavaş olur.

Tüm tablolar için bir kimlik alanı ekleyin

İyi performans gösteren her tablo, birincil anahtar (PRIMARY_KEY) ve AUTO_INCREMENT olan INT türünde bir kimlik alanına sahip olmalıdır. Ayrıca alan için UNSIGNED parametresi belirtilmelidir, bu da değerin her zaman pozitif olacağı anlamına gelir.
MySQL, birincil anahtarı kullanabilen dahili işlemlere sahiptir, bu, kümeler, paralellik vb. gibi karmaşık veritabanı yapılandırmaları için devreye girer.
Ayrıca, birden fazla tablonuz varsa ve birleştirilmiş bir sorgu çalıştırmak istiyorsanız, tablo kimlikleri kullanışlı olacaktır.

VARCHAR'a alternatif olarak ENUM

Bir tabloya belirli bir değerler kümesi içermesi gereken bir alan eklemek istediğinizi varsayalım. Geleneksel olarak, birçok programcı, alanlar için VARCHAR türünü ortaya çıkarır. Ancak, çok daha hızlı ve daha kompakt olan başka bir alan türü daha var. Bu türdeki değerler, TINYINT ile aynı şekilde saklanır, ancak bir dize türünde olduğu gibi görüntülenir.

NULL yerine NULL DEĞİL kullanın

NULL alanlar, bu NULL değerini işaretlemek gerektiğinden kayıtta daha fazla yer kaplar. MyISAM tabloları, NULL'lu alanlar, her alan en yakın bayta yuvarlanan 1 ekstra bit alacak şekilde saklanır. Bir alanda NULL kullanılması zorunlu değilse, NOT NULL kullanılması önerilir.

Hazır İfadeleri Kullan

$ res = "UPDATE ana bilgisayarları SET ip = INET_ATON (" ($ _ SERVER ["REMOTE_ADDR"]) ") NEREDE id = $ host_id";

Statik tablolar kullanın

Statik bir tablo, tablodaki her alanın sabit bir boyutu olması dışında, veritabanındaki normal bir tablodur. Tabloda sabit olmayan uzunlukta sütunlar varsa, örneğin, şunlar olabilir: VARCHAR, TEXT, BLOB, statik olmayı bırakır ve MySQL bunu biraz farklı şekilde ele alır. Statik tablolar veya sabit boyutlu tablolar olarak da adlandırılabilirler, statik olmayanlardan daha hızlı çalışırlar. Bu tür tablolardan alınan kayıtlar daha hızlı taranacaktır, gerekli satırı seçmeniz gerekirse MySQL konumunu hızlı bir şekilde hesaplayacaktır. Alan sabit bir boyutta değilse, bu durumda arama indeks tarafından gerçekleştirilir. Statik tablolar kullanmanın başka avantajları da vardır, gerçek şu ki, bu tabloların önbelleğe alınması ve ayrıca bir veritabanı çökmesinden sonra kurtarılması daha kolaydır.

Dikey ayırma kullan

Dikey bölme - Bu, tablonun performansını artırmak için tablonun sütunlara bölünmesi anlamına gelir. Örneğin, tabloda çok nadir kullanılan alanlar varsa veya bunlar değişken uzunlukta alanlarsa, ayrı bir tabloya taşınabilirler, böylece tabloyu boşaltırsınız, böylece çalışma hızınızı arttırırsınız.

Ayrı Hacimli INSERT ve DELETE Sorguları

Bu tür çok sayıda sorgunun yürütülmesi, tablonun kilitlenmesine yol açabilir, bunun sonucunda uygulama bir bütün olarak düzgün çalışmaz. Web sunucusuna yapılan paralel istekler, ek tablo erişimi oluşturabilir. Bir tablo önceki bir istek tarafından engellenirse, sonraki istekler sıraya alınır ve sonuç olarak bu, site yavaşlaması ve hatta sunucu çökmesi şeklinde kendini gösterir.
Çok fazla istek yapmanız gerekiyorsa, her şeyi veritabanına atmak yerine bunları küçük gruplar halinde göndererek kontrol etmeye çalışın. Bu durumda, isteğiniz daha uzun sürebilir, ancak bunun diğer kullanıcılar üzerinde daha az etkisi olacaktır.
Örnek:

while (1) (mysql_query ("DELETE FROM günlükleri WHERE log_date<= "2015-07-20" LIMIT 1000"); if (mysql_affected_rows() == 0){ // записи удалены успешно break; } usleep(50000); // делаем небольшую паузу }

Küçük marjlar kullanmaya çalışın

Bildiğiniz gibi, veritabanı verileri sabit diskte saklanır, bu genellikle bir web uygulamasının zayıf noktalarından biri olabilir. Mesele şu ki, küçük boyutlu kayıtlar daha çok tercih edilir, çünkü bunları kullanmak, sabit sürücü ile çalışmayı azaltır. Belirli bir tablonun birkaç satır depolayacağından eminseniz, mantıklı çözüm, mümkün olan en düşük değerlere sahip alan türlerini kullanmak olacaktır. Örneğin, ana anahtar INT türündeyse ve tabloda yalnızca az miktarda veri depolayacaksanız, bunu MEDIUMINT, SMALLINT veya hatta TINYINT türünde yapmak daha iyidir.

İhtiyaçlarınız için tablo türlerini seçin

Günümüzde yaygın olarak bilinen iki tablo türü, her birinin avantajları ve dezavantajları olan MyISAM ve InnoDB'dir. Örneğin, MyISAM, büyük hacimli tablolardaki verileri okumakta iyidir, ancak yazarken daha yavaştır. SELECT COUNT (*) gibi sorgularda da iyi performans gösterir.
InnoDB'nin depolama motoru MyISAM'den daha karmaşıktır, ancak satır kilitlerini destekler, bu da ölçeklendirme söz konusu olduğunda iyi bir şeydir. Bu nedenle birinin diğerinden daha iyi olduğunu söylemek mümkün değil ve doğru değil, ihtiyacınıza göre türü seçmeniz gerekiyor.

9 Ekim 2008 23:37

MySQL sorgularını optimize etme

• MySQL

Günlük işlerde, sorgu yazarken aynı tür hatalarla uğraşmak gerekir.

Bu yazımda sorguların nasıl yazılmaması gerektiğine dair örnekler vermek istiyorum.

• Tüm alanlar getiriliyor
  SEÇ * tablodan

  Sorgu yazarken, tüm alanların seçimini kullanmayın - "*". Yalnızca gerçekten ihtiyacınız olan alanları listeleyin. Bu, alınan ve gönderilen veri miktarını azaltacaktır. Ayrıca, dizinleri kapsamayı da unutmayın. Tablodaki tüm alanlara gerçekten ihtiyacınız olsa bile, bunları listelemek daha iyidir. İlk olarak, kodun okunabilirliğini artırır. Yıldız işareti kullanırken, tabloya bakmadan tabloda hangi alanların olduğunu bulmak imkansızdır. İkincisi, zamanla tablonuzdaki sütun sayısı değişebilir ve bugün beş INT sütunu varsa, o zaman bir ay içinde METİN ve BLOB alanları eklenebilir, bu da seçimi yavaşlatır.

• Döngüsel sorgular.
  SQL'in belirlenmiş bir dil olduğu konusunda net olmanız gerekir. Zaman zaman prosedürel diller açısından düşünmeye alışmış programcılar, düşüncelerini kümelerin dilinde yeniden düşünmekte zorlanırlar. Bu, basit bir kural benimsenerek oldukça basit bir şekilde yapılabilir - "asla bir döngüde sorgu yürütme." Bunun nasıl yapılabileceğine dair örnekler:

  1. Örnekler
  $ news_ids = get_list ("bugünün_haberlerinden haber_kimliğini SEÇ");
  while ($ news_id = get_next ($ news_ids))
  $ haber = get_row ("başlığı SEÇİN, haber FROM FROM NEREDE news_id =". $ news_id);

  Kural çok basittir - ne kadar az istek olursa o kadar iyidir (her ne kadar herhangi bir kuralda olduğu gibi bunun istisnaları olsa da). IN() yapısını unutmayınız. Yukarıdaki kod tek bir sorguda yazılabilir:
  SEÇİN başlık, gövde Today_news İÇERİSİNDEN HABER KULLANARAK KATIL (news_id)

  2. Ekler
  $ günlük = parse_log();
  while ($ kayıt = sonraki ($ günlük))
  sorgu ("INSERT INTO günlükleri SET değeri =" (! LANG :. $ günlük ["value"]);!}

  Bir sorguyu yapıştırmak ve yürütmek çok daha verimlidir:
  INSERT INTO günlükleri (değer) DEĞERLER (...), (...)

  3. Güncellemeler
  Bazen aynı tablodaki birkaç satırı güncellemek gerekir. Güncellenen değer aynıysa, her şey basittir:
  GÜNCELLEME haber SET başlığı = "(! LANG: test" WHERE id IN (1, 2, 3).!}

  Her kayıt için değiştirilen değer farklıysa, bu aşağıdaki sorgu ile yapılabilir:
  GÜNCELLEME haber SET
  başlık = VAKA
  WHEN news_id = 1 SONRA "aa"
  NE ZAMAN news_id = 2 SONRA "bb" SON
  WHERE news_id IN (1, 2)

  Testlerimiz, böyle bir isteğin birkaç ayrı istekten 2-3 kat daha hızlı olduğunu gösteriyor.

• İndekslenmiş alanlarda işlem yapma
  WHERE blogs_count kullanıcılardan user_id SEÇ * 2 = $ değer

  blogs_count sütunu dizine eklenmiş olsa bile bu sorgu dizini kullanmaz. İndexin kullanılabilmesi için sorgudaki indekslenmiş alan üzerinde herhangi bir dönüştürme yapılmamalıdır. Benzer sorgular için dönüştürme işlevlerini farklı bir bölüme taşıyın:
  WHERE blogs_count = $ değer / 2;

  Benzer bir örnek:
  WHERE TO_DAYS (CURRENT_DATE) - TO_DAYS (kayıtlı) kullanıcılardan user_id SEÇİN<= 10;

  Kayıtlı alan üzerinde bir dizin kullanmaz, oysa
  NERDE kayıtlı kullanıcılardan kullanıcı_kimliğini SEÇ> = DATE_SUB (CURRENT_DATE, ARALIK 10 GÜN);
  niyet.

• Sadece sayılarını saymak için satırları getirme
  $ sonuç = mysql_query ("SEÇ * tablodan", $ bağlantı);
  $ num_rows = mysql_num_rows ($ sonuç);
  Belirli bir koşulu karşılayan satır sayısını seçmeniz gerekiyorsa, yalnızca satır sayısını saymak için tüm satırları seçmek yerine COUNT COUNT (*) FROM FROM tablo sorgusunu kullanın.
• Fazladan satırlar getiriliyor
  $ sonuç = mysql_query ("SEÇ * tablo1'DEN", ​​$ bağlantı);
  while ($ satır = mysql_fetch_assoc ($ sonuç) && $ i< 20) {
  …
  }
  Yalnızca n örnek satıra ihtiyacınız varsa, uygulamanızda fazladan satır atmak yerine LIMIT kullanın.
• ORDER BY RAND () kullanma
  SEÇ * tablodan RAND'A GÖRE SİPARİŞ () LIMIT 1;

  Tabloda 4-5 binden fazla satır varsa, ORDER BY RAND () çok yavaş çalışacaktır. İki sorgu yürütmek çok daha verimli olacaktır:

  auto_increment tablosunda "yeni" bir birincil anahtar varsa ve boşluk yoksa:
  $ rnd = rand (1, sorgu ("tablodan MAX (kimlik) SEÇ"));
  $ satır = sorgu ("SELECT * FROM tablosu NEREDE id =". $ rnd);

  Veya:
  $ cnt = sorgu ("SAYI SEÇ (*) tablodan");
  $ satır = sorgu ("SEÇ * tablo LIMIT'DEN". $ cnt. ", 1");
  bununla birlikte, tabloda çok sayıda satır olduğunda da yavaş olabilir.

• Çok sayıda JOIN kullanma
  SEÇME
  v.video_id
  bir isim,
  g.tür
  İTİBAREN
  videolar AS v
  SOL YÖNDEN KATILIM
  link_actors_videos AS la ON la.video_id = v.video_id
  SOL YÖNDEN KATILIM
  ON OLARAK aktörler a.actor_id = la.actor_id
  SOL YÖNDEN KATILIM
  link_genre_video AS lg ON lg.video_id = v.video_id
  SOL YÖNDEN KATILIM
  türler AS g ON g.genre_id = lg.genre_id

  Tabloları birden çoğa bağlarken, her bir JOIN ile seçimdeki satır sayısının artacağı unutulmamalıdır.Bu gibi durumlarda, böyle bir sorguyu birkaç basit sorguya bölmek daha hızlıdır.

• LIMIT kullanılıyor
  SEÇ… LIMIT tablosundan $ başlangıç, sayfa başına $

  Birçok kişi böyle bir sorgunun sayfa başına $ kayıtları (genellikle 10-20) döndüreceğini ve bu nedenle hızlı çalışacağını düşünür. İlk birkaç sayfa için hızlı çalışacaktır. Ancak, kayıt sayısı büyükse ve bir SELECT… FROM tablosu LIMIT 1000000, 1000020 sorgusu yürütmeniz gerekiyorsa, böyle bir sorguyu yürütmek için MySQL önce 1000020 kaydı seçecek, ilk milyonu atacak ve 20 döndürecektir. hiç hızlı ol. Sorunu çözmenin önemsiz bir yolu yoktur. Birçok kişi, mevcut sayfaların sayısını makul bir sayı ile sınırlandırır. Bu tür sorguları, kapsama dizinleri veya üçüncü taraf çözümleri (sfenks gibi) kullanarak da hızlandırabilirsiniz.

• ON DUPLICATE KEY UPDATE kullanılmıyor
  $ satır = sorgu ("SELECT * FROM tablosu WHERE id = 1");

  Eğer ($ satır)
  sorgu ("GÜNCELLEME tablosu SET sütunu = sütun + 1 NEREDE id = 1")
  Başka
  sorgu ("INSERT INTO tablo SET sütunu = 1, id = 1");

  Kimlik alanı için birincil veya benzersiz bir anahtar olması koşuluyla, benzer bir yapı tek bir sorguyla değiştirilebilir:
  INSERT INTO tablo SET sütunu = 1, id = 1 ÇOĞALT ANAHTAR GÜNCELLEME sütunu = sütun + 1

Okumak

Bazen, bir sorgu oluştururken, bir tabloda yalnızca bir benzersiz satıra ihtiyacınız olduğunu zaten bilirsiniz. Benzersiz bir kayda dayalı bir seçim oluşturabilirsiniz. Veya durumunuza uyan herhangi bir sayıda kaydın varlığını kontrol edebilirsiniz.

Bu gibi durumlarda, LIMIT 1 yöntemini kullanmak performansı önemli ölçüde artırabilir:

// California halkının veritabanı var mı? // HAYIR, hiçbiri yok!: $ r = mysql_query ("SELECT * FROM user WHERE durumu =" California ""); if (mysql_num_rows ($ r)> 0) (// ... diğer kod) // Olumlu cevap $ r = mysql_query ("WHERE durumu =" California "LIMIT 1" kullanıcısından 1 SEÇİN); if (mysql_num_rows ($ r)> 0) (// ... diğer kod)

2. Sorgu önbelleğini işleyerek veritabanı ile çalışmanın optimizasyonu

Çoğu MySQL sunucusu, sorgu önbelleğe alma özelliğini destekler. Veritabanı motorunun sorunsuz bir şekilde ele aldığı en etkili performans artırıcı yöntemlerden biridir.

Aynı istek birkaç kez yürütüldüğünde, sonuç önbellekten alınır. Tüm tabloları tekrar işlemek zorunda kalmadan. Bu, süreci önemli ölçüde hızlandırır.

// sorgu önbelleği desteklenmiyorsa $ r = mysql_query ("Kullanıcı adını SEÇ WHERE signup_date> = CURDATE ()"); // önbellek destekleniyor! $ bugün_tarihi = tarih ("E-a-gün"); $ r = mysql_query ("NEREDE kayıt_tarihi kullanıcısından kullanıcı adını SEÇ> =" $ bugün_tarihi "");

3. Arama alanlarının indekslenmesi

Dizinler yalnızca birincil veya benzersiz anahtarlara atanmak için tasarlanmamıştır. Tablonuzda aradığınız sütunlar varsa, onları dizine eklemeniz neredeyse zorunludur.

Tahmin edebileceğiniz gibi, bu kural arama dizesinin bir kısmı için de geçerlidir: örneğin "last_name LIKE‘% '" gibi. Bir satırın başında arama yaparken, MySQL o sütunu indeksleyebilir.

Ayrıca ne tür sorguların normal dizinleri kullanamayacağını da anlamalısınız. Örneğin, bir kelime ararsanız (örneğin, "WHERE post_content LIKE'% domates% ""), normal bir dizin kullanmak sizin için işe yaramaz, bu durumda MySQL tam eşlemeli arama kullanmak veya oluşturmak daha iyidir. kendi indeksiniz.

4. Birleştirme sırasında aynı türdeki sütunları indeksleme ve kullanma

Uygulamanız çok sayıda birleştirme sorgusu içeriyorsa, katıldığınız her iki tablodaki sütunların dizine alındığından emin olmanız gerekir. Bu, MySQL'in dahili birleştirme işlemlerinin optimizasyonunu etkiler.

Ayrıca, birleştirilmekte olan sütunlar aynı türde olmalıdır. Örneğin, bir tablodan DECIMAL sütunu ve diğerinden bir INT sütununu birleştiriyorsanız, MySQL dizinlerden en az birini kullanamaz.

Birleştirilen sütunların karşılık gelen dizeleri için karakter kodlaması bile aynı türde olmalıdır.

// benim durumumdaki şirketleri arıyorum $ r = mysql_query ("kullanıcılardan şirket_adı SEÇİN SOL şirketlere KATILIN (users.state = şirketler.state) WHERE users.id = $ user_id"); // her iki durum sütunu da indekslenmeli // ve her ikisi de aynı tipte olmalı ve karşılık gelen dizgiler için aynı karakter kodlamasına sahip olmalı // veya MySQL tüm tabloyu taramak zorunda kalacak

5. Mümkün olduğunda SELECT * gibi sorgular kullanmayın

Bir sorgu sırasında tabloda ne kadar çok veri işlenirse, sorgunun kendisi o kadar yavaş yürütülür. Disk işlemlerinde zaman kaybedilir. Ayrıca veritabanı sunucusu web sunucusundan ayrıldığında, sunucular arasında veri aktarımında gecikmeler yaşanmaktadır.

// istenmeyen sorgu $ r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Hoş Geldiniz ($ d [" kullanıcı adı "])"; // aşağıdaki kodu daha iyi kullanın: $ r = mysql_query ("Kullanıcı adını SEÇ WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Hoş Geldiniz ($ d [" kullanıcı adı "])";

6. Lütfen ORDER BY RAND () sıralama yöntemini kullanmayın

Bu, ilk başta iyi görünen numaralardan biridir ve birçok acemi programcı buna bayılır. Bu filtreyi sorgularınızda kullanmaya başladığınızda, kendinize nasıl bir tuzak kurduğunuzu bilemezsiniz.

Arama sonuçlarınızda belirli dizeleri gerçekten sıralamanız gerekiyorsa, bunu yapmanın çok daha etkili yolları vardır. Diyelim ki sorgunuza ek kod eklemeniz gerekiyor, ancak bu tuzak bunu yapmanızı engelliyor, bu da veritabanı büyüdükçe işleme verimliliğini azaltacaktır.

Sorun, MySQL'in tablodaki her satır için sıralama yapmadan önce (sunucunun hesaplama kaynaklarını kullanan) bir RAND () işlemi gerçekleştirmesidir. Bu durumda, yalnızca bir satır seçilecektir.

// hangi kod KULLANILMAMALIDIR: $ r = mysql_query ("Kullanıcı Adını SEÇ ORDER BY RAND () LIMIT 1"); // şu kodu kullanmak daha doğru olur: $ r = mysql_query ("Sayıyı SEÇ (*) KULLANICIDAN"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d - 1); $ r = mysql_query ("LIMIT $ rand, 1 kullanıcısından kullanıcı adını SEÇİN");

Böylece daha az arama sonucu seçeceksiniz ve ardından 1. paragrafta açıklanan LIMIT yöntemini uygulayabilirsiniz.

7. VARCHAR yerine ENUM türündeki sütunları kullanın

ENUM türündeki sütunlar çok kompakttır ve bu nedenle işlenmesi hızlıdır. Veritabanı içinde, içerikleri TINYINT formatında saklanır, ancak herhangi bir değeri içerebilir ve görüntüleyebilirler. Bu nedenle, içlerinde belirli alanları ayarlamak çok uygundur.

Aynı türden birkaç farklı değer içeren bir alanınız varsa, VARCHAR sütunları yerine ENUM kullanmak daha iyidir. Örneğin, yalnızca "etkin", "etkin değil", "beklemede", "gibi değerleri içeren "Durum" sütunu olabilir. süresi doldu" vesaire.

MySQL'in tablonun yapısını değiştirmeyi "önereceği" bir komut dosyası belirtmek bile mümkündür. VARCHAR alanınız olduğunda, sistem otomatik olarak sütun biçimini ENUM olarak değiştirmenizi önerebilir. Bu, PROCEDURE ANALYZE () işlevi çağrılarak yapılabilir.

IP adreslerini depolamak için İMZASIZ INT alanlarını kullanın

Birçok geliştirici bu amaç için VARCHAR (15) alanları oluştururken, IP adresleri veritabanında ondalık sayılar olarak saklanabilir. INT türündeki alanlar, 4 bayta kadar bilgi depolama olanağı sağlar ve aynı zamanda bunlara sabit bir alan boyutu atanabilir.

IP adresi 32 bit olduğundan kolonlarınızın UNSIGNED INT formatında olduğundan emin olmalısınız.

Sorgularda, IP adreslerini ondalık sayılara dönüştürmek için INET_ATON () parametresini ve tam tersi INET_NTOA () parametresini kullanabilirsiniz. PHP'nin diğer benzer işlevleri long2ip() ve ip2long() vardır.

8. Dikey kesit (bölme)

Dikey bölümleme, veritabanıyla çalışmayı optimize etmek için bir tablonun yapısının dikey olarak bölündüğü bir işlemdir.

Örnek 1: Diyelim ki, diğer şeylerin yanı sıra ev adreslerini içeren bir kullanıcı tablonuz var. Bu bilgi çok nadiren kullanılır. Tablonuzu bölebilir ve adres verilerini başka bir tabloda saklayabilirsiniz.

Böylece, ana kullanıcılar tablonuzun boyutu gözle görülür şekilde daha küçük olacaktır. Ve bildiğiniz gibi, daha küçük tablolar daha hızlı işlenir.

Örnek 2: Tablonuzda bir "last_login" alanınız var. Kullanıcı kendi kullanıcı adı ile her giriş yaptığında güncellenir. Ancak bir tabloda yapılan her değişiklik, diskte depolanan o tablonun sorgu önbelleğine yazılır. Ana kullanıcı tablonuza gelen isabet sayısını azaltmak için bu alanı farklı bir tabloya taşıyabilirsiniz.

Ancak bölümleme sonrası elde edilen her iki tablonun da gelecekte eşit sıklıkta kullanılmayacağından emin olmalısınız. Aksi takdirde, performansı önemli ölçüde düşürür.

9. Daha küçük sütunlar daha hızlıdır

Veritabanı motorları için disk alanı belki de darboğazdır. Bu nedenle, bilgileri daha kompakt bir şekilde depolamak, performans açısından genellikle faydalıdır. Bu, disk erişim sayısını azaltır.

MySQL Docs, farklı veri türlerini depolamak için bir takım gereksinimler içerir. Bir tablonun çok fazla kayıt içermemesi bekleniyorsa, birincil anahtarı INT, MEDIUMINT, SMALLINT ve hatta bazı durumlarda TINYINT gibi alanlarda saklamak için hiçbir neden yoktur. Tarih biçiminde saat bileşenlerine (saat: dakika) ihtiyacınız yoksa, DATETIME yerine DATE türündeki alanları kullanın.

Ancak, gelecekte büyümek için kendinize yeterli alan bıraktığınızdan emin olun. Aksi takdirde, bir noktada çökme gibi bir şey meydana gelebilir.

İndekslerin yönetimi, yani nasıl oluşturuldukları ve sürdürüldükleri, sql sorgularının performansını önemli ölçüde etkileyebilir.

Aşağıdaki optimizasyonlar sıklıkla uygulanabilir:

• kullanılmayan dizinleri kaldır
• kullanılmayan ve etkisiz dizinleri belirleyin
• endeksleri geliştirmek
• sql sorgularından tamamen kaçının!
• sql sorgularını basitleştirin
• ve önbelleğe alma seçeneklerinin büyüsü

DDL Sorgularını Birleştirme

Veri yapısını değiştiren sorgular genellikle tablo engellemedir. Tarihsel olarak, bir ALTER sorgusu yürütmek, disk üzerinde çok zaman alıcı ve pahalı olabilen tablonun yeni bir kopyasını oluşturmayı gerektiriyordu. Bu nedenle, küçük değişikliklere sahip üç sorgu yerine, birleştirilmiş bir sorgu yürütmek çok daha karlı. Bu, veritabanı yönetim görevlerinde önemli miktarda zaman kazandırabilir.

Yinelenen Dizinleri Kaldırma

Yinelenen dizinler iki nedenden dolayı zararlıdır: dizinin eksiksizliğini korumak için çift çalışma yapıldığından tüm veri değişikliği istekleri daha yavaş olacaktır. Ayrıca bu, veritabanının boyutu fiziksel olarak büyüdüğü için dosya sistemi üzerinde gereksiz bir yük oluşturur ve yedekleme oluşturma süresi ile kurtarma süresinin artmasına neden olur.

Birkaç basit koşul, yinelenen dizinlere yol açabilir. Örneğin, mysql, BİRİNCİL alanlarda bir dizine ihtiyaç duymaz.

Dizinlerden birinin sol tarafı diğer dizinle tam olarak aynıysa, yinelenen bir dizin de mevcut olabilir.

perkona-toolkit'in pt-duplicate-key-checker yardımcı programı, veritabanı yapınızı gereksiz dizinler için kontrol etmenin basit ve hızlı bir yoludur.

Kullanılmayan dizinleri kaldırma

Yinelenen oldukları için hiçbir zaman kullanılmayan dizinlere ek olarak, hiçbir zaman kullanılmayan yinelenmeyen dizinler olabilir. Bu tür dizinler, kopya dizinlerle aynı etkiye sahiptir. Standart mysql'de hangi dizinlerin kullanılmadığını belirlemenin bir yolu yoktur, ancak bazı sürümler, örneğin Google MySQL yamasını kullanırken benzer bir yeteneğe sahiptir.

Bu yamada bir numara tanıtıldı: SHOW INDEX_STATISTICS.

Ve sıradan mysql'de önce kullanılan tüm sql sorgularını toplamanız, çalıştırmanız ve yürütme planını izlemeniz, her durumda kullanılan indeksler hakkında bilgi toplamanız ve tek bir tabloya indirmeniz gerekir. Her durumda, bu ödüllendirici bir deneyim.

İndeks alanlarının optimizasyonu.

Performansı artırmak için yeni dizinler oluşturmaya ek olarak, ek tasarım optimizasyonlarıyla performansı artırabilirsiniz. Bu optimizasyonlar, özel veri ve alan türlerinin kullanımını içerir. Bu durumda kâr, diskte daha düşük bir yük ve RAM'e sığabilecek daha büyük bir dizin hacmidir.

Veri tipleri

Bazı türler, mevcut mevcut temellere göre ağrısız bir şekilde değiştirilebilir.

BÜYÜK vs INT

BİRİNCİL anahtar BÜYÜK OTOMATİK ARTIRMA olarak tanımlandığında, genellikle onu kullanmak için hiçbir neden yoktur. INT UNSIGNED AUTO_INCREMENT veri türü maksimum 4294967295'e kadar saklayabilir. Gerçekten bu sayıdan daha fazla kaydınız varsa, büyük olasılıkla farklı bir mimariye ihtiyacınız olacaktır.

BIGINT'ten INT UNSIGNED'e böyle bir değişiklikten, tablonun her satırı 2 kat daha az disk alanı kaplamaya başlar, ayrıca PRIMARY anahtarının kapladığı boyut 8 bayttan 4'e düşer.

Bu belki de oldukça acısız bir şekilde yapılabilecek en somut basit iyileştirmelerden biridir.

DATETIME ve TIMESTAMP karşılaştırması

Burada her şey basit: zaman damgası 4 bayt, tarih saat 8 bayt.

Mümkün olduğunda kullanılmalıdır, çünkü:

• ek veri bütünlüğü kontrolü
• böyle bir alan, 255 benzersiz değeri depolamak için yalnızca 1 bayt kullanır
• bu tür alanların okunması daha kolaydır :)

Tarihsel olarak, enum alanlarının kullanımı, tabanın enumdaki olası değerlerdeki değişikliklere bağlı olmasına neden olmuştur. Engelleyen bir DDL isteğiydi. MySQL 5.1'den beri, enum'a yeni değişkenler eklemek çok hızlıdır ve tablo boyutuyla ilgili değildir.

NULL vs NULL DEĞİL

Bir sütunun boş bir değer içerip içermediğinden emin değilseniz, onu NULL DEĞİL olarak tanımlamak en iyisidir. Böyle bir sütundaki dizin daha küçük ve işlenmesi daha kolay olacaktır.

Otomatik tür dönüşümleri

Alanları birleştirmek için bir veri türü seçtiğinizde, alandaki veri türü tanımsız olur. Bir satır içi dönüştürme kesinlikle gereksiz ek yük olabilir.

Tamsayı alanlar için İMZALANMIŞ ve İMZASIZ aynı olduğundan emin olun, değişken alan türleri için birleştirme sırasında kodlamanın dönüştürülmesi gereksiz bir iş olabilir, bu yüzden onları da kontrol etmelisiniz. Yaygın bir sorun, latin1 ve utf8 kodlamaları arasında otomatik dönüştürmedir.

Sütun türleri

Belirli veri türleri genellikle yanlış sütunlarda depolanır. Türün değiştirilmesi, özellikle bu sütunlar dizine dahil edildiğinde daha verimli depolama ile sonuçlanabilir. Bazı tipik örneklere bakalım.

IP adresi

Bir IPv4 adresi, yalnızca 4 bayt alan INT UNSIGNED alanında saklanabilir. IP adresi 12 bayt alan VARCHAR (15) alanına kaydedildiğinde genellikle bir durum oluşur. Bu değişiklik tek başına boyutu 2/3 oranında azaltabilir. INET_ATON () ve INET_NTOA işlevleri, ip adresli bir dize ile sayısal bir değer arasında dönüştürme yapmak için kullanılır.

Giderek daha saldırgan hale gelen IPv6 adresleri için, 128 bitlik sayısal değerlerini BINARY (16) alanlarında saklamak ve insan tarafından okunabilir format için VARCHAR kullanmamak önemlidir.

md5 alanlarını CHAR (32) olarak saklamak yaygın bir uygulamadır. VARCHAR (32) alanı kullanırsanız, her değer için ek bir satır uzunluğu ek yükü eklersiniz. Ancak, md5 dizesi onaltılık bir değerdir - ve UNHEX () ve HEX () işlevleri kullanılarak daha verimli bir şekilde saklanabilir. Bu durumda veriler BINARY (16) alanlarında saklanabilir. Bu basit eylem, alan boyutunu 32 bayttan 16 bayta indirecektir. Benzer bir ilke, herhangi bir onaltılık değere uygulanabilir.

Ronald Bradford'un kitabından uyarlanmıştır.