关于MySQL数据被删除后空间重用的问题实验

以前知道，MySQL在通过delete语句删除数据后，空间并不会被腾出，而只是在数据文件中被标记为已删除，除非执行optimize table。前两天听说，虽然delete数据后硬盘空间不会被腾出，但是在以后插入的行，会使用被删除的数据的空间。换句话说，尽管硬盘空间没有被腾出，新插入数据也不会受影响，因为他们根本不会使用剩余的磁盘空间。这个在之前还真是从没听说过。

         对于这个问题，我做了一个实验，通过分析数据插入和删除后ibd文件的变化，来分析空间重用的情况。

        

创建表 ：

CREATE TABLE `test` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `a` varchar(30) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

行格式为默认的compact

 

 

 

第一步，插入数据：

先插入30条一样的数据

insert into test (a) values (‘jwx’);

 

然后查看test.ibd文件

hexdump  -C -v test.ibd > tmp

可以看到，数据已经正常写入,如截图所示

 

 

因为只插入了30条数据，所以只占用一个数据页，这也是为了看起来方便。

根据上图可以通过数据页的page header 看出最后插入的位置的偏移量PAGE_LAST_INSERT是 03 70，然后可以看到0xc370的位置就是最后一行所在的位置，如图

 

 

同时可以看到，PAGE_HEAP_TOP为03 84，指向0xc384，后面的内容就都是空闲空间了。

 

 

第二步，删除一行数据

delete from test where id = 1

删除id为1的数据

然后观察ibd文件

 

可以看到位于0xc079位置的数字变成了20，这是表示改行已被删除，后面可以看到行索引id为 00 00 00 01，此时id为1的行已被删除，同时可以注意到0xc08f到0xc091为 6a 77 78,这三个字节即为’jwx’，行内容仍然保留着。然后PAGE_HEAP_TOP保持不变，而PAGE_LAST_INSERT则变为0x0000，大概是因为本次操作为delete（这个是猜的，不过和后面的结论关系不大）。

 

第三步，插入一行数据

insert into test (a) values (‘ss’)

观察ibd文件

 

可以看到，第一行的信息有了明显的变化，0xc078从03变成了02，该字节表示本行变长字段的长度，从03（’jwx’）变成了02（’ss’） 0xc08f到0xc091变成了73 73 78(‘ssx’)，索引列字段变成了00 00 00 1f(31)，而PAGE_LAST_INSERT则是00 7e，指向第一行的开头，而不是堆顶，可以看出被删除的数据空间被重用了。

   

通过上面的实验可以看出mysql行被删除后可以重用空间，其实在页的PAGE_HEADER部分有一个PAGE_FREE字段就是用来记录可重用空间的首指针位置的，0xc02c开始的两个字节即为PAGE_FREE字段。mysql官方文档是这样描述这个字段的：

 

 

当然，空间重用还有一个条件，就是被删除的行长度至少要和新插入的行长度一样，如果小于新插入的行，则空间就没法重用了。

 

关于这个问题，stackoverflow上也有回答，详情可以看以下链接

http://stackoverflow.com/questions/634257/does-the-space-occupied-by-deleted-rows-get-re-used