Understanding MySQL Single‑Table Size Limits and InnoDB Page Structure

Background: In the backend community, a common belief is that a MySQL single table should not exceed 20 million rows, otherwise performance degrades. The author decides to push the limits and conduct experiments.

Experiment: A simple person table is created, a row is inserted, and a pseudo‑column rownum is generated using user variables. By repeatedly executing an INSERT ... SELECT statement that doubles the number of rows each time, millions of rows can be generated quickly. The script also shows how to limit the growth with a WHERE id > ... clause.

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 '人员信息表';

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;

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;

SET GLOBAL tmp_table_size = 512*1024*1024;  -- 512M
SET GLOBAL innodb_buffer_pool_size = 1*1024*1024*1024;  -- 1G

When the row count approaches 8‑10 million, MySQL may raise the error "The total number of locks exceeds the lock table size"; increasing tmp_table_size and innodb_buffer_pool_size resolves this.

Single‑Table Row Limit: The maximum number of rows is constrained by the primary key type. An INT (32‑bit) allows up to 2^32‑1 (~21 billion) rows, while BIGINT (64‑bit) permits up to 2^62‑1 (~4.6 × 10^18) rows. Using an unsigned BIGINT raises the ceiling to 18,446,744,073,709,551,615.

Table Space: InnoDB stores data in .ibd files divided into 16 KB pages. Each page contains a fixed header/footer and a variable data area (≈15 KB). Index pages store the smallest primary‑key value and the page number; leaf pages store actual rows.

Page Data Structure: A page holds a file header, page header, infimum/supremum records, and a file trailer, leaving about 15 KB for user data. For index pages, each entry (primary‑key + page number) occupies 12 bytes, allowing roughly 1,280 entries per page (x ≈ 1280). For leaf pages, assuming an average row size of 1 KB, about 15 rows fit per page (y ≈ 15).

Index Structure: InnoDB uses a B+‑tree where leaf nodes contain rows and non‑leaf nodes contain key‑page pointers. The total number of rows a B+‑tree can hold is Total = x^(z‑1) * y , where z is the tree height.

Recommended Values: With x ≈ 1280, y ≈ 15, and a typical three‑level B+‑tree (z = 3), the theoretical maximum is 1280^2 * 15 ≈ 24.6 million rows, matching the commonly cited 20 million recommendation. If rows are larger (e.g., 5 KB), y drops to 3, yielding 1280^2 * 3 ≈ 4.9 million rows.

Conclusion: MySQL tables store data in 16 KB pages; the practical row limit depends on primary‑key size, row size, and B+‑tree depth. Exceeding the 20 million‑row guideline may increase tree height, causing more disk I/O and slower queries, but proper hardware (enough memory for the buffer pool) can mitigate performance loss.