通過觸發器實現物化視圖
在電商平臺中，我們有時需要對用戶訂單進行一些聚合計算，如訂單總數有多少，總金額有多少，平均價格是多少，而實現這個特性基本有下面幾個辦法：
一， 每次查詢這些聚合信息的時候，直接執行SQL語句的sum，avg，count等，好處是實現簡單，不足是每次均需要進行掃表查詢，特別是訂單變更比較少，而查詢比較多的情況下，此方法會浪費不少的機器資源。
二， 新建一個聚合表，當有訂單增刪改的時候，通過程序進行計算新的聚合信息，然後存儲到該聚合表，每次查詢的時候隻需查詢對應計算好的記錄即可，好處是查詢非常簡單，不足是需要應用程序進行同步聚合信息，且如果訂單庫操作整個，而聚合庫失敗，則需要保證數據的一致性。
三，利用DB的觸發器實現物化視圖的方式，好處是數據的同步交給db 去保證，應用程序無需關註，並且若觸發器執行失敗，則對應的源表操作也會回滾，不足是需要開發對應的觸發器程序。本文主要說明用觸發器實現這樣的一個特性，為瞭更好的說明如何創建的過程，我們舉瞭這樣一個例子，該例子已經在mysql全部調試通過。 www.aiwalls.com
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; www.aiwalls.com
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; www.aiwalls.com
$$
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) www.aiwalls.com
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 www.aiwalls.com
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
