Understanding MySQL Single‑Table Size Limits and B+Tree Storage Structure

The author, a seasoned backend developer, starts by questioning the common advice that a MySQL single table should not exceed 20 million rows, and proposes an experiment to push the limit.

Experiment 1 – Table creation and data insertion

A simple person table is created:

CREATE TABLE person(
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  person_id tinyint NOT NULL COMMENT '用户id',
  person_name VARCHAR(200) COMMENT '用户名称',
  gmt_create datetime COMMENT '创建时间',
  gmt_modified datetime COMMENT '修改时间'
) COMMENT '人员信息表';

One row is inserted to verify the schema:

INSERT INTO person VALUES (1,1,'user_1',NOW(),NOW());

Using a MySQL user variable @i the script generates a pseudo‑column rownum and then repeatedly inserts exponentially growing batches of rows (2^20 ≈ 1 M, 2^23 ≈ 8 M, etc.). When the total number of rows approaches 8–10 million, the error “The total number of locks exceeds the lock table size” may appear; the fix is to increase tmp_table_size and innodb_buffer_pool_size :

SET GLOBAL tmp_table_size = 512*1024*1024;  -- 512 MB
SET GLOBAL innodb_buffer_pool_size = 1*1024*1024*1024;  -- 1 GB

Performance tests on MySQL 8.0 (run on a development machine) show query latency rising sharply after the table reaches roughly 20 million rows, confirming the practical limit.

Understanding the storage limit

MySQL InnoDB stores data in 16 KB pages. Each page contains a fixed header/footer (≈1 KB) and the remaining ~15 KB for records. With a BIGINT primary key (8 bytes) and a 4‑byte page number, an index entry occupies about 12 bytes, allowing roughly 1 280 index entries per page ( x ≈ 1280 ). A leaf page can hold about 15 rows if each row is ~1 KB ( y ≈ 15 ).

The total number of rows a B+Tree can hold is approximated by Total = x^(z‑1) * y , where z is the tree depth. For a typical three‑level B+Tree ( z = 3 ), Total ≈ 1280² * 15 ≈ 24.6 million , which matches the 20 million recommendation. If rows are larger (e.g., 5 KB), y drops to 3, reducing the total to about 5 million.

The article also explains how InnoDB pages are organized (file header, page header, infimum/supremum, trailer, and record slots) and how index pages mirror data pages but store only the minimum key and page pointer. It illustrates the B+Tree search process, showing that locating a row may require loading several pages, each causing a disk I/O if not cached.

Conclusion

1. MySQL stores table data in 16 KB pages; not all space is usable for rows. 2. The practical single‑table limit of ~20 million rows stems from B+Tree depth and page capacity rather than a hard MySQL constraint. 3. Larger rows or deeper trees reduce the effective limit, and exceeding the limit leads to more disk I/O and slower queries. 4. Proper configuration of buffer pool size and temporary table size can mitigate lock‑related errors during massive inserts.