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

The author, a seasoned backend engineer, questions the popular belief that a MySQL table should not exceed 20 million rows and conducts experiments to push the limit far beyond that threshold.

Experiment: A simple person table is created, a single row is inserted, and a user‑defined variable @i is used to generate sequential row numbers. By repeatedly executing an INSERT … SELECT statement that doubles the row count each run, the table quickly reaches millions of rows; the author notes that when the row count approaches 8–10 million, MySQL may report “The total number of locks exceeds the lock table size,” which can be mitigated by increasing tmp_table_size and innodb_buffer_pool_size :

SET GLOBAL tmp_table_size = 512*1024*1024;  -- 512 MiB
SET GLOBAL innodb_buffer_pool_size = 1*1024*1024*1024*1024;  -- 1 GiB

Test results on MySQL 8.0 (run on a personal machine with other tools active) show query latency sharply increasing after the table surpasses roughly 20 million rows, supporting the conventional guideline.

Maximum Row Count Theory: The primary key size determines the absolute upper bound (e.g., INT → 2³¹‑1 ≈ 2.1 billion, BIGINT UNSIGNED → 2⁶⁴‑1). However, practical limits arise from InnoDB’s storage architecture.

InnoDB stores data in 16 KB pages within a tablespace file ( *.ibd ). Each page contains a fixed header, trailer, and a directory, leaving about 15 KB for actual records. Index pages (B+Tree nodes) store the minimum primary‑key value and the page number; with a BIGINT key (8 bytes) and a 4‑byte page pointer, each index entry occupies 12 bytes, allowing roughly 1 280 entries per index page (x ≈ 1280).

Data pages hold full rows. Assuming an average row size of 1 KB, a data page can store about 15 rows (y ≈ 15). The total number of rows a B+Tree can address is Total = x^(z‑1) * y , where z is the tree depth. With a typical three‑level B+Tree (z = 3), the capacity is 1280² * 15 ≈ 24.6 million , which aligns with the 20 million‑row recommendation. Larger rows (e.g., 5 KB) reduce y to 3, yielding a capacity of about 5 million rows.

The analysis concludes that the 20 million‑row guideline stems from the practical limits of a three‑level B+Tree given typical page and row sizes; exceeding this may increase tree depth, causing more disk I/O and slower queries. Performance can also be improved by enlarging memory buffers or upgrading hardware.

Summary of Key Points:

MySQL tables are stored as 16 KB pages; not all space is usable for data.

Page headers, footers, and metadata consume roughly 1 KB, leaving ~15 KB for rows or index entries.

In a B+Tree, leaf pages store actual rows, while non‑leaf pages store primary‑key and page‑number pairs.

The commonly cited 20 million‑row limit reflects a three‑level B+Tree with typical row sizes; larger rows lower the practical limit.