Efektívne na MySQL, časť prvá

Na admin oddelení sa často stretávame s rôznymi problémami našich zákazníkov, ktoré sú nie vždy chybou na strane samotného servera a hostingu ako takého. Preto sme sa rozhodli písať seriál o rôznych tipoch, ktoré by vám mali pomôcť pochopiť a odstrániť niektoré problémy so stránkami. V tejto časti sa hlavne začneme venovať MySQL databázam.

Jednou z častých otázok, ktorú dostávame od klientov, je napr. Prečo je rýchlosť načítania  stránky zrazu nižšia než po spustení? Alebo aj Pri porovnávaní rýchlosti s tým, ako mi to beží na lokálnom PC, je načítanie oveľa pomalšie, prečo? Existuje viacero príčin pomalšieho chodu stránok. Jednou z najčastejších je práve to, že dotazy na databázu, ktoré pri menšom počte návštevníkov a menšom rozsahu databázy neboli také kritické, zrazu zaberajú čoraz viac času.

Prvou vecou, ktorú musíme spraviť, je prejsť si, aké dotazy nám môžu spôsobovať problémy. Na sledovanie vykonávania jednotlivých dotazov môžeme použiť príkaz SHOW processlist; priamo v phpmyadmine. Pomocou tohto príkazu môžeme okrem iného vidieť, ako dlho už dotaz beží, jeho identifikačné číslo, ako aj momentálny stav. V rámci hostingov WebSupportu využívame možnosť zaznamenávania MySQL dotazov trvajúcich príliš dlho do slow query logu, ktorý môžete nájsť vo WebAdmine v sekcii Databázy. Pokiaľ teda hľadáte príčinu spomalenia stránok a podozrievate databázu, toto miesto je výhodné vždy pozrieť čo najskôr.

Keď máme podchytený dotaz, ktorý sa vykonáva dlhší čas, vieme nájsť aj detaily toho, čo robí. Postačí uložiť si číslo dotazu (pokiaľ prebieha) alebo jeho syntax a následne MySQL zaslať dotaz EXPLAIN (EXPLAIN číslo_procesu; alebo EXPLAIN  syntax_pomalého_query;). Následne môžeme rýchlo zistiť, aký objem dát (množstvo spracovaných riadkov)  či a aké sa používajú indexy, či je nutné vytvoriť temporary tabuľky na disku, lebo sa medzivýsledok nevojde do pamäte, ako aj medzi akými typmi premenných porovnávame.

Indexovanie tabuliek 

Jednou zo základných možností, ako optimalizovať databázu, sú práve indexy. Často sa dáta v rámci stĺpca dajú zapísať oveľa efektívnejšie z pohľadu vyhľadávania –  jednotlivé polia môžeme upraviť do podoby hashu alebo binárneho stromu a následne zoradiť –  ako do kartotéky. Keď MySQL následne napr. vyberá podľa kritérií z tabuľky, nemusí prechádzať celý obsah databázy. Najčastejšie to užívateľ databázy môže pocítiť, keď jeho query ide naprieč viacerými tabuľkami (JOIN) a hľadaní unikátnych záznamov (DISTINCT).

Ak chceme spojiť dáta z dvoch tabuliek, je to čo sa týka rozsahu (a teda aj objemu) spracovaných dát trocha ošemetná situácia, pokiaľ by sme chceli hľadať hrubou silou. Náročnosť akéhokoľvek JOIN totiž stúpa s počtom riadkov a v najhoršom prípade rastie až exponenciálne. (Pikoška: že to môže byť až také zlé, som zbadal, keď som nad databázou istého fóra videl ako dotaz prehľadával niekoľko desiatok biliónov polí. Biliónov!)

Na to, aby sme mohli vyberať naprieč viacerými poľami, potrebujeme totiž najprv získať takú tabuľku, kde sú ku každej vybranej kombinácii stĺpcov z prvej tabuľky pridelené podľa možnosti relevantné stĺpce z druhej tabuľky, aby sme potom mohli vôbec porovnávať, ktoré riadky spĺňajú podmienky vo WHERE.

Pokiaľ teda vidíme, že je v dotaze direktíva  JOIN (alebo aj LEFT JOIN, a OUTER JOIN) a vidíme, že sa nepoužíva index, pozrieme, aké stĺpce sú v jednotlivých tabuľkách používané, pre ktoré následne vytvoríme index. Napríklad na tabuľke TABLE so stĺpcami TABLE.id,  TABLE.stĺpce,  TABLE.ktoré, a  TABLE.indexovať :

           CREATE INDEX id_join on TABLE (stĺpce, ktoré, indexovať);

To isté vykonáme aj pri druhej tabuľke. Indexovanie tabuliek je len jedno z viacerých riešení, ako zrýchliť načítavanie údajov z databáz a v neposlednom rade aj zrýchliť odozvu vašich stránok. K ďalším obvyklým ťažkostiam, ako je nesprávne použitie subquery, nevhodné PHP nastavenia, optimalizáciu spúšťaných skriptov atď. sa budeme venovať v ďalších blogpostoch. Dúfame, že vám tento článok aspoň trochu objasnil, ako funguje indexovanie v databázach a prečo je jeho používanie vhodné. Neváhajte sa podeliť s vašimi tipmi v komentároch.

Pokiaľ by ste mali o túto tému hlbší záujem, rozhodne môžem odporučiť nasledovné odkazy:

http://www.slideshare.net/osscube/indexing-the-mysql-index-key-to-performance-tuning

http://www.cs.duke.edu/csl/docs/mysql-refman/optimization.html