How Many Rows Can a MySQL Table Really Hold? A Deep Dive into B+ Tree Limits

01 Theory Knowledge

B+ Tree

MySQL stores data using a B+ tree. Key concepts:

Non‑clustered index leaf and non‑leaf nodes store index pointers.

Clustered index non‑leaf nodes store pointers; leaf nodes store the actual data rows.

InnoDB B+ tree height is usually ≤ 3 levels.

Below is a diagram of a 3‑level clustered index B+ tree:

Page Storage

B+ tree nodes are stored as pages of 16 KB.

Page structure (simplified):

File Header – 38 bytes

Page Header – 56 bytes

Infimum & Supremum – 26 bytes

User Records – variable

Free Space – variable

Page Directory – variable

File Trailer – 8 bytes

Effective usable space per page = 15/16 × 1024 KB – 128 bytes = 15232 bytes .

02 Leaf Node Calculation

3‑Level B+ Tree Maximum Data

Maximum data = x² × y, where x = number of leaf pointers per page, y = records per leaf.

Leaf Node Pointer Size

Assuming a BIGINT primary key (8 bytes) plus a 6‑byte pointer and a 5‑byte row header, each index pointer occupies 19 bytes .

Single page can store 15232 / 19 ≈ 801 pointers; after accounting for page directory slots (≈ 14 bytes), about 787 BIGINT pointers or 993 INT pointers per leaf page.

Leaf Node Count

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

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

Total Record Count

Assuming each leaf stores two 8 KB rows (minimum), the minimum total records for INT primary key ≈ 2 × 986 049 ≈ 1.24 million.

Assuming dense packing (e.g., 30‑byte rows), each leaf can hold ≈ 502 rows, giving a maximum ≈ 5 × 10⁸ records for INT primary key.

03 Actual Scenarios

Example 1: Simple Table

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 = 4 + 4 + 4 + 6 + 7 + 5 = 30 bytes . Each leaf can store 15232 / 30 ≈ 507 rows; after page directory overhead ≈ 502 rows.

Maximum total records = 502 × 986 049 ≈ 5 × 10⁸.

Example 2: Blog Table

CREATE TABLE `blog` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `author_id` bigint unsigned NOT NULL COMMENT '作者id',
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '标题',
  `description` varchar(250) CHARACTER SET utf8mb4 NOT NULL COMMENT '描述',
  `school_code` bigint unsigned DEFAULT NULL COMMENT '院校代码',
  `cover_image` char(32) DEFAULT NULL COMMENT '封面图',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `release_time` datetime DEFAULT NULL COMMENT '首次发表时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint unsigned NOT NULL COMMENT '发表状态',
  `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 AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Detailed row analysis:

Row header: 5 bytes

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

NULL bitmap (3 nullable columns): 1 byte

Transaction ID + 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

Cover image char(32): 32 bytes

Varchar content (estimated 765 bytes based on typical UTF‑8 distribution)

Total ≈ 869 bytes per row. Each leaf can hold 15232 / 869 ≈ 17 rows.

Maximum total records for BIGINT primary key = 17 × 619 369 ≈ 10.5 million.

04 Summary

The theoretical limit of a MySQL single table can reach hundreds of millions or even billions of rows, but realistic limits are governed by row size, index type, and storage engine overhead. Understanding B+‑tree structure, page layout, and row composition allows accurate estimation of capacity for specific schemas.