MySQL：如何快速的查看Innodb数据文件

阳光穿透心脏的1/2处 2023-06-11 09:24 6阅读 0赞

**导读：**

> 作者：高鹏（网名八怪），《深入理解MySQL主从原理32讲》系列文的作者
> 
> 本文版本 **MySQL 5.7.22**，水平有限如果有误，请谅解

**想阅读八怪源码文章欢迎订阅**

![640?wx\_fmt=png][640_wx_fmt_png]

**本文建议横屏观看，效果更佳**

--------------------

**注意：本文很多输出格式是16进制格式。**

**使用版本：MySQL 5.7.22**

经常有朋友问我一些如何查看Innodb数据文件的问题，比如：

*  如果我是UTF8字符集，如果插入字符‘a’到底占用几个字节 ？
 *  主键和普通索引叶子节点的行数据在存储上有哪些区别？
 *  如何证明rowid的存在？
 *  数据中的NULL值如何存储的？
 *  char和varchar在存储上的区别？......

如果要得到答案除了学习源码，可能更加直观的方式就是查看Innodb的ibd数据文件了，俗话说得好“眼见为实”，但是我们知道数据文件是二进制形式的，Innodb通过既定的访问方式解析出其中的格式得到正确的结果。如果我们要去访问这些ibd文件，通常的方式就是可以通过hexdump -Cv这样的命令进行二进制的访问，最初我也是这样访问的，但是看起来眼睛特别难受。因此我写了2个工具：

*  innblock：一个用于解析数据块的工具，能够得到每行的偏移量，并且按照逻辑和物理顺序排序。
    
    详细使用方式可以参考： https://mp.weixin.qq.com/s/yfi5XikDJlh6-nS-eoJbcA
    
    下载地址：
    
    https://github.com/gaopengcarl/innbloc
    
    除了代码我已经编译好了直接使用即可
 *  bcview：一个小工具，用于将数据文件按照既定的大小（比如16K）分块，然后访问每个块的偏移量后指定的字节数，通常我们并不知道记录到底多长，可以设置一个较大的查看字节数。
    
    下载地址：
    
    https://github.com/gaopengcarl/bcview
    
    除了代码我已经编译好了直接使用即可

有了这两工具可能访问ibd数据文件就更加方便一些了，下面我就使用这两个工具来进行数据文件的查看，来解决开头我们提出的这些问题。

### **一、行结构简述** ###

本文无意解释详细的Innodb文件结构，这样的文章和书籍很多，比如：

*  https://blog.jcole.us/innodb/

整个系列都是讲解Innodb文件结构的，我们只需要知道普通数据块，除去块级别的开销后，其第一行记录从偏移量94 开始，首先出现的是2个伪列 infimum 和 supremum，它们的位置固定在块的94-120字节，其中94-107为infimum 相关信息，而107到120为supremum相关信息，分别的heap no 为 0和1，它们是逻辑记录的开始和结尾，所有的实际的记录都链接在这一条链表上。

其中普通记录的大概格式如下：

![640?wx\_fmt=png][640_wx_fmt_png 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 inset(0.01 sec)

我们发现这里实际上除了rowid问题还不能包含，其他都包含了，接下来我们使用innblock进行扫描。如下：

> 1、扫描数据文件找到主键和普通索引数据块

[root@gp1 test]# ./innblock baguait1.ibd scan 16	
    ···	
    DatafileTotalSize: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 316	
    链表部分：	
    ==== 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 416	
    链表部分：	
    ==== 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 1626230|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行数据，即：

在主键上这条记录存在于（offset:180）中，在普通索引这条记录存在于（offset:137）中，下面我们分别解析：

> 主键（block 3 offset 180 ）：

[root@gp1 test]# ./bcview baguait1.ibd 1618050|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 1613720|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 1617320|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在存储上的区别？** ###

这一点我在上面已经说了，下面我们还是以第二行数据为例：

其中第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

下面就是NULL位图的表示方法，1为NULL，我们发现和我们记录中的NULL记录一模一样。

我们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 inset(0.00 sec)

使用innblock扫描发现其只包含了1个块如下：

[root@gp1 test]# ./innblock baguait1.ibd scan 16	
    ...	
    DatafileTotalSize:98304	
    ===INDEX_ID:325	
    level0 total block is(1)	
    block_no:         3,level:   0|*|

然后扫描这个块如下：

[root@gp1 test]# ./innblock baguait1.ibd 316	
    ...	
    ==== 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 1612860|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文件中提取出你感兴趣的信息。

**识别下方二维码添加作者为好友**

![640?wx\_fmt=png][640_wx_fmt_png 2]

END

--------------------

**点击下图小程序订阅**

**《深入理解MySQL主从原理32讲》专栏**

**可了解更多八怪技术文章**

**![640?wx\_fmt=png][640_wx_fmt_png]**

***![640?wx\_fmt=png][640_wx_fmt_png 3]***

![640?wx\_fmt=gif][640_wx_fmt_gif]

扫码加入MySQL技术Q群

**（群号：****650149401）**

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

[640_wx_fmt_png]: /images/20230531/b28c5a56460f4b3a896604c7c5ac5851.png
[640_wx_fmt_png 1]: /images/20230531/51697da7148948b6a35a9a423139ab74.png
[640_wx_fmt_png 2]: /images/20230531/9cfab72e18244488b323193765420e45.png
[640_wx_fmt_png 3]: /images/20230531/02e0d17544df43c5bf4dbe91c44d0eb3.png
[640_wx_fmt_gif]: /images/20230531/dd6d3b0dc6db4a04a6089c1386cff777.png
[640_wx_fmt_jpeg]: /images/20230531/87822669bd834450b7bd299b11a71c08.png