MySQL：如何快速的檢視Innodb資料檔案
歡迎關注我的《深入理解MySQL主從原理 32講 》，如下：
注意：本文輸出格式全是16進位制格式。
經常有朋友問我一些如何檢視Innodb資料檔案的問題比如：
- 如果我是UTF8字符集，如果插入字元‘a’到底佔用幾個位元組 ？
- 主鍵和普通索引葉子節點的行資料在儲存上有哪些區別？
- 如何證明rowid的存在？
- 資料中的NULL值如何儲存的？
- char和varchar在儲存上的區別？
……
如果要得到答案除了學習原始碼，可能更加直觀的方式就是檢視Innodb的ibd資料檔案了，俗話說得好“眼見為實”，但是我們知道資料檔案是二進位制形式的，Innodb通過既定的訪問方式解析出其中的格式得到正確的結果。如果我們要去訪問這些ibd檔案，通常的方式就是可以通過hexdump -Cv這樣的命令進行二進位制的訪問，最初我也是這樣訪問的，但是看起來眼睛特別難受。因此我寫了2個工具：
-
innblock：一個用於解析資料塊的工具，能夠得到每行的偏移量，並且按照邏輯和物理順序排序。詳細使用方式可以參考 https://www.jianshu.com/p/5c1a99614fb8
下載地址： https://github.com/gaopengcarl/innblock 除了程式碼我已經編譯好了直接使用即可 -
bcview：一個小工具，用於將資料檔案按照既定的大小（比如16K）分塊，然後訪問每個塊的偏移量後指定的位元組數，通常我們並不知道記錄到底多長，可以設定一個較大的檢視位元組數。
下載地址： https://github.com/gaopengcarl/bcview 除了程式碼我已經編譯好了直接使用即可
有了這兩工具可能訪問ibd資料檔案就更加方便一些了，下面我就使用這兩個工具來進行資料檔案的檢視，來解決開頭我們提出的這些問題。
一、行結構簡述
本文無意解釋詳細的Innodb檔案結構，這樣的文章和書籍很多，比如：
整個系列都是講解Innodb檔案結構的，我們只需要知道普通資料檔案，除去塊級別的開銷後，其第一行記錄從偏移量96 開始，首先出現的是2個偽列 infimum 和 supremum，它們的位置固定在塊的94-120位元組，其中94-107為infimum 相關資訊，而107到120為supremum相關資訊，分別的heap no 為 0和1，它們是邏輯記錄的開始和結尾，所有的實際的記錄都連結在這一條連結串列上。
其中普通記錄的大概格式如下：
我暫且將黃色部分稱為‘行頭’，圖中用粉紅色標記的innblock每行資料offset的位置， 我們發現innblock工具指向的是行頭以後實際欄位開啟的位置。
下面是一個innblock工具典型的部分輸出：
-----Total used rows:3 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(3) SUPREMUM record offset:112 heapno:1 n_owned 2,delflag:N minflag:0 rectype:3
-----Total used rows:3 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 2,delflag:N minflag:0 rectype:3
(3) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
我們可以找到一行除了infimum和 supremum記錄以外的normal記錄，並且標記了這樣記錄欄位的起點（offset:128），也就是圖中的粉紅色部分，但是需要注意的是聚集索引（表本身）而言，如果沒有主鍵前面3列分別為：
- rowid 6位元組
- trx id 6位元組
- roll ptr 7位元組
如果存在主鍵則為：
- 主鍵 和定義有關
- trx id 6位元組
- roll ptr 7位元組
關於rowid\trx id\roll ptr的原始碼中的定義如下：
#define DATA_ROW_ID 0 /* row id: a 48-bit integer */
#define DATA_ROW_ID_LEN 6 /* stored length for row id */
#define DATA_TRX_ID 1 /* transaction id: 6 bytes */
#define DATA_TRX_ID_LEN 6
#define DATA_ROLL_PTR 2 /* rollback data pointer: 7 bytes */
#define DATA_ROLL_PTR_LEN 7
而roll ptr的具體含義可以參考函式trx_undo_decode_roll_ptr如下：
/***********************************************************************//**
Decodes a roll pointer. */
//從高位到低位依次是
//第1位是否是insert
//第2到8位是segmentid
//第9到40位為page no
//第41位到56位為OFFSET
UNIV_INLINE
void
trx_undo_decode_roll_ptr(
/*=====================*/
roll_ptr_t roll_ptr, /*!< in: roll pointer */
ibool* is_insert, /*!< out: TRUE if insert undo log */
ulint* rseg_id, /*!< out: rollback segment id */
ulint* page_no, /*!< out: page number */
ulint* offset) /*!< out: offset of the undo
entry within page */
{
...
ut_ad(roll_ptr < (1ULL << 56));
*offset = (ulint) roll_ptr & 0xFFFF; //獲取低16位 為OFFSET
roll_ptr >>= 16; //右移16位
*page_no = (ulint) roll_ptr & 0xFFFFFFFF;//獲取32位為 page no
roll_ptr >>= 32;//右移32位
*rseg_id = (ulint) roll_ptr & 0x7F;//獲取7位為segment id
roll_ptr >>= 7;//右移7位
*is_insert = (ibool) roll_ptr; /* TRUE==1 *///最後一位
}
二、建立測試表
為了解決文中開頭的幾個問題，我們來建立測試表如下：
drop table baguait1;
create table baguait1(id int primary key,c1 varchar(20) ,c2 varchar(20),c3 char(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table baguait1 add key(c1);
insert into baguait1 values(1,NULL,'gaopeng','gaopeng');
insert into baguait1 values(2,'gaopeng',NULL,'gaopeng');
insert into baguait1 values(3,'gaopeng',NULL,NULL);
insert into baguait1 values(4,'a',NULL,NULL);
mysql> select * from baguait1;
+----+---------+---------+---------+
| id | c1 | c2 | c3 |
+----+---------+---------+---------+
| 1 | NULL | gaopeng | gaopeng |
| 2 | gaopeng | NULL | gaopeng |
| 3 | gaopeng | NULL | NULL |
| 4 | a | NULL | NULL |
+----+---------+---------+---------+
4 rows in set (0.01 sec)
我們發現這裡實際上除了rowid問題還不能包含，其他都包含了，接下來我們使用innblock進行掃描。如下：
1、掃描資料檔案找到主鍵和普通索引資料塊
[root@gp1 test]# ./innblock baguait1.ibd scan 16
···
Datafile Total Size:114688
===INDEX_ID:323
level0 total block is (1)
block_no: 3,level: 0|*|
===INDEX_ID:324
level0 total block is (1)
block_no: 4,level: 0|*|
這裡實際上323就是聚集索引，324就是普通索引，它們資料塊對應是3和4。
2、掃描聚集索引記錄
[root@gp1 test]# ./innblock baguait1.ibd 3 16
連結串列部分：
==== Block list info ====
-----Total used rows:6 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:180 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:231 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:262 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(6) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
-----Total used rows:6 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
(3) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:180 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:231 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:262 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
3、掃描普通索引記錄
```
[root @gp1 test]# ./innblock baguait1.ibd 4 16
連結串列部分：
==== Block list info ====
——-Total used rows:6 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:173 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:137 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:155 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(6) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
——-Total used rows:6 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
(3) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:137 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:155 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:173 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
我們發現不管聚集索引還是普通索引均包含了4條普通記錄，並且採集到了記錄的偏移量，我們需要注意一下這裡普通索引的邏輯連結串列順序中我們可以看到第4條記錄（offset:173 ）已經排到了第3位，實際上它是最後插入的，這是因為‘a’字元的順序肯定是在‘gaopeng’這個字串之前的。並且第一行記錄C1為NULL它在邏輯連結串列順序中依然是在第一位。好了下面我們就來將問題逐一解決。
###三、如果我是UTF8字符集，如果插入字元‘a’到底佔用幾個位元組 ？
在我們的語句中我們最後一條記錄插入的資料就是'a'，即：
| 4 | a | NULL | NULL |
我們使用bcview來檢視一下聚集索引 （offset 262 ）的資料是啥，我們直接從塊3的（offset 262 ）後檢視20個位元組，如下：
[root
@gp1 test]# ./bcview baguait1.ibd 16 262 30|grep 00000003
current block:00000003—Offset:00262—cnt bytes:30—data is:8000000400000005d970e000000043011061000000000000000000000000
我們來解析一樣：
- 80000004：主鍵4，8是符號位
- 400000005d970：trx id 6位元組
- e0000000430110：undo ptr 7位元組
- 61：字元‘a’，ASCII編碼
我們發現後面都是0了，實際上字元‘a’即便在UTF8字元下也只是佔用一個位元組而已。
###四、主鍵和普通索引葉子節點的行資料在儲存上有哪些區別？
下面我先總結一下：
- 主鍵會包含全部的欄位，普通索引只會包含它定義的欄位內容
- 主鍵會包含trx id和roll ptr，普通索引不會包含
- 即便不定義主鍵也會包含一個根據rowid排列的聚集索引，很明顯如果不定義普通索引則不會存在
- 普通索引葉子結點包含了主鍵或者rowid
下面我們驗證一下，我們來觀察第2行資料，即：
| 2 | gaopeng | NULL | gaopeng |
在主鍵上這條記錄存在於（offset:180）中，在普通索引這條記錄存在於（offset:137）中，下面我們分別解析：
>主鍵（block 3 offset 180 ）：
[root
@gp1 test]# ./bcview baguait1.ibd 16 180 50|grep 00000003
current block:00000003—Offset:00180—cnt bytes:50—data is:8000000200000005d96adc00000042011067616f70656e6767616f70656e6720202020202020202020202020070600002000
解析一下：
- 80000002：主鍵
- 00000005d96a：trx id 6位元組
- dc000000420110：undo ptr 7位元組
- 67616f70656e67：第二個欄位的‘gaopeng’的ASCII編碼
- 67616f70656e6720202020202020202020202020：第四個欄位的‘gaopeng’的ASCII編碼，並且因為是char(20)型別因此出現了0X20補足的情況，這實際上也解決了第5個問題，我們可以實實在在的看到這種補足操作，佔用了更多的空間。
這裡我們發現這條記錄沒有第三個欄位，因為其為NULL，其包含在NULL點陣圖中，後面我們會說明。
>普通索引（block 4 offset 137 ）：
[root
@gp1 test]# ./bcview baguait1.ibd 16 137 20|grep 00000004
current block:00000004—Offset:00137—cnt bytes:20—data is:67616f70656e67800000020700000020ffd56761
解析如下：
- 67616f70656e67：‘gaopeng’的ASCII編碼
- 80000002：主鍵值2
後面的內容是下一行的行頭了，這一點如果不確定可以看看最後一行，最後一行的位置是（offset:173）檢視如下：
[root
@gp1 test]# ./bcview baguait1.ibd 16 173 20|grep 00000004
current block:00000004—Offset:00173—cnt bytes:20—data is:6180000004000000000000000000000000000000
解析為：
- 61：‘a’的ASCII編碼
- 80000004：主鍵值4
後面是0了，我們這裡可以看到沒有trx id和roll ptr，除了鍵值以外普通索引還包含了主鍵。
###五、char和varchar在儲存上的區別？
這一點我在上面已經說了，下面我們還是以第二行資料為例：
| 2 | gaopeng | NULL | gaopeng |
其中第1個‘gaopeng’是varchar（20）第2個‘gaopeng’是char（20）下面是他們的儲存方式：
- 67616f70656e67：第二個欄位的‘gaopeng’的ASCII編碼
- 67616f70656e6720202020202020202020202020：第四個欄位的‘gaopeng’的ASCII編碼，並且因為是char(20)型別因此出現了0X20補足20位元組的情況，我們可以實實在在的看到這種補足操作，佔用了更多的空間。
不再過多熬述
###六、資料中的NULL值如何儲存的？
這一點還記得‘行頭’的NULL點陣圖嗎？實際上這個點陣圖會為每一個可以為NULL的欄位預留1位的空間，用於標記是否欄位的值為NULL，當然至少1位元組（8位）。
+——+————-+————-+————-+
| id | c1 | c2 | c3 |
+——+————-+————-+————-+
| 1 | NULL | gaopeng | gaopeng |
| 2 | gaopeng | NULL | gaopeng |
| 3 | gaopeng | NULL | NULL |
| 4 | a | NULL | NULL |
+——+————-+————-+————-+
c1\c2\c3均可以為空，因此我們分別訪問4條記錄聚集索引（block 3）上的NULL點陣圖資訊，計算方式如下：
- 第1行：記錄（offset:128）那麼128-5（5位元組固定）-1（1位元組NULL點陣圖）= 122
- 第2行：記錄（offset:180）那麼180-5（5位元組固定）-1（1位元組NULL點陣圖）= 174
- 第3行：記錄（offset:231）那麼231-5（5位元組固定）-1（1位元組NULL點陣圖）= 225
- 第4行：記錄（offset:262）那麼262-5（5位元組固定）-1（1位元組NULL點陣圖）= 256
好了有了偏移量我們可以使用bcview訪問這1位元組的NULL點陣圖資訊瞭如下：
>第1行
[root@gp1 test]# ./bcview baguait1.ibd 16 122 1 |grep 00000003
current block:00000003--Offset:00122--cnt bytes:01--data is:01
轉換為二進位制為：0000 0001
>第2行
[root@gp1 test]# ./bcview baguait1.ibd 16 174 1 |grep 00000003
current block:00000003--Offset:00174--cnt bytes:01--data is:02
轉換為二進位制為：0000 0010
>第3行
[root@gp1 test]# ./bcview baguait1.ibd 16 225 1 |grep 00000003
current block:00000003--Offset:00225--cnt bytes:01--data is:06
轉換為二進位制為：0000 0110
>第4行
[root@gp1 test]# ./bcview baguait1.ibd 16 256 1 |grep 00000003
current block:00000003--Offset:00256--cnt bytes:01--data is:06
轉換為二進位制為：0000 0110
下面就是這種表示方法，1為NULL，我們發現和我們記錄中的NULL記錄一模一樣。
||c3|c2|c1|
|-|-|-|-|
|第1行|0|0|1|
|第2行|0|1|0|
|第3行|1|1|0|
|第4行|1|1|0|
我們DDL修改欄位的NULL屬性的時候並不能通過修改資料字典來快速完成，我覺得修改更改ibd檔案的實際內容是其中很大的一部分原因。下面是我修改NULL屬性的記錄，具體參考官方文件。
設定NULL和NOT NULL屬性
都是inplace方式，因為需要修改NULL點陣圖 因此都需要重組，代價較高
ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
###七、如何證明rowid的存在？
這一點實際上也很好證明，我們先來建立一個不包含主鍵並且插入一條記錄如下：
drop table baguait1;
create table baguait1(id int ,c1 varchar(20) ,c2 varchar(20),c3 char(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into baguait1 values(1,NULL,’gaopeng’,’gaopeng’);
mysql> select * from baguait1;
+———+———+————-+————-+
| id | c1 | c2 | c3 |
+———+———+————-+————-+
| 1 | NULL | gaopeng | gaopeng |
+———+———+————-+————-+
1 row in set (0.00 sec)
使用innblock掃描發現其只包含了1個塊如下：
[root
@gp1 test]# ./innblock baguait1.ibd scan 16
…
Datafile Total Size:98304
===INDEX_ID:325
level0 total block is (1)
block_no: 3,level: 0|*|
然後掃描這個塊如下：
[root @gp1 test]# ./innblock baguait1.ibd 3 16
…
==== Block list info ====
——-Total used rows:3 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(3) SUPREMUM record offset:112 heapno:1 n_owned 2,delflag:N minflag:0 rectype:3
——-Total used rows:3 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 2,delflag:N minflag:0 rectype:3
(3) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
然後使用bcview檢視（offset:128 ）這條記錄如下：
[root
@gp1 test]# ./bcview baguait1.ibd 16 128 60 |grep 00000003
current block:00000003—Offset:00128—cnt bytes:60—data is:000001ac310000000005d97fea0000002c01108000000167616f70656e6767616f70656e672020202020202020202020202000000000000000000000
```
我們來解析一下：
- 000001ac3100：rowid 6位元組，上面的測試中這裡是主鍵定義的相關欄位值
- 00000005d97f：trx id 6位元組
- ea0000002c0110：roll ptr 7位元組
- 80000001：第1個欄位值 1
- 67616f70656e67：第2個欄位值 ‘gaopeng’的ASCII編碼
- 67616f70656e6720202020202020202020202020：第4個欄位值‘gaopeng’的ASCII編碼，並且char有0X20補足20位元組。
最後：
當然這裡只是列舉了一些例子來說明工具的使用方式，可以按照你的需求方便的從ibd檔案中提取出你感興趣的資訊。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2661713/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 檢視資料檔案大小
- 檢視資料庫資料檔案的總大小資料庫
- MySQL InnoDB系統表空間資料檔案配置MySql
- 物化檢視如何快速完成資料聚合操作？
- 小工具檢視MYSQL資料檔案塊和型別MySql型別
- mysql InnoDB鎖等待的檢視及分析MySql
- 【exp】快速檢測資料檔案壞塊
- 如何檢視EBS 檔案版本
- win10 如何檢視隱藏的資料夾 win10檢視隱藏檔案方法Win10
- datafile.sql 檢視資料檔案和臨時檔案SQL
- 叢集資料庫重要檔案的檢視管理資料庫
- 檢視MySQL資料庫的命令MySql資料庫
- MySQL InnoDB日誌檔案配置MySql
- MySQL資料庫檢視：檢視定義、建立檢視、修改檢視MySql資料庫
- mysql的資料檔案MySql
- 【大資料】【hadoop】檢視hdfs檔案命令大資料Hadoop
- du df 檢視檔案和資料夾大小
- 分享一個檢視xhprof資料檔案的docker映象Docker
- 檢視錶空間及資料檔案的checkpoint資訊
- 檢視Windows檔案系統資料塊大小的方法Windows
- 除了TRACE檔案,在哪檢視資料庫的 maxlogfiles ?資料庫
- MySQL innodb共享表空間新增表空間資料檔案方法MySql
- MySQL:Innodb如何快速殺掉堵塞會話的思考MySql會話
- win10如何檢視heic檔案_win10怎麼檢視heic檔案Win10
- 如何在Windows下利用Apche檢視MySQL資料庫？WindowsMySql資料庫
- 用bbed檢視資料檔案的資料塊block 0及block 1BloC
- win10 如何檢視raw檔案 win10 如何檢視rawWin10
- 檢視 Linux 檔案佔用程式寫資料?Linux
- 讓你在macOS上快速檢視txt文字檔案Mac
- 如何快速檢視 Mac 的溫度Mac
- Win10系統快速檢視檔案屬性的技巧Win10
- 如何檢視某一表空間的資料檔案都分佈了哪些資料物件薦物件
- [MySQL Help]答朋友問：5000W記錄的Innodb表如何快速的去重複資料MySql
- win10怎麼檢視最近修改的檔案_win10如何檢視最近開啟的檔案Win10
- 如何快速歸類整理檔案並新建資料夾
- 【DB 2學習】檢視一個資料庫的配置檔案資料庫
- swap檔案檢視
- 檢視大檔案