Ce este un index?
Indexurile din MySQL sortează datele într-un mod secvențial organizat. Acestea sunt create pe coloana (coloanele) care vor fi utilizate pentru filtrarea datelor. Gândiți-vă la un index ca la o listă ordonată alfabetic. Este mai ușor să căutați nume 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. Acest lucru se datorează faptului că MySQL creează un nou bloc de index de fiecare dată când datele sunt adăugate sau actualizate în tabel. În general, indicii ar trebui utilizați pe tabele ale căror date nu se schimbă frecvent, dar sunt utilizate foarte mult în interogările de căutare selectate.
La ce folosește un index?
Nimănui nu îi plac sistemele lente. Performanța ridicată a sistemului este de primă importanță în aproape toate sistemele de baze de date. Majoritatea companiilor investesc foarte mult în hardware, astfel încât preluarea și manipularea datelor să poată fi mai rapide. Dar există o limită a investițiilor hardware pe care o întreprindere 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 stocate aleatoriu în tabelele bazei de date. Interogările de căutare trebuie să parcurgă toate înregistrările stocate aleator, una după alta, pentru a localiza datele dorite. Acest lucru are ca rezultat baze de date cu performanțe slabe atunci când vine vorba de preluarea datelor din tabele mari. Prin urmare, se utilizează indexuri care sortează datele pentru a facilita căutarea.
Sintaxă: Creați index
Indexurile pot fi definite în 2 moduri
- În momentul creării mesei
- După ce tabela a fost creată
Pentru myflixdb, ne așteptăm la numeroase căutări în baza de date cu numele complet.
Vom adăuga coloana „full_names” 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 bancul de lucru MySQL împotriva „myflixdb”.
Reîmprospătarea myflixdb arată tabela nou creată numită members_indexed.
Tabela „Notă” members_indexed are „full_names” în nodul indexurilor.
Pe măsură ce baza membrilor se extinde și numărul de înregistrări crește, interogările de căutare din tabelul members_indexed care utilizează clauzele WHERE și ORDER BY vor fi mult mai rapide în comparație cu cele efectuate în tabelul membrilor 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 de căutare pe acesta sunt foarte lente. Durează prea mult să returneze rezultatele. După investigarea problemei, descoperim că putem îmbunătăți foarte mult performanța sistemului prin crearea INDEX în coloana cea mai frecvent utilizată în clauza WHERE.
Putem utiliza 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 de pe tabelul de filme sunt foarte lente și că 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 pe câmpul de titlu din tabelul cu filme.
Aceasta înseamnă că toate interogările de căutare de pe tabelul cu filme folosind „titlul” vor fi mai rapide.
Cu toate acestea, interogările de căutare din alte câmpuri din tabelul cu filme vor fi totuși mai lente comparativ cu cele bazate pe câmpul indexat.
Notă: Puteți crea indexuri pe mai multe coloane, dacă este necesar, în funcție de câmpurile pe care intenționați să le utilizați pentru motorul dvs. de căutare în baza de date.
Dacă doriți să vizualizați indexurile definite pe un anumit tabel, puteți utiliza următorul script pentru a face acest lucru.
SHOW INDEXES FROM table_name;
Să aruncăm acum o privire asupra tuturor indexurilor definite pe masa filmelor din myflixdb.
SHOW INDEXES FROM `movies`;
Executarea scriptului de mai sus în MySQL workbench împotriva myflixdb ne oferă rezultate privind indexul creat.
Notă: Cheile primare și externe de pe masă au fost deja indexate de MySQL. Fiecare index are propriul său nume unic și este afișată și coloana pe care este definit.
Sintaxă: drop index
Comanda drop este utilizată pentru a elimina indexurile deja definite pe o masă.
Pot exista momente în care ați definit deja un index pe un tabel care este actualizat frecvent. Poate doriți să eliminați indexurile de pe un astfel de tabel pentru a îmbunătăți performanțele interogărilor UPDATE și INSERT. Sintaxa de bază utilizată pentru a fixa un index pe un tabel este următoarea.
DROP INDEX `index_id` ON `table_name`;
Să vedem acum un exemplu practic.
DROP INDEX ` full_names` ON `members_indexed`;
Executarea comenzii de mai sus renunță la 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 interogărilor de căutare MySQL.
- Indexurile pot fi definite la crearea unui tabel sau adăugate ulterior după ce tabelul a fost deja creat.
- Puteți defini indici pe mai multe coloane dintr-un tabel.
- SHOW INDEX FROM table_name este utilizat pentru a afișa indexurile definite pe o tabelă.
- Comanda DROP este utilizată pentru a elimina un index definit pe un tabel dat.