【MySQL】物化视图

物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样

首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切操作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，使代码变得复杂。

视图是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了），他的存在未了在设计上的方便性

物化视图可以帮助加快严重依赖某些聚合结果的查询。

如果插入速度不是问题，则此功能可以帮助减少系统上的读取负载。

使用场景

在我们的user_order表中有800万的数据，这是时候如果需要使用聚合来查询数据，那么就会很慢，下图就可以看到，查询这些数据花费了4秒钟时间，这在项目中是很不允许的

解决方案

其实就是在新建一张表，把查出来的数据存放在那个表里边即可。当然这里也可以使用缓存来操作。

创建存储聚合查询出来的数据

CREATE TABLE record_data(
	name VARCHAR(60)  NOT NULL ,
	count INT NOT NULL,
	price_sum INT  NOT NULL,
	price_avg FLOAT NOT NULL,
	num_sum INT  NOT NULL,
	num_avg FLOAT NOT NULL,
  UNIQUE INDEX name (name)
);

在创建一个user_order的一个视图表，防止以后表结构的改动

切记这里的视图时在源数据的视图，不是存储聚合查询的那个表的视图

create view record_view as
	select
			name,
      count(*) count,
			sum(price_sum) price_sum,
			avg(price_avg) price_avg,
			sum(num_sum) num_sum,
			avg(num_avg) num_avg
	from
			record_data
	group by
			name;
			

将聚合查询的数据存放到record_data中

insert into record_data select `name`,count(*),sum(price),sum(num),avg(price),avg(num) from user_order;

然后就可以看到我们用来专门存储聚合查询的数据表里边的数据

同样也可以使用咱们刚刚定义好的视图来获取数据

insert into record_data select * from record_view;

定时更新

定时调用refresh_mv_now即可更新record_data的数据

-- 定义存储过程来定时执行更新数据
CREATE PROCEDURE refresh_mv_now ()
BEGIN
	-- 清空表里边的数据
  TRUNCATE TABLE record_data;
  -- 更新记录表里边的聚合查询数据
  insert into record_data select * from record_view;
END;

call refresh_mv_now ();

时时更新

实时更新数据, ==>> 触发器 使用触发器,是很会影响数据库的写操作的性能
这个没有进行测试其实就是利用触发器，进行时时的更新record_data的数据，原理明白就可以了

CREATE TRIGGER purchase_mv_trigger_ins AFTER INSERT ON purchase_order FOR EACH ROW
BEGIN
	SET @old_pro_price_sum = 0;
	SET @old_pro_price_avg = 0;
	SET @old_pro_num_sum = 0;
	SET @old_pro_num_avg = 0;
	SET @old_pro_count = 0;
	# 查询出之前的信息然后记录到不同的变量中
	SELECT
		IFNULL(pro_price_sum,0),IFNULL(pro_price_avg,0),
		IFNULL(pro_num_sum,0),IFNULL(pro_num_avg,0),
		IFNULL(pro_count,0)
	FROM
		purchase_mv
	WHERE
		supply_name = NEW.supply_name
	INTO
		@old_pro_price_sum,@old_pro_price_avg,@old_pro_num_sum,@old_pro_num_avg,@old_pro_count;
	# 然后再去计算更新操作之后的内容
	SET @new_pro_count = @old_pro_count + 1;
	SET @new_pro_price_sum = @old_pro_price_sum + NEW.pro_price;
	SET @new_pro_price_avg = @new_pro_price_sum / @new_pro_count;

	SET @new_pro_num_sum = @old_pro_num_sum + NEW.pro_num;
	SET @new_pro_num_avg = @new_pro_num_sum / @new_pro_count;

	REPLACE INTO
		purchase_mv
	VALUES(
		NEW.supply_name, @new_pro_count,
		@new_pro_price_sum, IFNULL(@new_pro_price_avg, 0),
		@new_pro_num_sum, IFNULL(@new_pro_num_avg, 0)
	);
END;

应用范围

物化视图 -> 存储过程 和 触发器的 综合应用
表的数据大 : 有些内容很频繁查询

查看

https://www.yuque.com/u30882/rx39g7/dzztm0