MySQL Vodič za indeks – Stvaranje, dodavanje i ispuštanje

Što je indeks?

Indeksi u MySQL sortirati podatke na organizirani sekvencijalni način. Oni se stvaraju u stupcima koji će se koristiti za filtriranje podataka. Zamislite indeks kao popis poredan abecednim redom. Lakše je tražiti imena koja su poredana abecednim redom nego ona koja nisu sortirana.

Korištenje indeksa na tablicama koje se često ažuriraju može rezultirati lošim performansama. Ovo je zbog MySQL stvara novi blok indeksa svaki put kada se ti podaci dodaju ili ažuriraju u tablici. Općenito, indekse treba koristiti na tablicama čiji se podaci ne mijenjaju često, ali se često koriste u odabranim upitima za pretraživanje.

Za što se koristi indeks?

Nitko ne voli spore sustave. Visoke performanse sustava od primarne su važnosti u gotovo svim sustavima baza podataka. Većina tvrtki ulaže velika sredstva u hardver kako bi dohvaćanje podataka i manipulacija bili brži. Ali postoji ograničenje ulaganja u hardver koje tvrtka može napraviti. Optimiziranje baze podataka je jeftinije i bolje rješenje.

Sporost u vremenu odgovora obično je posljedica toga što se zapisi nasumično pohranjuju u tablice baze podataka. Upiti za pretraživanje moraju proći kroz čitave nasumično pohranjene zapise jedan za drugim kako bi pronašli željene podatke. To rezultira lošim performansama baza podataka kada je u pitanju dohvaćanje podataka iz velikih tablica. Stoga se koriste indeksi koji razvrstavaju podatke radi lakšeg pretraživanja.

Sintaksa: Stvori indeks

Indeksi se mogu definirati na 2 načina

1. U trenutku izrade tablice
2. Nakon što je tablica kreirana

Primjer:

Za naš myflixdb očekujemo puno pretraživanja baze podataka na puno ime.

Dodat ćemo stupac “puna_imena” u indeks u novoj tablici “članovi_indeksirani”.

Skripta prikazana u nastavku pomaže nam u tome.

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;

Izvršite gornju SQL skriptu MySQL radni stol protiv “myflixdb”.

Osvježavanje myflixdb prikazuje novostvorenu tablicu pod nazivom members_indexed.

"Bilješka" members_indexed tablica ima “puna_imena” u čvoru indeksa.

Kako se baza članova širi i broj zapisa raste, upiti za pretraživanje na tablici members_indexed koja koristi klauzule WHERE i ORDER BY bit će mnogo brži u usporedbi s onima koji se izvode u tablici članova bez definiranog indeksa.

Dodajte osnovnu sintaksu indeksa

Gornji primjer kreirao je indeks prilikom definiranja tablice baze podataka. Pretpostavimo da već imamo definiranu tablicu i upiti za pretraživanje na njoj su vrlo spori. Predugo im treba da vrate rezultate. Nakon što smo istražili problem, otkrili smo da možemo znatno poboljšati performanse sustava stvaranjem INDEX-a na najčešće korištenom stupcu u WHERE klauzuli.

Možemo koristiti sljedeći upit za dodavanje indeksa

CREATE INDEX id_index ON table_name(column_name);

Pretpostavimo da su upiti za pretraživanje na tablici filmova vrlo spori i želimo upotrijebiti indeks na “naslovu filma” da bismo ubrzali upite, možemo upotrijebiti sljedeću skriptu da to postignemo.

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

Izvršavanjem gornjeg upita stvara se indeks u polju naslova u tablici filmova.

To znači da će svi upiti za pretraživanje na tablici filmova koji koriste "naslov" biti brži.

Upiti za pretraživanje na drugim poljima u tablici filmova ipak će biti sporiji u usporedbi s onima koji se temelje na indeksiranom polju.

Bilješka: Po potrebi možete stvoriti indekse na više stupaca, ovisno o poljima koja namjeravate koristiti za svoju tražilicu baze podataka.

Ako želite vidjeti indekse definirane na određenoj tablici, možete koristiti sljedeću skriptu za to.

SHOW INDEXES FROM table_name;

Pogledajmo sada sve indekse definirane na tablici filmova u myflixdb.

SHOW INDEXES FROM `movies`;

Izvršavanje gornje skripte u MySQL radna tezga u odnosu na myflixdb daje nam rezultate o stvorenom indeksu.

Bilješka: Primarni i strani ključevi u tablici već su indeksirani prema MySQL. Svaki indeks ima svoje jedinstveno ime, a prikazan je i stupac na kojem je definiran.

Sintaksa: Ispusti indeks

Naredba drop koristi se za uklanjanje već definiranih indeksa na tablici.

Ponekad ste već definirali indeks na tablici koja se često ažurira. Možda ćete htjeti ukloniti indekse na takvoj tablici da poboljšate performanse UPDATE i INSERT upita. Osnovna sintaksa koja se koristi za ispuštanje indeksa na tablicu je sljedeća.

DROP INDEX `index_id` ON `table_name`;

Pogledajmo sada praktičan primjer.

DROP INDEX ` full_names` ON `members_indexed`;

Izvršavanjem gornje naredbe ispušta se indeks s ID-om ` full_names ` iz tablice members_indexed.

Rezime

• Indeksi su vrlo moćni kada se radi o značajnom poboljšanju performansi MySQL upite za pretraživanje.
• Indeksi se mogu definirati prilikom izrade tablice ili dodati kasnije nakon što je tablica već izrađena.
• Možete definirati indekse na više od jednog stupca u tablici.
• SHOW INDEX FROM table_name se koristi za prikaz definiranih indeksa na tablici.
• Naredba DROP koristi se za uklanjanje definiranog indeksa na danoj tablici.