Understanding MySQL InnoDB Storage: From Data Pages to Tablespaces

Overview of the five‑level storage hierarchy

InnoDB stores data in a hierarchy: record → page → extent → segment → tablespace . A page is the basic 16 KB unit, an extent is 64 contiguous pages (1 MB), a segment is a logical collection of extents and free pages, and a tablespace is one or more files (up to 64 TB) that hold all these structures.

Data page internal structure (7 parts)

File Header

(38 bytes): common page metadata such as page number, checksum, and LSN. Page Header (56 bytes): page‑specific state like record count and slot count. Infimum + Supremum (26 bytes): two sentinel pseudo‑records that mark the smallest and largest keys in the page. User Records: the actual rows inserted by the user. Free Space: unused area inside the page. Page Directory: a list of slot offsets that accelerate record lookup. File Trailer (8 bytes): checksum and LSN used to detect half‑written pages.

When a page is first created it contains only free space; each INSERT carves a chunk from free space into a user record. When free space is exhausted the page becomes full.

Record chaining with next_record

Each record has a 5‑byte header containing fields such as delete_mask, n_owned, heap_no, record_type, and next_record. The next_record pointer links records into a single‑linked list ordered by primary‑key value, not by insertion order. Two sentinel records—Infimum (heap_no 0) and Supremum (heap_no 1)—anchor the list; Infimum points to the smallest real record and Supremum follows the largest.

Deletion does not immediately remove a record. The engine sets delete_mask to 1, rewires the surrounding next_record pointers, and places the deleted record on a “garbage list” whose space can be reused for future inserts, which explains why repeated delete‑insert cycles often do not grow the file.

Page Directory and fast lookup

All normal records (including the two sentinels) are grouped; the last record of each group records its n_owned (the number of records in the group). The offsets of these last records are stored as slots near the page tail, forming the Page Directory. Group size rules: the Infimum group has 1 slot, the Supremum group 1‑8 slots, other groups 4‑8 slots. When a group reaches 8 records it splits, adding a new slot.

Because slot keys are monotonic, locating a record becomes a binary search on slots followed by a tiny linear scan inside the chosen group. Example binary‑search steps (low = 0, high = 4 → mid = 2 → key 8 > 6 → high = 2, etc.) illustrate the process.

Page Header vs File Header fields

PAGE_N_RECS

: number of valid user records. PAGE_N_DIR_SLOTS: number of slots in the page directory. PAGE_FREE: head of the garbage list. PAGE_GARBAGE: total bytes occupied by deleted records. PAGE_DIRECTION / PAGE_N_DIRECTION: direction and count of consecutive inserts (helps InnoDB optimise split strategy). PAGE_LEVEL: B+‑tree level of the page (0 for leaf). PAGE_BTR_SEG_LEAF / PAGE_BTR_SEG_TOP: present only on root pages, linking leaf and non‑leaf segments.

File Header fields include FIL_PAGE_OFFSET (page number, 4 bytes, giving a maximum tablespace size of 64 TB), FIL_PAGE_PREV / FIL_PAGE_NEXT (previous/next page numbers forming a doubly‑linked list across the B+‑tree), FIL_PAGE_TYPE (e.g., 0x45BF for index pages), and FIL_PAGE_SPACE_OR_CHKSUM (checksum).

File Trailer integrity check

The first 4 bytes of the trailer duplicate the checksum stored in the header. When a page is flushed, the header is written first, the trailer last. If a power loss occurs mid‑write, the header checksum reflects the new data while the trailer still holds the old checksum, allowing InnoDB to detect a corrupted half‑written page. The remaining 4 bytes store the low 32 bits of the LSN, matching the LSN in the header for additional validation.

Why extents ("区") exist

Pages linked logically may be far apart on disk, causing random I/O during range scans. An extent groups 64 contiguous pages (1 MB), so allocating space by whole extents keeps physically adjacent pages together, turning many random reads into sequential I/O at the cost of a small amount of unused space.

Extents are further grouped into sets of 256 (a "group"). This grouping simplifies management of extent descriptors.

Why segments ("段") exist

Segments separate leaf‑node pages from non‑leaf‑node pages, preventing mixed I/O patterns. Each index therefore has two segments (leaf and non‑leaf); a clustered index adds two segments, and each secondary index adds another two.

Small tables would waste space if each segment immediately claimed whole extents. InnoDB therefore uses "fragment" pages: initially a segment draws pages one‑by‑one from a fragment pool; once it accumulates 32 fragment pages it graduates to allocating whole extents.

Managing massive numbers of extents and segments

Each extent has a 40‑byte XDES Entry describing its state (FREE, FREE_FRAG, FULL_FRAG, or FSEG) and a bitmap of free pages. Extents of the same state are linked together, forming three tablespace‑level lists (FREE, FREE_FRAG, FULL_FRAG) that allow O(1) allocation by taking the head of the appropriate list.

Similarly, each segment maintains three internal lists (FREE, NOT_FULL, FULL) of its extents, enabling constant‑time allocation within the segment.

Special pages that map the tablespace

Page 0: FSP_HDR – stores global tablespace attributes and the XDES entries for the first 256 extents.

Page 1: IBUF_BITMAP – records Change Buffer information for the group.

Page 2: INODE – holds INODE Entry structures describing segments.

Every subsequent group starts with an XDES page (describing the next 256 extents) followed by an IBUF_BITMAP page.

System tablespace and data dictionary

The independent tablespace (one .ibd file per table) coexists with a special system tablespace (Space ID 0). The system tablespace contains additional pages (3‑7) that store transaction system data, the first rollback segment, and the data‑dictionary header.

MySQL stores metadata about tables, columns, indexes, and fields in internal system tables: SYS_TABLES, SYS_COLUMNS, SYS_INDEXES, and SYS_FIELDS. These tables are hard‑coded in the server and reside in fixed pages (e.g., page 7) of the system tablespace.

Version differences:

In MySQL 5.7 the data dictionary lives in those internal tables; the double‑write buffer is stored in pages 64‑191 of the system tablespace.

Starting with MySQL 8.0 the .frm files are removed, the internal tables are replaced by a transactional data dictionary stored in the mysql.ibd tablespace, and the double‑write buffer moves to a separate .dblwr file.

The new dictionary provides atomic DDL, ensuring that CREATE/ALTER statements either fully succeed or fully roll back.

From record to tablespace – the complete map

A record is linked into a page via next_record, located quickly through the page directory’s binary‑search slots, pages are linked into a B+‑tree via FIL_PAGE_PREV/NEXT, pages are packed into sequential extents, extents are grouped into leaf or non‑leaf segments, and all of this is managed by XDES and INODE structures inside the tablespace file. The data dictionary records the metadata that ties every table and index to its root page.