mysql-物化视图-延时性物化视图和及时性物化视图的创建及其使用

物化视图是一个实体的表，物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样
首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切操作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，是代码变得复杂。
视图是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了），他的存在未了在设计上的方便性
物化视图可以帮助加快严重依赖某些聚合结果的查询。 如果插入速度不是问题，则此功能可以帮助减少系统上的读取负载。
可以看出来数据量庞大的时候这个时间.....

物化视图和视图类似，反映的是某个查询的结果，但是和视图仅保存SQL定义不同，物化视图本身会存储数据，因此是物化了的视图。

百度百科解释得很好：

物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照（类似于MSQL Server中的snapshot，静态快照） [1]  。对于复制，物化视图允许你在本地维护远程数据的副本，这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。（这个是基于本地的基表或者视图的聚合）。物化视图，说白了，就是物理表，只不过这张表通过mysql的内部机制可以定期更新，将一些大的耗时的表连接用物化视图实现，会提高查询的效率。当然要打开查询重写选项；

创建使用物化视图所需要的基础：视图，触发器。

物化视图分为 及时性和延时性 类型：

及时性：开销大，结果响应快

• 触发器（mysql）：执行insert，update，delete之后执行
• 程序（后端）：异步队列事件方式。

延时性：开销小，结果响应慢

• 存储过程（mysql）：定时调用这个函数（交予后台几分钟，几小时执行一次）
• 程序（后端）：定时计划处理

示例

• 延时性物理视图创建及其使用（测试）：

     过程简介：

1. 准备数据表-》创建核心表
2. 创建一个物理表-与之你需要的业务所对应的的一个物理表（比如说排名，每分钟后刷新的用户平均消费，总消费之类的表）
3. 创建视图-视图表和2步骤的物理表一一对应
4. 创建一个存储过程用于更新 步骤2 中物理表
5. 测试。
6. 这里没有写；需要在java代码中定义一个定时器，没几分钟或者是几小时去调用一次存储过程。

注意延时性需要每隔一段时间运行一次存储过程，一般交由java后端的定时器处理（sprinbgboot中就有一个定时器可以使用。这一步操作不在进行代码解释。）

代码如下

代码如下

--  1.创建核心表
DROP TABLE IF EXISTS purchase_order;
CREATE TABLE `purchase_order` (
  `order_id` smallint(5) NOT NULL auto_increment comment '订单id',
  `pro_num` int(8) DEFAULT NULL comment '单次购买件数',
  `pro_price` decimal(10,2) DEFAULT NULL comment '单次购买总价',
  `supply_name` varchar(255) DEFAULT NULL comment '用户名',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='订单购买详情表'; 

--  2.创建物理视图表（其实就是一个物理表便于区分所以叫他物理视图表）
drop table IF EXISTS purchase_mv;
CREATE TABLE purchase_mv(
supply_name VARCHAR(60) NOT NULL ,
pro_count INT NOT NULL comment '用户历史购买总次数',
pro_price_sum INT NOT NULL comment '用户历史购买总消费',
pro_price_avg FLOAT NOT NULL comment '用户平均每次购买消费',
pro_num_sum INT NOT NULL comment '用户历史购买总件数',
pro_num_avg FLOAT NOT NULL comment '用户每次购买平均件数',
UNIQUE INDEX supply_name (supply_name)
);
--  3.创建业务需要的视图，物理表需要和视图一一对应
drop view IF EXISTS por_view;
create view por_view as
SELECT
supply_name,
count(*) pro_count,
sum(pro_price) pro_price_sum,
avg(pro_price) pro_price_avg,
sum(pro_num) pro_num_sum,
avg(pro_num) pro_num_avg
from
purchase_order
group by
supply_name;
-- 添加数据
insert into purchase_mv select * from por_view;

-- // 4.定义存储过程
DROP PROCEDURE IF EXISTS refresh_mv_now;
DELIMITER ;;
CREATE PROCEDURE refresh_mv_now ()
BEGIN
#先清空数据 
TRUNCATE TABLE purchase_mv;
#再根据视图导入数据 
INSERT INTO purchase_mv SELECT * FROM por_view;
END;
;;
DELIMITER ;

-- //上面就是整个屋里视图的创建过程
-- //下面是测试
-- 测试 添加数据
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张三',10,200);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张三',80,2500);

INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张四',20,500);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张四',60,8000);

INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张张',40,600);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张张',30,5300);

INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('小三',30,2000);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('小三',20,1000);

-- 测试 调用存储过程
CALL refresh_mv_now();
-- 测试 查看数据是否更改
SELECT * FROM purchase_mv;
 

• 及时性物理视图创建及其使用（测试）：

过程简介：1. 2.  3 .步骤 和延时性的步骤是一样的 可以直接跳过看第四无=五步骤

1. 准备数据表-》创建核心表
2. 创建一个物理表-与之你需要的业务所对应的的一个物理表（比如说排名，每分钟后刷新的用户平均消费，总消费之类的表）
3. 创建视图-视图表和2步骤的物理表一一对应
4. 创建对应的触发器 用于更新 步骤2  的物理表
5. 查看数据是否更新。

注意：（缺点==》）及时性是每次插入就会更新无理视图表，这样会有很高的消耗。（和延时性触发器的区别 ==》）正因为使用了触发器所以不用想使用存储过程的延时性那样去写一个定时器在后台每隔一段时间去运行这个存储过程

--  1.创建核心表
DROP TABLE IF EXISTS purchase_order;
CREATE TABLE `purchase_order` (
  `order_id` smallint(5) NOT NULL auto_increment comment '订单id',
  `pro_num` int(8) DEFAULT NULL comment '单次购买件数',
  `pro_price` decimal(10,2) DEFAULT NULL comment '单次购买总价',
  `supply_name` varchar(255) DEFAULT NULL comment '用户名',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='订单购买详情表'; 

--  2.创建物理视图表（其实就是一个物理表便于区分所以叫他物理视图表）
drop table IF EXISTS purchase_mv;
CREATE TABLE purchase_mv(
supply_name VARCHAR(60) NOT NULL ,
pro_count INT NOT NULL comment '用户历史购买总次数',
pro_price_sum INT NOT NULL comment '用户历史购买总消费',
pro_price_avg FLOAT NOT NULL comment '用户平均每次购买消费',
pro_num_sum INT NOT NULL comment '用户历史购买总件数',
pro_num_avg FLOAT NOT NULL comment '用户每次购买平均件数',
UNIQUE INDEX supply_name (supply_name)
);
--  3.创建业务需要的视图，物理表需要和视图一一对应
drop view IF EXISTS por_view;
create view por_view as
SELECT
supply_name,
count(*) pro_count,
sum(pro_price) pro_price_sum,
avg(pro_price) pro_price_avg,
sum(pro_num) pro_num_sum,
avg(pro_num) pro_num_avg
from
purchase_order
group by
supply_name;
-- 添加数据
insert into purchase_mv select * from por_view;

-- // 4.触发器实现 after insert on
    drop trigger IF EXISTS purchase_mv_trigger_ins;
    DELIMITER ;;
    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;
    ;;
    DELIMITER ;

-- //上面就是整个屋里视图的创建过程
-- //下面是测试
-- 测试 添加数据
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张三',10,200);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张三',80,2500);

INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张四',20,500);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张四',60,8000);

INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张张',40,600);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('张张',30,5300);

INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('小三',30,2000);
INSERT INTO purchase_order (supply_name, pro_num, pro_price) VALUES ('小三',20,1000);

-- 测试 查看数据是否更改
SELECT * FROM purchase_mv;
 

 

其他网站：https://blog.csdn.net/qq_34581118/article/details/76511616