InnoDB行格式（compact，redundant）對比，innodbredundant

InnoDB行格式（compact，redundant）對比，innodbredundant
InnoDB行格式分兩種格式（COMPACT，redundant）預設為COMPACT compact的儲存格式為 首部為一個非NULL的變長欄位長度列表，而且是按照列的順序逆序放置的，當列的長度小於255位元組，用1位元組表示，若大於255個位元組。用2個位元組表 示，varchar的最大長度為65535>，因為兩個位元組為16位，即65535，第二部分是NULL標誌位，該位指示了該行是否有NULL值， 有用01表示，無則用00表示。接下去的部分是為記錄頭資訊（record header）固定佔用5個位元組，最後的部分就是實際儲存的每個列的資料，NULL不佔該部分任何資料，除了佔有NULL標誌位，實際儲存不佔任何空間， 另外注意,每行資料除了使用者定義的列外，還有兩個隱 藏列，事務ID（6位元組），會滾指標列（7位元組），若INNODB表沒有定義,Primay key,那麼每行會增加一個6位元組的rowid，如果有，怎有4個位元組的索引欄位。


redundant的儲存格式為 首部是一個欄位長度位移列表（每個欄位佔用的位元組長度及其相應的位移），同樣是按照列的順序逆序放置，當列的長度小於255位元組，用1位元組表示，若大於 255個位元組，用2位元組表>示。第二個部分為記錄頭資訊（record header），不同與compact行格式，它的行格式固定佔用6個位元組，最後的部分就是實際儲存的每個列的資料，NULL不佔該部分任何資料，但是 char中如果有NULL值則需要佔用相應的位元組，另外注意,每行資料除了使用者定義的列外，還有兩個隱藏列，事務ID（6位元組），會滾指標列（7位元組）， 若INNODB表沒有定義,Primay key,那麼每行會增加一個6位元組的rowid，如果有，怎有4個位元組的索引欄位


問：如何理解長度位移列表? 答：長度位移列表表示每個目錄的長度，及其相對的位置。


現在我們來做個實驗來具體看下他們的區別


CREATE TABLE test1 (
t1 varchar(10) DEFAULT NULL,
t2 varchar(10) DEFAULT NULL,
t3 char(10) DEFAULT NULL,
t4 varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
insert into test1 values('a','bb','bb','ccc');
insert into test1 values('d','ee','ee','fff');
insert into test1 values('d',NULL,NULL,'fff');
通過python py_innodb_page_info.py -v /vobiledata/mysqldata/test/test1.ibd分析可知資料頁存在00000003頁上，一個頁有16K，第三頁通過十六進位轉化為0000C000開始。
page 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 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
通過hexdump -C -v /vobiledata/mysqldata/test/test1.ibd>tes1.txt分析可知
0000bff0 00 00 00 00 00 00 00 00 52 4b 47 ff 63 5e 66 c7 |........RKG.cf.|
0000c000 30 4e 95 ae 00 00 00 03 ff ff ff ff ff ff ff ff |0N..............| 0000c010 00 00 00 50 63 5f 15 76 45 bf 00 00 00 00 00 00 |...Pc_.vE.......|
0000c020 00 00 00 00 15 9c 00 02 00 ef 80 05 00 00 00 00 |................|
0000c030 00 d8 00 02 00 02 00 03 00 00 00 00 00 00 00 00 |................|
0000c040 00 00 00 00 00 00 00 00 36 01 00 00 15 9c 00 00 |........6.......|
0000c050 00 02 00 f2 00 00 15 9c 00 00 00 02 00 32 01 00 |.............2..|
0000c060 02 00 1e 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00 |supremum........|
0000c080 2c 00 00 0c 84 58 03 00 00 00 62 81 97 80 00 00 |,....X....b.....|
0000c090 00 2d 01 10 61 62 62 62 62 20 20 20 20 20 20 20 |.-..abbbb |
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 0c | ccc........+...|
0000c0b0 84 58 04 00 00 00 62 81 f8 80 00 00 00 2d 01 10 |.X....b......-..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 0c 84 58 05 00 00 |..... ......X...|
0000c0e0 00 62 82 43 80 00 00 00 2d 01 10 64 66 66 66 00 |.b.C....-..dfff.|
0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c110 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|


我們可以分析上面的資料來看INNODB是怎麼存資料的。
儲存資料頁，在0000c078開始存資料，
03,02,01表示可變欄位儲存列表(記錄可變欄位的最長位元組)，
00表示記錄中沒有NUll（如有怎按照null的位置用二進位計算），
00 00 10 00 2c表示記錄頭資訊（規定為5個位元組長度），這個頭是用來串連聯絡的記錄，也用InnoDB預設使用緊湊（COMPACT）格式
儲存資料頁，在0000c078開始存資料， 03,02,01表示可變欄位儲存列表(記錄可變欄位的最長位元組)，
00表示記錄中沒有NUll（如有怎按照null的位置用二進位計算），
00 00 10 00 2c表示記錄頭資訊（規定為5個位元組長度），這個頭是用來串連聯絡的記錄，也用於行級鎖。
00 00 0c 84 58 03表示rowid，我們沒有設定主鍵，所以（隱藏的六個位元組），由此可見，為了減少資料表空間，我們在設計表是盡量要設定主鍵。
00 00 00 62 81 97 六個transation ID
80 00 00 00 2d 01 10 七個位元組的復原指標

PS：null不佔用空間

我們觀察下第三列存的資料：03，01表示第四列和第一列不為空白，06表示存在null值，為止在於第二位和第三位，00000110 = 06


接下來我們看下資料被刪除後空間的變化情況：
lin_ren@test 12:19:09>select * from test1;
t1 t2 t3t4
a bb bbccc
d ee eefff
d NULL NULL fff
3 rows in set (0.00 sec)


xue_binbin@test 12:27:40>delete from test1 where t1 = 'a';
Query OK, 1 row affected (0.00 sec) 
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 20 00 10 00 |supremum.... ...|
0000c080 00 00 00 0c 84 58 00 00 00 00 63 60 0c 00 00 00 |.....X....c`....|
0000c090 00 33 26 06 61 62 62 62 62 20 20 20 20 20 20 20 |.3&.abbbb |
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 0c | ccc........+...|
0000c0b0 84 58 01 00 00 00 62 7c 94 80 00 00 00 2d 01 1f |.X....b|.....-..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 0c 84 58 02 00 00 |..... ......X...|
0000c0e0 00 62 7c 94 80 00 00 00 2d 01 2e 64 66 66 66 00 |.b|.....-..dfff.|
0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
我們探索資料儲存沒有變化
xue_binbin@test 12:35:01>insert into test1 values('a','bb','bb','ccc');
Query OK, 1 row affected (0.00 sec)
xue_binbin@test 12:35:08>select * from test1;
t1 t2 t3t4
d ee eefff
d NULL NULL fff
a bb bbccc
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 ff |supremum........|
0000c080 ef 00 00 0c 84 58 0c 00 00 00 63 63 8f 80 00 00 |.....X....cc....|
0000c090 00 2d 01 10 61 62 62 62 62 20 20 20 20 20 20 20 |.-..abbbb |
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 0c | ccc........+...|
0000c0b0 84 58 01 00 00 00 62 7c 94 80 00 00 00 2d 01 1f |.X....b|.....-..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff|
0000c0d0 03 01 06 00 00 20 ff a9 00 00 0c 84 58 02 00 00 |..... ......X...|
0000c0e0 00 62 7c 94 80 00 00 00 2d 01 2e 64 66 66 66 00 |.b|.....-..dfff.|
由此表明資料空間刪除的時候不釋放，在等著下一個插入資料時刻，如果插入相同的資料就可以重複利用，如果沒有，就有片段
xue_binbin@test 12:35:13>insert into test1 values('c','aaa','aaa','cc');
Query OK, 1 row affected (0.00 sec)
xue_binbin@test 12:37:24>select * from test1;
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00 |supremum........|
0000c080 77 00 00 0c 84 58 0c 00 00 00 63 63 8f 80 00 00 |w....X....cc....|
0000c090 00 2d 01 10 61 62 62 62 62 20 20 20 20 20 20 20 |.-..abbbb |
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 0c | ccc........+...|
0000c0b0 84 58 01 00 00 00 62 7c 94 80 00 00 00 2d 01 1f |.X....b|.....-..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff|
0000c0d0 03 01 06 00 00 20 ff a9 00 00 0c 84 58 02 00 00 |..... ......X...|
0000c0e0 00 62 7c 94 80 00 00 00 2d 01 2e 64 66 66 66 02 |.b|.....-..dfff.|
0000c0f0 03 01 00 00 00 28 ff 78 00 00 0c 84 58 0d 00 00 |.....(.x....X...|
0000c100 00 63 64 b5 80 00 00 00 2d 01 10 63 61 61 61 61 |.cd.....-..caaaa|
0000c110 61 61 20 20 20 20 20 20 20 63 63 00 00 00 00 00 |aa cc.....|
由此可見，如果插入一條新的資料，則會接著往下空間存
接著我們來看下有主鍵的情況的INNODB儲存情況：
CREATE TABLE te1 (
id int(11) NOT NULL DEFAULT '0',
t1 varchar(10) DEFAULT NULL,
t2 varchar(10) DEFAULT NULL,
t3 char(6) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
insert into te1 values(1,'aa','bb','bb');
insert into te1 values(2,'cc',NULL，NULL)；
同樣的分析資料： 0000c070 73 75 70 72 65 6d 75 6d 02 02 00 00 00 10 00 26 |supremum.......&|
0000c080 80 00 00 01 00 00 00 62 98 e7 80 00 00 00 2d 01 |.......b......-.|
0000c090 10 61 61 62 62 62 62 20 20 20 20 20 20 20 20 02 |.aabbbb .|
0000c0a0 06 00 00 18 ff ca 80 00 00 02 00 00 00 62 99 2e |.............b..|
0000c0b0 80 00 00 00 2d 01 10 63 63 00 00 00 00 00 00 00 |....-..cc.......|
0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
我們發現如果有主鍵的話，在標頭檔（00 00 10 00 26）會有記錄索引的資訊（所有的記錄行80 00 00 01）01表示主鍵1，將比沒有索引的少存一個位元組
xue_binbin@test 12:44:05>update te1 set t1 ='cc' where id = 1;
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
0000c070 73 75 70 72 65 6d 75 6d 02 02 00 00 00 10 00 22 |supremum......."|
0000c080 80 00 00 01 00 00 00 63 68 c3 00 00 00 00 33 0f |.......ch.....3.|
0000c090 a7 63 63 62 62 62 62 20 20 20 20 02 06 00 00 18 |.ccbbbb .....|
0000c0a0 ff ce 80 00 00 02 00 00 00 63 31 07 80 00 00 00 |.........c1.....|
0000c0b0 2d 01 1d 63 63 00 00 00 00 00 00 00 00 00 00 00 |-..cc...........|
由此可見，update操作是在原有的資料位元置上做更改操作，不會產生片段
冗餘（redundant）
xue_binbin@test 11:00:55>create table test3 engine = innodb row_format = redundant as select * from test1; 
xue_binbin@test 07:40:21>select * from test3;
t1 t2 t3t4
a bb bbccc
d ee eefff
d NULL NULL fff
同樣分析資料：
0000c070 08 03 00 00 73 75 70 72 65 6d 75 6d 00 23 20 16 |....supremum.# .|
0000c080 14 13 0c 06 00 00 10 0f 00 ba 00 00 0c 84 58 06 |..............X.|
0000c090 00 00 00 63 40 0a 80 00 00 00 2d 01 10 61 62 62 |...c@.....-..abb|
0000c0a0 62 62 20 20 20 20 20 20 20 20 63 63 63 23 20 16 |bb ccc# .|
0000c0b0 14 13 0c 06 00 00 18 0f 00 ea 00 00 0c 84 58 07 |..............X.|
0000c0c0 00 00 00 63 40 0a 80 00 00 00 2d 01 1f 64 65 65 |...c@.....-..dee|
0000c0d0 65 65 20 20 20 20 20 20 20 20 66 66 66 21 9e 94 |ee fff!..|
0000c0e0 14 13 0c 06 00 00 20 0f 00 74 00 00 0c 84 58 08 |...... ..t....X.|
0000c0f0 00 00 00 63 40 0a 80 00 00 00 2d 01 2e 64 00 00 |...c@.....-..d..|
0000c100 00 00 00 00 00 00 00 00 66 66 66 00 00 00 00 00 |........fff.....|
0000c110 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
test3表中第一行記錄為（a，bb，bb，ccc）他們的長度分別為1，2，10，3
外加上還有隱藏的三個欄位（rowid（長度為6）,事務ID（6），復原ID（7））
所以長度位移列表有7個，分別為（06）6，（0c）12，（13）19，（14）20，（16）22，（20）32，（23）35
資料表空間資料記錄為反序，則為23，20，16，14，13，0c，06
23 20 16 14 13 0c 06 長度位移列表
00 00 10 0f 00 ba 標頭檔ID
00 00 0c 84 58 06 rowid
00 00 00 63 40 0a transactionID
80 00 00 00 2d 01 10 復原ID
至於主鍵的話，同樣的把rowid換成了4個位元組的主鍵資訊
對於插入的資料的順序，底層是怎麼插入的？


實驗： create table t3(id int not null,t1 varchar(10) CHARACTER SET latin1 DEFAULT NULL,t2 varchar(10) CHARACTER SET latin1 DEFAULT NULL,t3 char(6) CHARACTER SET latin1 DEFAULT NULL,PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=compact;
lin_ren@test 05:52:24>insert into t3 values(1,'aa','bb','cc');
Query OK, 1 row affected (0.00 sec)
lin_ren@test 05:53:39>insert into t3 values(3,'aaa','bbb','ccc');
Query OK, 1 row affected (0.00 sec)
lin_ren@test 05:54:50>insert into t3 values(2,'aa','bbbb','ccc');
Query OK, 1 row affected (0.00 sec)
0000c070 73 75 70 72 65 6d 75 6d 02 02 00 00 00 10 00 48 |supremum.......H|
0000c080 80 00 00 01 00 00 00 82 46 58 80 00 00 00 32 01 |........FX....2.|
0000c090 10 61 61 62 62 63 63 20 20 20 20 03 03 00 00 00 |.aabbcc .....|
0000c0a0 18 ff cd 80 00 00 03 00 00 00 82 46 65 80 00 00 |...........Fe...|
0000c0b0 00 32 01 10 61 61 61 62 62 62 63 63 63 20 20 20 |.2..aaabbbccc |
0000c0c0 04 02 00 00 00 20 ff db 80 00 00 02 00 00 00 82 |..... ..........|
0000c0d0 46 8a 80 00 00 00 
32 01 10 61 61 62 62 62 62 63 |F.....2..aabbbbc|
0000c0e0 63 63 20 20 20 00 00 00 00 00 00 00 00 00 00 00 |cc ...........|
0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
CREATE TABLE t1 (
id int(11) NOT NULL DEFAULT '0',
t1 varchar(10) CHARACTER SET latin1 DEFAULT NULL,
t2 varchar(10) CHARACTER SET latin1 DEFAULT NULL,
t3 char(6) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT |
lin_ren@test 05:57:17>insert into t1 values(1,'aa','bb','cc');
Query OK, 1 row affected (0.00 sec)
lin_ren@test 06:03:43>insert into t1 values(3,'a','b','c');
Query OK, 1 row affected (0.00 sec)
lin_ren@test 06:04:01>insert into t1 values(2,'aa','ab','cc');
Query OK, 1 row affected (0.00 sec)
0000c070 08 03 00 00 73 75 70 72 65 6d 75 6d 00 1b 15 13 |....supremum....|
0000c080 11 0a 04 00 00 10 0d 00 d5 80 00 00 01 00 00 00 |................|
0000c090 82 46 d3 80 00 00 00 32 01 10 61 61 62 62 63 63 |.F.....2..aabbcc|
0000c0a0 20 20 20 20 19 13 12 11 0a 04 00 00 18 0d 00 74 | ...........t|
0000c0b0 80 00 00 03 00 00 00 82 46 d5 80 00 00 00 32 01 |........F.....2.|
0000c0c0 10 61 62 63 20 20 20 20 20 1b 15 13 11 0a 04 00 |.abc .......|
0000c0d0 00 20 0d 00 b0 80 00 00 02 00 00 00 82 46 d6 80 |. ...........F..|
0000c0e0 00 00 00 32 01 10 61 61 61 62 63 63 20 20 20 20 |...2..aaabcc [BR]]


由此可見，不管是compact，還是redundant ,用ID做主鍵，插入的順序按照你的排列順序，而不是按照id的順序排列 
optimize table t3;
lin_ren@test 10:16:09>optimize table t3;


Table Op Msg_type Msg_text
test.t3 optimizenote Table does not support optimize, doing recreate + analyze instead
test.t3 optimizestatus OK
整理後資料排序
0000c070 73 75 70 72 65 6d 75 6d 02 02 00 00 00 10 00 23 |supremum.......#|
0000c080 80 00 00 01 00 00 00 82 5f ff 80 00 00 00 32 01 |........_.....2.|
0000c090 10 61 61 62 62 63 63 20 20 20 20 04 02 00 00 00 |.aabbcc .....|
0000c0a0 18 00 25 80 00 00 02 00 00 00 82 5f ff 80 00 00 |..%........_....|
0000c0b0 00 32 01 1d 61 61 62 62 62 62 63 63 63 20 20 20 |.2..aabbbbccc |
0000c0c0 03 03 00 00 00 20 ff a8 80 00 00 03 00 00 00 82 |..... ..........|
0000c0d0 5f ff 80 00 00 00 32 01 2a 61 61 61 62 62 62 63 |_.....2.*aaabbbc|
0000c0e0 63 63 20 20 20 00 00 00 00 00 00 00 00 00 00 00 |cc ...........|
刪除資料：
optimize table t3;
0000c000 6e 94 df 09 00 00 00 03 ff ff ff ff ff ff ff ff |n...............|
0000c010 00 00 00 5a 29 eb 95 68 45 bf 00 00 00 00 00 00 |...Z)..hE.......|
0000c020 00 00 00 00 16 c9 00 02 00 c0 80 04 00 00 00 00 |................|
0000c030 00 a3 00 02 00 01 00 02 00 00 00 00 00 00 00 00 |................|
0000c040 00 00 00 00 00 00 00 00 38 2f 00 00 16 c9 00 00 |........8/......|
0000c050 00 02 00 f2 00 00 16 c9 00 00 00 02 00 32 01 00 |.............2..|
0000c060 02 00 1d 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 02 02 00 00 00 10 00 23 |supremum.......#|
0000c080 80 00 00 01 00 00 00 82 60 50 80 00 00 00 32 01 |........`P....2.|
0000c090 10 61 61 62 62 63 63 20 20 20 20 04 02 00 00 00 |.aabbcc .....|
0000c0a0 18 ff cd 80 00 00 02 00 00 00 82 60 50 80 00 00 |...........`P...|
0000c0b0 00 32 01 1d 61 61 62 62 62 62 63 63 63 20 20 20 |.2..aabbbbccc |
0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
由此可見，如果刪除大量的資料，我們要執行optimize table t3操作來釋放空間。
以上就是InnoDB行格式分兩種格式（COMPACT，redundant）的區別，外加Insert，delete，update的本質區別。 

mysql 欄格式 compact ，compressed， default， fixed， redundant 的差別或者相關介紹文章

Mysql的row_format

在mysql中， 若一張表裡面不存在varchar、text以及其變形、blob以及其變形的欄位的話，那麼張這個表其實也叫靜態表，即該表的row_format是fixed，就是說每條記錄所佔用的位元組一樣。其優點讀取快，缺點浪費額外一部分空間。

若一張表裡面存在varchar、text以及其變形、blob以及其變形的欄位的話，那麼張這個表其實也叫動態表，即該表的row_format是dynamic，就是說每條記錄所佔用的位元組是動態。其優點節省空間的，缺點增加讀取的時間開銷。
所以，做搜尋查詢量大的表一般都以空間來換取時間，設計成靜態表。

row_format還有其他一些值：
DEFAULT
FIXED
DYNAMIC
COMPRESSED
REDUNDANT
COMPACT

修改行格式
ALTER TABLE table_name ROW_FORMAT = DEFAULT

修改過程導致：
fixed--->dynamic: 這會導致CHAR變成VARCHAR
dynamic--->fixed: 這會導致VARCHAR變成CHAR
 
MYSQL 行格式

compact行格式（innodb預設行格式），結構如下
變長欄位長度列表，null標誌位，記錄頭資訊，列1資料，列2資料。。。。
這個格式當初的設計是為了能高效存放資料，同時還有兩個隱藏列：事物ID列和復原指標列，如果沒有定義主鍵的話，每行還會增加個rowid列作為隱藏主鍵，6位元組

COMPRESSED 格式儲存的行資料會進行zlib演算法壓縮，所以適合儲存blob，text之類的大長度類型的資料

DYNAMIC 這個格式的解釋參考手冊的：

DYNAMIC 格式考慮的是如果一個較長的資料的一部分需要儲存在溢出頁上，那麼通常最有效方式就是將所有資料都儲存在溢出頁上。較短的列仍然會存放在Btree 節點上，可以減少對任何給定行所需的最少溢出頁的數量。-----適合動態，比如varchar之類的動態長度欄位

fixd行格式適合靜態定長類型 如char