Straipsnis skirtas žmonėm, kurie nenaudoja MySQL indeksų, nežino kas tai yra arba dar tik planuoja juos naudoti arba jais domėtis. Naiviai tikiuosi, kad didžioji dauguma „php programuotojų“ išnaudoja indeksų teikiamus privalumus, bet taip pat žinau, kad yra nemažai tų, kuries juos dar reikia atrasti.
Programos, kurios neišnaudoja MySQL indeksų galimybių švaisto sistemų resursus (CPU, RAM, IO) todėl yra privaloma suprasti kaip veikia MySQL indeksai ir kaip juos optimaliai išnaudoti.
Populiariausiuose MySQL duomenų valdymo varikliuose MyISAM ir InnoDB indeksų veikimas šiek tiek skiriasi. Todėl visada svarbu atkreipti dėmesį, kurį varikliį jūs naudojate.
Kalbant supaprastintai, indeksai tai yra tokia speciali duomenų struktūra, kurioje galima greitai atlikti paiešką. Informatikoje šią nišą vysto „Duomenų stuktūrų“ mokslo sritis. Kalbant apie indeksus MySQL’o minėtuose varikliuose bendru atveju viskas susiveda į dvejatainio medžio (binarinio medžio, angl. binary tree) struktūrą. Dvejatainių (ir ne tik) duomenų medžių yra įvairių.
Svarbu prisiminti, kad MySQL naudoja surikiuotus dvejatainius medžius (arba dvejatainius paieškos medžius; angl. binary search/ordered tree). Paprastai sutinkamas „BTREE“ trumpinys.
Kaip viskas veikia be indekso? Tarkime turime duomenų lentelę iš dviejų stulpelių: studento_id ir studento_vardas. Lentelė saugome faile. Tarkime, kad norime paredaguoti kažkokio tai studento vardą, tam mes turime užkrauti jo duomenis į formą, o atlikus redagavimą juos užsaugoti:
(...)programa.php?komanda=redaguoti&studento_id=6
SELECT studento_vadas
FROM studentai
WHERE studento_id = {$studento_id}
Neturint jokio indekso, norimo studento duomenų paieška būtų vykdoma skaitant visus duomenis ir lyginant id lauką su užduotu kriterijumi. Tuo tarpu, jeigu sukurtumėm indeksą studento_id stulpeliui, tai minėto studento duomenų suradimui būtų naudojamas jau minėtas dvejatainis medis, jame būtų surastas studento duomenų adresas duomenų faile ir tada nuskaitomi reikiami duomenys pagal adresą, o ne skanuojama visa duomenų lentelė.
Stebūklas šioje vietoje yra tas, kad informacijos suradimas dvejatiniame paieškos medyje yra žymiai greitesnis. Iliustracijoje pavaizduotame medyje surasti 6-o studento duomenų adresą mums reikės tik dviejų palyginimo operacijų. Tuo tarpu neturint indekso ir nežinant ar nėra daugiau negu vieno įrašo su tuo pačiu id teks perskaityti visą duomenų lentelę.
Indeksų panaudojimas
Dažniausiai indeksai naudojami, kai:
- Reikia surasti duomenis pagal WHERE kriterijus;
- Reikia atrinkti duomenis lentelių sujungimui (JOIN);
- Rikia surasti MIN ar MAX reikšmes;
- ir k.t.
Indeksai yra kuriami kiekvienai lentelei atskirai pagal tos lentelės stulpelių duomenis CREATE INDEX … komandos pagalba.
Lentelėje gali būti daugiau negu vienas indeksas. Tai reiškia, kad galima sukurti indeksą ne vienam stulpeliui.
Taip pat indeksas gali apimti daugiau negu vieną stupelį (kombinuoti indeksai, angl. composite index).
Kiekvienam užklausoje naudojamam stulpeliui MySQL panaudoja tik vieną indeksą. Kurį indeksą kiekvienam konkrečiam stulpeliui iš visų galimų panaudoti paprastai nusprendžia speciali MySQL posistemė pagal statistinius ir kitus metodus. Taip pat galima rankiniu būdu nurodyti kokį indeksą naudoti (angl. index hinting).
Kokius indeksus ir kaip sėkmingai MySQL naudoja konkrečioje užklausose galima sužinoti pasinaudojant EXPLAIN komanda. Pvz.:
EXPLAIN SELECT studento_vadas
FROM studentai
WHERE studento_id = {$studento_id}
EXPLAIN komandos rezultatų interpretavimas ir SQL užklausų optimizavimas yra vertas kitų, daug didesnių straipsnių.
InnoDB atveju pirminis indeksas ypatingas tuo, kad jame iš karto saugojami suinteksuoto stulpelio duomenis, kas leidžia tam tikrai atvejais dar papildomai eliminuoti operacijų išvengiant kreipimasį į duomenų failą.
Indeksų tipai
Iš karto noriu pasakyti, kad tai yra supaprastinta indeksų tipų apžvalga:
Pirminis raktas (anlg. primary key) – leidžiamos tik unikalios reikšmės nelygios NULL. Paprastai naudojamas, kai reikia vienarekšmiškai identifikuoti objektą ir negali būti neaprašytų objektų;
Unikalus indeksas (angl. unique) – leidžiamos tik unikalios reikšmės, gali būti NULL reikšmės;
Paprastas indeksas (angl. index) – reikšmės gali dubliuotis ir leidžiamos NULL reikšmės. Dažniausiai naudojamas, laukams pagal kuriuos sujungiamos lentelės arba atliekama paieška pagal kriterijus, kai negalime naudoti pirminio rakto;
Teisingumo dėlei verta paminėti, kad yra kitų indeksų tipų, bet apie juos galima bus pakalbėti kituose straipsniuose (-;
Minusai
Nors indeksų nauda akivaizdi, bet yra ir keletas neigiamų faktorių, kuriuos svarbu paminėti. Visų pirmą logiška, kad indeksai užima papildomą vietą. Aišku ta papildoma vieta diske yra labai stipriai kompensuojama paieškų vykdymo greičiu, todėl aš siūlau visai nevertinti padidėjusių vietos sąnaudų.
Daug svarbiau yra tai, kiek papildomai operacijų reikia indeksą atnaujinti įterpiant ir modifikuojant duomenis. Turint daug ir didelių indeksų vienoje lentelėje kartais gali reikšti, kad įterpimo ar atnaujinimo (INSERT, UPDATE ar pan.) operacijos bus lėtos. Kartais gali būti taip, kad į lentelę duomenys yra vien tik rašomi (pavyzdžiui žurnalo vedimas), tai gali būti taip, kad indeksai toje lentelėje nereikalingi.
Taip pat egzistuoja tam tikri indeksų apribojimai, pavyzdžiui ribotas indekso ilgis, ribotas/reguliuojamas resursų išskyrimas atliekant veiksmus su indeksais arba tai, kad skiriasi kokius indeksus galima naudoti skirtingo tipo stulpeliams.
Temos ateičiai
- SQL užklausų optimizavimas pasinaudojant EXPLAIN (EXPLAIN EXTENDED);
- MySQL duomenų tipų apžvalga;
- Tekstinės paieška MySQL’e;
6 komentarai