背景:
再一次看完<MySQL 技術(shù)內幕-Innodb存儲引擎> 一書(shū)的的第4章。對前面五節的內容做又有了新的認識，順便做下筆記。先了解下相關(guān)的概念：
表空間：INNODB 所有數據都存在表空間當中（共享表空間），要是開(kāi)啟innodb_file_per_table，則每張表的數據會(huì )存到單獨的一個(gè)表空間內（獨享表空間）。
獨享表空間包括：數據，索引，插入緩存，數據字典。共享表空間包括：Undo信息（不會(huì )回收<物理空間上>），雙寫(xiě)緩存信息，事務(wù)信息等。
段（segment）：組成表空間，有區組成。
區（extent）：有64個(gè)連續的頁(yè)組成。每個(gè)頁(yè)16K，總共1M。對于大的數據段，每次最后可申請4個(gè)區。
頁(yè)（page）：是INNODB 磁盤(pán)管理的單位，有行組成。
行（row）：包括事務(wù)ID，回滾指針，列信息等。
目的1：
了解表空間各個(gè)頁(yè)的信息和溢出行數據存儲的信息。通過(guò)該書(shū)作者蔣承堯編寫(xiě)的工具：http://code.google.com/p/david-mysql-tools/source/browse/trunk/py_innodb_page_type/
3個(gè)腳本：
py_innodb_page_info.py
mylib.py
include.py
測試1：
root@localhost : test 02:26:13>create table tt(id int auto_increment,name varchar(10),age int,address varchar(20),primary key (id))engine=innodb;Query OK, 0 rows affected (0.17 sec)root@zhoujy:/var/lib/mysql/test# ls -lh tt.ibd -rw-rw---- 1 mysql mysql 96K 2012-10-17 14:26 tt.ibd
查看ibd：
root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/tt.ibd -vpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000> ---葉子節點(diǎn)page offset 00000000, page type <Freshly Allocated Page>page offset 00000000, page type <Freshly Allocated Page>Total number of page: 6: Freshly Allocated Page: 2Insert Buffer Bitmap: 1File Space Header: 1B-tree Node: 1File Segment inode: 1
解釋?zhuān)?br>Total number of page: 總頁(yè)數
Freshly Allocated Page:可用頁(yè)
Insert Buffer Bitmap:插入緩存位圖頁(yè)
Insert Buffer Free List:插入緩存空閑列表頁(yè)
B-tree Node:數據頁(yè)
Uncompressed BLOB Page:二進(jìn)制大對象頁(yè)，存放溢出行的頁(yè)，即溢出頁(yè)
上面得到的信息是表初始化大小為96K，他是有 Total number of page * 16 得來(lái)的。1個(gè)數據頁(yè)，2個(gè)可用頁(yè)面。
root@localhost : test 02:42:58>insert into tt values(name,age,address) values('aaa',23,'HZZZ');
疑惑：為什么沒(méi)有申請區？區是64個(gè)連續的頁(yè)，大小1M。那么表大小也應該是至少1M。但是現在只有96K（默認）。原因是因為每個(gè)段開(kāi)始的時(shí)候，先有32個(gè)頁(yè)大小的碎片頁(yè)存放數據，使用
完之后才是64頁(yè)的連續申請，最多每次可以申請4個(gè)區，保證數據的順序。這里看出表大小增加是按照至少64頁(yè)的大小的空間來(lái)增加的，即1M增加。
驗證：
填充數據，寫(xiě)滿(mǎn)這32個(gè)碎片頁(yè)，32*16 = 512K?？纯词欠衲苌暾埓笥?M的空間。
"額外"頁(yè)：4個(gè)
page offset 00000000, page type <File Space Header> :文件頭空間頁(yè)
page offset 00000001, page type <Insert Buffer Bitmap>：插入緩存位圖頁(yè)
page offset 00000002, page type <File Segment inode>：文件段節點(diǎn)
page offset 00000003, page type <B-tree Node>, page level <0001>：根頁(yè)
碎片頁(yè)：32個(gè)
page type <B-tree Node>, page level <0000>
總共36個(gè)頁(yè)，ibd大小 576K的由來(lái)：32*16=512K（碎片頁(yè)）+ 4*16=64（額外頁(yè)），這里開(kāi)始要是再插入的話(huà)，應該申請最少1M的頁(yè)：
root@zhoujy:/home/zhoujy/jiaoben/read_ibd# ls -lh /var/lib/mysql/test/tt.ibd -rw-rw---- 1 mysql mysql 2.0M 2012-10-17 16:10 /var/lib/mysql/test/tt.ibdroot@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/tt.ibdTotal number of page: 128:Freshly Allocated Page: 91Insert Buffer Bitmap: 1File Space Header: 1B-tree Node: 34File Segment inode: 1
頁(yè)從36跳到了128，因為已經(jīng)用完了32個(gè)碎片頁(yè)，新的頁(yè)會(huì )采用區的方式進(jìn)行空間申請。信息中看到有很多可用頁(yè)，正好說(shuō)明這點(diǎn)。
▲溢出行數據存放：INNODB存儲引擎是索引組織的，即每頁(yè)中至少有兩行記錄，因此如果頁(yè)中只能存放一行記錄，INNODB會(huì )自動(dòng)將行數據放到溢出頁(yè)中。當發(fā)生溢出行的時(shí)候，實(shí)際數據保存在BLOB頁(yè)中，數據頁(yè)只保存數據的前768字節（老的文件格式），新的文件格式（Barracuda）采用完全行溢出的方式，數據頁(yè)只保存20個(gè)字節的指針，BLOB也保存所有數據。如何查看表中有溢出行數據呢？
root@localhost : test 04:52:34>create table t1 (id int,name varchar(10),memo varchar(8000))engine =innodb default charset utf8;Query OK, 0 rows affected (0.16 sec)root@localhost : test 04:53:10>insert into t1 values(1,'zjy',repeat('我',8000));Query OK, 1 row affected (0.00 sec)
查看ibd：
root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/t1.ibd -vpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000005, page type <Uncompressed BLOB Page>Total number of page: 6:Insert Buffer Bitmap: 1Uncompressed BLOB Page: 2File Space Header: 1B-tree Node: 1File Segment inode: 1
從信息中看到，剛才插入的一行記錄，已經(jīng)溢出了，保存到了2個(gè)BLOB頁(yè)中（<Uncompressed BLOB Page>）。因為1頁(yè)只有16K，又要存2行數據，所以每行記錄最好小于8K，而上面的遠遠大于8K，所以被溢出了。當然這個(gè)也不是包括特大字段，要是一張表里面有5個(gè)字段都是varchar(512)【多個(gè)varchar的總和大于8K就可以】，也會(huì )溢出：
root@localhost : test 05:08:39>create table t2 (id int,name varchar(1000),address varchar(512),company varchar(200),xx varchar(512),memo varchar(512),dem varchar(1000))engine =innodb default charset utf8;Query OK, 0 rows affected (0.17 sec)root@localhost : test 05:08:43>insert into t2 values(1,repeat('周',1000),repeat('我',500),repeat('丁',500),repeat('啊',500),repeat('噢',500),repeat('阿a',500));
1000+500+500+500+500+500=3500*3>8000字節；行會(huì )被溢出：
root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/t2.ibd -vpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000000, page type <Freshly Allocated Page>Total number of page: 6:Insert Buffer Bitmap: 1Freshly Allocated Page: 1File Segment inode: 1B-tree Node: 1File Space Header: 1Uncompressed BLOB Page: 1
<Uncompressed BLOB Page> 頁(yè)存放真正的數據，那數據頁(yè)到底存放什么？用hexdump查看：
root@zhoujy:/home/zhoujy/jiaoben/read_ibd# hexdump -C -v /var/lib/mysql/test/t1.ibd > t1.txt
查看ibd：
文本中剛好是48行，每行16字節。48*16=768字節，剛好驗證了之前說(shuō)的：數據頁(yè)只保存數據的前768字節（老的文件格式）。
總結1:
通過(guò)上面的信息，可以能清楚的知道ibd表空間各個(gè)頁(yè)的分布和利用信息以及表空間大小增加的步長(cháng)；特別注意的是溢出行，一個(gè)頁(yè)中至少包含2行數據，如果頁(yè)中存放的行數越多，性能就越好。
************************************
************************************
目的2：
了解表空間如何存儲數據，以及對NULL值的存儲。
測試2：
在測試前先了解INNODB的存儲格式（row_format）。老格式（Antelope）：Compact<默認>，Redumdant；新格式（Barracuda）：Compressed ，Dynamic。
這里測試指針對默認的存儲格式。
Compact行記錄方式如下:
|變長(cháng)字段長(cháng)度列表（1~2字節）|NULL標志位（1字節）|記錄頭信息（5字節）|RowID（6字節）|事務(wù)ID（6字節）|回滾指針（7字節）|上面信息除了 "NULL標志位"[表中所有字段都定義為NOT NULL]，"RowID"[表中有主鍵] ，"變長(cháng)字段長(cháng)度列表" [沒(méi)有變長(cháng)字段] 可能不存在外，其他信息都會(huì )出現。所以一行數據除了列數據所占用的字段外，還需要額外18字節。
一:字段全NULL
mysql> create table mytest(t1 varchar(10),t2 varchar(10),t3 varchar(10) ,t4 varchar(10))engine=innodb charset = latin1 row_format=compact;Query OK, 0 rows affected (0.08 sec)mysql> insert into mytest values('a','bb','bb','ccc');Query OK, 1 row affected (0.02 sec)mysql> insert into mytest values('a','ee','ee','fff');Query OK, 1 row affected (0.01 sec)mysql> insert into mytest values('a',NULL,NULL,'fff');Query OK, 1 row affected (0.00 sec)
測試數據準備完之后，執行shell命令：
root@zhoujy:/usr/local/mysql/test# hexdump -C -v mytest.ibd > /home/zhoujy/mytest.txt
打開(kāi)mytest.txt文件找到supremum這一行：
0000c070 73 75 70 72 65 6d 75 6d 03 02 02 01 00 00 00 10 |supremum........| ----------->一行，16字節0000c080 00 25 00 00 00 03 b9 00 00 00 00 02 49 01 82 00 |.%..........I...|0000c090 00 01 4a 01 10 61 62 62 62 62 63 63 63 03 02 02 |..J..abbbbccc...|0000c0a0 01 00 00 00 18 00 23 00 00 00 03 b9 01 00 00 00 |......#.........|0000c0b0 02 49 02 83 00 00 01 4b 01 10 61 65 65 65 65 66 |.I.....K..aeeeef|0000c0c0 66 66 03 01 06 00 00 20 ff a6 00 00 00 03 b9 02 |ff..... ........|0000c0d0 00 00 00 02 49 03 84 00 00 01 4c 01 10 61 66 66 |....I.....L..aff|0000c0e0 66 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |f...............|
解釋?zhuān)?br>第一行數據:
03 02 02 01 /*變長(cháng)字段*/ ---- 表中4個(gè)字段類(lèi)型為varchar，并且沒(méi)有NULL數據，而且每個(gè)字段君小于255。
00 /*NULL標志位，第一行沒(méi)有null的數據*/
00 00 10 00 25 /*記錄頭信息,固定5個(gè)字節*/
00 00 00 03 b9 00 /*RowID,固定6個(gè)字節,表沒(méi)有主鍵*/
00 00 00 02 49 01 /*事務(wù)ID,固定6個(gè)字節*/
82 00 00 01 4a 01 10 /*回滾指針,固定7個(gè)字節*/
61 62 62 62 62 63 63 63 /*列的數據*/
第二行數據和第一行數據一樣（顏色匹配）。
第三行數據（有NULL值）和第一行的解釋的顏色對應起來(lái)比較差別:
03 02 02 01 VS 03 01 ----------當值為NULL時(shí)，變長(cháng)字段列表不會(huì )占用存儲空間。
61 62 62 62 62 63 63 63 VS 61 66 66 66 --------- NULL值沒(méi)有存儲，不占空間
結論：當值為NULL時(shí)，變長(cháng)字段列表不會(huì )占用存儲空間。NULL值沒(méi)有存儲，不占空間，但是需要一個(gè)標志位（一行一個(gè)）。
二:字段全NOT NULL
mysql> create table mytest(t1 varchar(10) NOT NULL,t2 varchar(10) NOT NULL,t3 varchar(10) NOT NULL,t4 varchar(10) NOT NULL)engine=innodb charset = latin1 row_format=compact;Query OK, 0 rows affected (0.03 sec)mysql> insert into mytest values('a','bb','bb','ccc');Query OK, 1 row affected (0.01 sec)mysql> insert into mytest values('a','ee','ee','fff');Query OK, 1 row affected (0.01 sec)mysql> insert into mytest values('a',NULL,NULL,'fff');ERROR 1048 (23000): Column 't2' cannot be null
步驟和上面一樣，得到的ibd的結果是：
0000c070 73 75 70 72 65 6d 75 6d 03 02 02 01 00 00 10 00 |supremum........|0000c080 24 00 00 00 03 b9 03 00 00 00 02 49 07 87 00 00 |$..........I....|0000c090 01 4f 01 10 61 62 62 62 62 63 63 63 03 02 02 01 |.O..abbbbccc....|0000c0a0 00 00 18 ff cb 00 00 00 03 b9 04 00 00 00 02 49 |...............I|0000c0b0 08 88 00 00 01 50 01 10 61 65 65 65 65 66 66 66 |.....P..aeeeefff|
和上面比較,發(fā)現少了NULL的標志位信息。
結論: NULL值會(huì )有額外的空間來(lái)存儲，即每行1字節的大小。對于相同數據的表，字段中有NULL值的表比NOT NULL的大。
三:1個(gè)NULL，和1個(gè)''的數據:
mysql> create table mytest(t1 varchar(10) NOT NULL,t2 varchar(10) NOT NULL DEFAULT '',t3 varchar(10) NOT NULL ,t4 varchar(10))engine=innodb charset = latin1 row_format=compact;Query OK, 0 rows affected (0.02 sec)mysql> insert into mytest(t1,t2) values('A','BB');Query OK, 1 row affected, 1 warning (0.01 sec)
步驟和上面一樣，得到的ibd的結果是：
0000c070 73 75 70 72 65 6d 75 6d 00 02 01 01 00 00 10 ff |supremum........|0000c080 ef 00 00 00 43 b9 03 00 00 00 02 4a 15 90 00 00 |....C......J....|0000c090 01 c2 01 10 41 42 42 00 00 00 00 00 00 00 00 00 |....ABB.........|
和上面2個(gè)區別主要在于變長(cháng)列表和列數據這里。
結論：列數據信息里表明了 NULL數據和''數據都不占用任何空間，對于變長(cháng)字段列表的信息，和一對比得出：‘’數據雖然不需要占用任何存儲空間，但是在變長(cháng)字段列表里面還是需要占用一個(gè)字節<畢竟還是一個(gè)‘’值>，NULL值不需要占用”，只是NULL會(huì )有額外的一個(gè)標志位，所以能有個(gè)優(yōu)化的說(shuō)法：“數據庫表中能設置NOT NULL的就盡量設置為NOT NULL，除非確實(shí)需要NULL值得?！?在此得到了證明。
上面的測試都是針對VARCHAR的變長(cháng)類(lèi)型，那對于CHAR呢？
CHAR 測試：
root@localhost : test 10:33:35>create table mytest(t1 char(10),t2 char(10),t3 char(10) ,t4 char(10))engine=innodb charset = latin1 row_format=compact;Query OK, 0 rows affected (0.16 sec)root@localhost : test 10:33:59>insert into mytest values('a','bb','bb','ccc');Query OK, 1 row affected (0.00 sec)root@localhost : test 10:34:09>insert into mytest values('a','ee','ee','fff');Query OK, 1 row affected (0.00 sec)root@localhost : test 10:34:19>insert into mytest values('a',NULL,NULL,'fff');Query OK, 1 row affected (0.00 sec)
打開(kāi)ibd生成的文件：
0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|0000c070 73 75 70 72 65 6d 75 6d 00 00 00 10 00 41 00 00 |supremum.....A..|0000c080 00 0a f5 00 00 00 00 81 2d 07 80 00 00 00 32 01 |........-.....2.|0000c090 10 61 20 20 20 20 20 20 20 20 20 62 62 20 20 20 |.a bb |0000c0a0 20 20 20 20 20 62 62 20 20 20 20 20 20 20 20 63 | bb c|0000c0b0 63 63 20 20 20 20 20 20 20 00 00 00 18 00 41 00 |cc .....A.|0000c0c0 00 00 0a f5 01 00 00 00 81 2d 08 80 00 00 00 32 |.........-.....2|0000c0d0 01 10 61 20 20 20 20 20 20 20 20 20 65 65 20 20 |..a ee |0000c0e0 20 20 20 20 20 20 65 65 20 20 20 20 20 20 20 20 | ee |0000c0f0 66 66 66 20 20 20 20 20 20 20 06 00 00 20 ff 70 |fff ... .p|0000c100 00 00 00 0a f5 02 00 00 00 81 2d 09 80 00 00 00 |..........-.....|0000c110 32 01 10 61 20 20 20 20 20 20 20 20 20 66 66 66 |2..a fff|0000c120 20 20 20 20 20 20 20 00 00 00 00 00 00 00 00 00 | .........|
和一的varchar比較發(fā)現：少了變長(cháng)字段列表，但是對于char來(lái)講，需要固定長(cháng)度來(lái)存儲的，存不到固定長(cháng)度，也會(huì )被填充滿(mǎn)。如：20；并且NULL值也不需要占用存儲空間。
混合（varchar,char）:
root@localhost : test 11:21:48>create table mytest(t1 int,t2 char(10),t3 varchar(10) ,t4 char(10))engine=innodb charset = latin1 row_format=compact;Query OK, 0 rows affected (0.17 sec)root@localhost : test 11:21:50>insert into mytest values(1,'a','b','c');Query OK, 1 row affected (0.00 sec)root@localhost : test 11:22:06>insert into mytest values(11,'aa','bb','cc');Query OK, 1 row affected (0.00 sec)
從上面的表結構中看出：
1，變長(cháng)字段列表長(cháng)度：1
2，NULL標志位：1
3，記錄頭信息：5
4，RowID：6
5，事務(wù)ID：6
6，回滾指針：7
idb的信息：
0000c070 73 75 70 72 65 6d 75 6d 01 00 00 00 10 00 33 00 |supremum......3.| 0000c080 00 00 0a f5 07 00 00 00 81 2d 1a 80 00 00 00 32 |.........-.....2|0000c090 01 10 80 00 00 01 61 20 20 20 20 20 20 20 20 20 |......a |0000c0a0 62 63 20 20 20 20 20 20 20 20 20 02 00 00 00 18 |bc .....|0000c0b0 ff be 00 00 00 0a f5 08 00 00 00 81 2d 1b 80 00 |............-...|0000c0c0 00 00 32 01 10 80 00 00 0b 61 61 20 20 20 20 20 |..2......aa |0000c0d0 20 20 20 62 62 63 63 20 20 20 20 20 20 20 20 00 | bbcc .|
從上信息得出和之前預料的一樣：因為表中只有一個(gè)varchar字段，所以，變長(cháng)列表長(cháng)度就只有：01
特別注意的是：各個(gè)列數據存儲的信息：t1字段為int 類(lèi)型，占用4個(gè)字節的大小。第一行：80 00 00 01 就是表示 1 數字；第二行：80 00 00 0b 表示了11的數字。[select hex(11) == B ]，其他的和上面的例子一樣。
上面都是latin1單字節字符集的說(shuō)明，那對于多字節字符集的情況怎么樣？
root@localhost : test 11:52:10>create table mytest(id int auto_increment,t2 varchar(10),t3 varchar(10) ,t4 char(10),primary key(id))engine=innodb charset = utf8 row_format=compact;Query OK, 0 rows affected (0.17 sec)root@localhost : test 11:52:11>insert into mytest(t2,t3,t4) values('bb','bb','ccc');Query OK, 1 row affected (0.00 sec)root@localhost : test 11:55:34>insert into mytest(t2,t3,t4) values('我們','他們','我們的');Query OK, 1 row affected (0.00 sec)
ibd信息如下：
0000c070 73 75 70 72 65 6d 75 6d 0a 02 02 00 00 00 10 00 |supremum........|0000c080 28 80 00 00 01 00 00 00 81 2d 27 80 00 00 00 32 |(........-'....2|0000c090 01 10 62 62 62 62 63 63 63 20 20 20 20 20 20 20 |..bbbbccc |0000c0a0 0a 06 06 00 00 00 18 ff c7 80 00 00 02 00 00 00 |................|0000c0b0 81 2d 28 80 00 00 00 32 01 10 e6 88 91 e4 bb ac |.-(....2........|0000c0c0 e4 bb 96 e4 bb ac e6 88 91 e4 bb ac e7 9a 84 20 |............... |
因為表有了主鍵，所以ROWID（6字節）不見(jiàn)了。
特別注意的是：變長(cháng)字段列表是3？表里面的varchar類(lèi)型的列只有2個(gè)啊。經(jīng)測試得出：在多字節字符集的條件下，char類(lèi)型被當成可變長(cháng)度的類(lèi)型來(lái)處理，他們的行存儲基本沒(méi)有區別，所以這個(gè)就出現變長(cháng)列表是3了，因為是utf8字符集，占用三個(gè)字節。所以一個(gè)漢字均占用了一個(gè)頁(yè)中3個(gè)字節的空間(”我們“ ：e6 88 91 e4 bb ac)。
數據列的信息：
id列的1值，應該是 80 00 00 01，為什么這個(gè)顯示00 32 01 10，而且所有的id都是00 32 01 10。測試發(fā)現，id為自增主鍵的時(shí)候，id的4個(gè)字節長(cháng)度都是以00 32 01 10 表示。否則和前面一個(gè)例子里說(shuō)的，用select HEX（X） 表示。
總結2:
上面的測試都是基于COMPACT存儲格式的，不管是varchar還是char，NULL值是不需要占用存儲空間的；特別需要注意的是Redumdant的記錄頭信息需要6個(gè)固定字節，而NULL值對于varchar來(lái)說(shuō)是不需要占用存儲空間，對于char來(lái)說(shuō)將會(huì )占用最大值的字節數；在多字節字符集的條件下，CHAR和VARCHAR的行存儲基本是沒(méi)有區別的。
聯(lián)系客服
