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

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