關于MYSQL INNODB index page header學習和實驗總結
關于INNODB index header
所用到的工具是自己寫的
mysqlblock和bcview，
我放到了百度云盤
http://pan.baidu.com/s/1num76RJ
供大家下載和使用
普通表空間(及設置了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也是有的。
