Inside InnoDB: How MySQL Stores Data, Row Formats, Pages, and Indexes

Introduction

MySQL stores all table data on disk, but the exact file layout is defined by the storage engine. InnoDB is the default engine and the focus of this article.

InnoDB Row Formats

Each row can be stored in one of four formats: compact , redundant , dynamic , and compressed . The format determines how variable‑length columns, NULL values, and overflow data are recorded.

Compact (default since MySQL 5.7) stores a list of variable‑length field sizes and a NULL‑value bitmap.

Redundant (used before MySQL 5.0) is an older format without those lists; it is rarely seen today.

Dynamic is similar to compact but stores only a 20‑byte pointer for overflow columns.

Compressed builds on dynamic and compresses overflow pages with zlib, trading speed for space.

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  a1 INT,
  a2 INT,
  txt VARCHAR(100)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

To change the format of an existing table:

ALTER TABLE my_table ROW_FORMAT=DYNAMIC;

SHOW TABLE STATUS LIKE 'my_table';

Record Header Information

Beyond the user columns, each record stores additional metadata:

delete_mask – marks a record as deleted; deleted records form a reusable‑space chain.

min_rec_mask – indicates whether the record is the smallest in a B+Tree node (always 0 for normal records).

n_owned – the number of records owned by the last record of a group; used for page‑directory grouping.

heap_no – the position of the record within the page (0 and 1 are the infimum and supremum pseudo‑records).

record_type – 0 = ordinary, 1 = B+Tree non‑leaf, 2 = infimum, 3 = supremum.

next_record – byte offset from the current record to the next record in the same group.

Row Overflow

If a column exceeds 768 bytes, only the first 768 bytes are stored in the record; the remaining data is placed on separate overflow pages. In the compact format a 20‑byte pointer to those pages is stored, while the dynamic format stores the pointer directly in the record.

InnoDB Data Page Structure

Each InnoDB page is 16 KB and consists of several parts:

File Header (38 bytes) – common information for all page types (page number, previous/next page links).

Page Header (56 bytes) – page‑type‑specific data.

Infimum and Supremum – two pseudo‑records that bound the real user records.

User Records – the actual table rows.

Free Space – unused area that can be reclaimed for new records.

Page Directory – an index of the last record of each group, enabling binary search.

File Trailer (8 bytes) – checksum for page integrity.

Page Directory and Record Search

Records are grouped; the last record of each group stores n_owned. The page directory holds the offset of each group’s last record. To locate a row by primary key:

Perform a binary search on the page directory to find the group that may contain the key.

Traverse the linked list of records inside the group using next_record until the target key is found.

If the key is not in the current page, the B+Tree index moves to the next page via the double‑linked list of pages.

B+Tree Indexing

InnoDB automatically creates a clustered index on the primary key. The leaf nodes of this B+Tree contain the full row data, so the index is the data itself. Secondary indexes store only the indexed column(s) plus the primary key value; they are also B+Trees whose leaf nodes point to the clustered index pages.

Index Costs

Each index adds a separate B+Tree, consuming additional 16 KB pages (space cost). Every INSERT, UPDATE, or DELETE must also modify all affected indexes, causing page splits, record moves, and extra I/O (time cost).

Conclusion

By dissecting InnoDB’s storage logic—row formats, page layout, record headers, overflow handling, and index structures—developers gain a clear view of how MySQL stores and retrieves data, enabling more efficient schema design and query optimization.