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.
Indeksai (taip pat vadinami raktais) yra duomenų struktūros, kurios pagerina užklausų greitį lentelėje. Yra keli indeksų tipai:
NULL
reiškmes;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.;DESCENDING
indekso tipas yra toks indekso tipas, kuriame visos reikšmės surūšiuotos atvirkštine tvarka;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.
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:
WHERE
išlygomis pagreitinimui;JOIN
užklausose;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).
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.
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 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.
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šą.
Paieškos režimai yra dar viena unikali FULLTEXT
indeksų savybė. Yra trys paieškos tipai:
IN NATURAL LANGUAGE MODE
.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”).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:
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 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.
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.
Kiekvienas staliukas su InnoDB varikliu turi sugrupuotą indeksą – paprastai šis indeksas yra nurodytas kaip PRIMARY KEY
, tačiau tai priklauso nuo kelių dalykų:
PRIMARY KEY
? Jei taip, InnoDB naudos jį kaip sugrupuotą indeksą;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 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.
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.
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.
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ą.
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.
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.
Dubliuoti indeksai keliuose stulpeliuose šiek tiek skiriasi nuo pasikartojančių indeksų, tačiau trumpai tariant:
FULLTEXT
indeksu – tokie indeksai “nesipyktų”, nes jie yra pritaikyti skirtingiems tikslams.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.
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.
A hacking group related to North Korea is exploiting a zero-day in the Chromium browser…
What are crypto bubbles, how do they form, and should you worry about them? Learn…
Is the crypto-engine.pro blog legit and should you trust this resource? Learn here!
Reside in Brazil and found that your Twitter account suspended? There’s a good reason for…
This blog covers the recent Black Hat USA 2024 (DEFCON 2024) conference and digs into…
The CEO of Telegram and Telegram Web, Pavel Durov, has been released from custody and…