Tento komunitní návod je aktuální k 29. 8. 2019.
Aby i začátečníci v tomto oboru dobře pochopili, co to je a jak funguje databázový index, dovolíme si uvést analogii v běžném životě. Představte si, že jste ve velké veřejné knihovně a potřebujete najít knihu s konkrétním názvem (který znáte). Máte 2 možnosti – projít celou knihovnu, všechny její regály a číst si u každé knihy jeden název po druhém. To ale bude zjevně trvat velmi dlouho.
Druhá možnost je, že dojdete k rejstříku, což v knihovnách bývají šuplíky, ve kterých jsou lístky se základními informacemi o všech knihách. Zajdete k rejstříku, kde jsou lístky tříděny podle názvu knihy, šáhnete po šuplíku s počátečním písmenem hledané knihy a dle abecedního pořadí lístek velmi rychle najdete. Na něm se dozvíte, ve které místnosti, ve kterém regálu a ve které polici se hledaná kniha nachází a pak si pro tuto knihu na jistotu zajdete. Tento druhý způsob je zjevně velmi účinný, a pokud čtenáři často hledají knihy podle názvu nebo autora, určitě se vyplatí vytvořit pro ně pro tyto účely rejstříky.
A o tom samém je to v databázi.
Co je databázový index
Indexy jsou datové struktury, které umožňují rychlé vyhledávání záznamů podle indexovaných položek. Mějme napřiklad tabulku, ve které je každý záznam jednoznačně identifikován nějakým ID (např. celé číslo) a my chceme najít záznam s konkrétním číslem, třeba následujícím příkazem:
SELECT * FROM knihy WHERE ID=1234
Jestliže nad sloupcem ID neexistuje žádný index, musí MySQL projít úplně celý datový soubor, záznam po záznamu a v každém si přečíst ID. To je však relativně velmi náročné, protože mimo ID může záznam obsahovat další rozsáhlá data a to vše musí MySQL přečíst z datového souboru z disku do paměti a analyzovat. Navíc data v datovém souboru bývají zcela neuspořádaná (např. uložená v tom pořadí, v jakém byla do databáze vložena), a tak nelze pracovat s tím, že pokud při hledání dorazíme na vyšší hodnotu než hledanou, že bychom zbytek už nemuseli prohledávat.
Pro tyto případy je potřeba pro položku ID vytvořit index. To znamená, že se do zvláštního souboru uloží vhodně uspořádaný seznam všech ID (v tomto případě konkrétně tzv. B-strom, což je v jazyce matematiky a informatiky vyhledávací strom, zachovávající minimální hloubku), která se v tabulce vyskytují a k nim se uvede odkaz, na jakém místě v datovém souboru se záznam s tímto ID nachází. Index bývá mnohem menší (protože obsahuje jen to ID a dále nějaký ukazatel do datového souboru) a navíc je přímo uzpůsoben rychlému vyhledávání.
Indexy se nepoužívají pouze při hledání konkrétní jedné hodnoty položky, ale také se mohou použít pro hledání intervalu.
Smíšený index
Nad více položkami je samozřejmě možné vytvořit více indexů, pro každou položku zvlášť. Je zde však jiná možnost. Pokud potřebujete často hledat záznam podle více kritérií současně, můžeme vytvořit jeden index nad více sloupci tabulky současně. Například hledáme-li často knihy podle roku vydání a typu, mohl by příslušný SELECT dotaz vypadat takto:
SELECT * FROM knihy WHERE rok_vydani=2007 And typ='monografie'
Při hledání se použije vždy pouze nejvýše jeden index. Kdybychom měli jeden samostatný index nad sloupcem rok_vydani a druhý samostatný index nad sloupcem typ, použije se jen jeden z nich (volba mezi nimi záleží na implementaci, většinou se provádí nějaká predikce, který z nich je pravděpodobně účinnější). Použije-li se index pro rok_vydani, najdou se rychle v indexu všechny záznamy s rokem vydání 2007. Ty se však budou muset všechny v datovém souboru načíst a projít jeden po druhém a zjistit, které z nich jsou monografie, protože to se z indexu nedozvíme. Sice jsme si ušetřili čtení všech záznamů v tabulce z disku a omezili jsme se jen na všechny knihy z roku 2007, ale pořád to není ono. Pokud hledáme podle těchto dvou kritérií hodně často, chce to vylepšit.
Nyní přichází ke slovu smíšený index nad sloupci rok_vydani a typ (v tomto pořadí). Ve smíšeném indexu se hledá podle obou hodnot současně, resp. nejprve se vyhledá podle prvního sloupce a na to navazuje další index (B-strom), který odpovídá specifikované hodnotě prvního atributu a hledá se podle druhého atributu. Takový index už je komplikovanější a méně výkonný, ale oproti čtení dat z disku pořád řádově rychlejší.
Máme-li smíšený index dle výše uvedeného příkladu, nemusíme vždy hledat podle všech sloupců, které jsou v něm uvedeny, ale můžeme použít některou „levou“ část indexu. Smíšený index rok_vydani+typ můžeme použít na hledání třeba jen podle roku vydání, typy se budou brát všechny. Nelze to ale udělat naopak, tedy podle tohoto indexu hledat dle typu a vynechat rok_vydani.
Fulltextové indexy
Speciální kapitolou jsou fulltextové indexy. Ty slouží ke hledání ve velkých textových datech podle klíčových slov. Při vytvoření fulltextového indexu nad nějakým sloupcem se a analyzují slova a jejich četnost a podle toho se jim přidělí důležitost. Čím vícekrát se dané slovo vyskytuje v nějakém textu, tím je pravděpodobnější, že se tento text zabývá právě tématem, které dané slovo vyjadřuje. Fulltextové indexy samozřejmě nejsou takto jednoduché, záleží na konkrétní implementaci a dalších vylepšení.
Máme tedy sestaven index, obsahující slova a jejich důležitost (relevanci) v jednotlivých záznamech. Při hledání zadáme jedno nebo více klíčových slov, která se najdou v indexu a výsledky se většinou řadí dle relevance sestupně.
Fulltextové indexy jsou u MySQL k dispozici pouze při použití typu tabulky MyISAM.
Primární a unikátní index
Unikátním indexem se označuje takový index, který vylučuje výskyt jedné hodnoty v daném sloupci vícekrát. To nám zaprvé může zajistit, že skutečně omylem nevložíme do databáze jeden záznam s jednoznačným údajem vícekrát, zadruhé databázový systém může díky tomu lépe optimalizovat strukturu indexů, protože se nemůže stát, že jedna hodnota položky v indexu by odkazovala na více záznamů v datovém souboru.
Primárním indexem se zazývá unikátní index nad sloupcem tabulky, jehož hodnota jednoznačně identifikuje řádek (záznam) a má vždy hodnotu (nemůže být NULL).
Jak správně volit indexy
Vymyslet správné indexy nad tabulkou bývá často věda. Někoho by mohlo napadnou udělat indexy nad všemi sloupci. V tom případě ale došlo k nepochopení celé problematiky. Čím větší indexy, tím objemnější soubor, ve kterém jsou uloženy, tím více čtení bloků dat s indexy z disku a tím více paměti pro jejich uložení. Ale to není jediný problém velkých indexů. Je třeba myslet také na to, že přidáváme-li záznamy do tabulky (třeba příkazem příkaz INSERT INTO) či je měníme (UPDATE, DELETE), musí se zatřídit, změnit či smazat ve všech indexech, což nemusí být triviální operace (občas se musí celý index úplně překopat).
Při vymýšlení indexů je tedy nutno popřemýšlet nad následujícími věcmi:
- jaký bude poměr mezi dotazy a aktualizacemi – pokud bude velmi mnoho dotazů a velmi málo aktualizací, můžeme indexů mít více, protože se nebudou tak často měnit
- podle kterých sloupců se bude nejčastěji hledat, příp. jaké se budou používat jejich kombinace
- zda nelze trochu přeformulovat SQL dotazy tak, aby se daly lépe udělat indexy
Dále je třeba myslet na to, že indexy pro celočíselné hodnoty budou určitě mnohem rychlejší a méně objemné než indexy nad textovými poli (zde je navíc komplikace to, že textové řetězce mohou mít proměnnou délku a komplikovaněji se konstruuje příslušný B-strom).
Volba indexů je velmi individuální a je třeba nejprve provést analýzu způsobů používání databáze.