(转)MySQL的经典用法(三)----海量数据统计处理,模拟物化视图
一 物化视图物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
只有大型数据库oracle10g,db2才支持这个功能,而MySQL5.1暂时还没有这个功能。本人通过事件调度和存储过程模拟了物化视图。下面大家一起来讨论吧。^-^
二 准备知识
1)存储过程:玩过数据库的人，都知道他是啥~，~
2)事件调度:在MySQL5.1开始才有的新功能。说白了就是个定时器。跟java里的timer差不多。
Sql代码
[*]show processlist; --这个命令能是查看线程，如果启动了时间调度器，则会多个event_scheduler
[*]
set global event_scheduler = on; --启动时间调度器，关闭就不用我说了吧，off就可以了
[*]
show processlist\G;--查看线程，是不是多了个 User: event_scheduler
[*]
[*]
--如果想启动单个事件，则执行下面命令
[*]
ALTER EVENT `myEvent`
[*]
ON COMPLETION PRESERVE
[*]ENABLE;
[*]
--关闭
[*]
ALTER EVENT `myEvent`
[*]
ON COMPLETION PRESERVE
[*]DISABLE;
show processlist; --这个命令能是查看线程，如果启动了时间调度器，则会多个event_scheduler
set global event_scheduler = on; --启动时间调度器，关闭就不用我说了吧，off就可以了
show processlist\G;--查看线程，是不是多了个 User: event_scheduler
--如果想启动单个事件，则执行下面命令
ALTER EVENT `myEvent`
ON COMPLETION PRESERVE
ENABLE;
--关闭
ALTER EVENT `myEvent`
ON COMPLETION PRESERVE
DISABLE;
mysql默认不会启动event,所以需要修改你的my.ini或my.cnf的下面加入如下行
event_scheduler=1
三 模拟物化视图
1)先建个基表吧。
Sql代码
[*]CREATE TABLE `user` (
[*]
`Id` int(11) NOT NULL AUTO_INCREMENT,
[*]
`name` varchar(255) DEFAULT NULL,
[*]
`age` int(11) DEFAULT NULL,
[*]
PRIMARY KEY (`Id`)
[*]);
CREATE TABLE `user` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
);
2)设计视图
我想查询所有18岁员工的数量。
很简单，select count(*) from user where age=18;
如果是传统概念的视图，在MySQL中，每次访问视图的时候，他都会创建个临时表，然后执行一次。在海量数据的情况下，这样的效率是非常低的。而物化视图，则他会定时去刷新这个临时表，而不是你在用的时候才会去刷新。并且物化视图的"临时表"是一直存在的。所以效率高出非常多。拿空间换时间^-^
3)创建"物化视图"的表
Sql代码
[*]CREATE TABLE `user_view` (
[*]
`Id` int(11) NOT NULL AUTO_INCREMENT,
[*]
`c` int(11) DEFAULT NULL,
[*]
PRIMARY KEY (`Id`)
[*]);
CREATE TABLE `user_view` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
);
4)设计存储过程
Sql代码
[*]CREATE PROCEDURE demoSp()
[*]
BEGIN
[*]
DECLARE z INT;
[*]
SELECT COUNT(*) INTO z FROM user;
[*]
delete from user_view;
[*]
insert into user_view(c) values(z);
[*]
END;
CREATE PROCEDURE demoSp()
BEGIN
DECLARE z INT;
SELECT COUNT(*) INTO z FROM user;
delete from user_view;
insert into user_view(c) values(z);
END;
5)设计调度并执行，为了使实验明显，我就把调度设置成5秒一次吧。周期可以自己调节。
Sql代码
[*]CREATE EVENT `myEvent`
[*]
ON SCHEDULE EVERY 5 SECOND
[*]
ON COMPLETION PRESERVE
[*]DO call demoSp();
CREATE EVENT `myEvent`
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DO call demoSp();
四 总结
上面的实现，可能不太明显，但在海量数据处理的时候进行统计，性能有明显的提升。大家做实验的时候，可以增加一个insert语句，方便观察。在更新数据的时候，如果数据量大，则需要按下面步骤来处理
1)delete 1条记录
2)insert 1条记录
3)提交
如果一次性把数据全部删除，在查询的时候，有可能会出现空表的现象。而且会影响统计使用。
通过利用这个事件调度，定时备份的事情也同时解决了.
建议大家配合表分区,索引同时使用,这样可以提高性能.
未完成:在更新表的时候，需要采用某种算法来执行，这样才能提高性能，而不是单纯的删除再插入.
有问题大家请留言交流。版权所有，转载请注明出处 ^-^
页:
[1]