ARCHIV

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ý | Články autora | přečteno 21562×

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)):

• B-tree - základní typ indexu, který je vytvářen automaticky při neuvedení typu indexu. Pro server je tato indexace nejvíce urychlující operace <, <=, =, >=, >, LIKE, ILIKE, ~ a ~*.
• R-tree - typ indexu optimalizovaný pro geometrická data. Pro operátory <<, &<, &>, >>, @, ~= a && (jejich význam najdete zde).
• Hash - Index, který je nejpomalejší na vytvoření. Rychlý pro porovnávání řetězců (respektive jejich hashů), funguje pouze pro operátor =
• GiST (Generalized Search Tree - zobecněný vyhledávací strom) - Jedná se o rozšiřitelnou strukturu, která sdružuje mezi jinými vlastnosti B-trees a R-trees. Protože se jedná o malinko složitější problematiku, nebude v tomto seriálu prodrobněji probírána.

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í.

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:

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:

• Spustí se transakce (budou v některém z příštích dílů) pro udržení konzistentních dat
• Provede se zrušení indexů, které jsou na tabulce, do které se budou importovat data
• Importují se data (ať sekvencí INSERTů, nebo pomocí COPY, které bude probráno u zálohování PgSQL)
• Vytvoří se znovu potřebné indexy
• V případě, že nenastala chyba, potvrdí se transakce, jinak se transakce zruší (a stav tabulky bude stejný, jako před prvním krokem)

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.

Verze pro tisk