Why InnoDB Reads 16KB Pages and Varchar Limits? Deep Dive into MySQL Storage

1. What does InnoDB do?

InnoDB is a storage engine that stores table data on disk.

2. How does InnoDB read and write data?

InnoDB processes data in memory; it must load pages from disk into memory and flush modified pages back to disk. Disk I/O is much slower than memory, so InnoDB divides data into pages, the basic unit of disk‑memory interaction. The default page size is 16 KB, meaning each read or write transfers at least 16 KB.

When you test a paginated query, the first request incurs 300‑400 ms latency because it reads a 16 KB page from disk; subsequent pages are served from memory and take only 30‑40 ms unless the needed data is not in the cached page.

Note: pagination queries and the 16 KB page concept are two different ideas.

Note: the innodb_page_size variable cannot be changed after the MySQL data directory is initialized.

3. Varchar questions—InnoDB row format

Common doubts arise about the maximum length of varchar(255) and why varchar(65535) is not allowed, with the maximum being varchar(16383). Records are stored in a row format, which can be Dynamic, Compact, Redundant, or Compressed. MySQL 5+ defaults to the Dynamic format.

MySQL 5+ default row format is Dynamic.

SHOW VARIABLES LIKE "innodb_default_row_format"

We focus on the default Dynamic row format.

Example table structure (utf8mb4 charset):

CREATE TABLE test (
  c1 VARCHAR(10),
  c2 VARCHAR(10) NOT NULL,
  c3 CHAR(10),
  c4 VARCHAR(10)
) CHARSET = utf8mb4;

Insert example:

INSERT INTO test (c1, c2, c3, c4)
VALUES ('aaaa','你好啊','cc','d'),
       ('eeee','fff',NULL,NULL);

3.1 Dynamic—InnoDB default row format

Extra record information includes three parts: variable‑length field length list, NULL‑value list, and record header. This article discusses only the first two.

3.2 How does InnoDB know the actual varchar length?—Variable‑length field length list

Variable‑length types (VARCHAR, TEXT, BLOB) store the actual byte length L alongside the data. InnoDB uses an unsigned short (2 bytes) to record L, allowing a maximum of 65535 bytes.

Actual data content stored in the column.

Real byte count stored in the variable‑length field list.

Example with the first record of the test table shows the byte lengths of each variable‑length field.

Determine the byte length with SQL:

SELECT LENGTH(c2) FROM test WHERE c1='aaaa';

Variable‑length field lengths are stored in reverse column order.

3.3 How many characters can varchar(M) store? Why the max 16383?

VARCHAR(M) defines a maximum of M characters, not bytes. InnoDB records the actual byte length L using up to 2 bytes (max 65535). With utf8mb4 (W = 4 bytes per character), the maximum characters representable are floor(65535 / 4) = 16383.

Testing shows the practical limit is lower due to overflow columns.

create table t2 (name varchar(16383)) charset=utf8mb4;

Inserting data reaches a maximum of 48545 bytes before an error occurs.

Rules:

If M × W ≤ 255, L uses 1 byte.

If M × W > 255, L uses 1 byte when L ≤ 127, otherwise 2 bytes.

Variable‑length field list stores only non‑NULL columns.

3.4 How does InnoDB handle NULL values?—NULL value list

Columns that allow NULL are tracked in a NULL‑value list, where each column corresponds to a bit (1 = NULL, 0 = not NULL). Bits are stored in reverse column order and padded to whole bytes.

Illustrations of the NULL list for two records:

3.5 What if a column’s data is huge?—Overflow columns in Dynamic format

When a column’s data exceeds the space available in the row, InnoDB stores only a 20‑byte pointer in the row; the actual data is kept in overflow pages linked as a singly‑linked list.

Later

For more on other InnoDB row formats or record header details, refer to the book “How MySQL Works”. This article focuses on the default Dynamic format with the utf8mb4 charset, which is most relevant to everyday development.