Interogari MySQL lente: cum afli daca problema este query-ul, indexul sau volumul de date

Un raport care raspundea rapid cand aplicatia avea putine date poate deveni frustrant dupa luni sau ani de utilizare. Cresterea bazei de date poate scoate la iveala un query care citeste mai multe randuri decat are nevoie, un index care nu se potriveste filtrelor sau o sortare costisitoare. Uneori insa, interogarea asteapta dupa o alta tranzactie sau aplicatia trimite foarte multe interogari mici.

De aceea, diagnosticul incepe cu masurarea si planul de executie, nu cu adaugarea imediata a unui index sau marirea serverului. Scopul este sa afli unde se consuma timpul si ce schimbare merita testata pe date reprezentative.

1. Masoara simptomul inainte sa modifici interogarea

Incepe prin a afla ce operatiune a incetinit si in ce conditii. Noteaza durata, volumul aproximativ de date, filtrele folosite, frecventa apelului si daca intarzierea apare constant sau doar in perioade aglomerate. Un raport accesat de un singur utilizator o data pe zi are un profil diferit de o interogare chemata la fiecare incarcare a unui ecran.

Daca ai acces la configuratia serverului, slow query log poate ajuta la gasirea instructiunilor care depasesc pragul configurat. Acesta este un punct de pornire, nu o explicatie completa: trebuie sa corelezi query-ul cu ecranul sau procesul care l-a lansat, volumul de date si activitatea simultana.

Compara timpul SQL cu durata totala observata de utilizator. Daca query-ul dureaza putin, dar pagina ramane lenta, cauza poate fi in codul aplicatiei, in numarul mare de interogari, in transferul unui rezultat voluminos sau intr-o alta operatiune din acelasi flux. Daca aceeasi interogare are timpi foarte diferiti, compara si incarcarea serverului si tranzactiile active.

2. Citeste planul EXPLAIN

EXPLAIN arata cum intentioneaza MySQL sa execute o interogare, inclusiv ordinea in care trateaza tabelele din JOIN. Ruleaza-l pentru SQL-ul care apare in aplicatie, cu structura si filtre apropiate de cazul real. O interogare simplificata poate produce un plan diferit de raportul efectiv.

EXPLAIN
SELECT o.id, o.created_at, o.total
FROM orders AS o
WHERE o.company_id = 42
  AND o.status = 'open'
ORDER BY o.created_at DESC
LIMIT 50;

In output, urmareste in special ce tabel este citit, ce index este ales, cate randuri estimeaza MySQL ca va examina si ce operatii suplimentare apar. In formatul traditional, un tip de acces ALL este un semnal ca se face un scan complet al tabelului; interpretarea depinde insa de dimensiunea tabelului si de cate randuri trebuie intoarse.

Daca versiunea instalata permite EXPLAIN ANALYZE, il poti folosi pentru a compara estimarile planului cu executia observata si randurile procesate. Acesta executa interogarea pe care o analizeaza, asa ca foloseste-l cu prudenta pe productie: incepe cu un SELECT controlat, tine cont de costul lui si evita sa il aplici mecanic unei operatii care modifica date.

Un plan nu este un verdict de unul singur. Citeste-l impreuna cu durata masurata si cu numarul de randuri rezultate. Uneori optimizerul alege intentionat un scan, de exemplu cand query-ul are nevoie de o parte mare dintr-un tabel.

3. Interpreteaza full table scan in context

Un full table scan citeste randurile tabelului pentru a gasi sau procesa datele necesare. Pe un tabel mic, poate fi o alegere rezonabila; pe un tabel mare, care intoarce doar cateva randuri, poate semnala ca merita verificat un index sau modul in care este scris filtrul.

Intreaba-te ce rezultat cere de fapt raportul. Daca selecteaza o coloana pentru toate comenzile din ultimii ani, MySQL poate avea mult de citit chiar daca planul este corect. Daca selecteaza doar ultimele 50 de comenzi deschise pentru o anumita companie, dar examineaza o mare parte din tabel, merita sa investighezi filtrarea, ordonarea si indexurile.

Mai verifica si daca expresiile, conversiile de tip sau functiile aplicate coloanelor din conditii afecteaza planul. Nu presupune ca simpla prezenta a unei coloane intr-un index garanteaza utilizarea lui: tipurile si collation-urile folosite in comparatii conteaza, iar query-ul trebuie judecat prin plan si test.

4. Alege indexul dupa query, nu dupa o singura coloana

Un index compus poate ajuta cand query-ul combina filtre pe mai multe coloane. Ordinea coloanelor conteaza: MySQL poate folosi partile din stanga ale unui index multicolumn pentru cautari. De exemplu, indexul (company_id, status, created_at) poate fi un candidat de verificat pentru un raport care filtreaza dupa companie si status, apoi sorteaza dupa data.

CREATE INDEX idx_orders_company_status_created
ON orders (company_id, status, created_at);

Acesta este un exemplu de ipoteza, nu o recomandare universala. Verifica query-urile importante care folosesc tabelul si compara planurile inainte si dupa schimbare. Un index nou ocupa spatiu si poate adauga lucru operatiilor care modifica datele, deoarece indexurile trebuie mentinute. Evita sa adaugi un index pentru fiecare coloana fara sa stii ce interogare va sustine.

Include in analiza coloanele folosite in WHERE, JOIN si ORDER BY. Pentru JOIN-uri, verifica si daca tipurile coloanelor comparate sunt compatibile. Daca raportul filtreaza dupa o coloana, dar face join pe alta, un index doar pe filtrul initial poate sa nu fie suficient pentru planul complet.

5. Verifica sortarile si volumul returnat

Sortarea poate deveni costisitoare cand MySQL nu poate folosi un index potrivit pentru ordinea ceruta. In output-ul traditional EXPLAIN, Using filesort indica o operatie de sortare separata; nu inseamna automat ca query-ul este defect. Conteaza cate randuri ajung la sortare si cat de des se executa interogarea.

Un alt semn este intoarcerea unui volum mai mare decat foloseste interfata. Un raport care incarca toate inregistrarile si le pagineaza doar in browser transfera si proceseaza date pe care utilizatorul nu le vede inca. Verifica daca filtrarea si paginarea se pot face in query si daca aplicatia cere doar coloanele necesare in loc de SELECT *.

Inainte sa schimbi sortarea sau sa adaugi un index, compara planul si timpul pentru volume realiste. Testul cu putine randuri poate ascunde costul pe care il vei vedea dupa cresterea datelor.

6. Verifica statisticile optimizerului

Optimizerul foloseste statistici despre tabele si indexuri cand alege un plan. Dupa o crestere importanta sau o schimbare a distributiei datelor, estimarile pot sa nu mai reflecte bine continutul actual. Compara numarul estimat de randuri din EXPLAIN cu ceea ce observi in executie si verifica planul dupa ce statisticile au fost actualizate.

ANALYZE TABLE actualizeaza informatii de distributie folosite de optimizer. Nu este o comanda de pus pe lista oricarei incetiniri: foloseste-o cand exista un motiv, precum estimari vizibil neconvingatoare sau schimbari mari in coloanele indexate, si urmeaza procedurile echipei pentru mediul respectiv. Apoi ruleaza din nou query-ul si compara planul. Daca planul s-a schimbat, masoara si rezultatul real; un plan diferit nu garanteaza automat un timp mai bun.

7. Diferentiaza executia lenta de asteptarea unui lock

O cerere poate parea lenta chiar daca interogarea in sine nu proceseaza multe randuri: o alta tranzactie poate tine un lock de care operatia curenta are nevoie. In acest caz, adaugarea unui index sau mai multa memorie nu elimina automat tranzactia care blocheaza accesul.

In MySQL cu InnoDB, verifica sesiunile si lock wait-urile prin instrumentele disponibile, inclusiv informatiile din Performance Schema despre lock-uri si relatiile de asteptare. Uita-te la tranzactiile deschise, operatiile care cer FOR UPDATE sau FOR SHARE si codul care lasa o tranzactie activa mai mult decat este necesar.

Un detaliu important la interpretarea logurilor: documentatia MySQL precizeaza ca timpul initial pentru obtinerea lock-urilor nu este inclus in timpul raportat in slow query log. De aceea, compara logul cu informatiile despre asteptari si cu timestamp-urile din aplicatie inainte sa concluzionezi ca timpul observat este numai timp de executie SQL.

8. Cand are sens sa schimbi query-ul sau hardware-ul

Daca planul arata un scan sau o sortare ampla pentru un rezultat mic, incepe prin a verifica query-ul, filtrele, join-urile si indexurile. Daca query-ul cere in mod legitim un set mare de date, indexarea nu poate elimina toata munca: poate fi necesara schimbarea raportului, reducerea rezultatului sau planificarea unor resurse potrivite volumului si concurentei.

Hardware-ul poate conta cand masuratorile arata presiune constanta pe resurse, dar nu corecteaza un query care citeste inutil aceleasi date la fiecare apel. In sens invers, un query bine scris nu compenseaza orice limita a serverului daca aplicatia ruleaza multe interogari simultan sau proceseaza volume mari. Ia decizia dupa masuratori, nu dupa un singur rand din EXPLAIN.

Checklist de diagnostic

• Identifica query-ul exact, pagina sau procesul care il executa si frecventa apelurilor.
• Masura durata query-ului si durata totala observata in aplicatie.
• Compara parametrii si volumul de date pentru un caz rapid si unul lent.
• Ruleaza EXPLAIN si verifica tabelele, ordinea JOIN, indexul ales si randurile estimate.
• Verifica daca filtrarea, JOIN-ul si sortarea sunt aliniate cu indexurile disponibile.
• Verifica numarul si dimensiunea randurilor returnate, nu doar timpul din baza de date.
• Compara estimarile cu executia observata si verifica statisticile cand exista indicii ca sunt invechite.
• Cauta lock wait-uri, tranzactii lungi si diferente intre incarcarea normala si cea de varf.
• Schimba o singura variabila o data si masoara din nou pe date si parametri reprezentativi.

Daca incetinirea vine dintr-o aplicatie business custom, investigatia trebuie sa urmareasca impreuna interogarile, fluxurile din cod si volumele reale. Poti afla mai multe despre mentenanta website-urilor si aplicatiilor web sau despre dezvoltarea aplicatiilor web custom.

Intrebari frecvente despre interogarile MySQL lente

Este intotdeauna gresit ca EXPLAIN sa arate un full table scan?

Nu. Pentru un tabel mic sau un query care are nevoie de cele mai multe randuri, scanarea poate fi o alegere rezonabila. Devine un indiciu de investigat cand tabelul este mare, rezultatul este restrans, iar planul examineaza mult mai multe randuri decat sunt necesare.

Ar trebui sa adaug un index pe fiecare coloana folosita in WHERE?

Nu automat. Analizeaza interogarea completa, inclusiv coloanele din JOIN si ORDER BY, apoi testeaza daca un index simplu sau compus imbunatateste planul si masuratorile. Indexurile trebuie luate in calcul si pentru spatiul ocupat si operatiile de scriere.

De ce conteaza ordinea coloanelor intr-un index compus?

MySQL poate folosi prefixele din stanga ale indexului compus. De aceea, ordinea trebuie evaluata in raport cu filtrele si cautarile din query-urile reale. Un index care incepe cu o alta coloana poate sa nu sustina acelasi tipar de acces.

Cand merita sa rulez ANALYZE TABLE?

Ia in calcul actualizarea statisticilor cand estimarile planului par neconvingatoare sau cand datele si coloanele indexate s-au schimbat semnificativ. Compara planul si executia dupa comanda; nu presupune ca actualizarea statisticilor va rezolva orice problema de performanta.

Mai multa memorie sau un server mai puternic rezolva query-ul lent?

Poate ajuta daca masuratorile arata ca resursele serverului sunt o limita, dar nu inlocuieste corectarea unui query care proceseaza inutil prea multe date. Verifica planul, volumul rezultatului, concurenta si lock-urile inainte sa alegi schimbarea.