Take a look inside InnoDB with the problem

  • 1. Table segment segment page row concept
  • 2. Index execution process

InnoDB logical storage structure

Index organization table

  • In the InnoDB storage engine, tables are organized according to primary key order.
  • Every table has a primary key. If there is no defined primary key displayed when creating the table, InnoDB will select or create a primary key
    • First determine whether the table has a non-null unique index, if so, then the column is the primary key
    • If not, the InnoDB storage engine automatically creates a 6-byte pointer as the primary key

Table space

  • InnoDB stores the highest level of the engine’s logical structure, with all data in table Spaces.
  • InnoDB storage engine has a shared table space ibdata1 by default. If innodb_file_per_table is enabled, each table is placed in a separate table space.
  • When innodb_file_per_table is enabled, each tablespace contains only data, indexes, and insert buffer Bitmap pages. Other classes of data, such as undo information, insert buffer index page, system transaction information, secondary write buffer, etc., are still in the shared space.

Period of

  • A tablespace is composed of segments. Common segments include data segments, index segments, and rollback segments.
  • Non-leaf node segment with data segment B+ tree

area

  • Extents are Spaces made up of consecutive pages, each of which in any case has a size of 1MB
  • To ensure continuity of partitions, the InnoDB storage engine first applies for 4-5 partitions from the disk. By default, InnoDB engine page size is 16KB, which means there are 64 consecutive pages in an extent.

Page (Page)

  • Pages are the smallest unit of disk management in the InnoDB engine. The default size of each page is 16KB, which can be set with innodb_page_size.
  • Common types are:
    • Data page (B-tree Node)
    • Undo Log Page
    • System Page
    • Transaction System Page
    • Insert Buffer Bitmap
    • Insert Buffer Free List
    • Uncompressed binary large object page (Unconmpressed BLOB Pgae)
    • Compressed binary large object Page

line

  • InnoDB storage engine is column-oriented, meaning data is stored in rows. The number of rows on each page is defined. A maximum of 16KB/2-200 rows can be stored, that is, 7992 rows.

page

InnoDB data page structure

File Header

The name of the Size (bytes) instructions
FIL_PAGE_SPACE_OR_CHECKSUM 4 Checksum value
FIL_PAGE_OFFSET 4 The offset value of a page in a table space, used to locate the location of all pages in a specific page in the table space
FIL_PAGE_PREV 4 On the page preceding the current page, the B+Tree feature determines that leaf nodes must be bidirectional
FIL_PAGE_NEXT 4 On the page next to the current page, the B+Tree feature determines that leaf nodes must be bidirectional
FIL_PAGE_LSN 8 This value represents the last LogSequence position modified for the page. LSN(LogSequence Number)
FIL_PAGE_TYPE 2 InnoDB storage engine page type
FIL_PAGE_FILE_FLUSH_LSN 8 This value is defined in only one page of the system table space, indicating that the file has been updated to at least this LSN value. For independent tablespaces, the value is 0
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4 This value represents the space to which the page belongs

Page Header

The name of the Size (bytes) instructions
PAGE_N_DIR_SLOTS 2 The number of slots in the Page Directory
PAGE_HEAP_TOP 2 A pointer to the first record in the heap, which is stored in the page in terms of the heap
PAGE_N_HEAP 2 The number of records in the heap, 2 bytes, with the 15th bit representing the row record format
PAGE_FREE 2 The first pointer to the reusable space
PAGE_GARBAGE 2 The number of bytes of deleted records, that is, the total number of records with delete flag=1 in the row record structure
PAGE_LAST_INSERT 2 The last place to insert a record
PAGE_N_DIRECTION 2 The number of consecutive records inserted in one direction
PAGE_N_RECS 2 The number of records on this page
PAGE_MAX_TRX_ID 8 Change the maximum transaction ID for the current page. Note that this value is defined only at Secondary Index
PAGE_LEVEL 2 The position of the current page in the index tree, where 0x00 represents the leaf node, meaning that the page node is always at level 0
PAGE_INDEX_ID 8 Index ID, which indicates the index to which the current page belongs
PAGE_BTR_SEG_LEAF 10 Segment header for the segment where the non-page node of the B+ tree data page resides. Note that this value is defined only in the Root page of the B+ tree
PAGE_BTR_SEG_TOP 10 The segment header of the B+ tree data page. Note that this value is defined only in the Root page of the B+ tree

The Supremum and Infimum Record

  • Two virtual boundary values in the data page

User Record and Free Space

  • User Record: Indicates the content of the actual stored row records
  • Free Space Space

Page Directory

  • Page Directory holds the relative locations of records (note that this is the location of Page relative, not cheap), and sometimes these record Pointers are called Slots or Directory Slots.

File Trailer

  • To check that the disk is completely written, FIL_PAGE_END_LSN takes 8 bytes, with the first 4 bytes representing the checksum value of the page and the last 4 bytes being the same as FIL_PAGE_LSN in the File Header.
  • Compare these two values with the FIL_PAGE_SPACE_OR_CHECKSUM and FIL_PAGE_LSN values in the File Header to ensure page integrity.

Clustered index

Through the previous introduction, we have a general understanding of InnoDB storage engine logical storage structure. In conjunction with the logical storage structure, answer question 2: How does the index perform?

  • Pages are the smallest unit of InnoDB disk management. So the B+ tree index itself cannot find the specific row of data. All you can find is the page of the row. Then the database will load the Page into memory, through the Page directory binary search, find the data fell in the specific slot, through next_record to determine the specific row of data.

Library -> Table -> Page -> Page Directory -> Slot -> Row

Panda notes:[email protected]