Materialized View through triggers

On the e-commerce platform, we sometimes need to perform some aggregation calculations on user orders, such as the total number of orders, the total amount, and the average price, there are several methods to achieve this feature:

1. Execute the sum, AVG, and count statements of the SQL statement each time you query the aggregated information. The advantage is that the SQL statement is simple, and the result is that you need to scan the table every time, this method wastes a lot of machine resources, especially when there are few changes to orders and there are many queries.

2. Create an aggregate table. When an order is added, deleted, or modified, compute the new aggregate information through the program and store it to the aggregate table, you only need to query the corresponding calculated record for each query. The advantage is that the query is very simple, but the application needs to synchronize the aggregated information. If the order library is complete, but the aggregation library fails, data consistency must be ensured.

3. The database trigger is used to implement the materialized view. The advantage is that data synchronization is provided to the dB to ensure that the application does not need to pay attention to the view. If the trigger fails to be executed, the corresponding source table operations will also be rolled back, and the development of the corresponding trigger program is insufficient. This article mainly describes how to use a trigger to implement such a feature. In order to better illustrate how to create a process, we provide an example that has been successfully debugged in MySQL.

1. Create an order table

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. Create a table that stores aggregate information

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) // according to the product name aggregation, product_name is used as the unique key for de-duplication.

) Engine = InnoDB;

3. Create an after insert trigger for the table orders

First, explain how to check whether a table has created any triggers:
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. Create an After update trigger for the table orders

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 ),

// Error. At this time, price_sum is a new value and cannot be used again-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 ),

// Error. At this time, price_sum is a new value and cannot be used again-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. Create an After Delete trigger for the table orders

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;