【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)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
在创建一个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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
将聚合查询的数据存放到record_data中
insert into record_data select `name`,count(*),sum(price),sum(num),avg(price),avg(num) from user_order;
- 1
然后就可以看到我们用来专门存储聚合查询的数据表里边的数据
同样也可以使用咱们刚刚定义好的视图来获取数据
insert into record_data select * from record_view;
- 1
定时更新
定时调用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 ();
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
时时更新
实时更新数据, ==>> 触发器 使用触发器,是很会影响数据库的写操作的性能
这个没有进行测试其实就是利用触发器，进行时时的更新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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
应用范围
物化视图 -> 存储过程 和 触发器的 综合应用
表的数据大 : 有些内容很频繁查询
查看
https://www.yuque.com/u30882/rx39g7/dzztm0
文章来源: blog.csdn.net，作者：咔咔-，版权归原作者所有，如需转载，请联系作者。
原文链接：blog.csdn.net/fangkang7/article/details/97274462
- 点赞
- 收藏
- 关注作者
评论（0）