【MySQL】物化视图_mysql

概述物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在 *** 作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切 *** 作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，使代码变得复杂。视图是简化设计，...

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

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

视图是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图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 ROWBEGIN	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,		IFNulL(pro_num_sum,IFNulL(pro_num_avg,		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;