MYSQL INNODB 怎样计算B+树表的最大容量和行数
发布时间：2022-03-30 11:41:47 所属栏目：建站 来源：互联网
导读：考虑表结构如下： 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 i10000 do insert into testzh(id2,id3) values(FLOOR((RAND()*100000
 考虑表结构如下： 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字节，那么最大空间 将不会达到这么大，而行数更是受到实际一行数据大小限制，这里只是以文章开头建立的表为列子。 （编辑：晋江门户网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
