Detailed description of InnoDB Storage files in MySQL, mysqlinnodb

Detailed description of InnoDB Storage files in MySQL, mysqlinnodb

Physically, InnoDB tables consist of shared tablespace files (ibdata1), exclusive tablespace files (ibd), table structure files (. frm), and log files (redo files.

1. Table Structure File

Create any data table in MYSQL and have corresponding tables under the database directory corresponding to the data directory. frm file ,. the frm file is used to save the metadata (meta) information of each data table, including the definition of the table structure ,. frm files have nothing to do with the database storage engine, that is, data tables of any storage engine must have. frm file named as the data table name. frm, such as user. frm .. frm files can be used to restore the table structure when the database crashes.

2. tablespace files

(1) tablespace Structure Analysis

The table space structure of InnoDB is as follows:

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 InnoDB Storage engine is managed by the engine itself, and the Tablespace (Tablespace) is composed of scattered segments. A Segment contains multiple partitions (Extent ).

A partition (Extent) consists of 64 consecutive pages. Each Page is 16 KB, that is, the size of each partition is 1 MB. When you create a new table, use a 32-page fragmented page to store data. after use, the application is applied for a zone (InnoDB can apply for up to four zones each time to ensure the sequential performance of data)
Page types include: data page, Undo page, system page, transaction data page, insert buffer bitmap page, and insert buffer idle list page.

(2) Exclusive tablespace files

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

(3) shared tablespace files

In the InnoDB Storage engine, the default tablespace file is ibdata1 (mainly used to store shared tablespace data), which is initialized to 10 MB and can be expanded, as shown in:

In fact, the tablespace file of InnoDB can be modified. You can use the following statement to modify it:

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

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

When a single tablespace is used for storage, the data in each table is stored in a single file. At this time, the single table limit is changed to the file system size limit.

The following figure shows the maximum number of tablespace files on different platforms.

Operating System File-size Limit
Win32 w/FAT/FAT32 2 GB/4 GB
Win32 w/NTFS 2 TB (possibly larger)
Linux 2.4 + (using ext3 file system) 4 TB
Solaris 9/10 16 TB
MacOS X w/HFS + 2 TB
NetWare w/NSS file system 8 TB

※The following content is in the MySQL document:

For Windows users, note that FAT and VFAT (FAT32) are not suitable for MySQL production. NTFS should be used.

(4) shared and exclusive tablespaces

Shared and exclusive tablespaces are used for data storage.

Shared tablespace: All table data of a database is stored in one file. By default, the file path of the shared tablespace is under the data Directory. The default file name is: ibdata1 initialized to 10 M.

Exclusive tablespace: each table is generated and stored in an independent file (. ibd file, which includes the data content and index content of a separate table ).

1) Storage Content Comparison

After using the exclusive tablespace:

Data, indexes, and insert buffering for each table are stored in the exclusive tablespace (. idb file)

The undo information, system transaction information, and secondary write buffering information corresponding to each table are still stored in the original shared tablespace (ibdata1 file)

2) Comparison of features

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

Shared tablespace:

Advantages:

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

Put data and files together for convenient management.

Disadvantages:

When 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, however, multiple tables and indexes are stored in a table space in a hybrid manner. After a large number of deletion operations are performed on a table, there will be a lot of gaps in the table space, especially for statistical analysis, applications such as the log system do not use the most shared tablespace.

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

Advantages:

1. Each table has its own independent tablespace.
2. Each table's data and indexes are stored in its own tablespace.
3. A single table can be moved across different databases.
4. Available space

No matter how you delete a table that uses an independent tablespace, The tablespace fragments will not seriously affect the performance, and there is a chance to process them.

A) The Drop table operation automatically recycles the tablespace.

B) if you delete a large amount of data from a statistical analysis or daily value table, you can use alter table TableName engine = innodb to reduce unnecessary space.

C) using truncate table for innodb-plugin will also shrink the space.

5. When the server resources are limited and the data in a single table is not very large, the independent tablespace is much more efficient than the sharing method, but MySQL uses the shared tablespace by default.

Disadvantages:

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

3) Conversion between shared and exclusive tablespaces

Modify the configurations of an exclusive empty tablespace. Configure the following parameters:

Directory where innodb_data_home_dir = "/user/local/mysql/var" database files are stored

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

Innodb_data_file_path = ibdata1: 10 M: autoextend: configure a single file (shared data file) with an expandable size of 10 MB, named ibdata1. No file location is provided, so it is in the MySQL data directory by default.

Innodb_file_per_table = 1 whether to use a shared or exclusive tablespace (1: use an exclusive tablespace, 0: Use a shared tablespace)

Check the innodb_file_per_table variable. If it is OFF, the table space is shared (by default, the table space used is shared)

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

Note:

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

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