Optimizare MySQL: identificarea și eficientizarea interogărilor care nu folosesc indecși

Optimizarea serverului de baze de date MySQL reprezintă unul dintre cele mai importante aspecte pentru performanța, viteza și stabilitatea oricărui site web. O bază de date eficientizată reduce considerabil consumul de resurse și te poate scuti de costuri suplimentare mari pentru serviciile de găzduire.

Atunci când optimizez un server MySQL, una dintre primele directive pe care le configurez în fișierul de setări (my.cnf sau my.ini) este activarea jurnalizării comenzilor care nu utilizează indecși:

log-queries-not-using-indexes

Ce rol are directiva log-queries-not-using-indexes?

Pe scurt, această opțiune adaugă în jurnalul de interogări lente (slow query log) toate comenzile SQL care realizează o scanare completă a tabelelor (full table scan), adică acele interogări care nu se bazează pe niciun index definit.

Cu alte cuvinte, pentru a identifica rezultatele căutate, MySQL trebuie să parcurgă fiecare rând din tabel și să verifice dacă sunt îndeplinite condițiile din clauza WHERE. Dacă interogarea este efectuată pe un tabel cu 100.000 de înregistrări, vei observa că timpul de răspuns și încărcarea procesorului (CPU) cresc extrem de mult comparativ cu situația în care există un index optim.

Cum verificăm planul de execuție cu comanda EXPLAIN

Dar ce facem dacă nu avem acces direct la fișierul de configurare my.cnf sau dacă dorim să inspectăm direct o interogare specifică?

Întrebarea firească este: cum știm dacă o interogare folosește indexul optim?

Pentru a răspunde la această întrebare, MySQL oferă instrucțiunea EXPLAIN. Prin intermediul acesteia putem analiza modul exact în care optimizatorul bazei de date execută o interogare:

EXPLAIN SELECT id, username FROM users WHERE id = 3;

Rezultatul returnat este un set de date structurat cu detalii tehnice despre planul de execuție, unde două coloane sunt de o importanță critică:

• possible_keys: indică indecșii disponibili pe care MySQL ar putea să îi aleagă pentru a căuta înregistrările;
• key: arată indexul efectiv selectat și utilizat de MySQL în execuția interogării (sau NULL dacă niciun index nu a fost considerat potrivit).

Prin analizarea atentă a acestor indicatori, puteți depista rapid blocajele de performanță și adăuga indecși adecvați pe coloanele intens utilizate în filtrare sau ordonare. În articolele viitoare voi detalia o clasă dedicată pentru depanarea și profilarea facilă a interogărilor MySQL.