How Many Rows Can a Single MySQL Table Actually Store? A Deep Dive

Theoretical Foundations

InnoDB stores indexes and data in B+ trees. A non‑clustered index stores only index pointers in leaf and internal nodes; a clustered index stores the full row data in leaf nodes. The B+‑tree height is typically ≤ 3 levels.

Page Layout

Each B+‑tree node occupies a 16 KB page. The fixed‑size components are:

File Header – 38 bytes

Page Header – 56 bytes

Infimum & Supremum – 26 bytes

File Trailer – 8 bytes

Total fixed overhead = 128 bytes. InnoDB reserves 1/16 of the remaining space for future inserts, leaving 15/16 for user data. Usable space per page:

15/16 * 1024 - 128 = 15232 bytes

Row Format

MySQL row storage uses COMPACT (default ≤ 5.6) or DYNAMIC (default ≥ 5.7). A row consists of:

Record header – 5 bytes

Variable‑length column list – variable

NULL‑value bitmap – 1 bit per nullable column

Transaction ID – 6 bytes

Rollback pointer – 7 bytes

Actual column data – variable

Leaf‑Node Capacity Calculation

Maximum records in a 3‑level B+ tree

With three levels, the maximum number of records is x² * y, where x = pointers per index page and y = records per leaf page.

Pointer size and pointers per page

Assuming a BIGINT primary key (8 bytes) and an index record that also stores a 6‑byte page pointer and a 5‑byte row header, each index pointer occupies 19 bytes.

Usable pointers per page ≈ 15232 / 19 ≈ 801. Accounting for page‑directory slots (≈6 pointers per slot, ≈268 bytes total) reduces the count to:

BIGINT keys: (15232 - 268) / 19 ≈ 787 pointers per page

INT keys (4‑byte primary key): 993 pointers per page

Some references report up to 1.6 million leaf nodes for BIGINT keys; the calculation above provides a conservative estimate.

Number of leaf nodes

BIGINT primary key: 787² = 619,369 leaf nodes

INT primary key: 993² = 986,049 leaf nodes

Total Record Count

Minimum records (large rows)

If each leaf page can hold at least two rows of roughly 8 KB (the maximum that fits without overflow), the minimum total rows for an INT primary key are:

2 × 986,049 ≈ 1.24 million rows

Maximum records – concrete example

CREATE TABLE `course_schedule` (
  `id` int NOT NULL,
  `teacher_id` int NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Row size calculation (no NULLs, no variable‑length columns): 4 + 4 + 4 + 6 + 7 + 5 = 30 bytes.

Rows per leaf page: 15232 / 30 ≈ 507, reduced to 502 after directory overhead.

Maximum total rows for INT primary key:

502 × 986,049 ≈ 5.0×10⁸ rows

Practical scenario – blog table

CREATE TABLE `blog` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `author_id` bigint unsigned NOT NULL,
  `title` varchar(50) NOT NULL,
  `description` varchar(250) NOT NULL,
  `school_code` bigint unsigned DEFAULT NULL,
  `cover_image` char(32) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `release_time` datetime DEFAULT NULL,
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint unsigned NOT NULL,
  `is_delete` tinyint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `school_code` (`school_code`) USING BTREE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Per‑row byte breakdown (DYNAMIC format, assuming typical content):

Record header: 5 bytes

Variable‑length column list (title 1 byte, description 2 bytes): 3 bytes

NULL bitmap (3 nullable columns): 1 byte

Transaction ID + rollback pointer: 13 bytes

BIGINT fields (id, author_id, school_code): 24 bytes

DATETIME fields (create_time, release_time, modified_time): 24 bytes

Tinyint fields (status, is_delete): 2 bytes

CHAR(32) cover_image: 32 bytes

VARCHAR(50) title + VARCHAR(250) description: average 765 bytes (70 % Chinese 3 bytes, 25 % English 1 byte, 5 % emoji 4 bytes)

Total ≈ 869 bytes per row. Rows per leaf page: 15232 / 869 ≈ 17 (directory overhead does not reduce this count).

Maximum total rows for BIGINT primary key:

17 × 619,369 ≈ 10.5 million rows

Key Observations

The B+‑tree depth, page layout, and row format together determine the theoretical upper bound of rows a single InnoDB table can hold.

DYNAMIC row format stores long column values off‑page, reducing the amount of data that must reside in leaf nodes.

Actual production limits may differ due to fragmentation, additional indexes, and storage engine settings, but the presented methodology offers a reproducible way to estimate capacity.

References

掘金阿杆: https://juejin.cn/post/7165689453124517896

小白 Debug: https://mp.weixin.qq.com/s/AOtXJNmt50KowkSDN-FlhA