`Ghid de Optimizare SQL: Indecși B-Tree, Index Covering și EXPLAIN ANALYZE` în MySQL

Ghid complet de optimizare a performanței interogărilor în MySQL: anatomia unui arbore B+Tree, index covering (Using index), regula celui mai din stânga prefix, optimizarea JOIN-urilor și interpretarea rapoartelor EXPLAIN ANALYZE.

Sintaxă

EXPLAIN ANALYZE SELECT ... | CREATE INDEX idx_users_email_status ON users(email, status)

În producție, diferența dintre o bază de date care răspunde în 3 milisecunde și una care blochează procesorul serverului la 100% constă în proiectarea corectă a indecșilor și înțelegerea modului în care optimizatorul de interogări (Cost-based Optimizer) din MySQL ia decizii. Acest ghid detaliază mecanismele interne ale motorului InnoDB, modul de citire a planurilor de execuție și strategiile dovedite pentru eliminarea blocajelor de performanță.

1. Indice Clustered vs. Indice Secundar în InnoDB

-- În motorul InnoDB, fiecare tabel are un singur Clustered Index (de regulă PRIMARY KEY).
-- Rândurile fizice de date sunt stocate direct în nodurile frunză ale arborelui B+Tree al cheii primare!

-- Tabel de utilizatori:
CREATE TABLE utilizatori (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Clustered Index
    email VARCHAR(150) NOT NULL,
    status VARCHAR(20) NOT NULL,
    data_inregistrare DATETIME NOT NULL,
    INDEX idx_status (status)                  -- Secondary Index
) ENGINE=InnoDB;

-- Ce se întâmplă când căutăm după un index secundar?
-- SELECT * FROM utilizatori WHERE status = 'activ';
-- 1. MySQL parcurge arborele B+Tree al 'idx_status' -> găsește valoarea cheii primare (id-ul).
-- 2. "Bookmark Lookup" / "Row Lookup": MySQL merge în Clustered Index pentru a citi restul coloanelor.
-- Soluția de viteză maximă: Indicele Acoperitor (Covering Index)!

Conceptul de Index Covering (Using index)

Un Covering Index este un index care conține absolut toate coloanele solicitate de clauzele SELECT, WHERE, JOIN și ORDER BY ale interogării. Când un index este acoperitor, InnoDB nu mai este nevoit să facă al doilea pas de lookup în tabelul principal (clustered index), reducând dramatic I/O-ul pe disc sau cache misses din Buffer Pool.

Crearea și testarea unui Index Covering

-- Adăugăm un index compus care include atât coloana de filtrare cât și pe cea returnată
CREATE INDEX idx_status_email ON utilizatori(status, email);

-- Interogarea optimizată:
EXPLAIN SELECT email FROM utilizatori WHERE status = 'activ';

-- În coloana 'Extra' a planului EXPLAIN veți vedea:
-- "Using index" -> Confirmă că MySQL a citit datele EXCLUSIV din nodurile indexului din memorie!

Regula Celui Mai din Stânga Prefix (Leftmost Prefix Rule)

Dacă aveți un index compus pe trei coloane: INDEX idx_abc (col_a, col_b, col_c), indexul poate fi utilizat eficient doar de interogările care filtrează de la stânga la dreapta fără goluri:

WHERE col_a = 1 → DA, folosește indexul pe col_a.
WHERE col_a = 1 AND col_b = 2 → DA, folosește indexul pe ambele coloane.
WHERE col_a = 1 AND col_b = 2 AND col_c = 3 → DA, folosește indexul complet.
WHERE col_b = 2 AND col_c = 3 → NU! MySQL nu poate sări peste col_a; va face Full Table Scan sau Index Scan complet.
WHERE col_a = 1 AND col_c = 3 → Folosește indexul doar parțial pentru col_a, iar pentru col_c aplică filtrare în memorie (Index Condition Pushdown – ICP).

Diagnostic avansat cu EXPLAIN ANALYZE în MySQL 8

Comanda clasică EXPLAIN afișează doar estimările optimizatorului, care pot fi înșelătoare dacă statisticile tabelului sunt perimate. EXPLAIN ANALYZE rulează efectiv interogarea, măsoară timpii reali și afișează un arbore detaliat:

Exemplu raport EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT c.id, c.total, u.email
FROM comenzi c
JOIN utilizatori u ON c.utilizator_id = u.id
WHERE c.created_at >= '2026-01-01' AND c.status = 'finalizata';

-- Rezultat generat (format iterator tree):
-- -> Nested loop inner join  (cost=12.45 rows=25) (actual time=0.082..0.315 rows=25 loops=1)
--     -> Filter: ((c.`status` = 'finalizata') and (c.created_at >= TIMESTAMP'2026-01-01 00:00:00'))
--         -> Index range scan on c using idx_status_created (cost=3.70 rows=25) (actual time=0.041..0.098 rows=25 loops=1)
--     -> Single-row index lookup on u using PRIMARY (id=c.utilizator_id) (cost=0.35 rows=1) (actual time=0.007..0.008 rows=1 loops=25)

Metrici esențiale din raportul EXPLAIN ANALYZE:

Indicator	Descriere	Ce trebuie urmărit
`actual time=X..Y`	Timpul în milisecunde până la primul rând (X) și până la finalizarea citirii tuturor rândurilor (Y).	Dacă Y este ridicat pe un nod intermediar, acolo se află bottleneck-ul de I/O sau CPU.
`rows=N (actual)` vs `rows=N (est.)`	Comparația dintre rândurile estimate de optimizator și cele procesate în realitate.	Divergențele masive (ex: estimat 10 rânduri, dar returnat 500.000) indică statistici perimate (necesită `ANALYZE TABLE`).
`loops=K`	De câte ori a fost executat iteratorul respectiv (tipic în Nested Loop Join).	Timpul total al acelui nod este timpul afișat înmulțit cu valoarea din `loops`.

Top Antipatterns SQL de Evitat în Producție

Transformarea coloanelor în clauza WHERE: WHERE DATE(created_at) = '2026-10-24' anulează indexul pe created_at. Folosiți intervale: WHERE created_at >= '2026-10-24 00:00:00' AND created_at < '2026-10-25 00:00:00'.
Filtrare cu wildcard la început: LIKE '%ter' forțează o scanare completă de tabel. Folosiți LIKE 'ter%' sau o căutare Full-Text (MATCH ... AGAINST).
Mismatches de tip de date (Implicit Type Conversion): Dacă o coloană telefon este VARCHAR și interogați cu un număr întreg WHERE telefon = 0712345678, MySQL convertește fiecare rând din tabel în număr, invalidând indexul B-Tree!
Paginarea adâncă ineficientă: LIMIT 100000, 20 citește și aruncă primele 100.000 de rânduri din memorie. Folosiți Keyset Pagination (Seek method): WHERE id > 100000 ORDER BY id ASC LIMIT 20.