模拟物化视图的实现

本文主要是介绍模拟物化视图的实现，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

物化视图在数据库的数据分析中占有很重要的作用（相关概念记录链接），但是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)
);

更新

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

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

更新的方式有：

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

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

INSERT INTO sales_mvSELECT product_name, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount), COUNT(*)FROM salesGROUP BY product_name;

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

SELECT * FROM sales_mv;

使用存储过程更新

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

CREATE PROCEDURE refresh_mv (OUT rc INT)
BEGINTRUNCATE TABLE sales_mv;			-- 清空物化视图INSERT INTO sales_mv				-- 重新放入数据SELECT product_name, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount), COUNT(*)FROM salesGROUP 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_mvWHERE product_name = NEW.product_nameINTO @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
BEGININSERT INTO sales_mv_log VALUES (NEW.product_name, NEW.product_price, NEW.product_amount, NEW.sales_id, NULL);
END;