Využití triggerů pro aktualizaci souhrnné informace
Školení, která pořádám 
Z výkonnostních důvodů se někdy do tabulky přidává sloupec uchovávající např. počet diskusních příspěvků u článku. Pokud pak vypisujeme seznam článků spolu s informací o počtu diskusních příspěvků u každého z nich, nemusíme podřízenou tabulku připojovat. Je to konec konců i pohodlnější.
Aktualizaci tohoto sloupce (který je z pohledu návrhu databáze nadbytečný) je vhodné vykonávat automaticky, aby se na ni nedalo zapomenout a aby vypočítaná hodnota vždy odpovídala skutečnosti. O tuto automatickou aktualizaci se může postarat trigger:
CREATE TRIGGER diskuse_ai AFTER INSERT ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + 1 WHERE id = NEW.clanek; CREATE TRIGGER diskuse_ad AFTER DELETE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet - 1 WHERE id = OLD.clanek; CREATE TRIGGER diskuse_au AFTER UPDATE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + IF(id = NEW.clanek, 1, -1) WHERE id = OLD.clanek XOR id = NEW.clanek;
Při změně v tabulce diskuse se aktualizuje počet u těch článků, jejichž ID odpovídá staré nebo nové hodnotě vazebního sloupce u diskuse. Pokud se hodnota vazebního sloupce nezměnila, aktualizace se neprovede, což zajišťuje operátor XOR.
Prvotní inicializaci sloupce zajistí poddotaz: UPDATE clanky SET diskuse_pocet = (SELECT COUNT(*) FROM diskuse WHERE clanek = clanky.id).
Pro tento typ automaticky aktualizovaného sloupce by se hodilo spíše nějaké deklarativní vyjádření hodnoty, kterou ve sloupci očekáváme. To se samozřejmě dá zajistit pohledem, u něj ale hodnota není materializovaná, takže nedojde ke zvýšení výkonu.
Přijďte si o tomto tématu popovídat na školení Konfigurace a výkonnost MySQL.
Diskuse
krteQ:
jeste par detailu
- foreign key nespusti trigger, dejte si na to pozor
- je vhodne pouzit deklaraci DEFINER=`user`@`server` a ujistit se, ze dany uzivatel existuje i na produkcnim serveru :) navic musi mit SUPER privilege => vic nez vhodne jiny uzivatel nez ten pro web
- pro replikaci vznikaji u triggeru mnoha omezeni, viz manual mysql
- views jsou v 5.0 verzi mysql spis nepouzitelym vtipem, zhorsuji vykon DB nez aby toho vyuzily, doporucuji se jim pokud mozno vyhnout
krteQ: 
finc:
Proc by foreign key mel spoustet trigger? Pokud nekde mam foreign key, mam prece primarni klic vlastnici tabulky.
Jedine, co bych videl jako problem je, ze pokud budu mit v diskuzi foreign key na clanek a ten bude "delete cascade" a ja se pokusim smazat clanek, ktery by mi mel smazat i diskuzni prispevky, nevim jak se s tim DB vyporada. V jakemkoli jinem pripade (akcnim dotazu nad clanky), prece nemusim spoustet trigger.
Me spise vadi jiny fakt, viz muj prispevek: http://finc.ic.cz/?p=6
Jinak si nejsem uplne jisty, zda bude rychlejsi reseni, ktere zde ukazuje Jakub. Asi by bylo dobre urcit faktory, ktere ovlivnuji rychlost (pocet dat, typ tabulky (MyISAM, InnoDB), slozitost tabulky, existence ref. integrit, atd.). Jde totiz o to, ze smysl tzv. "predpocitanych dat" ma pouze v tom pripade, kdy jde o kritickou cast aplikace. "Diky" teto vlastnosti totiz castecne ziskavam redundatni data. Prvnim krokem by melo byt: ulozit jen nutne, zbytek dopocitat, pote analyzovat a pote (pokud je to nevyhnutelne) predpocitavat.
finc: 
Jakub Vrána 
:
krteQ mluvil právě o tom "delete cascade".
Motivací pro uchování souhrnu dat může být i to, že kompletní data nemusí být v některých případech k dispozici (např. u importovaných článků máte k dispozici počet diskusních příspěvků, ale už ne jejich obsah).
Jakub Vrána : krteQ:
Mysql se s novejma fcema 5 verze vytahovali, ale cca do verze 5.0.22 je to nedoresenej bastl. Zkusili jsme 5.0.45 a ta vypada OK. Samozrejme si vazim ze mam takovej soft zdarma vubec k dispozici, ale obcas mi pripadaj jejich kroky trosku uspechany :)
krteQ: finc:
S tim se neda nesouhlasit. Jinak, vse jiste vyresi verze 6 s novym, vylestenym, extra-ultra-skvelym falcon enginem :)
Trosku se bojim toho, kam se vlastne vsechny ty featury dostanou...
finc: krteQ:
Problem je, ze Falcon bude v dobe vydani teprve mlady nedoladeny system se spoustou nemilych prekvapeni. Bude trvat roky nez se vetsina veci odladi a vykon naroste tak, aby se dal vyhodne pouzit misto InnoDB. Ale tesim se na nej...
krteQ: 
dgx:
Docela by mě zajímalo, jak se projeví na výkonu, když budu často modifikovat v tabulce "diskuse" nejaky s pripadem nesouvisejici sloupec. Napriklad pocet shlednuti nebo karmy body apod.
dgx: krteQ:
Nejaka rezie tam samozrejme je. Musis to napsat takovym zpusobem, aby to neprovadelo zbytecny dotazy. Ve chvili kdy se nejaky sloupec nemeni (treba menis nazev clanku), neni potreba prepocitavat s nim spojeny agregovany data.
Jde hlavne o to, ze se ti ta rezie vyplati - nadbytecnej join provedenej 500krat za sekundu je neco jinyho nez update jedenkrat za minutu :)
krteQ: havier:
Daju sa nejak zapisat 2 triggery pre INSERT alebo nejak zjednotit do jedneho ??mam tieto 2 triggery ktore sa lisia podla atributu typ_inz. ak je 1 tak chcem aby sa zvysil pocet o 1 v stlpci
poc_predaj a ked je hodnota 2 tak v stplci poc_kupa.
CREATE TRIGGER pridaj_predaj AFTER INSERT ON inz FOR EACH ROW UPDATE kat_inz SET poc_predaj = poc_predaj + 1 WHERE id = NEW.id_kat and NEW.typ_inz = 1;
CREATE TRIGGER pridaj_kupa AFTER INSERT ON inz FOR EACH ROW UPDATE kat_inz SET poc_kupa = poc_kupa + 1 WHERE id =
NEW.id_kat and NEW.typ_inz = 2;
Ked zadam len jeden trigger vsetko funguje ok ak obo tak vypise error:
This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
Dik za pripadne rady
havier:
uz som na to dosiel sam :)riesenie:
CREATE TRIGGER pridaj_pocet_kat_inz AFTER INSERT ON inz
FOR EACH ROW
BEGIN
IF (NEW.typ_inz) = 1 THEN
UPDATE kat_inz SET poc_predaj = poc_predaj + 1 WHERE id = NEW.id_kat;
END IF;
IF (NEW.typ_inz) = 2 THEN
UPDATE kat_inz SET poc_kupa = poc_kupa + 1 WHERE id = NEW.id_kat;
END IF;
END; //
Jakub Vrána :
Bez BEGIN/END se to dá vyřešit takhle:
UPDATE kat_inz SET poc_predaj = poc_predaj + IF(NEW.typ_inz = 1, 1, 0), poc_kupa = poc_kupa + IF(NEW.typ_inz = 2, 1, 0) WHERE id = NEW.id_kat;
Je to sice na jeden řádek, ale není to výkvět elegance a přehlednosti.
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 