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

This article will explain in detail how to understand the storage structure of MySQL InnoDB. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Physically speaking, the InnoDB table consists of shared tablespaces, log filegroups (redo filegroups), and table structure definition files. If innodb_file_per_table is set to on, the system will generate a separate table_name.ibd file for each table

In this file, the data, index, and internal data dictionary information of the table related to the table are stored. The table structure file ends with .frm, which is independent of the storage engine.

The following is the table space structure diagram of InnoDB:

In the InnoDB storage engine, the default tablespace file is ibdata1, 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, and this tablespace 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.

It's its own limitations. 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.

Shared tablespaces and exclusive tablespaces can be converted by the parameter innodb_file_per_table. If 1, exclusive tablespaces are enabled, otherwise, shared table storage is enabled.

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.

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 over non-synchronous files). Data and files are put together for easy management.

Disadvantages:

All the data and indexes are stored in one file, which means that 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 deletions have been done on a table, the table space will

There are a lot of gaps, especially for statistical analysis, applications such as daily value systems are the least suitable for shared tablespaces.

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

Advantages:

1. Each table has its own independent tablespace.

2. The data and indexes of each table will be stored in its own tablespace.

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

4. Space can be recycled (table emptiness cannot be recycled by itself except for drop table operation)

A) the Drop table operation automatically reclaims tablespaces. For statistical analysis or daily value tables, you can delete a large amount of data through: alter table TableName engine=innodb; to retract unused space.

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

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

Disadvantages:

Single table increases too much, such as more than 100 gigabytes.

After enabling the parameter option of innodb_file_per_table, only the data, index and insert buffer are stored in the .idb file corresponding to each table, while the undo information, system transaction information, secondary write buffer and so on are stored in the original shared tablespace.

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

The management of the InnoDB storage engine is done by the engine itself, and the tablespace is made up of scattered pages and segments.

The zone consists of 64 consecutive pages, each with a page size of 16K, that is, the size of each section is 1MB. When creating a new table, there are 32 pages of fragmented pages to store data, and the application for the zone will be made only after use. (InnoDB applies for a maximum of 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.

On how to understand the storage structure of MySQL InnoDB to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.