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.

Jakub Vrána, Dobře míněné rady, 17.2.2010, diskuse: 30 (nové: 0)

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

ikona Jakub Vrána OpenID:

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íčů.

paranoiq:

@sniper: třetí trigger je pro případ, že je komentář přesunut pod jiný článek

pá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.

ikona Jakub Vrána OpenID:

Ř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.

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é.

ikona Jakub Vrána OpenID:

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.

paranoiq:

díky za článek.

pokud jsou použity cizí klíče, tak není potřeba ani čtvrtý trigger

ikona Jakub Vrána OpenID:

Ale ano, minimálně část NEW.pocet_komentaru = OLD.pocet_komentaru

paranoiq:

aha. ale jen v případě, že někdo přepíše přímo sloupec pocet_komentaru

Kajman:

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.

ikona Karel Dytrych:

To ale nezaruci konzistenci pri upravach primo z databaze...

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.

ikona 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é?

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ší.

ikona Jakub Vrána OpenID:

Když právo vytvářet triggery chybí, tak obvykle stačí požádat o jejich vytvoření správce.

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.

ikona 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...

ikona Jakub Vrána OpenID:

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š.

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?

ikona Jakub Vrána OpenID:

Ne, čtvrtý trigger se aktivuje jen při změně ID článku.

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.

ikona Jakub Vrána OpenID:

Pokud ID zůstane stejné, tak se provede akorát SET NEW.pocet_komentaru = OLD.pocet_komentaru.

Martin:

Což vrátí změnu vyvolanou prvním triggerem a +1 se nepřičte.

ikona Jakub Vrána OpenID:

Fakt že jo. Díky za upozornění, opravil jsem to.

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.

avatar © 2005-2024 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.