MySQL为什么表数据删掉一半，表文件大小不变？

最新推荐文章于 2023-04-17 11:54:25 发布

一笑杯莫停

最新推荐文章于 2023-04-17 11:54:25 发布

阅读量359

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/merryxuan/article/details/115342693

版权

9 篇文章 1 订阅

订阅专栏

今天又学了一部分知识，做个笔记。

RT先看一下数据库表的空间回收。这里只说InnoDB，InnoDB分两部分，表结构定义和数据。MySQL8.0之前表结构在.frm的文件中，8.0以后的版本允许放在系统表中，因为表结构定义占用空间其实很小。接下来重点看表数据。

数据表可以存在共享表空间，也可以存单独的文件，由innodb_file_per_table这个参数控制，参数的值有两种：

MySQL5.6.6版本以后默认设置为ON。不过建议无论在哪里都设置为ON，因为：

设置为ON后，drop table可以删除表文件，但是业务中很多情况下我们只是删除数据的一部分行，用delete，这样就会出现我们要讨论的问题：数据删除了，但空间没有被回收。

众所周知，InnoDB中的数据都是B+tree的方式组织的，如图：

假设要删除R4这个纪录，，InnoDB只会将该纪录标记为删除，而不是直接将它清除，方便以后在300-600之间出入纪录的时候复用这个位置。所以磁盘空间并没有变化。

又众所周知，InnoDB上的数据都是按页存储的，如果我们直接删除整页数据会出现什么情况？

答案是：整页数据都会被标记，可复用。这里要说但是，但是数据页的复用和部分纪录的复用是不同的。

如果R4被删除后插入一个id=400的纪录刚刚好被复用，但是如果不是呢？很明显这个位置不会被复用，如果一直不被复用呢？那磁盘就产生了碎片，造成空间浪费。

如果PageA被删除后表姐可复用，那么随表插入一条数据id=50就可复用了。系统会把利用率较小的相邻两个页的数据合并到一个页里面，将另一个页标记为可复用。

我们删除数据也不可能那么精确的删除一页数据，如果删除整张表的数据呢？那所有的数据页都会标记为可复用，但并不会回收磁盘空间。现在明白了，delete命令只是将纪录标记为可复用，而不是真正的删除，因此文件大小是不会变的。

事实上不但是delete纪录会产生碎片，insert纪录也会产生碎片。

如果是按递增的顺序插入数据，索引的位置是紧凑的，但是如果是随机插入数据，情况就有些乱了，会造成页的分裂，少量的数据被分裂到多个页中，造成磁盘碎片。更新一个索引的值，可以看成是删除一个值重新插入另一个值，这样也会产生碎片，当然我们应该刽去修改主键索引的值，更新的一般都是二级索引的值。

那么，这个问题该如何解决呢？

既然drop table命令可以回收表空间，那我们是不是可以：

可以用alter table A engine=InnoDB命令来重建表。命令执行后MySQL会自动帮你完成上述步骤。显然这个过程最耗时的就是将表中的数据规整的复制到临时表中去，而且复制的过程中要freeze整张表，这个时候要插入新的数据就会丢失。

MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。

这也就是 Online DDL 名字的来源。对于很大的表进行DDL操作的时候肯定会很耗CPU和IO资源。如果是线上，要控制操作时间，可以用github上的开源的gh-ost来做。

图2中旧表中导出的是临时表tmp_table 是在MySQLserver层创建的图3中，导出的是临时文件temp_file是在InnoDB中完成的，对于server来说并没有影响，没有临时表，这就是inplace。

图3的做法是 alter table t engine=InnoDB


alter table t engine=innodb,ALGORITHM=inplace;

图2的做法是，用法是：


alter table t engine=innodb,ALGORITHM=copy;

关注