preface

Article “how the mysql data storage (under) | mysql series (4)” we share the mysql innodb engine trading the physical location of data and the distinction between the physical space, the logical data is how to organize? Actually about this aspect of knowledge, predecessors have sorted out a lot. Here is just according to my understanding to sort out, when a porter of knowledge. Let’s take the InnoDB storage engine as an example.

Innodb logical storage structure

The storage structure of MySQL is divided into five levels: table Spaces, segments, clusters, pages, and rows.

【 Table Space】

Table Spaces can be considered as the highest logical structure of InnoDB storage engine. All data is stored in table Spaces. There are system tablespaces, exclusive tablespaces, general tablespaces, temporary tablespaces, and Undo tablespaces.

“Section of the Segment”

A table space is composed of various segments, such as data segment, index segment, and rollback segment. Segment is a logical concept. An IBD file (independent table space file) can consist of many segments.

Creating an index creates two segments:

  • A leaf node segment manages data from non-leaf nodes

  • Non-leaf node segments manage data from leaf nodes.

In other words, the number of segments in a table is the number of indexes multiplied by 2.

Segment memory structure: INODE Entry

【 cluster among 】

A Segment consists of a number of segments, each of which is 1MB (64 contiguous pages) in size.

Each segment has at least one cluster. The space managed by a segment is infinite and can be extended forever, but the smallest unit of expansion is the cluster.

Area memory structure: XDES Entry

[page page]

A cluster is a space made up of contiguous pages, with 64 contiguous pages in a cluster. (1MB / 16KB=64). These pages are physically and logically contiguous.

Pages are the basic unit of disk and memory interaction in MySQL, and MySQL is the basic unit of storage space management. Innodb defaults to 16KB per page. This can also be set with innodb_page_size.

A table space can have a maximum of 2^32 pages. By default, the size of a page is 16KB, meaning that a table space can store a maximum of 64TB of data.

InnoDB page and operating system page mapping

Operating systems deal with memory, the smallest unit of which is a Page. File system memory pages are typically 4K.

InnoDB also designs several different types of pages for different purposes. Undo log pages, segment information nodes, NSERt Buffer free lists, Insert Buffer bitmaps, system pages, index pages, what we call data pages, etc.

The 16KB storage space represented by the data page can be divided into multiple parts with different functions, as shown in the figure below:

“Row Row”

Each row of data is stored on a page. InnoDB stores data in rows, and each row is a data row. The procedure for inserting data is as follows:

Each record has a record and an area holds the record header information

** Organization in each page of data **

The organization of groups in each page of data

InnoDB divides the records on a Page into groups. The address offset of the last record in each group is stored as a slot in the Page Directory. Facilitate binary search location data.

** Page and page direct organization **

This depends on two attributes in the File Header

  • FIL_PAGE_PREV: indicates the page number of the previous page

  • FIL_PAGE_NEX: indicates the page number of the next page

A segment is composed of clusters. A segment connects and manages clusters in different states through three linked lists. The linked list is bidirectional, and the head of the list is FSEG_FREE,FSEG_FULL, and FSEG_NOT_FULL. A cluster descriptor, occupying 40B of space, this information is stored in a page called the cluster description page, which stores the content called the cluster descriptor. In Innodb, a cluster description page manages 16834 pages by default, the cluster size defaults to 64 pages, and a cluster descriptor is 40B, so there are 16834/64=256 clusters in a cluster description page. Where 256*40B space is used to store cluster descriptors, the diagram is as follows:

Table space, Inode page, Inode, segment, cluster, page relationships:

So far, the whole data storage context is probably clear. InnoDB page management logic diagram is as follows:

Reference:

Segmentfault.com/a/119000003…

www.cnblogs.com/zsql/p/1146…