PostgreSQL 12 - urychlení výběrů

Jakákoliv databáze, je-li v ní malé množství záznamů se chová relativně rychle. S rostoucím počtem vět zpomaluje, často pod únosnou mez. Tento díl je věnován urychlení výběrů a to pomocí indexů.

28.6.2005 06:00 | MaReK Olšavský | přečteno 17656×

Indexy

Jakýkoliv databázový server ukládá data v nějaké datové struktuře, v souborech na disku (nebo diskovém poli, je-li k dispozici),  a je-li dat malé množství, probíhá vyhledávání patřičných vět (záznamů), příkazem SELECT, relativně velikou rychlostí. Téměř s určitostí lze tvrdit, že jediná data, která do databáze přicházejí uspořádaně jsou identifikační čísla vět, tj. hodnoty typu serial a bigserial, a jsou-li použita časová razítka pro čas uložení věty. Všechna ostatní data budou v podstatě neorganizovaný chaos. K tomu, aby se pro vyhledávání vět do tohoto chaosu vnesl alespoň nějaký pořádek slouží indexace.

Ekvivalent z reálného světa existuje. V zjednodušené formě se jedná o abecední rejstřík, který je k nalezení téměř ve všech odborných knihách. Pokud čtenář hledá určitý termín, nahlédne do rejsříku a pak přejde přímo na stránku knihy, kde se tento termín vyskytuje. Protože indexy nefungují pouze pro klíčová slova, lze je přirovnat spíše ke katalogizaci knih ve veřejných knihovnách, zde jsou k nalezení katalogy tříděné dle názvů knih, autorů, oborů, případně jazyků.

Index v databázi lze popsat jako virtuální tabulku s určeným tříděním, do které uživatel přímo nezapisuje a ani ji přímo nečte. Celou režii práce s indexy si řeší databázový server sám. Aby jejich použití nebylo tak 100% jednoduché, je třeba je používat s rozvahou, protože při nevhodném použití naopak databázi zpomalí, zejména pak při vkládání dat, což se opět nemusí projevit viditelně při vložení několika vět, nebo je-li jich v tabulce málo, ale při vkládání velkých dávek může dojít až k přerušení vkládání, kdy režie neustálé přeindexace bude tak veliká, že aplikace s databází spolupracující takříkajíc ztratí trpělivost. Co s tímto problémem bude uvedeno na konci článku.

Typy indexů

PostgreSQL server nabízí 4 typy indexů - B-tree (tento je defaultní), R-tree, GiST a Hash. Jak bylo v minulém díle napsáno, díky nepodpoře full textového vyhledávání, tak jak existuje v MySQL, v základní instalaci, není tento typ indexu v mání. Lze indexovat nejen jeden sloupec, ale i několik do jednoho indexu, je-li dle této kombinace pravidelně vyhledáváno, případně indexovat za použití funkce.

Základní vlastnosti jednotlivých typů indexů (přesný popis je v originální dokumentaci zde a zde (GiST)):

Indexů lze pro každou tabulku libovolné množství, ale při jejich neuváženém použití může naopak dojít k citelnému zpomalení práce s databází, zejménapři vkládání.

Kromě neomezeného počtu indexů pro tabulku lze indexy definovat i přes několik sloupečků tabulky. Logicky, pokud se omezují řádky ve výběru tabulky (podmínkami v části WHERE) a děje se tak pravidelně v určité kombinaci sloupců, je vhodné tuto kombinaci sloupečků zaindexovat. Index je pak uspořádán, že jsou první zaindexován první sloupeček, poté druhý, ... Maximální počet sloupečnků pro zaindexování v jednom indexu je dle dokumentace 32, ale už při kombinaci více než 3 sloupců do jednoho indexu by mělo být zváženo změnění návrhu struktury databáze a přistupující aplikace. Multisloupcové indexy lze definovat jen pro B-tree a GiST.

Indexy ať už na jednom sloupečku, nebo multisloupcové mohou mít vynucenu i informaci, že musí být unikátní. V současné době je tato vlastnost podporována pouze u B-tree indexů.

Za zvláštní typ indexů lze považovat klíče, proto jim bude věnován celý díl.

Indexace pomocí funkce je má význam, pokud se k datům ve sloupci přistupuje pravidelně s použitím funkce, npříklad SIN(), COS(), LOWER(), ...

Vytváření a rušení indexů

Pro vytvoření indexů slouží příkazy:

CREATE [UNIQUE] INDEX jmeno ON tabulka
[USING typ_indexu] (sloupec [trida_operatoru]
[, sloupec [trida_operatoru], ...])[ WHERE podminky];
CREATE [UNIQUE] INDEX jmeno ON tabulka
[USING typ_indexu] ( funkce (sloupec[, ...]) [trida_operatoru] [, ...]);
CREATE [UNIQUE] INDEX jmeno ON tabulka
[USING typ_indexu] (sloupec [trida_operatoru]
[, sloupec [trida_operatoru], ...]);

Trida_operatoru určuje oprátory, které může server v indexu pro daný sloupec použít, příkladem může být například, jejich příklad lze nalézt v originální dokumentaci.

Část WHERE slouží k vytvoření tzv. částečných indexů. Jsou kompromisní volbou mezi velikostí databázových souborů a rychlostí. Kupříkladu mohou být v databázi taková data, kdy se v 98% případů přistupuje na 10% řádek, poté má význam naindexovat těch 10% řádek a zbytek nechat nezaindexován s tím, že pokud uživatel potřebuje tato málo používaná data, musí být malinko trpělivější (při spojování více obsáhlých tabulek pomocí JOIN, hodně trpělivý).

Indexy se ruší příkazem, který je velmi jednoduchý příkaz:

DROP INDEX jmeno [, dalsi_jmeno [, ...]] [CASCADE | RESTRICT];

Ve většině případů stačí jednoduché DROP INDEX jmeno_indexu, přídavné klauzule CASCADE (zrušení všech objektů závislých na rušeném indexu) a RESTRICT (zákaz rušení objektů závislých na indexu) najdou použití ve složitějších databázích.

Občas se může vyskytnout potřeba vynutit opravu indexů na databázi, tato nutnost vzniká ve dvou případech, index byl poškozen a obsahuje neplatná data (tato varianta by neměla nikdy nastat), nebo při velkém množství indexů se mohou vyskytnout místa, kde nebyl index regenerován. K vynucení opravy se používá příkaz REINDEX {DATABASE | TABLE | INDEX} jmeno [FORCE]. Pro reindexaci všech indexů v databázi se použije REINDEX DATABASE ..., pro tabulku REINDEX TABLE ..., a pro jeden index REINDEX INDEX ... Parametr FORCE nemá význam, je jen kvůli kompatibilitě se staršími verzemi a jeho význam byl, že reindexace se provedla vždy i když indexy byly v pořádku.

Příklady:

CREATE INDEX idx_prods_title_hash ON products USING HASH (title);
CREATE INDEX idx_prods_title_btree ON products USING btree (title);
CREATE UNIQUE INDEX idx_prods_ids ON products (id, id_category);
DROP INDEX idx_prods_title CASCADE;
REINDEX TABLE users;

Práce s indexy

Indexy jsou vhodným nástrojem k urychlení výběrů dat, sice s drobným omezením rychlosti při vkládání dat. Při impotrech velkého množství dat se z tohoto důvodu používá malý Cimrmanovský úkrok stranou:

Závěrem

Tento díl byl věnován základům práce s indexy u PostgreSQL serveru. V příštím díle budou probrány klíče a poté se seriál opět vrátí k urychlování výběru a vkládání dat tentokrát optimalizací dotazů a dat, byť některé tyto záležitosti byly již několikráte v průběhu seriálu naznačeny.

Během psaní seriálu autor objevil poměrně zajímavou záležitost, EnterpriseDB, která je stavěna na základech PostgreSQL a přidává navíc funkcionalitu a velkou úroveň kompatibility aplikační úrovně s Oracle serverem.

Online verze článku: http://www.linuxsoft.cz/article.php?id_article=878