關於MYSQL INNODB index page header學習和實驗總結

關於INNODB  index header


所用到的工具是自己寫的mysqlblock和bcview，
我放到了百度雲盤

供大家下載和使用


普通表空間(及設定了innodb_file_per_table每個表都對應一個idb檔案)從第4個塊開始通常是innodb的資料頁。
前38位元組為FILE HEADER
從38位元組到74位元組為INDEX HEADER，如下：


number of directory slot            2bytes 槽的個數，
heap top position                   2bytes 塊的最高資料記錄的偏移地址
number of heap records/format flag  2bytes 行的記錄數量但是第15位為行模式的標示如果15位為1，就是COMPACT模式
first garbage record offset         2bytes 第一行刪除記錄的偏移量
garbage space                       2bytes 刪除的空間大小單位bytes
last insert position                2bytes 最後一個插入的位置偏移量
page direction                      2bytes 行的插入方向，取值為左，右或者無順序 0x02 右 0x01 左  0x05 無序
number of inerts in page direction  2bytes 同一方向插入資料的行數，如果方向改變則重置
number of record                    2bytes 總的行數
maximum transaction id              8bytes 最大事物的ID
page level                          2bytes 頁的級別，頁節點為0，然後網上加1，如果3層，根節點為 2，分支節點為1，頁節點為0
index ID                            8bytes 索引的ID這個在INNODB_SYS_INDEXES也是有的


接下來分析我設定了innodb_file_per_table
create table km1(id int ,name varchar(20));
insert into km1 values(1,'gaopeng');
insert into km1 values(2,'gaopeng');
insert into km1 values(3,'gaopeng'); 
insert into km1 values(4,'gaopeng'); 


分析檔案：
[root@hadoop1 test]# mysqlblock km1.ibd -d|more
***************************************************
USEAGE: mysqlblock datafile -t/-d                  
This small tool used in study and test database,not
uesd on online database!                           
This tool is used to find how many blocks and types
in specified datafile,Exp:how many undo block in d 
ata file!                                          
QQ:2238980                                         
***************************************************
-t Only Total blocks types in ibdata!              
-d Blocks types detail  in ibdata!                 
***************************************************
FILE SIZE IS : 98304
current read blocks is : 0 --This Block is file space header blocks!
current read blocks is : 1 --This Block is insert buffer bitmap  blocks!
current read blocks is : 2 --This Block is inode blocks!
current read blocks is : 3 --This Block is data blocks( index pages)!
current read blocks is : 4 --This Block is new allocate blocks!
current read blocks is : 5 --This Block is new allocate blocks!
Total Block Status    :
Total  block                   :     6,Total size is: 0.093750 MB
Total undo block               :     0,Total size is: 0.000000 MB
Total inode block              :     1,Total size is: 0.015625 MB
Total insert buffer free blocks:     0,Total size is: 0.000000 MB
Total data(index pages) block  :     1,Total size is: 0.015625 MB
Total new allocate blocks      :     2,Total size is: 0.031250 MB
Total insert buf bitmap blocks :     1,Total size is: 0.015625 MB
Total system blocks            :     0,Total size is: 0.000000 MB
Total transaction system blocks:     0,Total size is: 0.000000 MB
Total file space header blocks :     1,Total size is: 0.015625 MB
Total extrenl disc blocks      :     0,Total size is: 0.000000 MB
Total LOB blocks               :     0,Total size is: 0.000000 MB
Total Unkown blocks            :     0,Total size is: 0.000000 MB


我這裡資料很少，值有3條，所以一共就只有6個塊，塊3就是資料塊(0 開始)使用

1、number of directory slot
bcview km1.ibd 16 38 2|more
current block:00000003--Offset:00038--cnt bytes:02--data is:0002
這裡有一個槽的概念，他實際上是用於更快的定位資料，以後的文章會更加細節的研究學習

2、heap top position 
bcview km1.ibd 16 40 2|more
current block:00000003--Offset:00040--cnt bytes:02--data is:010c
表明記錄的最高為出現在偏移量10c，及十六進位制的268

3、number of heap records/format flag 

bcview km1.ibd 16 42 2|more
current block:00000003--Offset:00042--cnt bytes:02--data is:8006
這裡8006十六進位制，就說明是compact模式的行記錄，如果使用Redundant我們看看
 create table km3 (id int,name varchar(20)) ROW_FORMAT = Redundant;
current block:00000003--Offset:00042--cnt bytes:02--data is:0002
可以看到這裡第15為為0，說明是Redundant格式
而我們的行記錄是6，明明只是插入了4條記錄為什麼是6呢，其實MYSQL的每個塊裡面有
2個虛擬列infimum 和supremum，他們都在固定的位置，而 infimum指向了第一條記錄的
offset，而最後一條記錄的offset表示為supremum的offset，這樣形成了行記錄的一個
單項鍊表。
4、first garbage record offset 
bcview km1.ibd 16 44 2|more
current block:00000003--Offset:00044--cnt bytes:02--data is:0000
為0，如果說明沒有刪除的記錄，如果此時我們來delete 2條記錄
delete from km1 where id in (1,3);
(由於bcview為基於檔案的工具，修改的資料髒資料解除安裝buffer中，所以我重啟了一次資料才能看到)
再次檢視
current block:00000003--Offset:00044--cnt bytes:02--data is:00c9
看到這裡為c9及offset 201

5、garbage space
bcview km1.ibd 16 46 2|more
current block:00000003--Offset:00046--cnt bytes:02--data is:004a
這裡刪除的位元組為4a及74個位元組，為什麼我刪除了2行資料卻有這麼多位元組呢？
其實每行的資料除了資料本身還有很還有24個位元組左右的開銷，並且我們這裡
沒有主鍵的表MYSQL INNODB會自動生成一個6位元組的48位的ROWID，那麼加上就是
大約30個位元組的開銷，如果有建表的時候加上了主鍵ROWID 6位元組開銷就沒了，同樣
實驗一下：
create table km4 (id int primary key,name varchar(1000));
insert into km4 values(1,'gaopeng');
insert into km4 values(2,'gaopeng');
insert into km4 values(3,'gaopeng'); 
insert into km4 values(4,'gaopeng'); 
delete from km4 where id in (1,3);
再次檢視km4
 bcview km4.ibd 16 46 2|more
current block:00000003--Offset:00046--cnt bytes:02--data is:003
這裡3e是十進位制62,74-62=12 剛好6位元組證明了我的說法。
還要注意一點：如果建表的時候沒有加入主鍵，插入資料後加入，這每行6位元組的開銷也是有的，所以建表的時候儘量要加主鍵。
如果沒有刪除的行或者所有刪除的空間從用了這個值減少為0.

6、last insert position 
bcview km1.ibd 16 48 2|more
current block:00000003--Offset:00048--cnt bytes:02--data is:0000

這個取值在沒有刪除重用空間的時候都0，但是隨後的測試中這個值代表的
是最後一次插入的偏移量。如果使用的是刪除的空間，那麼這個值會出現
指向小於當前偏移量的情況，因為刪除的資料的空間在當前行的物理偏移量
以前

7、page direction

bcview km1.ibd 16 50 2|more
current block:00000003--Offset:00050--cnt bytes:02--data is:0002

這裡代表的插入的順序為0x02 右

8、number of inerts in page direction
bcview km1.ibd 16 52 2|more
current block:00000003--Offset:00052--cnt bytes:02--data is:0003

這裡代表以0x02 這個順序插入資料的行數為4-1，因為我插入了4行它為3

9、number of record  
bcview km1.ibd 16 54 2|more
current block:00000003--Offset:00054--cnt bytes:02--data is:0002

可以看到這裡的行數實際為2了因為我刪除了2行，而
number of heap records/format flag
的記錄還是8006,可以看到記錄還是6，除掉infimum 和supremum還有4行。
那麼我們可以得到一個結論
number of heap records 是本block中 delete的行數+未delete的行數+infimum 和supremum=6
而number of record     是本block中 未delete的行數

我們可以做下實驗：
現在表中有2行，我們增加一行資料看看變化
mysql> insert into km1 values(5,'gaopeng');
number of record 
current block:00000003--Offset:00054--cnt bytes:02--data is:0003
number of heap records/format flag
current block:00000003--Offset:00042--cnt bytes:02--data is:8006
可以看到我們的空閒的空間重用了，因為number of heap records還是6，而number of record變成了3

那麼還可以看看page direction和number of inerts in page direction
按照理論既然是重用了空間，那會插入的順序是相反的，那麼這兩個值會有所變化
不出所料
page direction
current block:00000003--Offset:00050--cnt bytes:02--data is:0005
值為5
number of inerts in page direction
current block:00000003--Offset:00052--cnt bytes:02--data is:0000
變為了0，因為順序改變了0X02改變為0X05那麼這個值被重置了

同時我們這個時候來看
last insert position
bcview km1.ibd 16 48 2|more
current block:00000003--Offset:00048--cnt bytes:02--data is:00c9
以前這值是
current block:00000003--Offset:00048--cnt bytes:02--data is:0000
而這個值剛好也是 剛才 刪除資料後first garbage record offset 的值
current block:00000003--Offset:00044--cnt bytes:02--data is:00c9
而此時值first garbage record offset
bcview km1.ibd 16 44 2|more
變為了
current block:00000003--Offset:00044--cnt bytes:02--data is:007f
這裡7f變得更小，這個就是我們id=1的地址，而id=3的地址已經重用了

10、maximum transaction id 
bcview km1.ibd 16 56 8|more
current block:00000003--Offset:00056--cnt bytes:08--data is:0000000000000000

這個值應該代表是所有行中最高的事物ID，但是沒有測試出來


11、page level 
 bcview km1.ibd 16 64 2|more
current block:00000003--Offset:00064--cnt bytes:02--data is:0000

代表是索引的層次，這裡根節點是頁節點是一個節點所以是0

12、index ID 

bcview km1.ibd 16 66 8|more
current block:00000003--Offset:00066--cnt bytes:08--data is:0000000000000255
這是索引的ID這個在INNODB_SYS_INDEXES也是有的。

學習了這些內容，我們有了一些對INDEX PAGE結構的瞭解。
總結一下：
1、heap top position 是塊的高水位，就是索引頁曾經達到的最高點。
2、last insert position 為最後一次索引頁插入的行的偏移量，如果刪除了資料會進行從用，那麼這個值和heap top position並沒有什麼關係。
3、number of record 是塊中未刪除的資料量行數，每次刪除和插入資料一定變化
4、number of heap records/format flag 記錄的是 未刪除行+刪除行+infimum 和supremum的行數 ，未刪除行可以重用，所以這個值可能在你插入資料後不會變化。
5、format flag是第15位的值1為compact格式，0為Redundant
6、如果不加入主鍵，那麼會自動生成一個6位元組48位的ROWID，它會加大你的儲存空間，所以儘可能加入主鍵吧再建表的時候，建表插入資料後加入主鍵也不會改善
7、garbage space 就是刪除資料DELETE後剩餘的空間，隨著不斷的重用這個空間不斷減少，如果沒有delete後可以從用的空間為0
8、index ID 這是索引的ID這個在INNODB_SYS_INDEXES也是有的。