Návrh struktury databáze
Základem optimalizovaných dotazů je již samotný návrh databáze. Ten by měl ideálně splňovat alespoň 3 normální formy. Blíže k normám viz. cs.wikipedia.org, nebo také užitečné manualy.net.
Při správném použití těchto normálních forem získáte řádně navržené tabulky se správně umístěnými klíči. Ty jsou doslova klíčové pro správné dotazy.
Typy tabulek v MySQL
V MySQL databází máte při tvorbě tabulek na výběr hned z několika typů. Dvě nejvyužívanější volby jsou MyISAM a InnoDB. Základním rozdílem mezi těmito typy je tzv. transaction-safe engine. Jedná se o množinu vlastností InnoDB tabulky, která zajišťuje správnou konzistenci dat při jednotlivých transakcích.
Nabízí uživatelské volby commit a rollback, které uživateli dovolí potvrzení dotazu - commit, případně jeho „odrolování“ - (vrácení dat do stavu před dotazem) - rollback. MyISAM transaction-safe engine nepodporuje a nechává správu a konzistenci dat na uživateli. Je však o poznání rychlejší a tudíž vhodné do internetových prezentací, kde si programátor aplikačně zajistí správnost dat sám. Více o InnoDB a jejím transaction-safe enginu naleznete anglicky zde.
Použití indexů a zacházení s EXPLAIN
Dalším krokem k urychlení dotazů je použití indexů. Indexy jsou datové struktury, které umožňují rychlé vyhledávání označených (zaindexovaných) záznamů. Blíže k indexům se můžete dočíst v seriálu na linuxsoft.cz
S volbou správného umístění indexů vám může pomoci mimo jiných i MySQL příkaz EXPLAIN. Nejprve si zformulujeme dotaz, který chceme analyzovat a pak před něj jednoduše dopíšeme EXPLAIN.
V PHPMyAdministratoru vypadá analýza dotazu následovně:
Jak je vidět, tento příkaz vrací mnoho užitečných výsledků: každý řádek představuje 1 tabulku z dotazu (část FROM). U každé je sloupec possible_keys, který vyjadřuje návrh sloupců pro použití indexů. Další sloupeček key představuje výpis sloupců, kde jsou indexy skutečne umístěny. Neposlední významnou informací je sloupec rows. Číslo uvedené v tomto sloupci představuje množství řádků, které MySQL musí projít při spuštění dotazu u každé tabulky. Přesný popis výstupů příkazu EXPLAIN najdete v dokumentaci MYSQL
Omezení množství výsledků - LIMIT
Užitečnou volbou dotazů je rovněž omezení množství výsledků pomocí klauzule LIMIT. Použití je vidět na příkladu výše. LIMIT přijímá 1 nebo 2 nezáporné parametry. Při použití 1 parametru definované číslo vyjadřuje maximální počet vrácených řádků od začátku tabulky. Při použití 2 parametrů udává první číslo offset od prvního řádku a druhé je maximální počet vrácených řádků. Výhodné použití této volby nalezneme především při listování stránkama s výsledky/záznamy.
Velikosti tabulek
Při správě databáze bychom měli dbát na velikost tabulek - množství dat v nich. Je více než jasné, že právě počet záznamů je rozhodujicí pro rychlost kladeného dotazu. V praktickém použití se nabízí například rozdělení dat do více tabulek. Např. chceme logovat přístupy lidí do aplikace. Pokud potřebujeme uchovávat logy několik let zpětně můžeme pro každý rok vytvořit vlastní tabulku a klást dotazy pouze na konkrétní tabulku.
Byť se toto může zdát v rozporu s normálními formami, tak se najdou v praxi podobné, ne-li užitečnéjší příklady využití pro optimalizaci dotazů.
Údržba tabulek
Zejména u InnoDB tabulek existuje možnost zavolání jednoduchého příkazu: ALTER TABLE název_tabulky;
Tento na první pohled bezvýznamný dotaz MYSQL server interně zpracuje tak, že danou tabulku uzamkne pro zápis a reoptimalizuje vyhledávání. Dopuručujeme využívat zejména u velkých tabulek např. v nočních hodinách (rozuměj v době, kdy není potřeba do tabulky zapisovat).