Dopočítávané sloupce
Školení, která pořádám 
Řekněme, že na stránce chceme zobrazovat deset článků s největším počtem diskusních příspěvků. K tomu můžeme použít zhruba takovýto dotaz:
SELECT clanky.nadpis, COUNT(diskuse.id) AS pocet_komentaru FROM clanky LEFT JOIN diskuse ON clanky.id = diskuse.clanek GROUP BY clanky.id ORDER BY pocet_komentaru DESC LIMIT 10
Problém s tímto dotazem je v tom, že se pro setřídění nemůže použít index, což je obzvlášť bolestné kvůli klauzuli LIMIT – při existenci indexu by stačilo prozkoumat 10 záznamů, bez indexu je potřeba prozkoumat všechny. Takže když článků bude přibývat, dotaz bude pomalejší a pomalejší. Jak z toho ven?
MySQL nedovoluje vytvářet materializované pohledy, takže řešením může být si sloupec do tabulky přidat ručně a vytvořit nad ním index (třeba v komentáři je vhodné naznačit, že jde o dopočítávaný sloupec). Naplnění tohoto sloupce můžeme zajistit dotazem UPDATE clanky SET pocet_komentaru = (SELECT COUNT(*) FROM diskuse WHERE clanek = clanky.id). Pokud si chceme být jisti jeho konzistencí, tak musíme nadefinovat celkem 5 triggerů:
CREATE TRIGGER `diskuse_ai` AFTER INSERT ON `diskuse` FOR EACH ROW UPDATE clanky SET pocet_komentaru = pocet_komentaru + 1 WHERE id = NEW.clanek; CREATE TRIGGER `diskuse_ad` AFTER DELETE ON `diskuse` FOR EACH ROW UPDATE clanky SET pocet_komentaru = pocet_komentaru - 1 WHERE id = OLD.clanek; -- pro případ, že se změní diskuse.clanek CREATE TRIGGER `diskuse_au` AFTER UPDATE ON `diskuse` FOR EACH ROW UPDATE clanky SET pocet_komentaru = pocet_komentaru + IF(id = NEW.clanek, 1, -1) WHERE id IN (OLD.clanek, NEW.clanek) AND OLD.clanek != NEW.clanek; CREATE TRIGGER `clanky_bu` BEFORE UPDATE ON `clanky` FOR EACH ROW SET NEW.pocet_komentaru = (SELECT COUNT(*) FROM diskuse WHERE clanek = NEW.id); -- není potřeba, pokud je dodržena referenční integrita CREATE TRIGGER `clanky_bi` BEFORE INSERT ON `clanky` FOR EACH ROW SET NEW.pocet_komentaru = (SELECT COUNT(*) FROM diskuse WHERE clanek = NEW.id);
Dotaz se pak zjednoduší na prosté SELECT nadpis, pocet_komentaru FROM clanky ORDER BY pocet_komentaru DESC LIMIT 10.
V MySQL je potřeba dát pozor na to, že triggery se nespustí při kaskádové aktualizaci záznamů cizím klíčem.
Závěr
Jde o poměrně pracnou techniku zlepšení efektivity dotazů, takže bychom ji měli používat jenom tam, kde měření výkonnosti ukáže, že je co zlepšovat. Dotaz pro získání dat se nicméně podstatně zjednoduší, takže ji na druhou stranu můžeme někdy využít i místo definice pohledu.
Technika jde použít na prakticky libovolné dopočítávané sloupce, ať už jde třeba o výpočet prodejní ceny podle nastavené marže nebo o ID uživatele momentálně nejvýše přihazujícího v aukci.
Přijďte si o tomto tématu popovídat na školení Konfigurace a výkonnost MySQL.
Diskuse
Sniper:
Zdravim,
triggery mam rad a prave pro dopocitavane sloupce je pouzivam ve velkem, protoze vlastne udelam jenom jeden insert do diskuse/hlasovani/whatever a databaze se o celou vec postara sama.
Nicmene k tem triggerum co jsou v clanku - prvni a druhy je mi jasnej. Nicmene treti, ctvrtej a vlastne ani patej uz moc ne.
Pochopil jsem spravne, ze treti a ctvrty trigger ma zajistit, ze pokud dojde k preideckova clanku, aby se upravila reference v diskusi? nejak si neumim predstavit rozumnou situaci, kdy by k preideckovani clanku doslo.
A ten paty, ten nechapu vubec. vzdyt preci vzdycky bude vracet nulu, ledaze by existoval komentar k neexistujicimu clanku, nebo ne?
Asi je to zpusobeny tim, ze jsem zvyklej pracovat s postgresem, kterej umi bezvadne cizi klice apod., ale i tak...ten treti, ctvrtej a patej trigger mi prijde divnej
Jakub Vrána 
:
Vždyť je to v SQL komentáři popsané. Třetí trigger je potřeba, když změním článek, k němuž je diskuse připojena. Čtvrtý pro případ, že bych ručně změnil pocet_clanku. Druhá polovina potom jednak pro situaci, kdy je vypnutá kontrola cizích klíčů a jednak pro hypotetickou situaci, že id není unikátní :-). Pátý je potřeba pouze když je vypnutá kontrola cizích klíčů.
Jakub Vrána : paranoiq:
@sniper: třetí trigger je pro případ, že je komentář přesunut pod jiný článekpátý pro případ, že komentář k článku (včeně id článku) vznikne dřív než článek samotný. to by se ale opravdu stávat nemělo
Franta:
Každý kus kódu by měl dělat jednu věc a dělat ji pořádně – ne se starat o milion blbostí kolem, které by hypoteticky mohly nastat (i když nedávají smysl).
Komentáře existující před vytvořením článku nedávají smysl – a proto bych tam ten pátý trigger vůbec nedával – akorát matoucí zbytečný kus kódu.
Čtvrtý se dá brát leda jako ochrana proti chybě programátora (pokus měnit automaticky generovaný sloupec), ale elegantnější mi přijde, když na tenhle sloupec nebude mít uživatel právo UPDATE (trigger pak poběží s právy vlastníka, což by měl být někdo jiný).
Třetí trigger v případě články-komentáře taky nedává smysl – asi těžko budeš přehazovat komentáře od jednoho článku k druhému. Smysl by to mělo třeba u diskusí, kde se může stát, že chceš vyčlenit nějaké vlákno do samostatné diskuse.
Jakub Vrána :
Řešení se snaží být co nejvíc neprůstřelné. Před pátým triggerem je komentář přesně v duchu toho, co říkáš.
Právo pro sloupce nastavuje málokdo. Druhá větev je opět kvůli neprůstřelnosti.
Třetí trigger je nezbytný - mě se už několikrát stalo, že jsem diskusní příspěvek omylem přesouval k jinému článku, protože ho někdo omylem zapsal jinam než chtěl.
Jde o to, jestli chceš polovičaté řešení nebo úplné. S čtvrtým a pátým triggerem se s tebou dá částečně souhlasit (po odebrání práva, což mi osobně přijde krkolomnější), ale s odebráním třetího nikoliv. Když si člověk myslí, že taková situace nemůže nastat, tak schválně nastane.
Jakub Vrána : Hever:
Třetí a čtvrtý případ by se stávat neměl, nedává to smysl. Takto to ale zareaguje i v tom případě, který nedává smysl.U toho pátého je to tak jak píšeš. Smažeš článek, vzápětí ho tam znovu nainsertuješ, diskuze k němu zůstala.
Ještě bych upozornil na to, že diskuze.clanek musí být NOT NULL. v opačném případě by si to na OLD.clanek != NEW.clanek vylámalo zuby. Bylo by potřeba NOT (OLD.clanek <=> NEW.clanek) nebo tak něco.
A IF podmínky bych přepsal před SET. Pro čtvrtý trigger:
IF(NEW.id <=> OLD.id) THEN # <=> je rovnítko které si rozumí s NULL
SET NEW.pocet_komentaru = (SELECT COUNT(*) FROM diskuse WHERE clanek = NEW.id)
END IF
Hever:
Aha, už chápu použití IF ve čtvrtém triggeru, je to správně řešené. Jakub Vrána :
Třetí případ je pro zcela legitimní situaci. Někdo omylem přispěje k jinému článku než chtěl a já ho přesunu k tomu správnému.
Jakub Vrána : paranoiq:
díky za článek.pokud jsou použity cizí klíče, tak není potřeba ani čtvrtý trigger
Jakub Vrána :
Ale ano, minimálně část NEW.pocet_komentaru = OLD.pocet_komentaru
Jakub Vrána : paranoiq:
aha. ale jen v případě, že někdo přepíše přímo sloupec pocet_komentaruKajman:
Když se vynechá pátý trigger, tak je potřeba ohlídat, aby sloupeček pocet_komentaru měl defaultně hodnotu 0. K null hodnotě by nešly přičítat jedničky.pojízdná kočka:
Díky za článek."Jde o poměrně pracnou techniku..." - já bych řekla, že ani ne ;-) Pracnější (pro SŘBD) je ty záznamy pokaždé počítat. Taktéž souhlasím, že pro řádově desítky článků a diskuzních příspěvků na článek ještě tento "kalibr" nasazen být nemusí.
A ještě bych dodala (ale to už se myslí samo sebou), že skoro všechny free hostingy s mysql triggery nepovolují, takže tam by alternativou bylo při vložení příspěvku provést kód uvnitř triggeru ve skriptu.
Karel Dytrych:
To ale nezaruci konzistenci pri upravach primo z databaze...
Karel Dytrych: pojízdná kočka:
To samozřejmě - ale když triggery máš zakázáno využívat, tak je tohle řešení stále nejlepší - rozhodně lepší, než zatěžovat databázi extra dotazem při každém načtení přehledu diskuzí.Dále - když už zasáhnu přímo do databáze, tak mám dost slušnosti na to, abych "po sobě uklidila", tedy aktualizovala data (buď manuálně nebo skriptem, který to udělá za mě).
A vůbec - zasahování přímo do databáze by mělo být jen ve velmi výjimečných případech - vůbec ne běžně, jak se (možná - nevím) snažíš naznačit.
Karel Dytrych:
Ty třeba tu slušnost máš abys data aktualizovala, ale pokud bude na stejném projektu ještě dělat nový kolega co dopočítávané sloupce ani znát nebude a prostě půjde pouze smazat nějaké záznamy, tak věřím že žádný úklid neudělá...
Proč automaticky vycházíš z předpokladu, že jsou triggery zakázané?
Karel Dytrych: pojízdná kočka:
To s tím kolegou už je věc, která zachází někam do lidských zdrojů a personalistiky...Není to zas tak "automatický" předpoklad - prostě to tak je (nebo přinejmenším - taková je má zkušenost), že freehostingy s MySQL na ní svým uživatelům nepovolují trigerry. Jak Jakub říká, jde o ně požádat, ale v některých případech (tam, kde jsou pod jednou doménou desetitisíce webů) to chce více trpělivosti a štestí na to nenarazit na ignorantského nebo nafrněného správce.
Kajman:
Většina hostingů ještě má mysql verze 5.0, kde je na triggery nutné super právo, které dávat nebudou. Od verze 5.1.6 (se speciálním právem pro triggery) by jejich povolení mělo být častější. Jakub Vrána :
Když právo vytvářet triggery chybí, tak obvykle stačí požádat o jejich vytvoření správce.
Jakub Vrána : Kcko:
Proc je nutne techto 5 slozitych triggeru, resp ty 3 posledni urcite a nestacni v aplikaci 1 SQL dotaz, ktery se postara vzdy o totez.Typu: (vkladam komentar do clanku s ID 7)
UPDATE clanky SET pocet_komentaru = (SELECT COUNT(*) FROM diskuse WHERE clanek = clanky.id AND clanek = 7) WHERE id = 7
Rychlostne to nebude o nic pomalejsi v pripade ze diskuse nebude mit tisice komentaru. Radsi takovyto jednoduchy dotaz nez patlat 5 triggeru a pak se dalsi programator divi kde a jak se co dopocitavat. Jednoduchy SQL dotaz nadevse, konzistence zajistena.
Karel Dytrych:
Protoze se muze aktualizovat DB z vice mist vice uzivateli a s triggery je to potom jedno... sami dopocitaji spravny pocet komentaru at uz uzivatel udela cokoliv...
Karel Dytrych: Jakub Vrána :
Zapomněl jsi na to, že komentář se může smazat a aktualizovat. Navíc je potřeba ošetřit úpravu článku a v MySQL i jeho vložení. Pokud chceš mít data konzistentní, tak jsou triggery nutné, pokud ti stačí přibližný počet, tak to můžeš řešit aplikačně.
Všechny triggery by skutečně mohly být skoro stejné, ale osobně mi přijde mnohem přehlednější použít +1 než vždy klást poddotaz (navíc je to efektivnější). V MySQL navíc nejde v triggeru pracovat s tabulkou, kterou aktualizuješ.
Jakub Vrána : pojízdná kočka:
Pokud bych si nad diskuzemi představila jednoduchý admínek, který by umožňoval (mj.) mazání komentářů (po němž by skript provedl dotaz na zjištění počtu komentářů a dopsal je do dané diskuze), pak za předpokladu, že budu pracovat pouze přes něj, je konzistence zajištěna.Naopak, pokud by (z nějakého - byť nepravděpodobného - důvodu) byl počet komentářů od začátku chybný, pak přičítáním +1 nebo -1 budu mít konzistentní chybu po celou dobu - poddotazem pouze do prvního akce, která jej spustí (přidání/smazání).
Martin:
Nejsem si jistý, ale není tam chyba? Pokud přidám do diskuze příspěvek, tak první trigger mi zvedne hodnotu pocet komentau u článku, ale 4. trigger tuto hodnotu vrátí zpátky, nebo se pletu? Jakub Vrána :
Ne, čtvrtý trigger se aktivuje jen při změně ID článku.
Jakub Vrána : Martin:
Ja bych rekl ze ctvrty trigger se aktivuje pri jakemkoliv update na tabulce clanky. Pokud se nezmeni id tak pocet komentaru zustane stejny (new=old) a tady je ten problem. Protoze tento ctvrty trigger vyvola prvni trigger ktery zvysuje pocet komentaru a hodnota se nezvysi. Jakub Vrána :
Pokud ID zůstane stejné, tak se provede akorát SET NEW.pocet_komentaru = OLD.pocet_komentaru.
Jakub Vrána : Martin:
Což vrátí změnu vyvolanou prvním triggerem a +1 se nepřičte. Jakub Vrána :
Fakt že jo. Díky za upozornění, opravil jsem to.
Jakub Vrána : Martin:
Vracim se k tvemu prikladu, ted je tam zase problem v tom ze pri pridani diskuze se sice zvysi +1 v clancich, ale zaroven se vyvola trigger (4.), ktery to jeste jednou prepise ze selectu, coz je celkem zbytecne pak uz, cili na pridani clanku se zavola update, select, update, neni to skoda? Napada te lepsi reseni?Diskuse je zrušena z důvodu spamu.
© 2005-2024 