Optimizare Query MySQL

Utilitarul de optimizare a interogărilor din MySQL are numeroase scopuri, dar prima pala sa menire este de a folosi indexurile ori de câte ori este posibil, precum si de a folosi indexul cel mai restrictiv, pentru a elimina cât mai multe rânduri într-un timp cât scurt.
Poate părea ciudat, deoarece, atunci când emiteţi instrucţiuni SELECT, scopul dumneavoastră este de a găsi rânduri, nu de a le elimina. Motivul pentru care utilitarul optimizare funcţionează în acest mod este următorul:
- cu cât pot fi scoase din disc rânduri într-o manieră mai expeditivă, cu atât pot fi găsite mai rapid rândurile care satisfac criteriile de căutare.
Interogările pot fi prelucrate mai rapid dacă testele cele restrictive se pot efectua primele.

Să presupunem că aveţi o interogare care testează doua coloane, ambele coloane fiind dotate cu index:

WHERE coloana1 = "o valoare" AND cooana2 = "o alta valoare"

Să presupunem că testul aplicat asupra coloanei l găseşte 900 de rânduri, că testul aplicat asupra coloanei 2 găseşte 300 de valori, şi că ambele teste reuşesc în cazul a 30 de rânduri.
Dacă testaţi mai întâi coloana l, trebuie să examinaţi 900 de rânduri pentru a le găsi cele 30 care corespund şi valorii din coloana 2. Asta înseamnă 870 de teste ratate.
Daca testaţi mai întâi coloana 2, trebuie să examinaţi numai 300 de rânduri pentru a le găsi pe cele 30 care de asemenea corespund valorii din coloana 1. Numărul testelor ratate în; caz este de 270, ceea ce implică mai puţine calcule si operaţii de intrare-ieşire cu disc.

Puteţi ajuta utilitarul de optimizare să folosească indexurile folosind următoarele îndrumari:
- Comparaţi coloane de acelaşi tip.
Când folosiţi coloane indexate în comparaţii, utilizaţi coloane de acelaşi tip.
De exemplu, CHAR (10) este considerat identic VARCHAR(10), dar diferit de CHAR(12) sau
VARCHAR(12).
INT este diferit de BIGINT
Tipurile de coloane identice vă vor oferi performanţe superioare tipurilor de coloane diferite.

• Nu folosiţi caractere de înlocuire la începutul unui model LIKE. Uneori caută şiruri folosind o clauză WHERE de forma următoare:

WHERE nume_coloana LIKE "%sir

Aceasta este o operaţie corectă dacă doriţi să găsiţi şirul şir indiferent unde aplicati acesta în interiorul coloanei. Dar nu inseraţi caracterul % de ambele părţi doar din obişnuinţă.
Dacă într-adevăr căutaţi şirul numai atunci când acesta apare la începută coloanei, atunci nu mai scrieţi primul caracter %.
De exemplu, în cazul în care caut într-o coloană care conţine nume de familie acele nume care încep cu "Mac", scriu clauza WHERE astfel:

WHERE nume LIKE "Mac%"

Folosiţi coloane cu lungime fixă, nu coloane cu lungime variabilă. Acest lucru este valabil mai ales pentru tabelele care sunt modificate frecvent si care sunt, ca atare, supuse la fragmentare.
De exemplu, transformaţi toate coloanele caracter din VARCHAR in CHAR.
Compromisul este acela că tabelul dumneavoastră va folosi mult spaţiu, dar, dacă vă puteţi permite spaţiul suplimentar, rândurile cu lungime fixa pot fi prelucrate mai rapid decât rândurile cu lungime variabilă.

- Nu folosiţi coloane mai lungi atunci când se pot utiliza şi coloane mai scurte.
Dacă folosiţi coloane CHAR de lungime fixă, nu le faceţi inutil de lungi.
Dacă valoarea cea mai scurtă pe care o stocaţi într-o coloană este de 40 caractere, nu o declaraţi sub forma CHAR(255); declaraţi-o sub forma CHAR(40).
Dacă puteţi folosi MEDIUMINT în loc de BIGINT, tabelul dumneavoastră va fi mai mic (ceea ce implică mai puţine operaţii de intrare-ieşire cu discul), iar valorile vor fi prelucrate mai rapid în cadrul calculelor.

- Declaraţi coloanele ca NOT NULL. Astfel, obţineţi o viteză de prelucrare mai mare şi aveţi nevoie de un spaţiu de stocare mai redus. De asemenea, va simplifica uneori interogările, deoarece nu trebuie să trataţi NULL ca pe un caz special.

- Luaţi în calcul utilizarea de coloane ENUM.
Dacă aveţi o coloană şir care conţine numai un număr limitat de valori distincte, luaţi în considerare conversia acesteia la o coloană ENUM. Valorile ENUM pot fi prelucrate rapid, deoarece sunt reprezentate intern sub formă de valori numerice.

- Folosiţi PROCEDURE ANALYSE().
Dacă dispuneţi de MySQL versiunea 3.23 sau de o versiune ulterioară, rulaţi funcţia PROCEDURE ANALYSE() pentru a primi informaţii despre coloanele din tabelul dumneavoastră:

SELECT * FROM nume_tabel PROCEDURE_ANALYSE()
SELECT * FROM nume_tabel PROCEDURE_ANALYSE(16,256)

Una dintre coloanele datelor de ieşire este o sugestie a tipului optim de coloană pentru fiecare dintre coloanele tabelului dumneavoastră. Cel de-al doilea exemplu indică funcţiei PROCEDURE ANALYSE() să nu sugereze tipuri ENUM care conţin mai mult de 16 valori sau care ocupă mai mult de 256 octeţi (puteţi modifica valorile după cum doriţi). Fără asemenea restricţii, datele de ieşire pot fi foarte lungi declaraţiile ENUM sunt dese ori dificil de citit.

în funcţie de datele de ieşire ale funcţiei PROCEDURE ANALYSE(), puteţi descoperi că tabelul dumneavoastră poate fi modificat pentru a beneficia de un tip mai eficient.
Folosiţi ALTER TABLE dacă doriţi să modificaţi un tip de coloană.

- Folosiţi instrucţiunea OPTIMIZE TABLE pentru tabele supuse la fragmentare.
Utilizarea sistematică instrucţiunii OPTIMIZE TABLE contribuie la împiedicarea degradării performanţele tabelului.