欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

通过触发器实现物化视图_MySQL

程序员文章站 2022-04-30 08:46:31
...
bitsCN.com
通过触发器实现物化视图 在电商平台中,我们有时需要对用户订单进行一些聚合计算,如订单总数有多少,总金额有多少,平均价格是多少,而实现这个特性基本有下面几个办法: 一, 每次查询这些聚合信息的时候,直接执行SQL语句的sum,avg,count等,好处是实现简单,不足是每次均需要进行扫表查询,特别是订单变更比较少,而查询比较多的情况下,此方法会浪费不少的机器资源。 二, 新建一个聚合表,当有订单增删改的时候,通过程序进行计算新的聚合信息,然后存储到该聚合表,每次查询的时候只需查询对应计算好的记录即可,好处是查询非常简单,不足是需要应用程序进行同步聚合信息,且如果订单库操作整个,而聚合库失败,则需要保证数据的一致性。 三,利用DB的触发器实现物化视图的方式,好处是数据的同步交给db 去保证,应用程序无需关注,并且若触发器执行失败,则对应的源表操作也会回滚,不足是需要开发对应的触发器程序。本文主要说明用触发器实现这样的一个特性,为了更好的说明如何创建的过程,我们举了这样一个例子,该例子已经在mysql全部调试通过。 1, 新建一个订单表 drop table orders if exists; create table orders ( order_id int unsigned not null auto_increment, product_name varchar(30) not null, price decimal(8,2) not null, amount smallint not null, primary key (order_id) )engine=innodb; 2,创建一个存储聚合信息的表 drop table orders_mv if exists; create table orders_mv ( product_name varchar(30) not null, price_sum decimal(8,2) not null, amount_sum int not null, price_avg float not null, orders_cnt int not null, unique key product_name(product_name) //因为需要按照产品名字聚合,这里把product_name作为唯一key进行去重 ) engine=innodb; 3,为表orders创建after insert的触发器 首先说明一下如何查看一个表中是否已经创建了哪些触发器:
select * from information_schema.TRIGGERS where event_object_table='tbl_name'/G drop trigger tgr_orders_insert; delimiter $$ create trigger tgr_orders_insert after insert on orders for each row begin set @old_price_sum = 0; set @old_amount_sum = 0; set @old_price_avg = 0; set @old_orders_cnt = 0; select IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0) from orders_mv where product_name = NEW.product_name into @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt; set @new_price_sum = @old_price_sum + NEW.price; set @new_amount_sum = @old_amount_sum + NEW.amount; set @new_orders_cnt = @old_orders_cnt + 1; set @new_price_avg = @new_price_sum / @new_orders_cnt; replace into orders_mv values (NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt); end; $$ delimiter ; 4,为表orders创建after update的触发器 drop trigger tgr_orders_update; delimiter $$ create trigger tgr_orders_update after update on orders for each row begin if (STRCMP(OLD.product_name, NEW.product_name)) then update orders_mv set price_sum = (price_sum - OLD.price), amount_sum = (amount_sum - OLD.amount), orders_cnt = (orders_cnt - 1), //错误,此时的price_sum已经是新值, 不能重新 -OLD.price + NEW.price //price_avg = (price_sum - OLD.price) / IF((orders_cnt-1)>0, (orders_cnt-1), 1) price_avg = price_sum /IF(orders_cnt>0, orders_cnt, 1) where product_name = OLD.product_name; set @old_price_sum = 0; set @old_amount_sum = 0; set @old_price_avg = 0; set @old_orders_cnt = 0; select IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0) from orders_mv where product_name = NEW.product_name into @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt; set @new_price_sum = @old_price_sum + NEW.price; set @new_amount_sum = @old_amount_sum + NEW.amount; set @new_orders_cnt = @old_orders_cnt + 1; set @new_price_avg = @new_price_sum / @new_orders_cnt; replace into orders_mv values (NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt); else update orders_mv set price_sum = (price_sum - OLD.price + NEW.price), amount_sum = (amount_sum - OLD.amount + NEW.amount), //错误,此时的price_sum已经是新值, 不能重新 -OLD.price + NEW.price //price_avg = (price_sum - OLD.price + NEW.price) /IF(orders_cnt>0,orders_cnt,1) price_avg = price_sum /IF(orders_cnt>0,orders_cnt,1) where product_name = OLD.product_name; end if; end; $$ delimiter ; 5,为表orders创建after delete的触发器 drop trigger tgr_orders_delete; delimiter $$ create trigger tgr_orders_delete after delete on orders for each row begin update orders_mv set price_sum = (price_sum - OLD.price), amount_sum = (amount_sum - OLD.amount), orders_cnt = (orders_cnt - 1), price_avg = price_sum /IF(orders_cnt>0, orders_cnt, 1) where product_name = OLD.product_name; end; $$ delimiter ; 作者 tenfyguo bitsCN.com