Mysql删除表数据,表文件大小不变

首先明确一个概念，innodb表包含两部分,表结构定义和数据，Mysql8.0以前表结构定义存放在.frm为后缀的文件里，而Mysql8.0版本以后允许表结构定义放到系统数据表中，因为表结构定义占用的空间很小，因此我今天主要说是表数据，

日常开发中，当我们删除一个表的的数据的时候，发现表空间大小并不会变小,我们要知道为什么会发生这样神奇的事,

参数innodb_file_per_table

表数据可以存在共享表空间里，也可以在单独的文件中，这个行为由参数innodb_file_per_table控制，

1. 这个参数为OFF的时候，说明表的数据存放在系统共享表空间，也就是跟数据字典放在一起
2. 如果这个参数是ON的时候，说明表的数据放到单独的文件中，Mysql5.6.6以后默认就是ON

无论使用哪个版本我们都建议把这个值设置成ON,单独放到一个文件，方便管理,当我们不需要这个表的时候，通过drop table 就可以直接删除这个文件。如果放到系统共享表空间中，即使删除掉了，空间也就是不会回收的

假如我删除500这个数据，innodb只会把这个记录标记为删除，如果之后要插入400的时候，就会直接复用这个位置，但是磁盘的文件并不变小,当然我如果删除了在跟个pageA的数据，当然也是被复用的,但是数据页的复用和记录的复用是不一样的

记录的复用,只限于符合条件的数据，正如上面的例子，但是如果插入一个800的数据，就不能复用这个位置了，而数据页可以复用任何位置，如上图为例，把pageA的数据全部删除，PageA页就会被标记为可复用，这个时候插入一条id=50的时候，要创建新的数据页的时候，就会复用pageA,

现在我们知道delete 命令只会把记录的位置或数据页标记为可复用，但是磁盘的文件大小不会变小，也就是说delete并不会回收表空间，这个可以进行复用，而没有使用的空间，看起来就像空洞，

实际上不止删除会造成空洞，插入数据也会.

如果数据按照索引递增顺序插入，那么索引是紧凑，但是如果数据是随机的，就可能存在数据页的分裂，

我们看到当先插入一个数据的时候，此时要申请一个数据页pageB,来保存数据了,页分裂完成后，PageA尾部就留下了空洞，另外更新索引的值，可以理解为删除一个数据，新增一条数据，不难理解这样也是会导致空洞的.

总之，经过大量的增删改的表都可能存在空洞，所以，如果能把这些空洞去掉，就能达到收缩表空间的目的，而重建表，就可以达到这样的目的

重建表

试想一下，如果我们需要去掉这些空洞，如何做呢，当然你可以建立一个和A表一样表B,把数据A的数据复制到表B,

由于表B是新建的表,所以表B不存在空洞，显然表B的主键索引更紧凑，数据页利用率也高，如果我们建立一个临时表B，把数据从表A导入表B的操作完成后，用表B替换A,效果上就起到了收缩表A的作用.

我们可以使用下面命令重建表，在mysql5.5版本之前，这个命令的执行流程跟我们前面描述的差不多，区别就是这个临时表B不需要手动创建，MySql会自动完成转存数据，就换表明，删除旧表的操作

显然上图中最花时间的步骤是往临时表插入数据的过程，如果整个过程中，有新的数据写入是不被允许的，这个就会造成数据丢失，表A不会有数据的更新，这个DDL不是Online的

但是在mysql5.6版本开始引入Online DDL，这个操作流程做了优化，如下面流程

1. 建立一个临时表,扫描表A主键的所有数据页
2. 用数据页中表A记录生成B+树，存储到临时文件中，
3. 生成临时文件的过程，将所有对表A的操作应用到日志文件中
4. 临时文件生成后，将日志文件应用到临时文件中，得到一个逻辑数据和表A相同的数据文件
5. 用临时文件替换表A的数据文件

可以看到上图和之前的不同之处在于，由于日志文件激励和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表A增删改，这就是Online DDL的来源

之前我们介绍过在执行DDL的时候会先拿到MDL的写锁，但是这个写锁在真正拷贝数据之前就会退化成读锁，因为这样才能实现Online DDL,不会阻塞增删改的操作.

但是为什么不直接接触锁呢，那是因为防止其他线程对这个表进行DDL操作，

而对于一个达标来说，Online DDL最耗时的过程就是拷贝数据，这个步骤执行期间可以增删改，所以相对于真个DDL锁的时间非常短，对业务来说，可以容忍，当然对于大表的重建表，这个操作是很消耗IO和CPU资源的，生产环境要谨慎执行，

Online 和inplace

我们看到第一张状态的图是把表A的数据导出来放到temp_table，这个是临时表，在server层建立的

但是，第二章状态的图是把表A的数据放到临时文件temo_file，这个临时文件是innodb的内部创建出来的,整个DDL过程都在InnoDB内部完成的，对于server层来说，没有把数据挪到临时表，是一个原地操作，这就是inplace名称的来源，

但是我们发现在重建表的过程中，inplace和online好像是一个意思，其实他们是不一样的，比如如果我要在innodb中给一个字段加全文多音，写法是

alter table t add fulltext(file_name)

这个过程是inplace的，但是会阻塞增删改操作，是非Online的，

他们的关系如下

1. DDL的过程如果是Online,就一定是inplace的
2. 反过来未必,也就是inplace的DDL,有可能不是online,截止到Mysql8.0,添加全文索引和空间索引就属于这种情况，

这个说明一个重建表的区别

• mysql5.6版本开始alter table t engin=innodb(recreate),默认就是第二张状态图描述的
• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁
• aptimize table t =recreate+analyze