How Many Rows Can an InnoDB B+ Tree Store? Explanation, Calculations, and Practical Verification

InnoDB uses a B+‑tree index to store rows, and a common question is how many rows a single B+‑tree can hold; the short answer is roughly 20 million rows.

To understand this, we first review the storage hierarchy: a disk sector is 512 bytes, a filesystem block (e.g., XFS/EXT4) is 4 KB, and InnoDB’s minimal storage unit, a page, is 16 KB.

Assuming each row occupies about 1 KB, a single page can store 16 rows (16 KB / 1 KB). InnoDB pages are always a multiple of 16 KB, and the .ibd files are sized accordingly.

In a B+‑tree, leaf pages store the actual row data, while non‑leaf pages store key‑value pairs plus pointers. If the primary key is a BIGINT (8 bytes) and the pointer size is 6 bytes, each entry consumes 14 bytes, allowing a page to hold 16384 / 14 ≈ 1170 pointers.

Using these numbers, a B+‑tree of height 2 (root + leaf) can store 1170 × 16 = 18 720 rows. A height 3 tree can store 1170 × 1170 × 16 ≈ 21 902 400 rows. Since InnoDB B+‑trees typically have heights of 1‑3, they comfortably support tens of millions of rows with only 1‑3 I/O operations per lookup.

The root page of a primary‑key index is always page number 3 in the tablespace file, and the page level (tree height – 1) is stored at offset 64 within that page. By examining the tablespace file with hexdump at offset 16384 × 3 + 64 = 49 216, we can read the page level value.

Examples from the TPC‑H benchmark show:

lineitem table: page level 2 → tree height 3, ~6 million rows

customer table: page level 2 → tree height 3, ~150 k rows

region table: page level 0 → tree height 1, 5 rows

These cases demonstrate that even with large differences in row count, the tree height remains low, keeping index lookup performance consistent.

Interview question: why does MySQL use B+‑trees instead of B‑trees? Because B‑trees store data in both leaf and internal nodes, reducing the fan‑out (number of pointers) and increasing tree height, which leads to more I/O operations. B+‑trees store data only in leaves, maximizing fan‑out and minimizing height.

In summary, the article starts from a simple question, explains the principles of InnoDB’s index‑organized tables, shows how to calculate storage capacity, and validates the theory with real‑world metadata inspection.

select * from user where id=5;

5    zhao2    27