Materialized view plays an important role in data analysis of database( Related concept record link )However, MySQL and other databases only have ordinary views and do not implement materialized views. We can manually implement similar functions according to the idea of materialized view, that is, use an entity table to store frequently accessed data and update it as needed.

Create materialized view chart

First, create an item table and fill it with data

CREATE TABLE sales (
    sales_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
  , product_name   VARCHAR(128) NOT NULL
  , product_price  DECIMAL(8,2) NOT NULL
  , product_amount SMALLINT     NOT NULL
);

INSERT INTO sales VALUES
  (NULL, 'Apple', 1.25, 1), (NULL, 'Apple', 2.40, 2),
  (NULL, 'Apple', 4.05, 3), (NULL, 'Pear', 6.30, 2),
  (NULL, 'Pear', 12.20, 4), (NULL, 'Plum', 4.85, 3);

The materialized view is actually a physically real table. Therefore, first, you need to create an entity table to store the materialized view, such as materialized view sales for the above item table_ MV is used to store common statistical information:

CREATE TABLE sales_mv (
	product_name VARCHAR(128)  NOT NULL ,
	price_sum    DECIMAL(10,2) NOT NULL,
	amount_sum   INT           NOT NULL,
	price_avg    FLOAT         NOT NULL,
	amount_avg   FLOAT         NOT NULL,
	sales_cnt    INT           NOT NULL,
	UNIQUE INDEX product (product_name)
);

to update

The update timing of materialized views can be divided into the following three types:

1. Update only at the beginning, never after, for static data
2. Update by frequency, such as every day
3. Update the data in time after each modification

The update methods are:

1. Complete update and write data to the whole table again, which is slow
2. Incremental update, log the changed data, and then update only the changed data

Fill in the data for the materialized view with the following statement

INSERT INTO sales_mv
	SELECT product_name, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount), COUNT(*)
	FROM sales
	GROUP BY product_name;

The data we want can be obtained by accessing the materialized view through the following statement

SELECT * FROM sales_mv;

Update using stored procedures

You can create a stored procedure refresh_mv to update the materialized view, as shown below

CREATE PROCEDURE refresh_mv (OUT rc INT)
BEGIN
	TRUNCATE TABLE sales_mv;			-- Empty materialized view
	INSERT INTO sales_mv				-- Reinsert data
		SELECT product_name, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount), COUNT(*)
		FROM sales
		GROUP BY product_name;
	SET rc = 0;
END;

Call the stored procedure to update the materialized view when necessary

CALL refresh_mv(@rc);

Update with trigger

If only some rows are updated each time, we only need to update the changed rows without operating the whole table. Define the trigger as follows to update the materialized view sales after inserting the sales table_ mv

CREATE TRIGGER sales_ins
AFTER INSERT ON sales			-- yes sales Triggered after table insert
FOR EACH ROW
BEGIN
	# Get the original value
	SET @old_price_sum = 0;
	SET @old_amount_sum = 0;
	SET @old_price_avg = 0;
	SET @old_amount_avg = 0;
	SET @old_sales_cnt = 0;
	SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0)
		FROM sales_mv
		WHERE product_name = NEW.product_name
	INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_sales_cnt;
	
	# Calculate and update tuples
	SET @new_price_sum = @old_price_sum + NEW.product_price;
	SET @new_amount_sum = @old_amount_sum + NEW.product_amount;
	SET @new_sales_cnt = @old_sales_cnt + 1;
	SET @new_price_avg = @new_price_sum / @new_sales_cnt;
	SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;
	REPLACE INTO sales_mv VALUES(
		NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_amount_avg, @new_sales_cnt);
END;

Use log update

First, you need a separate log table sales_mv_log to record the changes

CREATE TABLE sales_mv_log (
    product_name   VARCHAR(128) NOT NULL
  , product_price  DECIMAL(8,2) NOT NULL
  , product_amount SMALLINT     NOT NULL
  , sales_id       INT UNSIGNED NOT NULL
  , product_ts     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP()
);

When inserting into the sales table, define the trigger action as follows to record the inserted information into the log table sales_mv_log

CREATE TRIGGER sales_ins
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
	INSERT INTO sales_mv_log 
	VALUES (NEW.product_name, NEW.product_price, NEW.product_amount, NEW.sales_id, NULL);
END;