Dotaz: Návrh databáze - xml nebo ohýbání struktury?

[dbm]

Ahoj. Mám databázi kterou využívám pro "kešování" dat. Mám centrální tabulku s uživateli. Dále aplikaci, která pravidelně čte předhozené textové soubory (různé systémy uživatelských účtů) a podle potřeby vytvoří tabulku s potřebnými datovými typy a importuje do ní data. Pak mám další tabulku, ve které mám seznam všech takto vytvořených tabulek. Je to takový jednoduchý systém který má za úkol jediné - rychle najít uživatele.

centrální tabulka:

uzivatel | kategorie
--------------------
jarda    | kat.
...


tabulka názvů tabulek:

tabulka_nazev
-------------
soubor1
...


příklad vstupního souboru:

uzivatel(string), kvota_limit(int32), certifikat(bin)
jarda2, 100, BINARNI_DATA

takový soubor se převede do tabulky "nazev_souboru":
uzivatel | kvota_limit | certifikat
-----------------------------------
jarda2   | 100         | BINARNI_DATA

s tím, že se konvertují datové typy (string na varchar, int32 na int, bin na blob)



Při výpisu uživatelů pak není problém zobrazit všechny lidi, který mají kvota_limit XY  a jsou v kategorii KATEGORIE. Vlastně je to velice rychlé, protože se provede jednoduchá podmínka where na left joinech tabulek.

Teď co mám za problém. Ačkoliv je tento návrh rychlý a efektivní, tak se mi vůbec nelíbí zasahovat dynamicky(i když jen občas cca 1x ročně) do struktury db. Proto hledám jiný způsob ukládání dat. Napadlo mě přidat centrální tabulce sloupec a všechno mít v XML struktuře. Jak je na tom třeba mysql či postgresql s hledáním v takové struktuře? Nebo neměli byste lepší nápad na návrh databáze?

Odpovědi

[Kit]

To vypadá, jako bys hledal fulltext. Také záleží na tom, kolik řádek máš v databázi. Jestli je to jen pár tisíc, tak databázi nezatíží ani sekvenční hledání s LIKE. Ovšem hledání jména uživatele v XML bude asi náročnější, než třeba v CSV. XML však můžeš s úspěchem využít k ukládání neklíčových položek.

[dbm]

Myslel jsem to takhle:

uzivatel | kategorie | atributy
------------------------------------
jarda    | kat.      | XML_STRUKTURA

Těch řádek je tam teď cca 500 000 v každé spojované tabulce a přibývají další (jsou to takové speciální fiktivní uživatelské účty). V současnosti jsem schopen po spojení 5 takových tabulek vyhledat data (samozřejmě indexovaná) do cca 2 sekund. S manuálním vyhledáváním (pomocí aplikace) v XML by to trvalo déle, proto mě zajímá něco co přímo podporuje databáze, ideální by bylo kdyby zvládla hledat v XML, což ale asi nepůjde? Jakou jinou strukturu bych mohl použít pro ukládání dat, aby hledání bylo stejně rychlé jako doposud?

[Kit]

Zásadní otázka: Podle čeho hledáš? Pokud podle libovolného slova, tak ti pomůže jen fulltext.

Mám podobnou databázi v obyčejném textovém souboru a prohledávám ji sekvenčně pomocí regulárního výrazu. Skvělá rychlost, maximální spokojenost. Pokud budeš jakkoli sekvenčně prohledávat databázi, degraduješ ji do pozice pomalejšího textového souboru. To už je lepší všechny možné klíče naházet do key->value store.

Klidně používej XML, ale klíče je vhodné uložit tak, aby se daly indexovat. Hledání klíčů v XML není dobrý nápad.

Jaké máš dotazy do své databáze? Možná je řešení jednoduché až primitivní. Jen je potřeba vědět, co vlastně hledáš, které položky jsou klíčové.

[dbm]

Vyhledávám podle všech položek (klidně najednou). Tudíž všechny sloupce jsou vlastně klíčové (i v reálu je to všechno přeindexované). Dotazy jsou typu select a jde o to většinou najít všechny uživatele, kteří jsou v nějaké kategorii, jejich jméno začíná nějakým písmenem a jejichž atribut soubor1.atribut1 je menší než 50 a jejichž soubor2.atributX se rovná "řetězec". Na takové věci by mi fulltext asi nepomohl. Tímto způsobem mohu i řadit podle různých datových typů (datetime, int, varchar ...). Na tom mém návrhu mi vadí ještě to, že jsou některé záznamy redundantní - z důvodu rychlosti při spojování je lepší spojit 5 tabulek s redundancí než 50 tabulek v 3NF bez redundance.

[l0gik]

A jaké jsou XML data, která se tam ukládají? Je to XML víceúrovňové, nebo ploché (každý uživatel má X vlastností)? A podle čeho se vyhledává?

Nestačilo by navrhnout pevné tabulky pro data, podle kterých se vyhledává a kromě toho jednu tabulku s údaji uživatel - název vlastnosti - hodnota vlastnosti?

[dbm]

Zatím žádná, ale pokud bych použil XML, tak asi použiji přímo XSD. Stromově by struktura vypadala následovně:

         soubor1
            |
   ------------------------
   |                      |
atribut1               atributN
   |                   |       |
hodnota1           hodnota1 hodnota2

Takže by to bylo víceúrovňové, uživatel má vždy N vlastností (atributů) a každá vlastnost může mít několik hodnot.

[Ladicek]

Já bych se na to vykašlal, namísto XML bych generoval JSON a ten bych indexoval v Elasticu. Vyhledávání jedna báseň.

[dbm]

Umí to i wildcard znaky? A jak to vlastně funguje? Z jejich popisu mi to není moc jasné - jasně je to search engine, ale nad čím to vyhledává - co to používá jako default storage (persistentní/nepersistentí ...)?

[Kit]

Připadá mi to jako variace na CouchDB.

Při shlédnutí vyhledávacích požadavků mě napadlo jednoduché řešení: Textový soubor, každý záznam jeden řádek, struktura řádku JSON. Žádné indexy, vyhledávání grepem. Možná to bude i rychlejší.

Klidně by to mohlo být i v několika souborech, každý s jinou strukturou.

[dbm]

No relační databáze mi umožňuje použít transakce. Během aktualizace záznamů může bez problému probíhat několik selectů. Při selhání aktualizace se jen zruší transakce a data jsou v pořádku. Toho s textovým souborem nedosáhnu (ani s couchdb myslím ne - tam i přes verzování není zaručena "správnost dat").

[Kit]

Netušil jsem, do jaké míry vyžaduješ ACID. Také jsi to nikde nepsal a požadavky na hledání spíš svědčily o opaku. Podle popisu hledání mám spíš pocit, že ve tvém případě indexy místo pomáhání spíš škodí. Proto jsem navrhl jejich odstranění, případně redukci.

Při zvyšování výkonnostních nároků na databázi se často sáhne po NoSQL řešení, do kterého patří i XML a JSON. Ovšem nesmí se zapomínat na indexy, protože prohledávání neindexovaných objektů je drahé.

Také bys mohl mít jednotlivé tabulky jako JSON objekty. Aktualizoval bys pak celou tabulku naráz. Pokud vyhledáváš výrazně častěji než aktualizuješ, mohlo by to být řešení. Prohledání 50 objektů regulárním výrazem je rychlejší, než prohledávání 500000 malých neindexovaných objektů. Zejména pokud víš, ve kterém z těch 50 objektů se požadovaná data nachází.

[dbm]

No jediná NoSQL databáze která by se mi líbila je MongoDB, ale na ní jsem se dočetl o možnosti ztráty dat a o memory leaks, protože je to relativně mladá db, tak nevím.

[Kit]

NoSQL je víc druhů, MongoDB se hodí tam, kde potřebuji škálovat. A když už budu potřebovat škálovat, podívám se nejprve, jak to dělá Cassandra.

Na popsaný problém by se dala klidně použít třeba i DB4, ale nesměl bys být líný si v ní vytvořit všechny potřebné indexy ve vlastní režii.

[dbm]

No upřímně řečeno jak procházím různé nosql db, říkám si, že by bylo moudřejší zůstat u SQL databáze a vyřešit to nějak tam.

[univerz]

od cca 1.8 je single server durability defaultne nastavenie. zatial mi bezi cca 2 mesiace v pohode bez vyrazneho zrania pamate.

[Ondra]

Napadá mě DB2 v9.7, kde je technologie PureXML, která umožňuje uchovávat nativně XML dokument v databázi, takže je to přesně to co chceš:

uzivatel | kategorie | atributy
------------------------------------
jarda    | kat.      | XML_STRUKTURA

Dá se s tím pracovat jak pomocí SQL, tak pomocí XQuery pro XML strukturu. IBM DB2 v9.7 Express-C je na stránkách IBM volně stažitelná a použitelná i pro komerční účely. Jediné omezení jsou tam 2 CPU a 2 GB RAM. Velikost databáze omezená není.

Pokud ale vyžaduješ MySQL nebo Postgres, tak: http://wiki.postgresql.org/wiki/XML_Support - taky koukám, že umí XQuery http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html - tam je to o něco horší

[dad]

k te DB2.

Jak se projevuje to omezeni? Jestlize mam stroj s vice nez 2 GB RAM, vubec se ta db nenainstaluje, nebo nenastartuje, nebo co se stane?

2 CPU jsou jadra nebo 2 sockety?

[kovariadam]

normalne to pojde, len viac nevyuzije

[l0gik]

No, zkušenosti nemám, ale zdravej rozum mi říká, že XQuery nikdy nedosáhne rychlosti vyhledávání v db tabulkách. Jestli jsem to pochopil, tak struktura xml je víceméně vždy stejná. Pak to jde dát do vhodných tabulek. Např.

Skupina 1:N   Uživatel    1:N Hodnota atributu (Int)      N:1 Název atributu
                          1:N Hodnota atributu (Varchar)  N:1
                          1:N Hodnota atributu (Datetime) N:1
                              ....

Vyhlednávání rychlé, indexované, struktura univerzální, ACID. Je něco, co to neumí?

PS: Pokud se něco dělá podle prvního písmene skupiny, je název skupiny zřejmě složený datový typ, takže bych ho rozdělil.

[dbm]

Je něco, co to neumí?

Třeba řazení např. pokud budu chtít řadit podle atributu1 vzestupně a zároveň podle atributu2 sestupně (SQL = order by atribut1, atribut2 desc). Také to neumí datové typy (leda že bych měl tolik sloupců kolik existuje datových typů a hodnotu sypal do toho správného).

[l0gik]

Neumí ?

SELECT * uzivatel  u 
INNER JOIN atribut a1 ON (u.id = a1.uzivatel_id AND a1.nazev_id = :nazev1)
INNER JOIN atribut a2 ON (u.id = a2.uzivatel_id AND a2.nazev_id = :nazev1)
ORDER BY a1.hodnota, a2.hodnota desc

Jo, nenadefinuješ složený index, ale jednoduchej index atribut(nazev_id, hodnota) to použije, tak pokud není první řazení podle nějakýho hodně neselektivního kritéria, tak to je dostatečný.

Na ty nejpoužívanější pak můžeš použít materializovanej view, popř. si nejpoužívanější vlastnosti sypat rovnou k uživateli. Furt je to rychlejší než cokoli, co dostaneš přes XPath nebo XQuery.

Různý typy můžeš řešit více tabulkama či více sloupci, těch typů zas tolik není (int, float, datum, string).

[dbm]

Vyzkouším to, ale nejsem si jistý jestli to má šanci fungovat (kvůli náročnosti). Takhle vlastně místo toho, abych měl 5 tabulek po 500 000 řádcích a třeba 10 sloupcích budu mít 1 tabulku s [5 * 500 000 * 10] řádky, nad kterou budu provádět joiny nad sebou samé.

[l0gik]

No můžeš nechat současnou tabulku a do tědlech "metatabulek dávat jen ty" přidávaný data.

[Ondra]

Ano i tak by se dalo, pokud se chceme držet ploché struktury. Otázka je, jak má postavenou aplikaci, která s těma datama pracuje. To by měl při tom návrhu zohlednit, pokud nedělá všechno na zelené louce.

BTW. PureXML je dost rychlé. Nebál bych se toho. http://www-01.ibm.com/software/data/db2/xml/performance.html

[l0gik]

Já věřím, že je dosti rychlé. Furt ale musí zvládat obecnější množinu dat, takže nemůže bejt tak optimalizovanej jako dotaz nad relační algebrou. Fór je v tom, že tadle aplikace defakto nepotřebuje nijak velkou varibailitu: akorát občas má tu a tam ten a ten objekt o vlastnost víc či míň. Síla xml se projeví v případě, kdy o datech moc nevíme a tak nemůžeme připravit nějakej obecnej mustr. To ale myslím není tento případ...

[dbm]

Ještě k tomu sql dotazu na řazení. Musel jsem sice použít left joiny (protože nechci ztratit řádky uživatelů jen proto, že nemají ty a ty atributy), ale nevypadá to vůbec špatně. Rychlostně je to +- nastejno s mým stávájícím řešením, až jsem se divil. Ještě je ale potřeba vyřešit ty problémy s datovými typy. Když mám tabulku se sloupci hodnotaINT, hodnotaDATE, hodnotaVARCHAR atd., tak se použije vždy jen jeden, ostatní budou NULL. Jak vyberu ale ten správný sloupec? Nechce se mi to řešit aplikačně, lze toto řešit i na databázové úrovni (tak, že pro každý nalezený řádek vypíše z hodnota1-hodnotaN vždy tu, která není NULL)?

[rADOn]

Nechce se mi to řešit aplikačně, lze toto řešit i na databázové úrovni (tak, že pro každý nalezený řádek vypíše z hodnota1-hodnotaN vždy tu, která není NULL)?

Hledáš sql funkci COAELSCE

[rADOn]

Ups, chtel jsem říci COALESCE

[l0gik]

COALESCE by IMHO bylo zbytečně pomalý - navíc: jak poznáš, podle kterýho typu řadit jako první?

Osobně myslím, že budeš muset někde určit, jakej typ mají daný data. Spoléhat na autodetekci se mi nezdá úplně dobrej nápad. V tu chvíli je podle mě nejlepší nespoléhat na automatiku, ale prostě si udělat konfigurák, kam předepíšeš tendle sloupec je INTEGER, tendle je DATETIME atd.... Sloupce, který v konfiguráku nebudou se pak budou porovnávat jako řetězce.

Výhoda toho je, že pokud budeš potřebovat nějaký spešl řazení, který DB nativně nenabízí, tak se to velmi snadno doimplementuje apod. Jako bonus tam i můžeš snadno implementovat sloupce, který budou přímo v tabulce uživatele a podobný vychytávky. Další výhoda bude jednoduchá "změna typu" danýho sloupce atd...

[rADOn]

COALESCE by IMHO bylo zbytečně pomalý

Pomalejší než index? Určitě. Pomalejší než nějaká hrůza zahrnující xml a fultext? Nevím nevím.

[l0gik]

To asi jo, no ona jde naindexovat i expression, o to nejde. Takže zas o tolik pomalejší by to ani nebylo (akorát by byly větší indexy). Ale stejně z principu nejde určit, pro jakou vlastnost se má použít jakej sloupec, takže to musí určit programátor (přinejmenším pro zápis). A když to určuje pro zápis, tak proč by to neurčil i pro čtení?

[rADOn]

A když to určuje pro zápis, tak proč by to neurčil i pro čtení?

To já nevím, já jen odpověděl na dotaz jak najít nenullový sloupec. Ale v původním dotazu nešlo o to jak překopávání schématu přizpůsobit aplikaci, ale jak se strojovému překopávání tabulek vyhnout úplně. Udělat ze sloupců vazbu řádky je IMO celkem dobrý způsob jak to udělat, rozhodně lepší než nějaká hrůznost s xml a fulltextem.

[dush]

Nastuduj si EAV model, mohlo by to byt to co hledas http://en.wikipedia.org/wiki/Entity-attribute-value_model

Pripadne pokud chces pouzit MySQL + XML http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

[dbm]

Tak jsem si chvilku hrál s EAV modelem a přijde mi to nějaké pomalé. Mám tabulky users, attributes a eav_"datovy_typ". Jak z toho co nejefektivněji dostanu data? Dotaz

select users.uid, users.name, eav_blob.value, eav_date.value, eav_datetime.value, eav_float.value, eav_int.value, eav_string.value, eav_time.value from users left join eav_blob on (users.uid = eav_blob.uid) left join eav_date on (users.uid = eav_date.uid) left join eav_datetime on (users.uid = eav_datetime.uid) left join eav_float on (users.uid = eav_float.uid) left join eav_int on (users.uid = eav_int.uid) left join eav_string on (users.uid = eav_string.uid) left join eav_time on (users.uid = eav_time.uid) inner join attributes on (attributes.aid = eav_blob.aid OR attributes.aid = eav_date.aid OR attributes.aid = eav_datetime.aid OR attributes.aid = eav_float.aid OR attributes.aid = eav_int.aid OR attributes.aid = eav_string.aid OR attributes.aid = eav_time.aid)

zabere celou sekundu a to tam nemám skoro žádná data a zatím jsem ani nepoužíval eav entity.

[l0gik]

No protože ty joinuješ přes všechny atributový tabulky najednou - tzn. ve výsledku budou všechny možný kombinace atributů: intvalue, stringvalue, datevalue, datetime

Navíc tam duplikuješ users.name v každym dotazu, to taky není moc dobře, lepší je udělat pro každý "set" zvlášť (první dotaz select name, id from users, druhý pro atributy a na aplikační úrovni spojit přes id).

Jestli to chceš dělat takhle, musíš udělat ještě jednu mezitabulku eav_id, která bude přidělovat idčka sdílená pro všechny tabulky a joinovat

eav_id INNER JOIN atributy ON (eav_id.atrributes_id = atrributes.id) LEFT eav_date USING (id) LEFT eav_string USING (id) ....