18. Jak správně používat indexy

V předchozím článku o indexech v MySQL jsme si řekli jaké druhy indexů databáze podporuje a jak je vytvořit. Abychom ale databázi využívali skutečně efektivně, nemůžeme indexy povytvářet jak se nám zachce.

Pokud nyní čekáte univerzální radu jak vytvářet indexy, musím vás zklamat. Logika vytváření indexů je vždy závislá na konkrétní aplikaci, kterou vytváříte. Vždy je potřeba zamyslet se, jaké operace s databází bude aplikace provádět nejčastěji. Pokud jde spíše o aplikaci, která bude data dlouhodobě ukládat a nebude k nim příliš přistupovat, je na zvážení zda-li indexy využívat, protože jak jsme si řekli, zápisové operace jsou indexy značně zpomalovány.

Většina aplikací ale častěji data čte než zapisuje, proto se indexy vyplatí. U každé aplikace si musíme ujasnit, jaké prohledávání databáze bude nejčastější. Pokud máme databázi knih, pravděpodobně budeme často hledat dle názvu, jmena autora a isbn. Nejspíš ale nebudeme často k hledání využívat počet stran.

Index není jenom WHERE

Index se nevyužívá pouze v klauzuli WHERE, další využití je při ražení. Klauzule ORDER BY taktéž využívá indexy vytvořené nad tabulkou. Při návrhu musíme tedy vzít v potaz i to, podle čeho bueme často řadit výsledky našich dotazů.

Taktéž při spojování tabulek se uplatňují indexy. Pokud jsou v obou tabulkách na sloupcích přes které se spojení vytváří indexy a mají stejný datový typ, bude operace spojení díky použití indexu rychlejší.

Aby toho nebylo málo, databáze využije indexů i v případě seskupování dat pomocí GROUP BY nebo využítí klíčového slova DISTINCT.

Použité datové typy

Použití vhodných datových typů nesouvisí přímo s indexy, ale jde také o věc, které může zrychlit a zefektivnit vaše dotazy, což je i hlavním smyslem indexů.

Nejrychlejší porovnávací operací je porovnání obyčejného čísla. To neznamená pouze datový typ INT, číslem jsou interně reprezentovány i další typy. Jde například o BOOL nebo ENUM, který na první pohled vypadá jako řetězec, ale jednotlivé hodnoty kterých může nabývat jsou interně reprezentovány čísly. Obdobně rychlou operací je porovnání vůči NULL.

Naopak, některé datové typy na první pohled vypadají jako čísla, ale jsou reprezentovány řetězcem. To je případ například FLOAT nebo DECIMAL. Porovnání řetězců je pak výrazně náročnější operací.

Mohutnost

Důležitým pojmem v rámci indexů je také mohutnost. Tímto výrazem označujeme počet možných hodnot, kterých daný sloupec nabývá (ne teoreticky kvůli datovému typu, ale dle skutečných uložených dat). Pokud bychom měli například tabulku uživatelů a v ní sloupec pohlaví, jeho mohutnost pravděpodobně bude 2 (muž, žena). Sloupec město ve stejné tabulace by mohl mít mohutnost až 6258 (hrubý počet obcí v ČR). Tento popis je značně zjednoduššující, ale pro představu snad dostačující.

Pokud má náš index podobnou mohutnost jako je počet řádků v celé tabulce, použití indexu nám operaci příliš neurychlý. Databáze v takovém případě projde celou tabulku, jako kdyby žádný index nebyl, protože použití indexu je v tomto případě pomalejší. S rostoucí mohutností indexu také roste velikost kterou index na disku zabírá. Může se snadno stát že bude i násobně vetší než velikost samotné tabulky a je nutné s tím počítat. Obecně řečeno je tedy výhodnější mít menší mohutnost indexu.

Indexy na více sloupcích

Velmi často využijeme index na více sloupcích. Nemám tím na mysli například podmínku, kde se vyskytují tři sloupce, z nichž každý má index, ale jeden index který je vytvořen na třech sloupcích. V takovém případě je velmi důležité, jaké je pořadí těchto sloupců.

Žádoucí je, abychom indexy vytvářeli tak, že první sloupce (které se procházejí dříve) budou mít co nejmenší mohutnost, abychom co nejvíce omezili počet procházených řádků. Pokud budeme uvažovat naši zmiňovanou tabulku uživatelů, jako první sloupec indexu bychom určitě měli použít pohlaví a až druhý město. Díky takto vytvořenému indexu může databáze při dotazu rovnou přeskočit témeř polovinu položek a dále vyhledávát již pouze v druhé polovině.

Vynucení a ignorování indexu

V některých případech může databázový systém vyhodnotit, že použití indexů nemusí být výhodné. Může to být například v případě, který jsme si zmiňovali u mohutnosti. Další možností je že námi stanovená podmínka vyřadí příliš málo řádku (například WHERE mesto != 'Vyškov'), tato podmínka by nám v normálním rozložení uživatelů vynechala jen zlomek ze všech záznamů, databáze tedy opět raději zvolí normální sekvenční průchod.

V takových případech máme možnost vynucení použití indexu pomocí USE INDEX(), kde specifikujeme jaký konkrétní index (nebo více indexů oddělených čárkami) musí databáze použít:

SELECT jmeno, prijmeno

FROM uzivatele

USE INDEX (mesto)

WHERE (mesto != 'Vyškov');

Obdobným způsobem můžeme databázi určit, že nějaký index využít nesmí. Dotaz by vypadal stejně, pouze bychom použili IGNORE INDEX().



Vytvoření správných indexů, které zefektivní práci s naší databází není jednoduchá věc. Než se do jejich tvorby pustíme, měli bychom si detailně rozplánovat, jaké typy dotazů v naší aplikaci budeme nejčastěji využívat a jakou strukturu uložených dat očekáváme.

Další díly tutoriálu

16. Výběr dat - co se jinam nevešlo
17. K čemu slouží indexy
18. Jak správně používat indexy
19. Transakce
20. Uložené procedury