Efektivně na MySQL, část první


V admin oddělení se často setkáváme s různými problémy našich zákazníků, které jsou ne vždy chybou na straně samotného serveru nebo hostingu jako takového. Proto jsme se rozhodli začít psát seriál o různých tipech, které by vám měly pomoci pochopit a odstranit některé problémy se stránkami. V této části se hlavně začneme věnovat MySQL databázím.

Jednou z častých otázek, kterou dostáváme od klientů, je např.: „Proč je rychlost načítání stránky najednou nižší než po jejím spuštění?“. Nebo: „Při porovnání rychlosti s tím, jak mi to běží na lokálním PC, je načítání na serveru mnohem pomalejší, proč?“. Existuje řada příčin pomalejšího chodu stránek. Jednou z nejčastějších je právě to, že dotazy na databázi, které při menším počtu návštěvníků a menším rozsahu databáze nebyly takové kritické, najednou zabírají stále více času.

První věcí, kterou musíme udělat, je projít si, jaké dotazy nám mohou způsobovat problémy. Pro sledování provádění jednotlivých dotazů můžeme použít příkaz SHOW processlist; přímo v PhpMyAdminu. Pomocí tohoto příkazu můžeme mimo jiné vidět, jak dlouho už dotaz běží, jeho identifikační číslo a momentální stav. V rámci hostingů WebSupportu využíváme možnost zaznamenávání MySQL dotazů trvajících příliš dlouho do slow query logu, který můžete najít ve WebAdminu v sekci Databáze. Pokud tedy hledáte příčinu zpomalení stránek a podezříváte databázi, na toto místo byste se vždy měli podívat úplně nejdříve.

Když máme podchycen dotaz, který se provádí delší dobu, umíme najít i detaily toho, co dělá. Postačí uložit si číslo dotazu (pokud probíhá) nebo jeho syntaxi a následně MySQL zaslat dotaz EXPLAIN (EXPLAIN číslo_procesu; nebo EXPLAIN syntax_pomalého_query;). Následně můžeme rychle zjistit, jaký objem dat (množství zpracovaných řádků) a použité indexy se využívají. Zda je nutné vytvořit temporary tabulky na disku, protože se mezivýsledek nevejde do paměti a také typy proměnných, které porovnáváme.

Indexování tabulek

Jednou ze základních možností, jak optimalizovat databázi, jsou právě indexy. Často se data v rámci sloupce dají zapsat mnohem efektivněji z pohledu vyhledávání – jednotlivá pole můžeme upravit do podoby hash nebo binárního stromu a následně seřadit – jako do kartotéky. Když MySQL následně např. vybírá podle kritérií z tabulky, nemusí procházet celý obsah databáze. Nejčastěji to uživatel databáze může pocítit, když jeho query jde napříč několika tabulkami (JOIN) a hledání unikátních záznamů (DISTINCT).

Pokud chceme spojit data ze dvou tabulek, je to co se týče rozsahu (a tedy i objemu) zpracovaných dat trochu ošemetná situace, pokud bychom chtěli hledat hrubou silou. Náročnost jakéhokoli JOIN totiž stoupá s počtem řádků a v nejhorším případě roste až exponenciálně. (Pikoška: že to může být až tak špatné, jsem zažil, když jsem u databáze jistého fóra viděl, jak dotaz prohledával několik desítek bilionů polí. Bilionů!).

Na to, abychom mohli vybírat napříč více poli, potřebujeme totiž nejprve získat takovou tabulku, kde jsou ke každé vybrané kombinaci sloupců z první tabulky přidělené podle možnosti relevantní sloupce z druhé tabulky, abychom pak mohli vůbec srovnávat, které řádky splňují podmínky ve WHERE.

Pokud tedy vidíme, že je v dotazu direktiva JOIN (nebo i LEFT JOIN a OUTER JOIN) a vidíme, že se nepoužívá index, podíváme se, jaké sloupce jsou v jednotlivých tabulkách používané a pro ty následně vytvoříme index. Například na tabulce TABLE sloupcům TABLE.id , TABLE.sloupce , TABLE.které , a TABLE.indexovat:

CREATE INDEX id_join on TABLE (sloupce, které, indexovat);

Totéž provedeme i při druhé tabulce. Indexování tabulek je jen jedno z více řešení, jak zrychlit načítání dat z databází a v neposlední řadě také zrychlit odezvu vašich stránek. K dalším obvyklým potížím, jako je nesprávné použití subquery, nevhodné PHP nastavení, optimalizaci spouštěných skriptů atd. se budeme věnovat v dalších blogpostoch. Doufám, že vám tento článek alespoň trochu objasnil, jak funguje indexování v databázích a proč je jeho používání vhodné. Neváhejte se podělit s vašimi tipy v komentářích.

Pokud byste měli o toto téma hlubší zájem, rozhodně můžu doporučit následující 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


4 komentáře

Přidej něco
  1. 1
    JimGvid

    Dobrý den,
    měl bych dotaz k tomuto článku týkající se zefektivnění vyhledávání v MySQL DB. Je nějaký rozdíl pokud hledám jeden řádek a v dotazu použiji „LIMIT 1“ oproti tomu, když jej nepoužiji?

    Chápu, že v malém počtu záznamů je rozdíl zanedbatelný, ale jak by se to projevilo v nějakém velkém počtu záznamů?

    Děkuji

    Reply

    asi v rychlosti, kamo

    Reply

  2. 2
    František Droják

    Dobry den,
    v skratke: vykonove rozdiely zavisia od toho, ako velmi sa nim podari obmedzit rozsah prehladavanych dat.
    Statement LIMIT urcuje, aky pocet vysledkov je dostacujuci pre ukoncenie query. Preto je treba sledovat rychlost vykonania pre viacere pripady. Ako na profilovanie vykonu sa mozete docitat v druhom dieli :)
    Pokial je v sade (s velkym poctom zaznamov) viacero poli vyhovujucich podmienkam query, LIMIT 1 zastavi query po najdeni prveho vyhovujuceho.
    Ak je vysledok medzi prvymi, je to vyhra, ak je medzi poslednymi prehladanymi, rozdiel je minimalny.
    Co je vsak dolezite, LIMIT sa vykonava az uplne na konci (sub)query . V pripade, ak je zretazenych viacero query v jednom (join,subquery..) , je umiestnenie LIMIT klucove . (zelany stav je, aby sa muselo robit co najmenej kombinacii s co najrychlejsie dostupnymi datami.
    Pekne je to ukazane na prikladoch:
    http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
    http://stackoverflow.com/questions/5983156/fetching-a-single-row-from-join-table/6023217#6023217
    V praxi vzdy zalezi na konkretnej databaze a udajov, ktore hladate. Ked viete, ze potrebujete prave 1 (prvy/lubovolny vysledok), nie je v beznych podmienkach dovod ho nepridat.

    Reply

  3. 3
    JimGvid

    Dobrý den,
    děkuji moc, za Vaši obsáhlou odpověď! Na zmíněné případy jsem se díval a pochopil jsem z nich (snad dobře), že je to přesně jak píšete. V případě kdy vím, že potřebuji jeden řádek, nic nezkazím přidáním LIMIT 1 – pouze můžu získat při složitějším příkazu dřívější ukončení hledání výsledného řádku.

    Ještě jednou děkuji.

    Reply

+ Přidej komentář