How Does MySQL Store a Row? Inside InnoDB Files, Pages, and Row Formats

Data directory and file layout

MySQL stores all data on disk. The location of the data directory can be obtained with: SHOW VARIABLES LIKE 'datadir'; Typical output shows a path such as /var/lib/mysql/. Under this directory each database has its own sub‑directory. For a table created with the default InnoDB engine ( innodb_file_per_table=1 since MySQL 5.6.6) three files appear:

db.opt – database default character set and collation.

table_name.frm – table definition (metadata).

table_name.ibd – the table’s data and indexes stored in an exclusive tablespace file.

InnoDB tablespace hierarchy

InnoDB organizes a tablespace as a hierarchy of segments → extents → pages → rows . The hierarchy is illustrated in the diagram below.

Page

InnoDB reads and writes data in 16 KB pages (the smallest I/O unit). Pages can be data pages, undo log pages, overflow pages, etc.

Extent

An extent is a 1 MB region consisting of 64 consecutive pages. Grouping pages into extents reduces random I/O for large indexes because adjacent pages are physically close.

Segment

Segments are collections of extents. Typical segment types are data, index, and rollback segments.

InnoDB row formats

Four row formats exist:

Redundant (obsolete)

Compact (default since MySQL 5.1)

Dynamic (default since MySQL 5.7)

Compressed

The Compact format is the most widely used and the basis for understanding the other two.

Compact row layout

A Compact record consists of two logical parts: extra information and real data .

Extra information

Variable‑length field length list – stores the actual byte length of each VARCHAR, CHAR, TEXT, BLOB column. Lengths are written in reverse column order to improve CPU‑cache locality.

NULL‑value list – one bit per nullable column, also stored in reverse order and padded to a whole byte.

Record header – contains flags such as delete_mask (deletion flag), next_record (pointer to the next record’s header), and record_type (0 = ordinary row, 1 = non‑leaf B‑Tree node, etc.).

Example table:

CREATE TABLE `t_user` (
  `id`   INT NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age`  INT DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT;

Assume three rows are inserted:

For the first row ( name='a', phone='123') the length list is 03 01 (phone length = 3, name length = 1). The reverse order allows the CPU cache line that holds the record header to also contain the length bytes of the earliest columns, improving cache‑hit probability.

When a column is NULL, its length is omitted from the list. For the third row where phone is NULL, the length list contains only the length of name.

NULL‑value list

Each nullable column contributes one bit. Bits are ordered in reverse column order and packed into whole bytes (high‑order bits padded with 0). A bit value of 1 indicates NULL, 0 indicates a non‑NULL value.

In the example:

Row 1 – no NULLs → 0x00 Row 2 – age is NULL → 0x04 Row 3 – phone and age are NULL →

0x06

Real data section

Beyond user‑defined columns, three hidden fields may appear:

row_id (6 bytes) – present only when the table lacks a primary key or unique index.

trx_id (6 bytes) – the transaction identifier that created the row.

roll_pointer (7 bytes) – pointer to the previous version of the row for MVCC.

Maximum VARCHAR length

The InnoDB row size limit is 65 535 bytes, including the variable‑length list and NULL list. Therefore the usable payload for a single nullable VARCHAR with an ASCII charset (1 byte/character) is:

65 535 – 2 (length‑list bytes) – 1 (NULL‑list byte) = 65 532 bytes

Consequently the practical maximum for VARCHAR(n) is n = 65 532 in ASCII. With UTF‑8 (up to 3 bytes/character) the limit becomes ⌊65 532 / 3⌋ = 21 844 characters.

If a table contains multiple columns, the sum of all column data plus the overhead must stay ≤ 65 535 bytes.

Attempting to create VARCHAR(65535) fails because the overhead pushes the row size over the limit. Creating VARCHAR(65532) succeeds, confirming the calculation.

Row overflow handling

When a row cannot fit into a single 16 KB page, InnoDB stores the excess in overflow pages.

Compact format – the first part of the oversized column remains on the original page; a 20‑byte pointer to the overflow page is stored in the record.

Dynamic and Compressed formats – only the 20‑byte pointer is stored on the original page; the original page contains no partial column data.

Key take‑aways

NULL values are stored in a separate NULL‑value list; they do not occupy space in the real data area.

The length of each variable‑length field is recorded in the variable‑length field length list, stored in reverse column order for cache efficiency.

A row can hold at most 65 535 bytes, including overhead. For a single nullable ASCII VARCHAR the practical maximum is 65 532 bytes; for UTF‑8 it is 21 844 characters.

When a row overflows a page, InnoDB uses overflow pages and stores a 20‑byte pointer in the original page (Compact) or only the pointer (Dynamic/Compressed).