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/，如需轉載，請註明出處，否則將追究法律責任。