MySQL 数据库的最大表大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48633/
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
Maximum table size for a MySQL database
提问by Caleb Elston
What is the maximum size for a MySQL table? Is it 2 million at 50GB? 5 million at 80GB?
MySQL 表的最大大小是多少？50GB 是 200 万吗？80GB 500 万？
At the higher end of the size scale, do I need to think about compressing the data? Or perhaps splitting the table if it grew too big?
在规模规模的高端，我是否需要考虑压缩数据？或者，如果桌子变得太大，可能会拆分桌子？
回答by Kevin Bedell
I once worked with a very large (Terabyte+) MySQL database. The largest table we had was literally over a billion rows.
我曾经使用过一个非常大的（Terabyte+）MySQL 数据库。我们拥有的最大的表实际上超过了 10 亿行。
It worked. MySQL processed the data correctly most of the time. It was extremely unwieldy though.
有效。MySQL 大部分时间都正确处理了数据。虽然它非常笨拙。
Just backing up and storing the data was a challenge. It would take days to restore the table if we needed to.
仅备份和存储数据是一项挑战。如果我们需要，恢复该表需要几天时间。
We had numerous tables in the 10-100 million row range. Any significant joins to the tables were too time consuming and would take forever. So we wrote stored procedures to 'walk' the tables and process joins against ranges of 'id's. In this way we'd process the data 10-100,000 rows at a time (Join against id's 1-100,000 then 100,001-200,000, etc). This was significantly faster than joining against the entire table.
我们在 10-1 亿行范围内有许多表。对表的任何重要连接都太耗时，而且会花费很长时间。所以我们编写了存储过程来“遍历”表并根据“id”的范围处理连接。通过这种方式，我们将一次处理 10-100,000 行数据（根据 id 的 1-100,000 然后 100,001-200,000 等加入）。这比加入整个表要快得多。
Using indexes on very large tables that aren't based on the primary key is also much more difficult. Mysql stores indexes in two pieces -- it stores indexes (other than the primary index) as indexes to the primary key values. So indexed lookups are done in two parts: First MySQL goes to an index and pulls from it the primary key values that it needs to find, then it does a second lookup on the primary key index to find where those values are.
在不基于主键的非常大的表上使用索引也更加困难。Mysql 将索引存储为两部分——它将索引（主索引除外）存储为主键值的索引。因此，索引查找分为两部分：首先，MySQL 访问索引并从中提取需要查找的主键值，然后对主键索引进行第二次查找以找到这些值的位置。
The net of this is that for very large tables (1-200 Million plus rows) indexing against tables is more restrictive. You need fewer, simpler indexes. And doing even simple select statements that are not directly on an index may never come back. Where clauses musthit indexes or forget about it.
这样做的结果是，对于非常大的表（1-2 亿多行），对表进行索引的限制更大。您需要更少、更简单的索引。并且即使不直接在索引上执行简单的 select 语句也可能永远不会回来。Where 子句必须命中索引或忘记它。
But all that being said, things did actually work. We were able to use MySQL with these very large tables and do calculations and get answers that were correct.
但话虽如此，事情确实奏效了。我们能够将 MySQL 与这些非常大的表一起使用，并进行计算并获得正确的答案。
回答by Fernando Barrocal
About your first question, the effective maximum size for the databaseis usually determined by operating system, specifically the file size MySQL Server will be able to create, not by MySQL Server itself. Those limits play a big role in tablesize limits. And MyISAM works differently from InnoDB. So any tables will be dependent on those limits.
关于您的第一个问题，数据库的有效最大大小通常由操作系统决定，特别是 MySQL Server 能够创建的文件大小，而不是 MySQL Server 本身。这些限制在表大小限制中发挥着重要作用。MyISAM 的工作方式与 InnoDB 不同。因此，任何表都将取决于这些限制。
If you use InnoDB you will have more options on manipulating table sizes, resizing the tablespace is an option in this case, so if you plan to resize it, this is the way to go. Give a look at The table is fullerror page.
如果您使用 InnoDB，您将有更多关于操作表大小的选项，在这种情况下调整表空间大小是一个选项，因此如果您计划调整它的大小，这是要走的路。看看表格是完整的错误页面。
I am not sure the real record quantity of each table given all necessary information (OS, Table type, Columns, data type and size of each and etc...) And I am not sure if this info is easy to calculate, but I've seen simple table with around 1bi records in a couple cases and MySQL didn't gave up.
在给出所有必要信息（操作系统、表类型、列、数据类型和每个表的大小等...）的情况下，我不确定每个表的真实记录数量，我不确定这些信息是否容易计算，但我在几个案例中已经看到了包含大约 1bi 条记录的简单表，MySQL 并没有放弃。
回答by clb4u
See this http://www.clb4u.com/2013/10/scalability-and-limits-of-mysql.html
看到这个http://www.clb4u.com/2013/10/scalability-and-limits-of-mysql.html
Support for large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 200,000 tables and about 5,000,000,000 rows.
支持大型数据库。我们将 MySQL Server 与包含 5000 万条记录的数据库一起使用。我们还知道使用 MySQL Server 的用户有 200,000 个表和大约 5,000,000,000 行。
