导读:
作者:高鹏(网名八怪),《深入理解MySQL主从原理32讲》系列文的作者 本文版本MySQL 5.7.22,水平有限如果有误,请谅解
想阅读八怪源码文章欢迎订阅
本文建议横屏观看,效果更佳
注意:本文很多输出格式是16进制格式。
使用版本:MySQL 5.7.22
经常有朋友问我一,比如:
如果要得到答案除了学习源码,可能更加直观的方式就是查看Innodb的ibd数据文件了,俗话说得好“眼见为实”,但是我们知道数据文件是二进制形式的,Innodb通过既定的访问方式解析出其中的格式得到正确的结果。如果我们要去访问这些ibd文件,通常的方式就是可以通过hexdump -Cv这样的命令进行二进制的访问,最初我也是这样访问的,但是看起来眼睛特别难受。因此我写了2个工具:
有了这两工具可能访问ibd数据文件就更加方便一些了,下面我就使用这两个工具来进行数据文件的查看,来解决开头我们提出的这些问题。
本文无意解释详细的Innodb文件结构,这样的文章和书籍很多,比如:
整个系列都是讲解Innodb文件结构的,我们只需要知道普通数据块,除去块级别的开销后,其第一行记录从偏移量94 开始,首先出现的是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\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 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它在逻辑链表顺序中依然是在第一位。好了下面我们就来将问题逐一解决。
在我们的语句中我们最后一条记录插入的数据就是'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
我们来解析一样:
我们发现后面都是0了,实际上字符‘a’即便在UTF8字符下也只是占用一个字节而已。
下面我先总结一下:
下面我们验证一下,我们来观察第2行数据,即:
| 2| gaopeng | NULL | gaopeng |
在主键上这条记录存在于(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
解析一下:
这里我们发现这条记录没有第三个字段,因为其为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
解析如下:
后面的内容是下一行的行头了,这一点如果不确定可以看看最后一行,最后一行的位置是(offset:173)查看如下:
[root@gp1 test]# ./bcview baguait1.ibd 1617320|grep 00000004
current block:00000004--Offset:00173--cnt bytes:20--data is:6180000004000000000000000000000000000000
解析为:
后面是0了,我们这里可以看到没有trx id和roll ptr,除了键值以外普通索引还包含了主键。
这一点我在上面已经说了,下面我们还是以第二行数据为例:
| 2| gaopeng | NULL | gaopeng |
其中第1个‘gaopeng’是varchar(20)第2个‘gaopeng’是char(20)下面是他们的存储方式:
不再过多熬述
这一点还记得‘行头’的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位图信息,计算方式如下:
好了有了偏移量我们可以使用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;
这一点实际上也很好证明,我们先来建立一个不包含主键并且插入一条记录如下:
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 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 1612860|grep 00000003
current block:00000003--Offset:00128--cnt bytes:60--data is:000001ac310000000005d97fea0000002c01108000000167616f70656e6767616f70656e672020202020202020202020202000000000000000000000
我们来解析一下:
当然这里只是列举了一些例子来说明工具的使用方式,可以按照你的需求方便的从ibd文件中提取出你感兴趣的信息。