模拟物化视图的实现

物化视图在数据库的数据分析中占有很重要的作用（相关概念记录链接），但是MySQL等数据库只有普通视图，没有实现物化视图。我们可以根据物化视图的思路手动实现类似的功能，即用一个实体表存储经常访问的数据，并按需对其进行更新。

创建物化视图表

首先创建一个物品表并为其填充数据

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

物化视图实际上就是一个物理上真实存在的表，因此首先需要创建存放物化视图的实体表，例如对上面物品表创建物化视图sales_mv用于存放其常用的统计信息：

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

更新

物化视图的更新时机可以分为以下三种：

1. 只在开始更新，之后从不更新，用于静态数据
2. 按频率更新，比如每天
3. 每次数据修改之后及时更新

更新的方式有：

1. 完全更新，重新对整个表写入数据，速度慢
2. 增量更新，使用日志记录发生变化的数据，然后只更新变化的数据

通过如下语句为物化视图填入数据

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;

通过如下语句访问物化视图就可以得到我们想要的数据

SELECT * FROM sales_mv;

使用存储过程更新

可以创建一个存储过程refresh_mv来执行对物化视图的更新操作，如下所示为对物化视图进行全量更新

CREATE PROCEDURE refresh_mv (OUT rc INT)
BEGIN
	TRUNCATE TABLE sales_mv;			-- 清空物化视图
	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;
	SET rc = 0;
END;

当需要的时候调用存储过程对物化视图进行更新

CALL refresh_mv(@rc);

使用触发器更新

如果每次只对某些行进行更新，那我们只需要对发生改变的行进行更新而不必对整个表进行操作，如下所示定义触发器，当对sales表执行插入后更新物化视图sales_mv

CREATE TRIGGER sales_ins
AFTER INSERT ON sales			-- 对sales表执行插入后触发
FOR EACH ROW
BEGIN
	# 获取原来的值
	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;
	
	# 计算并更新元组
	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;

使用日志更新

首先需要一个单独的日志表sales_mv_log 来记录发生的改变

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()
);

当对sales表执行插入操作时，定义触发器动作如下，将插入的信息记录到日志表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;