Why MySQL Single Tables Should Stay Under 20 Million Rows: A B+ Tree Deep Dive

Background: The 20 Million Row Rule

Backend developers often hear that a MySQL single table should not exceed 20 million rows (2000w). This article investigates the origin of this guideline through experiments and InnoDB internals analysis.

Experiment: Generating Test Data

The author creates a person table with columns id (INT PRIMARY KEY), person_id (TINYINT), person_name (VARCHAR(200)), gmt_create, and gmt_modified. Starting with one row, they use a recursive INSERT ... SELECT pattern that doubles the row count each execution. Running it 20 times yields ~1 million rows; 23 times yields ~8 million. A tmp_table_size increase to 512MB and innodb_buffer_pool_size to 1GB avoids "lock table size" errors during bulk inserts.

Primary Key Limits

An INT primary key (32-bit) supports up to 2^32-1 ≈ 2.1 billion rows. A BIGINT (64-bit) supports 2^64-1 ≈ 1.8×10^19 rows — far beyond practical storage limits. The real constraint is not the primary key range but index structure and performance.

InnoDB Page Structure

InnoDB stores data in 16KB pages ( .ibd files). Each page contains seven sections: File Header, Page Header, Infimum/Supremum Records, User Records, Free Space, Page Directory, and File Trailer. Records are inserted into Free Space; when exhausted, a new page is allocated. The Page Directory enables binary search within a page for efficient lookups.

B+ Tree Index Structure

InnoDB uses B+ trees for indexes. Both leaf and non-leaf nodes are 16KB pages. Non-leaf nodes store (minimum primary key, page number) pairs pointing to child pages. Leaf nodes (page level = 0) store full row data. The tree grows upward as data increases.

Calculating the 20 Million Threshold

Assume each non-leaf entry (key + pointer) occupies ~1KB (Y=1024 bytes). A 16KB page holds ~16 entries (Z=16). With a 3-level tree (root + 1 intermediate + leaf):

Root: 1 page → 16 pointers

Level 1: 16 pages → 16 × 16 = 256 pointers

Leaf: 256 pages × rows per leaf page

If each row is ~1KB, a leaf page holds ~16 rows. Total rows = 256 × 16 = 4,096. But the article uses a different fan-out: assuming 1280 entries per non-leaf page (Y≈12.8 bytes/entry), 3 levels give 1280² × 16 ≈ 26 million rows. The author notes that 3 levels is a practical maximum; a 4th level would push totals to hundreds of billions and add an extra disk I/O per query.

Row Size Impact

If row size grows to 5KB, a leaf page holds only 3 rows. With the same 3-level tree: 1280² × 3 ≈ 4.9 million rows. Thus the "safe" row count varies inversely with row size.

Other Performance Factors

When table size exceeds the InnoDB buffer pool, indexes spill to disk, causing random I/O and latency spikes. Increasing memory (or using memory-backed storage) can restore performance. SQL quality, MySQL version, and hardware also matter.

Summary

MySQL stores data in 16KB pages; not all space is for user records.

B+ tree leaf nodes hold row data; non-leaf nodes hold (key, page pointer) pairs.

The 20 million figure is a recommendation, not a hard limit. Exceeding it may increase tree height, adding disk I/O.

Actual threshold depends on row size, buffer pool, and workload.