Understanding MySQL Single‑Table Row Limits and InnoDB Page Structure

1. Background In the MySQL community it is often said that a single table should not exceed 20 million rows, otherwise query performance degrades. The author treats this as a hypothesis to be tested.

2. Experiment A table person is created with columns id , person_id , person_name , gmt_create , and gmt_modified . Sample data is inserted, and a MySQL variable @i is used to generate a large number of rows by repeatedly inserting with exponential growth (2^20 ≈ 1 M rows, 2^23 ≈ 8 M rows, etc.). The script also shows how to control the amount of data with a WHERE clause.

CREATE TABLE person(
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'primary key',
  person_id tinyint NOT NULL COMMENT 'user id',
  person_name VARCHAR(200) COMMENT 'user name',
  gmt_create datetime COMMENT 'creation time',
  gmt_modified datetime COMMENT 'modification time'
) COMMENT 'person information table';

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

SELECT (@i:=@i+1) AS rownum, person_name FROM person, (SELECT @i:=100) AS init;
SET @i=1;

INSERT INTO person(id, person_id, person_name, gmt_create, gmt_modified)
SELECT @i:=@i+1,
       LEFT(RAND()*10,10),
       CONCAT('user_',@i%2048),
       DATE_ADD(gmt_create, INTERVAL + @i*CAST(RAND()*100 AS SIGNED) SECOND),
       DATE_ADD(DATE_ADD(gmt_modified, INTERVAL +@i*CAST(RAND()*100 AS SIGNED) SECOND), INTERVAL + CAST(RAND()*1000000 AS SIGNED) SECOND)
FROM person;

If the number of rows approaches 8 M–10 M, MySQL may raise the error “The total number of locks exceeds the lock table size”. The fix is to increase temporary table and buffer pool sizes:

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

3. Single‑Table Row Limit The maximum number of rows is ultimately bounded by the size of the primary‑key column. An INT (32‑bit) can hold up to 2^32‑1 ≈ 21 billion values; a BIGINT (64‑bit) can hold up to 2^62‑1 ≈ 4.6 × 10^18. In practice, other factors (disk space, memory, index size) cause tables to become unusable far before these theoretical limits.

Using an unsigned BIGINT as an auto‑increment column gives a maximum of 18 446 744 073 709 551 615. At a rate of one insert per second, it would take millions of years to exhaust.

4. Table Space InnoDB stores data in files such as person.ibd . Each file is divided into 16 KB pages, which are further split into fixed‑size headers, footers, and a free‑space area. Only the remaining space (~15 KB) is available for actual row data.

5. Page Data Structure A page contains a header, a directory, user records, and a trailer. New rows are allocated from the free‑space area; when it is exhausted, a new page is allocated.

6. Index Data Structure InnoDB indexes are also stored in 16 KB pages. Each index entry records the minimum primary‑key value of the child page and the child page number, forming a B+‑tree. Leaf pages store the actual row pointers; non‑leaf pages store only the key‑page mappings.

7. Suggested Single‑Table Value Assuming a three‑level B+‑tree with a branching factor of about 1 280 (derived from 15 KB / 12 bytes per index entry) and roughly 15 rows per leaf page (1 KB per row), the total row capacity is:

Total = x^(z‑1) * y
x = 1280, y = 15, z = 3 → Total ≈ 2.45 × 10^7 rows (≈ 20 M)

If rows are larger (e.g., 5 KB each), a leaf page holds only 3 rows, reducing the capacity to about 5 M rows. Thus the “20 M rows” guideline corresponds to a three‑level B+‑tree with typical row sizes.

8. Summary MySQL tables are stored as 16 KB pages; not all space is usable for rows due to headers and metadata. InnoDB’s B+‑tree index structure determines how many rows can be efficiently accessed. When a table grows beyond the point where its indexes fit in memory, disk I/O increases and performance degrades, which explains the practical recommendation of keeping single‑table row counts around 20 million.

9. References

https://www.jianshu.com/p/cf5d381ef637

https://www.modb.pro/db/139052

《MYSQL 内核：INNODB 存储引擎 卷 1》