LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> 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 18324×

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

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:

  • 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

pridej.cz

 

DISKUZE

Nejsou žádné diskuzní příspěvky u dané položky.



Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

4.10.2018 21:30 /Ondřej Čečák
LinuxDays 2018 již tento víkend, registrace je otevřená.
Přidat komentář

18.9.2018 23:30 /František Kučera
Zářijový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 20. 9. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář

9.9.2018 14:15 /Redakce Linuxsoft.cz
20.9.2018 proběhne v pražském Kongresovém centru Vavruška konference Mobilní řešení pro business. Návštěvníci si vyslechnou mimo jiné přednášky na témata: Nejdůležitější aktuální trendy v oblasti mobilních technologií, správa a zabezpečení mobilních zařízení ve firmách, jak mobilně přistupovat k informačnímu systému firmy, kdy se vyplatí používat odolná mobilní zařízení nebo jak zabezpečit mobilní komunikaci.
Přidat komentář

12.8.2018 16:58 /František Kučera
Srpnový pražský sraz spolku OpenAlt se koná ve čtvrtek – 16. 8. 2018 od 19:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát jsou tématem srazu databáze prezentaci svého projektu si pro nás připravil Standa Dzik. Dále bude prostor, abychom probrali nápady na využití IoT a sítě The Things Network, případně další témata.
Přidat komentář

16.7.2018 1:05 /František Kučera
Červencový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 7. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát bude přednáška na téma: automatizační nástroj Ansible, kterou si připravil Martin Vicián.
Přidat komentář

18.6.2018 0:43 /František Kučera
Červnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 21. 6. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát na téma: F-Droid, aneb svobodný software do vašeho mobilu. Kromě toho budou k vidění i vývojové desky HiFive1 se svobodným/otevřeným čipem RISC-V.
Přidat komentář

23.5.2018 20:55 /Ondřej Čečák
Od pátku 25.5. proběhne na Fakultě informačních technologií ČVUT v Praze openSUSE Conference. Můžete se těšit na spostu zajímavých přednášek, workshopů a také na Release Party nového openSUSE leap 15.0. V na stejném místě proběhne v sobotu 26.5. i seminář o bezpečnosti CryptoFest.
Přidat komentář

20.5.2018 17:45 /Redakce Linuxsoft.cz
Ve čtvrtek 31. května 2018 připravuje webový magazín BusinessIT ve spolupráci s Best Online Média s.r.o. pátý ročník odborné konference Firemní informační systémy 2018. Akce proběhne v kongresovém centru Vavruška (palác Charitas), Karlovo náměstí 5, Praha 2 (u metra Karlovo náměstí) od 9:00 hod. dopoledne do cca 15 hod. odpoledne. Konference je zaměřena na efektivní využití firemních informačních systémů a na to, jak plně využít jejich potenciál. Podrobnější informace na webových stránkách konfrence.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

5.10.2018 17:12 / Jakub Kuljovsky
Re: Jaký kurz a software by jste doporučili pro začínajcího kodéra?

20.9.2018 10:04 / Jan Ober
Jaký kurz a software by jste doporučili pro začínajcího kodéra?

20.9.2018 10:00 / Jan Ober
Re: Gimp

20.2.2018 18:48 / Ivan Majer
portal

20.2.2018 15:57 / Jan Havel
Jak využíváte služby cloudu v podnikání?

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2018) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze