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

清疚 2022-09-02 05:25 105阅读 0赞

![a5215e8c77a92c7240d41184f3aa5696.png][]

来源：toutiao.com/i6935264754059477542

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

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

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

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

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

## Mysql数据结构 ##

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

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

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

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

![9e097bb50071a20ab6a8237a867c4370.png][]

为什么delete表数据，磁盘空间却还是被占用

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

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

## 总结 ##

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

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

PS：如果觉得我的分享不错，欢迎大家随手点赞、在看。
    ▽加我微信,交个朋友
    长按/扫码添加↑↑↑
    1、这次专治Docker疑难杂症2、微服务架构实施原理详解3、盘点 HashMap 源码中的那些优雅的设计！4、面试官欺负人：new Object()到底占用几个字节？5、这三种单点登录的方式你都Get吗?6、面试官问：缓存与数据库一致性如何解决？先操作数据库，还是缓存？

**看完本文有收获？请转发分享给更多人**

请长按二维码，关注 Java大数据修炼之道.

**推荐程序员必备公众号 **

**▼**

> **Java大数据修炼之道**
> 
> 公众号：
> 
> gh\_9119f24d3793
> 
> **推荐理由：**
> 
> 在这里，我们分享程序员相关技术，职场生活，行业热点资讯。不定期还会分享IT趣文和趣图。这里属于我们程序员自己的生活，工作和娱乐空间。
> 
>  ▼长按下方↓↓↓二维码识别关注
> 
> ####  ####
> 
> 关注 java大数据修炼之道
> 
>     每天学习java技术你想学的Java知识这里都有**![87595228153d663028493838f069c0f8.png][]**
> 
> ![5ceda7e6f96eb80034f7d2423f954d69.png][]

![4aafb8eece0dcb842983f73feeac1bad.png][]

点「在看」的人都涨薪了哦

文章有帮助的话，在看，转发吧。
    谢谢支持哟 (*^__^*)

[a5215e8c77a92c7240d41184f3aa5696.png]: /images/20220829/3f5e77f2328547348b0d30c959ee5ea6.png
[9e097bb50071a20ab6a8237a867c4370.png]: /images/20220829/9c81888678d049cc84d02d2d9a7cce08.png
[87595228153d663028493838f069c0f8.png]: /images/20220829/5c4f1ca04b2849529fdf9c6b71fdf0f3.png
[5ceda7e6f96eb80034f7d2423f954d69.png]: /images/20220829/e39a40c99a9a48829b5ccf2cfd18f77a.png
[4aafb8eece0dcb842983f73feeac1bad.png]: /images/20220829/0cb5baac26cf43b7877802b13a9d2045.png