InnoDB Storage Model Overview and Internal Structures

Everyone knows that MySQL data is stored on physical disks, but the exact files depend on the storage engine; this article focuses on the default InnoDB engine.

InnoDB Storage Model Overview

InnoDB organizes data into tablespaces, segments, extents, and pages. A tablespace holds all data; the default system tablespace is ibdata1, while file-per-table tablespaces create separate .ibd files per table.

Segments group related pages (data, index, rollback, undo). Extents are contiguous 1 MB blocks of pages, and pages (typically 16 KB) are the smallest storage unit.

Row Formats

Rows can be stored in Compact, Redundant, Dynamic, or Compressed formats. The article details the Compact format, which stores a variable‑length field list, a NULL‑value bitmap, and a 5‑byte record header before the actual column data.

Example table creation and data insertion:

CREATE TABLE test(
        a INT,
        b INT,
        c VARCHAR(100),
        PRIMARY KEY (a)
      ) CHARSET=ascii ROW_FORMAT=Compact;
      INSERT INTO test VALUES(1,10,'aaa');
      INSERT INTO test VALUES(2,20,'bbb');
      INSERT INTO test VALUES(3,30,'ccc');
      INSERT INTO test VALUES(4,40,'ddd');

The record header contains fields such as delete_mask , min_rec_mask , n_owned , heap_no , record_type , and next_record , which manage deletion, grouping, and navigation within a page.

Page Directory

To avoid linear scans, InnoDB builds a page directory that stores offsets of the last record in each group, enabling binary search within a page. Groups contain 1–8 records, with special handling for Infimum and Supremum records.

B+‑Tree Indexes

Pages are linked via file headers, forming a doubly‑linked list, while records within a page are ordered by primary key, forming a singly‑linked list. The B+‑tree uses leaf pages to store full user records (clustered index) and internal pages to store index entries.

Lookup proceeds from the root page, using binary search on the page directory to locate the correct child page, and continues until the target record is found.

Clustered vs. Secondary Indexes

Clustered indexes store the full row in leaf pages and are automatically created on the primary key. Secondary (non‑clustered) indexes store only the indexed column plus the primary key, requiring a “back‑lookup” to retrieve the full row when necessary.

Understanding these structures helps developers reason about MySQL performance, storage efficiency, and index usage.