Curs Sql – Top 10 Best Practices in MySql

In multe aplicatii web, baza de date reprezinta veriga slaba din punct de vedere al performantei si al securitatii.

La cursul Baze de date Sql pe care-l organizam, incercam sa rezolvam aceste probleme astfel incat aplicatiile pe care le dezvolti  sa fie cat mai eficiente si mai sigure.

In acest post iti vom explica 10 recomandari pentru o crestere a eficientei bazelor de date MySql. AcMySQLestea sunt simplu de realizat, nu necesita un efort foarte mare, iar rezultatul este deosebit.

 

1. Foloseste LIMIT 1 cand selectezi un singur rand.

De multe ori ca programator, stii ca un anumit SELECT va returna un singur rand. De exemplu cand selectezi o tara sau o valuta. Daca adaugi LIMIT 1 batabase engine se va opri din cautat dupa ce a gasit un rezultat. Altfel va cauta in tot tabelul.

 

// selectam utilizatorii din Romania
 
// varianta nerecomandata:
$result = mysql_query("SELECT * FROM users WHERE country = 'RO'");
if (mysql_num_rows($result) > 0) {
    // ...
}
 
// varianta recomandata:
$result= mysql_query("SELECT * FROM users WHERE country = 'RO' LIMIT 1");
if (mysql_num_rows($result) > 0) {
    // ...
}

 

2.  Evita sql-ul de tipul SELECT *

Cu cat se citesc mai multe campuri din tabel, cu atat vom avea un query mai ineficient din punct de vedere al performantei. In aceasta capcana cad inclusiv programatorii mai avansati, deoarece este mai usor sa foloseasca SELECT * in sql-uri, decat sa specifice fiecare coloana in parte. 

Specifica deci fiecare coloana in parte in SQL!

 

// de evitat
$result = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($result);
echo "Welcome {$d['username']}";
 
 
// recomandat, sql mult mai rapid, folosim inclusiv LIMIT 1 deoarece returneaza mereu un singur username:
$result = mysql_query("SELECT username FROM user WHERE user_id = 1 LIMIT 1");
$d = mysql_fetch_assoc($result);
echo "Welcome {$d['username']}";

 

 

3. Foloseste ENUM in loc de VARCHAR sau alt tip de date, atunci cand se poate.

Intern, MySql salveaza tipul de date ENUM ca TINYINT chiar daca de cele mai multe ori el reprezinta un string.

Foloseste ENUM atunci cand ai cateva valori posibile pentru un anumit camp, cum ar fi de exemplu "currency" cu valorile "ron", "usd", "eur" sau "gbp".

 

Folosirea tipului de date ENUM creste performanta serverului MySql!

 

4. Evita folosirea campurilor NULL.

Foloseste mereu campuri NOT NULL, care trebuie sa aiba mereu o valoare. In cazul stringurilor poate fi un empty string, iar in cazul lui INT poate fi 0. Cumpurile NULL adauga complexitate si ocupa de asemenea mai mult spatiu.

In documentia oficiala a serverului MySql este precizat:

"NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte."

 

 

5. Foloseste tipul de date UNSIGNED INT pentru coloanele in care salvezi IP-uri de tipul IPv4, in loc de VARCHAR.

Astfel se ocupa mai putin spatiu si creste performanta bazei de date.

Foloseste apoi functiile MySql INET_ATON() pentru a converti un IP intr-un INTEGER si INET_NTOA() pentru a converti un INTEGER intr-un IP.

Observatie: IPv6 ocupa 128 biti si nu mai poate fi salvat in UNSIGNED INT.

 

6. Alege Storage Engine potrivit.

In MySql cele mai folosite Storage Engine sunt MyISAM si InoDB.

MyISAM este eficient pentru operatii de citire, dar nu pentru cele de scriere.  Pentru baze de date mici, MyISAM este mai potrivit decat InoDB.

In cazul bazelor de date mari, sau daca se doreste folosirea tranzactiilor, InoDB este Storage Engine-ul de preferat.

 

7.  Foloseste QUERY CACHE si optimizeaza SQL Query pentru acesta.

Acest feature este util pentru optimizarea SELECT-urilor pe tabele al caror continut nu s-a modificat. Multe tabele au continut care se modifica rar, foarte rar.  In momentul in care continutul tabelului nu s-a modificat, informatia este returnata din cache fara a se cauta efectiv in tabela.

De multe ori programatorul nu tine cont de optiunea QUERY CACHE si scrie cod care dezactiveaza cachingul.

 

// query cache nu va merge deoarece se foloseste functia CURDATE() . Serverul MySql va dezactiva query cache deoarece rezultatul functiei poate fi mereu altul.
$result = mysql_query("SELECT username FROM users WHERE signup_date >= CURDATE()");
 
// In acest caz query cache merge! Prelum data curenta intr-o variabila PHP
$today = date("Y-m-d");
$result = mysql_query("SELECT username FROM users WHERE signup_date >= '$today'");

 

 

 

 

 

 

Pentru a activa QUERY CACHE trebuie configurat serverul MySql ca atare. Daca folosim Linux, in fisierul de configurare al serverului, care este /etc/mysql/my.cnf trebuie sa avem urmatoarele optiuni:

 

query_cache_limit = 1M

query_cache_size = 16M

query_cache_type = 1

 

Daca optiunea query_cache_size este mai mare ca zero, optiunea query_cache_type influenteaza la ce se face cache.

0 – dezactiveaza caching
1 – face caching la toate selecturile mai putin cele care sunt de genul SELECT SQL_NO_CACHE
2 – face caching la toate selecturile de genul SELECT SQL_CACHE

query_cache_limit stabileste dimensiunea maxima pt. un cache la un anumit query. Default este 1MB.

 

8. Foloseste in fiecare tabel o cheie primara de tipul PRIMARY KEY, AUTO_INCREMENT, UNSIGNED INT.

Chiar daca in anumite tabele putem folosi ca si cheie primara alte coloane cu valori unice precum "username" sau "email", se recomanda sa avem o coloana speciala pentru PRIMARY KEY de tipul UNSIGNED INT.

Modul in care MySql Server opereaza intern este in favoarea acestui tip de organizare.

 

9. Foloseste tipurile de date corecte.

Daca pentru tabele cu putine inregistrari, diferanta dintre INT si TINYINT nu se observa ca si performanta, cand tabelele ajung sa aiba milioane de inregistrari, modul in care am ales tipul de date poate face diferenta.

Foloseste tipuri de date conform cu realitatea datelor salvate in tabele. Foloseste ENUM in loc de VARCHAR, TINYINT sau UNSIGNED INT in loc de INT, VARCHAR(16) in loc de VARCHAR(128) cand e cazul etc.

Ca si regula generala, foloseste cele mai mici tipuri de date posibile.

 

10. Foloseste corect JOIN-urile.

In aproape orice aplicatie vei folosi JOIN-uri pentru a extrage date din mai mult decat o singura tabela. JOIN-urile sunt mari consumatoare de resurse.

Exista 2 recomandari aici:

a) coloana pe care se face join sa fie indexata in tabelele pe care facem join. Serverul MySql va optimiza operatia astfel.

b) coloanele pe care facem join sa fie de acelasi tip de date. Sa nu fie de exemplu una INT si alta DECIMAL.

 

Aceste 10 recomandari reprezinta doar un punct de plecare pentru eficientizarea operatiilor cu baze de date MySql.

 

Daca vrei sa devii un bun administrator, operator sau programator de aplicatii web care folosesc Sql, te asteptam la cursul baze de date SQL organizat de Crystal Mind Academy! Vei invata de la traineri entuziasti si bine pregatiti secretele care te vor ajuta sa dezvolti baze de date sau aplicatii, eficiente si sigure!