Mysql-indici pentru Dummies

Pentru a începe cu, care de multe ori se vedea erorile asociate cu crearea indicelui în MySQL. Mulți dezvoltatori (și nu numai noi la MySQL) a crea mai multe indicii pe coloanele care vor fi utilizate în probe, și cred că este cea mai bună strategie. De exemplu, dacă am nevoie pentru a executa o interogare cum ar fi vârsta = 18 AND STATE = „CA“, mulți oameni pur și simplu crea 2 indice separat pe coloane și STATUL AGE.

Mult mai bine (aici și mai jos, Nota traducătorului :. Și, de obicei, singura corectă) strategia este de a crea un indice de tip combinat (AGE, STAT). Să ne uităm la ce este așa.

De obicei (dar nu întotdeauna) indici în MySQL sunt btree-indici - indicele de acest tip este capabil de a vizualiza rapid informațiile conținute în prefixele lor, și sortarea prin Variază valori sortate. De exemplu, atunci când solicitați AGE = 18 btree indexului pe vârsta coloana MySQL găsește primul rând din tabelul corespunzător cererii și să continue căutarea, atâta timp cât se constată primul rând nepotrivit - atunci se oprește căutare, deoarece El consideră că în continuare nu este ceva potrivit. Benzile precum interogări ale formei între 18 și 20, funcționează într-un mod similar - MySQL se oprește la alte valori.

Situația oarecum mai complicată cu întrebări cum ar fi vârsta IN (18,20,30), deoarece MySQL are de fapt, pentru a trece de mai multe ori prin index.

Deci, am discutat modul în care MySQL este în căutarea de index, dar nu specifică faptul că se întoarce după o căutare - de obicei (dacă nu vorbim despre acoperirea (Acoperitor) index) devine un „șir de caractere pointer“, care poate fi valoarea cheii primare (dacă utilizați motorul de InnoDB ), un fizic de compensare în fișierul (pentru `MyISAM„) sau ceva de genul asta. Este important ca motorul MySQL intern poate pe acest index pentru a găsi o linie completă a tuturor datelor necesare corespunzătoare valorii indicelui specificat.

Și care sunt opțiunile în MySQL, dacă ați creat două indice separat? Se poate utiliza fie doar unul dintre ele pentru a selecta linia corespunzătoare (și apoi se filtrează datele extrase din Guided UNDE - dar fără utilizarea indicilor), sau se poate obține un pointer la un șir de caractere din toate indexurile corespunzătoare și se calculează intersecția lor, și apoi să se întoarcă datele .

Ceea ce mod este mai adecvat depinde de indicii de selectivitate și de corelare. Dacă UNDE după lucru de pe prima coloană este selectat rânduri de 5%, iar utilizarea unei cantități mai WHERE linie coloana a doua filtre la 1% din total, utilizarea intersecțiilor, desigur, are sens. Dar, în cazul în care a doua atunci filtra doar până la 4,5%, în general, este mult mai avantajos să se utilizeze numai primul index și filtra liniile noastre nedorite după extragerea datelor.

Să ne uităm la câteva exemple:

CREATE TABLE 'idxtest' (
int 'I1' (10) UNSIGNED NOT NULL,
int 'I2' (10) UNSIGNED NOT NULL,
'Val' varchar (40) DEFAULT NULL,
KEY 'i1' ( 'i1'),
KEY 'i2' ( 'i2'),
KEY 'combinată' ( 'i1', 'i2')
) MOTORULUI = MyISAM DEFAULT CHARSET = latin1

Am creat o coloană i1 și i2 independent unul de altul, și fiecare dintre ele selectează aproximativ 1% din rândurile din tabel care conține un total de 10 Mill. Înregistrări.

mysql> EXPLICAȚI SELECT avg (lungime (val)) FROM idxtest WHERE i1 = 50 AND i2 = 50;

După cum puteți vedea MySQL a ales să utilizeze un indice de combinat și interogare executat în mai puțin de 10 ms!

Acum, să presupunem că avem un cod doar pentru difuzoare individuale (pentru a spune optimizator pentru a ignora indicele combinat):

mysql> EXPLICAȚI avg SELECT (lungime (val)) DIN idxtest IGNORE INDEX (combinat) UNDE i1 = 50 AND i2 = 50;

După cum puteți vedea, în acest caz, MySQL executat indicii de intersecție de căutare, și pentru a efectua interogarea a 70 ms - 7 ori mai mult!

Acum să vedem ce se întâmplă dacă utilizați doar un singur indice și se filtrează datele:

mysql> EXPLICAȚI SELECT avg (lungime (val)) DIN idxtest IGNORE INDEX (combinate, i2) UNDE i1 = 50 AND i2 = 50;

În acest moment, MySQL a trebuit să meargă mult mai multe rânduri, executarea interogare a luat 290 ms. Astfel, vom vedea că utilizarea indicilor de intersecție este mult mai bună decât folosind un singur index, dar este mult mai bine să utilizați un indice combinat.

Cu toate acestea, această problemă nu se termină cu indicele de intersecție. În prezent, posibilitatea de a folosi această procedură în MySQL limitată în mod semnificativ, astfel încât MySQL le utilizează nu este întotdeauna:

mysql> EXPLICAȚI avg SELECT (lungime (val)) DIN idxtest IGNORE INDEX (combinat) UNDE i1 = 50 AND i2 IN (49,50);

Odată ce o cerere pentru una dintre coloanele devine comparație și de transfer, MySQL nu mai poate utiliza indicii de intersecție, în ciuda faptului că, în acest caz, la cererea IN i2 (49,50) ar fi mai mult decât rezonabil, deoarece . cererea rămâne destul de selectiv.

Acum să petrec mai mult de un test. Am șters masa și re-umplut cu date într-un mod care valorile i1 și i2 puternic corelate. De fapt, ele acum, în general, sunt:

mysql> UPDATE idxtest SET i2 = i1;

Query OK, 10900996 linii afectate (6 min 47,87 sec)
Rânduri potrivire: 11010048 schimbat: 10900996 Avertismente: 0

Să vedem ce se va întâmpla în acest caz:

mysql> EXPLICAȚI SELECT avg (lungime (val)) FROM idxtest WHERE i1 = 50 AND i2 = 50;

Optimizatorul a ales să utilizeze indicatorii de intersecție, deși a fost probabil cea mai proastă soluție! Executarea unei interogări a luat 360 ms. De asemenea, să acorde o atenție la o eroare mare în evaluarea numărului aproximativ de rânduri.

Acest lucru sa întâmplat datorită faptului că MySQL presupune valori în coloanele i1 și i2 independente, și, prin urmare, alege indicii de intersecție. De fapt, el nu se poate imagina o alta, deoarece nu există statistici privind corelația valorile pe care le are coloane.

mysql> EXPLICAȚI SELECT avg (lungime (val)) DIN idxtest IGNORE INDEX (i2) UNDE i1 = 50 AND i2 = 50;

Și acum, când ne este interzis să utilizeze coloana MySQL indicele I2 (și nu se poate găsi, prin urmare intersecția indicelui), se folosește un index pe o singură coloană, și nu combinate. Sa întâmplat acest lucru, deoarece MySQL au statistici privind numărul aproximativ de rânduri de calificare, iar din moment ce este egal pentru ambii indicatori, MySQL a ales mai mici. Rularea o interogare a luat 290 ms din nou - exact la fel ca ultima dată.

Forța MySQL pentru a utiliza indicele numai combinate:

mysql> EXPLICAȚI avg SELECT (lungime (val)) DIN idxtest IGNORE INDEX (i1, i2) UNDE i1 = 50 AND i2 = 50;

Se observă că MySQL aproximativ 20% în estimarea greșită a numărului de rând căutat, care, desigur, este falsă, deoarece Se utilizează același prefix, ca și atunci când se utilizează indicele numai coloana i1. MySQL nu știe acest lucru, pentru că vezi statistici pentru indicii individuali și nu încearcă să le armonizeze.

Datorită faptului că indicele combinat utilizat este mai mare decât un indice de coloană, executarea interogare a luat 300 ms.

Astfel, vom vedea că MySQL poate decide să utilizeze indicii de intersecție, chiar dacă aceasta este cea mai rea opțiune, deși din punct de vedere tehnic, acest lucru va fi cu siguranță cel mai bun plan, având în vedere că alte statistici nu au avut.

Există modalități simple de a face MySQL nu utilizează indicii de intersecție, dar, din păcate, nu știu cum să-l pentru a utiliza trecere, în cazul în care consideră că această opțiune optimă. Sper că va fi adăugat o astfel de posibilitate în viitor.

În cele din urmă, să ia în considerare o situație în care indicii de intersecție procedurii de constatare funcționează mult mai bine decât indicii combinate pe mai multe coloane. Vorbim despre atunci când folosim sau la selectarea între coloane. În acest caz, un indice combinat devine inutil, si MySQL au posibilitatea de a alege între o scanare completă tabel (scanare completă) și efectuarea de asociere (UNION) în loc de căutare de valori de date de intersecție pe care le-a primit de la masa audio.

Încă o dată, am modificat valoarea în coloanele i1 și i2, astfel încât acestea să conțină date independente (o situație tipică pentru tabele).

mysql> EXPLICAȚI SELECT avg (lungime (val)) FROM idxtest WHERE i1 = 50 OR i2 = 50;

Astfel de interogări executate 660 ms. Dezactivarea indexului pe coloana a doua obținem FULL SCAN:

mysql> EXPLICAȚI SELECT avg (lungime (val)) DIN idxtest IGNORE INDEX (i2) UNDE i1 = 50 OR i2 = 50;

Rețineți că MySQL au chei i1, combinate în care este posibil de a utiliza, dar, de fapt, o astfel de posibilitate nu. Punerea în aplicare a unor astfel de cereri ia 3370 ms!

De asemenea, rețineți că executarea cererii a durat mai mult de 5 ori, în ciuda faptului că FULL SCAN a trecut de aproximativ 50 de ori mai multe rânduri. Acest lucru arată o diferență foarte mare de performanță între deschiderea completă a mesei și accesul prin cheie, care durează de 10 ori mai mult (în sensul de „valoare“ linia de acces), în ciuda faptului că se realizează în memorie.

În cazul în care optizator UNION acționează mai avansate și este capabil să facă față cu intervalele de variație:

mysql> EXPLICAȚI SELECT avg (lungime (val)) FROM idxtest WHERE i1 = 50 SAU i2 IN (49,50);

În cele mai multe cazuri, utilizarea unor indici combinate pe mai multe coloane este cea mai bună soluție dacă utilizați și între aceste coloane în cazul în care. Folosind indicii de intersecție, în principiu, îmbunătățește performanța, dar este încă în mod semnificativ mai rău decât atunci când se utilizează o combinație de taste. Dacă utilizați sau între coloanele pe care trebuie să le aibă asupra indicelui pe fiecare coloană pentru MySQL ar putea găsi intersecția lor, și indexurile combinate nu pot fi utilizate pentru astfel de cereri.

Toate indexurile MySQL (PRIMARĂ, unic și INDEX) stocate într-un B-arbore. Șiruri sunt comprimate în mod automat pentru a elimina lacunele din prefixele și spațiile rămase în urmă (a se vedea secțiunea 6.5.7, «CREATE INDEX Sintaxa").
Indecșii sunt utilizate pentru:

SELECT MIN (key_part2), MAX (key_part2) DIN table_name unde key_part1 = 10

  • Pentru a sorta, sau gruparea în tabel, în cazul în care aceste operațiuni sunt efectuate pe un prefix al cheii din stânga folosite (de exemplu, ORDER BY key_part_1, key_part_2). În cazul în care toate părțile cheie sunt DESC, cheia este citită în ordine inversă (a se vedea secțiunea 5.2.7, „Cum MySQL Optimizeaz ORDER BY»).
  • În unele cazuri, interogarea poate fi optimizat pentru a prelua valori fără a consulta fișierul de date. Dacă toate coloanele folosite în unele tabel sunt numerice și formează un prefix pentru o cheie de capătul din stânga, pentru a oferi o viteză mare, valoarea dorită poate fi preluată direct din arborele de index:

SELECT key_part3 FROM WHERE table_name key_part1 = 1

Să presupunem că emite următoarea declarație SELECT:

mysql> SELECT * FROM WHERE tbl_name col1 = VAL1 ȘI col2 = val2;

Dacă există un index mai multe coloane pe col1 și col2, rândurile corespunzătoare nu pot fi preluate direct. În cazul col1 coloanei și col2 există coduri distincte, optimizatorul încearcă să găsească indicele cel mai restrictiv prin determinarea indicelui care găsește mai puține rânduri și utilizează indexul pentru a prelua aceste linii.
Dacă tabelul are un indice de mai multe coloane, orice prefix al indicelui stânga poate fi utilizat de optimizator pentru a găsi rânduri. De exemplu, în cazul în care există un index pe cele trei coloane (col1, col2, Col3), atunci există potențialul pentru o căutare indexată (col1), (col1, col2) și (col1, col2, Col3).
MySQL nu se poate utiliza un indice parțial în cazul în care coloanele nu formează un prefix al indicelui extremitatea stângă. Să presupunem că există o declarație SELECT după cum se arată mai jos:

mysql> SELECT * FROM tbl_name UNDE col1 = VAL1;
mysql> SELECT * FROM tbl_name UNDE col2 = val2;
mysql> SELECT * FROM WHERE tbl_name col2 = val2 ȘI Col3 = val3;
mysql> SELECT * FROM WHERE tbl_name col1 = VAL1 ȘI col2 = val2;

În cazul în care un index există pe (col1, col2, Col3), primul și al patrulea interogările prezentate mai sus utilizează indexul. A doua și a treia solicitări de acest fel includ coloane indexate, dar (col2) și (col2, Col3) nu este capătul din stânga prefixele de piese (col1, col2, Col3).

* În același timp, indicii nu va funcționa indiferent de tipul de index, și anume și tipul de index: INDEX și tipul index unic va lucra foarte repede.

MySQL indici se aplică și pentru comparații ca în cazul în argumentul LIKE de expresie reprezintă un șir de caractere constantă nu începe cu un simbol șablon. De exemplu, următoarele afirmații SELECT folosesc indici:

mysql> SELECT * FROM tbl_name UNDE key_col cum ar fi "Patrick%";
mysql> SELECT * FROM tbl_name UNDE key_col cum ar fi "Pat% _ck%";

Numai rânduri sunt luate în considerare în prima echipă cu „Patrick“ <= key_col <"Patricl", а во второй - только строки с "Pat" <= key_col <"Pau".

Următoarele instrucțiuni SELECT nu se vor utiliza indici:

mysql> SELECT * FROM tbl_name UNDE key_col cum ar fi "% Patrick%";
mysql> SELECT * FROM tbl_name UNDE key_col LIKE other_col;

În prima echipa, valoarea LIKE începe cu un caracter wildcard. A doua comandă ca valoare nu este o constantă.

In versiunea de MySQL 4.0 este produsă o altă optimizare pe expresia LIKE. Dacă utilizați o expresie. Cum ar fi „%% șir de caractere“, iar lungimea șirului (șir) mai mult de 3 caractere, MySQL va utiliza un algoritm Turbo Boyer-Moore pentru a inițializa un șablon șir și apoi să utilizați acest șablon pentru a efectua o căutare mai rapidă.

Atunci când căutați folosind COLUMN_NAME IS NULL va folosi indexurile în cazul în care COLUMN_NAME este un index.

MySQL utilizează în mod normal, indicele care găsește cel mai mic număr de rânduri. Indicele este folosit pentru coloanele pe care le compara cu următorii operatori: =,>,> =, <, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something%.

În cazul în care indicele nu acopera toate și nivelurile în clauza WHERE, nu este folosit pentru a optimiza interogare. Cu alte cuvinte: pentru a putea folosi indexul, un prefix de index trebuie să fie incluse în fiecare și de grup.

Următoarele clauza WHERE folosesc indici:

UNDE index_part1 = 1 AND index_part2 = 2 AND other_column = 3
. UNDE index = 1 SAU A = 10 AND index = 2 / * index = index = 1 OR 2 * /
. UNDE index_part1 = 'Hello' ȘI index_part_3 = 5
/ * Optimizat ca "index_part1 = 'Hello'" * /
. UNDE index1 = 1 și index2 = 2 sau index1 = 3 și index3 = 3;
/ * Puteți utiliza indexul pe index1, dar nu pe index2 sau indicele * 3 /
Următoarele clauze WHERE nu utilizează indici:
. UNDE index_part2 = 1 AND index_part3 = 2
/ * Index_part_1 nu este în uz * /
. UNDE index = 1 SAU A = 10
/ * Nu utilizați indecși pe ambele părți ale AND * /
. UNDE index_part1 = 1 OR index_part2 = 10
/ * Nu există nici un index care acoperă toate liniile * /

În unele cazuri, MySQL nu va folosi un index, chiar dacă este posibil. Câteva exemple de astfel de situații este prezentată mai jos:

articole similare