Unlock MySQL InnoDB Secrets: How Row Formats Store Your Data

Introduction: Understanding Record Structure

You may think you wrote a single SQL statement, but you are actually interacting with an entire storage mechanism of the database.

1. Why Knowing the Record Structure Matters

When you execute an INSERT, the data is not dumped directly onto the disk; InnoDB builds a specific structure before persisting it.

2. Starting from an INSERT Statement

Example:

INSERT INTO users (name, age, address) VALUES ('张三', 25, '北京.海淀');

The row is stored as multiple parts: a record header, a variable‑length field list, and a NULL bitmap.

Assume a

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  name VARCHAR(100) NOT NULL COMMENT 'User Name',
  age INT UNSIGNED COMMENT 'Age',
  address VARCHAR(255) COMMENT 'Address',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Info Table';

Inserting a row (1, '张三', 25, '北京.海淀') yields the visible data:

id | name | age | address
1  | 张三 | 25  | 北京海淀

In InnoDB, the row is split into several components:

Record header stores metadata such as record type, delete flag, and B+Tree position.

Variable‑length field list records lengths of VARCHAR/TEXT/BLOB fields.

NULL bitmap marks which columns are NULL.

3. COMPACT Row Format Details

The COMPACT format is the most common template.

3.1 Record Header – The Row’s Identity Card

The header occupies only 5 bytes but contains crucial information such as record type, delete flag, and B+Tree location.

When a DELETE is issued, InnoDB sets a delete flag in the header instead of removing the row immediately.

Field

Bits

Description

Reserved1

1

Unused

Reserved2

1

Unused

delete_mask

1

1 if record is deleted

min_rec_mask

1

1 if record is the smallest in the leaf

record_type

3

0=normal, 1=min, 2=directory, 3=max

n_owned

4

Number of owned records

heap_no

13

Slot number within the page

next_record

16

Offset of the next record

3.2 Variable‑Length Field List – Handling Flexible Data

For columns like VARCHAR, TEXT, or BLOB, InnoDB stores the length of each field in a reverse‑ordered list.

In the example, name occupies 6 bytes and address occupies 13 bytes, so the list stores [13, 6].

3.3 NULL Bitmap – Space‑Saving Trick

The NULL bitmap uses one bit per nullable column to indicate NULL (1) or not NULL (0). Only columns that allow NULL are included.

In the users table, age and address are nullable, so the bitmap contains two bits representing their NULL status.

The order of bits in the NULL bitmap follows the order of nullable columns in the table definition.

4. Row Overflow – When Data Exceeds a Page

InnoDB stores data in 16KB pages. If a row is larger than a page, the excess is moved to overflow pages, and a pointer (about 20 bytes) is kept in the original page.

Overflow improves storage of large fields but can cause slower queries, fragmentation, and complex space management. Mitigation strategies include splitting large tables, choosing appropriate data types and row formats, limiting field lengths, and avoiding indexes on large columns.

5. Conclusion – Mastering the Underlying Mechanics

The performance “secret” of a database lies in its low‑level structures. Understanding how an INSERT becomes a COMPACT row, how variable‑length fields and NULL bitmaps are stored, and how overflow is handled equips you to answer interview questions confidently and optimize real‑world workloads.