MySQL Engine Features · File physical structure of the InnoDB file system

Review

From the upper point of view, the InnoDB layer of files, in addition to the redo log, basically has a fairly uniform structure, are fixed block size, commonly used btree structure to manage data. Only application scenarios for different blocks will be assigned different page types. Typically, by default, the size of each block is univ_page_size, and without any configuration value of 16kb, you can also choose to specify a block size for the instance when you install it. For compressed tables, block size can be specified while the table is in progress, but the extracted pages in memory are still a uniform page size.

From the classification of physical files, there are log files, main system tablespace files ibdata, undo tablespace files, temporary tablespace files, user table spaces.

Because the database needs to guarantee the integrity of the data, it encapsulates its own file system on the OS system. Let's take a look at a picture so that we can better understand the file structure of the InnoDB database

650) this.width=650; "title=" InnoDB vs File System "style=" "alt=" InnoDB vs File System "src=" http://s3.51cto.com/wyfs02/ M02/9e/20/wkiol1ml9u3g9tfiaabajpl_pji545.jpg "" 366 "height=" 271 "/>

First, look at the structure of the page

As previously mentioned, a tablespace file consists of a 16kb page, each with a 32-bit ordinal (page number), often called an offset, which is the offset from the initial position of the tablespace. Because each page size is 16kb, the No. 0 page has an offset of 0, the first page has an offset of 16384, and so on. Because the maximum value for a 32-bit is 2^32, the maximum value for a table space is 2^32*16KB=64TB.

650) this.width=650; "title=" FIL header and Trailer "style=" "alt=" FIL Header and Trailer "src=" Http://s3.51cto.com/wyfs0 2/m01/9e/20/wkiol1ml91rxpul_aact9eohboa325.png "" 640 "height=" 426 "/>

1. Checksum for checksum, a program that deals with disks must use checksums to ensure data correctness, to verify that data corruption is caused by disk space corruption;
2. Offset (page number) is the ordinal of the page, which is the offset;
3. Previous page and next page InnoDB data in memory buffers are organized by B + trees, whereas pages in the B + tree are strung together by a doubly linked list because each page header has pointers to the previous and next pages, and this structure improves the efficiency of the full table scan;
4. LSN for last page modification LSN If you do not understand, you can view the InnoDB storage Engine This book, simply said to represent the amount of flushing to redo log data, can be used to redo the log recovery database.
5. The type of page, the type of page that determines the data stored in other parts of the page, the common page types are data industry, undo page, System page, and so on;
6. Space ID is the table space that this page belongs to
7. Flush LSN This value stores the maximum LSN value that is flushed to any page of the entire system.

650) this.width=650; "title=" type "style=" "alt=" type "src=" http://s3.51cto.com/wyfs02/M02/9E/20/ Wkiol1ml91uameogaafjtkmqyn4210.jpg "" 643 "height=" 351 "/>

Now let's take a look at some of the basics of tablespace (tablespace)

Table space is segmented management, if there is a table with only one primary key index, then the table has two segments, one is the internal node segment, that is, the non-leaf node segment, there is a leaf segment, that is, the node that stores data. If a table in addition to the primary key index, there is a secondary index, then this table space has four segments, the primary key internal node segment, the primary key leaf node segment, secondary index internal node segment, secondary index leaf node segment. InnoDB Storage Engine There is a diagram that shows the relationship of segments, districts, and pages well:

650) this.width=650; "title=" tablespace "style=" border-image:none; "alt=" tablespace "src=" Http://s3.51cto.com/wyfs02/M01/9E/23/wKiom1mL91yh9sCDAAGPMa5vFHk281.png "" 466 "height=" 309 "/>

Of course, shared tablespace ibdata is not the same as the user table space, because it needs to store more global information, such as Doublewrite,undo and so on, so the shared tablespace has more segments, where the user table space is analyzed first.

Each tablespace has a unique space ID, because this ID is used in many places, such as when the memory data is being brushed to disk, and the space ID is used to find the Tablespace file. InnoDB there is always a "system space", that is, shared tablespace, the space ID of this table system tablespace is always 0.

Table spatial Structure

A tablespace file is made up of a series of pages with a maximum number of pages up to 2^32. In order to better manage the page, the page is divided into groups by 1MB (64 consecutive pages), this group is called the area, InnoDB is generally by the district to allocate space for the segment.

In order to manage all pages, extents, and tablespaces of the tablespace itself, InnoDB must use some data structures to track information such as the Save page area, showing a table space:

650) this.width=650; "title=" Space File Overview "style=" "alt=" Space File Overview "src=" http://s3.51cto.com/wyfs02/ M01/9e/20/wkiol1mmau_xlak0aadcfxynrly215.jpg "" 313 "height=" 480 "/>

The first page of each tablespace is the FSP_HDR (file Space header) page, which holds the FSP header structure, which preserves the size of the table space and the use of extents,fragment that are not used at all. And the use of inode, and so on, the next detail.

The 1th page can only save 256 extents, which is 16,384 pages, 256MB. So every 16,384 pages must be assigned a new page to save the next 16,384 pages of information, this page is the Xdes page, the Xdes page and the 1th page in addition to the FSP_HDR structure 0 (because the first page already contains the base list, so the following xdes Page's FSP_HDR structure 0), the others are the same.

Second page Ibuf_bitmap This page is the Insert cache bitmap page that records some information about the insert buffer.

The third page is the Inode page, which stores all the segments in the table space using a list (file segments), before the paragraph is composed of several extents, in fact, in addition to extents, there are 32 separately allocated "Fragments" page, because some segments may not use a zone, So the main purpose here is to save space.

FSP_HDR PAGE

The first page type of the data file is FIL_PAGE_TYPE_FSP_HDR initialized () when a new tablespace is created, and fsp_header_init the page is used to track the spatial management of the subsequent 256 extent (approximately 256MB file size), so a similar data page is created every 256MB. Type is FIL_PAGE_TYPE_XDES , Xdes page In addition to the head of the file, other than FSP_HDR the page has the same data structure, can be called extent description page, each extent occupies 40 bytes, a xdes page describes up to 256 extent.

MySQL Engine Features · File physical structure of the InnoDB file system