MySQL MySQL表的最大行数是多少

What are the max rows for MySQL table

提问by avon_verma

Note: I've searched for other similar Qs here, and none of the other answered questions are even remotely similar.. With that...I have a question about MySql tables (more precisely, on specific fields from a table- i.e. tweetsor updates).

注：我搜索过其他类似Qs的位置，并没有其他回答问题，甚至是远程类似。随着那......我有一个关于MySQL表的问题（更确切地说，在特定的领域从表-即tweets或updates）。

So the question... what are the maximum amount of rows on an InnoDB table? That is if there is a significant amount of difference between the amounts MyIsam, InnoDB, and others can hold, if there isn't, then, in general. Secondly, if the table gets really large, what are the best practices for storing the data- (same one table, or split/multiple tables/dbs)?

所以问题是…… InnoDB 表上的最大行数是多少？也就是说，如果 MyIsam、InnoDB 和其他人可以持有的数量之间存在显着差异，如果没有，那么一般来说。其次，如果表变得非常大，存储数据的最佳实践是什么（同一个表，或拆分/多个表/dbs）？

I read that twitter gets something like 100 million tweets a day. In the same context, how would my second question apply to something like twitter?

我读到 Twitter 每天收到大约 1 亿条推文。在相同的背景下，我的第二个问题如何适用于 twitter 之类的东西？

回答by atp

There isn't a row limit, but a size limiton an InnoDB database:

InnoDB 数据库没有行限制，但有大小限制：

The minimum tablespace size is 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.

最小表空间大小为 10MB。最大表空间大小为 40 亿个数据库页面 (64TB)。这也是表的最大大小。

You could always horizontally partitionyour tables by storing rows in multiple partitions of the same table, in multiple files.

您始终可以通过将行存储在同一表的多个分区中的多个文件中来对表进行水平分区。

回答by 9000

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.htmlwill allow you to calculate restrictions from your key size.

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html将允许您根据密钥大小计算限制。

But I humbly suggest that you don't want to store info like tweets in a transactional engine with referential integrity and other features of InnoDB, and you definitelydon't store them all in one table in a single DB instance. You put them into lots and lots of independent non-SQL databases that allow you to append fast, and then extract meta-information (like hashtags, RTs, etc) into a more complex database. Presentations on Twitter DB architecture are easy to google (e.g. http://www.slideshare.net/nkallen/q-con-3770885).

但我谦虚地建议您不要在具有参照完整性和 InnoDB 其他功能的事务引擎中存储诸如推文之类的信息，并且绝对不要将它们全部存储在单个数据库实例的一个表中。您将它们放入大量独立的非 SQL 数据库中，以便您快速追加，然后将元信息（如主题标签、RT 等）提取到更复杂的数据库中。Twitter 数据库架构上的演示文稿很容易用谷歌搜索（例如http://www.slideshare.net/nkallen/q-con-3770885）。

If you must store large amounts of data in one table, partitioning is your friend, and possibly Postgres has better support for it. A partitioned table is physically several tables that logically look as one table. You put these sub-tables (partitions) to different disks, independently run maintenance on them, etc. Also, a "star schema" with one very long table that contains only essential columns and a number of tables that store bigger but optional columns may help.

如果你必须在一张表中存储大量数据，分区是你的朋友，而且 Postgres 可能对它有更好的支持。分区表实际上是多个表，在逻辑上看起来像一个表。您将这些子表（分区）放在不同的磁盘上，对它们独立运行维护等。此外，一个“星型模式”具有一个非常长的表，其中仅包含基本列和一些存储更大但可选列的表可能帮助。

回答by Rick James

The 64TB limit for a table has the following exceptions:

表的 64TB 限制有以下例外：

• That assumes innodb_page_size = 16K(the default). That can be set to powers of 2 between 4K and 64K, changing the tablespace limit proportionately.
• A PARTITIONedtable is essentially a bunch of 'sub-tables' organized together and acting as one big table. The pre-5.6.7 limit on number of partitions was 1024. After that, it has been 8192. So multiply the 64TB by that.

• 假设innodb_page_size = 16K（默认）。这可以设置为 4K 和 64K 之间的 2 的幂，按比例更改表空间限制。
• 一张PARTITIONed表本质上是一堆“子表”组织在一起并充当一个大表。5.6.7 之前的分区数限制是 1024。之后是 8192。所以乘以 64TB。

OK, that only gives you a bytelimit. Furthermore, it includes overhead, and indexes. You then need to divide by how big an average record is to get the max number of rows.

好的，那只会给你一个字节限制。此外，它还包括开销和索引。然后，您需要除以平均记录的大小以获得最大行数。

(It is not easy to compute the average record size.)

（计算平均记录大小并不容易。）

Simple answer:

简单回答：

You can probably easily get 1 trillion "small" records in an InnoDB table. With some effort, you might get to 1000 trillion. But I suspect you budget for disk drives would be exhausted before that. Furthermore, it would take years to do all the INSERTsto fill it up!

您可能可以轻松地在 InnoDB 表中获得 1 万亿条“小”记录。通过一些努力，您可能会达到 1000 万亿。但我怀疑在此之前您的磁盘驱动器预算会耗尽。此外，完成所有这些工作需要数年时间INSERTs才能填满它！

So, realistic answer: MySQL can handle an 'unlimited' number of rows.

所以，现实的答案：MySQL 可以处理“无限”数量的行。

Real life? I have heard of a few tables with more than a billion rows, even as much as 15 billion.

现实生活？我听说过一些表的行数超过 10 亿，甚至高达 150 亿。

See my Limits, but it does not say more on the question of Rows.

见我的限制，但它没有说更多关于行的问题。