Unveiling MySQL InnoDB: From Instances to Page Storage Architecture

Database Definition

In MySQL a database is the collection of physical files on the file system, while an instance is the running MySQL server composed of background threads and a shared memory area.

Database and Instance

Developers interact with a database through its instance; direct file manipulation is not possible. On Unix, starting an instance typically creates two processes: mysqld (the daemon) and mysqld_safe (a monitor that restarts mysqld on failure).

MySQL Architecture

MySQL’s architecture can be viewed as three layers:

Top layer – connection handling and thread management.

Middle layer – core services such as SQL parsing, analysis, optimization, caching, stored procedures, triggers, and views.

Bottom layer – storage engines (e.g., InnoDB, MyISAM) that actually store and retrieve data.

Data Storage

InnoDB stores data logically in a tablespace , the highest logical unit. A tablespace contains segments , which are divided into extents , and each extent consists of pages . The default page size is 16 KB and can be changed with the innodb_page_size option; the page size also determines the extent size.

How Tables Are Stored

When InnoDB stores a table, the table definition and indexes are kept in separate files:

The definition resides in a .frm file.

Data and indexes are stored in a .ibd file when innodb_file_per_table is enabled.

.frm File

Every MySQL table creates a .frm file that describes the table’s format. Example:

CREATE TABLE test_frm (
    column1 CHAR(5),
    column2 INTEGER
);

.ibd File

InnoDB uses two kinds of files for data storage:

System tablespace files such as ibdata1, ibdata2, which hold system information and shared user data.

Per‑table .ibd files (when innodb_file_per_table is enabled) that contain that table’s data and indexes.

How Records Are Stored

InnoDB manages data in 16 KB pages; each page can hold 2–200 rows depending on row size. MySQL 5.7 supports four row formats:

Compact

Redundant

Compressed

Dynamic

Compact stores column lengths, saving roughly 20 % space compared to Redundant, which stores column offsets.

Row Data Overflow

For very long VARCHAR or BLOB values, InnoDB stores the first 768 bytes in the page and the remainder in overflow pages. In the Compressed or Dynamic formats only a 20‑byte pointer is kept in the row; the full data resides in overflow pages.

InnoDB Page Structure

Each InnoDB page consists of seven parts:

File Header

File Trailer

Page Header

Page Directory

Infimum and Supremum virtual records (place‑holders)

User Records (actual rows, linked via next_record)

Free Space (a linked list for efficient inserts)

The B+‑tree index uses the page header/directory to locate the correct page, then follows next_record to fetch the desired row.

Indexes

Indexes are the primary mechanism for fast record location. InnoDB builds most indexes using B+ trees, which provide balanced search performance. The tree finds the page containing the target row; the page is loaded into memory, and the row is retrieved via the page directory and next_record pointer.

Index Data Structure

InnoDB uses B+ trees for indexes. A B+ tree is balanced, so the number of comparisons needed to find any node equals the tree’s height. The index points to the page that holds the row; the actual row is located in memory after the page is loaded.

Reference: https://draveness.me/mysql-innodb