Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of InnoDB storage files in MySQL", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of InnoDB storage files in MySQL".

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

1. Table structure file

When any data table is established in MYSQL, there is a .frm file corresponding to the database directory of its data catalog. the .frm file is used to save the meta information of each data table, including the definition of the table structure, etc., and 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, named as the data table name .frm, such as user.frm. The .frm file can be used to recover the table structure when the database crashes.

2. Tablespace file

(1) Analysis of table spatial 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). A segment (Segment) contains multiple zones (Extent).

The Extent consists of 64 consecutive pages (Page), each with a page size of 16K, that is, the size of each 1MB. When creating a new table, 32-page fragmented pages are used to store data, and then the application for the zone is completed (InnoDB applies for a maximum of 4 extents 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 files

If innodb_file_per_table is set to on, the system will generate a separate table_name.ibd file for each table, in which the data, index, and internal data dictionary information of the table are stored.

(3) share tablespace files

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

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

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

When using shared tablespace storage, all data in Innodb is stored in a single tablespace, which can be composed of many files, and a table can exist across multiple files, so its size limit is no longer a file size limit, but its own limit. As can be seen from the official documentation of Innodb, the maximum tablespace limit of Innodb is 64TB, that is, 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 a single tablespace storage mode, 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 of separate 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 what is in the MySQL document:

Please note to Windows users: FAT and VFAT (FAT32) are not suitable for MySQL production. NTFS should be used.

(4) shared table space and exclusive table space

Both shared and exclusive tablespaces are based on the way data is stored.

Shared tablespaces: all the table data and index files of a 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 initializes to 10m.

Exclusive tablespace: each table will be generated and stored as a separate file (.ibd file, which includes the data contents of a single table and the contents of the index).

1) comparison of storage content

After using exclusive tablespaces:

The data, indexes, and insert buffers corresponding to each table are stored in the 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 tablespace (ibdata1 file).

2) characteristic comparison

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

Shared tablespaces:

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).

Data and files are put together for easy management.

Disadvantages:

When all the 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, there will be a lot of gaps in the table space after a large number of deletions have been done on a table, especially for statistical analysis, applications such as logging systems are the least suitable for sharing table spaces.

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

Advantages:

Each table has its own independent table space.

The data and indexes of each table are stored in its own tablespace.

It is possible to move a single table in different databases.

Space can be recycled.

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

A) Drop table operation automatically reclaims tablespaces

B) for statistical analysis or daily tables, you can delete a large amount of data by: alter table TableName engine=innodb; retracts the unused space.

C) using truncate table for Innodb of innodb-plugin also shrinks the space.

5. In the case of limited server resources and not a lot of single table data, independent tablespaces are obviously more efficient than sharing. But MySQL defaults to shared tablespaces.

Disadvantages:

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

3) conversion between shared and exclusive tablespaces

Modify the exclusive empty tablespace configuration to configure the following parameters

Innodb_data_home_dir = "/ user/local/mysql/var" the directory where the database file is stored

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

The innodb_data_file_path=ibdata1:10M:autoextend setting configures a separate 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 data directory of MySQL.

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

Look at the innodb_file_per_table variable, if you indicate to OFF that you are using shared tablespaces (by default, shared tablespaces are used)

When you make changes to innodb_file_per_table, the previously used shared tablespaces will not be affected unless you modify them manually

Note:

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

When transplanting and backing up data, we should pay attention to the integrity of data files.

The above is all the contents of the article "sample Analysis of InnoDB Storage Files in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.