Návrh štruktúry databázy
Základom optimalizovaných otázok je už samotný návrh databázy. Ten by mal ideálne splňovať aspoň 3 normálne formy. Bližšie k normám viď. cs.wikipedia.org, alebo tiež užitočné manualy.net.
Pri správnom použití týchto normálnych foriem získate riadne navrhnuté tabuľky so správne umiestnenými kľúčmi. Tie sú doslova kľúčové pre správne dotazy.
Typy tabuliek v MySQL
V MySQL databáze máte pri tvorbe tabuliek na výber hneď z niekoľkých typov. Dve najvyužívanejšie voľby sú MyISAM a InnoDB. Základným rozdielom medzi týmito typmi je tzv. transaction-safe engine. Jedná sa o množinu vlastností InnoDB tabuľky, ktorá zaisťuje správnu konzistenciu dát pri jednotlivých transakciách.
Ponúka užívateľské voľby commit a rollback, ktoré užívateľovi dovolia potvrdenie otázky - commit, prípadne jeho „odrolovánie“ - (vrátenie dát do stavu pred dotazom) - rollback. MyISAM transaction-safe engine nepodporuje a necháva správu a konzistenciu dát na užívateľovi. Je však o poznanie rýchlejšie a teda vhodné do internetových prezentácií, kde si programátor aplikačne zaistí správnosť dát sám. Viac o InnoDB a jej transaction-safe enginu nájdete anglicky tu.
Použitie indexov a zachádzanie s EXPLAIN
Ďalším krokom k urýchlenie dotazov je použitie indexov. Indexy sú dátové štruktúry, ktoré umožňujú rýchle vyhľadávanie označených (zaindexovaných) záznamov. Bližšie informácie o indexoch sa môžete dočítať v seriály na linuxsoft.cz
S voľbou správneho umiestnenia indexov vám môže pomôcť mimo iných aj MySQL príkaz EXPLAIN. Najprv si sformulujeme dotaz, ktorý chceme analyzovať a potom pred neho jednoducho dopíšeme EXPLAIN.
V PHPMyAdministratoru vyzerá analýza dotazu nasledovne:
Ako je vidieť, tento príkaz vracia mnoho užitočných výsledkov: každý riadok predstavuje 1 tabuľku z dotazu (časť FROM). Pri každej je stĺpec possible_keys, ktorý vyjadruje návrh stĺpcov pre použitie indexov. Ďalší stĺec key predstavuje výpis stĺpcov, kde sú indexy skutočne umiestené. Významnou informáciou je stĺpec rows. Číslo uvedené v tomto stĺpci predstavuje množstvo riadkov, ktoré MySQL musí prejsť pri spustení dotazu pri každej tabuľke. Presný popis výstupov príkazu EXPLAIN nájdete v dokumentácií MYSQL
Obmedzenie množstva výsledkov - LIMIT
Užitočnou voľbou dotazov je rovnako obmedzenie množstva výsledkov pomocou klauzule LIMIT. Použitie je vidieť na príklade vyššie. LIMIT prijíma 1 alebo 2 nezáporné parametre. Pri použití 1 parametru definované číslo vyjadruje maximálny počet vrátených riadkov od začiatku tabuľky. Pri použití 2 parametrov udáva prvé číslo offset od prvého riadku a druhé je maximálny počet vrátených riadkov. Výhodné použitie tejto voľby nájdeme predovšetkým pri listovaní stránkami s výsledkami/záznamami.
Veľkosť tabuliek
Pri správnej databáze by sme mali dbať na veľkosť tabuliek - množstvo dát v nich. Je viac než jasné, že práve počet záznamov je rozhodujúci pre rýchlosť kladeného dotazu. V praktickom použití sa ponúka napríklad rozdelenie dát do viac tabuliek. Napr. chceme logovať prístupy ľudí do aplikácie. Pokiaľ potrebujeme uchovávať logy niekoľko rokov spätne, môžeme pre každý rok vytvoriť vlastnú tabuľku klásť dotazy iba na konkrétnu tabuľku.
Aj keď sa toto môže zdať v rozpore s normálnymi formami, tak sa nájdu v praxi podobné, ak nie užitočnejšie príklady využití pre optimalizáciu dotazov.
Údržba tabuliek
Hlavne pri InnoDB tabuliek existuje možnosť zavolania jednoduchého príkazu: ALTER TABLE názov_tabuľky;
Tento na prvý pohľad bezvýznamný dotaz MYSQL server interne spracuje tak, že danú tabuľku uzamkne pre zápis a reoptimalizuje vyhľadávanie. Odporúčame využívať hlavne pri veľkých tabuľkách napr. v nočných hodinách (rozumej v dobe, keď nie je potrebné do tabuľky zapisovať).