面试官问：MySQL 删除表数据，磁盘空间还一直被占用，为什么?

谁践踏了优雅 2022-08-28 01:46 120阅读 0赞

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

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

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

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

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

**Mysql数据结构**

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

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

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

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

![图片][fc2ac47b809f7d4f95391e5a0b3d8b79.png]

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

**总结**

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

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

[fc2ac47b809f7d4f95391e5a0b3d8b79.png]: /images/20220828/5d72416c878c44e3af0017118e15ab08.png

发表评论取消回复

表情：

评论列表（有 0 条评论，120人围观）

还没有评论，来说两句吧...

相关阅读

相关【Redis】数据被删除，内存占用还这么大？

【Redis】数据被删除，内存占用还这么大？ > 操作系统分配给 Redis 的内存有 6GB，通过指标 used\_memory\_human 发现存储数据只使用了 4

拼搏现实的明天。/ 2024年03月17日 14:09/ 0 赞/ 8 阅读

相关自以为不会被问到的面试题 --》Redis和Memcatch的选型 --然而面试官还一直问到底

背景：因为一直在用Redis，也感觉挺方便的，最近小编准备换工作，准备面试前，也有想到为啥要用redis，小编之前没有用过Memcach，自认为这么古老的应该没人问吧，

小咪咪/ 2023年02月09日 13:11/ 0 赞/ 7 阅读

相关面试官问：为什么Redis单线程架构还那么快

1 单线程模型 Redis客户端对服务端的每次调用都经历了发送命令，执行命令，返回结果三个过程。其中执行命令阶段，由于Redis是单线程来处理命令的，所有每一条到达服务端的

曾经终败给现在/ 2022年12月27日 07:59/ 0 赞/ 151 阅读

相关面试官不讲武德问我：为什么MySQL不建议使用delete删除数据？

> 有情怀，有干货，微信搜索【三太子敖丙】关注这个不一样的程序员。 > > 本文 GitHub [https://github.com/JavaFamily][https_g

快来打我*/ 2022年12月22日 00:45/ 0 赞/ 130 阅读

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

![54008f53eb6e8f3d49c4417d3ab5a9f6.png][] 来源：www.toutiao.com/i6935264754059477542 最近有个

分手后的思念是犯贱/ 2022年10月14日 05:42/ 0 赞/ 131 阅读

相关面试官问：MySQL 删除表数据，磁盘空间还一直被占用，为什么?

来源：[www.toutiao.com/i6935264754059477542/][www.toutiao.com_i6935264754059477542] 最近有个上位

àì夳堔傛蜴生んèń/ 2022年09月16日 01:28/ 0 赞/ 113 阅读

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

![a5215e8c77a92c7240d41184f3aa5696.png][] 来源：toutiao.com/i6935264754059477542 最近有个上位机获

清疚/ 2022年09月02日 05:25/ 0 赞/ 105 阅读

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

![0D5F937FE12312312D81F17F.jpg][] 程序员的成长之路互联网/程序员/技术/资料共享 [关注][Link 1] 阅读本文大概需要 3.

野性酷女/ 2022年08月30日 11:25/ 0 赞/ 111 阅读

相关面试官问：MySQL 删除表数据，磁盘空间还一直被占用，为什么?

最近有个 "上位机" 获取 "下位机" 上报数据的项目，由于上报频率比较频繁且数据量大，导致数据增长过快，磁盘占用多。为了节约成本，定期进行数据备份，并通过delete删除

谁践踏了优雅/ 2022年08月28日 01:46/ 0 赞/ 121 阅读

相关诡异的Linux磁盘空间被莫名其妙占用问题

前段时间收到kafka服务器磁盘空间满的报警邮件。在删除机器上多余的文件之后发现。有近200G的存储空间莫名奇妙的被占用了逐个目录的du 都没找到相应的文件。

约定不等于承诺〃/ 2022年08月02日 13:40/ 0 赞/ 190 阅读