MySQL indeksai

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.

Dvejatainis paieškos medis

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 / Primary key 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 / Unique index Unikalus indeksas (angl. unique) – leidžiamos tik unikalios reikšmės, gali būti NULL reikšmės;
  • Paprastas indeksas / Index 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;
Žymos: , ,

6 komentarai

  1. xeranas

    Kaip supratau jei jau „primary key“ naudoji tai gauni visus index’ų teikiamus pliusus? INT tipas raktui turbut optimaliausias būtų?

    • Ne, PRIMARY KEY yra tik vienas iš pasirinkimų. Pliusas tas, kad tik su juo gali naudoti AUTO_INCREMENT. Minusas tas, kad PRIMARY KEY gali būt tik vienas visoje lentelėje. Beto nepanaudosi PRIMARY KEY jeigu yra besidubliuojančių reikšmių.. na čia gal ne minusas, čia tokia ypatybė..

      Taip, jeigu galima INT tipas yra vienas iš geresnių pasirinkimų. Tiesa, jis gana didelis, dažnai gali užtekti ir mažesnių, pvz.: MEDIUMINT, ar TINYINT.

    • Arunas Junevicius

      PRIMARY KEY yra visiskai tas pats kaip ir UNIQUE NOT NULL, innodb toki index’a net ir naudotu kaip PRIMARY KEY savo viduje, jei jis nebutu nurodytas explicitly.
      Truputi prasciau yra su UNIQUE KEY’ais kurie gali tureti NULL reiksmes, del to kad reikia saugoti papildoma baita ir tai apdoroti(inno ir myisam turi skirtumu sitoje vietoje), del to patiriamas overhead’a.
      O jei indexas nera UNIQUE tai viskas priklauso nuo jo cardinality, jei jis didelis – liux, o priesingu atveju MySQL’o optimizatorius gali pasirinkt skenuoti lentas. ir tas index’as be reikalo uzims vieta diske ir RAM’e.
      Del to kas turi buti pasirenkamas kaip PRIMARY KEY, tai manau reliaciniu db teoretikai nesutiktu kad reikia aklai naudoti INT ir panasius. pirma reikia rasti prasminga PRIMARY KEY, o tuo atveju kai nepavyksta galima naudoti kazka NUMERIC, bet kuo mazesni, kad index’a islaikyti maza, nors esu mates diskusiju del GIUD’u naudojimo ir galiu isivaizduoti kad tam tikrose situacijose tai padetu performance’ui.
      „Tekstinės paieška MySQL’e“ tai apie FULLTEXT INDEX’us? butu idomu paskaityti, Mindaugai, neuzmesk sitos idejos;]

      • Dėl indeksų panašumų skirtumų tai lyg ir rašiau (-; Reikia suprasti, kad indeksų tam tikri „funkcionalumai“ persidengia, ir yra šiokia tokia hierachinė sėka kaip tie indeksai vis „girežtėja“.

        Nereikia taip kategoriškai teigti, kad PRIMARY KEY yra vidiškai tas pats kaip ir UNIQUE KEY, nes, kaip ir rašiau, egzistuoja ne vien „NOT NULL“ skirtumas – pamiršai dar AUTOINCREMENT, pamiršai ribojimus dėl tokio tipo indeksų kiekio ir pan.

        Indeksai lentoje turi būti sudėti tik tie, kurie realiai yra reikalingi. Nenaudojamas indeksas – logiška – turi būti ištrintas (-;

        Kas dėl optimizatoriaus – faktas, kad jis ne visada pasirenka teisingiausią indeksą, todėl visada, gali pasitikrinti, ir esant poreikiui nurodyti, kurį indeksą naudoti konkrečiai lentelei užklausoje.

        Idėjos dėl kitų temų neužmestos, tik labai nėra laiko, o ir mažai su tuo bedirbu. Gal kada nors…. (-;

  2. Arunas Junevicius

    autoincrement galima deti ant bet kokio index’uoto lauko, aisku beveik visada buna ant primary key’aus.
    turejau patirties kad geriau nenurodyti optimizatoriui koki indexa naudoti nes su laiku keiciasi duomenys, reiketu pradet naudot kita indexa. sekanti karta geriau keisciau optmizatoriaus nustatymus.
    „Nenaudojamas indeksas – logiška – turi būti ištrintas (-;“ gaila, bet dar nedirbau tokioje firmoje kur tai butu laikoma logiska;]

    • Taip, tikra tiesa – AUTO_INCREMENT gali būt ant kitokio tipo indeksų – mano klaida.

      Kas dėl indeksų naudojimo tai daug visokių patirčių, todėl irgi sutinku, kad kažką nustatyti, palikti ir niekada neperžiūrėti yra nelabai teisinga, nes pasikeitus duomenims, duomenų kiekiams, ir ypač schemoms – reiktų daryt indeksų reviziją (-;

Parašykite komentarą

El. pašto adresas nebus skelbiamas. Būtini laukeliai pažymėti *

Galite naudoti šias HTML žymas ir atributus: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>