Optimizarea bazelor de date se face pe mai multe nivele, pornind de la un design puternic si terminându-se cu analiza query-urilor pentru optimizări la nivelul aplicaţiei. ROHOST oferă MySQL, un server de baze de date de natură relaţională stabil, cu performanţă ridicată şi cu facilităţi avansate. Vom avea în vedere in special MySQL, dar o mare parte din recomandările de mai jos se aplică oricărui tip de baze de date relaţionale.

De departe cea mai importantă optimizare de structură a bazei de date este indexarea coloanelor folosite la căutări/sortări. Indexarea presupune adăugarea unei structuri (index) pe lângă o tabelă, care păstrează date despre ordinea înregistrărilor/rândurilor din coloanele pentru care a fost configurată. La orice căutare, ordonare, ştergere, etc. după coloana respectivă, serverul de baze de date va folosi indexul pentru a-şi uşura munca şi a căuta rapid între înregistrări.

• Exemplu: pentru o tabelă conţinând coloanele:

o căutare pentru angajaţii cu vârsta între X şi Y ani va fi ajutată imens de prezenţa unui index pentru coloana varsta. De asemenea, dacă păstrăm o altă tabelă cu datele de contact pentru fiecare angajat, care conţine id_angajat, sau dacă această coloană este folosită pentru identificarea unui angajat (de obicei da), putem numi o greşeală lipsa indexării pentru coloana id_angajat.

Indexarea adaugă un plus de spaţiu folosit pe lângă cel al tabelei, dar mărimea este în general mică comparativ cu spaţiul ocupat de tabelă. Avantajele indexării sunt multiple si balansează clar spaţiul suplimentar ocupat:

Recomandăm citirea articolului Cum foloseşte MySQL indecşii pentru explicaţii mai în adâncime asupra modului de funcţionare a indexării.

• returnarea aproape instantă a rândurilor căutate după una sau mai multe coloane indexate, folosind operatori gen =, >, <, ⇐.
• consumul redus de resurse prin evitarea parcurgerii întregii tabele;
• viteza crescută exponenţial în cadrul interogărilor care conţin uniri(JOIN) de coloane.

Cel mai uşor mod de a adăuga un index pentru una/mai multe coloane dintr-o tabelă MySQL este prin intermediul folosirii utilitarului phpMyAdmin, opţiunea Structure pentru o anumită tabelă, butonul Add index, urmând alegerea coloanei (coloanelor) pentru care se doreşte indexarea.

Altă soluţie este modificarea structurii bazei de date ca urmare a unei interogări de tipul ALTER TABLE sau echivalentul MySQL CREATE INDEX.

Aşa cum explică şi articolul prezentat mai sus, în cazul căutării într-un câmp de tip text, indexul este folosit doar în cazurile în care structura căutată conţine câteva caractere invariabile la început.

• Exemplu:

SELECT * FROM angajati WHERE nume LIKE 'Ghe%_ghe%';

va folosi indexarea pe coloana nume, pentru primele 3 caractere, dar

SELECT * FROM angajati WHERE nume LIKE '%Gheorghe%';

nu va folosi indexarea, datorita variabilităţii caracterelor înainte de Ghe.

Dacă situl dv. foloseşte căutările variabile în text, soluţia este obicei folosirea unui index FULLTEXT pentru coloanele respective, asociată cu interogarea tip FULLTEXT cu MATCH()… AGAINST() (fără folosirea expresiei LIKE).

	La momentul actual (februarie 2008) indexarea FULLTEXT se face doar pentru tabele MyISAM, dacă folosiţi tabele InnoDB este recomandată crearea unei tabele separate MyISAM pentru căutări în text.

Normalizarea este o optimizare de structură a bazei de date, care presupune reducerea/eliminarea redundanţei prin separarea logică a coloanelor in tabele diferite, care combinate prin unire(JOIN) produc acelaşi efect ca şi tabela iniţială, dar păstrează un ordin de mărime similar pentru toate coloanele existente.

• Exemplu: o tabelă cu următoarele coloane:

poate fi înlocuită cu cu 2 tabele:

şi

unde în tabela a doua se pot stoca mai multe adrese de email pentru acelaşi angajat, fara să existe o limită pentru ele şi făra să existe neapărat o adresă de email definită pentru un angajat.

Efecte pozitive:

• economia de spaţiu;
• risc mai scăzut de inconsistenţe logice în baza de date;
• eliminarea erorilor logice de inserţie/ştergere, datorate unor câmpuri prezente în aceeaşi tabelă, dar separate logic.

În teorie normalizarea exagerată poate duce la o pierdere de performanţă datorată numărului ridicat de JOIN-uri necesare pentru o interogare (query) mai complexă. Totuşi noile recomandări MySQL indică promovarea JOIN-urilor unde schema bazei de date impune normalizarea, obţinându-se chiar viteze superioare de-normalizării. Concluzia: nu ocoliţi normalizarea.

	Este extrem de importantă indexarea coloanelor supuse JOIN-ului in urma normalizării ( în exemplul prezentat mai sus: id_angajat, id_email ), precum şi folosirea unui tip numeric pentru aceste coloane.

Paginarea nu este neapărat o optimizare a bazei de date, cât o cerinţă a folosirii acesteia. Limitarea rezultatelor afişate într-o pagină web este impusă de caracterul dinamic al bazei de date, care face ca numărul de rezultate să crească foarte uşor peste cel confortabil de citit în cadrul unei pagini. Soluţia: limitarea numărului de rezultate returnate din interogare la cel afişat în pagină, în paralel cu posibilitatea de navigare pentru a vizualiza mai multe rezultate.

Câteva articole despre paginare:

• http://php.about.com/od/phpwithmysql/ss/php_pagination.htm
• http://www.goodphptutorials.com/out/Simple_PHP_MySQL_Pagination
• http://www.phpsimplicity.com/tips.php?id=1

	Limitaţi întotdeauna în cadrul interogărilor numărul de rânduri returnate la cel afişat în pagină, folosind LIMIT.

• Nu folosiţi coloane de tip CHAR, VARCHAR, TEXT pentru a stoca date de tip INTEGER (pt. exemplul de mai sus: id_angajat ar trebui să fie întotdeauna de tip INTEGER ).
• Pentru tabelele care sunt schimbate des şi conţin câmpuri text de lungime variabilă (VARCHAR, TEXT), utilizaţi din când în când OPTIMIZE TABLE.

În cazul în care consumul de resurse realizat de interogările la baza de date se extinde foarte mult peste limitele normale în cazul găzduirii de tip shared, veţi primi o notificare în acest sens. Paşii de mai jos sunt un ghid acest sens.

De obicei notificarea pentru baza dv. de date este însoţită de un log (fişier text) care conţine interogările lente (slow queries) înregistrate pe server în ultimele zile. Ele vor conţine pentru fiecare slow query înregistrat, pe lângă textul său:

În funcţie de fiecare variabilă şi importanţa ei, vom examina pe rând soluţiile posibile:

	Pentru eliminarea posibilelor probleme, este important ca orice modificare adusă bazei de date să fie efectuată şi testată întâi la nivel local, nu în mediu final (de producţie) pe serverul de găzduire.

Odată modificările efectuate pentru baza de date, este recomandată testarea performanţelor:

• Folosirea EXPLAIN pentru desfăşurarea procesului pe care serverul de MySQL îl parcurge când primeşte o interogare, şi optimizarea indexării în urma examinării parametrilor.
• Înregistrarea timpilor de răspuns pentru funcţia de interogare, din aplicaţia web. Dacă aceştia variază liniar în funcţie de numărul de rânduri din tabelă, este nevoie de indexare.
• Folosirea phpMyAdmin pentru examinarea timpului de execuţie, afişat după fiecare interogare realizată din secţiunea SQL.