MySQL行格式

所謂行格式，就是指mysql一行數據的存儲格式。

InnoDB 儲存引擎支持有四種行儲存格式：Compact、Redundant、Dynamic 和 Compressed。

Redundant是很古老的行格式了，因為占用空間最多，導致內存碎片化最嚴重，比較低效，現在基本上已經不用了，

Compact是MySQL 5.0之后引入的行記錄存儲方式，是一種緊湊的行格式，設計的初衷就是為了讓一個數據頁中可以存放更多的行記錄，從 MySQL 5.1 版本之后，行格式默認設置成 Compact。

Dynamic和Compressed 兩個都是緊湊的行格式，它們的行格式都和 Compact 差不多，因為都是基于 Compact進行改進。從 MySQL5.7 版本之后，默認使用 Dynamic 行格式。

應該說Compact格式是一個比較經典的格式，因此本文將以Compact格式為例，詳細介紹其具體的內容。

mysql表的數據存儲在哪里？

進入mysql，查看mysql的data目錄在哪里，例如下面所示：

mysql> show variables like "datadir";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

進入該目錄中，會看到一個一database命名的目錄，進入該目錄中，則會看到一個以表名+.ibd的文件，該文件即是存儲mysql表數據的文件。

COMPACT 行格式長什么樣？

compact行格式如下所示：

mysql

主要分為兩個個部分

• 存儲的額外數據
• 存儲的真實數據

存儲的額外數據中包含了變長數據列的長度，NULL值的列表，記錄頭信息。

存儲的真實數據中包含了三個隱藏列和真實數據。

首先看存儲的額外數據。

存儲的額外數據的第一塊用于記錄變長數據列的長度，其排放順序是逆序排放的。

例如下面這張表，name和city列為變長字段，由于是逆序排放的，第一條記錄的變長數據列的長度的值為07 03

+------+-------+---------+-------+
| id   | name  | city    | level |
+------+-------+---------+-------+
|    0 | tom   | Nanjing | a     |
|    1 | kitty | Beijing | b     |
|    2 | simth | Wuhan   | c     |
+------+-------+---------+-------+

額外數據的第二塊是記錄NULL值的列表，它使用bit來標記列值是否為空。其低位（最右側的位）標記第0個列是否為NULL。

例如這里的第一條記錄，其city列為NULL，因此其NULL列的值為00000100，為04。

+------+-------+---------+-------+
| id   | name  | city    | level |
+------+-------+---------+-------+
|    0 | Nancy | NULL    | c     |
|    1 | NULL  | NULL    | c     |

額外數據的第三塊是記錄頭信息，其格式如下所示，共5個字節：

接下來是存儲的真實數據部分：

其第一部分包含三個隱藏列，其格式如下所示：

• DB_ROW_ID：該字段占6個字節，用于標識一條記錄
• DB_TRX_ID：該字段占6個字節，其值為事務ID
• DB_ROLL_PTR：該字段占7個字節，其值為回滾指針

其第二部分存儲的就是每個非NULL列真實的數據。

有了這些基礎，下面對照ibd文件，具體分析。

實驗分析ibd文件格式

下面將通過分析.ibd文件的方式來進一步了解。首先需要準備好環境。這里我使用的是Docker環境進行環境準備的。

首先使用docker pull拉取最新版本的mysql的鏡像。

docker pull mysql

再鏡像拉取完畢之后啟動mysql，這里我將本地目錄掛載到了mysql容器中，便于后續獲取ibd文件。

docker run -v /home/work/data/mysql:/var/lib/mysql/ -e MYSQL_ROOT_PASSword=111111 -d 鏡像的id

進入mysql容器中，創建demo的數據庫，并在demo數據庫中創建user_tbl表。user_tbl表包含了四個字段，其中name和city字段為變長字段。id和level為固定長度字段。

create database demo;
use demo;
create table user_tbl (
	id int,
	name varchar(20) comment 'mutable-length',
	city varchar(20) comment 'mutable-length',
    level char(1) comment 'fix-length'
)row_format=compact;

進一步，向user_tbl表中添加5條測試數據。

insert into user_tbl values(0,'tom','Nanjing','a');
insert into user_tbl values(1,'kitty','Beijing','b');
insert into user_tbl values(2,'simth','Wuhan','c');
insert into user_tbl values(3,'Nancy',NULL,'c');
insert into user_tbl values(4,NULL,NULL,'c');

退出容器，去掛載的目錄中去獲取idb文件，例如，我的目錄就是
/home/work/data/mysql/demo/user_tbl.ibd。

通過二進制查看工具，例如notepad--可以很好的對其進行分析。通過記錄中的字符串，可以很快地在二進制文件中定位到位置，例如在我的實驗中，數據記錄在文件中的位置如下所示：

mysql

有了這些數據，就可以對其進行分析了。

我這里獲取到的五條的數據記錄如下所示，對照上面講解的Compact數據行格式，是一致的。

第一條數據格式：

07 03 //第三列長度為7 第二列長度為3
00 //NULL bit映射為空
00 00 10 00 2B //header info
00 00 00 00 02 00 //DB_ROW_ID 
00 00 00 00 07 19 //DB_TRX_ID
82 00 00 01 1E 01 10 //DB_ROLL_PTR 
80 00 00 00 //0
74 6F 6D    //tom 
4E 61 6E 6A 69 6E 67 //Nanjing 
61 //a
01 

第二條數據格式：

07 05 //第三列長度為7 第二列長度為5
00 //NULL bit映射為空
00 00 18 00 2D //header info
00 00 00 00 02 01 //DB_ROW_ID 
00 00 00 00 07 1A //DB_TRX_ID
81 00 00 01 1E 01 10 //DB_ROLL_PTR 
80 00 00 01 //1
6B 69 74 74 79 //kitty
42 65 69 6A 69 6E 67 //Beijing
62 //b
01 

第三條數據格式：

05 05 //第三列長度為5 第二列長度為5
00 //NULL bit映射為空
00 00 20 00 2A //header info
00 00 00 00 02 02 //DB_ROW_ID 
00 00 00 00 07 1F //DB_TRX_ID
82 00 00 01 0A 01 10 //DB_ROLL_PTR 
80 00 00 02 //2
73 69 6D 74 68 //simth
57 75 68 61 6E //Wuhan
63 //c
01 

第四條數據格式：

05 //第二列長度為5
04 //NULL 00000100 第三列為NULL
00 00 28 00 24 //header info
00 00 00 00 02 03 //DB_ROW_ID 
00 00 00 00 07 20 //DB_TRX_ID
81 00 00 01 0E 01 10 //DB_ROLL_PTR 
80 00 00 03 //3
4E 61 6E 63 79 //Nancy
63 //c
01 

第五條數據格式：

// 沒有變長列的長度
06 //NULL 00000110 第二列和第二列為NULL
00 00 30 FF 49 //header info
00 00 00 00 02 04 //DB_ROW_ID 
00 00 00 00 07 25 //DB_TRX_ID
82 00 00 01 0C 01 10 //DB_ROLL_PTR 
80 00 00 04 //4 
63 //c