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

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ů

14.11.2017 16:56 /František Kučera

Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt – tradičně první čtvrtek před třetím pátkem v měsíci: 16. listopadu od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).


Přidat komentář

12.11.2017 11:06 /Redakce Linuxsoft.cz
PR: 4. ročník odborné IT konference na téma Datová centra pro business proběhne již ve čtvrtek 23. listopadu 2017 v konferenčním centru Vavruška, v paláci Charitas, Karlovo náměstí 5, Praha 2 (u metra Karlovo náměstí) od 9:00. Konference o návrhu, budování, správě a efektivním využívání datových center nabídne odpovědi na aktuální a často řešené otázky, např Jaké jsou aktuální trendy v oblasti datových center a jak je využít pro vlastní prospěch? Jak zajistit pro firmu či jinou organizaci odpovídající služby datových center? Podle jakých kritérií vybrat dodavatele služeb? Jak volit součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně spravovat datové centrum? Jak eliminovat možná rizika? apod.
Přidat komentář

13.9.2017 8:00 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt – tentokrát netradičně v pondělí: 18. září od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

3.9.2017 20:45 /Redakce Linuxsoft.cz
PR: Dne 21. září 2017 proběhne v Praze konference "Mobilní řešení pro business". Hlavní tématy konference budou: nejnovější trendy v oblasti mobilních řešení pro firmy, efektivní využití mobilních zařízení, bezpečnostní rizika a řešení pro jejich omezení, správa mobilních zařízení ve firmách a další.
Přidat komentář

15.5.2017 23:50 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 18. května od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

12.5.2017 16:42 /Honza Javorek
PyCon CZ, česká konference o programovacím jazyce Python, se po dvou úspěšných ročnících v Brně bude letos konat v Praze, a to 8. až 10. června. Na konferenci letos zavítá např. i Armin Ronacher, známý především jako autor frameworku Flask, šablon Jinja2/Twig, a dalších projektů. Těšit se můžete na přednášky o datové analytice, tvorbě webu, testování, tvorbě API, učení a mentorování programování, přednášky o rozvoji komunity, o použití Pythonu ve vědě nebo k ovládání nejrůznějších zařízení (MicroPython). Na vlastní prsty si můžete na workshopech vyzkoušet postavit Pythonem ovládaného robota, naučit se učit šestileté děti programovat, efektivně testovat nebo si v Pythonu pohrát s kartografickým materiálem. Kupujte lístky, dokud jsou.
Přidat komentář

2.5.2017 9:20 /Eva Rázgová
Putovní konference československé Drupal komunity "DrupalCamp Československo" se tentokrát koná 27. 5.2017 na VUT FIT v Brně. Můžete načerpat a vyměnit si zkušenosti z oblasti Drupalu 7 a 8, UX, SEO, managementu týmového vývoje, využití Dockeru pro Drupal a dalších. Vítáni jsou nováčci i experti. Akci pořádají Slovenská Drupal Asociácia a česká Asociace pro Drupal. Registrace na webu .
Přidat komentář

1.5.2017 20:31 /Pavel `Goldenfish' Kysilka
PR: 25.5.2017 proběhne v Praze konference na téma Firemní informační systémy. Hlavními tématy jsou: Informační systémy s vlastní inteligencí, efektivní práce s dokumenty, mobilní přístup k datům nebo využívání cloudu.
Přidat komentář

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

> Poslední diskuze

18.9.2017 14:37 / Rojas
high security vault

15.9.2017 7:33 / Wilson
new zealand childcare jobs

31.8.2017 12:11 / Jaromir Obr
Re: ukůládání dat ze souboru

30.7.2017 11:12 / Jaromir Obr
Národní znaky

27.7.2017 12:24 / Jaromir Obr
Cteni/zapis

Více ...

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