MYSQL INNODB 如何计算B+树表的最大容量和行数

考虑表结构如下：
create table testzh(id int  primary key auto_increment ,id2 int,id3 int);
插入数据：
delimiter //
 create procedure ins3()
     begin
    declare i int;
     set i=0;
     while i<10000 do
         insert into testzh(id2,id3) values(FLOOR((RAND()*100000)),FLOOR((RAND()*100000)));
         set i=i+1;
     end while;
  end;
//
delimiter ;


这里仅仅考虑聚合索引的B+数结构。
首先我们要考虑2个因素：
1、分支节点如何存储一行数据
2、叶子结点如何存储一行数据

位了找到这个问题我们必须要找到哪些块是叶子结点，哪些块是非叶子结点，这里使用自己写的一个程序
找到详细参考最后的代码
(http://blog.itpub.net/7728585/viewspace-2128817/)
[root@testmy test]# ./t1  testzh.ibd
file size is 442368
Block id is 3:Index page no is 552 : B+ Tree Level is 1
Block id is 4:Index page no is 552 : B+ Tree Level is 0
Block id is 5:Index page no is 552 : B+ Tree Level is 0
Block id is 6:Index page no is 552 : B+ Tree Level is 0
Block id is 7:Index page no is 552 : B+ Tree Level is 0
Block id is 8:Index page no is 552 : B+ Tree Level is 0
Block id is 9:Index page no is 552 : B+ Tree Level is 0
.....

可以看到在这个文件中block_id = 3的是非叶子结点
其他的块是叶子结点。
那我们来研究第一个问题
1、分支节点如何存储一行数据
其实这个问题答案就是
6字节固定开销+4字节(int数据类型4字节)+4字节(指向叶子结点的指针开销)
我们知道每个数据库块的前120直接是管理固定开销如：
FILE HEADER,INDEX HEADER等
在块尾部也有8字节的固定开销
那么我们从offset 120开始向后面数14个字节，这里也要使用我自己写的工具
bcview 方便查看
./bcview testzh.ibd 16 120 14
current block:00000003--Offset:00120--cnt bytes:14--data is:00100011000e8000000100000004
得到数据:
00100011000e8000000100000004
分析一下：
00100011000e8000000100000004

固定开销(6字节)
00    nullable field bitmap (?)
10    info flags+number of records owned
0011  order+ record type (0000 0000 0001 0001)
000e     下一个偏移量
--可变开销(实际数据4字节)
80000001 (实际主键数据1其中8是符号位)
--固定开销(4字节)
00000004 (叶子结点block指针)
我们可以看到这是非叶子结点存储数据的格式如此，除了4字节的主键外,这里包含了10字节的额外开销。

2、叶子结点如何存储一行数据
接下来我们来看一下这个表的每一行数据是如何存放的，二进制如下：
./bcview testzh.ibd 16 120 31
current block:00000004--Offset:00120--cnt bytes:31--data is:00000010001f800000010000004d1995cd000001440110800046cd80000683

00 nullable field bitmap(?)
00 info flags+number of records owned
0010 order+record type
001f 下一个偏移量
80000001 (实际主键id数据1其中8是符号位)
0000004d1995  transaction id 
cd000001440110 roll pointer
800046cd (实际数据id2:18125 8是符号位)
80000683 (实际数据id2:1667  8是符号位)


实际上就是31个字节
那么我们很容易计算出来如果满存储行大约(16*1024-128(块头块尾部))/31 = 524 行数据。当然实际上存储达不到这个值受到
B+树分裂行为以及填充因子等限制实际上到不了这个值，我这里去大约500行数据

好了对于这张表这里我们可以实际大约计算一下理论值，实际值将略小：

一层B+树     最大16K空间                                                                                         约500行数据
二层B+树     最大约18M空间((16*1024-128)/14 * 16/1024 )                                         约58000行数据((16*1024-128)/14 *500)
三层B+树     最大约21000M空间(power((16*1024-130)/14,2) * 16/1024)                       约673960500行数据（power((16*1024-130)/14,2) * 500）
四层B+树     最大约24452000M空间(power((16*1024-130)/14,3) * 16/1024)                  约782468140500行数据（power((16*1024-130)/14,3) * 500）


但是要注意这里最大空间受到主键选择影响很大，如果不是int为主键那么其非叶子结点一行数据将不会是14字节如果是long类型将是18字节，那么最大空间
将不会达到这么大，而行数更是受到实际一行数据大小限制，这里只是以文章开头建立的表为列子。

作者微信：