Mysql InnoDBdel数据后不会释放空间

我不是女神ヾ 2022-12-24 12:57 141阅读 0赞

Innodb数据库对于已经删除的数据只是标记为删除，并不真正释放所占用的磁盘空间，这就导致InnoDB数据库文件不断增长。

如果在创建数据库的时候设置innodb\_file\_per\_table=1，这样InnoDB会对每个表创建一个数据文件，然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。

运行OPTIMIZE TABLE 表名后，虽然最后会报Table does not support optimize, doing recreate + analyze instead，但其实已经成功了：）

optimize table 的本质是 ALTER TABLE xxx ENGINE = InnoDB;

**1、参数介绍**

在 Innodb 存储引擎中，innodb\_file\_per\_table 参数是用来控制表数据的存储方式的。

当参数为 OFF 的时候，所有数据都存放于默认路径下名为 ibdata\* 的共享表空间里，即将数据库所有的表数据及索引文件存放到一个文件中。在删除数据表的时候，ibdata\* 文件不会自动收缩。

当参数为 ON 的时候，每一个表都将存储在一个以 .ibd 为后缀的文件中。这样每个表都有了自己独立的表空间，通过 drop table 命令就可以将表空间进行回收。

从 Mysql 5.6.6 版本开始，innodb\_file\_per\_table 默认为 ON 状态。

**2、参数设置**

通过 show variables like '%per\_table%' 命令，可以查看 innodb\_file\_per\_table 参数的当前状态：

![0302d672beea29de5c615d11a5b2afe6.png][]

如果想修改参数的状态，可通过 SET GLOBAL 动态地修改为 ON 或 OFF，也可以在 my.cnf 中做永久性修改。需要注意的是，在 my.cnf 中修改后生效的话需要重启 mysqld 服务。

**疑问：如果之前参数为 OFF 状态，设置为 ON 状态后，表空间如何分配？**

答案是仅对后续操作生效。

什么意思呢？修改前的数据还维持原状，也就是说之前的数据继续存放于 ibdata\* 文件中，修改后的使用独立表空间。

**所以建议在开始就将该参数设置为 ON 状态。**

在这之前要先介绍下 Innodb 存储数据所用的 B+ 树结构，画个图你理解下：

![ff1c1971f062db0102161be96b14084b.png][]

在图中，P 代表一页数据，R 代表一行数据。

假设我们要删掉 R2 这条记录，InnoDB 引擎只会将其标记为删除状态，并不会真正把这行数据所占的空间释放掉，也就是说这个坑位还留着。如果后续所插入的数据在 R1 与 R3 之间的话，这个空间是可以被使用上的。

假设我们恰好删除了 R1、R2、R3 这三条记录，也就是说 P1 这一页的数据都被删掉了，那么 P1 所在的空间都会被标记为可复用。如果插入的数据需要使用新页的话，P1 的坑位就可以被利用起来了。

那么你可能会问了，我插入的数据恰好巧妙的避开了这些位置呢。那我还能说啥，骚呗。这样会造成很多空间被浪费，如果删除大量的数据的话，被浪费的空间也会是巨大的。

optimize table 的本质是 ALTER TABLE xxx ENGINE = InnoDB;

[0302d672beea29de5c615d11a5b2afe6.png]: /images/20221120/49dedc88385b4910b9d77eab7de03ba6.png
[ff1c1971f062db0102161be96b14084b.png]: https://img-blog.csdnimg.cn/img_convert/ff1c1971f062db0102161be96b14084b.png

Mysql InnoDBdel数据后不会释放空间

发表评论取消回复

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

相关阅读

相关 mysql删除数据后多久释放空间

相关 mysql delete后怎么释放表空间

相关 mysql清除表中数据后优化磁盘（释放磁盘空间）

相关 MongoDB数据删除后的磁盘空间释放

相关疑难杂症 | rm删除后df磁盘空间不释放

相关 MySQL删除数据后磁盘空间的释放总览

相关 Mysql InnoDBdel数据后不会释放空间

相关 Oracle 删除数据后释放数据文件所占磁盘空间

相关 MongoDB数据删除后的磁盘空间释放

相关 Mysql空间释放