为什么MySQL 删除表数据磁盘空间还一直被占用

最近有个上位机获取下位机上报数据的项目，由于上报频率比较频繁且数据量大，导致数据增长过快，磁盘占用多。

为了节约成本，定期进行数据备份，并通过delete删除表记录。

明明已经执行了delete，可表文件的大小却没减小，令人费解

项目中使用mysql作为数据库，对于表来说，一般为表结构和表数据。表结构占用空间都是比较小的，一般都是表数据占用的空间。

当我们使用 delete删除数据时，确实删除了表中的数据记录，但查看表文件大小却没什么变化。

1、mysql数据结构

凡是使用过mysql，对b+树肯定是有所耳闻的，mysql innodb 中采用了 b+ 树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。因此在删除数据时，会有两种情况：

删除数据页中的某些记录
删除整个数据页的内容

2、表文件大小未更改和mysql设计有关

比如想要删除 r4 这条记录：

为什么MySQL 删除表数据磁盘空间还一直被占用

innodb 直接将 r4 这条记录标记为删除，称为可复用的位置。如果之后要插入 id 在 300 到 700 间的记录时，就会复用该位置。

由此可见，磁盘文件的大小并不会减少。

通用删除整页数据也将记录标记删除，数据就复用用该位置，与删除默写记录不同的是，删除整页记录，当后来插入的数据不在原来的范围时，都可以复用位置，而如果只是删除默写记录，是需要插入数据符合删除记录位置的时候才能复用。

因此，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。

3、那怎么才能让表大小变小

delete只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间，可以使用optimize table来回收未使用的空间，并整理数据文件的碎片。

				?

									optimize table 表名;

注意：optimize table只对myisam, bdb和innodb表起作用。

另外，也可以执行通过alter table重建表

				?

									alter table 表名 engine=innodb

有人会问optimize table和alter table有什么区别？

alter table t engine = innodb（也就是recreate），而 optimize table t 等于 recreate+analyze

4、online ddl

最后，再说一下online ddl，dba的日常工作肯定有一项是ddl变更，ddl变更会锁表，这个可以说是dba心中永远的痛，特别是执行ddl变更，导致库上大量线程处于“waiting for meta data lock”状态的时候。因此在 5.6 版本后引入了 online ddl。

online ddl推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。online方式与前两种方式相比，不仅可以读，还可以支持写操作。

执行online ddl语句的时候，使用algorithm和lock关键字，这两个关键字在我们的ddl语句的最后面，用逗号隔开即可。示例如下：

				?

									alter table tbl_name add column col_name col_type, algorithm=inplace, lock=none;

algorithm选项

inplace：替换：直接在原表上面执行ddl的操作。
copy：复制：使用一种临时表的方式，克隆出一个临时表，在临时表上执行ddl，然后再把数据导入到临时表中，在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的操作。执行期间，表不允许dml的操作。
default：默认方式，有mysql自己选择，优先使用inplace的方式。
lock选项
share：共享锁，执行ddl的表可以读，但是不可以写。
none：没有任何限制，执行ddl的表可读可写。
exclusive：排它锁，执行ddl的表不可以读，也不可以写。
default：默认值，也就是在ddl语句中不指定lock子句的时候使用的默认值。如果指定lock的值为

default，那就是交给mysql子句去觉得锁还是不锁表。不建议使用，如果你确定你的ddl语句不会锁表，你可以不指定lock或者指定它的值为default，否则建议指定它的锁类型。
执行ddl操作时，algorithm选项可以不指定，这时候mysql按照instant、inplace、copy的顺序自动选择合适的模式。也可以指定algorithm=default，也是同样的效果。如果指定了algorithm选项，但不支持的话，会直接报错。