表设计疑问:关于大量数据的存储 当前有user表,存储用户信息,用户所属不同的组要对用户行为进行记录(假设记录到表act),当前预估数据量为 3000条/人/天,预估用户数量为10000,所以[act]表每天的记录约为3千万条这样,对于一个每天有千万数量级的表,还要进行按用户,按用户所属组的数据查询和统计等运算,肯定会有较大难度请问各位,这个[act]表如何设计是好? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 没看到你的CREATE TABLE语句。如果大量数据,则应该考虑分区表。 这个分配不一定合理,要看act是干嘛的,如果是给网站所有者,做用户分析的,比如购物网站,那么这个分配就可能是合理的,如果是给用户或者其好友查询的,比如SNS,那么这个分配就不一定合理了,可能需要用到用户ID之类。 另外,有的应用场景下,与其生成这样的act:张三 | 2011-10-01 00:00:01 | X | 1张三 | 2011-10-01 00:01:11 | X | 1张三 | 2011-10-01 00:02:18 | Y | 2张三 | 2011-10-01 00:05:21 | X | 2张三 | 2011-10-01 00:12:59 | Y | 1张三 | 2011-10-02 08:55:11 | Y | 1张三 | 2011-10-03 14:32:00 | X | 5不如直接张三 | 2011-10-01 | X | 4张三 | 2011-10-01 | Y | 3张三 | 2011-10-02 | Y | 1张三 | 2011-10-03 | X | 5 IMHO1 在不增加业务操作太大压力的情况下,优先考虑把部分统计结果记录到表中2 考虑最合适的分区3 在没有分区之前,不必考虑分库 用SSAS分析.才这么点数据而已.分析以后的数据永久保存供历史查询. 业务上要求历史数据需要保存,而且需要详细数据。有几个纠结的地方分机器/分库/分表/分区 我没在生产中用过分区表,单个分区表能保存多大的数据量?如果不按用户/用户组分表,对查询性能影响有多大?因为大部分的查询都是针对用户或用户组的。 分开之后,主要影响就是应用层的统计分析程序需要特别处理。统计 可以按天/按用户做好一些统计,但是会遇到些问题,比如统计需求更改,比如统计好后那个时间段又有新数据到来,需要重新统计。备份 必须考虑到备份机器的成本,用主从备份还是文件备份? 对于INNODB类型的表,当数据库稍微有一点量时文件备份就没办法了,只能用MyISAM。 而主从备份对备份机器的配置要求比较高。 之前做过类似的大数据量应用,基本原则就是使用MyISAM引擎, 分服务器、分数据库、分表。 首先有一个系统表user_table_info,记录用户在某个时间段的数据存在哪台机器哪个库的哪个表里。 人工判断是否需要加机器;程序判断是否需要分库、分表,并更新user_table_info信息。 服务器到磁盘空间满为止; 单个数据库的大小保持在2000个表(6000个文件)以内,以不影响单个目录ext3文件系统读取效率为准; 单个表不超过50万条记录,原则是最常用的按用户和时间段查询速度和文件备份方式的开销(因为历史表是不需要重新备份的)。 按用户/天/月提供统计数据,存入其他数据库。 备份方式除系统数据库采用主从备份外,详细数据采用文件备份方式,使用sata硬盘。 这种方式的优点就是对海量数据的扩充支持比较好,缺点是业务系统查询时比较复杂,而且需要新类型的统计结果时比较麻烦。 现在这个项目和之前的比,单个用户的数据量不会太大,所以不想和上项目一样把单个用户的数据也分开,这样业务系统查询起来比较方便,带来的问题是采用文件备份方式的话只能拷贝全部文件了,考虑到数据库操作应该主要是插入和查询操作,因此用MySQL主从备份方式还是可以接受的。 按天汇总这种操作还是需要,尽管会有如上所述的问题。 如果用这种方式,有个问题是从用户到服务器、数据库、表的映射方式如何设计?是按用户名或注册时间之类的参数固定hash算法,还是有好点的方案,能根据当前的服务器负载,自动分配到负载最低的服务器? 已知条件太少了,这种问题就要具体问题具体分析了。就好像3楼说的,购物网站、sns还是其他的?不同的需求不同的设计。 您好!大致的结构是:用户组表 id group用户表 id groupid用户行为表 userid 用户行为(例如用户所在位置,停留时间等)需求:可以查询历史任一时间段的行为 详细数据;对历史任一时间段的行为做统计; 非常感谢 ACMAIN_CHM 和 shine333 的答复,也谢谢各位同学的关注! 希望我更清楚地描述了我提出的问题,期待各位建议! 可以只保留一天的数据,超过一天的数据可以备份到另一个表中,当要查询时,可以查询备份的table。 以前接触过一点数据挖掘,每周每月统计都是增量形式存储在一个新的数据库中,至于原始数据,这个量太大,dba方面的东东刚开始学习,没啥建议。 这个sql语句如何优化? ACCESS的资料导入到MYSQL里 navicat8连接远程数据库 mysql拼音首字母查文字 ACMAIN_CHM 请进。昨天的一个贴子。 我是初学者,使用mysql时候总是出现command denied to user 怎么做到一条语句查询并修改 show full processlist Sleeping的连接数据可以删除吗 Mysql的存储过程:查找一张表的数据再根据查出来的数据去另外一张表中取另外的数据 存储过程里,动态添加字段,变量不起作用,WHY? MYSQL表加索引问题? 求一个sql语句(800万条记录)
张三 | 2011-10-01 00:00:01 | X | 1
张三 | 2011-10-01 00:01:11 | X | 1
张三 | 2011-10-01 00:02:18 | Y | 2
张三 | 2011-10-01 00:05:21 | X | 2
张三 | 2011-10-01 00:12:59 | Y | 1
张三 | 2011-10-02 08:55:11 | Y | 1
张三 | 2011-10-03 14:32:00 | X | 5不如直接
张三 | 2011-10-01 | X | 4
张三 | 2011-10-01 | Y | 3
张三 | 2011-10-02 | Y | 1
张三 | 2011-10-03 | X | 5
1 在不增加业务操作太大压力的情况下,优先考虑把部分统计结果记录到表中
2 考虑最合适的分区
3 在没有分区之前,不必考虑分库
分析以后的数据永久保存供历史查询.
业务上要求历史数据需要保存,而且需要详细数据。有几个纠结的地方
分机器/分库/分表/分区
我没在生产中用过分区表,单个分区表能保存多大的数据量?如果不按用户/用户组分表,对查询性能影响有多大?因为大部分的查询都是针对用户或用户组的。
分开之后,主要影响就是应用层的统计分析程序需要特别处理。
统计
可以按天/按用户做好一些统计,但是会遇到些问题,比如统计需求更改,比如统计好后那个时间段又有新数据到来,需要重新统计。
备份
必须考虑到备份机器的成本,用主从备份还是文件备份? 对于INNODB类型的表,当数据库稍微有一点量时文件备份就没办法了,只能用MyISAM。 而主从备份对备份机器的配置要求比较高。
之前做过类似的大数据量应用,基本原则就是使用MyISAM引擎, 分服务器、分数据库、分表。 首先有一个系统表user_table_info,记录用户在某个时间段的数据存在哪台机器哪个库的哪个表里。
人工判断是否需要加机器;程序判断是否需要分库、分表,并更新user_table_info信息。 服务器到磁盘空间满为止; 单个数据库的大小保持在2000个表(6000个文件)以内,以不影响单个目录ext3文件系统读取效率为准; 单个表不超过50万条记录,原则是最常用的按用户和时间段查询速度和文件备份方式的开销(因为历史表是不需要重新备份的)。
按用户/天/月提供统计数据,存入其他数据库。
备份方式除系统数据库采用主从备份外,详细数据采用文件备份方式,使用sata硬盘。
这种方式的优点就是对海量数据的扩充支持比较好,缺点是业务系统查询时比较复杂,而且需要新类型的统计结果时比较麻烦。
现在这个项目和之前的比,单个用户的数据量不会太大,所以不想和上项目一样把单个用户的数据也分开,这样业务系统查询起来比较方便,带来的问题是采用文件备份方式的话只能拷贝全部文件了,考虑到数据库操作应该主要是插入和查询操作,因此用MySQL主从备份方式还是可以接受的。 按天汇总这种操作还是需要,尽管会有如上所述的问题。
如果用这种方式,有个问题是从用户到服务器、数据库、表的映射方式如何设计?是按用户名或注册时间之类的参数固定hash算法,还是有好点的方案,能根据当前的服务器负载,自动分配到负载最低的服务器?
大致的结构是:
用户组表 id group
用户表 id groupid
用户行为表 userid 用户行为(例如用户所在位置,停留时间等)需求:
可以查询历史任一时间段的行为 详细数据;
对历史任一时间段的行为做统计;