Hlavní navigace

Na co si dát pozor při návrhu databáze?

Pavel Stěhule 16. 11. 2016

Při monitorování systému se snažíme o detekci úzkých hrdel. U systémů s extrémními problémy bez extrémní zátěže jsou problémy většinou už v samotném návrhu databáze a v návrhu dotazů.

Pokud je problém v software, musí být primárně v software také opraven. Krátkodobě si lze pomoci silnějším hardware (více paměti, SSD disky, apod), tím se ovšem problém nevyřeší – pouze se 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ů: za prvé zatemňuje návrh schématu (hůře se píší dotazy), za druhé 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 hardware 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žena 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ískali 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 UPDATEINSERT. 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,  č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říkazEXPLAIN ( 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 sloupcem 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.

Našli jste v článku chybu?

16. 11. 2016 9:45

Úprava schématu je drahá - pokud se jednou schéma navrhne špatně, tak je to nadlouho. A přitom si stačí pamatovat a respektovat pár jednoduchých doporučení.
Bohužel velká většina, téměř všechny problémy s databázemi jsou způsobené nějakou banalitou. Živím se jako databázový konzultant, a člověk si už kolikrát připadá jak v jednom nonstop dejavu. Opravdu originálních problémů je promile - ale pak to stojí za to (low level problémy v zámcích kernelu nebo Postgresu). Téměř všechno, s čím se obyčejn…

16. 11. 2016 7:27

pb. (neregistrovaný)

Předpokládám, že ten příklad se zaměstnanci byl uvedený opravdu jen jako jednoduchý, snadno pochopitelný příklad. Nečekal bych, že to někdo vezme tak doslova.

Taky už jsem dělal databázi, u které jsem si říkal, že tam bude jen pár tisícovek záznamů, protože přece i ty největší organizace v našich končinách nemají více než 5000 zaměsnanců, ale pak se z toho vyvinula evidence obyvatel na evropské úrovni (neberte mě zase TAK doslovně - nebyla to evidence lidí).

K funkcím "nad stĺpcami":

Lupa.cz: Kdo pochopí vtip, může jít do ČT vyvíjet weby

Kdo pochopí vtip, může jít do ČT vyvíjet weby

Měšec.cz: Zdravotní a sociální pojištění 2017: Připlatíte

Zdravotní a sociální pojištění 2017: Připlatíte

Měšec.cz: Kdy vám stát dá na stěhování 50 000 Kč?

Kdy vám stát dá na stěhování 50 000 Kč?

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Měšec.cz: Nenechte se ošidit, když vám staví dům

Nenechte se ošidit, když vám staví dům

Podnikatel.cz: 1. den EET? Problémy s pokladnami

1. den EET? Problémy s pokladnami

Lupa.cz: Propustili je z Avastu, už po nich sahá ESET

Propustili je z Avastu, už po nich sahá ESET

Vitalia.cz: Jsou čajové sáčky toxické?

Jsou čajové sáčky toxické?

DigiZone.cz: ČRa DVB-T2 ověřeno: Hisense a Sencor

ČRa DVB-T2 ověřeno: Hisense a Sencor

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

Vitalia.cz: Znáte „černý detox“? Ani to nezkoušejte

Znáte „černý detox“? Ani to nezkoušejte

Měšec.cz: Golfové pojištění: kde si jej můžete sjednat?

Golfové pojištění: kde si jej můžete sjednat?

DigiZone.cz: Flix TV má set-top box s HEVC

Flix TV má set-top box s HEVC

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

Lupa.cz: Teletext je „internetem hipsterů“

Teletext je „internetem hipsterů“

Vitalia.cz: Dáte si jahody s plísní?

Dáte si jahody s plísní?

Podnikatel.cz: Babiše přesvědčila 89letá podnikatelka?!

Babiše přesvědčila 89letá podnikatelka?!

Vitalia.cz: Mondelez stahuje rizikovou čokoládu Milka

Mondelez stahuje rizikovou čokoládu Milka

DigiZone.cz: Rádio Šlágr má licenci pro digi vysílání

Rádio Šlágr má licenci pro digi vysílání

Lupa.cz: Proč firmy málo chrání data? Chovají se logicky

Proč firmy málo chrání data? Chovají se logicky