Indekset Mysql. Përdorimi i indekseve në MySQL. Një seri mbi parimet e punës me MySQL, materiale të mëparshme

Në një projekt, një faktor shumë i rëndësishëm është shpejtësia e punës së tij.
Një nga metodat për të rritur shpejtësinë e një projekti është: përshpejtimi i kohës që duhet për të përfunduar një kërkesë Baza e të dhënave MySql, duke reduktuar kohën e përgjigjes gjatë ekzekutimit të një pyetjeje të bazës së të dhënave.
Sot do të njihemi me një mjet të tillë si " Indekset".
Përdorimi i indekseve do të reduktojë kohën e përgjigjes së bazës së të dhënave MySql, e cila na jep avantazhe të mëdha kur kampionojmë të dhëna nga një i madh DB dhe do të jetë me përfitim të madh kur përdoret në projekte të mëdha me ngarkesë të lartë.
Le të shohim se çfarë janë indekset, çfarë lloje indeksesh ekzistojnë dhe çfarë përfitimesh mund të merrni prej tyre.

Indeksi MySqlështë një mjet i rëndësishëm dhe i dobishëm që ju lejon të optimizoni përzgjedhjen nga baza e të dhënave. Përdorimi i indekseve redukton ndjeshëm kohën e ekzekutimit të pyetjeve, duke ju lejuar të rikuperoni të dhënat më shpejt.
Ndryshimi i dukshëm gjatë përdorimit Indekset MySql mund të shihet kur punoni me tavolina të mëdha.
Indeksiështë një strukturë që ruan vlerat e një ose më shumë kolonave A tabelat [s] dhe referenca për rreshtat ku ndodhen këto vlera. Për të ruajtur indekset, " pemë binare".

Le të shohim një shembull se si funksionojnë indekset:
Ju mund të përjetoni përfitimet e indekseve duke vrapuar ZGJIDH kërkesë me kusht KU.
Ne kemi një tabelë që ruan informacione për përdoruesit.
Tabela ka kolona:

id - rritje automatike
emri - Emri
mbiemri - Mbiemri
mosha - Mosha

Fusha e parë idështë një fushë me rritje automatike dhe gjithmonë unike, kështu që ne i caktojmë asaj një indeks si parazgjedhje.
Tani, duke përdorur këtë tabelë, ne do të zgjedhim të gjithë përdoruesit, mosha e të cilëve është 20 vjeç.
Kjo kërkesë do të duket si kjo:
SELECT * NGA përdoruesit KU mosha = 20;
Kjo kërkesë nuk duket të jetë e ndërlikuar dhe nuk duhet të jetë një barrë, por nëse juaja DB madhësia e më shumë se 1000 rreshtave dhe numri i kërkesave të tilla është më shumë se një, pastaj ekzekutimi të kësaj kërkese tashmë do të vendosë një ngarkesë në bazën e të dhënave dhe koha e shpenzuar për rikthim nuk do të jetë më ajo që pritej.
Tani le ta ekzekutojmë këtë pyetje në një bazë të dhënash me një madhësi prej 600 MB dhe që përmban 15 milionë rreshta.
Dhe kështu, kërkesa niset dhe koha e ekzekutimit të kësaj kërkese është 10.562 sekonda.
E përsëris ekzekutimin e kësaj kërkese edhe 2 herë, në mënyrë që koha mesatare e ekzekutimit të kërkesës të jetë e qartë:
Vrapimi i dytë - 10,359
Ecuria e tretë - 10,715

Pajtohu që të presësh 10 sekonda për një rezultat të pyetjes është një kohë mjaft e gjatë.
Tani le të përcaktojmë " indeks kolona " mosha dhe përsëris kërkesën.
Koha e ekzekutimit të pyetjes ishte 0.031 sekonda, që është dukshëm më e shpejtë se më parë.
Kështu, ne shohim një rritje të konsiderueshme të shpejtësisë gjatë përdorimit indekset.

Llojet e indekseve MySql:

ÇELËSI PRIMAR- Çelësi primar: Çelësi kryesor që ju lejon të ruani vlerat e tij si hyrje unike në tabelë. Ky llojçelësi duhet të jetë i pranishëm në tabelë në një kopje të vetme. Në mënyrë tipike, ky lloj çelësi përcaktohet nga një kolonë me emrin id.
UNIKE- Çelësi unik: Pjesërisht i ngjashëm me një çelës primar, në atë që vlerat e kolonës së tabelës duhet të jenë unike (të mos përsëriten) dhe jo të barabarta NULL.
Indeksi i përbërë: Ky lloj indeksi përfshin fusha të shumta në indeksim. Ky lloj indeksimi zakonisht përdoret në pyetje në të cilat është e nevojshme të zgjidhen disa fusha (ku në kusht KU ndodh më shumë se një parametër).

Shembull i një kampioni me një indeks të përbërë:
SELECT * NGA përdoruesit KU mosha = 20 DHE emri = "Alex";
Si të krijoni një indeks në MySql?
Ka disa mënyra për të krijuar një indeks.
Më poshtë është një shembull se si të krijoni indeks duke përdorur softuerin e administrimit MySql bazat e të dhënave ( DB) - PhpMyAdmin dhe si të krijoni një indeks duke përdorur një pyetje të bazës së të dhënave ( DB).

Si të krijoni një indeks unik në MySql?
Shembull i krijimit të një indeksi unik duke përdorur PhpMyAdmin dhe duke krijuar një indeks unik duke përdorur pyetjen:

Si të krijoni një indeks kompleks në MySql?
Shembull i krijimit të një indeksi të përbërë duke përdorur PhpMyAdmin dhe duke krijuar një indeks të përbërë duke përdorur pyetjen:

Si të fshini indeksin MySql?

Një sërë disavantazhesh gjatë përdorimit të indekseve:
Nëse indekset japin një rritje të tillë gjatë ekzekutimit të pyetjes, atëherë pse të mos përcaktohen indekset në të gjitha kolonat e tabelës?

Gjatë indeksimit të kolonës MySql tabelat krijohen tabela shtesë në të cilat ruhen të dhënat e renditura. Dhe të dhëna të tilla marrin një sasi të caktuar të memories fizike.
Gjatë ekzekutimit të një numri pyetjesh, prania e indekseve mund të luajë një rol krejtësisht të kundërt dhe të rrisë kohën e ekzekutimit të pyetjes.
Nëse ka një indeks në tabelë, INSERT pyetja kërkon më shumë kohë për t'u plotësuar sesa në një tabelë që nuk përmban indekse.
Përdorimi i indekseve gjithashtu mund të ketë një efekt negativ kur ekzekutoni një pyetje duke përdorur shprehjen LIKE ne gjendje KU. Për shembull duke përdorur shprehjen LIKE në një fushë të indeksuar nuk do të japë ndonjë rritje në shpejtësinë e ekzekutimit.

Ne kemi diskutuar vetëm disa nga disavantazhet kryesore gjatë përdorimit të indekseve. Sasia aktuale mund të ndryshojë.

Në artikujt e mëparshëm kam përmendur shpesh indekset në MySQL. dhe ju premtova se do t'ju tregoja për to së shpejti. Pra, kjo kohë ka ardhur, dhe sot do të mësoni rreth Indekset MySQL, për qëllimin e tyre dhe mënyrën e krijimit të tyre.

Indekset përdoren për të shpejtuar marrjen e të dhënave nga tabelat e bazës së të dhënave. Në fakt, indeks në MySQL- kjo është renditja e një fushe specifike në një tabelë. Kjo do të thotë, nëse një fushë është bërë një indeks, atëherë e gjithë tabela do të renditet sipas kësaj fushe. Pse është kjo e dobishme?

Le të themi se tabela jonë përmban 1000000 rekorde. Çdo hyrje ka një identifikues unik ID. Dhe le të themi se duhet të nxjerrim një rekord nga ID = 530124. Nëse nuk ka indeks, atëherë MySQL do të përsërisë të gjitha shënimet në tabelë derisa të gjejë atë që i nevojitet. Në rastin më të keq, ai do të detyrohet të zgjidhet 1000000 rekorde. Sigurisht, kjo do të jetë një kohë shumë e gjatë. Dhe nëse do të kishte një indeks (d.m.th., fusha do të renditej), atëherë rekordi do të zgjidhej mesatarisht në 100,000 herë më shpejt. Siç mund ta shihni, përfitimet janë të dukshme.

Megjithatë, indekset kanë një të metë të rëndësishme, e cila nuk lejon që çdo fushë tabele të indeksohet. Në fakt, një indeks është një tabelë tjetër, por thjesht me fushën përkatëse të renditur. Kjo do të thotë, duke bërë një indeks në një fushë, ju krijoni një tabelë tjetër saktësisht të njëjtë, e cila do të marrë hapësirë shtesë në disk.

Një tjetër minus i vogël indekset në MySQL Problemi është se kërkesat për të futur rekorde të reja detyrojnë tabelën të renditet përsëri. Si rezultat, futja e rekordeve të reja do të zgjasë pak më shumë se zakonisht. Por mos harroni se në shumicën e rasteve ju duhet ta bëni këtë shumë më rrallë sesa marrja e mostrave, kështu që ky minus nuk është i rëndësishëm.

Si të krijoni një indeks në MySQL?

Për çelësat kryesorë ( ÇELËSI PRIMAR) indeksi krijohet automatikisht, por për fushat e tjera sekuenca e veprimeve është PHPMyAdmin tjetër:

Dhe së fundi, do të doja të bëja një përmbledhje të shkurtër në mënyrë që të kuptoni: " Kur të krijohen indekset MySQL":

Nëse një fushë merret shumë shpesh, atëherë ajo duhet të bëhet një indeks.
Nëse të dhënat shtohen në tabelë shumë shpesh, dhe kampionimi ndodh rrallë (kjo ndodh ndonjëherë), atëherë nuk ka nevojë të krijohen indekse.

Dhe një gjë tjetër. Nëse papritmas shihni se kërkesat tuaja për marrjen e mostrave janë shumë të ngadalta, atëherë analizoni arsyen për këtë. Me shumë mundësi, ju vetëm duhet të shtoni një indeks. Në përgjithësi, provojeni dhe gjithçka do të bëhet e qartë.

Indekset përdoren për të gjetur shpejt rreshta me një vlerë të caktuar në një kolonë. Pa një indeks, një tabelë lexohet në të gjithë tabelën duke filluar me regjistrimin e parë derisa të gjenden rreshtat që përputhen. Sa më e madhe të jetë tavolina, aq më e madhe është kostoja. Nëse tabela përmban një indeks në kolonat në fjalë, atëherë MySQL mund të përcaktojë shpejt pozicionin e kërkimit në mes të skedarit të të dhënave pa kaluar nëpër të gjitha të dhënat. Për një tabelë që përmban 1000 rreshta, kjo do të jetë të paktën 100 herë më e shpejtë në krahasim me përsëritjen në të gjitha rekordet në mënyrë sekuenciale. Megjithatë, në rastin kur pothuajse të gjitha 1000 rreshtat duhet të aksesohen, leximet vijuese do të jenë më të shpejta sepse nuk kërkohen kërkime në disk.

Të gjithë indekset MySQL (PRIMARY, UNIQUE dhe INDEX) ruhen si pemë B. Vargjet kompresohen automatikisht, duke hequr parashtesat dhe hapësirat pasuese (shih seksionin 6.5.7 KRIJO SINtaksë të deklaratës së INDEKSIT).

Indekset përdoren për:

Gjeni shpejt rreshtat që përputhen me një klauzolë WHERE.
Merrni rreshtat nga tabelat e tjera kur kryeni bashkime.
Gjeni vlerat MAX() ose MIN() për një kolonë të caktuar të indeksuar. Ky operacion është optimizuar nga një paraprocesor që kontrollon nëse po përdorni WHERE key_part_4 = konstante mbi të gjitha pjesët e një çelësi të përbërë SELECT MIN(key_part2),MAX(key_part2) FROM emri_tabelës ku key_part1=10
Kryeni renditjen ose grupimin në një tabelë nëse këto operacione kryhen në prefiksin më të majtë të çelësit të përdorur (për shembull, ORDER BY key_pjesa_1, key_pjesa_2). Nëse të gjitha pjesët e një çelësi ndiqen nga DESC , atëherë çelësi lexohet në rend të kundërt (shih seksionin 5.2.7 Si MySQL Optimizon ORDER BY).
Në disa raste, pyetja mund të optimizohet për të tërhequr vlerat pa hyrë në skedarin e të dhënave. Nëse të gjitha kolonat e përdorura në një tabelë të caktuar janë numerike dhe formojnë prefiksin më të majtë për një çelës të caktuar, atëherë për të siguruar shpejtësi më të madhe, vlerat e kërkuara mund të merren direkt nga pema e indeksit: SELECT key_part3 NGA emri_tabelës WHERE key_part1=1

Supozoni se deklarata e mëposhtme SELECT quhet:

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

Nëse një indeks me shumë kolona ekziston në kolonat col1 dhe col2, atëherë rreshtat përkatës mund të zgjidhen drejtpërdrejt. Në rastin kur ekzistojnë indekse të veçanta në kolonat col1 dhe col2, optimizuesi përpiqet të gjejë indeksin më kufizues duke përcaktuar se cili indeks gjen më pak rreshta dhe e përdor atë indeks për të marrë ato rreshta.

Nëse një tabelë e caktuar ka një indeks me shumë kolona, atëherë çdo parashtesë më e majtë e atij indeksi mund të përdoret nga optimizuesi për të gjetur rreshta. Për shembull, nëse keni një indeks në tre kolona (col1,col2,col3), atëherë ekziston mundësia për kërkime të indeksuar në (col1), (col1,col2) dhe (col1,col2,col3).

Në MySQL, nuk mund të përdorni një indeks të pjesshëm nëse kolonat nuk formojnë prefiksin më të majtë të atij indeksi. Le të supozojmë se keni komandat SELECT të paraqitura më poshtë:

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

Nëse një indeks ekziston në (col1,col2,col3), atëherë vetëm pyetja e parë e paraqitur më sipër e përdor atë indeks. Pyetjet e dyta dhe të treta përfshijnë kolona të indeksuara, por (col2) dhe (col2,col3) nuk janë pjesa më e majtë e prefikseve (col1,col2,col3).

MySQL përdor gjithashtu indekse për LIKE krahasimet nëse argumenti në shprehjen LIKE është një varg konstant që nuk fillon me një karakter wildcard. Për shembull, komandat e mëposhtme SELECT përdorin indekse:

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

Komanda e parë shikon vetëm rreshtat me "Patrick"

Komandat e mëposhtme SELECT nuk do të përdorin indekse:

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

Në komandën e parë, vlera LIKE fillon me një karakter wildcard. Në komandën e dytë, vlera LIKE nuk është konstante.

MySQL 4.0 prezanton një optimizim të ndryshëm në shprehjen LIKE. Nëse përdoret shprehja... LIKE "%string%" dhe gjatësia e vargut është më e madhe se 3 karaktere, atëherë MySQL do të përdorë algoritmin Turbo Boyer-Moore për të inicializuar një model për vargun dhe më pas do ta përdorë atë model për të kryer kërkimi më i shpejtë.

Kur kërkoni duke përdorur emrin e kolonës IS NULL, indekset do të përdoren nëse emri i kolonës është një indeks.

MySQL zakonisht përdor indeksin që gjen më pak rreshta. Indeksi përdoret në kolonat që krahasohen duke përdorur operatorët e mëposhtëm: =, >, >=,

Nëse një indeks nuk mbulon të gjitha nivelet DHE në klauzolën WHERE, atëherë ai nuk përdoret për të optimizuar pyetjen. Me fjalë të tjera: që një indeks të jetë i përdorshëm, prefiksi i atij indeksi duhet të shfaqet në secilin grup AND.

Klauzolat e mëposhtme WHERE përdorin indekse:

WHERE index_part1=1 AND index_part2=2 AND other_column=3 ... WHERE index=1 OSE A=10 AND index=2 /* index = 1 OR index = 2 */ ... WHERE index_part1="përshëndetje" DHE index_part_3= 5 /* optimizuar si "index_part1="përshëndetje"" */ ... WHERE index1=1 dhe index2=2 ose index1=3 dhe index3=3; /* Ju mund të përdorni një indeks në indeksin 1, por jo në indeksin 2 ose indeksin 3 */

Klauzolat e mëposhtme WHERE Jo përdorni indekset:

WHERE index_part2=1 AND index_part3=2 /* index_part_1 nuk përdoret */ ... WHERE index=1 OSE A=10 /* Indeksi nuk përdoret në të dyja pjesët e DHE */ ... WHERE index_part1=1 OSE index_part2 =10 /* Nuk ka indeks që mbulon të gjitha rreshtat*/

Në disa raste, MySQL nuk përdor një indeks edhe pse është e mundur. Disa shembuj të situatave të tilla janë dhënë më poshtë:

Nëse përdorimi i një indeksi kërkon që MySQL të përshkojë më shumë se 30% të rreshtave në një tabelë të caktuar (në raste të tilla, kalimi i tabelës ka të ngjarë të jetë shumë më i shpejtë pasi do të kërkohen më pak kërkime). Një gjë që duhet mbajtur parasysh është se nëse një pyetje si kjo përdor LIMIT vetëm në nëngrupin e rreshtave që merren, atëherë MySQL do të përdorë indeksin gjithsesi, pasi një numër i vogël rreshtash mund të gjenden shumë më shpejt për të kthyer rezultatin.
Nëse diapazoni i ndryshimit të indeksit mund të përmbajë vlera NULL kur përdorni shprehjet ORDER BY ... DESC.

KRIJO INDEKS_indeks_name ON tbl_name (col_name[(length)],...)

Komanda CREATE INDEX në versionet e MySQL para 3.22 nuk bën asgjë. Në versionin 3.22 e më vonë, CREATE INDEX është ekuivalente me komandën ALTER TABLE për sa i përket krijimit të indekseve. Shih seksionin 6.5.4 Sintaksa e Deklaratës së Deklaratës TABELA ALTER.

Në mënyrë tipike, të gjitha indekset krijohen në një tabelë kur vetë tabela krijohet me komandën CREATE TABLE. Shih seksionin 6.5.3 KRIJO TABELA e sintaksës së deklaratës. CREATE INDEX ju lejon të shtoni indekse në tabelat ekzistuese.

Një listë e kolonave të formës (col1,col2,...) krijon një indeks në kolona të shumta. Vlerat e indeksit formohen duke bashkuar vlerat e kolonave të specifikuara.

Për kolonat CHAR dhe VARCHAR, parametri col_name(length) mund të përdoret për të krijuar indekse që përdorin vetëm një pjesë të kolonës (për kolonat BLOB dhe TEXT, duhet të specifikoni gjatësinë). Komanda e mëposhtme krijon një indeks duke përdorur 10 karakteret e para të kolonës së emrit:

Mysql> CREATE INDEX part_of_name ON klienti (emri(10));

Meqenëse shumica e emrave zakonisht ndryshojnë nga njëri-tjetri në 10 karakteret e para, ky indeks nuk duhet të jetë shumë më i ngadalshëm se ai i krijuar nga e gjithë kolona e emrit. Përveç kësaj, duke përdorur kolona të pjesshme për indekset, mund ta bëni skedarin e indeksit shumë më të vogël, gjë që kursen hapësirën në disk dhe gjithashtu rrit shpejtësinë e operacioneve INSERT!

Ju lutemi vini re se në MySQL 3.23.2 dhe më vonë, për tabelat MyISAM, mund të shtoni një indeks vetëm në kolonat që mund të pranojnë vlera NULL ose në kolonat BLOB/TEXT.

Për më shumë informacion se si MySQL përdor indekset, shihni seksionin 5.4.3 Përdorimi i Indekseve në MySQL.

Opsioni FULLTEXT mund të përdoret për të indeksuar vetëm kolonat VARCHAR dhe TEXT dhe vetëm në tabelat MyISAM. Ky funksion disponohet vetëm në versionin 3.23.23 të MySQL dhe më të lartë. Shih seksionin

Indeksimi i tabelave përdoret për të reduktuar kohën e kërkimit të të dhënave. Pa indekse, kërkimi ndodh thjesht duke numëruar të gjitha vlerat e tabelës, gjë që kërkon mjaft kohë.

Llojet e mëposhtme të indekseve përdoren në tabelat mysql:

Çelësi primar
Indeks unik
Indeksi i rregullt
Indeksi i tekstit të plotë

Përveç indekseve të mësipërme, përdoret gjithashtu indeksi i tekstit të plotë, por nuk diskutohet në këtë artikull.

Çelësi primar

Çelësi primar përdoret për të identifikuar në mënyrë unike të dhënat në një tabelë. Ndër pikat kryesore në lidhje me çelësin primar, duhet të theksohen sa vijon:

1. një tabelë nuk mund të ketë vetëm një çelës unik;

2. vlera kryesore duhet të jetë unike brenda tabelës;

3. Çelësi primar nuk mund të jetë i tipit NULL.

Shembull i krijimit të një çelësi primar:

Krijimi i një çelësi primar kur krijoni një tabelë në përshkrimin e fushës

Shembulli 3:

Nëse çelësi kryesor është një fushë teksti, atëherë duhet të tregoni në kllapa numrin e karaktereve të përfshira në indeksimin.

Shembulli 5:

Nëse tabela është krijuar tashmë, atëherë mund të shtoni një indeks primar si më poshtë.

$query = "ALTER tabela automatike SHTO ÇELËSI PRIMAR TË KUFIZIMIT (numri_id)";
$rezultat = $lidhje->pyetje($query);
?>

Indekse të rregullta dhe unike

Përveç indeksit primar, një tabelë mund të përmbajë edhe indekse të rregullta dhe unike. Ndryshe nga indeksi primar, mund të ketë disa nga këto indekse. Dallimi midis një indeksi të rregullt dhe një indeksi unik është se një indeks unik nuk mund të ketë vlera identike.

Deklarimi i indekseve të rregullta bëhet duke përdorur fjalë kyçe kyç ose indeks. Nëse keni nevojë të deklaroni një indeks unik, atëherë më parë kyç ose indeksështë vënë fjala kyçe unike.

Shembulli 6:

Krijoni një indeks unik

Heqja e indekseve

Heqja e indekseve kryhet duke përdorur komandën e indeksit të rënies, duke përdorur atë, çdo indeks fshihet.

Shembulli 8:

$connection = mysqli i ri ("localhost", "root", "", "mybase");
$query = "heq modelin e indeksit në automatik";
$rezultat = $lidhje->pyetje($query);
?>

Materialet tematike: