Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the restrictions on the size of a single MySQL table". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What are the restrictions on the size of MySQL single table

In the older version of MySQL3.22, the single table size of MySQL was 4GB, and the storage engine of MySQL at that time was the ISAM storage engine. However, with the advent of the MyISAM storage engine, that is, starting with MySQL3.23, the maximum limit for MySQL single tables has been extended to 64PB (official documentation shows). In other words, from the current technical environment, the MyISAM storage engine single table size limit of MySQL database is no longer determined by the MySQL database itself, but by the file system on the OS of the host.

However, Innodb, one of the most popular storage engines of MySQL, has two strategies for storing data, one is shared tablespace storage, and the other is exclusive tablespace storage.

When using shared tablespace storage, all data in Innodb is stored in a single tablespace, which can be composed of many files, and a table can exist across multiple files, so its size limit is no longer a file size limit, but its own limit. What you can see from the official documentation of Innodb is that the maximum tablespace limit of Innodb is 64TB, that is, the single table limit of Innodb is basically around 64TB, of course, this size includes all indexes of the table and other relevant data.

When exclusive tablespaces are used to store Innodb tables, the data of each table is stored in a separate file, so the single table limit becomes the size limit of the file system.

Here is a little bit of information collected, but not all of it is accurate:

Operating system size limit

Java code

Win32w/FAT/FAT322GB/4GB

Win32w/NTFS2TB (maybe larger)

Linux2.2-Intel32-bit2GB (LFS:4GB)

Linux2.4+4TB (ext3)

Solaris9/1016TB

NetWarew/NSSfilesystem8TB

MacOSXw/HFS+2TB

Win32w/FAT/FAT322GB/4GB

Win32w/NTFS2TB (maybe larger)

Linux2.2-Intel32-bit2GB (LFS:4GB)

Linux2.4+4TB (ext3)

Solaris9/1016TB

NetWarew/NSSfilesystem8TB

MacOSXw/HFS+2TB

What are the restrictions on the size of MySQL single table

The following is what is in the MySQL document:

Please note to Windows users: FAT and VFAT (FAT32) are not suitable for MySQL production. NTFS should be used.

By default, the maximum size allowed for MyISAM created by MySQL is 4GB. You can use the SHOWTABLESTATUS statement or myisamchk-dvtbl_name to check the maximum size of the table. See Section 13.5.4, "SHOW Grammar".

If you need to use MyISAM tables larger than 4GB (and your operating system supports large files), use CREATETABLE statements that allow AVG_ROW_LENGTH and MAX_ROWS options. After you create the table, you can also use ALTERTABLE to change these options to increase the maximum allowable capacity of the table.

This is the end of the content of "what are the restrictions on the size of the MySQL single table". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.