MyCodeSchool

Pasul 1 din 5

MySQL

Pasul 1 din 5 · Ce construiești

  • MySQL
  • Mediu
  • 13 minute

Indecși în MySQL: de la 100 000 de rânduri citite la unul

Biblioteca județeană are 100 000 de cititori. Cauți unul după email. Fără index, MySQL citește tot tabelul. Cu o singură comandă, ajunge direct la rândul căutat:

planul căutării, înainte și după
-- fără index
 type: ALL
  key: NULL
 rows: 99994
-- după CREATE INDEX
 type: ref
  key: idx_email
 rows: 1

rows e numărul de rânduri pe care MySQL estimează că le va citi. Diferența apare din primul tabel mare cu care lucrezi.

Pasul 2 din 5

Măsoară, adaugă indexul, măsoară din nou

Întâi umpli tabelul. Scriptul de mai jos generează 100 000 de cititori cu o interogare recursivă, fără să scrii rândurile de mână.

date.sql
CREATE TABLE cititori (  id INT PRIMARY KEY AUTO_INCREMENT,  nume VARCHAR(60),  oras VARCHAR(40),  email VARCHAR(80));SET SESSION cte_max_recursion_depth = 100000;INSERT INTO cititori (nume, oras, email)WITH RECURSIVE n(i) AS (  SELECT 1 UNION ALL  SELECT i + 1 FROM n WHERE i < 100000)SELECT CONCAT('Cititor ', i),  ELT(1 + i % 5, 'Cluj', 'Iași',      'Brașov', 'Timișoara', 'Sibiu'),  CONCAT('c', i, '@ex.ro')FROM n;

Acum rulează scriptul de măsurare. \G la final afișează rezultatul pe verticală, un câmp pe rând, mai ușor de citit pe ecran.

idx.sql
SELECT COUNT(*) FROM cititori;EXPLAIN SELECT nume FROM cititori  WHERE email = 'c777@ex.ro'\GCREATE INDEX idx_email  ON cititori (email);EXPLAIN SELECT nume FROM cititori  WHERE email = 'c777@ex.ro'\G

Indecși pe tabel

Ce răspunde MySQL


      

  1. Linia 1. Verifici că tabelul are 100 000 de rânduri. Singurul index e cheia primară, PRIMARY, pe coloana id, creată automat.
  2. Liniile 2–3. EXPLAIN nu rulează căutarea, doar arată planul. type: ALL înseamnă că MySQL citește tot tabelul, rând cu rând. key: NULL: niciun index folosit.
  3. Liniile 4–5. CREATE INDEX construiește o structură ordonată după email, separat de tabel. Datele din tabel nu se schimbă.
  4. Liniile 6–7. Aceeași interogare are alt plan. type: ref și key: idx_email: MySQL coboară prin index direct la emailul căutat și estimează un singur rând citit.
  5. Gata. Interogarea n-ai schimbat-o deloc. Doar baza de date are acum un drum mai scurt până la răspuns.

Prezice ieșirea

Rulezi EXPLAIN SELECT nume FROM cititori WHERE id = 777\G. Ce apare la key?

Răspuns: PRIMARY.

Cheia primară e și ea un index, creat automat. Planul arată type: const și key: PRIMARY: cel mult un rând poate avea id-ul 777.

Pasul 3 din 5

Cum lucrează un index

Trei idei despre ce face un index și ce te costă. Apoi două interogări care par să-l folosească, dar nu-l folosesc.

Un index e o listă ordonată, cu trimiteri

Gândește-te la indexul de la finalul unui manual: termenii sunt în ordine alfabetică și fiecare trimite la o pagină. MySQL ține ceva asemănător, un arbore B-tree: valorile din coloană, ordonate, fiecare cu drumul spre rândul ei.

Într-o listă ordonată nu cauți rând cu rând. Sari la jumătate, apoi la jumătatea jumătății. La 100 000 de valori ajung cam 17 astfel de pași.

Prețul: fiecare INSERT, UPDATE sau DELETE trebuie să actualizeze și indexul, iar indexul ocupă spațiu pe disc. De aceea pui indecși doar pe coloanele după care chiar cauți, unești sau sortezi.

Indexul compus și prefixul din stânga

compus.sql
CREATE INDEX idx_oras_nume  ON cititori (oras, nume);EXPLAIN SELECT email FROM cititori  WHERE oras = 'Sibiu'    AND nume = 'Cititor 5'\GEXPLAIN SELECT email FROM cititori  WHERE nume = 'Cititor 5'\G

Planul pentru oraș și nume

 type: ref
  key: idx_oras_nume
 rows: 1

Planul doar pentru nume

 type: ALL
  key: NULL
 rows: 99994

Indexul (oras, nume) e ordonat întâi după oraș, apoi după nume în fiecare oraș. E ca o carte de telefon ordonată după nume de familie: găsești ușor „Popescu Ana”, dar nu și toți cei care se numesc Ana.

MySQL folosește indexul doar pornind din stânga: oras, sau oras plus nume. Pune primele coloanele pe care le ai mereu în WHERE.

UNIQUE: indexul care păzește

unic.sql
CREATE UNIQUE INDEX idx_email_unic  ON cititori (email);INSERT INTO cititori (nume, oras, email)  VALUES ('Ana Pop', 'Cluj', 'c777@ex.ro');
ERROR 1062 (23000): Duplicate entry 'c777@ex.ro' for key 'cititori.idx_email_unic'

Un index UNIQUE face două lucruri: accelerează căutarea și refuză valorile repetate. Două conturi cu același email nu mai pot exista, indiferent ce bug are aplicația.

Dacă tabelul are deja duplicate, nici crearea indexului nu merge. Pe coloana oras, MySQL se oprește la primul oraș găsit de două ori: Duplicate entry 'Brașov'.

Capcană: o funcție pe coloana indexată

Vrei o căutare care să nu țină cont de litere mari sau mici și pui UPPER pe coloană. Interogarea merge, dar planul s-a întors la citirea completă:

functie.sql
EXPLAIN SELECT nume FROM cititori  WHERE UPPER(email) = 'C777@EX.RO'\G

Planul

 type: ALL
  key: NULL
 rows: 99994

Indexul e ordonat după email, nu după UPPER(email). Ca să compare, MySQL trebuie să calculeze funcția pe fiecare rând.

Repară lăsând coloana neatinsă: WHERE email = 'c777@ex.ro'. Colațiile obișnuite din MySQL 8 compară deja fără să țină cont de majuscule.

Capcană: LIKE care începe cu %

like.sql
EXPLAIN SELECT nume FROM cititori  WHERE email LIKE '%77@ex.ro'\GEXPLAIN SELECT nume FROM cititori  WHERE email LIKE 'c77%'\G

Planurile, în ordine

 type: ALL     key: NULL
 type: range   key: idx_email

Cu % în față nu știi cu ce începe textul, deci nu poți sări în lista ordonată. Cu 'c77%', MySQL caută doar intervalul de emailuri care încep cu „c77”.

Repară punând procentul doar la final, acolo unde se poate. Pentru căutare în mijlocul textului există indecși FULLTEXT.

Pasul 4 din 5

Verifică-te

Tabelul are indecșii PRIMARY (id), idx_email (email) și idx_oras_nume (oras, nume).

1 din 5 · Prezice ieșirea

Ce apare la key pentru EXPLAIN SELECT email FROM cititori WHERE oras = 'Sibiu'\G?

Răspuns: idx_oras_nume.

oras e prima coloană din index, deci prefixul din stânga e respectat. Lipsa lui nume nu contează. Contează doar să nu sari peste prima coloană.

2 din 5 · Alege varianta

Care condiție poate folosi idx_email?

Răspuns: LIKE 'c12%'.

Un început cunoscut înseamnă un interval în lista ordonată. Procentul din față și funcția pe coloană obligă MySQL să verifice fiecare rând.

3 din 5 · Prezice ieșirea

Ce valoare are type pentru EXPLAIN SELECT email FROM cititori WHERE nume = 'Cititor 5'\G?

Răspuns: ALL.

nume e a doua coloană din idx_oras_nume. Fără oraș în condiție, MySQL nu are de unde intra în index și citește tot tabelul.

4 din 5 · Alege varianta

De ce nu pui un index pe fiecare coloană, „ca să fie”?

Răspuns: scrierile și spațiul.

Un tabel poate avea mulți indecși, iar ei ajută la citire. Costul apare la fiecare INSERT, UPDATE și DELETE, plus spațiul pe disc. Un index pe care nicio interogare nu-l folosește e doar cost.

5 din 5 · Prezice ieșirea

Ce se întâmplă la CREATE UNIQUE INDEX idx_oras_unic ON cititori (oras);?

Răspuns: eroarea 1062.

Sunt doar 5 orașe la 100 000 de cititori, deci valorile se repetă. MySQL nu șterge nimic ca să facă loc indexului. Refuză: ERROR 1062 (23000): Duplicate entry 'Brașov' for key 'cititori.idx_oras_unic'.

Pasul 5 din 5

Ce ai învățat

Rețeta, pe scurt

Când o interogare e lentă, pornește de la plan, nu de la presupuneri. Rulează-l, adaugă indexul potrivit, apoi verifică din nou că apare la key.

idx.sql
SELECT COUNT(*) FROM cititori;EXPLAIN SELECT nume FROM cititori  WHERE email = 'c777@ex.ro'\GCREATE INDEX idx_email  ON cititori (email);EXPLAIN SELECT nume FROM cititori  WHERE email = 'c777@ex.ro'\G

Continuă cu