Summary of the storage structure of MySQL InnoDB

Physically, the InnoDB table consists of a shared tablespace, a log file group (redo filegroup), and a table structure definition file. If Innodb_file_per_table is set to ON, a TABLE_NAME.IBD file is generated for each table, in which data, indexes, and internal data dictionary information about the table are stored. The table structure file ends with a. frm, which is independent of the storage engine.

The following is a table spatial diagram of InnoDB:

In the InnoDB storage engine, the default tablespace file is Ibdata1, initialized to 10M, and can be extended as shown in:

In fact, the InnoDB tablespace file is modifiable and can be modified using the following statement:

Innodb_data_file_path=ibdata1:370m;ibdata2:50m:autoextend

when using shared table Space storage, Innodb all of the data is stored in a separate tablespace, and the table space can consist of many files, a table can exist across multiple files, so its size limit is no longer a file size limit, but its own limitations. as you can see from Innodb's official documentation, the maximum table space limit is 64TB, which means thatInnodb 's single-table limit is basically about 64TB , of course, this size is all the other relevant data including all indexes of this table .

when using separate table space storage, the data for each table is stored in a single file, and this time the single table limit becomes the size limit of the file system.

The following is the maximum of individual tablespace files under different platforms.

Operating System file-size Limit
Win32 W/fat/fat32 2GB/4GB
Win32 W/ntfs 2TB (possibly larger)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
MacOS X w/hfs+ 2TB
NetWare W/nss File System 8TB

※ The following are the contents of the MySQL documentation:
Windows users note: Fat and VFAT (FAT32) are not suitable for MySQL production use. You should use NTFS.

Shared tablespace and exclusive tablespace can be converted by parameter innodb_file_per_table, or, if 1, exclusive tablespace is turned on, otherwise shared table storage is turned on.

Independent table spaces are significantly more efficient than sharing in situations where server resources are limited and single-table data is not particularly high. However, MySQL defaults to a shared table space.

The advantages and disadvantages of specific shared tablespace and stand-alone table spaces are as follows:

To share a table space:
Advantages:
You can put the table space into multiple files on each disk (the Tablespace file size is not limited by the size of the table, such as a table can be spread over the files on the different steps). Data and documents are easily managed together.
Disadvantages:
All the data and indexes are stored in a file that will have a very large file, although it is possible to divide a large file into smaller files, but multiple tables and indexes are stored in the table space, so there will be a lot of voids in the table space after a large number of deletions for a table, especially for statistical analysis, Such applications as day-value systems are most unsuitable for sharing table spaces.

Stand-alone tablespace: Set in configuration file (my.cnf): innodb_file_per_table

Advantages:
1. Each table has a self-contained table space.
2. The data and indexes for each table will exist in the table space themselves.
3. You can implement a single table to move through different databases.
4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)
A) The drop table operation automatically reclaims the tablespace, if for statistical analysis or a daily value table, delete a large amount of data can pass: ALTER TABLE TableName ENGINE=INNODB;
b) The use of TRUNCATE table for Innodb-plugin InnoDB also shrinks the space.
c) For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much and there is a chance to process it.
Disadvantages:
Single table increased too large, such as more than 100 g.

※ for innodb_file_per_table-enabled parameter options, only data, index and insert buffers are stored in the. IDB file for each table, and undo information, system transaction information, two write buffers, etc., are stored in the original shared table space.

※ The data segment is the leaf node of the B + tree, and the index segment is the non-indexed node of the B + tree.

The management of the ※INNODB storage engine is done by the engine itself, and the table space consists of scattered pages and segments.

※ The area consists of 64 consecutive pages, each page size is 16K, that is, each zone size is 1MB, when creating a new table, there are 32 pages of fragmented pages to hold the data, the application after the use of the zone, (InnoDB up to 4 zones per request, to ensure the sequential performance of data)

※ Page types are: Data page, undo page, System page, transactional data page, insert buffer bitmap page, and insert buffer free list page.

Summary of the storage structure of MySQL InnoDB