物化视图是相对于视图而言的,但是两者实际上并没有什么关系就如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实现方式:定时调用存储过程函数即可 程序实现:定时计划处理
文章表: 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操作后执行触发器 程序实现:异步队列事件方式
在每个语句之后进行完全刷新是没有意义的。
但我们仍然希望得到正确的结果。
要做到这一点,要复杂一点。
在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 ;