MySQL表空间回收的正确姿势，java面试题高并发

MySQL表空间回收的正确姿势，java面试题高并发

在MySQL控制台执行 call multinsert(0,100000) 命令，往 ta表插入10万条数据

mysql> call multinsert(0,100000); mysql> select count(*) from ta; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.02 sec)

查看磁盘上ta表的数据文件 ta.ibd 的大小

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ [root@ecs-centos-7 test]# ls -l ta.ibd -rw-r----- 1 mysql mysql 11534336 1月 3 23:14 ta.ibd

从上面的结果可以知道，ta表插入10万条数据之后，ta.ibd 大小为 11534336 字节( 大约 11M )

现在我们使用 delete 命令删除一半儿表数据( 5万行记录 )

mysql> delete from ta where id between 1 and 50000; Query OK, 10000 rows affected (0.03 sec) mysql> select count(*) from ta; +----------+ | count(*) | +----------+ | 50000 | +----------+ 1 row in set (0.02 sec)

删除 *** 作完成之后，再次查看磁盘上 ta.ibd 的大小

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ [root@ecs-centos-7 test]# ls -l ta.ibd -rw-r----- 1 mysql mysql 11534336 1月 3 23:14 ta.ibd

从上面的结果可以知道，ta表删除了一半儿，也就是5万行数据之后，ta.ibd的大小是 11534336 字节( 约11M )

也就是说 ta表删除数据前后，磁盘上表数据文件并没有缩小

要弄明白数据文件为什么没有缩小，就需要深入了解删除数据的原理

删除数据原理

我们都知道，InnoDB里的数据都是用B+树组织的，关于B+树的知识请参考 理解B+树

图(1)

上面是InnoDB的索引示意图，其中用虚线框起来的节点是属于Page1数据页，叶子节点存储的是索引对应的数据，它们按照索引从小到大的顺序组成了一个有序数组

假如我们要删除Page1页中索引key值为 13 的数据，也即上图中红色部分

InnoDB引擎会把索引key值为13的节点标记为已删除，它并不会回收节点真实的物理空间，只是将它标记为已删除的节点，后续是可以复用的，所以，删除表记录，磁盘上数据文件不会缩小

你可能会说，上面只是删除了Page1页中一个节点的数据，那如果把Page1页中节点数据全部删除了，应该会回收Page1页的空间吧？

答案是，不会回收

当Page1页数据全部删除了，整个数据页都会被标记为已删除，并且整个数据页都可以复用，所以，这种情况下，磁盘上的数据文件仍然不会缩小

数据的复用

数据的复用涉及到数据节点的插入、删除、转移以及数据页的合并等 *** 作，具体的 *** 作流程相关的细节请参考 理解B+树，这里就不再重复说明了

• 数据节点的复用

在上面 图(1) 中，当删除了索引key值为 13 的节点后，此节点就被标记为可复用的

如果之后又插入了一条索引key值在 7 到 18 之间的记录时，就会复用原来索引key值为13的数据节点

但是如果之后插入的记录的索引key值不在 7 到 18 之间时，可能就无法复用原来索引key值为13的数据节点

也就是说，数据节点的复用，需要索引key值满足一定的范围条件

• 数据页的复用

在 图(1) 当删除了Page1数据页全部数据节点后，Page1整页都是可复用的，当插入的记录需要用到新页的时候，Page1就可以被复用

当相邻的数据页利用率比较低的时候，有可能会把它们合并到其中一个数据页中，这时，另外一个数据页就空出来了，这个空出来的数据页就变成可复用的了

哪些 *** 作会造成数据空洞

我们用 delete 命令删除一条记录后，InnoDB只是把对应的数据节点标记为已删除且可复用的，这些可空着的等待使用的数据节点可以看作是一个一个的数据空洞

• 删除数据

删除数据的时候，会造成数据空洞，前面已经解释过，这里不再赘述了

• 插入数据

如果数据是按照索引大小顺序插入，这个时候数据页是紧凑的，不会出现数据空洞

如果是从索引中间插入的话，有可能会造成页分裂，分裂之后的页有可能出现数据空洞，下图就是插入导致页分裂的一个例子

![](https://img-blog.csdnimg.cn/img_convert/59316ff3f19959f

【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】

浏览器打开：qq.cn.hn/FTf 免费领取

ff4c1044cb9ca5af4.png)

如图所示，分裂前叶子页面已经满了，这时数据排列得很紧凑

现在插入了一个索引key值为15的数据，插入之后，Page1 页分裂成了上图中 Page1，Page2两个页面

分裂之后，Page1 页面出现了两个空洞，这两个数据节点是可复用的，而 Page2页面刚好满了

• 更新数据

更新数据可以看成先删除再插入，也是有可能造成数据空洞