• notice
  • Congratulations on the launch of the Sought Tech site

Detailed explanation of InnoDB storage files in MySQL

In a physical sense, InnoDB tables are composed of shared tablespace files (ibdata1), exclusive tablespace files (ibd), table structure files (.frm), and log files (redo files, etc.).

1.Table structure file

To create any data table in MYSQL, there is a.frm file corresponding to the table in the database directory corresponding to the 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, the data table of any storage engine must have a.frm file, and the naming method is 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 file

(1) Analysis of table space structure

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 (Segment )composition.A segment (Segment) contains multiple areas (Extent).

The

area (Extent) is composed of 64 consecutive pages (Page), each page size is 16K, that is, the size of each area is 1MB, when creating a new table, first use 32-page fragmented pages to store data, use After finishing, it is the application of the district (InnoDB applies for up to 4 districts at a time to ensure the sequential performance of the data)
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 separately.In this file, data related to the table, indexes, and internal data dictionary information of the table are stored.

(3) Shared tablespace file

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

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

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

When using the shared table space storage method, all data of Innodb is stored in a single table space, and this table space can be composed of many files, and a table can exist across multiple files, so its size limit is no longer It is the limitation of file size, but its own limitation.As can be seen from Innodb’s official documents, the maximum limit of its table space is 64TB, that is to say, Innodb’s single table limit is basically around 64TB, of course this size includes all indexes of this table and other related data.

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

The following is the maximum size of a single table space file 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 is the content in the MySQL document:

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

(4) Shared table space and exclusive table space

Shared tablespaces and exclusive tablespaces are all based on how data is stored.

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

Exclusive table space: Each table will be generated and stored as a separate file (.ibd file, this file includes the data content and index content of a single table).

1) Comparison of stored content

After using the exclusive table space:

The data, index and insert buffer corresponding to each table are stored in an exclusive table space (.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 table space and independent table space are as follows:

Shared table space:

Advantages:

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

Data and files are put together for easy management.

Disadvantages:

All data and indexes are stored in one file, and there will be a very large file.Although a large file can be divided into multiple small files, multiple tables and indexes are stored in the table space.After a large number of delete operations on a table, there will be a lot of gaps in the table space, especially for statistical analysis, the log system is the most suitable for applications such as shared table space.

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

Advantages:

  1. Each table has its own independent table space.
  2. The data and indexes of each table will be stored in its own table space.
  3. A single table can be moved in different databases.
  4. Space can be reclaimed

For a table that uses an independent table space, no matter how you delete it, the fragmentation of the table space will not seriously affect the performance, and there is a chance to deal with it.

a)Drop table operation automatically reclaims table space

b) For statistical analysis or daily value tables, after deleting a large amount of data, you can pass: alter table TableName engine=innodb; to shrink the unused space.

c) Using truncate table for Innodb using innodb-plugin will also shrink the space.

5.In the case of limited server resources and not too much data in a single table, the independent table space is obviously more efficient than the shared method.However, MySQL defaults to the shared table space.

Disadvantages:

The volume of a single meter may be too large, such as more than 100 G.

3) Conversion between shared table space and exclusive table space

Modify the exclusive empty table space configuration, 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 Set and configure a single file (shared data file) with an expandable size of 10MB, named ibdata1.The location of the file is not given, so the default is in the MySQL data directory.

innodb_file_per_table=1 whether to use shared or exclusive table space (1: to use exclusive table space, 0: to use shared table space)

Check the innodb_file_per_table variable, if it is OFF, it means that the shared table space is used (by default, the table space used is the shared table space)

When innodb_file_per_table is modified, it will not affect the previously used shared table space, unless it is modified manually

Note:

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

When doing data migration and backup, pay attention to the integrity of the data file.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+