MySql教程

mysql优化之物化视图

本文主要是介绍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 ;
这篇关于mysql优化之物化视图的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!