MySQL数据库的最大表大小 [英] Maximum table size for a MySQL database

问题描述

MySQL表的最大大小是多少？它是200万在50GB吗？ 500万在80GB？

What is the maximum size for a MySQL table? Is it 2 million at 50GB? 5 million at 80GB?

在大小规模的高端，我需要考虑压缩数据吗？

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?

推荐答案

我曾经使用过一个非常大的（Terabyte +）MySQL数据库。我们所拥有的最大的表是超过十亿行。

I once worked with a very large (Terabyte+) MySQL database. The largest table we had was literally over a billion rows.

它工作。 MySQL大多数时间都正确处理数据。这是非常尴尬，但。

It worked. MySQL processed the data correctly most of the time. It was extremely unwieldy though.

只是备份和存储数据是一个挑战。如果我们需要，需要几天才能恢复表。

Just backing up and storing the data was a challenge. It would take days to restore the table if we needed to.

我们在1000万行范围内有很多表。任何重要的连接到表是太费时，并会永远。因此，我们编写了存储过程来走表和处理连接对'id'的范围。这样，我们一次处理数据10-100,000行（加入ID为1-100,000，然后100,001-200,000等）。这大大快于加入整个表。

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.

在非基于主键的非常大的表上使用索引也困难得多。 Mysql存储索引两个部分 - 它存储索引（主索引除外）作为主键值的索引。因此，索引查找分为两部分：首先MySQL到一个索引，并从它需要找到的主键值，然后它对主键索引进行第二次查找，以找到这些值的位置。

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.

这样做的结果是，对于非常大的表（1-200亿行），对表的索引更具限制性。您需要更少，更简单的索引。甚至做一些不直接在索引上的简单select语句也许永远不会回来。 where子句必须命中索引或忘记索引。

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 must hit indexes or forget about it.

但是所有的说，事情确实工作。我们能够使用MySQL和这些非常大的表，并做计算，并得到正确的答案。

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数据库的最大表大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！