PHPPamokos.lt


11. Indeksai ir užklausų optimizacija

Šioje pamokoje susipažinsite su duomenų bazės indeksais ir su bendrais spartos optimizavimo principais. Tiesa, iškart pasakysiu kad duomenų bazės lėto veikimo problemos yra unikalios beveik kiekvienam projekte, tad neretai nėra vienareikšmiško atsakymo į klausimą "kodėl stabdo", bet iš šios pamokos gausite svarbiausius pagrindus, kur ieškoti ką optimizuoti.

Indeksai ir jų kūrimas

Pačioje kurso pradžioje minėjau, kad MySQL suskirsto duomenis patogesniam darbui su jais - norint juos filtruoti, rūšiuoti ir panašiai. Nemelavau, bet tai tiesa tik iš dalies - galime dar paspartinti lentelių veikimą, nurodydami papildomus parametrus - indeksus. Paprastai kalbant, nurodome, kurie laukai konkrečioje lentelėje mums yra svarbūs, ir į juos MySQL turėtų "atkreipti ypatingą dėmesį".

Iš esmės, indeksas yra taisyklė, pagal kurią konkretus laukas arba keli laukai yra vidinėje MySQL sistemoje surūšiuojami taip, kad galima būtų greičiau atlikti paiešką pagal duos laukus - pvz atliekant SELECT su WHERE.

Sakykime, jeigu dažnai atliekame SELECT užklausas su WHERE pagal lauką customer_id, tai "apsimoka ant to lauko" sukurti indeksą - tada toks SELECT sakinys veiks 10-100 kartų greičiau.

Indeksą per phpMyAdmin sukurti paprasta - užėjus į konkrečios lentelės meniu Structure, ir po apačia yra skiltis Indexes:

Kaip matote, sąraše jau yra vienas indeksas - tai yra auto_increment laukas id, jis automatiškai taip pat laikomas indeksu, dėl to tokios užklausos kaip ... WHERE id = X veikia dažniausiai labai greitai.

Norėdami sukurti indeksą, spaudžiame apačioje esantį mygtuką Go ir matome štai tokį langą:

Indekso pavadinimas gali būti mūsų sugalvotas - dažniausiai jį pavadina pagal principą: prefiksas "idx", tada lentelės pavadinimas ir lauko pavadinimas. Sakykime, šiuo atveju pavadinimas galėtų būti idx_orders_product_id.

Komentaro laukas yra nebūtinas, o toliau - indeksų tipai yra keli:

  • INDEX - populiariausias ir mums būtinas žinoti, tiesiog uždeda indekso taisyklę ant lauko
  • UNIQUE - prie indekso, dar uždeda ir taisyklę, kad lauko reikšmės privalo būti skirtingos, t.y. negalima įterpti antros eilutės su tokiu pačiu el.paštu, ar customer_id, ar koks bebūtų tas laukas
  • PRIMARY - tai ir yra id laukas, tai yra tas pats UNIQUE, tik dar turintis savybę, kad lentelėje gali būti tik vienas PRIMARY indeksas.
  • FULLTEXT - šitas indekso tipas yra atskiras nuo visų kitų, nes jis naudojamas tekstiniams laukams, siekiant pagerinti paiešką pagal juos, norint surasti teksto dalis, kai yra jau šimtai tūkstančių ar milijonai eilučių.

Taigi, įprastuoju būdu pasirenkame tame lange tipą INDEX, pasirenkame ant kokio lauko reikia uždėti indeksą ir spaudžiame Go.

Taip pat indeksą galima sukurti ir "rankiniu" būdų per SQL, sintaksė yra labai paprasta:

CREATE INDEX idx_orders_product_id
ON orders (product_id);

Arba, jei norite unikalaus indekso, prirašome UNIQUE:

CREATE UNIQUE INDEX idx_orders_product_id
ON orders (product_id);

Taip pat galima sukurti indeksą, skirtą daugiau nei vienam laukui - tikslas būtų pagreitinti SELECT .. WHERE paiešką, kai yra atrenkama būtent pagal tuos du laukus vienu metu.

Svarbi pastaba: gali atrodyti, kad indeksai yra labai geras sprendimo būdas bet kokiems duomenų bazės "stabdžiams". Bet būkite atsargūs - lazda turi du galus: indeksus verta kurti tik ant tų laukų, pagal kuriuos realiai vykdoma paieška. Nes kiekvienas indeksas, nors ir pagreitina SELECT sakinius, kažkiek sulėtina INSERT sakinius (nes kiekvienai įterptai eilutei reikia pergeneruoti indeksus), taigi jei dėsite indeksus ant visų laukų - tai gali sukelti dar didesnės painiavos ir problemų.

EXPLAIN - kaip iš tikrųjų vykdomos užklausos

MySQL pateikia patogų variantą, kaip galima giliau pasianalizuoti užklausų vykdymą. Tam reikia tiesiog prie vykdomo SQL sakinio pradžioje pridėti žodį EXPLAIN:

EXPLAIN SELECT * FROM orders;

Įvykdę tokią SQL užklausą per phpMyAdmin, gauname štai tokį rezultatą - mums parodoma, koks būtent atrinkimo tipas vykdomas, ir kitos detalės. Galbūt pradžioje jas sunku suprasti, bet sudėtingesniėms užklausoms, kur yra visokių JOIN ir GROUP BY komandų, kartais EXPLAIN paaiškinimai labai informatyvūs ir padeda greitai atrasti spragas - kuri užklausos dalis veikia lėčiau ir kodėl (pvz nėra indekso ar jis yra netinkamai naudojamas).

Duomenų bazės optimizavimas - pagrindiniai patarimai

Na, ir atėjo laikas bendrai aptarti tam tikrus niuansus, kaip vyksta duomenų bazės optimizavimas - tai yra, kaip padaryti taip, kad užklausos vyktų greičiau. Štai 8 patarimai.

1. Pradinėje struktūroje - kuo daugiau laukų-skaičių

Jeigu turite lauką iš kelių atsakymų variantų, sakykime yra kažkoks prekės tipas ir tipų yra tik 10. Tai jeigu darysite prie lentelės products lauką product_type ir rašysite ten tekstu pvz "Tipas 1", "Tipas 2" ir pan., tai atrinkimas pagal tekstą veisk žymiai lėčiau negu tai būtų laukas product_type_id su išoriniu raktu į atskirą lentelę product_types, kur ir būtų saugomi tipų pavadinimai.

2. Protingai sudėlioti indeksai

Apie indeksus jau kalbėjome pamokos pradžioje, tad per daug nesikartosiu - indeksų reikia, tik klausimas ant kokių laukų ir kiek - o tai jau priklauso nuo jūsų užklausose esančių WHERE salygų.

3. Imkite tik reikiamus laukus, o ne *

Iš principo, sakinys "SELECT * FROM ..." yra blogis. Nes jis grąžina visus visus lentelės laukus, o mums dažniausiai iš jų reikia tik keletos ar net tik vieno. Tai yra perduodami papildomi baitai informacijos, kurie neneša jokios naudos. O perdavimo kanalas vis tiek ne guminis, tai su dideliu apkrovimu gali pasijausti. Tad visada išvardinkite, kokių laukų jums reikia, pvz SELECT code, price FROM products.

4. Naudokite LIMIT

Panašus patarimas į praėjusį. SELECT užklausose, jei nenurodote kitaip, grąžinama visa lentelė - ir jeigu ten yra šimtai tūkstančių įrašų, tada jums gali tekti palaukti kol tie duomenys bus sugrąžinti ir apdoroti. O dažniausiai mums juk reikia tik porcijos, tad naudokite LIMIT.

5. Kuo mažiau LIKE užklausų

WHERE sąlygoje esantis operatorius LIKE, lyginantis eilutes, veikia sąlyginai labai lėtai - kiekviename lauke ieško sutapimų su norima eilute, ir tai užtrunka. Tad jį naudokite tik tada, kai tikrai reikalinga plati tekstinė paieška, bet net ir ten galima sugalvoti pagrindinius raktažodžius, pagal kuriuos ieškoma, ir saugoti juos atskirai, taip pagreitinant paiešką.

6. Problemos? Suraskite "stabdančias" užklausas

Deja, MySQL kaip nemokamas įrankis neturi stipraus įrankio stebėjimui, kuris parodytų, kokios užklausos vykdomos lėčiausiai - tam reikia diegti kitus servisus, tokius kaip New Relic (tiesa, jis skirtas viso projekto stebėjimui, ne tik MySQL). Bet, tiesą pasakius, daugumoje atveju lėčiausias užklausas gana paprasta rasti ir pačiam - reikia tiesiog pažiūrėti į Analytics ar kitokį srauto matavimo įrankį, pažiūrėti kokie puslapiai veikia lėčiausiai, arba kurie kraunami dažniausiai - ir pažiūrėkite, kas gali būti kaltininkas: užklausa su daug JOIN, su LIKE paieška, su WHERE sąlyga be indekso ir t.t.

7. Kuriant duomenų bazę - reikiami laukų tipai

Jeigu laukas yra skaičius, ir didžiausia įmanoma jo reikšmė gali būti sakykime 365 (metų dienos) arba apie 10000 (sakykime, tiek Lietuvoje yra krepšininkų), tai jums nereikia lauko BIGINT - jis daugiau vietos užims diske ir vėlgi gali būti stabdymo priežastimi. Analogiškai ir su kitais laukais - kurkite tik tiek kiek jums realiai reikia. Prireikus, po to nesunkiai padidinsite.

8. EXPLAIN - jūsų draugas

Ypač sudėtingoms užklausoms vykdyti labai praverčia planas, kur vizualiai matote, kas naudojama duomenų atfiltravimui. Idealiame pasaulyje viskas turi būti ieškoma pagal indeksus, tai jei kur nors EXPLAIN laukelyje key matysite NULL, tada žinokite, kad paieška vyksta paprastuoju būdu, nieko neoptimizuojant, ir jau galima toje vietoje bandyti kažką pagreitinti.

Kurso apibendrinimas

Štai ir viskas šiame kurse - šių pamokų pilnai užtenka, kad naudotumėtės MySQL "buitiniame lygyje". Jeigu dirbsite su labai dideliais projektais, tada galima nerti giliau į mišką ir aiškintis MySQL serverio konfigūravimo ypatumus ir kitokius dalykus, bet čia jau būtų visai kito lygio kursas. Bet jeigu pamatysite, kad pamokose yra kažkas praleista ar norite kažko giliau, drąsiai rašykite povilas@phppamokos.lt - paaiškinsiu asmeniškai arba įtrauksiu medžiaga į pamoką. Sėkmės taikant žinias praktikoje!



(c) 2015-2018. Visais klausimais kreipkitės povilas@laraveldaily.com