| Autor | Zpráva | ||
|---|---|---|---|
| sysel Profil |
#1 · Zasláno: 28. 7. 2009, 20:38:26
Pokusil jsem se naštudovat 'normální formy' databází, ale nejsem moudrý z toho, jak na celkem jinak jednoduchou úlohu.
Mějme, například, seznam vojáčků s jejich id, jeménem, hodností, zařazením ke zbrani a (zatím) prázdnou položkou zařazení do jednotky. Druhý je seznam jednotek s id, názvem, velitelem a opět (zatím) prázdnou položkou začlenění jednotky do vyšší jednotky - úloha by tedy mohla být vícevrstevná, ale to zatím neřešme. Nějakým postupem dosáhneme přizazení vojáků z prvního seznamu k jednotkám z druhého - tím se vyplní u některých vojáků položka zařazení do jednotky. Podle šedivé teorie je to klasický příklad na nevlastní klíče. Když jsem se pokusil zrealisovat toto v MySQL (ale obecně to (ne)půjde v jakémkoliv SQL), první problém nastal s různými potřebami jednotek, (kupř od dvou do deseti), ale to šlo minout mlčky právě tím nevlastním klíčem, protože id jednotky může být vloženo libovolnému počtu vojáků. Ale dát dohromady několikanásobný JOIN tabulky vojáků k tabulce jednotek, tak abych dostal v každém řádku výpisu jedno kompletní obsazení jedné jednotky, to už obecně nejde. Pouze se znalostí počtu vojáků v jednotce lze setavit výpis všech členů jednotky 'vedle sebe'. Ergo kladívko nejprve vypsat dvoumužné, pak třímužné ... až deseti mužné jednotky. Ale i to má své úskalí, a tím je náročnost zpracování. Pokud jsou jednotky do pěti - šesti vojáků, výpisu se lze dočkat. Pro deset vojáků v jednotce už si na výpis lze počkat v kavárně (i přes důsledné indexování). Rovněž tím vícenásobným kartézským součinem rostou nestydatě nároky na paměť. Došel jsem k přesvědčení, že na pokyn JOIN MySQL nejprve vytvoří spojení všech kompletních tabulek a teprve pak na výslednou obrtabulku aplikuje požadované podmínky. Nevím proč jsem naivně doufal, že MySQL připojuje pouze podmínkou ON vybrané řádky z tabulek postupně, což by věc patrně urychlylo a výrazně snížilo hrabání na disk i do paměti. Pokud jsem takové chování nafixloval v rámci skriptu pomocí temporary table dosahoval jsem výrazně kratších časů. - lze nějak (tajným parametrem, kouzelným slůvkem, vhodnou modlitbou) zařídit, aby MySQL připojila z tabulky pouze podmínkou ON vybrané řádky??? - napadá někoho, zda by se dalo jedním vrzem vypsat všechny kompletní jednotky bez ohledu na jejich počty vojáků (menší jednotky by klidně mohly mít několik NULL vojáků) Příklad je pochopitelně vymyšlený, ale celkem obdobně by to vypadlo se zaměstnanci v podniku, s rodinami s nestejným počtem dětí apod. Díky za Vaše nápady a ztrátu času :-) |
||
| TomášK Profil |
#2 · Zasláno: 28. 7. 2009, 21:05:27 · Upravil/a: TomášK
Nejsem si jist, jestli jsem dobře pochopil zařazování do jednotek. Zkusím napsat, jak tomu rozumím:
* každý voják patří do právě jedné jednotky * jednotka může mít nadřízenou jednotku, čím se vytváří stromová struktura jednotek (ale to až v budoucnu, v příkladu s tím nepočítíme) Tabulky vojaci(id, jednotka_id) jednotky(id) Dotaz na přiřazení jednotek k vojákovi používá jen jeden JOIN. Pokud je struktura taková, že by pro každý počet vojáků v jednotce potřebovala další JOIN, pak je to zřejmě špatně navržené. Dotaz na přiřazení jednotek k vojákům by měl být za zlomek vteřiny i pokud bychom registrovali celou naší profesionální armádu. MySQL do detailu neznám, ale opravdu hodně by mě překvapilo, pokud vytvářelo kartézský součin. Postup, který znám z ostatních databází je, že se optimalizátor na základě statistik (indexů?) rozhodne, v jakém pořadí provede JOINy, aby to bylo co nejúspornější (nejrychlejší). Předpokládám, že vezme dvě tabulky, které se rozhodl spojit, ořeže je podle podmínek pro danou tabulku, udělá kartézský součin totho, co zbylo a vyřadí z něj záznamy, které nějaké neodpovídají podmínce nad oběma tabulkami. Tím snížil počet tabulek o 1 a pokračuje dál, dokud mu nezbude jedna tabulka. Určitě nedělá na začátku kartézský součin všeho - jedině, že usoudil, že ve výsledku bude většina z toho součinu, pak bych si dokázal představit, že to udělá. Chování MySQL nějaký parametry ovlivňovat snad i jde (detaily neznám), ale tohle je imho základní chování. EXPLAIN by mohl napovědět, co dotaz skutečně dělá. |
||
| sysel Profil |
#3 · Zasláno: 28. 7. 2009, 21:36:34 · Upravil/a: sysel
TomášK
„Nejsem si jist, jestli jsem dobře pochopil “ ... pochopil vojaci(id, jednotka_id, jmeno, cvo) jednotky(id, nazev) Pro jednotky s právě třemi vojáky: SELECT j.nazev, v1.jmeno, v1.cvo, v2.jmeno, v2.cvo, v3.jmeno, v3.cvo FROM jednotky AS j JOIN vojaci AS v1 ON j.id = v1.jednotka_id AND v1.cvo = 'kulometcik' JOIN vojaci AS v2 ON j.id = v2.jednotka_id AND v2.cvo = 'pomocnik_kulometcika' JOIN vojaci AS v3 ON j.id = v3.jednotka_id AND v3.cvo = 'odhanec_much' ; O optimalisátoru jsem také již slyšel, ale rovněž jsem se někde dočetl, že ausgerechnet MySQL dlabe na ON podmínky, pouze je posbírá, sloučí s WHERE podmínkami a ty pak použije až nad celým kartézským součinem. To docela odpovídá pozorovanému chování. Moje skutečná databáze už se rozrostla nad 30tis záznamů a půl giga paměti už s tím má opravdu problém. Přiznám, že jsem zatím použil databázově neortodoxní řešení: pokud nevlastní klíče považuji za zpětné ukazatele, před používáním databáze si do pilotních záznamů (jednotky) skriptem přidám položku s řetězcem složeným z dopředných ukazatelů, který pak v JOINu rozparsuji na samostatné id do tabulky vojáků. Vím, že je to (databázově) prasárna (moderátoři odpustí), ale funguje to a prakticky stejně svižně bez ohledu na počet vojáků v jednotce. |
||
| Alphard Profil |
#4 · Zasláno: 28. 7. 2009, 21:58:55
Takhle chcete vyjmenovávat každou pozici?
Mně jako schůdnější řešení připadá select j.nazev, v.jmeno, v.cvo from vojaci v left join jednotky j order by j.nazev (doplněním jednotek jako left join vyřeším i problém nezařazeného vojáka) Tento dotaz byde zpracován velmi rychle a v aplikaci lze snadno seskupit členy jedné jednotky na řádek (je seřazeno dle jednotek, takže jak se původní jednotka != současná jednotka, jsem u další). Kdyby nás zajímala jen jména, můžeme použít group_concat(). |
||
| Joker Profil |
#5 · Zasláno: 28. 7. 2009, 22:02:30
sysel:
Teď mi není úplně jasné, co přesně je cílem. Dělat (počet vojáků v jednotce) krát join tabulky je samozřejmě nesmysl. Ovšem ten výše uvedený dotaz se dá jednoduše napsat takhle: SELECT j.nazev, v.jmeno FROM jednotky j JOIN vojaci v ON v.jednotka_id = j.id -> vybere vojáky příslušející k dané jednotce. |
||
| TomášK Profil |
#6 · Zasláno: 28. 7. 2009, 22:04:19 · Upravil/a: TomášK
Dotaz [imho] vrací všechny jednotky, které obsahují alespoň jednoho kulometčíka a alespoň jednoho pomocníka a alespoň jednoho odháněče much. Pokud je v jednotce víc kulometčíků, pomocníků a odháněčů, vrátí to všechny jejich kombinace. V důsledku bude počet řádků ve výsledku růst exponenciálně s počtem profesí v jednotce, tedy i složitost dotazu musí být exponenciální. Jednotka o 100 vojácích od každé profese přispěje do výsledku 100^3 = milion řádků. Jednotka o deseti profesích by vrátila 100^10 = hodně.
Tenhle dotaz by měl vrátit všechny jednotky, které obsahují právě tři vojáky tří uvedených profesí a nikoho jiného (což je zadání, pokud ne nemýlím).
SELECT j.nazev
FROM jednotky AS j
WHERE
id IN (
SELECT jednotka_id
FROM vojaci
WHERE cvo IN ('kulometcik' 'pomocnik_kulometcika' 'odhanec_much')
GROUP BY jednotka_id
HAVING
COUNT(*) = 3 AND
COUNT(DISTINCT cvo) = 3);
Pokud vím, použití ON je jen 'syntaktický cukr' všude, dělá to to samé, jako kdyby to bylo ve WHERE (s rezervou, Vybavuju si, že jsem nedávno viděl nějaký příklad, kde se to chovalo odlišně, který mě zaskočil). Ale podmínky z WHERE a ON přijdou do jednoho pytle. Což ale neznamená, že se nejdřív provede kartézský součin a až pak filtruje. Naopak to rozšiřuje možnosti při rozhodování, v jakém pořadí filtrovat. Kartézský součin minimálně nedělá vždy, mám i dotazy, kde je třeba 5 JOINů a v každé tabulce několik desítek tisíc záznamů. Kdyby z toho udělal kartézský součin, tak se výsledku nedožiju. |
||
| sysel Profil |
#7 · Zasláno: 28. 7. 2009, 22:19:49
Alphard
„Takhle chcete vyjmenovávat každou pozici? “ ... no já tušil, že dostanu vynadáno :-) Ale jednak jsem se chtěl vyhnout právě následnému řešení v apllikaci, ale ono to tak nějak vzniklo postupným přechodem z databáze, která byla tvořena jedinou tabulkou, kde to bylo všechno, stokrát opakované a plné chyb. A já se v tom snažil udělat trochu pořádek malými, pokud možno nenápadnými, kroky. Takže toto ošklivé řešení se vlastně snažilo podstrčit aplikaci data tak, jakoby pocházela z původní tabulky. A to už na úrovni mysql resultu, protože se mi příčilo se v té aplikaci vrtat. Pokud, jak bylo řečeno, má voják jednoznačné zařazení k jednotce, je vše OK. V úloze se však musí počítat s tím, že voják může mít vícečetné zařazení, kupř. do požárního družstva, přičemž by samozřejmě bylo fajn, kdyby se jeho osobní data vyskytovala v tabulce vojáků právě jednou. V původním stavu se však toto vůbec neřešilo a v první fási jsem, ač se mi to příčilo, musel toto zvěrstvo akceptovat. Inu vstupovat do kalných vod cizí práce je někdy velmi nechutné. |
||
| Kajman_ Profil * |
#8 · Zasláno: 28. 7. 2009, 22:24:32
TomášK:
Nemá tam být COUNT(DISTINCT cvo) = 3 ? |
||
| Alphard Profil |
#9 · Zasláno: 28. 7. 2009, 22:38:43
sysel:
„V úloze se však musí počítat s tím, že voják může mít vícečetné zařazení“ Jak může být ve více jednotkách, když má jediné id_jednotky? To bychom najednou měli spojovací tabulku a relace M:N, ale o tom nepadlo ani slovo. K tomu dotazu, zdá se, že Joker a já jsem to pochopili jinak než TomášK, jesti chcete konrétní řešení, budete to muset upřesnit. |
||
| TomášK Profil |
#10 · Zasláno: 28. 7. 2009, 22:55:08
Kajman_
Má. Dotaz jsem zformátoval a opravil. Začínám mít deprese z toho, že pomalu každý dotaz, který napíšu, obsahuje nějakou chybu... Bojím se, abych místo pomoci neškodil. Ale snad je to většinou ku prospěchu i s tou chybou, na kterou 'se' (často díky tobě, Kajmane :) ) většinou rychle přijde. Dám si novoroční předsevzetí, že za každou chybu udělám 10 dřepů, snad to pomůže... |
||
|
Časová prodleva: 15 let
|
|||
0