Index (databáze)

Index je obvykle definován výběrem tabulky a jednoho konkrétního sloupce (nebo více sloupců), nad kterými si analytik nebo designér databáze přeje dotazování urychlit; dále pak technickým určením datového typu a typu indexu. Chování a způsoby uložení indexů se mohou významně i výrazně lišit podle použité databázové technologie.

Výjimku mohou tvořit například fulltextové indexy, které jsou v některých případech (nerelační databáze typu Lotus Notes) definovány nad celou databází, nikoliv nad konkrétní tabulkou.

Vytvoření indexu způsobí zvýšení nároků databázového serveru na operační paměť a diskový prostor (o vyhledávací struktury, pokud se ukládají ve formě souboru). Velikost samotných základních dat to ale neovlivní. Ovlivní se ale práce databáze. Například, při importu dat (exportovaných z databáze na jiném počítači) definuje cílový databázový server strukturu tabulek a indexů, inicializuje pro ně potřebnou paměť (a diskový prostor) a během importu bude krom samotných dat průběžně aktualizovat i struktury indexů. Jinými slovy, ukládání do db je zpomaleno těmito procesy(vytvoření indexu a zapsání), ale čtení je značně urychleno (právě díky indexům).

Vytvořením indexu (například příslušným příkazem jazyku SQL – CREATE) databázový server (též zvaný Systém řízení báze dat – SŘBD) zarezervuje pro požadovaný index určitou část paměťového prostoru a uloží do něj informace o rozmístění hodnot indexovaných sloupců v tabulce (obvykle ve strojové a pro člověka nečitelné podobě, která závisí na použitých vnitřních algoritmech indexace). Pokud později dojde k dotazu (například pomocí příkazu SELECT nebo použitím pohledu), který se týká indexovaných sloupců, není tabulka prohledávána podle toho, jak jsou za sebou řádky uloženy, ale pomocí informací uložených v paměťovém prostoru indexu je přistupováno přímo k relevantním řádkům tabulky – dalo by se říct, že index funguje trochu podobně jako rejstřík v knize, kde místo odkazu na příslušnou stránku knihy je ukazatel na paměťové místo s kýženými daty.

Na první pohled by se mohlo zdát, že čím víc indexů, tím lepší chování databáze a že po vytvoření indexů pro všechny sloupce všech tabulkách dosáhneme maximálního zrychlení. Tento přístup naráží na dva zásadní problémy:

1. Každý index zabírá v paměti vyhrazené pro databázi nezanedbatelné množství místa (vzhledem k paměti vyhrazené pro tabulku). Při existenci mnoha indexů se může stát, že paměť zabraná pro jejich chod je skoro stejně velká, jako paměť zabraná jejími daty – zvláště u rozsáhlých tabulek (typu faktových tabulek v datovém skladu) může něco takového být nepřijatelné.
2. Každý index zpomaluje operace, které mění obsah indexovaných sloupců (například SQL příkazy UPDATE, INSERT). To je dáno tím, že databáze se v případě takové operace nad indexovaným sloupcem musí postarat nejen o změny v datech tabulky, ale i o změny v datech indexu.

Pro správné zvolení indexů by ten, kdo databázi navrhuje, měl vědět, jak často se vybírané záznamy z dané tabulky třídí podle zamýšleného sloupce (a kandidáta na index) a nakolik je důležité, aby vyhledávání a třídění podle něj bylo rychlé. U některých tabulek, které jsou např. číselníky o stálém počtu položek, řekněme max. několika desítkách, nemusí být třeba index definovat vůbec (Toto platí pouze v případě, že načtení celé tabulky (full table scan) se provede za max. dvě operace čtení).

Příklad definice klíčů v jazyku SQL

CREATE TABLE diskuzni_forum(
 id int(8) NOT NULL auto_increment,
 jmeno varchar (30) NOT NULL,
 email varchar (30) NOT NULL,
 prispevek mediumtext NOT NULL,
 cas_pridani datetime NOT NULL,
 PRIMARY KEY  (id),
 INDEX (cas_pridani)
)

Červený (sedmý) řádek udělá nad sloupcem id primární klíč; zelený (osmý) řádek povyšuje sloupec cas_pridani na (standardní) index – hledání a třídění podle něj bude od této chvíle optimalizováno databázovým strojem.

Databázové indexy (či indexsekvenční moduly) dělíme do různých druhů podle toho, co chceme při přístupech k primárním datům příslušné databázové tabulky optimalizovat. Označení druhů indexů se může různit, nejčastěji se používají tyto hlavní druhy:

Tento primární index tvoří sloupec (nebo kombinace více sloupců), které obsahují primární klíč (někdy označován také jako hlavní index). Jedná se o zvláštní druh indexu, který se v každé tabulce může vyskytovat nejvýše jednou. Je definován sloupcem (sloupci) tabulky, který svou hodnotou jednoznačně identifikují každý záznam. Ve většině případů dnes je dodržována zvyklost resp. existuje vžitá konvence tento sloupec nazvat ID (odvozeno od slova identifikovat) a jeho datový typ pak stanovit jako typ celočíselný (tedy typ INTEGER či SMALLINT, není-li třeba jinak – není to ale bezpodmínečně nutné). Databázový server musí být v tomto případě navržen tak, že není možné, aby do takto označeného sloupce (k němuž se tento primární index vztahuje) byla vložena duplicitní či multiplicitní hodnota klíče, tedy stejný klíč, který již v tabulce existuje resp. který již byl jednou vložen (takový pokus končí chybovým hlášením a zápisem do chybového logovacího souboru či do logovací tabulky). Klíč je tedy v tabulce unikátní, jedná se de facto o zvláštní případ druhu klíče UNIQUE.

Tento unikátní index je tvořen ze sloupců obsahujících unikátní klíč, jedná se o speciální druh indexu, který je významově podobný předchozímu typu PRIMARY co do jednoznačnosti záznamu podle unikátní hodnoty klíče (typ PRIMARY je pouze zvláštní případ typu UNIQUE, jedná se vlastně o podtyp) v databázové tabulce (ostatně, jak naznačuje i sám jeho název) a v praktickém dopadu, který to pak na práci s příslušnou databází má. Na rozdíl od předchozího typu však nemusí být unikátní index jediný, ale může být definováno více. Například kromě ID záznamu o osobě můžeme požadovat i unikátnost sloupce s loginovým jménem osoby. Jednotlivý index může být i složen z více sloupců. Například v tabulce o biologických druzích budeme potřebovat unikátnost kombinace druhového a rodového jména. Potom opět nelze vložit záznam s hodnotou klíče, který by již v této kombinaci někde v tabulce existoval respektive byl již dříve do tabulky vložen (situace opět vede k chybovému hlášení vypisovanému na standardní výstup a zápisu do chybového logovacího souboru či tabulky).

Index též zvaný SECONDARY je tvořen pro sloupce, které obsahují sekundární klíč čili druhotný klíč (někdy bývá též označován jako vedlejší index). Definicí jednoho či více indexů tohoto typu v tabulce zajišťujeme optimalizaci vyhledávání podle dalších sloupců, mimo primární nebo unikátní indexy. Databázový server vytvoří a nadále udržuje vnitřní konstrukci odkazů na řádky tabulky, jež poskytuje uspořádání podle příslušných hodnot ve sloupci, k němuž je index logicky vázán (podle hodnot sekundárního klíče). Udržování takto uspořádané konstrukce urychluje vyhledávání záznamů v databázi (je možno použít některé matematické interpolační numerické metody), logické či fyzické řazení záznamů jakož i jiné další datové operace s tabulkou, jež se mají provést na podmnožině záznamů z tabulky vymezené podmínkou položenou na hodnoty v sekundárním klíči. Na rozdíl od předchozích indexů PRIMARY a UNIQUE lze do tabulky vkládat záznamy, které nejsou v sekundárním indexu unikátní. U některých databázových systémů se může jednat i o sloupce tzv. fiktivní, tedy sloupce odvozené respektive vypočtené z hodnot sloupců fyzických resp. uložených.

Vytvořením indexu tohoto typu se databázový server bude snažit optimalizovat fulltextové vyhledávání v daném sloupci u dané tabulky. To, jakým způsobem to udělá, záleží na databázovém serveru samotném, průvodním jevem může být to, že na disku nebo v operační paměti bude udržovat například statistiku slov, které byly v tomto sloupci a tabulce zadány, nebo jiné pomocné hašovací funkce nebo vyhledávací tabulky.

Parciální index je index, který spadá do jednoho z výše uvedených základních druhů (v drtivé většině případů INDEX), ale týká se jen určité podmnožiny řádků tabulky. Databázový stroj s ním pracuje jen za určité podmínky, tato podmínka se týká některých z ostatních polí v dané tabulce, tato podmínka je součástí definice parciálního klíče. Parciální klíče mají jen některé databáze (např. PostgreSQL). Situace, kdy záznamy tabulky příliš nerovnoměrně spadají do jedné velké podskupiny, je příkladem a důvodem pro vytvoření parciálního indexu, který v této podskupině (a pouze v ní) záznamy zindexuje.

Funkční nebo též výrazový index pracuje (tj. porovnává, vyhledává, vytváří si interní pořadové tabulky) s prostou hodnotou sloupce, nad kterým je definován, ale nad funkční hodnotou na něj aplikovanou nebo i výrazem z nich složených. Pokud sledovaný výraz umožňuje použití i více sloupců z tabulky, lze pomocí něj nastavit i složitější chování přímo v databázovém stroji, například:

• porovnávat nezávisle na velikosti písmen

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

• třídit podle rozkódované hodnoty sloupce

CREATE INDEX test1_decode_col1_idx ON test1 (decode(col1));

• nastavit index pouze podle prvních např. 2 písmen

CREATE INDEX test1_left2_col1_idx ON test1 (substr(col1,0,2));

• nastavit unikátnost záznamu na základě shody dvou nebo více sloupců (například jména a příjmení)

CREATE INDEX full_name ON people ((first_name || ' ' || last_name));

V jiných databázích by se tato funkčnost provedla přiřazením sloupců, které mají dohromady být unikátní (v tomto případě first_name a last_name) do jednoho unikátního indexu. Podporuje-li databázový stroj funkční indexy s libovolným výrazem ze sloupců z dané tabulky, schopnost přiřadit více sloupců do jednoho indexu tím více než dostatečně supluje a objektivně se dá říci, že se jedná o flexibilnější řešení s většími možnostmi.

Funkčními indexy disponují například databáze PostgreSQL.

• Primární klíč
• Prostorový index

• Využití databázových indexů na root.cz
• Typy databázových indexů a Ukázka použití indexů – články Jakuba Vrány