MySQL InnoDB Logical Storage structure

MySQL InnoDB Logical Storage structure

From the logical structure of the INNODB storage engine, all data is logically stored in a space, called a tablespace, and the table space consists of segments (sengment), extents (extent), pages (page) . PS: The page is also called block in some documents.

The logical storage structure of the InnoDB storage engine is roughly as follows:

　　

　　I. tablespace (table space)

The table space is divided into two categories, here is a simple summary:

1. Stand-alone table space: Each table will be generated in a separate file way for storage, each table has a. frm table description file, and an. ibd file. This file includes the data content of a single table and the index content, which, by default, is stored in the table's location.

2. Shared tablespace: All of InnoDB's data is stored in a separate tablespace, and the tablespace can consist of many files, a table can exist across multiple files, so its size limit is no longer a file size limit, but its own limitations. As you can see from InnoDB's official documentation, the maximum table space limit is 64TB, that is, InnoDB's single-table limit is basically around 64TB, and of course this size is all other relevant data including all the indexes of this table.

InnoDB the data in a tablespace, the tablespace can be seen as the highest level of the logical structure of the INNODB storage engine. is essentially a virtual file system consisting of one or more disk files. The InnoDB table space does not only store tables and indexes, but also holds rollback segments, double write buffers, and so on.

Ii. paragraph (segment)

Table spaces are composed of segments, common segments are data segments, index segments, rollback segments, and so on. The previous article (MySQL InnoDB Index Organization table & PRIMARY key role) has been introduced about the InnoDB storage Engine Compartment index Organization (index organized), so data is indexed, indexed as data. The data segment is a B + Tree leaf node (leaf segment), and the index segment is a B + Tree segment non-indexed node.

Third, district (extent)

A zone is a space of contiguous pages (page), and in any case each area is 1MB in size, in order to guarantee the continuity of the page, the InnoDB storage engine requests 4-5 extents at a time from disk. By default, the InnoDB storage engine has a page size of 16KB, which is 64 contiguous pages in a zone. (1mb/16kb=64)

innodb1.0.x version began to introduce compressed pages, each page size can be set by the parameter key_block_size to 2K, 4K, 8K, so each region corresponding to the end of the page 512, 256, 128.

The innpdb1.2.x version has a new parameter innodb_page_size that lets you set the default page size to 4K, 8K, but the data in the page is not compressed.

but sometimes to conserve disk capacity, the default size for creating tables is 96KB, with 64 contiguous pages in the zone. (for some small tables)

Iv. Pages (page)

The page is the smallest unit of disk Management for the InnoDB storage engine, and the default 16kb;innodb storage engine for each page is in the way of the 1.2.x version, which sets the page size to 4K, 8K, 16K by parameter innodb_page_size. If the settings are complete, the pages in all the tables are innodb_page_size in size and cannot be modified again unless the new library is generated by mysqldump import and export operations.

The common page types in the InnoDB storage engine are:

1. Data page (B-tree Node)

2. Undo page (undo Log page)

3. System page

4. Things data page (Transaction System page)

5. Insert Buffer Bitmap page (insert buffer Bitmap)

6. Insert Buffer Idle list page (insert buffer free)

7. Uncompressed binary Large Object page (uncompressed BLOB page)

8. Compressed binary Large Object page (compressed BLOB page)

Five, line (row)

The InnoDB storage engine is column-oriented (row-oriented), which means that data is stored by row, and Row records for each page are hard-defined, allowing up to 16kb/2-200, or 7992 rows of records.

If there is inappropriate, but also hope to advise, thank you!

Reference books:

[1]. mysql Technology insider: InnoDB Storage Engine

MySQL InnoDB Logical Storage structure