Zvýšení výkonu MySQL databází díl 1

Dnes se ve většině případů využívají různé frameworky které obsahují ORM, díky kterým dotazy na databázi vůbec nepíšeme. To má určitě výhodu při implementaci ORM, pro složitější dotazy ale nemusí funkce ORM stačit, nebo je vygenerovaný dotaz pomalý. 

Nechci tu rozebírat výhody či nevýhody ORM, protože jich je mraky pro různé jazyky a každý je jinak výkonný. Ukážeme si ale různé možnosti optimalizace, některé půjdou využít i v případě použití s ORM, jiné nikoli.

1 Optimalizace fyzického návrhu

Jako první, se podíváme na optimalizaci tabulek, indexů apod. Tyto úpravy můžete uplatnit i v případě, že ORM využíváte.

1.1 Zvažte přidání INDEXu

Pokud podle nějakého pole vyhledáváte často, je vhodné přidat index. Tímto zrychlíte vyhledávání, ale zpomalíte vkládání, mazání a v případě úpravy daného sloupce i tuto činnost. Proto je vhodné zvážit nebo otestovat jestli se přidání vyplatí.

Druhá věc je, že indexy zabírají místo, a pokud přidáte indexy všude, může vám velikost indexů několikrát převýšit velikost tabulky. Pokud vyhledáváte často v několika sloupcích zároveň, můžete přidat 1 index na více sloupců. neboli složený index.

-- Index je tvořen sloupcem objednavka_id a zbozi_id v tomto pořadí
-- Zde se index využije
SELECT cena FROM objednavka_polozka
WHERE objednavka_id = 2 AND zbozi_id = 5

-- Zde se index také využije
SELECT SUM(cena) FROM objednavka_polozka
WHERE objednavka_id = 2

-- Zde se index nevyužije, zbozi_id je až jako druhý sloupec.
-- Řazení je primárně podle objednavka_id
SELECT SUM(cena) FROM objednavka_polozka
WHERE zbozi_id = 5

1.1.1 Pozor na velikost indexu

Pokud máte v tabulce sloupec VARCHAR(200) a vytvoříte na něm index, bude každý záznam mít 200 bytů. V Indexu musí mít každý záznam shodnou velikost. Dobře proto zvažte kolik je opravdu potřeba mít maximální délku sloupců.

1.2 Zvažte UNIQUE Index v případě unikátního pole

Pokud hledáte uživatele podle loginu, ten bude unikátní, můžete přidat UNIQUE index. Při nalezení výskytu se server nebude snažit nalézt další výskyt a vykonávání ukončí. Je to podobné jako v případě psaní LIMIT 1. Prvně ale zvažte, jestli opravdu je vhodné umístit index.

SELECT id, avatar FROM users WHERE login = 'arxeiss'

1.3 Používat ENUM místo VARCHAR

Ideální příklad je sloupec post_status ve WordPressu. Obsahuje pouze pár možností jako je published, private, closed apod. Přesto je VARCHAR(20). Kdybychom jej předělali na ENUM tak vnitřně DB bude ukládat pouze malá čísla (1 byte) ale při vložení nebo selekci to ani nepoznáme a můžeme s tím pracovat jako se stringem.

1.4 Tabulky fixní délky

Na VŠB má každý žák login ve tvaru 3 písmena z příjmení a poté 4 číslice. Mé je KUT0028. Pomocí tohoto loginu se v DB bude určitě vyhledávat často, takže vytvoříme INDEX ale abychom neplýtvali místem, použijeme VARCHAR(7). V minulosti totiž byly číslice pouze 3.

Přesto to můžeme ještě vylepšit přepsáním na CHAR(7), zde každý sloupec bude mít 7 bytů i v případě starého loginu s 6 znaky. Pokud ale v tabulce budeme mít pouze sloupce s pevnými délkami, server se může rychleji přesouvat mezi záznamy, protože celý záznam (řádek) bude mít vždy stejnou délku a přeskočit na další záznam je vždy posun o stejný počet bytů.

2 Optimalizace dotazů

Druhý způsob zrychlení je v samotném způsobu zápisů SQL dotazů. Přesto některé způsoby můžete aplikovat také s ORM.

2.1 Dotazy podporující cache

Server se snaží cachovat výsledky častých dotazů. Pokud ale v dotazu je nedeterministická funkce (funkce která se stejnými parametry vrátí jiný výsledek) jako je např. RAND, CURDATE, NOW atd. server výsledek nebude cachovat. Co když hledám všechny uživatele registrované dnes?

// Cache bude vypnuta, je použita nedeterministická funkce
mysql_query("SELECT login FROM users WHERE registered >= CURDATE()");

// Cache zůstane zapnuta
$today = date("Y-m-d");
mysql_query("SELECT login FROM users WHERE registered >= '{$today}'");

TIP: Pokud si píšte vlastní funkci, přidejte ji do definice DETERMINISTIC pokud to je možné. MySQL Manuál

2.2 Využívejte LIMIT 1

Pokud víte, že dotaz vrátí pouze 1 výsledek, nebo vás zajímá, jestli tam je alespoň 1, připiště na konec dotazu LIMIT 1. Tímto server po nalezení prvního výskytu ukončí hledání a tím urychlí vykonání. Pokud vždy bude pouze 1 výsledek, zvažte přidání UNIQUE indexu.

// Je to uživatelova první objednávka?

// Pokud jich bude 1000, budeme hledat ID všech objednávek
mysql_query("SELECT id FROM objednavky WHERE user_id = ".intval($user_id));

// Pokud jich bude 1000, po prvním výskytu se vyhledávání zastaví
mysql_query("SELECT id FROM objednavky WHERE user_id = ".intval($user_id)." LIMIT 1");

2.3 Nepoužívat SELECT *

Pokud nepotřebujete všechny sloupce, nepoužívejte hvězdičku. Takto server musí najít všechna data a poté je ještě poslat. Pokud je server na jiném stroji a data se musejí poslat přes síť, dojde tímto k mnohem většímu zpomalení.

---

Nezapomeňte se podívat na pokračování druhým dílem

Příště se podíváme jak otestovat SQL dotazy a zjistit, která část je zpomaluje. Čerpal jsem z látky probrané na VŠ tak také v článku Top 20+ MySQL Best Practices.