Detailed explanation of InnoDB storage files in MySQL

Physically speaking, an InnoDB table consists of a shared tablespace file (ibdata1), an exclusive tablespace file (ibd), a table structure file (.frm), and log files (redo files, etc.).

1. Table structure file

When you create any data table in MYSQL, there will be a .frm file for the corresponding table in the database directory corresponding to its data directory. The .frm file is used to save the metadata (meta) information of each data table, including the definition of the table structure, etc. The .frm file has nothing to do with the database storage engine, that is, any data table of the storage engine must have a .frm file, named as the data table name.frm, such as user.frm. The .frm file can be used to restore the table structure when the database crashes.

2. Tablespace files

(1) Table space structure analysis

The following is the table space structure diagram of InnoDB:

The data segment is the leaf node of the B+ tree, and the index segment is the non-leaf node of the B+ tree. The management of the InnoDB storage engine is completed by the engine itself, and the tablespace is composed of scattered segments. A segment contains multiple extents.

An extent consists of 64 consecutive pages, each of which is 16K in size, that is, each extent is 1MB in size. When creating a new table, 32-page fragments are used to store data first, and then the extent is applied after the data is used up (InnoDB applies for up to 4 extents at a time to ensure the sequential performance of the data)
The page types are: data page, undo page, system page, transaction data page, insert buffer bitmap page, and insert buffer free list page.

(2) Exclusive tablespace file

If innodb_file_per_table is set to on, the system will generate a table_name.ibd file for each table. In this file, the data, indexes, and internal data dictionary information related to the table are stored.

(3) Shared tablespace files

In the InnoDB storage engine, the default tablespace file is ibdata1 (mainly storing shared tablespace data), which is initialized to 10M and can be expanded, as shown in the following figure:

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

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

When using the shared tablespace storage method, all Innodb data is stored in a separate tablespace, which can be composed of many files. A table can exist across multiple files, so its size limit is no longer the file size limit, but its own limit. From the official documentation of Innodb, we can see that the maximum limit of its table space is 64TB, that is to say, the single table limit of Innodb is basically around 64TB. Of course, this size includes all indexes of this table and other related data.

When using separate tablespace storage, the data of each table is stored in a separate file. At this time, the single table limitation becomes the size limitation of the file system.

The following are the maximum sizes of individual tablespace files on 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 is from the MySQL documentation:

Note for Windows users: FAT and VFAT (FAT32) are not suitable for production use of MySQL. NTFS should be used.

(4) Shared tablespace and exclusive tablespace

Shared tablespaces and exclusive tablespaces refer to the way data is stored.

Shared tablespace: All table data and index files of a database are placed in one file. The default file path of this shared tablespace is in the data directory. The default file name is: ibdata1 and is initialized to 10M.

Exclusive tablespace: Each table will be generated and stored in an independent file (.ibd file, which includes the data content and index content of a single table).

1) Storage content comparison

After using exclusive tablespace:

The data, indexes, and insert buffers corresponding to each table are stored in an exclusive tablespace (.idb file)

The undo information, system transaction information, secondary write buffer, etc. corresponding to each table are still stored in the original shared table space (ibdata1 file)

2) Comparison of features

The specific advantages and disadvantages of shared tablespaces and independent tablespaces are as follows:

Shared tablespace:

advantage:

The tablespace can be divided into multiple files and stored on different disks (the size of the tablespace file is not limited by the size of the table, for example, a table can be distributed on different files).

Data and files are put together for easy management.

shortcoming:

If all data and indexes are stored in one file, there will be a very large file. Although a large file can be divided into multiple small files, multiple tables and indexes are mixed and stored in the table space. In this way, after a large number of deletion operations are performed on a table, there will be a large number of gaps in the table space. Especially for applications such as statistical analysis and logging systems, it is least suitable to use shared table spaces.

Independent tablespace: (set innodb_file_per_table in the configuration file (my.cnf))

advantage:

1. Each table has its own separate tablespace.
2. The data and indexes of each table are stored in their own tablespace.
3. A single table can be moved between different databases.
4. Space can be recycled

For tables using independent tablespaces, no matter how they are deleted, tablespace fragmentation will not affect performance too seriously, and there is still a chance to deal with it.

a) Drop table operation automatically reclaims table space

b) If it is a statistical analysis or daily value table, after deleting a large amount of data, you can shrink the unused space by: alter table TableName engine=innodb;

c) For Innodb using innodb-plugin, using truncate table will also shrink the space.

5. When server resources are limited and the data in a single table is not particularly large, independent tablespaces are obviously more efficient than shared tablespaces. However, MySQL uses shared tablespaces by default.

shortcoming:

The size of a single table may be too large, such as more than 100 GB.

3) Conversion between shared tablespace and exclusive tablespace

Modify the exclusive empty tablespace configuration and configure the following parameters

innodb_data_home_dir = "/user/local/mysql/var" The directory where the database files are stored

innodb_log_group_home_dir = "/user/local/mysql/var" log storage directory

innodb_data_file_path=ibdata1:10M:autoextend sets a separate file (shared data file) named ibdata1 with an expandable size of 10MB. No location is given for the file, so the default is to be in the MySQL data directory.

innodb_file_per_table=1 Whether to use shared or exclusive tablespace (1: use exclusive tablespace, 0: use shared tablespace)

Check the innodb_file_per_table variable. If it is OFF, it means that a shared tablespace is used (by default, the tablespace used is a shared tablespace).

When innodb_file_per_table is modified, it will not affect the previously used shared tablespace unless it is modified manually.

Notice:

InnoDB does not create directories, so make sure the configured path directory exists before starting the server.

When migrating and backing up data, pay attention to the integrity of the data files.