从表中删除数据行后，MySQL InnoDB不会释放磁盘空间 [英] MySQL InnoDB not releasing disk space after deleting data rows from table

问题描述

我有一个使用InnoDB存储引擎的MySQL表；它包含大约2M数据行.当我从表中删除数据行时，它没有释放分配的磁盘空间.运行optimize table命令后，ibdata1文件的大小也没有减小.

I have one MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I deleted data rows from the table, it did not release allocated disk space. Nor did the size of the ibdata1 file reduce after running the optimize table command.

有什么办法可以从MySQL回收磁盘空间?

Is there any way to reclaim disk space from MySQL?

我处境很糟；该应用程序在大约50个不同的位置运行，现在几乎所有位置都出现磁盘空间不足的问题.

I am in a bad situation; this application is running in about 50 different locations and now problem of low disk space is appearing at almost all of them.

推荐答案

MySQL不会减小ibdata1的大小.曾经.即使使用optimize table从已删除的记录中释放已使用的空间，它也会在以后重新使用.

MySQL doesn't reduce the size of ibdata1. Ever. Even if you use optimize table to free the space used from deleted records, it will reuse it later.

另一种方法是将服务器配置为使用 innodb_file_per_table ，但这需要备份，删除数据库并还原.积极的一面是，该表的.ibd文件在optimize table之后被减小.

An alternative is to configure the server to use innodb_file_per_table, but this will require a backup, drop database and restore. The positive side is that the .ibd file for the table is reduced after an optimize table.

这篇关于从表中删除数据行后，MySQL InnoDB不会释放磁盘空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！