This section is intended to start the index learning, but in the InnoDB storage engine index, there are some concepts of data storage structure, this section will first look at InnodDB logical storage structure, to lay a foundation for index learning.

From the storage structure of InnoDB storage engine, all data are logically placed in a space, which is composed of tablespace, extent, and page. Pages are also called blocks in some documents.

1. InnoDB logical storage structure

The logical storage structure of InnoDB storage engine is shown as follows:

This diagram shows the inclusion of these Spaces more clearly:

1.1.1 Tablespace (Tablespace)

Table Spaces can be considered as the highest level of InnoDB logical structure, where all data is stored.

By default, InnoDB storage engines have a shared table space, IBDatA1, where all data is stored. If innodb_file_per_TABLE is enabled, data from each table can be placed in a separate table space.

If innodb_file_per_TABLE is enabled, it is also important to note that only data, index and insert buffer Bitmap pages are stored in each table space. Other classes of data, such as rollback (undo) information, insert buffer index pages, system transaction information, secondary write buffer, etc. are stored in the original shared table space.

1.2. Segment

A table space is composed of segments. Common segments include data segments, index segments, and rollback segments.

InnoDB storage engine tables are index organized, so data is index and index is data. Data segments are Leaf node segments of the B+ tree, and index segments are non-leaf node segments of the B+ tree. These contents will be described in detail in the following index study.

1.3 area (extend)

Extents are Spaces made up of contiguous pages, each of which is 1MB in size in any case. To ensure page continuity in extents, the InonoDB storage engine requests 4-5 extents from disk at a time. By default, InnoDB storage engine pages are 16KB, meaning there should be 64 consecutive pages in a range.

The innodb1.0.x version starts to introduce compressed pages. The size of each page can be set to 2K, 4K, or 8K with KEY_BLOCK_SIZE, so each section has a corresponding footer of 512, 256, or 128.

Innodb1.2. x has a new parameter, Innodb_page_size, which can set the default page size to 4K, 8K, but the data in the page is not compressed.

However, sometimes to save disk capacity, the default size of the created table is 96KB, with 64 consecutive pages in the area. (For some small watches)

1.4. Page

Pages are the smallest unit of disk management for InnoDB storage engine, with each page defaulting to 16KB; InnoDB storage engine starting with 1.2.x, page sizes can be set to 4K, 8K, 16K with the innodb_page_size parameter.

Once set, all tables are innodb_page_size and cannot be changed again unless new libraries are generated by mysqldump import and export operations.

Common page types in innoDB storage engine are:

✅ Data page (B-tree Node)

✅ undo Page (undo Log Page)

✅ System Page

✅ Transaction System Page

✅ Insert Buffer Bitmap page

✅ Insert Buffer Free List page

✅ Uncompressed BLOB Page

✅ compressed binary large object Page (compressed BLOB Page)

1.5, Row

InnoDB storage engine is row-oriented, that is, data is stored in rows, and the number of rows per page is defined, with a maximum of 16KB/2-200, or 7992 rows.

2, InnoDBLine record format

InnoDB storage engine like most databases (such as Oracle and Microsoft SQL Server databases), records are stored as rows. This means that the page holds rows of data from the table. Prior to the 1.0x release of InnoDB, the InnoDB storage engine provided Compact and Redundant formats for storing row record data, which are currently the most used formats.

2.1 Compact Line record format

Compact row records were introduced in MySQL 5.0 and are designed to store data efficiently. Simply put, the more rows of data a page has, the better its performance.

The following figure shows how Compact row records are stored:

The Compact row record format starts with a list of non-null variable-length field lengths, which are placed in reverse order of column length:

  • If the column length is less than 255 bytes, use 1 byte.

  • If the value is greater than 255 bytes, use 2 bytes.

The length of variable length fields cannot exceed 2 bytes, because the maximum length of VARCHAR type in the MySQL database is 65535 bytes. The second part after the variable-length field is the NULL flag bit, which indicates whether there is a NULL value in the row, and 1 if so.

The next part is the Record header, which takes up a fixed 5 bytes (40 bits). See table for each meaning:

The name of the Size (bit) describe
(a) 1 The unknown
(a) 1 The unknown
deleted_flag 1 Whether the row has been deleted
min_rec_flag 1 1 if the row record is predefined as minimal
n_owned 4 The number of records owned by the record forSlot
heap_no 13 Index The index number of this record in the heap
record_type 3 Record type, 000 (normal), 001 (B+Tree pointer), 010 (Infimum), 011 (Supremum)
next_record 16 The relative position of the next record in a page
Total 40(5Byte) nothing

The last part is to actually store the data for each column.

Note that NULL does not occupy any space in this section, i.e. NULL does not occupy any space in the actual storage except for the NULL flag bit. Another point to note is that each row of data has two hidden columns in addition to the user-defined columns, the transaction 1D column and the rollback pointer column, which are 6 and 7 bytes in size, respectively. If the InnoDB table does not define a primary key, a 6-byte ROWId column is added to each row.

Redundant is an InnoDB row storage format prior to MySQL 5.0, which is not expanded here.

2.2. Row overflow data

The InnoDB storage engine can store some data in a record outside of the actual data page. Because the default size of a typical data page is 16KB, if a data page cannot store the inserted data, row overflow will definitely occur.

Storage of large object column types, such as BLObs and LOBs, is generally thought to store data outside the data page. However, bloBS may not place data on overflow pages, and even VARCHAR column data types may still be stored as row overflow data.

3,InnoDBData page structure

Page is the minimum disk unit for InnoDB storage engine management database. The page type of the B-tree Node holds the actual data of the rows in the table.

The InnoDB data page consists of the following 7 sections:

  • File Header

  • Page Header

  • Infimun and Supremum Records

  • User Records (row Records)

  • Free Space

  • Page Directory

  • File Trailer

The size of File Header, Page Header and File Trailer are fixed, which are 38, 56 and 8 bytes respectively. These Spaces are used to mark some information of the Page, such as Checksum and the number of layers of B+ tree index where the data Page is located. The User Records, Free Space, and Page Directory sections are the actual row record storage Space, so the size is dynamic.




Reference:

[1] : MySQL Technology Insider InnoDB Storage Engine

[2] : MySQL Architecture and Components

[3] : Understand MySQL row overflow mechanism?

[4] : InnoDB data page structure analysis

[6] : InnoDB — Row record format