Understanding InnoDB Primary‑Key B+Tree Capacity and Height

InnoDB stores data in 16 KB pages, which are the smallest storage unit for the engine; a page can hold roughly 16 rows if each row is about 1 KB.

Each page can also store key‑value pairs and pointers; with an 8‑byte bigint primary key and a 6‑byte pointer, a page can hold about 1 170 such entries.

For a B+Tree of height 2 (root + leaf nodes), the maximum number of rows is 1 170 × 16 ≈ 18 720. For height 3, it becomes 1 170² × 16 ≈ 21 902 400 rows, which easily satisfies millions of records with only 1‑3 I/O operations.

The root page of a primary‑key index is always page number 3 in the tablespace file; the page level stored at offset 64 of the root page indicates the tree height (height = page level + 1). Using SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0; you can verify the root page numbers.

Hexdump tools can read the page level directly from the file offset (e.g., 16384 × 3 + 64 = 49216) to confirm the B+Tree height.

Practical examples on tables like lineitem , customer , and region show heights of 3, 3, and 1 respectively, demonstrating that even with large data volume the index depth remains shallow.

The article also answers the interview question of why MySQL uses B+Tree instead of B‑Tree: B+Tree stores only keys in internal nodes, allowing higher fan‑out, lower height, and fewer I/O operations.

Reference: Jiang Chengyao, MySQL Technical Inside: InnoDB Storage Engine .