MySQL MySQL表有多少行“太多”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5350581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How many rows are 'too many' for a MySQL table?
提问by HappyDeveloper
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).
我正在为将有用户的应用程序构建数据库方案，并且每个用户在关系表中都有许多行，例如“收藏夹”。每个用户可能有数千个收藏夹，并且可能有数千个注册用户（随着时间的推移）。
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).
鉴于用户永远不会被删除，因为这要么会使其他实体成为孤立的，要么也将它们删除（这是不希望的），因此这些表将永远保持增长，我想知道结果表是否可能太大（例如：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?
这是要走的路吗？或者 mysql 可以轻松处理表中的 1kk 行吗？有已知的限制吗？还是完全依赖于硬件？
采纳答案by Neville Kuyt
I agree with klennepette and Brian - with a couple of caveats.
我同意 klennepette 和 Brian 的观点 - 有几个警告。
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.
如果您的数据本质上是相关的，并且受制于与 SQL 配合良好的查询，那么您应该能够扩展到数亿条记录，而无需特殊的硬件要求。
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.
但是，如果您有以文档为中心的需求、需要自由文本搜索或有很多层次关系，您可能需要再次查看。
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.
如果你需要 ACID 事务，你可能会比不关心事务更早遇到可扩展性问题（尽管这在实践中仍然不太可能影响你）；如果您有长时间运行或复杂的事务，您的可扩展性会迅速下降。
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.
我建议从头开始构建项目，并考虑到可扩展性要求。我过去所做的是设置一个包含数百万条记录的测试环境（我使用了 DBMonster，但不确定它是否仍然存在），并使用负载测试工具定期针对该数据库测试正在进行的代码米特。
回答by Jon Black
Here's an example that demonstrates what can be achived using a well designed/normalised innodb schema which takes advantage of innodb's clustered primary key indexes (not available with myisam). The example is based on a forum with threads and has 500 million rows and query runtimes of 0.02 seconds while under load.
这是一个示例，它演示了使用精心设计/规范化的 innodb 模式可以实现的目标，该模式利用了 innodb 的聚集主键索引（不适用于 myisam）。该示例基于一个带有线程的论坛，在负载下有 5 亿行和 0.02 秒的查询运行时间。
回答by Brian
Millions of rows is fine, tens of millions of rows is fine - provided you've got an even remotely decent server, i.e. a few Gbs of RAM, plenty disk space. You will need to learn about indexes for fast retrieval, but in terms of MySQL being able to handle it, no problem.
数百万行很好，数千万行也很好 - 前提是您有一个甚至远程不错的服务器，即几 Gbs 的 RAM，充足的磁盘空间。您将需要了解用于快速检索的索引，但就 MySQL 能够处理它而言，没问题。
回答by klennepette
It is mostly hardware dependant, but having that said MySQL scales pretty well. I wouldn't worry too much about table size, if it does become an issue later on you can always use partitioningto ease the stress.
它主要依赖于硬件，但据说 MySQL 的扩展性很好。我不会太担心表的大小，如果以后确实成为问题，您可以随时使用分区来缓解压力。
