Search
Pokročilé databázové systémy umožňují zefektivnit dotazy pomocí indexů a zjednodušit dotazování nad daty pomocí pohledů, použití uložených procedur. Zárověň pomocí triggerů (spouštěčů) umožňují ovlivňovat výsledky zapisovacíh operací (INSERT, UPDATE,DELETE), případně jejich prostřednictvím vyjádřit složitější integritní omezení (včetně minimálních a maximálních parcialit)
Pokud se při různých dotazech opakují spojování tabulek, podmínky či agregace; nebo je-li účelné zviditělnit některou (pod)sestavu, je možné tuto opakující se část nahradit pohledem. Pohled typicky koncovému uživateli umožňuje náhled na data bez použití denormalizace, zatímco data jsou bezpečně uložena v normovaném tvaru.
Pohled může být
Nematerializované pohledy se vytvářejí pomocí CREATE VIEW . Pro vytváření materializovaných pohledů lze standardně použít konstrukce CREATE TABLE AS a následně ji doplnit o volání triggerů nad zdrojovými tabulkami zajištující aktuálnost dat pohledu; některé databáze umožňují tento proces zautomatizovat pomocí konstrukce CREATE MATERIALIZED VIEW .
Ačkoliv do pohledu nelze zapisovat, lze tuto funkcionalitu doplnit pomocí tzv. pravidel, viz CREATE RULE. Některé databázové systémy tyto pravidla zavádějí defaultně při založení pohledu
Vytvořte sestavu pro vypsání položek na faktuře:
CREATE VIEW fakturalist AS SELECT id_faktura, zbozi.nazev, ks, fakturazbozi.cena AS cena_ks, fakturazbozi.sleva AS sleva, fakturazbozi.cena * fakturazbozi.ks * (CASE WHEN fakturazbozi.sleva IS NULL THEN 1 ELSE 1-fakturazbozi.sleva END) as cenacelkem FROM zbozi JOIN zbozifaktura USING (id_zbozi)
Indexací se rozumí rodina optimalizačních technik sloužících k urychlení vykonávání SQL dotazů. Nejprve si připomeňme, jak PostgreSQL vykonává SQL dotazy. Při vyhodnocení SELECT dotazu plánovač dotazů v PostgreSQL serveru sestaví tzv. query plan, tedy plán, jak se dotaz vykoná. Tento plán si můžeme prohlédnout pomocí klíčového slova EXPLAIN před příslušným dotazem, např. (viz. příklad databáze dále v tomto cvičení)
EXPLAIN ANALYZE SELECT count(*) FROM mytable;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1693.00..1693.01 rows=1 width=0) (actual time=251.654..251.655 rows=1 loops=1) -> Seq Scan on mytable (cost=0.00..1443.00 rows=100000 width=0) (actual time=0.025..127.282 rows=100000 loops=1) Total runtime: 251.721 ms
Vidíme zde, že se provádí agregace (Aggregate) na základě výsledků sekvenčního scanu tabulky mytable.
mytable
Plánovač dotazů se snaží optimalizovat vyhodnocení dotazu. Využívá pro to různých technik, např. odhadu počtu řádků v dané tabulce. Tyto odhady je třeba čas od času aktualizovat příkazem ANALYZE. Je však možné v konfiguraci PostgreSQL zapnout AUTOVACUUM démon, který se o to v pravidelných intervalech postará.
Malá vsuvka o tom, proč nejsou statistiky aktualizovány při každé změně dat v DB. Důvodem je to, že pri DELETE/UPDATE se musi zachovat historicke (puvodni) verze aktualizovanych/mazanych radku pro transakce, ktere si drive zamkli tyto radky pro cteni. Podobne 'mrtve radky' mohou vzniknout pri rollbacku transakce. K temto mrtvym radkum se neni mozne jiz dostat, je proto treba je smazat, aby nezabiraly misto (to se provede příkazem VACUUM). Tyto řádky však nejsou z výkonnostních důvodů mazány automaticky - zbytečně by to zabíralo čas.
V postgresql.conf je nastaveno “autovacuum off”, takže statistiky nejsou aktualizovány automaticky. Vytvoříme testovací tabulku. Vsimnete si, ze se automaticky vytvori index na primarni klic.
CREATE TABLE mytable( id serial PRIMARY KEY, value int );
INSERT INTO mytable(value) SELECT trunc(1000*random()) FROM generate_series(1,100000);
SELECT relname, reltuples FROM pg_class WHERE relname='mytable';
VACUUM ANALYZE;
EXPLAIN ANALYZE SELECT count(*) FROM mytable WHERE id=100;
Úkol 1: Srovnejte s dotazem, ktery filtruje podle sloupce, ktery nema index. Jaky typ scanu se pouziva a proc ? Srovnejte runtime casy v obou prikladech.<file>EXPLAIN ANALYZE SELECT count(*) FROM mytable WHERE value=100;</file>
Zkusime vytvorit index pomocí konstrukce CREATE INDEX:
CREATE INDEX i on mytable(value);
Úkol 2: Ověřte, jak se změní vyhodnocení předchozího SELECTu.
Lepší výsledek indexu je dán nejen indexem, ale i částečně cachováním výsledků předchozího dotazu (bez indexace) v PostgreSQL. Můžete si prohlédnout statistiky počty jednotlivých typů scanů/přístupů na disk v pg_stat_user_tables, resp. pg_stat_user_tables. Před každým takto testovaným dotazem je však třeba tyto statistiky vynulovat (může pouze administrátor databáze) pomocí
SELECT pg_stat_reset();
Úkol 3: Indexy mají výhodu pouze, má-li dotaz, který index využívá, vysokou selektivitu. V opačném případě může plánovač zvolit sekvenční scan navzdory existujícímu indexu. Abychom to ověřili, zkuste smazat všechna data z tabulky a vygenerovat data ještě jednou s většími množinami stejných hodnot (např. 10*random()). Pote naleznete hranici X filtru WHERE value < X v predchozim dotazu, pri kterem k prepnuti na sekvencni scan dojde. Porovnejte časy vyhodnocovaní dotazu v hranicnich pripadech.
10*random()
WHERE value < X
Unikátní omezení patří k nejčastějším. Lze je vytvořit v zásadě třemi způsoby: (i) automaticky vytvořením primárního klíče, (ii) klíčovým slovem UNIQUE za definicí atributu v CREATE TABLE, (iii) vytvoření indexu CREATE UNIQUE INDEX, který bude unikátnost kontrolovat.
UNIQUE
CREATE TABLE
CREATE UNIQUE INDEX
Úkol 4: Napadá vás, jaký je rozdíl mezi označením atributu UNIQUE a vytvořením unikátního indexu CREATE UNIQUE INDEX na tento atribut (zkuste si oba vytvořit) ? Který způsob je z hlediska údržby databáze výhodnější, a proč ?
Lze vytvářet i vícesloupcové indexy. Pokud vytvoříme např. index pro (id,value) v příkladě nahoře, výsledný index bude patrně menší než dva samostatné indexy na id a na value, ovšem jeho použitelnost bude pouze na dotazy, které buď používají filtr přes 'id', nebo přes id a value, ovšem nikoliv přes 'value' samostatně.
id
value
Úkol 5: Vyzkoušejte si, jak se liší doba vyhodnocení různých typů dotazů pro vícesloupcový index a pro více jednosloupcových indexů.
O indexech pojednávají například i články na rootu. Například Využití databázových indexů nebo Postgre Guide - Indexes. Tutoriál obdobný tomu, který je uveden výše lze najít ještě na tutorialspoin.com/postgresql_indexes.htm.
Uložené procedury, někdy označované jako databázové funkce, umožňují definovat složitější uživatelské výpočty. Různé databázové systémy podporují různé procedurální jazyky.
Vytvořte funkci pro exponenciální zapomínání.
CREATE OR REPLACE FUNCTION oblivion(timestamp with time zone) RETURNS float AS $_$ SELECT exp((extract(epoch from $1) - extract (epoch from now()))/(3600*24*2)); $_$ LANGUAGE 'sql';
Všimněte si, že je bezpodíněčně nutné, aby SELECT vrátil pouze jednu hodnotu (identicky se vnořeným SELECTem na pozici hodnot). U uložených procedur je navíc kontrolována shoda mezi deklarovaným a vráceným typem.
Vytvořte funkci nahrazující funkcionalitu jednoatributového textového primárního klíče (t.j. funkci, která převede textový primární klíč na int)
CREATE OR REPLACE FUNCTION indexzbozi(varchar) RETURNS int AS $_$ DECLARE ret int; BEGIN ret = (SELECT id_zbozi FROM zbozi WHERE nazev=$1); IF ret IS NULL THEN ret=nextval('seq_zbozi'); INSERT INTO zbozi(id_zbozi,nazev) VALUES (ret,$1); RAISE NOTICE 'indexzbozi (%) - nove id=%',$1, ret; ELSE RAISE NOTICE 'indexzbozi (%) = %',$1,ret ; END IF; RETURN ret; END; $_$ LANGUAGE 'plpgsql' ;
Použití:
SELECT indexzbozi('ponozky');
Jazyk plpgsql umožňuje zejména větvení, cykly i obsluhu vyjímek.
Uložené procedury nemusejí vracet pouze jednu hodnotu, ale i celou tabulku.
Vytvořte tabulkovou uloženou proceduru na výtisk faktury.
CREATE OR REPLACE FUNCTION polozkyfaktury(int) RETURNS set of fakturalist AS $_$ SELECT * FROM fakturalist WHERE id_faktura = $1; $_$ LANGUAGE 'sql';
SELECT * FROM polozkyfaktury(10) AS seznam;
Pokud vrácený výsledek neodpovídá žádnému existujícímu typu, pohledu či tabulce, je možné použít generický typ RECORD. Při dotazování je však nezbytně v SELECTu mnutné provést výčet atributů včetně jejich typů.
Se zapisovacími operacemi lze svázat volání uložených procedur pomocí triggerů.
Trigger může být volán
Pokud obslužná procedura triggeru před voláním operace vrátí NULL, operace je zrušena (t.j. neprovede se). Podobně obslužná procedura může vyvolat vyjímku - opět dochází k přerušení operace.
Příklad:
CREATE OR REPLACE FUNCTION trg_odectizeskladuzbozi() RETURNS TRIGGER AS $_$ BEGIN UPDATE sklad SET ks = ks - NEW.ks WHERE id_zbozi = NEW.id_zbozi; RETURN NEW; END; $_$ LANGUAGE 'plpgsql' CREATE TRIGGER trg_odectizeskladuzbozi BEFORE INSERT TO fakturazbozi FOR EACH ROW EXECUTE PROCEDURE trg_odectizeskladuzbozi;
Dalsi priklad triggeru realizujici slozitejsi integritni omezeni - viz http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html