mysql优化之物化视图

物化视图

目录

• 物化视图
  • 概念
  • 物化视图更新方式
  • 延迟更新与及时更新
    • 数据表
      • 结构
      • 数据
    • 延迟更新
      • mysql的实现方式
        • 创建物化视图
        • 确定执行的查询语句
        • 创建与物化视图相关视图
        • 添加数据
        • 创建存储过程
        • 最终测试
    • 及时更新
      • 特点
      • mysql实现方式
        • 触发器实现

概念

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

首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。

者带来的问题是使用 视图并不能将常用数据分离出来，优化查询速度，且操作视图的很多命令和普通表一样，这回导致在业务中无法通过sql区分表和视图，是代码变得复杂。

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

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

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

可以看出来数据量庞大的时候这个 时间...

物化视图更新方式

名称	描述
从不更新	只在开始更新
根据需要	每天，每夜
及时	每次修改数据之后
全部更新	速度慢，完全从无到有
延时的	速度快，使用log表

延迟更新与及时更新

数据表

结构

CREATE TABLE `purchase_order` (
  `order_id` smallint NOT NULL AUTO_INCREMENT COMMENT '采购订单ID',
  `order_sn` varchar(30) DEFAULT '' COMMENT '单号 订单组号+仓库ID',
  `plan_id` smallint NOT NULL DEFAULT '0' COMMENT '计划ID',
  `group_id` int DEFAULT '0' COMMENT '订单组ID',
  `pro_num` int DEFAULT '0' COMMENT '商品总数',
  `pro_price` decimal(10,2) DEFAULT '0.00' COMMENT '商品总价',
  `pro_cat` smallint DEFAULT '0' COMMENT '商品种类',
  `supply_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

数据

延迟更新

延迟更新特性：开销小，结果响应慢
mysql实现方式：定时调用存储过程函数即可
程序实现：定时计划处理

mysql的实现方式

文章表： id; 文章名; 作者名称；点击量；浏览量；文章内容

1000w+ 查询点击量，浏览量，排名前五

创建物化视图

drop table purchase_mv; 
CREATE TABLE purchase_mv( 
supply_name VARCHAR(60) NOT NULL , 
pro_count INT NOT NULL, 
pro_price_sum INT NOT NULL, 
pro_price_avg FLOAT NOT NULL, 
pro_num_sum INT NOT NULL, 
pro_num_avg FLOAT NOT NULL, 
UNIQUE INDEX supply_name (supply_name) 
);

确定执行的查询语句

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;

创建与物化视图相关视图

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

按需更新物化视图

根据需要更新物化视图，我们可以用存储过程来实现

创建存储过程

DROP PROCEDURE 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 (order_sn, `ctime`, supply_id, supply_name, pro_num, pro_price, shipping_state) VALUES ('C1803221553615160063su','1521701992', '1','友阿果园',75,479,2) 

CALL refresh_mv_now(); 

SELECT * FROM purchase_mv;

及时更新

特点

及时更新特性：开销大，结果响应快 
mysql实现方式:执行insert，update，delete，alter操作后执行触发器 
程序实现：异步队列事件方式

mysql实现方式

在每个语句之后进行完全刷新是没有意义的。

但我们仍然希望得到正确的结果。

要做到这一点，要复杂一点。

在purchase_order表上的每一个插入项上，我们都必须更新我们的物化视图。

我们可以通过purchase_order表上的 INSERT/UPDATE/DELETE触发器透明地实现这一点：这里以isnert为列子

思路：通过触发器，然后在添加完数据之后获取之前的聚合值的数据，然后根据新增的这条数据再做实时更新

触发器实现

after insert on drop trigger 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 ;