Mysql indeksi. Korištenje indeksa u MySQL-u. Serija o principima rada sa MySQL, ranijim materijalima

U projektu veoma važan faktor je brzina njegovog rada.
Jedna od metoda za povećanje brzine projekta je: ubrzavanje vremena potrebnog za dovršenje zahtjeva MySql baza podataka, smanjenjem vremena odgovora prilikom izvršavanja upita baze podataka.
Danas ćemo se upoznati s takvim alatom kao što je " Indeksi".
Korištenje indeksa će smanjiti vrijeme odgovora baze podataka MySql, što nam daje ogromne prednosti prilikom uzorkovanja podataka iz velikog DB i biće od velike koristi kada se koristi u velikim projektima sa velikim opterećenjem.
Pogledajmo šta su indeksi, koje vrste indeksa postoje i koje se koristi mogu izvući iz njih.

MySql Index je važan i koristan alat koji vam omogućava da optimizirate odabir iz baze podataka. Korištenje indeksa značajno skraćuje vrijeme izvršenja upita, omogućavajući vam da brže dohvatite podatke.
Primjetna razlika prilikom korištenja MySql indeksi može se vidjeti pri radu sa velikim stolovima.
Indeks je struktura koja pohranjuje vrijednosti jednog ili više stupaca A[s] tabele i reference na redove u kojima se nalaze ove vrijednosti. Za pohranjivanje indeksa, " binarnih stabala".

Pogledajmo primjer kako funkcioniraju indeksi:
Možete iskusiti prednosti indeksa pokretanjem SELECT zahtjev sa uslovom GDJE.
Imamo tabelu koja pohranjuje informacije o korisnicima.
Tabela ima kolone:

id - autoinkrement
ime - Ime
prezime - Prezime
starost - Starost

Prvo polje id je polje koje se automatski povećava i uvijek je jedinstveno, tako da mu po defaultu dodjeljujemo indeks.
Sada ćemo, koristeći ovu tabelu, odabrati sve korisnike čija je starost 20 godina.
Ovaj zahtjev će izgledati ovako:
SELECT * FROM korisnika WHERE starost = 20;
Ovaj zahtjev se ne čini komplikovanim i ne bi trebao biti opterećenje, ali ako je vaš DB veličine više od 1000 linija i broj takvih zahtjeva je više od jednog, zatim izvršenje ovog zahtjeva već će opteretiti bazu podataka i vrijeme utrošeno na pronalaženje više neće biti ono što se očekivalo.
Sada pokrenimo ovaj upit na bazi podataka veličine 600 MB i koja sadrži 15 miliona redova.
I tako, zahtjev je pokrenut i vrijeme izvršenja ovog zahtjeva je 10,562 sekunde.
Ponavljam izvršenje ovog zahtjeva još 2 puta, tako da je prosječno vrijeme izvršenja zahtjeva jasno:
Druga vožnja - 10.359
Treća vožnja - 10.715

Slažete se da je čekanje 10 sekundi za rezultat upita prilično dugo.
Hajde sada da definišemo" index" kolona Dob i ponovite zahtjev.
Vrijeme izvršenja upita bilo je 0,031 sekundi, što je znatno brže nego prije.
Dakle, vidimo značajno povećanje brzine prilikom upotrebe indeksi.

Vrste MySql indeksa:

PRIMARY KEY- Primarni ključ: primarni ključ koji vam omogućava da pohranite njegove vrijednosti kao jedinstvene unose u tabeli. Ovaj tip ključ mora biti prisutan u tabeli u jednom primjerku. Tipično, ovaj tip ključa je određen kolonom s imenom id.
UNIQUE- Jedinstveni ključ: Djelomično sličan primarnom ključu, po tome što vrijednosti stupca tablice moraju biti jedinstvene (ne ponavljane) i ne jednake NULL.
Kompozitni indeks: Ovaj tip indeksa uključuje više polja u indeksiranju. Ova vrsta indeksiranja se obično koristi u upitima u kojima je potrebno odabrati nekoliko polja (gdje je u uvjetu GDJE javlja se više od jednog parametra).

Primjer uzorka sa kompozitnim indeksom:
SELECT * FROM users WHERE age = 20 AND firstname = "Alex";
Kako kreirati indeks u MySql?
Postoji nekoliko načina za kreiranje indeksa.
Ispod je primjer kako kreirati index koristeći administrativni softver MySql baze podataka ( DB) - PhpMyAdmin i kako kreirati indeks koristeći upit baze podataka ( DB).

Kako kreirati jedinstveni indeks u MySql-u?
Primjer kreiranja jedinstvenog indeksa koristeći PhpMyAdmin i kreiranje jedinstvenog indeksa pomoću upita:

Kako kreirati složeni indeks u MySql-u?
Primjer kreiranja kompozitnog indeksa koristeći PhpMyAdmin i kreiranje kompozitnog indeksa pomoću upita:

Kako izbrisati MySql indeks?

Brojni nedostaci pri korištenju indeksa:
Ako indeksi daju takvo povećanje tokom izvršavanja upita, zašto onda ne definirati indekse na svim stupcima tabele?

Tokom indeksiranja stupaca MySql tabele kreiraju se dodatne tabele u kojima se pohranjuju sortirani podaci. A takvi podaci zauzimaju određenu količinu fizičke memorije.
Prilikom izvršavanja većeg broja upita, prisustvo indeksa može igrati potpuno suprotnu ulogu i povećati vrijeme izvršenja upita.
Ako postoji indeks na tabeli, INSERT upit traje duže da se dovrši nego na tablici koja ne sadrži indekse.
Upotreba indeksa također može imati negativan učinak pri izvršavanju upita pomoću izraza LIKE u stanju GDJE. Na primjer korištenjem izraza LIKE na indeksiranom polju neće dati nikakvo povećanje brzine izvršavanja.

Razmotrili smo samo neke od glavnih nedostataka pri korištenju indeksa. Stvarna količina može varirati.

U prethodnim člancima sam često spominjao indeksi u MySQL. i obećao sam da ću ti uskoro pričati o njima. Dakle, došlo je ovo vrijeme i danas ćete naučiti o tome MySQL indeksi, o njihovoj namjeni i kako ih kreirati.

Indeksi se koriste za ubrzanje preuzimanja podataka iz tabela baze podataka. Zapravo, indeks u MySQL- ovo je sortiranje određenog polja u tabeli. To jest, ako je polje napravljeno kao indeks, onda će cijela tabela biti sortirana po ovom polju. Zašto je ovo korisno?

Recimo da naša tabela sadrži 1000000 evidencije. Svaki unos ima jedinstveni identifikator ID. I recimo da moramo izvući zapis iz ID = 530124. Ako nema indeksa, onda MySQLće iterirati kroz sve zapise u tabeli dok ne pronađe onaj koji mu je potreban. U najgorem slučaju, biće primoran da se sredi 1000000 evidencije. Naravno, ovo će biti jako dugo. A kada bi postojao indeks (tj. polje bi se sortiralo), onda bi zapis bio uzorkovan u prosjeku u 100.000 puta brže. Kao što vidite, prednosti su očigledne.

Međutim, indeksi imaju jednu značajnu manu, koja ne dozvoljava da svako polje tabele bude indeks. U stvari, indeks je druga tabela, ali jednostavno sa sortiranim odgovarajućim poljem. Odnosno, pravljenjem indeksa na jednom polju, kreirate drugu potpuno istu tabelu, koja će zauzeti dodatni prostor na disku.

Još jedan mali minus indeksi u MySQL Problem je u tome što zahtjevi za umetanjem novih zapisa prisiljavaju da se tabela ponovo sortira. Kao rezultat toga, umetanje novih zapisa će trajati malo duže nego inače. Ali nemojte zaboraviti da u većini slučajeva to morate raditi mnogo rjeđe od uzorkovanja, tako da ovaj minus nije značajan.

Kako napraviti indeks u MySQL-u?

Za primarne ključeve ( PRIMARY KEY) indeks se kreira automatski, ali za ostala polja redoslijed radnji je PHPMyAdmin sljedeći:

I na kraju, želio bih napraviti kratak sažetak kako biste razumjeli: " Kada kreirati MySQL indekse":

Ako se polje uzorkuje vrlo često, onda ga treba napraviti indeks.
Ako se zapisi dodaju u tablicu vrlo često, a uzorkovanje se događa rijetko (ovo se ponekad dešava), onda nema potrebe za kreiranjem indeksa.

I još nešto. Ako iznenada vidite da su vaši zahtjevi za uzorkovanje vrlo spori, analizirajte razlog za to. Najvjerovatnije je potrebno samo dodati indeks. Općenito, testirajte i sve će vam biti jasno.

Indeksi se koriste za brzo pronalaženje redova sa određenom vrijednošću u jednoj koloni. Bez indeksa, tabela se čita kroz cijelu tabelu počevši od prvog zapisa dok se ne pronađu odgovarajući redovi. Što je veći sto, veći su troškovi. Ako tabela sadrži indeks dotičnih kolona, onda MySQL može brzo odrediti poziciju pretraživanja u sredini datoteke podataka bez prolaska kroz sve podatke. Za tabelu koja sadrži 1000 redova, ovo će biti najmanje 100 puta brže od iteracije kroz sve zapise uzastopno. Međutim, u slučaju kada je potrebno pristupiti skoro svim 1000 redova, sekvencijalna čitanja će biti brža jer nije potrebno traženje diska.

Svi MySQL indeksi (PRIMARY, UNIQUE i INDEX) su pohranjeni kao B-stabla. Nizovi se automatski komprimiraju, uklanjajući prefiks i razmake na kraju (pogledajte odjeljak 6.5.7 Sintaksa naredbe CREATE INDEX).

Indeksi se koriste za:

Brzo pronađite redove koji odgovaraju klauzuli WHERE.
Dohvati redove iz drugih tabela prilikom izvođenja spajanja.
Pronađite MAX() ili MIN() vrijednosti za dati indeksirani stupac. Ovu operaciju optimizira predprocesor koji provjerava da li koristite WHERE dio_4 = konstantu, preko svih dijelova kompozitnog ključa SELECT MIN(key_part2),MAX(key_part2) FROM table_name gdje je ključ_part1=10
Izvršite sortiranje ili grupiranje u tabeli ako se ove operacije izvode na krajnjem lijevom prefiksu korištenog ključa (na primjer, ORDER BY key_part_1,key_part_2). Ako iza svih dijelova ključa stoji DESC , tada se ključ čita obrnutim redoslijedom (pogledajte odjeljak 5.2.7 Kako MySQL optimizira ORDER BY).
U nekim slučajevima, upit se može optimizirati za dohvaćanje vrijednosti bez pristupa datoteci podataka. Ako su svi korišteni stupci u određenoj tablici numerički i čine krajnji lijevi prefiks za određeni ključ, tada da bi se obezbijedila veća brzina, tražene vrijednosti se mogu preuzeti direktno iz indeksnog stabla: SELECT key_part3 FROM table_name WHERE key_part1=1

Pretpostavimo da je pozvana sljedeća SELECT izjava:

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

Ako indeks sa više stupaca postoji na stupcima col1 i col2, tada se odgovarajući redovi mogu odabrati direktno. U slučaju kada odvojeni indeksi postoje na stupcima col1 i col2, optimizator pokušava pronaći najrestriktivniji indeks određujući koji indeks pronalazi najmanje redova i koristi taj indeks da dohvati te redove.

Ako data tablica ima indeks sa više kolona, onda optimizator može koristiti bilo koji krajnji lijevi prefiks tog indeksa za pronalaženje redova. Na primjer, ako imate indeks na tri kolone (stupac1,stupac2,stupac3), onda postoji potencijal za indeksirana pretraživanja na (kol1), (kol1,stupac2) i (kol1,kol2,kol3).

U MySQL-u, ne možete koristiti djelomični indeks osim ako kolone ne čine krajnji lijevi prefiks tog indeksa. Pretpostavimo da imate naredbe SELECT prikazane u nastavku:

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

Ako indeks postoji na (col1,col2,col3), tada samo prvi upit prikazan iznad koristi taj indeks. Drugi i treći upit zaista uključuju indeksirane kolone, ali (col2) i (col2,col3) nisu krajnji lijevi dio (col1,col2,col3) prefiksa.

MySQL takođe koristi indekse za LIKE poređenja ako je argument u izrazu LIKE konstantni niz koji ne počinje zamjenskim znakom. Na primjer, sljedeće SELECT naredbe koriste indekse:

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

Prva komanda gleda samo redove sa "Patrick"

Sljedeće SELECT naredbe neće koristiti indekse:

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

U prvoj naredbi, vrijednost LIKE počinje zamjenskim znakom. U drugoj naredbi, vrijednost LIKE nije konstanta.

MySQL 4.0 uvodi drugačiju optimizaciju izraza LIKE. Ako se koristi izraz... LIKE "%string%" i dužina niza je veća od 3 znaka, tada će MySQL koristiti Turbo Boyer-Moore algoritam da inicijalizira obrazac za string, a zatim koristi taj obrazac za izvođenje brža pretraga.

Kada pretražujete koristeći column_name IS NULL, indeksi će se koristiti ako je column_name indeks.

MySQL obično koristi indeks koji pronalazi najmanje redova. Indeks se koristi na stupcima koji se upoređuju pomoću sljedećih operatora: =, >, >=,

Ako indeks ne pokriva sve I nivoe u klauzuli WHERE, onda se ne koristi za optimizaciju upita. Drugim riječima: da bi indeks bio upotrebljiv, prefiks tog indeksa mora se pojaviti u svakoj grupi I.

Sljedeće klauzule WHERE koriste indekse:

GDJE index_part1=1 I index_part2=2 I other_column=3 ... GDJE index=1 ILI A=10 I index=2 /* indeks = 1 ILI indeks = 2 */ ... GDJE index_part1="zdravo" I index_part_3= 5 /* optimizirano kao "index_part1="hello" */ ... GDJE index1=1 i index2=2 ili index1=3 i index3=3; /* Možete koristiti indeks na index1, ali ne na index2 ili indeks 3 */

Sljedeće klauzule WHERE Ne koristite indekse:

GDJE index_part2=1 I index_part3=2 /* index_part_1 se ne koristi */ ... GDJE index=1 ILI A=10 /* Indeks se ne koristi u oba dijela AND */ ... GDJE index_part1=1 ILI index_part2 =10 /* Ne postoji indeks koji pokriva sve redove*/

U nekim slučajevima, MySQL ne koristi indeks iako je to moguće. Neki primjeri takvih situacija su dati u nastavku:

Ako korištenje indeksa zahtijeva da MySQL pređe više od 30% redova u datoj tabeli (u takvim slučajevima, obilazak tabele će verovatno biti mnogo brži jer će biti potrebno manje pregleda). Jedna stvar koju treba imati na umu je da ako upit poput ovog koristi LIMIT samo na podskupu redova koji se preuzimaju, onda će MySQL ionako koristiti indeks, budući da se mali broj redova može pronaći mnogo brže za vraćanje rezultata.
Ako raspon promjene indeksa može sadržavati NULL vrijednosti kada koristite izraze ORDER BY ... DESC.

CREATE INDEX index_name NA tbl_name (naziv_kolone[(dužina)],...)

Naredba CREATE INDEX u verzijama MySQL-a prije 3.22 ne radi ništa. U verziji 3.22 i novijim, CREATE INDEX je ekvivalentna naredbi ALTER TABLE u smislu kreiranja indeksa. Pogledajte odjeljak 6.5.4 ALTER TABLE Sintaksa izraza.

Obično se svi indeksi kreiraju na tablici kada se sama tablica kreira naredbom CREATE TABLE. Pogledajte odjeljak 6.5.3 Sintaksa izraza CREATE TABLE. CREATE INDEX vam omogućava da dodate indekse postojećim tabelama.

Lista kolona oblika (col1,col2,...) kreira indeks na više kolona. Vrijednosti indeksa se formiraju spajanjem vrijednosti navedenih stupaca.

Za stupce CHAR i VARCHAR, parametar col_name(length) može kreirati indekse koji koriste samo dio stupca (za BLOB i TEXT stupce, morate navesti dužinu). Naredba u nastavku kreira indeks koristeći prvih 10 znakova kolone imena:

Mysql> CREATE INDEX part_of_name NA kupcu (name(10));

Budući da se većina imena obično razlikuje jedno od drugog u prvih 10 znakova, ovaj indeks ne bi trebao biti mnogo sporiji od indeksa koji se kreira iz cijelog stupca imena. Osim toga, korištenjem djelomičnih kolona za indekse, možete učiniti indeksnu datoteku mnogo manjom, što štedi prostor na disku i također povećava brzinu INSERT operacija!

Imajte na umu da u MySQL 3.23.2 i novijim verzijama, za MyISAM tabele, možete dodati indeks samo na stupce koji mogu prihvatiti NULL vrijednosti ili na BLOB/TEXT stupce.

Za više informacija o tome kako MySQL koristi indekse, pogledajte odjeljak 5.4.3 Korištenje indeksa u MySQL-u.

Opcija FULLTEXT se može koristiti za indeksiranje samo VARCHAR i TEXT stupaca i samo u MyISAM tablicama. Ova funkcija je dostupna samo u MySQL verziji 3.23.23 i novijim. Vidi odjeljak

Indeksiranje tablice se koristi za smanjenje vremena pretraživanja podataka. Bez indeksa, pretraga se odvija jednostavnim pretraživanjem svih vrijednosti tablice, što traje prilično dugo.

Sledeći tipovi indeksa se koriste u mysql tabelama:

Primarni ključ
Jedinstveni indeks
Redovni indeks
Indeks punog teksta

Osim gore navedenih indeksa, također se koristi puni tekst indeks, ali se o tome ne govori u ovom članku.

Primarni ključ

Primarni ključ se koristi za jedinstvenu identifikaciju zapisa u tabeli. Među glavnim tačkama u vezi s primarnim ključem treba napomenuti sljedeće:

1. tabela ne može imati samo jedan jedinstveni ključ;

2. vrijednost ključa mora biti jedinstvena unutar tabele;

3. Primarni ključ ne može biti tipa NULL.

Primjer kreiranja primarnog ključa:

Kreiranje primarnog ključa prilikom kreiranja tabele u opisu polja

Primjer 3:

Ako je primarni ključ tekstualno polje, tada morate u zagradama navesti broj znakova uključenih u indeksiranje.

Primjer 5:

Ako je tabela već kreirana, tada možete dodati primarni indeks na sljedeći način.

$query = "ALTER tabelu automatski DODAJ OGRANIČENJA PRIMARNI KLJUČ (id_number)";
$result = $connection->query($query);
?>

Redovni i jedinstveni indeksi

Pored primarnog indeksa, tabela takođe može sadržati regularne i jedinstvene indekse. Za razliku od primarnog indeksa, može postojati nekoliko ovih indeksa. Razlika između redovnog i jedinstvenog indeksa je u tome što jedinstveni indeks ne može imati identične vrijednosti.

Deklarisanje regularnih indeksa se vrši pomoću ključnih reči ključ ili index. Ako trebate deklarirati jedinstveni indeks, prije ključ ili index ključna riječ je stavljena jedinstveno.

Primjer 6:

Kreirajte jedinstveni indeks