mysql-innodb更新varchar字段值时存储的变化

这篇笔记是innodb行格式这一篇的后续,主要是想看一下varchar类型的字段更新的时候数据页的变化。

首先还是建立一个与innodb行格式这篇笔记一样的表,只不过我改了一下表名。

CREATE TABLE `testupdate` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` varchar(255) DEFAULT NULL,
  `c3` char(11) DEFAULT NULL,
  `c4` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT

往里面插入了一样的数据。那么我们可以设想,将testupdate.ibd转成十六进制来查看的时候,也可以在0xC080找到第一行的内容,可以在0xC0C1找到第三行的内容,同理可以在同样的位置找到其他行的内容。。

mysql> select * from testupdate;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 | a    | bbbb | cc   |
|  2 | bb   | NULL | NULL |
|  3 | cc   | NULL | dd   |
|  4 | dddd | e    | NULL |
+----+------+------+------+
4 rows in set (0.00 sec)

下面修改一下c1=4这一行的数据里面的c2列

mysql> update testupdate set c2='hello world' where c1=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from testupdate;
+----+-------------+------+------+
| c1 | c2          | c3   | c4   |
+----+-------------+------+------+
|  1 | a           | bbbb | cc   |
|  2 | bb          | NULL | NULL |
|  3 | cc          | NULL | dd   |
|  4 | hello world | e    | NULL |
+----+-------------+------+------+
4 rows in set (0.00 sec)

还是将修改之后testupdate.ibd转成十六进制来查看,执行了update之后的十六进制文件。

update之后的十六进制

先看一下第三行的next_record数据,它指向的是第四行的位置。

我们在前一篇笔记innodb行格式里面知道了可以在0xC0C1找到第三行的内容。从0xC0C1往前两个字节,里面存的就是我们想要的next_record,可以看到是0x0043,那么第四行的内容位置是0xC0C1 + 0x0043 = 0xC104

0xC104开始看:

00 00 00 04                            // 这是c1列的值
00 00 00 00 f6 81                      // 这是6个字节的事务id列,我们可以不用关心
39 00 00 01 b5 0f 5b                   // 这是7个字节的回滚指针列,我们可以不用关心
68 65 6c 6c 6f 20 77 6f 72 6c 64       // 这是c2列的值,也就是hello world
65 20 20 20 20 20 20 20 20 20 20       // 这是c3列的值,65也就是字符e,因为c3是char(11),所以会进行填充
                                       // c4列是null,所以不占空间

我在innodb行格式这篇笔记里面写到,没有update之前,原来的第四行的内容是从0xC0DD。而根据上面的分析,update之后,第四行是在0xC104

所以我们可以分析出来,对于变长字段的更新,如果更新后的值的长度超过了原来的长度,即原来的位置不够存了,那么会重新在新的位置存储该条记录的内容。

上面验证的是,修改varchar列,update后的字符数比原字符数多的情况。下面来看一下update后的字符数比原字符数少的情况。

继续在上面的基础上,执行update语句:

mysql> update testupdate set c2='hi' where c1=4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from testupdate;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 | a    | bbbb | cc   |
|  2 | bb   | NULL | NULL |
|  3 | cc   | NULL | dd   |
|  4 | hi   | e    | NULL |
+----+------+------+------+
4 rows in set (0.00 sec)

再次将ibd转成十六进制文件,看一下里面的内容。

这次我们还是按照上面的方式,先看第三行的next_record指向的第四行的位置,再看第四行的内容。

可以从第三行的next_record里面看到,存储的还是0x0043,那么也就是说第四行的位置还是在0xC104。那么我们从0xC104往后看

00 00 00 04                            // 这是c1的值
00 00 00 00 f6 84                      // 这是6个字节的事务id列,我们可以不用关心
3b 00 00 02 34 2d 8f                   // 这是7个字节的回滚指针列,我们可以不用关心
68 69                                  // 这是c3列的值,也就是hi
65 20 20 20 20 20 20 20 20 20 20       // 这是c3列的值,65也就是字符e,因为c3是char(11),所以会进行填充
20 20 20 20 20 20 20 20 20             // 因为从update之后,第四行整体比之前少占了9个字节,所以用0x20填充

我们需要注意的是,从update之后,第四行整体比之前少占了9个字节,所以用0x20填充。因为我们在每行的compact记录头里面有变长字段长度、NULL标志位,所以innodb不会把用于填充的0x20作为c4列的值。

所以,我们的结论是:

  • 对于变长字段的更新,如果更新后的值的长度超过了原来的长度,即原来的位置不够存了,那么会重新在新的位置存储该条记录的内容。
  • 对于变长字段的更新,如果更新后的值的长度没超过原来的长度,那么会在原来的位置上修改。
Show Comments