Maximum table size for a MySQL database

问题:

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?

回答1:

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

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.

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.

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.

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.

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.

回答2:

About your first question, the effective maximum size for the database is 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 table size limits. And MyISAM works differently from InnoDB. So any tables will be dependent on those limits.

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 full error page.

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.

回答3:

See this 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.