Suprasti MySQL: Indeksų Galia

Kai kalbame apie užklausų optimizavimą, indeksavimas yra viena iš pirmų temų, kuri yra paliečiama. Aptarkime indeksų rūšis, jų svarbą, jų pranašumus ir trūkumus.

Nors viename įraše visko apie įrašus beveik neįmanoma aprėpti (apie indeksus galima parašyti knygą), pamėginsime įsigilinti ir išsiaiškinti kelių rūšių indeksų tipus, taip pat aptarsime kur jie gali būti naudojami.

Kas yra Indeksai?

Indeksai (taip pat vadinami raktais) yra duomenų struktūros, kurios pagerina užklausų greitį lentelėje. Yra keli indeksų tipai:

1. INDEX – indekso rūšis kuriai nebūtinos unikalios reikšmės. Priima NULL reiškmes;
2. UNIQUE INDEX – visi įrašai, esantys lentelės stulpelyje su šiuo indeksu, privalo būti unikalūs. Toks indekso tipas dažnai naudojamas besidubliuojančių įrašų lentelėje panaikinimui;
3. FULLTEXT INDEX – indeksas, naudojamas stulpeliuose, kuriuose naudojamos pilno teksto paieškos galimybės. Užklausose naudojančiose šį indekso tipą naudojami teiginiai MATCH() ir AGAINST(). Šis indeksas randa raktinius žodžius tekste, o ne lygina vertes tiesiogiai su indekso vertėmis. Šis indekso tipas turi savo niuansų – lauke, naudojančiame šį indekso tipą, negalima naudoti tam tikrų žodžių, laukas naudojantis šį indekso tipą, gali turėti kelis paieškos režimus ir kt.;
4. DESCENDING INDEX – šis indekso tipas įvestas aštuntoje MySQL versijoje. DESCENDING indekso tipas yra toks indekso tipas, kuriame visos reikšmės surūšiuotos atvirkštine tvarka;
5. PRIMARY KEY – unikalus indeksas. Nepriima NULL reikšmių ir nustačius jo reikšmes jų keisti negalima. Dažnai naudojamas kartu su AUTO_INCREMENT automatiškai didinant ID reikšmes.

Indeksų tikslas užklausų našumą išvengiant skaitymo iš visos lentelės.

Indeksai

Binarinio medžio indeksai yra naudojami pagal nutylėjimą daugumoje MySQL duomenų saugojimo variklių. Tokie indeksai veikia saugodami duomenis juos surikiavę. Tokie indeksai dažniausiai naudojami naudojant padidinti užklausų greitį kai užklausoje yra WHERE sąlyga.

MySQL naudoja indeksus šiems veiksmams:

• Užklausų su WHERE išlygomis pagreitinimui;
• Norint atlikti užklausą per mažiau įrašų;
• Norint gauti įrašus iš kitų staliukų JOIN užklausose;
• Norint ieškoti įrašų naudojant indeksus keliuose stulpeliuose – tokiu atveju naudojamas kairėje pusėje esantis indeksas.

Indeksai taip pat gali naudoti pirmus n įrašo simbolių – tokiu būdu naudojant indeksus indeksų dydžiai tampa mažesniais. InnoDB lentelėse naudojančiose REDUNDANT ar COMPACT įrašų formatus pirmi įrašo simboliai gali būti iki 767 baitų dydžio (šių įrašų formatų šiame įraše detaliau neaptarsime).

Unikalūs Indeksai

Unikalus (“UNIQUE“) indekso tipas yra leidžia užtikrinti įrašų, esančių lentelės stulpelyje, unikalumą – toks indekso tipas skirtas tam, kad įrašai viename stulpelyje nesikartotų. Pasikartojančių įrašų įterpimas parodys klaidą "Error 1062 - duplicate key" – tokios klaidos galima išvengti panaudojant IGNORE raktažodį. Stulpelyje esančius dublikatus galima pašalinti pridedant unikalų indeksą su IGNORE raktažodžiu.

Nors stulpelyje su UNIQUE indeksu reikšmės nesikartoja, tačiau stulpelis gali turėti kelias pasikartojančias NULL reikšmes. Nors šitoks MySQL veikimas buvo praneštas kaip galima klaida 2007 metais, tai nėra klaida.

Full-text Indeksai

Paieškos kurios naudoja “full-text” indeksą visiškai skiriasi nuo paieškų naudojančių įprastą indeksą. Taip yra todėl, kad kaip ir minėta ankščiau, šio tipo indeksai turi daug unikalių savybių – pirmiausia apžvelgsime filtruojamus žodžius (“stopwords”).

Yra dvi filtruojamų žodžių rūšys – MyISAM žodžiai ir InnoDB žodžiai. Kiekvienas iš jų naudojamas tada, kai naudojamas atitinkamas paieškos variklis.

MyISAM Filtruojami Žodžiai

MyISAM filtruojamų žodžių sąrašas gaunamas iš failo storage/myisam/ft_static.c – MyISAM turi 143 filtruojamus žodžius. Kintamasis “ft_stopword_file” įgalina numatytojo sąrašo pakeitimą žodžiais iš kito failo arba filtruojamų žodžių ignoravimą. Faile negali būti komentarų – MyISAM traktuoja visus žodžius kaip filtruotinus.

Šių žodžių filtravimas taip pat gali būti išjungtas “ft_stopword_file” kintamajame neįrašius nieko. Tačiau jei kintamasis arba failo turinys pasikeičia, “full text” indeksas turi būti perdarytas iš naujo naudojant REPAIR TABLE table_name QUICK kur table_name yra lentelės, kurioje iš naujo nustatomi indeksai, pavadinimas.

InnoDB Filtruojami Žodžiai

Pagal nutylėjimą InnoDB filtruotinų žodžių sąrašas yra žymiai trumpesnis nei MyISAM sąrašas – jame yra tik 36 žodžiai. Žodžiai “full text” indeksuose yra įgalinami tik tuo atveju, jei indekso sukūrimo metu nustatytas kintamasis “innodb_ft_enable_stopword“.

InnoDB filtruojami žodžiai yra gaunami iš lentelės, kuri nustatoma innodb_ft_user_stopword_table, kitu atveju jie gaunami iš kintamojo innodb_ft_server_stopword_table. Jei nei vienas iš šių kintamųjų nėra nustatytas, InnoDB naudoja savo variklyje esantį sąrašą.

Full-text Paieškos Režimai

Paieškos režimai yra dar viena unikali FULLTEXT indeksų savybė. Yra trys paieškos tipai:

1. Natūralios kalbos paieškos režimas – toks paieškos būdas iterpretuoja paiešką interpretuoja kaip šnekamosios kalbos frazę. Ieškoma vertė pateikiama dvigubose kabutėse. Toks paieškos režimas bus įjungtas jei užklausa neturės jokių papildomų reikšmių – režimą taip pat galima įjungti naudojant modifikatorių IN NATURAL LANGUAGE MODE.
2. Paieškos režimas išplečiant užklausą – toks paieškos režimas veikia atliekant paiešką du kartus. Antrosios paieškos frazė yra tokia pati kaip ir pirma, tačiau prie rezultatų dar pridedami pirmosios paieškos rezultatai. Toks paieškos režimas suveiks jei FULLTEXT paieškos užklausoje bus naudojamas modifikatorius WITH QUERY EXPANSION. Naudoti šį paieškos režimą yra naudinga, kai vartotojas turi numanomų žinių (pavyzdžiui tada, kai vartotojas ieškodamas reikšmės “MySQL” paieškos rezultatuose tikisi išvysti ir MySQL variklius “InnoDB” bei “MyISAM”).
3. Loginių operatorių (“boolean”) paieškos režimas – toks paieškos režimas leidžia ieškoti naudojant labai sudėtingas užklausas, į kurias gali būti įtraukti ir loginiai operatoriai. Toks paieškos režimas bus įjungtas, jei BOOLEAN paieškos užklausoje bus naudojamas IN BOOLEAN MODE modifikatorius. Naudoti šį paieškos rėžimą naudinga tada, kai reikia rasti įrašus su sudėtingomis ar neaiškiomis reikšmėmis, pavyzdžiui, taip galima rasti įrašus, kurie tekste turi “Duomenų bazė” raktažodį, bet neturi “MySQL” raktažodžio.

FULLTEXT paieška su loginiais operatoriais palaiko šias reikšmes:

• + nurodo, kad reikšmė privalo būti įraše;
• – nurodo, kad reikšmės neturi būti įraše;
• < ir > atitinkamai pakeičia reikšmės prioritetu. “<” vertina reikšmę mažesniu prioritetu, “>” vertina reikšmę didesniu prioritetu;
• ( ir ) leidžia sukurti užklausas užklausose;
• ~ sumažina vertės indėlį rezultatuose. Pvz. užklausa “+žalia ~mėlyna” rastų įrašus, kuriuose būtų žodžiai “žalia” ir “mėlyna”, tačiau jei įraše būtų žodis “mėlyna”, to įrašo indėlis būtų mažesnis.
• * yra “wildcard” atitikmuo. Užklausa “kaž*” rastų “kažkas”;
• “” atitinka tik tuos įrašus, kurie atitinka pažodžiui įvestą reikšmę kabutėse.

FULLTEXT paieška turi minimalų žodžių ilgį. Ilgis yra trys simboliai InnoDB varikliams ir keturi simboliai MyISAM varikliams.

FULLTEXT indeksai yra naudingi tada, kai atliekate tokias paieškas, kada LIKE užklausa nenaudoja indeksų (tai aptarsime indeksų naudojimo pavyzdžiuose).

FULLTEXT indeksai yra skirti atlikti MATCH() AGAINST() užklausas – jie neskirti WHERE užklausoms. Tai reiškia, kad jei jūsų paieškoje bus naudojama WHERE sąlyga, Binarinio medžio indeksas vis tiek bus naudingas. Taip pat verta paminėti, kad FULLTEXT indeksas gali būti sukurtas tik ant CHAR, VARCHAR ar TEXT stulpeliams.

Mažėjantys Indeksai

Mažėjantys indeksų tipai buvo įvesti į aštuntą MySQL versiją – užklausa naudoja tokį indeksą kai užklausą reikalauja mažėjančių verčių. Norėdami pridėti mažėjantį indeksą prie stulpelio, indekso užklausoje paminėkite raktažodį DESC. Mažėjančius indekso tipus palaiko tik InnoDB variklis.

Pagrindiniai Raktai

Pagrindinis lentelės raktas (“PRIMARY KEY“) žymi stulpelius, kurie yra dažniausiai naudojami užklausose. Viena lentelė gali turėti tik vieną pagrindinį raktą – su juo sukuriamas ir unikalus indeksas. Pagrindinis raktas turi su juo susijusį indeksą – šis indeksas pagal nutylėjimą yra sugrupuotas.

Sugrupuoti Indeksai

Kiekvienas staliukas su InnoDB varikliu turi sugrupuotą indeksą – paprastai šis indeksas yra nurodytas kaip PRIMARY KEY, tačiau tai priklauso nuo kelių dalykų:

• Ar jūsų lentelė turi PRIMARY KEY? Jei taip, InnoDB naudos jį kaip sugrupuotą indeksą;
• Ar jūsų lentelė turi UNIQUE INDEX? Jei indekso reikšmės yra NOT NULL, InnoDB naudos tą stulpelį sugrupuotam indeksui.

Sugrupuoti indeksai saugo binarinio medžio indeksą ir įrašus kartu toje pačioje struktūroje todėl užklausos naudojančios sugrupuotą indeksą yra greitesnės, tačiau viena lentelė gali turėti tik vieną sugrupuotą indeksą.

Antriniai Indeksai

Antriniai indeksai yra visi indeksai, kurie nėra pirminiai. Juose yra pirminio indekso stulpeliai kartu su antrinio indekso stulpeliais – antrinis indeksas yra būdas pasiekti įrašus nenaudojant pagrindinio indekso.

Maišos Indeksai

Be binarinio medžio indeksų, MySQL turi ir maišos (“hash”) indeksus. Pagrindinis šių dviejų indeksų skirtumas yra tai, kad maišos indeksai leidžia naudoti lygybės palyginimus, kuriuose naudojamos = ir <=> reikšmės, o binarinio medžio tipo indeksai leidžia naudoti palyginimus tarp stulpelių užklausose, naudojančiose arba =, >, >=, <, <= arba BETWEEN reikšmes.

Dengiantys Indeksai

Dengiantis indeksas (angl. “covering index”) yra indeksas, kuris apima visus duomenis, reikalingus sėkmingam užklausos vykdymui. Kai naudojamas dengiamasis indeksas, visi užklausoje naudojami stulpeliai įtraukiami į dengiamąjį indeksą. Tai reiškia, kad užklausa, kurioje naudojamas dengiamasis indeksas, neskaitys eilučių iš lentelės – užklausa skaitys duomenis iš indekso.

Kelių Stulpelių Indeksai

MySQL taip pat gali kurti sudėtinius indeksus – sudėtiniai indeksai yra indeksai, sudaryti iš kelių stulpelių. Kelių stulpelių indeksavimo tema yra dažnai suprantama neteisingai – viena iš daugiausiai painiavos sukeliančių temų yra stulpelių indekse tvarka. Kai naudojate indeksus keliems stulpeliams, labai svarbu pasirinkti jų eiliškumą: prieš kuriant indeksus stulpeliams gali būti naudinga duomenų bazėje paleisti keletą užklausų tam, kad išsiaiškintumėte, kurį stulpelį indeksuoti labiausiai apsimoka.

Verta atsiminti, kad jei yra pasirinkimas tarp kelių indeksų, MySQL paprastai naudoja indeksą, kuriame randama mažiausiai įrašų, todėl prieš pradedant indeksuoti stulpelius gali reikėti patikrinti duomenų kardinalumą.

Indeksų Naudojimo Pavyzdžiai

Dabar apžvelgsime keletą pavyzdžių kai tam tikro tipo indeksai naudojami neteisingai ir pakoreguosime naudojimus pavyzdžius. Pažvelkime į pirmąjį indekso naudojimo pavyzdį:

SELECT * FROM table WHERE column LIKE '%text%';

Šitokia užklausa nenaudos indekso, nes neaišku nuo ko prasideda įrašas – norėdami išspręsti šią problemą, pakeiskite užklausą:

SELECT * FROM table WHERE column LIKE 'text%';

Šitokiu atveju užklausa naudotų indeksą todėl, nes būtų aišku, ko yra ieškoma.

Kita dažnai pasitaikanti klaida yra tai, kad indeksuotas stulpelis nėra izoliuotas – MySQL negali naudoti indeksų jei užklausa atlieka tam tikrus veiksmus (daugybą padalijimus ir pnš.) po to, kai stulpelis jau yra paminimas užklausoje:

SELECT * FROM table WHERE column * 10 = 100

Tokia užklausa nebus labai efektyvi – ji negali naudoti indekso, nes stulpelis nėra izoliuotas. Norėdami išspręsti šią problemą išskirkite stulpelius – įsitikinkite, kad indeksuotas stulpelis yra vienintelis dalykas prieš lygybę. Stulpelis neturėtų būti funkcijos ar bet kokios išraiškos viduje.

Venkite Pasikartojančių Indeksų

MySQL leidžia programuotojams sukurti kelis to pačio tipo indeksus. Pavyzdžiui ši užklausa:

CREATE TABLE table (
`id` INT(5) NOT NULL,
`field` VARCHAR(10) NOT NULL,
`field_2` VARCHAR(10) NOT NULL,
UNIQUE(id),
INDEX(id)
) ENGINE = InnoDB;

Aukščiau pateikta užklausa sukurs du indeksus ant to pačio stulpelio. Pridėkite “AUTO_INCREMENT PRIMARY KEY” prie “id” stulpelio ir turėsite tris indeksus. Vienintelė gera priežastis naudoti tokius indeksus būtų tada, kai norima pagreitinti kelių tipų užklausas. Atminkite, kad jums gali prireikti maišos (“hash”) arba FULLTEXT indekso tipo kartu su binarinio medžio indeksais, nes tokie indeksų tipai efektyvūs skirtinguose scenarijuose.

Nereikalingi Indeksai Keliuose Stulpeliuose

Dubliuoti indeksai keliuose stulpeliuose šiek tiek skiriasi nuo pasikartojančių indeksų, tačiau trumpai tariant:

• Jei turite indeksą ant (A,B) jums nereikia dar vieno indekso ant A nes A yra pati kairiausia indekso dalis. Jums gali prireikti indekso ant B todėl, nes B stulpelis nėra pats kairiausias ir todėl stulpelis gali būti reikalingu;
• Skirtingų tipų indeksai “nesikiša” į binarinio medžio indeksus. Tai yra pirminė priežastis kodėl galite turėti binarinio medžio indeksą su, pavyzdžiui, FULLTEXT indeksu – tokie indeksai “nesipyktų”, nes jie yra pritaikyti skirtingiems tikslams.

Nenaudojami Indeksai

Naudodamiesi lentele “INFORMATION_SCHEMA.INDEX_STATISTICS” ir įjungdami MySQL kintamajį “userstats” galite pamatyti, kiek naudojamas kiekvienas indeksas ir tada panaikinti nereikalingus indeksus – tokiu būdu galite laimėti šiek tiek vietos diske, ypač jei lentelė turi daug įrašų. Turint indeksą bet jo nenaudojant tiesiog švaistoma diske esanti vieta.

Santrauka

MySQL indeksai gali būti labai galingu įrankiu, bet vis dėl to, kartais jie gali padaryti daugiau žalos nei naudos. Jei reikės naudoti indeksus, žinokite skirtingų indeksų tipų skirtumus, pliusus ir minusus ir naudokite tik reikalingus indeksus.