MySQL Index Tutorial – Creați, adăugați și plasați

Ce este un index?

Indici în MySQL sortarea datelor într-un mod secvenţial organizat. Acestea sunt create pe coloanele care vor fi utilizate pentru filtrarea datelor. Gândiți-vă la un index ca la o listă sortată alfabetic. Este mai ușor să căutați numele care au fost sortate în ordine alfabetică decât cele care nu sunt sortate.

Utilizarea unui index pe tabele care sunt actualizate frecvent poate duce la performanțe slabe. Asta pentru ca MySQL creează un nou bloc de index de fiecare dată când datele sunt adăugate sau actualizate în tabel. În general, indecșii ar trebui să fie utilizați pe tabele ale căror date nu se modifică frecvent, dar sunt folosite foarte mult în anumite interogări de căutare.

La ce folosește un index?

Nimănui nu-i plac sistemele lente. Performanța ridicată a sistemului este de o importanță primordială în aproape toate sistemele de baze de date. Majoritatea companiilor investesc mult în hardware, astfel încât recuperările și manipulările datelor să poată fi mai rapide. Dar există o limită a investițiilor hardware pe care o afacere le poate face. Optimizarea bazei de date este o soluție mai ieftină și mai bună.

Încetinirea timpului de răspuns se datorează de obicei înregistrărilor care sunt stocate aleatoriu în tabelele bazei de date. Interogările de căutare trebuie să parcurgă toate înregistrările stocate aleatoriu una după alta pentru a localiza datele dorite. Acest lucru are ca rezultat bazele de date cu performanță slabă atunci când vine vorba de preluarea datelor din tabele mari. Prin urmare, sunt folosiți indici care sortează datele pentru a facilita căutarea.

Sintaxă: Creați index

Indicii pot fi definiți în 2 moduri

1. În momentul creării mesei
2. După ce tabelul a fost creat

Exemplu:

Pentru myflixdb ne așteptăm la o mulțime de căutări în baza de date cu numele complet.

Vom adăuga coloana „nume_complete” la Index într-un nou tabel „members_indexed”.

Scriptul prezentat mai jos ne ajută să realizăm acest lucru.

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

Executați scriptul SQL de mai sus în MySQL banc de lucru împotriva „myflixdb”.

Reîmprospătarea myflixdb arată tabelul nou creat numit members_indexed.

"Notă" Tabelul members_indexed are „full_names” în nodul de indexuri.

Pe măsură ce baza de membri se extinde și numărul de înregistrări crește, interogările de căutare din tabelul members_indexed care folosesc clauzele WHERE și ORDER BY vor fi mult mai rapide în comparație cu cele efectuate în tabelul de membri fără indexul definit.

Adăugați sintaxa de bază a indexului

Exemplul de mai sus a creat indexul la definirea tabelului bazei de date. Să presupunem că avem deja un tabel definit și căutările pe acesta sunt foarte lente. Durează prea mult pentru a returna rezultatele. După investigarea problemei, descoperim că putem îmbunătăți considerabil performanța sistemului prin crearea INDEX pe coloana cea mai frecvent utilizată din clauza WHERE.

Putem folosi următoarea interogare pentru a adăuga index

CREATE INDEX id_index ON table_name(column_name);

Să presupunem că interogările de căutare din tabelul de filme sunt foarte lente și dorim să folosim un index pe „titlul filmului” pentru a accelera interogările, putem folosi următorul script pentru a realiza acest lucru.

CREATE INDEX `title_index` ON `movies`(`title`);

Executarea interogării de mai sus creează un index în câmpul titlu din tabelul de filme.

Aceasta înseamnă că toate interogările de căutare din tabelul de filme folosind „titlu” vor fi mai rapide.

Interogările de căutare din alte câmpuri din tabelul de filme vor fi totuși mai lente în comparație cu cele bazate pe câmpul indexat.

Notă: Puteți crea indici pe mai multe coloane, dacă este necesar, în funcție de câmpurile pe care intenționați să le utilizați pentru motorul de căutare a bazei de date.

Dacă doriți să vizualizați indecșii definiți într-un anumit tabel, puteți utiliza următorul script pentru a face asta.

SHOW INDEXES FROM table_name;

Să aruncăm o privire acum la toți indecșii definiți în tabelul de filme din myflixdb.

SHOW INDEXES FROM `movies`;

Executarea scriptului de mai sus în MySQL banc de lucru împotriva myflixdb ne oferă rezultate la indexul creat.

Notă: Cheile primare și externe de pe tabel au fost deja indexate de MySQL. Fiecare index are propriul nume unic și este afișată și coloana pe care este definit.

Sintaxă: Drop index

Comanda drop este folosită pentru a elimina indecșii deja definiți dintr-un tabel.

Pot exista momente când ați definit deja un index pe un tabel care este actualizat frecvent. Poate doriți să eliminați indecșii de pe un astfel de tabel pentru a îmbunătăți performanța interogărilor UPDATE și INSERT. Sintaxa de bază folosită pentru a plasa un index pe un tabel este următoarea.

DROP INDEX `index_id` ON `table_name`;

Să ne uităm acum la un exemplu practic.

DROP INDEX ` full_names` ON `members_indexed`;

Executarea comenzii de mai sus scade indexul cu id ` full_names ` din tabelul members_indexed.

Rezumat

• Indicii sunt foarte puternici atunci când vine vorba de îmbunătățirea semnificativă a performanței MySQL interogări de căutare.
• Indecșii pot fi definiți la crearea unui tabel sau adăugați ulterior după ce tabelul a fost deja creat.
• Puteți defini indecși pe mai multe coloane dintr-un tabel.
• SHOW INDEX FROM table_name este folosit pentru a afișa indecșii definiți pe un tabel.
• Comanda DROP este folosită pentru a elimina un index definit dintr-un tabel dat.