Základní princip fungování indexů

V této kapitole si vysvětlíme, co je databázový index a proč může výrazně ovlivnit rychlost vyhledávání dat. Ukážeme si, že index není automatickým řešením pro každý dotaz a že jeho přínos závisí hlavně na tom, kolik záznamů dokáže z vyhledávání vyloučit. Také se dozvíte, proč některé indexy pomáhají výrazně, zatímco jiné mohou mít jen malý nebo žádný přínos.

K čemu slouží index

Index v databázi si lze představit jako pomocnou kartotéku.

Představme si kartotéku pacientů u lékaře. Skutečné karty pacientů jsou uložené v hlavní kartotéce kde jsou uloženy podle čísel pacientů tak jak je lékař postupně zakládal např 1, 2, 3 … . Pokud bychom je měli zcela neuspořádané, hledání konkrétního pacienta by znamenalo procházet jednu kartu po druhé.

Javůrek  -> odkaz na kartu pacienta č. 15842
Novák    -> odkaz na kartu pacienta č. 00821
Svoboda  -> odkaz na kartu pacienta č. 03115

Když hledáme pacienta „Javůrek“, nemusíme procházet všechny pacienty. V seřazené pomocné kartotéce rychle najdeme příjmení Javůrek a podle odkazu sáhneme do hlavní kartotéky pro skutečnou kartu.

To je základní princip databázového indexu.

Index není kopie celé tabulky

Index obvykle neobsahuje všechna data z tabulky. Obsahuje pouze hodnotu, podle které hledáme, a odkaz na skutečný záznam v tabulce.

Databáze tedy při použití indexu často provádí dva kroky:

najde odpovídající hodnotu v indexu,
podle odkazu načte skutečný záznam ze zdrojové tabulky.

To je výhodné tehdy, když index najde jen malý počet záznamů. Pokud ale index vrátí příliš mnoho záznamů, databáze musí opakovaně přeskakovat z indexu do tabulky a načítat mnoho jednotlivých záznamů. V takovém případě index nemusí pomoci a někdy může být i nevhodný.

Dobrý index: hledání podle příjmení

Představme si, že máme 100 000 pacientů a hledáme konkrétní příjmení:

Příjmení = 'Javůrek'

Index podle příjmení dává smysl, protože příjmení rozdělí pacienty na mnoho malých skupin. Databáze rychle najde jen několik málo odpovídajících záznamů.

Takový index má vysokou vypovídací hodnotu. Říká databázi: „Hledej v malé části dat.“

Špatný index: hledání podle pohlaví

Nyní si představme index podle pohlaví:

Pohlaví = 'M'
Pohlaví = 'Ž'

Takový index rozdělí celou kartotéku pouze na dvě velké skupiny. Pokud má ordinace 100 000 pacientů, může být ve skupině „M“ například 50 000 pacientů a ve skupině „Ž“ dalších 50 000 pacientů.

Index sice databázi řekne, kde jsou muži a kde ženy, ale pořád vrátí obrovské množství odkazů na skutečné karty pacientů.

Databáze pak musí udělat mnoho operací typu:

najdi záznam v indexu
sáhni do hlavní kartotéky
najdi další záznam v indexu
sáhni do hlavní kartotéky
najdi další záznam v indexu
sáhni do hlavní kartotéky
...

To nemusí být rychlejší než projít hlavní kartotéku přímo. Proto samotný index nad údajem, který má jen několik málo hodnot, často nepřináší očekávané zrychlení.

Co znamená selektivita indexu

U indexů je důležité přemýšlet nad tím, kolik záznamů daná hodnota vybere. Tomu se říká selektivita.

Dobře selektivní údaj:

ID pacienta
číslo dokladu
ID skladové karty
příjmení v rozsáhlé evidenci

Špatně selektivní údaj:

pohlaví
skryto/neskryto
aktivní/neaktivní
typ s několika málo hodnotami

Index je užitečný hlavně tehdy, když databázi umožní rychle vybrat malou část záznamů. Existují situace, kdy i na hodnotu Ano/Ne je vhodné vytvořit index a to tehdy, když většina záznamů má jednu hodnotu a za druhou hledáme, abychom našli např. nedokončené doklady.

Příklad se sloupcem HIDDEN

Sloupec typu HIDDEN obvykle obsahuje jen hodnoty:

A
N

Takový sloupec je podobný příkladu s pohlavím. Nerozděluje data na mnoho malých skupin, ale jen na dvě velké skupiny. Pokud většina záznamů má hodnotu:

HIDDEN = 'N'

pak index nad tímto sloupcem databázi příliš nepomůže. Databáze by přes index našla velké množství odkazů a následně by musela načítat velké množství skutečných záznamů z tabulky.

Proto není vhodné automaticky říkat:

Ve WHERE je sloupec HIDDEN, doplňme na něj index.

Správnější otázka zní:

Která podmínka v dotazu nejvíce omezuje počet záznamů?

Příklad s více indexy

Oba indexy obsahují jen odkazy na skutečné záznamy. Skutečná data jsou stále v hlavní tabulce. Databáze se při zpracování dotazu rozhoduje, který index použije. Obvykle je výhodnější použít index, který záznamy omezí nejvíce. Pokud hledáme konkrétního pacienta podle příjmení, je výhodnější index podle příjmení. Pokud hledáme všechny ženy v evidenci, index podle pohlaví může vrátit příliš mnoho záznamů a nemusí být přínosný.

Tabulka může mít více indexů. V příměru ke kartotéce to znamená, že vedle hlavní kartotéky existuje více pomocných kartoték.

Například:

Index podle příjmení:
Javůrek  -> odkaz na kartu č. 15842
Novák    -> odkaz na kartu č. 00821
Svoboda  -> odkaz na kartu č. 03115

a zároveň:

Index podle pohlaví:
M -> odkazy na mnoho tisíc karet
Ž -> odkazy na mnoho tisíc karet

Důležité pravidlo pro psaní dotazů

Při psaní SQL dotazu je vhodné přemýšlet stejně jako při hledání v kartotéce:

Podle čeho bych začal hledat, kdybych to dělal ručně?

Nejprve je vhodné použít údaj, který nejvíce zúží hledání. Teprve potom má smysl připojovat další související informace. U SQL dotazů to znamená:

začínat tabulkou, kde je nejsilnější omezující podmínka,
používat JOIN místo historického výčtu tabulek ve FROM,
nepřidávat index jen proto, že se sloupec vyskytuje ve WHERE,
vždy přemýšlet, kolik záznamů daný index skutečně vybere.