MySQL informacinis vadovas. MySql. Kas yra indeksai? Kaip sukurti arba ištrinti indeksą? Mysql sukurti rodyklę lentelėje

Projekte labai svarbus veiksnys yra jo darbo greitis.
Vienas iš būdų pagreitinti projektą yra: pagreitinti užklausos užbaigimo laiką MySql duomenų bazė, sumažinant atsako laiką vykdant duomenų bazės užklausą.
Šiandien mes susipažinsime su tokiu įrankiu kaip " Indeksai".
Indeksų naudojimas sumažins duomenų bazės atsako laiką MySql, o tai suteikia mums didžiulių pranašumų imant duomenis iš didelės DB ir bus labai naudingas naudojant dideliuose, didelės apkrovos projektuose.
Pažiūrėkime, kas yra indeksai, kokių tipų indeksai yra ir kokią naudą iš jų galima gauti.

MySql indeksas yra svarbus ir naudingas įrankis, leidžiantis optimizuoti pasirinkimą iš duomenų bazės. Indeksų naudojimas žymiai sumažina užklausos vykdymo laiką, todėl duomenis galite gauti greičiau.
Pastebimas skirtumas naudojant MySql indeksai galima pamatyti dirbant su dideliais stalais.
Indeksas yra struktūra, kurioje saugomos vieno ar kelių stulpelių reikšmės A[s] lentelės ir nuorodos į eilutes, kuriose yra šios reikšmės. Norėdami saugoti indeksus, dvejetainiai medžiai".

Pažvelkime į pavyzdį, kaip veikia indeksai:
Indeksų pranašumus galite patirti paleisdami PASIRINKTI prašymas su sąlyga KUR.
Turime lentelę, kurioje saugoma informacija apie vartotojus.
Lentelėje yra stulpeliai:

• id - automatinis padidėjimas
• vardas - Vardas
• pavardė – pavardė
• amžius – amžius

Sutikite, kad užklausos rezultato laukimas 10 sekundžių yra gana ilgas laikas.
Dabar apibrėžkime " indeksas“ stulpelis amžiaus ir pakartokite prašymą.
Užklausos vykdymo laikas buvo 0,031 sekundės, tai yra žymiai greičiau nei anksčiau.
Taigi, naudojant matome žymiai padidėjusį greitį indeksai.

MySql indeksų tipai:

• PIRMINIS RAKTAS- Pirminis raktas: pirminis raktas, leidžiantis išsaugoti jo reikšmes kaip unikalius lentelės įrašus. Šio tipo raktas turi būti lentelėje vienoje kopijoje. Paprastai šio tipo raktas nustatomas pagal stulpelį su pavadinimu id.
• UNIKALUS- Unikalus raktas: iš dalies panašus į pirminį raktą, nes lentelės stulpelių reikšmės turi būti unikalios (nepasikartoti) ir nelygios NULL.
• Sudėtinis indeksas: Šio tipo indeksas apima kelis indeksavimo laukus. Šis indeksavimo tipas dažniausiai naudojamas užklausose, kuriose reikia pasirinkti kelis laukus (kur sąlygoje KUR atsiranda daugiau nei vienas parametras).

1. Stulpelių indeksavimo metu MySql lentelės sukuriamos papildomos lentelės, kuriose saugomi surūšiuoti duomenys. Ir tokie duomenys užima tam tikrą fizinės atminties kiekį.
2. Vykdant daugybę užklausų, indeksų buvimas gali atlikti visiškai priešingą vaidmenį ir padidinti užklausos vykdymo laiką.
3. Jei ant stalo yra rodyklė, ĮDĖTI užklausa užtrunka ilgiau nei lentelėje, kurioje nėra indeksų.
4. Indeksų naudojimas taip pat gali turėti neigiamą poveikį vykdant užklausą naudojant išraišką KAIP būklės KUR. Pavyzdžiui, naudojant išraišką KAIP indeksuotame lauke vykdymo greitis nepadidės.

Indeksai MySQL yra svarbus ir naudingas įrankis, leidžiantis optimizuoti pasirinkimą iš duomenų bazės, žymiai sutrumpinant reikalingų duomenų gavimo laiką.

Tuo pačiu metu galite pastebėti skirtumą dideli stalai kuriame yra dešimtys ir šimtai tūkstančių eilučių. Jei tokiose lentelėse nėra lauko indekso, tada pasirinktos užklausos pereis visas eilutes iš eilės, kol bus rasta norima reikšmė.

Dažnai programuotojai kurdami duomenų bazę nepagalvoja apie MySQL indeksus, tačiau to pasekmės pradeda jaustis vėliau, kai projektas tampa populiarus ir lankomas. Svetainė pradeda lėtėti, o jos savininkai mano, kad dėl to kaltas blogas priegloba arba silpnas serveris. Dėl to didėja techninės įrangos sąnaudos, nors tereikia optimizuoti duomenų bazę.

Duomenų indeksavimas naudojamas ne tik MySQL. Kasdieniame gyvenime taip pat randame naudos taupydami laiką. Taigi, pavyzdžiui, kiekvienas, kada nors buvęs didelėse miestų bibliotekose, yra matęs ir naudojęs kartoteką, kuriame visų knygų pavadinimai buvo surūšiuoti pagal pirmąsias raides. Įsivaizduokite situaciją, jei tokios klasifikacijos nebūtų. Tikriausiai mums būtų labai sunku rasti knygą tarp šimtų tūkstančių kitų.

Taigi, MySQL indeksai yra specifinis duomenų rūšiavimas lentelėje, siekiant pagreitinti duomenų gavimą. Rūšiavimas vyksta „žemame“ (mašinos) lygyje, ir mums nereikia gilintis į jo procesus. Viskas, ką turime padaryti, tai nurodyti MySQL, kuriuos laukus indeksuoti.

Kaip nustatyti, kuriems laukams kurti indeksus?

Visų pirma, indeksus reikia sukurti tuose laukuose, kurie dažnai patenka į jūsų sql užklausų sąlygą „kur“.

Pavyzdžiui, tarkime, kad jūsų produktų lentelės struktūra yra tokia:

Pavyzdžiui, norėdami sukurti puslapius, dažnai turite pateikti užklausas ir ieškoti tos pačios kategorijos produktų:

SELECT id, product_name FROM products WHERE cat_id = "5"

Šiuo atveju, norint optimizuoti užklausas, patartina sukurti rodyklę lauke cat_id. Pirmasis laukas – id – visada turi unikalią reikšmę ir jam patartina susikurti „pirminį raktą“.

Kaip sukurti indeksus MySQL duomenų bazėje

Norėdami sukurti indeksą MySQL, galite naudoti phpMyAdmin sąsają. Priešais norimą lauką struktūros redagavimo režimu galite matyti įvairias lauko indeksų pridėjimo parinktis.

Pasirinkite, pavyzdžiui, „pridėti indeksą“ ir jis bus įtrauktas į pasirinktą lauką.

Antrasis būdas yra sukurti SQL užklausą, pavyzdžiui:

ALTER TABLE lentelės_pavadinimas ADD INDEX (index_col_name,...)

Pavyzdžiui,

ALTER TABLE vartotojai ADD INDEX i_name (vartotojo vardas);

Indeksų tipai (tipai) MySQL

• Pirminis raktas (PRIMARY KEY) yra pagrindinis raktas, kurį lentelėje gali turėti tik vienas. Tai leidžia identifikuoti unikalius įrašus lentelėje. Vertės, esančios stulpelyje, kuriame laukai turi PIRMINĮ RAKTĄ, negali būti kartojami. Dažnai pirminis raktas priskiriamas laukams su ID identifikatoriumi.
• Unikalus raktas (UNIKALUS) iš esmės yra alternatyva pirminiam raktui: tokiuose laukuose esančios reikšmės taip pat negali būti kartojamos ir turi NULL reikšmę.
• Sudėtinis indeksas – leidžia į indeksą įtraukti kelis laukus, kurie dažnai naudojami atrankai. Pavyzdžiui, jei sąlygose dažnai atsiranda du parametrai:
  PASIRINKTI naudotojo vardą FROM naudotojai, WHERE miestas = "5" IR amžius > "18"
  Naudojant tokias užklausas, sudėtinis miesto ir amžiaus laukų indeksas padės pagreitinti duomenų gavimą.

Indeksų naudojimo trūkumai:

1. Už viską reikia mokėti, o MySQL indeksai nėra išimtis. Indeksavimo proceso metu MySQL sukuria papildomas lenteles, kuriose saugomi surūšiuoti duomenys. Tačiau daugeliu atvejų našumo nauda yra didesnė nei fizinės atminties sąnaudos.
2. Duomenų įterpimo į lentelę užklausos užtrunka šiek tiek ilgiau, nes MySQL reikia sukurti naujus indeksus ir pertvarkyti duomenis numatytose lentelėse. Tačiau kadangi įterpimo užklausos vykdomos rečiau nei duomenų gavimo užklausos, tai paprastai nesukelia didelių nuostolių.

Apskritai, kaip matote, indeksų naudojimo pranašumai gerokai nusveria nedidelį skaičių trūkumų. Dideliuose ir dažnai lankomuose projektuose neįmanoma išsiversti be indeksų.

Ankstesniuose straipsniuose dažnai minėjau indeksai MySQL. ir pažadėjau, kad netrukus apie juos papasakosiu. Taigi, šis laikas atėjo, ir šiandien jūs apie tai sužinosite MySQL indeksai, apie jų paskirtį ir kaip juos sukurti.

Indeksai naudojami pagreitinti duomenų gavimą iš duomenų bazių lentelių. Faktiškai, indeksas MySQL- tai konkretaus lauko rūšiavimas lentelėje. Tai yra, jei laukas yra indeksas, visa lentelė bus rūšiuojama pagal šį lauką. Kodėl tai naudinga?

Tarkime, kad mūsų lentelėje yra 1000000 įrašų. Kiekvienas įrašas turi unikalų identifikatorių ID. Ir, tarkime, reikia ištraukti įrašą iš ID = 530124. Jei nėra indekso, tada MySQL kartos visus lentelės įrašus, kol suras reikiamą. Blogiausiu atveju jis bus priverstas susitvarkyti 1000000 įrašų. Žinoma, tai truks labai ilgai. Ir jei būtų indeksas (ty laukas būtų surūšiuotas), tada įrašas būtų atrenkamas vidutiniškai 100 000 kartų greičiau. Kaip matote, nauda yra akivaizdi.

Tačiau indeksai turi vieną reikšmingą trūkumą, kuris neleidžia kiekvienam lentelės laukui būti indeksu. Tiesą sakant, indeksas yra kita lentelė, bet tiesiog su atitinkamu lauku. Tai yra, sudarę indeksą viename lauke, sukuriate kitą lygiai tokią pačią lentelę, kuri užims papildomos vietos diske.

Dar vienas mažas minusas indeksai MySQL Problema ta, kad užklausos įterpti naujus įrašus priverčia lentelę iš naujo rūšiuoti. Dėl to naujų įrašų įterpimas užtruks šiek tiek ilgiau nei įprastai. Tačiau nepamirškite, kad daugeliu atvejų tai turite daryti daug rečiau nei imti mėginius, todėl šis minusas nėra reikšmingas.

Kaip sukurti indeksą MySQL?

Pirminiams raktams ( PIRMINIS RAKTAS) indeksas sukuriamas automatiškai, bet kitiems laukams veiksmų seka yra PHPMyAdmin Kitas:

Ir galiausiai norėčiau padaryti trumpą santrauką, kad suprastumėte: Kada kurti MySQL indeksus":

• Jei laukas imamas labai dažnai, jis turėtų būti sudarytas kaip indeksas.
• Jei įrašai į lentelę pridedami labai dažnai, o atranka vyksta retai (taip kartais nutinka), tada indeksų kurti nereikia.

Ir dar vienas dalykas. Jei staiga pamatysite, kad jūsų atrankos užklausos yra labai lėtos, išanalizuokite to priežastį. Labiausiai tikėtina, kad jums tiesiog reikia pridėti indeksą. Apskritai išbandykite ir viskas paaiškės.

Sprendimas
ALTER TABLE sąlyga gali pašalinti ir pridėti ne tik stulpelius, bet ir tų stulpelių indeksus. Tokios operacijos dažnai pagerina duomenų bazės našumą. Paprastai dažnai naudojamo stulpelio indeksavimas pagreitina SELECT sakinius, nes nereikia visiškai nuskaityti lentelių. Kai kuriais atvejais indekso ištrynimas taip pat gali būti naudingas. Kai eilutė atnaujinama, MySQL turi atnaujinti visus indeksus, kuriuose yra pakeisti stulpeliai. Jei indeksą naudojate retai, tai gali reikšti, kad lentelė yra perkrauta indeksais, o pašalinus vieną iš jų gali padidėti lentelės apdorojimo efektyvumas.

Diskusija
Kad būtų lengviau naudoti, pradėkime nuo naujo mytbl testo lentelės egzemplioriaus kūrimo. Norėdami ištrinti, naudojame sąlygas DROP TABLE ir CREATE TABLE esama versija ir atkurti lentelę originalia forma:

DROP TABLE mytbl;
KURTI LENTELĘ mytbl
aš INT,
c CHAR(1)
);

Skyriaus pradžioje naudojome SHOW COLUMNS, kad stebėtume lentelės keitimo rezultatus.

Dabar mes išnagrinėsime indekso pakeitimus ir parodysime rezultatus naudodami SHOW INDEX, o ne SHOW COLUMNS. Šiuo metu lentelėje nėra indeksų, nes jie nebuvo nurodyti CREATE TABLE sakinyje:

Mysql> RODYTI RODYKLĄ IŠ mytbl;
Tuščias rinkinys (0,00 sek.)

Indeksų pridėjimas
Yra keturių tipų sąlygos, kurios į lentelę prideda indeksus:

ALTER TABLE lentelės_pavadinimas ADD PRIMARY KEY (column_list);
ALTER TABLE lentelės_pavadinimas ADD UNIQUE indekso_pavadinimas (stulpelio_sąrašas);
ALTER TABLE lentelės_pavadinimas ADD INDEX index_name (stulpelio_sąrašas);
ALTER TABLE lentelės_pavadinimas ADD FULLTEXT index_name (stulpelio_sąrašas);

Pirmoji sąlyga prideda pirminį raktą (PRIMARY KEY), tai yra, indeksuotos reikšmės turi būti unikalios ir joje neturi būti NULL. Antroji sąlyga sukuria indeksą, kurio reikšmės turi būti unikalios (išskyrus NULL reikšmes, kurios gali atsirasti kelis kartus). Trečioji sąlyga prideda įprastą indeksą, kuriame bet kuri reikšmė gali būti rodoma kelis kartus. Pastarasis sukuria specialų FULLTEXT indeksą, kuris naudojamas tekstui peržiūrėti.

Jei sakinio konstrukcijose yra indekso_vardas, jis yra neprivalomas.

Jei jo nenurodysite, MySQL automatiškai priskirs indeksui pavadinimą.

Indeksuotini stulpeliai nurodomi parametre column_list – vieno ar kelių stulpelių pavadinimų, atskirtų kableliais, sąraše.

Pažvelkime į du paprastus pavyzdžius: pirmasis sukuria vieno stulpelio indeksą c, o antrasis sukuria kelių stulpelių indeksą, apimantį c ir i:

ALTER TABLE mytbl ADD INDEX (c);
ALTER TABLE mytbl ADD INDEX (c,i);

Daugeliu atvejų indeksuojami stulpeliai turi būti paskelbti ne NULL. Pavyzdžiui, jei sukursite mytbl kaip ISAM lentelę, aukščiau pateikti ADD INDEX teiginiai nepavyks, nes ISAM lentelėse neleidžiama NULL jokiuose indeksų tipuose. Be to, PRIMARY KEY indeksuose negali būti NULL reikšmių, neatsižvelgiant į lentelės tipą. Jei bandote pridėti indeksą ir MySQL skundžiasi NULL problemomis, naudokite sąlygą ALTER TABLE, kad pakeistumėte atitinkamą (-ius) stulpelį (-ius) į ne NULL ir bandykite dar kartą sukurti indeksą. Pavyzdžiui, jei bandysite i stulpelį padaryti pirminiu raktu, atsiras klaida:

Mysql>
KLAIDA 1171 5 eilutėje: visos PIRMINIO RAKTO dalys turi būti NENULIS;
Jei rakte reikia NULL, naudokite UNIQUE

Pirmiausia turite iš naujo apibrėžti stulpelį i, kad jis neleistų NULL:

Mysql> ALTER TABLE mytbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);

Viskas pavyko.

Ir pirmuoju atveju, kaip matyti iš klaidos pranešimo, vietoj pirminio rakto galite sukurti UNIKALĮ indeksą, jei indekse reikia NULL reikšmių. Indeksų pašalinimas Norėdami ištrinti indeksą, naudokite vieną iš toliau nurodytų būdų sakiniai:

ALTER TABLE lentelės_pavadinimas DROP PRIMARY KEY;
ALTER TABLE lentelės_pavadinimas DROP INDEX indekso_pavadinimas;

Paprasčiausias būdas ištrinti PAGRINDINIO RAKTO indeksą, nes jums nereikia žinoti indekso pavadinimo:

ALTER TABLE mytbl DROP PRIMARY KEY;

Norėdami ištrinti indeksą, kuris nėra pagrindinis raktas, turite nurodyti jo pavadinimą. Jei nežinote, kaip indeksas vadinamas, naudokite SHOW INDEX. Norėdami išvengti per ilgų eilučių, naudokite vertikalią išvestį (\G):

Mysql> RODYTI RODYKLĄ IŠ mytbl\G
****************************** 1. eilutė ******************** * ******
Lentelė: mytbl
Ne_unikalus: 1
Key_name: c
Seq_in_index: 1
Stulpelio_pavadinimas: c
Surinkimas: A
Kardinalumas: NULL
Sub_part: NULL
Supakuota: NULL
komentaras:
****************************** 2. eilutė ************************ * ******
Lentelė: mytblNon_unique: 1
Key_name: c_2
Seq_in_index: 1
Stulpelio_pavadinimas: c
Surinkimas: A
Kardinalumas: NULL
Sub_part: NULL
Supakuota: NULL
komentaras:
****************************** 3. eilutė ************************ * ******
Lentelė: mytbl
Ne_unikalus: 1
Key_name: c_2
Seq_in_index: 2
Stulpelio_pavadinimas: i
Surinkimas: A
Kardinalumas: NULL
Sub_part: NULL
Supakuota: NULL
komentaras:

Key_name ir Seq_in_index reikšmės atitinka indeksų pavadinimus ir stulpelių pozicijas indekse. Dabar žinote, kad mytbl lentelė turi vieno stulpelio indeksą, pavadintą c, ir kelių stulpelių indeksą, pavadintą c_2 (šiuos pavadinimus MySQL pasirinko dviem indeksams, kuriuos sukūrėme anksčiau). Indeksų pašalinimo teiginys būtų toks:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

Kaip matote, viename ALTER TABLE sakinyje galima atlikti kelias operacijas, kurios turi būti atskirtos kableliais.

Lentelės indeksavimas naudojamas siekiant sutrumpinti duomenų paieškos laiką. Be indeksų paieška vyksta tiesiog surašant visas lentelės reikšmes, o tai užtrunka gana ilgai.

Mysql lentelėse naudojami šių tipų indeksai:

• Pirminis raktas
• Unikalus indeksas
• Įprastas indeksas
• Pilno teksto rodyklė

Be minėtų indeksų, jis taip pat naudojamas pilno teksto rodyklė, tačiau šiame straipsnyje apie tai nekalbama.

Pirminis raktas

Pirminis raktas naudojamas unikaliai identifikuoti įrašus lentelėje. Tarp pagrindinių punktų, susijusių su pirminiu raktu, reikėtų pažymėti:

1. lentelė negali turėti tik vieno unikalaus rakto;

2. rakto reikšmė lentelėje turi būti unikali;

3. Pirminis raktas negali būti NULL tipo.

Pirminio rakto kūrimo pavyzdys:

Pirminio rakto sukūrimas kuriant lentelę lauko aprašyme

3 pavyzdys:

Jei pirminis raktas yra teksto laukas, tada skliausteliuose turite nurodyti indeksuojant naudojamų simbolių skaičių.

5 pavyzdys:

Jei lentelė jau sukurta, galite pridėti pirminį indeksą taip.

Įprasti ir unikalūs indeksai

Be pirminio indekso, lentelėje taip pat gali būti įprasti ir unikalūs indeksai. Skirtingai nuo pirminio indekso, šių indeksų gali būti keli. Skirtumas tarp įprasto indekso ir unikalaus indekso yra tas, kad unikalus indeksas negali turėti identiškų verčių.

Įprastų indeksų deklaravimas atliekamas naudojant raktinius žodžius Raktas arba indeksas. Jei reikia deklaruoti unikalų indeksą, tada anksčiau Raktas arba indeksasįdėtas raktinis žodis Unikalus.

6 pavyzdys:

Sukurkite unikalų indeksą

Indeksų pašalinimas

Indeksų pašalinimas atliekamas naudojant komandą drop index, visi indeksai ištrinami.

8 pavyzdys: