mysql innodb 行数_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字节，那么最大空间

将不会达到这么大，而行数更是受到实际一行数据大小限制，这里只是以文章开头建立的表为列子。

作者微信：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-2139931/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-2139931/