MySQL表有多少行“太多"? [英] How many rows are 'too many' for a MySQL table?

问题描述

可能重复:
数据库中有多少行?

Possible Duplicate:
How many rows in a database are TOO MANY?

我正在为将要有用户的应用程序构建数据库方案，每个用户在诸如收藏夹"之类的关系表中将有很多行. 每个用户可能有成千上万的收藏夹，并且可能有成千上万的注册用户(随着时间的流逝).

I am building the database scheme for an application that will have users, and each user will have many rows in relation tables such as 'favorites'. Each user could have thousands of favorites, and there could be thousands of registered users (over time).

鉴于用户永远不会被删除，因为这会使其他实体变成孤立的实体，或者也将它们删除(这是不希望的)，因此这些表将永远增长，我想知道结果表是否可以太大(例如:1kk行)，我应该担心这一点，并进行一些操作，例如将旧的和不活跃的用户标记为已删除，并删除仅影响他们的关系(例如收藏夹和其他首选项).

Given that users are never deleted, because that would either leave other entities orphaned, or have them deleted too (which isn't desired), and therefore these tables will keep growing forever, I was wondering if the resulting tables could be too big (eg: 1kk rows), and I should worry about this and do something like mark old and inactive users as deleted and remove the relations that only affect them (such as the favorites and other preferences).

这是要走的路吗?还是MySQL可以轻松处理表中的1kk行?有已知限制吗?还是完全依赖于硬件?

Is this the way to go? Or can mysql easily handle 1kk rows in a table? Is there a known limit? Or is it fully hardware-dependant?

推荐答案

我同意klennepette和Brian的一些警告.

I agree with klennepette and Brian - with a couple of caveats.

如果数据与生俱来是关系型的，并且受与SQL配合使用的查询的影响，那么您应该能够扩展到数亿条记录，而无需特殊的硬件要求.

If your data is inherently relational, and subject to queries that work well with SQL, you should be able to scale to hundreds of millions of records without exotic hardware requirements.

您将需要投资建立索引，查询调优，并为提高速度而偶尔牺牲关系模型.设计表时，至少应该点点性能-例如，对于键，首选整数而不是字符串.

You will need to invest in indexing, query tuning, and making the occasional sacrifice to the relational model in the interests of speed. You should at least nod to performance when designing tables – preferring integers to strings for keys, for instance.

但是，如果您有以文档为中心的要求，需要免费文本搜索或有很多层次关系，则可能需要再次查看.

If, however, you have document-centric requirements, need free text search, or have lots of hierarchical relationships, you may need to look again.

如果您需要ACID事务，则可能会比不关心事务的时候更早遇到可伸缩性问题(尽管在实践中这仍然不太可能影响您)；如果您有长期运行的事务或复杂的事务，则可伸缩性会迅速下降.

If you need ACID transactions, you may run into scalability issues earlier than if you don't care about transactions (though this is still unlikely to affect you in practice); if you have long-running or complex transactions, your scalability decreases quite rapidly.

我建议从头开始构建项目，同时要考虑到可伸缩性要求.我过去所做的工作是建立一个测试环境，该环境中填充了数百万条记录(我使用DBMonster，但不确定是否仍然存在)，并使用负载测试工具(例如，杰米特(Jmeter).

I'd recommend building the project from the ground up with scalability requirements in mind. What I've done in the past is set up a test environment populated with millions of records (I used DBMonster, but not sure if that's still around), and regularly test work-in-progress code against this database using load testing tools like Jmeter.

这篇关于MySQL表有多少行“太多"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！