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