数据库 delete 表数据，磁盘空间还是被一直占用，为什么?

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

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

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

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

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

MySQL数据结构

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

• 删除数据页中的某些记录

• 删除整个数据页的内容

表文件大小未更改和MySQL设计有关

比如想要删除 R4 这条记录

InnoDB 直接将 R4 这条记录标记为删除称为可复用的位置。如果之后要插入 ID 在 300 到 700 间的记录时就会复用该位置。由此可见磁盘文件的大小并不会减少。

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

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

那怎么才能让表大小变小

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

Online DDL

最后再说一下Online DDLdba的日常工作肯定有一项是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语句的最后面用逗号隔开即可。示例如下

ALTERTABLE tbl_name ADDCOLUMN 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选项但不支持的话会直接报错。

OPTIMIZE TABLE 和ALTER TABLE 表名 ENGINE=INNODB都支持Oline DDL但依旧建议在业务访问量低的时候使用

总结

delete 删除数据时其实对应的数据行并不是真正的删除仅仅是将其标记成可复用的状态所以表空间不会变小。

可以重建表的方式快速将delete数据后的表变小OPTIMIZE TABLE 或ALTER TABLE在 5.6 版本后创建表已经支持 Online 的操作但最好是在业务低峰时使用。