Dotaz: Náročnost inner joinu s a bez limitu řádků

[vasek]

Ahoj. Provádím docela náročné dotazy (select na spojení několika statisíceřádkových tabulek) v databázi mysql. Potřebuji vypsat 30 řádků. Je nějaký rozdíl co se týče složitosti spojení v mysql jestli provedu už dotaz s parametrem LIMIT nebo jestli až v aplikaci (php) vypíšu prvních 30 řádků? Jde o to, že LIMITem v mysql nepostihnu všechno (aplikace má ještě nějaké vnitřní kontroly).

Odpovědi

[poky74]

Jestli jsem to správně pochopil , tak jste strašné prase =-o.

Takže vy selectujete několik 100 tisícovích tabulek a použijete jenom 30 řádků? Už jak to slyšim tak se mi ježí chlupy na zádech...

PS. Jaké vnitřní kontroly?

[vasek]

A to mám selectovat 100 000 řádků jenom proto, že je ta tabulka má? To by bylo trochu stupidní vykreslovat potom webovou stránku která má pár gigabajtů. Jednou z těch vnitřních kontrol je např. poměrně složitý mechanismus detailního oprávnění, který se nedá "vimplementovat" do databáze.

[ghost]

dejte konkretni priklad, urcite to nepujde rozlozit alespon do vice kroku? Pouzit sice vice, ale levnych dotazu a pak az ten samotny pro vyber? patricne upraveny aby vyzvedl jen mnozinu ziskanou z predchoziho kroku?

[poky74]

Ptáte se jestli je rozdíl v tom, jestli používat "limit" na vrstvě databáze nebo aplikace, z toho plyne že bez limitu se sellectuje vše, proto se na to ptám...

Ale k věci, tohle nasvědčuje o špatném návrhu aplikace/databáze

[ghost]

Principialne ano a velky. Ty vyselectovane radky jsou v db ulozeny v nejake temporary table. A co zjistit ta omezeni predtim nez spustite samotny select? Jak ta kontrola vypada? Jsou to dalsi nejake dotazy do db?

[vasek]

Ta kontrola vypadá tak, že načtu řádek z databáze a na základě dat z těch selectovaných sloupců (někdy i na základě předchozího řádku) rozhoduji o tom, které údaje zobrazit a které ne, případně které pozměnit. Aby se ta kontrola dala provádět, musím to z té databáze mít už načtené. Dříve jsem to řešil tak, že jsem při selectu za where dával ta omezení (oprávnění), jenže teď se mechanismus oprávnění dost rozšířil a taková podmínka where by byla hodně moc dlouhá a pro databázi dost neefektivní (dost často jsou používány i regulární výrazy).

[ghost]

Hm, tak na prvni pohled to vypada na hodne spatne navrzenou databazi - zejmena nedodrzeni normalnich forem. V takovemto pripade si bez podrobnejsiho seznameni netroufam navrhnout lepsi reseni. Mimochodem, Vasek M. ?

[vasek]

Celá databáze je ve 3NF, indexy jsou tam kde mají být a obecně je to řekl bych na možnosti udělané dobře. Ty oprávnění jsou tam dodělávané později. Hoodně dlouho jsem řešil jak to co nejlépe navrhnout aby to vyhovovalo požadavkům. Neříkám, že by se to nedalo rozložit do selectu jako součást dotazu, ale když má uživatel desítky různých oprávnění typu může číst/zapisovat ... sloupec ten a ten pouze pokud je ve zbývajících to a to tak taková podmínka už by byla minimálně pár tisíc znaková. Hlavně si myslím že mysql si dobře neporadí z těmi regexpy (i když by to umět mělo).

[vasek]

Mimochodem, Vasek M. ?

ne

[ghost]

když má uživatel desítky různých oprávnění typu může číst/zapisovat ... sloupec ten a ten pouze pokud je ve zbývajících to

O jakou aplikaci se jedna (aspon obor)? Zkus to rozepsat trosku podrobneji - desitky ruznych opravneni. Z popisu predpokladam spravne, ze opravneni jsou definovana az na uroven sloupcu? Co v tech sloupcich treba je?

[vasek]

Je to takový centrální dohled nad jednočipovými externími jednotkami a zároveň synchronizace uživatelských účtů z různými webovými aplikacemi. Těžko se to dá popsat, je to dost specifická věc.

selectnuté sloupce tabulek vypadají nějak takto:

zamestnanec_id | jmeno | email | skupina | ... | zar1_hodnota_typ | zar2_hodnota_val | ... | zar3_....

Sloupce jsou většinou varchar, občas nějaký int. A ano je to definováno až na úroveň sloupců. Oprávnění mi mohou říci třeba to, že uživatel se jmenem pepa a emailem pepa@mail.cz muze cist hodnotu zar2_hodnota_val, ktera je v rozsahu 50-80 a jejiz nazev je /c(.*)[ax]/ nebo jejiz hodnota je 60-90 a zároveň je uživatel ve skupině skupina.

Mimochodem teď jsem zkusil vyselektovat ty hodnoty bez LIMITu a limitovat to až v aplikaci a žádné zpoždění se nekonalo, funguje to krásně rychle, řekl bych že malinko rychleji než s těmi původními právy které byly v podmínce where. Jak je to možné? Mimochodem používám v php PDO a prepared statements, 30x fetchnu řádek a pak prepared statement zahodím.

[vasek]

Teda samozřejmě že to zpoždění by pak nastalo při aplikaci oprávnění (prošlo by se hodně řádků), ale tahle to funguje rychle.

[ghost]

selectnuté sloupce tabulek vypadají nějak takto:
zamestnanec_id | jmeno | email | skupina | ... | zar1_hodnota_typ | zar2_hodnota_val | ... | zar3_....

takze ve vysledku se objevuje zarX_hodnota_typ a zarX_hodnota_val, kde X muze byt 0 az X ? tzn. promenny pocet sloupcu ve vysledku?

Sloupce jsou většinou varchar, občas nějaký int. A ano je to definováno až na úroveň sloupců. Oprávnění mi mohou říci třeba to, že uživatel se jmenem pepa a emailem pepa@mail.cz muze cist hodnotu zar2_hodnota_val, ktera je v rozsahu 50-80 a jejiz nazev je /c(.*)[ax]/ nebo jejiz hodnota je 60-90 a zároveň je uživatel ve skupině skupina.

Toto opravdu silne zavani spatne navrzenou databazi! Predpokladam, ze neexistuje vazebni tabulka napr. uzivatel_smi_cist_zarizeni ? a dalsi vazebni tabulky realizujici takto prava? Opravdu bych na to sel nejakou rozumnou dekompozici - zkratka podobnou logikou jako se chovaji ruzne ORM. NApriklad v prvnim kroku vytahnout uzivatele, v druhem skupinu, ve tretim vsechny jednotky na ktera ma prava on, na ktere skupina, atd ... Minimalne z toho duvodu, aby ses alespon vyhnul zbytecnemu joinovani. Ve vysledku uz muzes vyselektovat jen ty jednotky na ktere ma pravo (jejich idecka mas z predchozich kroku) a aplikovat na ne pripadne dalsi slozitejsi selekt.

Mimochodem teď jsem zkusil vyselektovat ty hodnoty bez LIMITu a limitovat to až v aplikaci a žádné zpoždění se nekonalo, funguje to krásně rychle, řekl bych že malinko rychleji než s těmi původními právy které byly v podmínce where. Jak je to možné? Mimochodem používám v php PDO a prepared statements, 30x fetchnu řádek a pak prepared statement zahodím.

Neni to nacachovane? Kolik je ve vysledku radku? Zalezi co vsechno bylo v te podmince WHERE, od urcite slozitosti dotazu (alespon v MySQL) se vyplati rozdelit jeden slozity dotaza na vice malych.

[vasek]

X je nějakých 10 a mění se to velmi velmi zřídka (+-1). Ale to co jsem uvedl už je výsledek po inner joinu. Standardně se dívám jen do jedné z tabulek, ale občas to potřebuji zobrazit všechno. Vazební tabulky mám jen typu uživatel-skupina a skupina-práva. Ostatní oprávnění bohužel pomocí vazebních tabulek realizovat nejdou. Obešel bych se asi i bez regulárních výrazů, ale musel bych to nahradit LIKE znaky % a _ abych pomohl databázi a i tak by vazební tabulky nešlo použít.

Neni to nacachovane? Kolik je ve vysledku radku? Zalezi co vsechno bylo v te podmince WHERE, od urcite slozitosti dotazu (alespon v MySQL) se vyplati rozdelit jeden slozity dotaza na vice malych.

Nekešované to není, zkoušel jsem vybírat 300 řádku postupně s různými OFFSETy a LIMIT jsem nastavoval na 1000000 a zkoušel jsem to i bez LIMITu. V té době nebylo v podmínce where nic. Předtím když jsem zkoušel ten LIMIT 300 v sql, tak tam byly asi 4 podmínky LIKE.

[ghost]

Proc musis pouzivat pro filtrovani regularni vyraz nebo like? Uzivatel to vyhledava podle jmena? To nejde pouzit primarni klic? Ci nazvy jednotek znaci i jejich pripadnou skupinu? Treba c01,c02,b01,d03 tak ty chces vybrat vsechny jednotky ze skupiny c?
Kdyz uz to chces filtrovat na strane aplikace, nebo ti nic jineho nezbyva, tak se zkus zbavit alespon co nejvice joinu. Jeste bych mozna uvazoval, jestli to povaha aplikace vubec dovoli, o duplikaci te tabulky ze ktere filtrujes a udelal jeji kopii do tabulky, ktera ma jako storage engine Memory ... at to jede aspon rychle.
Jeste by melo zajimal zpusob, jakym to v te aplikaci filtrujes. Ale to jen tak ze zvedavosti.

[vasek]

Na memory storage nemám dost paměti (několik GB do budoucna). V té aplikaci filtruji (pokud potřebuji) všechny sloupce a porovnávám hodnoty příp. (NOT) LIKE hodnota. Nakonec to asi vyřeším kompromisem: v 1. kroku vyberu všechno aby to odpovídalo filtrování a zároveň aplikuji jednoduchá práva, potom pro jednotlivé tabulky budu aplikovat práva postupně při čtení.

[ghost]

Vyzkousej, uvidis sam. Mne se porad nezda navrh db, ale tak nechme to konovi ... Jelikoz toho vic nevim, tak uz me zadny jiny zpusob nenapada. Chtelo by to dost detailnejsi popis ...

[GeoRW]

tiez si myslim, ze to mas zle navrhnute; urcite sa to da realizovat na urovni databazy priamo do datoveho modelu prip. si vytvorit par procedur, finkcii na databaze; davat tuto logiku do aplikacie v PHP je nezmysel

[vasek]

Pouze částečně by to šlo realizovat na úrovni databáze. Dejme tomu že chci změnit řádek v tabulce na něco. Nejprve si ale musím projít oprávnění, abych zjistil na co to mohu změnit.

Např.

tabulka:
zamestnanec_id | jmeno | email | skupina

původní řádek:
1, josef, jose@mail.cz, externiste

nový řádek:
1, josef, josef@mail2.com, dohled

Co když mi oprávnění závisí na sloupcích email a skupina, které jsou na sobě závislé. To bych pak musel aktualizovat po sloupcích a to by bylo dost zlé. Kdybych měnil 20 sloupců, musel bych pro každý z nich zavolat funkci do db JE_OPRAVNEN(...), přičemž při každém volání této funkce by se znovu musely projít oprávnění. Kdežto když to řeším na úrovni aplikace, tak mi stačí dočasně řádek upravit a jednou prohnat kontrolou oprávnění ještě před tím než to zapíšu do databáze. Pokud nepočítám nějaké "hloubkové" procházení dat, tak už tady je "rozdíl složitosti" n^2 : n, a to nepočítám čas potřebný pro komunikaci s db.

[vasek]

Navíc pokud mám přístup k řádkům s hodnotami .. jose@mail.cz, externiste nebo ..josef@mail2.com, dohled tak ani nemohu měnit hodnoty postupně.

[vasek]

Nicméně také sem mi nelíbí realizace na úrovni aplikace, takže jestli mátě nějaký kostruktivní návrh jak to provést na úrovni db, sem s ním (konstruktivním návrhem nepočítám dočasné transakce).

[pht]

Oprávnění mi mohou říci třeba to, že uživatel se jmenem pepa a emailem pepa@mail.cz muze cist hodnotu zar2_hodnota_val, ktera je v rozsahu 50-80 a jejiz nazev je /c(.*)[ax]/ nebo jejiz hodnota je 60-90 a zároveň je uživatel ve skupině skupina.

Tady by pomohlo mít ta oprávnění předpočítaná v separátním sloupci abyste nemusel v každém dotazu vyjadřovat takhle složité podmínky. Předpočítání můžete dělat při insertu nebo třeba periodicky.

[l0gik]

Co se týče přístupu k zařízením s hodnotou 50-80, tak tato zařízení by měla tvořit nějakou "logickou" skupinu, ne? Tak zavést do databáze tu skupinu a oprávnění k nim. Celý se Ti to najednou smrksne na problém, že máš uživatele, ty jsou členuy různých skupin a ty skupiny maj práva k různým výrobkům.

Uživatel - (je v) Skupině uživatelů - (má práva k) Skupině výrobků - (má práva k) výrobkům

Vypsat všechny výrobky, ke kterejm mám práva, popř. vyselektit jeden výrobek, pokud k němu mám práva, je pak dosti efektnivní záležitost jednoho rekurzivního dotazu.

Navíc Ti to bude automaticky podporovat vnořené uřživatelské skupiny, vnořené skupiny výrobků atd... A když si napíšeš vhodně modely, tak Ti bude skládat selekty pro zjišťování práv automaticky na jednom místě.

[kuka]

Obecne toto nelze rict. Za urcitych okolnosti muze klauzule limit ovlivnit plan provedeni dotazu ve smeru pouziti indexu a tim to radove zrychlit. Pro takto "vystizne popsanou" situace ale tezko rict.