Indexy

Jak databáze najde objednávky zákaznice 3? Bez pomoci čte tabulku objednavky řádek po řádku, všechny, a nechá si ty, jejichž zakaznik_id je 3. U 30 řádků je to hned; u 30 milionů to chvíli trvá.

Index funguje jako rejstřík na konci knihy: místo abyste četli celou knihu, najdete v rejstříku slovo a jdete rovnou na jeho stránky. Index nad sloupcem uchovává jeho hodnoty seřazené, každou s řádky, ve kterých je, a v seřazeném seznamu databáze hodnotu najde rychle. Vytvoří se takhle:

CREATE INDEX objednavky_zakaznik_id ON objednavky (zakaznik_id);

Index má název (obvykle podle tabulky a sloupce) a za ON následuje tabulka a v závorkách sloupec. V dotazech index nikdy nezmiňujete: databáze sama pozná, kdy pomůže. Dotazy zůstanou stejné a najdou stejné řádky, jen rychleji.

Co databáze plánuje udělat, uvidíte, když před dotaz napíšete EXPLAIN QUERY PLAN. Jeho výsledek má několik sloupců; podstatný je detail. Bez indexu:

EXPLAIN QUERY PLAN
SELECT * FROM objednavky WHERE zakaznik_id = 3;

SCAN znamená, že databáze čte celou tabulku. S indexem hledá (SEARCH):

SEARCH znamená, že hodnotu vyhledá v indexu, tady v objednavky_zakaznik_id. Závorka říká, podle kterého sloupce hledá, a otazník zastupuje hledanou hodnotu, tady 3.

Přesné znění závisí na verzi SQLite; podstatné je, jestli je tam SCAN, nebo SEARCH. Index pomáhá u velkých tabulek, a to u sloupců, podle kterých dotazy hledají ve WHERE, spojují v JOIN … ON a řadí v ORDER BY. Primární klíč má vlastní index a stejně tak každý sloupec s UNIQUE (tak databáze rychle ověří, že tam hodnota ještě není). Cizí klíče ho v SQLite samy nedostanou, a protože se podle nich tabulky spojují pořád, bývají to první sloupce, které index dostanou. Index může mít i několik sloupců: ON hry (zanr, cena) pomůže dotazům, které hledají podle žánru nebo podle žánru a ceny, jako telefonní seznam seřazený podle příjmení a jména.

Index ale není zadarmo. Zabírá místo a každý INSERT, UPDATE a DELETE musí upravit všechny indexy tabulky, takže je pomalejší. Proto tabulky nedostávají index pro každý sloupec, jen pro ty, které dotazy opravdu potřebují. A u malých tabulek, jako jsou ty v obchodě Pixel Shop, index žádný znatelný rozdíl neudělá.

CREATE UNIQUE INDEX vytvoří index, který navíc dovolí každou hodnotu jen jednou, stejně jako UNIQUE. Tak jde toto pravidlo přidat k tabulce, která už existuje, což ALTER TABLE v SQLite neumí. Index, který nepotřebujete, smažete příkazem DROP INDEX a jeho názvem.

Typická chyba: obalit sloupec funkcí. Index nad sloupcem email nepomůže WHERE LOWER(email) = '…': index obsahuje e-maily tak, jak jsou, ne malými písmeny, takže databáze stejně musí LOWER() spočítat pro každý řádek.