MySQL基础----海量数据统计处理,模拟物化视图

一 物化视图
    物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
    只有大型数据库oracle10g,db2才支持这个功能,而MySQL5.1暂时还没有这个功能。本人通过事件调度和存储过程模拟了物化视图。下面大家一起来讨论吧。^-^

二 准备知识
    1)存储过程:玩过数据库的人，都知道他是啥~，~
    2)事件调度:在MySQL5.1开始才有的新功能。说白了就是个定时器。跟java里的timer差不多。

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[mysqld]的下面加入如下行

event_scheduler=1

三 模拟物化视图
    1)先建个基表吧。

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)创建"物化视图"的表

CREATE TABLE `user_view` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);

    4)设计存储过程

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秒一次吧。周期可以自己调节。

CREATE EVENT `myEvent` 
ON SCHEDULE EVERY 5 SECOND 
ON COMPLETION PRESERVE 
DO call demoSp();

四 总结
    上面的实现，可能不太明显，但在海量数据处理的时候进行统计，性能有明显的提升。大家做实验的时候，可以增加一个insert语句，方便观察。在更新数据的时候，如果数据量大，则需要按下面步骤来处理
1)delete 1条记录
2)insert 1条记录
3)提交
    如果一次性把数据全部删除，在查询的时候，有可能会出现空表的现象。而且会影响统计使用。
    通过利用这个事件调度，定时备份的事情也同时解决了.

    建议大家配合表分区,索引同时使用,这样可以提高性能.

未完成:在更新表的时候，需要采用某种算法来执行，这样才能提高性能，而不是单纯的删除再插入.

有问题大家请留言交流。版权所有，转载请注明出处 ^-^

 

评论

19 楼 85977328 2011-01-12  

是啊，如果有oracle,直接用oracle的物化视图了.呵呵

18 楼 shun6826118 2011-01-12  

爪哇岛岛主 写道

其实吧，认真看看，觉得是很简单的东西啊

这个只是一个最基础的例子，而且只介绍个思想，你想在项目中用还是得思考一番的。

很佩服LZ的思路，跟我们目前开发的一套大数量处理方案很类似，目前我们用ORACLE 存储过程配合JAVA定时器做统计多路线程做数据同步，终于是完成了这个方案，而且现在很成熟，处理能力单表200W的数据量，分页查询每页2.8秒。效果显而易见

17 楼 无为1055 2010-11-25  

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

16 楼 85977328 2010-08-19  

爪哇岛岛主 写道

其实吧，认真看看，觉得是很简单的东西啊

有机会多多交流 

15 楼 爪哇岛岛主 2010-08-19  

其实吧，认真看看，觉得是很简单的东西啊

14 楼 muerjie 2010-08-18  

学习了，学习了

13 楼 爪哇岛岛主 2010-08-17  

恩，大家都是高手啊，我来学习来了。

12 楼 qn_lf 2010-08-09  

如果每月或者每天定时统计一次还好，可以加如时间等查询条件，而且以前统计的数据也没必要全部删除。

否则大批量数据定时触发刷新，性能应该是个大问题。

11 楼 85977328 2010-08-06  

webee 写道

不能能吧！没发安装?

我是没装上，楼上的用过吗?

10 楼 webee 2010-08-06  

不能能吧！没发安装?

9 楼 85977328 2010-08-06  

beiluka 写道

MySQL物化视图方案 FlexViews

MySQL物化视图方案 FlexViews
这个用不了，项目我下载过了。根本没法安装
哪位朋友如果要是尝试过MySQL FlexViews
请与我一起讨论^-^

8 楼 beiluka 2010-08-05  

MySQL物化视图方案 FlexViews

7 楼 85977328 2010-08-05  

jychenok 写道

85977328 写道

jychenok 写道

一直用oracle.....物化视图确实蛮爽的.....特别是设计到多个数据库交互大数据量的时候...查询性能提高很多....
最近转到mysql，有点茫然.....呵.用得很爽的oracle console不知道在mysql中有没有类似的实现...请教下楼主...最好能监控到顶级活动的..

MySQL不支持物化视图,只能这么模拟了
我查了很多相关的资料和官方文档
都没有物化视图的资料
我们公司如果用ORACLE,这个问题也不会烦恼我这么久了

楼主理解错我的意思了，我是问mysql有没有类似oracle的console服务，在线监控工具，能够监控到顶级活动，顶级使用者等信息

我在用MySQL官方提供的GUI,我感觉功能挺强大的
应该能监控Server相关信息的^-^

6 楼 jychenok 2010-08-05  

85977328 写道

jychenok 写道

一直用oracle.....物化视图确实蛮爽的.....特别是设计到多个数据库交互大数据量的时候...查询性能提高很多....
最近转到mysql，有点茫然.....呵.用得很爽的oracle console不知道在mysql中有没有类似的实现...请教下楼主...最好能监控到顶级活动的..

MySQL不支持物化视图,只能这么模拟了
我查了很多相关的资料和官方文档
都没有物化视图的资料
我们公司如果用ORACLE,这个问题也不会烦恼我这么久了

楼主理解错我的意思了，我是问mysql有没有类似oracle的console服务，在线监控工具，能够监控到顶级活动，顶级使用者等信息

5 楼 85977328 2010-08-05  

jychenok 写道

一直用oracle.....物化视图确实蛮爽的.....特别是设计到多个数据库交互大数据量的时候...查询性能提高很多....
最近转到mysql，有点茫然.....呵.用得很爽的oracle console不知道在mysql中有没有类似的实现...请教下楼主...最好能监控到顶级活动的..

MySQL不支持物化视图,只能这么模拟了
我查了很多相关的资料和官方文档
都没有物化视图的资料
我们公司如果用ORACLE,这个问题也不会烦恼我这么久了

4 楼 jychenok 2010-08-05  

一直用oracle.....物化视图确实蛮爽的.....特别是设计到多个数据库交互大数据量的时候...查询性能提高很多....
最近转到mysql，有点茫然.....呵.用得很爽的oracle console不知道在mysql中有没有类似的实现...请教下楼主...最好能监控到顶级活动的..

3 楼 85977328 2010-08-04  

clican 写道

应该不是这么简单的删了重新导，而是有相应的算法的。不然性能上无法忍受。

是的，具体的方法需要大家去扩展了。我这里只是给出最基本的方案
只是简单的模拟一下,不然MYSQL处理海量数据,在统计的时候确实有点麻烦

2 楼 clican 2010-08-04  

应该不是这么简单的删了重新导，而是有相应的算法的。不然性能上无法忍受。

1 楼 85977328 2010-08-04  

allskylove 写道

楼主精神可嘉 ！ 是否可以对物化视图分区？ 物化视图最核心的是刷新模式。其他的方面倒是很像真正的物化视图。慎用物化视图。当然用的话要保证数据库的基本性质。自己实现起来考虑的东西比较麻烦。尤其数据量比较大的时候。问题就比较多了。

其实我这个模拟物化视图,是模拟在真实表基础上的.所以分表分区都可以的.我之前本来想用这个功能，后来发现，还是用触发器吧.有机会多多讨论