对超大数量数据的处理

[cuitaxuexunmei]

有张基础数据表，如下：
CREATE TABLE if not exists business_monitor_table(
dev_num varchar(15) not null, // 设备编号
statistical_code varchar(10) not null, // 统计码
response_code varchar(8) not null, // 应答码
out_account varchar(25), // 出款方账户
transit_account varchar(25), // 中转账户
in_account varchar(25), // 入款方账户
trade_money decimal(7,2), // 交易金额
admin_account varchar(25), // 管理员账号
trade_time datetime not null, // 交易时间
statistical_time datetime not null, // 统计时间
currency_code varchar(3) not null, // 货币代码
accept_bank_code varchar(20), // 受理行机构代码
accept_bank_inf varchar(20), // 受理行信息
sendout_bank_code varchar(20), // 发卡行机构代码
admin_num varchar(8) not null, // 管理员号
card_type varchar(3)， // 卡种类型
foreign key(dev_num) references dev_register_table (dev_num) on update cascade on delete cascade,
foreign key(statistical_code) references statistical_code_table(statistical_code) on update cascade on delete cascade); // 外键约束

按需求设计，这张表每天大约产生70万--700万的数据，每月产生2000万--2亿的大量数据，当然这是极端情况，就目前的情况，每天最多产生70万、每月产生2000万的数据，我用的是MySQL5.1，我的设计方法是先分表后分区，就是每月自动生成一个表，然后每个表生成31个区，每天会自动结算，将当天的数据整理到其他的表中，现在有以下问题：
1。目前我不清楚MySQL5.1的分区是否稳定。
2。分区不支持外键，我有两个外键就得去掉，对这个表而言，外键是有好啊，还是没有好。
3。这个设计方法是否可行，MySQL能存储这么大的数据吗。
4。怎样设计大数据量的数据库，有没有其他的方法或者其他注意的地方，请高手指点。

[feixianxxx]

对于外键好不好是仁者见仁智者见智的事情。。。
外键最大好处就是保持数据完整性...这对于数据也是非常重要的...

mysql是可以承受的

大数据量的数据库设计注意点 我想首先表设计的合理性是最先也是最重要的一步(这里涉及到反规范化设计)...设计定型后,索引的设计也是很重要的 要在查询和插入之前做权衡 ....之后的数据表的数据处理 主要是归档和分区

[cuitaxuexunmei]

[Quote=引用 5 楼 feixianxxx 的回复:]
对于外键好不好是仁者见仁智者见智的事情。。。
外键最大好处就是保持数据完整性...这对于数据也是非常重要的...

mysql是可以承受的

大数据量的数据库设计注意点 我想首先表设计的合理性是最先也是最重要的一步(这里涉及到反规范化设计)...设计定型后,索引的设计也是很重要的 要在查询和插入之前做权衡 ....之后的数据表的数据处理 主要是归档和分区
[/Quote]
上面两位高手都提到了‘归档’，我对这个术语不是很明白，这个表每天插入大量的数据，在凌晨时对这一天的业务进行结算，整理到其他的表中，然后这些数据就很少再用到，只有在客户出现问题要进行详细查询的时候才用到，那也查的很少，我需要归档吗

[布道]

mysql是可以承载这么点数据量的，但是你说的那个每天”归档“就有点过分了，归档时是为了解决什么什么问题呢？select上的问题？没有必要呀，insert,update,delete都是一样的操作，不能解决任何实质上的问题

[cuitaxuexunmei]

[Quote=引用 7 楼 itsoftchenfei 的回复:]
mysql是可以承载这么点数据量的，但是你说的那个每天”归档“就有点过分了，归档时是为了解决什么什么问题呢？select上的问题？没有必要呀，insert,update,delete都是一样的操作，不能解决任何实质上的问题
[/Quote]
每天70多万的数据已经不小了吧，我每天结算时为了统计业务的，如果到近2000万的表中进行查询求和，应该很慢了。

[feixianxxx]

[Quote=引用 6 楼 cuitaxuexunmei 的回复:]

引用 5 楼 feixianxxx 的回复:
对于外键好不好是仁者见仁智者见智的事情。。。
外键最大好处就是保持数据完整性...这对于数据也是非常重要的...

mysql是可以承受的

大数据量的数据库设计注意点 我想首先表设计的合理性是最先也是最重要的一步(这里涉及到反规范化设计)...设计定型后,索引的设计也是很重要的 要在查询和插入之前做权衡 ....之后的数据表的数据处理 ……
[/Quote]
归档就是 把不使用或者极少使用的数据分离出那个业务表 放到其他地方去

[ciyuanlong]

每天70w数据真的不大

不知道你的业务是什么

按天分做分区表可以吗？

[ACMAIN_CHM]

1。目前我不清楚MySQL5.1的分区是否稳定。
稳定，在目前的MYSQL的BUG报告中关于分区的报告并不多。

2。分区不支持外键，我有两个外键就得去掉，对这个表而言，外键是有好啊，还是没有好。
分区目前只有MYISAM存储引擎支持，而MYISAM是不支持外键的。
针对于外键，有很多种观点。一种就是认为在数据库中使用外键会影响效率，不如在程序中由代码实现。
如果按照你的情况来说，我也趋向于不设置外键，要知道，在每次记录插入的时候，MYSQL数据库都需要做外键检查，显然会影响插入的速度。如果你的程序中能够确保dev_num，statistical_code不出现其它值，则完全可以不需要这两个外键。


3。这个设计方法是否可行，MySQL能存储这么大的数据吗。
可行，MYSQL可以支持。

4。怎样设计大数据量的数据库，有没有其他的方法或者其他注意的地方，请高手指点。
主要是针对你的查询来考虑，总体上来说，就是分区，归档，

[cuitaxuexunmei]

谢谢你的帮助，分区也支持InnoDB存储引擎的，我就用的InnoDB建的分区已经成功了，只是不支持外键。

[ACMAIN_CHM]

是的，你是正确的，分区中可以使用INNODB。

[php739051932]

这样好的贴这被子怕再也没有了！

[cuitaxuexunmei]

[Quote=引用 10 楼 ciyuanlong 的回复:]
每天70w数据真的不大

不知道你的业务是什么

按天分做分区表可以吗？
[/Quote]
70w不大，就是说用MySQL操作这些数据，速度没有问题

银行业务

可以

[cuitaxuexunmei]

我再补充一下，我的这张表没有主键，我想加一个自动增加的无意义主键，“id int primary key auto_increment”,这样的话我就不能分区了，必须把分区的那个字段也设成主键，但是感觉把分区的那个字段设成主键没有必要，请问有必要添加自动增加的无意义主键id吗

[loveflea]

自增列 可以设置为索引,不一定是primary key or unique key才可以哈!

[cuitaxuexunmei]

[Quote=引用 13 楼 loveflea 的回复:]
自增列 可以设置为索引,不一定是primary key or unique key才可以哈!
[/Quote]
其他的列我设置了索引，我是为了使这个表有主键，才设置自增列，不是为了设置索引，如果表有没有主键都可以，不影响性能，我就不设置自增列了

[zuoxingyu]

如果没有定义主键，INNODB会试着使用唯一的非空索引（UNIQUE NONNULLABLE INDEX)来代替。如果没有这样的索引，INNODB就会定义隐藏的主键然后在上面进行聚集。INNODB只会聚集在同一页面中的记录，包含相邻键值的页面也许会相距甚远。

我觉得你还是建这么一个自增字段。

[cuitaxuexunmei]

[Quote=引用 15 楼 zuoxingyu 的回复:]
如果没有定义主键，INNODB会试着使用唯一的非空索引（UNIQUE NONNULLABLE INDEX)来代替。如果没有这样的索引，INNODB就会定义隐藏的主键然后在上面进行聚集。INNODB只会聚集在同一页面中的记录，包含相邻键值的页面也许会相距甚远。

我觉得你还是建这么一个自增字段。
[/Quote]
谢谢你的帮助，我为这张表建立了个组合索引（statistical_time,dev_num, statistical_code, admin_num），如果定义主键对性能有好处，我就为这个分区表建个组合主键了，把(id,statistical_time)定义成组合主键。

[mynameismavk]

这么有水准的帖子一定要顶。

谢谢各位的解答。