Summary of various size restrictions of MySQL database table

Time:2021-1-10

All the items in this article are summarized from the official website of mysql5.6

table-size-limit

1. The number of columns in MySQL table is limited

1.1 MySQL has a hard limit of 4096 columns per table

1.2 constraints of InnoDB storage engine: the maximum number of columns in each table is 1017;

2. Restriction of each row of data in MySQL table

2.1 the maximum row size of MySQL table is 65535 bytes

2.2 InnoDB limits the maximum page size of row size to half a page, and the default page size is 16K, that is, row size < 8K;

3. The limitation of the total amount of data in MySQL table

3.1 the total size limit of a table depends on the file limit of the operating system

1) For windows FAT32, the biggest is 4G

If you want to exceed 4G, use NTFS on 64 bit windows;

2) Other Windows files on the number of restrictions

On windows, the path (including file name) of a table space file cannot exceed max_ Path restriction.

Before windows 10, Max_ The path limit is 260 characters. Starting from the version of windows 10 1607, Max is removed from the common Win32 file and directory functions_ Path restrictions, but new behaviors must be enabled.

3.2 by default, the maximum size of MyISAM data and index files is 256tb, but this limit can be changed to 65536tb

3.3 table size limit of InnoDB: 64t (depending on page size, see 5 below)

4. The number of MySQL database tables: Unlimited (InnoDB allows 4 billion tables)

MySQL has no limit on the number of tables.

The underlying file system may limit the number of files that represent the table.

A single storage engine may impose engine specific constraints: InnoDB allows up to 4 billion tables

5. To sum up – summarize the limitations of InnoDB

5.1 After MySQL 5.6, the maximum number of InnoDB columns is 1017

5.2 a InnoDB table can have up to 64 secondary indexes

5.3 the maximum length of the default index prefix is 767 bytes

5.4 a maximum of 16 columns are allowed in the joint index. If there are too many columns, an error will be reported

5.5 the maximum row size of InnoDB is half page (less than 8K – default)

Because the default page size is 16K, it is required to be less than half page size, that is, less than 8K;

innodb_page_sizeThis can be changed to 4K, 8K; in this way, the rowsize will be limited to less than 2K, 4K;

Although InnoDB internally supports row sizes larger than 65535 bytes, MySQL itself imposes a 65535 row size limit on the combined sizes of all columns. -“Cross page” and “other settings”?

5.6 the maximum combined size of InnoDB log file is 512g

5.7 InnoDB table space size limit 64t (the maximum table space size is also the maximum table size)

The minimum table space size is slightly larger than 10MB

The maximum table space size depends on the page size of InnoDB:

InnoDB Page Size Maximum Tablespace Size
4KB 16TB
8KB 32TB
16KB 64TB

Reference 1:Limits on Table Column Count and Row Size

Summary of various size restrictions of MySQL database table

Reference 2:Limits on Table Size