MySQL使用InnoDB存储引擎下,索引使用B+树的数据结构,B+树上页的默认大小为16KB, 每一个页的存储结构如下:
B+树上非叶子节点存放索引,非叶子节点里的每条数据都指向新的页,真正一行行的数据存放在叶子节点。假设:
-
非叶子节点指向其他页的数量为 x
-
叶子节点能容纳的数据行数为 y
-
B+ 树的层数为 z
可存放的总记录数Total =x^(z-1) *y ,也就是说总数等于 x 的 z-1 次方 与 Y 的乘积。
对于页结构中的File Header (38 B)、Page Header (56 B)、Infimum + Supermum(26 B)、File Trailer(8 B), 再加上页目录,大概 1kB左右,所以大概有15KB的大小用于存放用户记录数据。
对于非叶子节点,用户记录数据主要存放的数据是主键和页号,主键我们假设是 Bigint (8 B), 而页号也是固定的(4 B), 那么索引页中的一条数据也就是 12B。所以 x=15*1024/12≈1280 。
对于叶子节点,用户记录数据主要存放的是主键和真正的行数据,这个影响的因素就会多很多,比如,字段的类型,字段的数量。每行数据占用空间越大,页中所放的行数量就会越少。如果按一条行数据大概1kB 来算,那一页就能存下 15 条,Y = 15KB/1KB =15。
根据上述的公式,Total =x^(z-1) *y,已知 x=1280,y=15:
-
假设 B+ 树是两层,那就是 z = 2, Total = (1280 ^1 )*15 = 19200
-
假设 B+ 树是三层,那就是 z = 3, Total = (1280 ^2) *15 = 24576000 (约 2.45kw)
为了不影响查询性能,一般 B+ 树的高度也就是 3 层。所以也就有了单表建议值2000W的说法!