MySQL：如何快速的查看Innodb数据文件-阿里云开发者社区

欢迎关注我的《深入理解MySQL主从原理 32讲》，如下：

水平有限，有误请谅解

注意：本文很多输出格式是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/innblock 除了代码我已经编译好了直接使用即可
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，它们是逻辑记录的开始和结尾，所有的实际的记录都链接在这一条链表上。

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

格式.jpg

我暂且将黄色部分称为‘行头’，图中用粉红色标记的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字节

关于rowidtrx idroll 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    |
+----+---------+---------+---------+

c1c2c3均可以为空，因此我们分别访问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记录一模一样。

||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文件中提取出你感兴趣的信息。

作者微信：gaopp_22389860

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

一、行结构简述

二、建立测试表

三、如果我是UTF8字符集，如果插入字符‘a’到底占用几个字节？

四、主键和普通索引叶子节点的行数据在存储上有哪些区别？

五、char和varchar在存储上的区别？

六、数据中的NULL值如何存储的？

七、如何证明rowid的存在？

最后：

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

一、行结构简述

二、建立测试表

三、如果我是UTF8字符集，如果插入字符‘a’到底占用几个字节 ？

四、主键和普通索引叶子节点的行数据在存储上有哪些区别？

五、char和varchar在存储上的区别？

六、数据中的NULL值如何存储的？

七、如何证明rowid的存在？

最后：

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

三、如果我是UTF8字符集，如果插入字符‘a’到底占用几个字节？