Optimizare MySQL

Salut. Este nevoie de optimizarea interogarilor mysql atunci cand traficul si incarcarea pe servere reclama din plin acest lucru.

Este si o chestiune de probitate profesionala. La urma urmelor, este mai usor sa scrii query-uri bune decat proaste, cu conditia sa te pregatesti, sa studiezi acest aspect al ingineriei web …

… chiar daca traficul este mic, serverele functioneaza “in largul” lor …

Optimizare MySQL. Interogari optimizate

Recent am avut de optimizat o platforma custom care functiona destul de sincopat si cu blocari la trafic mare (si intr-adevar traficul este foarte mare de ~ 170.000 unici / zi pe aceste site-uri despre care discutam)

E limpede ca miza este importanta. Cu aceleasi hardware avute la dispozitie se poate suporta lejer un astfel de trafic sau dimpotriva sa se blocheze sistemul. Aceste rezultate tin printre altele si de arhitectura aleasa pe server, optimizarea bazei de date, a interogarilor facute din web server, a structurii bazei de date.

Ma voi opri asupra optimizarii interogarilor pe baza de date (MySQL) si fac urmatorele sugestii. Pot fi luate ca principii care trebuie urmarite si aplicate. (in exemplul la care m-am referit, am aplicat exact aceste principii de baza):

1. selectati doar numarul necesar de inregistrari de care aveti nevoie. Atentie, daca rulati interogarea cu EXPLAIN SELECT veti obtine si informatii legate de numarul de inregistrari necesare motorului MySQL pentru a le inspecta astfel incat sa returneze numarul final de inregistrari. Daca aceste doua valori sunt apropiate, este foarte bine si spre acest rezultat vor trebui indreptate eforturile dvs ca programator.
2. selectati doar coloanele de care aveti efectiv nevoie. EXPLAIN SELECT va da informatii legate de modalitatea in care sunt calculate si procesate inregostrarile din setul de inregistrari obtinute din baza de date in timpul interogarii create de dvs si aplicate MySQL. Astfel, vom afla daca setul de date a fost procesat in memorie sau a fost necesara crearea unor fisiere temporare pe disk, ca urmare a marimii mari a setului de date, fata de capacitatea de memorie a serverului alocata pentru aceste procesari MySQL. Daca selectam mai putine coloane, marimea setului de date va fi mai mic, poate atat cat trebuie ca tot procesul sa decurga in memorie.
3. reduceti numarul de interogari. Daca nu aveti nevoie de o interogare nu o executati. Am intalnit situatii in care pentru anumite widget-uri afisate in pagina se executau interogari specifice sectiunilor cu paginatie. In astfel de sectiuni se obtine din pagina de date numarul total de inregistrari (articole) pentru a crea widgetul de paginatie (il stiti, cel afisat de obicei in partea de jos a unei pagini care permite deplasarea de pe o pagina pe alta a setului de rezultate). Astfel, pentru a efectua

     SELECT COUNT(*) FROM `tabel` WHERE `category_id`=325

   motorul MySQL parcurge toate inregistrarile care au category_id=325. Acestea pot fi cu zecile de mii (site-ul de care am amintit are astfel de valori)

4. folositi indecsi pe coloanele potrivite (cele prezente in clauzele WHERE si ORDER BY). Atentie, am avut o astfel de tentatie sa plasam un index pe  coloana `numar_afisari` aflata in ORDER BY. Nu va sfatuiesc deoarece pe un site cu trafic mare, aceasta coloana este actualizata in front-end la fiecare afisare de pagina (in principiu). Actualizarea acestei coloane implica si actualizarea indexului pe aceasta coloana. Pe un tabel cu 1.000.000 de inregistrari este sentinta de blocare a serverelor. Asadar atentie pana unde mergeti cu optimizarile, amintiti-va proverbul (uneori) Mai bine este vrajmasul binelui! 
5. Reproiectati query-urile. Exemplu: inlocuiti FIND_IN_SET(`coloana`, $vector) cu IN() in anumite conditii. Exemplu: SELECT m.`id` FROM `tabel1` WHERE `id` IN (SELECT  `id` FROM `tabel2` WHERE FIND_IN_SET(`coloana`, $vector_valori)  LIMIT 3) ORDER BY `numar_afisari`”;
6. Atentie: MySQL executa mai intai interogarea externa si apoi subquery-ul. Asadar, chiar daca punem LIMIT 3 pe subquery, query-ul principal obine sa zicem 180.000 de rows si subquery-ul se va executa pentru fiecare din aceste inregistrari, iar la final isi va retine 3 din setul final. Atentie in acest sens la query-ul dat exemplu mai sus. Acest gen de query omoara serverul la trafic mare. Asadar se poate sparge in 2 query-uri separate. Aici mai mult inseamna mai bine: mai intai execut subquery-ul si apoi query-ul pricipal, in care partea WHERE `id` IN (SELECT  `id` FROM `tabel2` WHERE FIND_IN_SET(`coloana` … va deveni WHERE `id` IN ($vector_ids). Acest lucru are si un alt impact: EXPLAIN SELECT va va spune ca executati un tip de acces la baza de date de tip “range” (interval) care dupa cel de tip const (la un query care contine WHERE `id`=421) si de tip index este foarte eficient si de urmarit in proiectarea queri-urilor.
7. Folositi baze de date NoSql (Ati auzit de Elastic Search? Este uimitor! Incercati-l)
8. Faceti cache la tot ce este regenerat in aceeasi forma (Memcached?)
9. Renuntati la query-uri definitiv si inlocuiti cu cod html fixat in surse. Dar deja aici intram in optimizarea paginii la destinatie (clientul browser) si este un alt subiect

As vrea sa subliniez ca acesta nu se vrea un material de umplutura. Ci as vrea sa certifice – in final – ca aplicand aceste masuri simple (?) – dar de bun simt – se pot obtine rezultate impresionante. Pentru site-ul descris, daca inainte de astfel de optimizari aveam load-uri foarte mari si blocari constante ale serverelor la traficul mare amintit – cu tot sistemul de servere proxy prezent in solutia tehnica de gazduire – dupa optimizarile efectuate load-ul a scazut dramatic, paginile se incarca super repede si grijile s-au spulberat in bataia vantului primavaratic …. 🙂 NU, nu avenit inca primavara  dpdv calendaristic, dar nu e nici ea departe!

Programare usoara!

Sector web

4.