Chyby při návrhu relační databáze

Autor: Pavel Stěhule

Při monitorování systému se snažíme o detekci úzkých hrdel, které mohou být jak v hw, tak v sw. U těch systémů s extrémními problémy bez extrémní zátěže (300-400 připojených uživatelů by na dnešním hw nemělo pro db představovat větší zátěž ), jsou problémy většinou v návrhu databáze a v návrhu dotazů. Je proto dobré začít s popisem nejčastějších a nejhorších problémů.

Pokud je problém v sw, tak primárně musí být v sw také opraven. Krátkodobě si lze pomoci silnějším hw (více paměti, SSD disky, apod), tím se ovšem problém nevyřeší, pouze odsouvá.

Aktuálně největší problém vývoje databází je fakt, že minimum vývojářů má realistickou představu o tom, jak mohou být databáze rychlé. Někdy se přehlížejí závažné problémy s databází, jindy mají vývojáři naprosto nereálná očekávání.

Jak nenavrhovat schéma relační databáze

Při návrhu databáze musíme mít na paměti, pro kolik uživatelů databázi navrhujeme a jak bude databáze velká. Pokud máme málo uživatelů, málo dat (do několika málo miliónů řádků), tak nám realita hodně chyb promine. Pokud máme mnoho dat, mnoho uživatelů, tak pak chyby v návrhu většinou způsobují obtížné provozní problémy a většinou i problémy při údržbě a dalším rozšiřování software.

Antipattern - pokusy o dědičnost

Relační databáze nijak nepodporují dědičnost (Postgres, je výjimka potvrzující pravidlo). Pokusy o implementaci dědičnosti způsobem, kdy každé třídě odpovídá tabulka, vedou k velkému množství tabulek, potažmo k dotazům s velkým množstvím JOINů (musím je udělat abych získal ucelenou entitu (objekt)).

-- špatně!
CREATE TABLE clovek(
  id integer PRIMARY KEY,
  jmeno text,
  prijmeni text
)

CREATE TABLE zamestnanec(
  id integer PRIMARY KEY,
  zarazeni integer REFERENCES ciselnikX(id),
  mzda numeric
)

CREATE TABLE student(
  id integer PRIMARY KEY,
  studijni_obor integer REFERENCES ciselnikY(id),
  rocnik integer
)

Co je špatného na velkém množství JOINů (na zbytečném JOINování)? Každý JOIN znamená další chybu v odhadu výsledku - u velkého množství JOINů jsou odhady zatížené tak velkou chybou, že se skoro nedá pak mluvit o optimálním prováděcím plánu.

Nehezkým fixem je denormalizace - což je vlastně pokus o neúplný model bez dědičnosti - jde se několik kroků zpátky. Denormalizace je špatná z několika důvodů: 1) zatemňuje návrh schématu (hůře se píší dotazy), 2) aktualizace dat je výrazně náročnější - musí se modifikovat více tabulek, je náročnější zajistit konzistenci.

Pokud se používá relační databáze a očekává se větší zátěž - pak je základem zapomenout při návrhu schématu na OOP. Mám entity - ty by pak měly odpovídat tabulkám. Zde není plán B, žádná možnost, jak efekt chybně navrženého schématu opravit - jedině to utlouct hw, a smířit se s horším výkonem (a náročnější údržbou) systému.

Dědičnost v Postgresu nepoužívejte - sice netrpí problémy s JOINy - na rozdíl od výše uvedené ukázky dochází k propagaci atributů předka k potomkům, takže tam odpadají JOINy. Závažný problém je jinde - pro objektový model není připravena kontrola referenční integrity - tu by si člověk musel napsat sám.

Uznávám, že OOP (zde ORM) pomůže při návrhu aplikace. Pro relativně malá data je cena za ORM akceptovatelná. U větších databází (desítky, stovky GB) může objektový návrh působit neuvěřitelné (a hlavně neopravitelné, neřešitelné) problémy.

Antipattern - pokus o univerzální model (EAV)

Entity-Attribute-Values je dalším oblíbeným vzorem vývojářů, který má ale naprosto fatální důsledky na výkon databáze. Relační databáze předpokládá, že jsou data uloženy v normalizovaném schématu. Každý sloupeček nese jeden atribut, který má jednoznačný význam. EAV tento předpoklad staví na hlavu. Jeden sloupeček může obsahovat všechny atributy (v EAV schématu). S EAV, abychom získaly entitu, musíme udělat několik self JOINů - tolik kolik má entita atributů. Jakmile se dostanete za hranici HashJoinu (vypadnete z RAM), tak rychlost načítání entit z databáze dramaticky poklesne.

--špatně!
CRATE TABLE data(
  id_objektu SERIAL PRIMARY KEY,
  id_vlastnosti integer,
  hodnota text
)

V ideálním světě mám všechny data uložené v normalizované databázi. V praxi by to ale mohlo vést k řídké databázi (tabulkám, kde většina obsahu budou hodnoty NULL). Databázi je to jedno, ale špatně se s takovou databází pracuje. Je dobré nalézt kompromis. Atributy, které jsou univerzální např (hmotnost, výška, šířka, hloubka, barva) ukládáme normalizovaně. U ostatních atributů si můžeme pomoci strukturovanými hodnotami (XML, JSON, HStore) nebo přinejhorším EAV.

-- dobře
CREATE TABLE produkty(
  id_objektu SERIAL PRIMARY KEY,
  nazev text,
  hmotnost numeric,
  barva_rgb text,
  x int,
  y int,
  z int,
  ostatni jsonb
);

Nevhodné použití typů pro primární klíče

Pokud je primárním klíčem 128 znakový string, tak nemůžete čekat žádnou skvělou rychlost JOINů. Navíc tyto klíče zaberou hodně místa RAM - nevejdete se do work_mem.

Umístění často modifikovaného atributu do tabulky s důležitým primárním klíčem

Většinu zámků v relační databázi generuje referenční integrita. Pokud často modifikujete tabulky, které obsahují důležité primární klíče (např. id uživatele), pak se množství zámků zvyšuje. S větší zátěží může docházet k deadlockům, k dlouhým čekáním na zámky, atd. Je proto praktické často měněné atributy z takových tabulek přesunout do speciálně vytvořené tabulky (většinou stejně takový atribut neodpovídá realitě, a je to jen zkratka vývojáře). Např. v tabulce uživatelů by neměla být hodnota "čas poslední akce".

Chybějící indexy, nebo příliš mnoho indexů

Chybějící indexy - to je taková klasika. Ještě jsem nezažil aplikaci, kde by nějaký index nechyběl. Co mne zaráží je neaktivita vývojářů - přidat index je práce na deset minut, a zrychlení aplikace může být neuvěřitelné. Díky vyšší rychlosti mohou zmizet provozní problémy (např. vyčerpávání spojení do databáze, atd).

Je důležité aplikaci dostat, co nejrychleji, do produkce ve stavu, kdy nekolabuje a je použitelná. Při startu aplikace intenzivně sleduji pomalé dotazy, časté dotazy, zkontroluji je a případné chybějící indexy přidám. Je důležité po pár měsících tuto proceduru zopakovat, aby indexy vždy odpovídaly velikosti databáze, provozu, používání. Indexy se často dávají na sloupečky cizích klíčů, a důležitých atributů s dobrou selektivitou (název, čas vložení, referenční datum, atd). Na primárních klíčích jsou implicitně. Není ostuda zapomenout index. Je ale naprosto ostudné provozovat databázi a chybějící indexy nevytvořit.

Tak jak indexy zrychlují získávání dat, tak zpomalují změny dat - údržba indexu je jedna z nejnáročnějších operací v databázi. Příliš mnoho indexů způsobuje pomalé příkazy UPDATE, INSERT. Nepoužívané indexy smažte. Statistiku použití indexů najdete v Postgresu v tabulce pg_stat_user_indexes.

Abych věděl, kam mám umístit index, potřebuji zjistit pomalé a časté dotazy. Z možností, které mám v Postgresu, mi pořád nejvíc vyhovuje PgFouine - jednoduchá, jednoúčelová aplikace, která ze zalogovaných dotazů vytvoří report s nejpomalejšími, nejčastějšími, a nejnáročnějšími (čas x četnost) dotazy. Pokud provozujete relační kritičtější databázi, tak čtení takového reportu by měl být Váš denní chleba. Samozřejmě, že ze samotného dotazu nic moc nezjistím - musím umět číst prováděcí plány - viz příkaz " EXPLAIN (EXPLAIN ANALYZE).

Při troše znalostí (těch nutných znalostí opravdu není mnoho) zjistíte, že relační databáze mohou být fantasticky rychlé, a že používat relační databáze je hrozně jednoduché.

Jak nepsat dotazy

Nepoužívání čistých predikátů

Pro psaní dotazů je dobré znát termín čistý predikát a používat jej. Čistý predikát je predikát ve formátu

  atribut OP konstanta nebo výraz (pro výrazy platí určitá omezení)

tj jmeno = 'Pavel' nebo jmeno = upper('Pavel') jsou čisté predikáty. Naopak upper(jmeno) = 'PAVEL' už čistým predikátem není. Pro většinu relačních databází platí, že se indexy použijí jen tehdy, pokud se podmínka zapsala jako čistý predikát (nebo kombinace čistých predikátů). Můžeme si pomoci funkcionálními indexy, ale to už jsou indexy navíc (víc času budeme pálit na aktualizaci indexů).

Zkušenější programátoři už znají vzory pro různé situace a napíší je efektivně. Např. pro filtrování dat z listopadu 2016 mohu napsat několik různých forem predikátů:

1. extrémně špatné řešení citlivé na konfiguraci (pokud se nějak změní datestyle tak přestane fungovat)

   WHERE substring(d::text FROM 1 FOR 7) = '2016-11'
   

2. spatné řešení (není čistým predikátem, používají se konverze do stringu a stringové operace

   WHERE to_char(d, 'YYYYMM') = '201611'
   

3. ne úplně ideální řešení - není čistým predikátem, pracuji s více funkcemi

   WHERE EXTRACT(month FROM d) = 11 AND EXTRACT(year FROM d) = 2016
   

4. ideální řešení

   WHERE d >= '2016-11-01' AND d < '2016-12-01'
   

Jiný příklad - výběr záznamů vložených předchozích den:

vloženo + 1 = CURRENT_DATE -- špatně!

není čistým predikátem, a index se nepoužije. Naopak

vloženo = CURRENT_DATE - 1

je čistým predikátem a index nad sloucem vloženo se použije.

S málo daty, s málo uživateli mohu relační databázi používat téměř bez jakýchkoliv znalostí. U větších dat, u více uživatelů je nezbytné minimum znalostí dost zásadní. Relační databáze fungují spolehlivě, jednoduše, když víte co děláte. Vyplatí se do sebe zainvestovat den, dva, aby si člověk přečetl základy, a aby pro něj databáze nebyla magická černá skříňka.