Acasă » Haina de primavara » Exemplu de creare a unui index mysql. Ghid de referință MySQL

Exemplu de creare a unui index mysql. Ghid de referință MySQL

Sarcină
Scanarea tabelului este prea lentă. Sau inserarea și actualizarea înregistrărilor durează prea mult.

Soluţie
Clauza ALTER TABLE poate elimina și adăuga nu numai coloane, ci și indici pe acele coloane. Astfel de operațiuni îmbunătățesc adesea performanța bazei de date. De obicei, indexarea unei coloane frecvent utilizate accelerează instrucțiunile SELECT, nefiind nevoie să scaneze complet tabelele. În unele cazuri, ștergerea indexului poate fi, de asemenea, benefică. Ori de câte ori un rând este actualizat, MySQL trebuie să actualizeze toți indecșii care conțin coloanele modificate. Dacă utilizați rar un index, acest lucru poate indica faptul că tabelul este supraîncărcat cu indici, iar eliminarea unuia dintre ei poate îmbunătăți eficiența procesării tabelului.

Discuţie
Pentru ușurință în utilizare, să începem prin a crea o nouă instanță a tabelului de testare mytbl. Folosim clauzele DROP TABLE și CREATE TABLE pentru a elimina versiunea existentă și a recrea tabelul în forma sa originală:

DROP TABLE mytbl;
CREATE TABLE mytbl
eu INT,
c CHAR(1)
);

La începutul capitolului, am folosit SHOW COLUMNS pentru a monitoriza rezultatele modificării unui tabel.

Acum vom examina modificările indexului și vom afișa rezultatele folosind SHOW INDEX, mai degrabă decât SHOW COLUMNS. În prezent, nu există indecși pe tabel deoarece nu au fost specificați în instrucțiunea CREATE TABLE:

Mysql> SHOW INDEX FROM mytbl;
Set gol (0,00 sec)

Adăugarea de indici
Există patru tipuri de clauze care adaugă indecși la un tabel:

ALTER TABLE table_name ADD PRIMARY KEY (listă_coloană);
ALTER TABLE nume_tabel ADD UNIQUE nume_index (listă_coloană);
ALTER TABLE nume_tabel ADD INDEX nume_index (listă_coloană);
ALTER TABLE nume_tabel ADD FULLTEXT nume_index (listă_coloană);

Prima clauză adaugă o cheie primară (PRIMARY KEY), adică valorile indexate trebuie să fie unice și să nu conțină NULL. A doua clauză creează un index pentru care valorile trebuie să fie unice (cu excepția valorilor NULL, care pot apărea de mai multe ori). A treia clauză adaugă un index obișnuit în care orice valoare poate apărea de mai multe ori. Acesta din urmă creează un index special FULLTEXT, care este folosit pentru a vizualiza text.

Dacă există un nume_index în construcțiile de propoziție, atunci este opțional.

Dacă nu îl specificați, MySQL va atribui automat un nume indexului.

Coloanele care trebuie indexate sunt specificate în parametrul column_list — o listă cu unul sau mai multe nume de coloane separate prin virgulă.

Să luăm în considerare două exemple simple: primul creează un index cu o singură coloană pe c, iar al doilea creează un index cu mai multe coloane care include c și i:

ALTER TABLE mytbl ADD INDEX (c);
ALTER TABLE mytbl ADD INDEX (c,i);

În multe cazuri, coloanele care sunt indexate trebuie să fie declarate non-NULL. De exemplu, dacă creați mytbl ca tabel ISAM, instrucțiunile ADD INDEX de mai sus vor eșua deoarece tabelele ISAM nu permit NULL în niciun tip de index. În plus, indecșii PRIMARY KEY nu pot conține valori NULL, indiferent de tipul tabelului. Dacă încercați să adăugați un index și MySQL se plânge de problemele NULL, utilizați clauza ALTER TABLE pentru a schimba coloanele relevante în non-NULL și încercați să creați din nou indexul. De exemplu, dacă încercați să faceți din coloana i cheia primară, veți primi o eroare:

Mysql>
EROAREA 1171 la linia 5: Toate părțile unei CHEIE PRIMARĂ trebuie să fie NU NUL;
Dacă aveți nevoie de NULL într-o cheie, utilizați în schimb UNIQUE

Mai întâi trebuie să redefiniți coloana i, astfel încât să nu permită NULL:

Mysql> ALTER TABLE mytbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE mytbl ADD CHEIE PRIMARĂ (i);

Totul a mers.

Și în primul caz, după cum se vede din mesajul de eroare, în loc de cheia primară, puteți crea un index UNIQUE dacă aveți nevoie de valori NULL în index. Eliminarea indecșilor Pentru a șterge un index, utilizați unul dintre următoarele propozitii:

ALTER TABLE nume_tabel DROP PRIMARY KEY;
ALTER TABLE nume_tabel DROP INDEX nume_index;

Cel mai simplu mod de a șterge un index PRIMARY KEY este pentru că nu trebuie să cunoașteți numele indexului:

ALTER TABLE mytbl DROP PRIMARY KEY;

Pentru a șterge un index care nu este o cheie primară, trebuie să specificați numele acestuia. Dacă nu știți cum se numește indexul, utilizați SHOW INDEX. Pentru a evita ieșirea de linii prea lungi, utilizați ieșirea verticală (\G):

Mysql> AFIȚĂ INDEX DIN mytbl\G
************************** 1. rând ******************** * *****
Tabel: mytbl
Ne_unic: 1
Nume_cheie: c
Seq_in_index: 1
Nume_coloană: c
Colație: A
Cardinalitate: NULL
Sub_parte: NULL
Ambalat: NULL
Comentariu:
************************** 2. rând ******************** * *****
Tabel: mytblNon_unique: 1
Nume_cheie: c_2
Seq_in_index: 1
Nume_coloană: c
Colație: A
Cardinalitate: NULL
Sub_parte: NULL
Ambalat: NULL
Comentariu:
************************** 3. rând ******************** * *****
Tabel: mytbl
Ne_unic: 1
Nume_cheie: c_2
Seq_in_index: 2
Nume_coloană: i
Colație: A
Cardinalitate: NULL
Sub_parte: NULL
Ambalat: NULL
Comentariu:

Valorile Key_name și Seq_in_index corespund numelor indicilor și pozițiilor coloanelor din index. Acum știți că tabelul mytbl are un index cu o singură coloană numit c și un index cu mai multe coloane numit c_2 (aceste nume au fost alese de MySQL pentru cei doi indici pe care i-am creat mai devreme). Declarația de eliminare a indecșilor ar fi:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

După cum puteți vedea, mai multe operații pot fi efectuate într-o singură instrucțiune ALTER TABLE, care trebuie separată prin virgule.

Se folosesc indici pentru căutare rapidă rânduri cu valoarea specificată a unei coloane. Fără un index, un tabel este citit prin întregul tabel, începând cu prima înregistrare, până când sunt găsite rânduri care se potrivesc. Cu cât masa este mai mare, cu atât este mai mare suprafața. Dacă tabelul conține un index pe coloanele în cauză, atunci MySQL poate determina rapid poziția de căutare în mijlocul fișierului de date fără a parcurge toate datele. Pentru un tabel care conține 1000 de rânduri, aceasta va fi de cel puțin 100 de ori mai rapidă în comparație cu iterarea secvenţială a tuturor înregistrărilor. Cu toate acestea, în cazul în care aproape toate cele 1000 de rânduri trebuie să fie accesate, citirile secvențiale vor fi mai rapide, deoarece nu sunt necesare căutări de disc.

Toți indecșii MySQL (PRIMARY, UNIQUE și INDEX) sunt stocați ca arbori B. Șirurile sunt comprimate automat, eliminând prefixul și spațiile de sfârșit (vezi secțiunea 6.5.7 Sintaxa instrucțiunii CREATE INDEX).

Indecii sunt folosiți pentru:

Găsiți rapid rânduri care se potrivesc cu o clauză WHERE.
Preluați rânduri din alte tabele atunci când efectuați îmbinări.
Găsiți valorile MAX() sau MIN() pentru o anumită coloană indexată. Această operație este optimizată de un preprocesor care verifică dacă utilizați WHERE key_part_4 = constantă, peste toate părțile unei chei compuse SELECT MIN(key_part2),MAX(key_part2) FROM table_name unde key_part1=10
Efectuați sortarea sau gruparea într-un tabel dacă aceste operații sunt efectuate pe prefixul din stânga al cheii utilizate (de exemplu, ORDER BY key_part_1,key_part_2). Dacă toate părțile unei chei sunt urmate de DESC , atunci cheia este citită ordine inversă(vezi secțiunea 5.2.7 Cum se optimizează MySQL ORDER BY).
În unele cazuri, interogarea poate fi optimizată pentru a prelua valori fără a accesa fișierul de date. Dacă toate coloanele utilizate dintr-un anumit tabel sunt numerice și formează prefixul din stânga pentru o anumită cheie, atunci pentru a oferi o viteză mai mare, valorile căutate pot fi preluate direct din arborele index: SELECT key_part3 FROM table_name WHERE key_part1=1

Să presupunem că următoarea instrucțiune SELECT este numită:

Mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Dacă există un index cu mai multe coloane pe coloanele col1 și col2, atunci rândurile corespunzătoare pot fi selectate direct. În cazul în care există indecși separați pe coloanele col1 și col2, optimizatorul încearcă să găsească cel mai restrictiv index determinând care index găsește cele mai puține rânduri și folosește acel index pentru a prelua acele rânduri.

Dacă un tabel dat are un index cu mai multe coloane, atunci orice prefix din stânga acelui index poate fi folosit de optimizator pentru a găsi rânduri. De exemplu, dacă aveți un index pe trei coloane (col1,col2,col3), atunci există potențialul pentru căutări indexate pe (col1), (col1,col2) și (col1,col2,col3).

În MySQL, nu puteți utiliza un index parțial decât dacă coloanele formează prefixul din stânga acelui index. Să presupunem că aveți comenzile SELECT prezentate mai jos:

Mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Dacă există un index pe (col1,col2,col3), atunci numai prima interogare afișată mai sus folosește acel index. A doua și a treia interogare includ coloane indexate, dar (col2) și (col2,col3) nu sunt partea din stânga a prefixelor (col1,col2,col3).

MySQL folosește, de asemenea, indecși pentru comparații LIKE dacă argumentul din expresia LIKE este un șir constant care nu începe cu un caracter wildcard. De exemplu, următoarele comenzi SELECT utilizează indecși:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

Prima comandă se uită doar la liniile cu „Patrick”

Următoarele comenzi SELECT nu vor folosi indecși:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE cheie_col LIKE alt_col;

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

MySQL 4.0 introduce o optimizare diferită a expresiei LIKE. Dacă se folosește expresia... LIKE „%string%” și lungimea șirului este mai mare de 3 caractere, atunci MySQL va folosi algoritmul Turbo Boyer-Moore pentru a inițializa un model pentru șir și apoi va folosi acel model pentru a efectua căutarea mai rapidă.

Când căutați folosind nume_coloană este NULL, vor fi folosiți indecși dacă numele_coloană este un index.

MySQL folosește de obicei indexul care găsește cele mai puține rânduri. Indexul este utilizat pe coloanele care sunt comparate folosind următorii operatori: =, >, >=,

Dacă un index nu acoperă toate nivelurile AND din clauza WHERE, atunci nu este utilizat pentru a optimiza interogarea. Cu alte cuvinte: pentru ca un index să fie utilizabil, prefixul acelui index trebuie să apară în fiecare grup AND.

Următoarele clauze WHERE folosesc indecși:

WHERE index_part1=1 AND index_part2=2 AND other_column=3 ... WHERE index=1 SAU A=10 AND index=2 /* index = 1 SAU index = 2 */ ... WHERE index_part1="bună ziua" AND index_part_3= 5 /* optimizat ca „index_part1="bună ziua"" */ ... WHERE index1=1 și index2=2 sau index1=3 și index3=3; /* Puteți folosi un index la index1, dar nu la index2 sau index 3 */

Următoarele clauze WHERE Nu utilizați indici:

WHERE index_part2=1 AND index_part3=2 /* index_part_1 nu este folosit */ ... WHERE index=1 SAU A=10 /* Indexul nu este folosit în ambele părți ale AND */ ... WHERE index_part1=1 SAU index_part2 =10 /* Nu există niciun index care să acopere toate rândurile*/

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

Dacă folosirea unui index necesită ca MySQL să parcurgă mai mult de 30% din rândurile dintr-un tabel dat (în astfel de cazuri, parcurgerea tabelului va fi probabil mult mai rapidă, deoarece vor fi necesare mai puține căutări). Rețineți că, dacă o interogare ca aceasta folosește LIMIT numai pentru subsetul de rânduri care este preluat, atunci MySQL va folosi oricum indexul, deoarece un număr mic de rânduri poate fi găsit mult mai rapid pentru a returna rezultatul.
Dacă intervalul de modificare a indexului poate conține valori NULL atunci când utilizați expresiile ORDER BY ... DESC.

Indexarea tabelelor este utilizată pentru a reduce timpul de căutare a datelor. Fără indici, căutarea are loc prin simpla enumerare a tuturor valorilor din tabel, ceea ce durează destul de mult.

Următoarele tipuri de indecși sunt utilizate în tabelele mysql:

Cheie primară
Index unic
Index regulat
Index text integral

Pe lângă indicii de mai sus, se mai folosește indexul textului integral, dar nu este discutat în acest articol.

Cheie primară

Cheia primară este utilizată pentru a identifica în mod unic înregistrările dintr-un tabel. Printre punctele principale privind cheia primară, trebuie remarcate următoarele:

1. un tabel nu poate avea o singură cheie unică;

2. valoarea cheii trebuie să fie unică în tabel;

3. Cheia primară nu poate fi de tip NULL.

Exemplu de creare a unei chei primare:

Crearea unei chei primare la crearea unui tabel în descrierea câmpului

Exemplul 3:

Dacă cheia primară este un câmp de text, atunci trebuie să indicați între paranteze numărul de caractere implicate în indexare.

Exemplul 5:

Dacă tabelul a fost deja creat, atunci puteți adăuga un index primar după cum urmează.

$query = "ALTERAREA tabelului automat ADĂUGAȚI CHEIE PRIMARĂ DE CONSTRINGERE (număr_id)";
$rezultat = $conexiune->interogare($interogare);
?>

Indici regulați și unici

Pe lângă indexul primar, un tabel poate conține și indecși obișnuiți și unici. Spre deosebire de indexul primar, pot exista mai mulți dintre acești indici. Diferența dintre un index obișnuit și un index unic este că un index unic nu poate avea valori identice.

Declararea indexurilor obișnuite se face folosind cuvinte cheie cheie sau index. Dacă trebuie să declarați un index unic, atunci înainte cheie sau index este pus cuvânt cheie unic.

Exemplul 6:

Creați un index unic

Eliminarea indicilor

Eliminarea indexurilor se efectuează folosind comanda drop index folosind aceasta, orice index este șters.

Exemplul 8:

$conexiune = mysqli nou("localhost","root","","mybase");
$query = "picurare model index pe auto";
$rezultat = $conexiune->interogare($interogare);
?>

CREAȚI [UNIC | TEXT COMPLET | SPATIAL ] INDEX nume_index [ UTILIZARE = tip_index ] ON nume_tabel (coloane_index)

Cuvintele cheie UNIQUE, FULLTEXT și SPATIAL pot fi adăugate pentru a afișa proprietăți specifice indexului. Dacă niciunul nu este specificat, este creat un index neunic. Instrucțiunea CREATE INDEX nu poate fi utilizată pentru a crea un index PRIMARY KEY, trebuie utilizată o instrucțiune ALTER TABLE.

ALTER TABLE table_name ADD PRIMARY KEY (index_columns) ALTER TABLE table_name ADD INDEX (index_columns) ALTER TABLE table_name ADD FULLTEXT (index_columns) ALTER TABLE table_name ADD UNIQUE (index_columns) (index_columns) ALTER TABLE table_name (index_columns)

Dacă sunt specificate mai multe coloane, numele trebuie separate prin virgule. Dacă numele indexului nume_index nu este definit, este creat automat pe baza primei coloane indexate. În plus, instrucțiunea ALTER TABLE vă permite să eliminați indecși:

ALTER TABLE nume_tabel DROP nume_index ALTER TABLE nume_tabel DROP PRIMARY KEY

Indecșii pot fi abandonați folosind instrucțiunea DROP INDEX:

DROP INDEX nume_index ON nume_tabel DROP INDEX `PRIMARY` ON nume_tabel

Puteți utiliza operatorul USING pentru a defini algoritmul de indexare. TYPE este un sinonim pentru USING, pentru tipurile de tabel MyISAM și InnoDB poate fi BTREE. Pentru tabelele MEMORY, acesta ar putea fi HASH sau BTREE.

Indicii compoziți au o particularitate de utilizare, care este determinată de faptul că, dacă există un astfel de index, de exemplu, pentru coloane (col1, col2, col3) , orice prefix din stânga poate fi folosit pentru căutare. Adică, nu este nevoie să creați în plus indecși (col1) Şi (col1, col2) .

În ciuda tuturor avantajelor indexării, această operațiune are și dezavantaje. În primul rând, indexurile accelerează regăsirea datelor, dar încetinesc operațiunile de adăugare, ștergere și modificare a coloanelor indexate. Acest lucru se datorează faptului că cu cât un tabel are mai mulți indici, cu atât încetinirea operațiunilor de înregistrare este mai mare. În al doilea rând, fișierul index ocupă un anumit spațiu pe disc. La crearea cantitate mare indexuri, dimensiunea unui astfel de fișier poate atinge rapid maximul posibil (pentru sisteme moderne dimensiunea maximă a fișierului poate fi foarte mare).

Lista surselor folosite:

Paul Dubois, MySQL, ediția a 3-a.

Material preluat de pe site:

Indecii sunt utilizați pentru a găsi rapid rânduri cu o valoare specificată într-o coloană. Fără un index, un tabel este citit prin întregul tabel începând cu prima înregistrare până când sunt găsite rânduri care se potrivesc. Cu cât masa este mai mare, cu atât este mai mare suprafața. Dacă tabelul conține un index pe coloanele în cauză, atunci MySQL poate determina rapid poziția de căutare în mijlocul fișierului de date fără a parcurge toate datele. Pentru un tabel care conține 1000 de rânduri, aceasta va fi de cel puțin 100 de ori mai rapidă în comparație cu iterarea secvenţială a tuturor înregistrărilor. Cu toate acestea, în cazul în care aproape toate cele 1000 de rânduri trebuie accesate, citirile secvențiale vor fi mai rapide, deoarece nu sunt necesare căutări de disc.

Indecii sunt folosiți pentru:

Găsiți rapid rânduri care se potrivesc cu o clauză WHERE.
Preluați rânduri din alte tabele atunci când efectuați îmbinări.
Găsiți valorile MAX() sau MIN() pentru o anumită coloană indexată. Această operațiune este optimizată de un preprocesor care verifică dacă utilizați WHERE key_part_4 = constantă, peste toate părțile cheii compuse . În acest caz, MySQL va efectua o singură căutare pe cheie și va înlocui expresia cu constanta MIN(). Dacă toate expresiile sunt înlocuite cu o constantă, interogarea va returna imediat rezultatul: SELECT MIN(key_part2),MAX(key_part2) FROM table_name unde key_part1=10
Efectuați sortarea sau gruparea pe un tabel dacă aceste operații sunt efectuate pe prefixul din stânga al cheii utilizate (de exemplu ORDER BY key_part_1,key_part_2). Dacă toate părțile unei chei sunt urmate de DESC , atunci cheia este citită în ordine inversă (vezi secțiunea 5.2.7 Cum optimizează MySQL ORDER BY ).
În unele cazuri, interogarea poate fi optimizată pentru a prelua valori fără a accesa fișierul de date. Dacă toate coloanele utilizate dintr-un anumit tabel sunt numerice și formează prefixul din stânga pentru o anumită cheie, atunci pentru a oferi o viteză mai mare, valorile căutate pot fi preluate direct din arborele index: SELECT key_part3 FROM table_name WHERE key_part1=1

Să presupunem că următoarea instrucțiune SELECT este numită:

Mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

În MySQL, nu puteți utiliza un index parțial decât dacă coloanele formează prefixul din stânga acelui index. Să presupunem că aveți comenzile SELECT prezentate mai jos:

Mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

Prima comandă se uită numai la liniile cu „Patrick” , iar în al doilea rând numai cu „Pat” .

Următoarele comenzi SELECT nu vor folosi indecși:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE cheie_col LIKE alt_col;

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

MySQL 4.0 introduce o optimizare diferită a expresiei LIKE. Dacă se folosește expresia ... LIKE „%string%” și lungimea șirului este mai mare de 3 caractere, atunci MySQL va folosi algoritmul Turbo Boyer-Moore pentru a inițializa un model pentru șir și apoi va folosi acel model pentru a efectua căutarea mai rapidă.

Când căutați folosind nume_coloană este NULL, vor fi folosiți indecși dacă numele_coloană este un index.

MySQL folosește de obicei indexul care găsește cele mai puține rânduri. Un index este utilizat pe coloanele care sunt comparate folosind următorii operatori: =, >, >= și LIKE cu un prefix care nu conține un caracter joker, cum ar fi ceva% .

Următoarele clauze WHERE folosesc indecși:

Următoarele clauze WHERE Nu utilizați indici:

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

Dacă folosirea unui index necesită ca MySQL să parcurgă mai mult de 30% din rândurile dintr-un tabel dat (în astfel de cazuri, parcurgerea tabelului va fi probabil mult mai rapidă, deoarece vor fi necesare mai puține căutări). Rețineți că, dacă o interogare ca aceasta folosește LIMIT numai pentru subsetul de rânduri care este preluat, atunci MySQL va folosi oricum indexul, deoarece un număr mic de rânduri poate fi găsit mult mai rapid pentru a returna rezultatul.
Dacă intervalul de modificare a indexului poate conține valori NULL atunci când utilizați expresiile ORDER BY ... DESC.

Dacă aveți alte întrebări sau ceva nu este clar - bine ați venit pe site-ul nostru

Vizualizări