MySQL InnoDB在从表中删除数据行后没有释放磁盘空间

Question

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

有没有办法从MySQL中回收磁盘空间？

我现在处于困境中；这个应用程序正在大约50个不同的位置运行，现在几乎所有位置都出现了低磁盘空间的问题。

- Sumit Deo

---

可能是 https://dev59.com/YWbWa4cB1Zd3GeqPaMR5 的重复问题（但这个问题先出现在这里）。 - FlipMcF

“optimize table” 命令运行后，ibdata1 文件的大小并未减小。这是因为您的 innodb_file_per_table 参数被关闭了。好消息是，在最近版本的 MySQL 中，默认情况下此选项已经打开。 - Accountant م

我运行了"optimize table xxxx"命令，但是收到了消息"Table does not support optimize, doing recreate + analyze instead."。之后在shell上运行了"du -h /var/lib/mysql"命令，我发现数据库已经缩小了。 - user1097111

Answer 1

MySQL永远不会减小ibdata1的大小。即使您使用optimize table来释放已删除记录占用的空间，它以后也会重新使用它。另一种选择是配置服务器使用innodb_file_per_table，但这将需要备份、删除数据库和恢复。积极的一面是，在optimize table之后，表的.ibd文件会减小。

- Leonel Martins

---

为了利用[InnoDB文件每表]功能，对于现有表，您可以打开file-per-table设置，并在现有表上运行ALTER TABLE t ENGINE=INNODB。 这意味着您可以打开此功能，使用ALTER TABLE命令“转换”现有表以使用单独的InnoDB文件，然后优化表以缩小其大小。 但是，完成后，您必须找出如何删除（巨大的）源InnoDB文件... - Josh

我猜这基本上回答了问题，但我认为大多数搜索这个主题的人都在寻找实际收缩/回收空间的过程，而这个答案没有提供。 - Manachi

@Manachi 这个过程是：“配置服务器使用innodb_file_per_table”，备份服务器，“删除数据库”，停止mysql，删除.ibd文件，启动服务器并恢复数据库。在MySQL 5.5+中，您可以使用Josh所说的方法，在更改所有表之后，停止服务器，删除巨大的.ibd文件，然后再次启动它。 - Leonel Martins

由于MySQL 8.0的情况已经改变，建议对表进行优化并重新启动MySQL以释放磁盘空间。请不要将此回答视为机器翻译，而是一个专业、流畅、优雅的翻译内容。 - Pradyut Bhattacharya

@PradyutBhattacharya，那么这个问题就不会发生了。所以没有必要给这个答案投反对票，因为它是问题的解决方案。 - Leonel Martins

Answer 2

我自己遇到了同样的问题。

发生的情况是，即使你删除了数据库，InnoDB 仍然不会释放磁盘空间。我不得不导出数据、停止 MySQL、手动删除文件、启动 MySQL、创建数据库和用户，然后再导入数据。谢天谢地，我只有 200MB 的数据行，但这节省了 250GB 的 InnoDB 文件。

失败是设计上的问题。

- gilm

---

没错，那肯定是失败了。 - trusktr

MySql 5.5也存在同样的问题：我运行了"optimize table"来减少一个28GB的表所占用的磁盘空间。该操作可能尝试创建原始表的优化副本，并使用了分区上的所有空间。现在，"optimize table"失败了，即使我删除了整个数据库，分区仍然没有空间...非常令人失望。 - acorello

4年多后我在MySQL中遇到了同样的问题。MS SQL也相似：https://dba.stackexchange.com/questions/47310/shrinkfile-best-practices-and-experience - Csaba Toth

Answer 3

如果您不使用innodb_file_per_table，回收磁盘空间是可能的，但相当繁琐，并需要大量停机时间。

如何操作非常详细 - 但我复制了下面的相关部分。

确保在转储中也保留模式的副本。

目前，您无法从系统表空间中删除数据文件。 要减小系统表空间大小，请遵循以下过程：

使用mysqldump转储所有InnoDB表。

停止服务器。

删除所有现有的表空间文件，包括ibdata和   ib_log文件。如果要备份信息，   然后在移除MySQL安装中的文件之前将所有ib *文件复制到另一个位置。

删除任何InnoDB表的.frm文件。

配置新的表空间。

重新启动服务器。

导入转储文件。

- FlipMcF

---

感谢您包含这些步骤 - 据我所知，“如何”链接不再包含此信息。 - aland

Answer 4

十年后，我又遇到了同样的问题。我用以下方法解决了它：

• 我优化了所有剩余的数据库。
• 我重启了电脑和MySQL服务（Windows+r --> services.msc）。

就是这样 :)

- Matheus Douglas

---

好的，只需要执行 "OPTIMIZE TABLE tbl_name;" 命令并重新启动 MySQL。 - Vit

我不知道为什么这个没有得到足够的投票。显然，这应该是最佳答案。 - Gayan Kavirathne

很遗憾，这不起作用 - 在看到这个答案后尝试了一下，它没有释放任何文件大小。 - Louis M.

Answer 5

我找到的最短的方法是：

ALTER TABLE YOURTABLE ENGINE=InnoDB

删除不需要的记录后，我运行了这个命令，表空间就会缩小。

Shlomi Noach写了一篇很好的文章，我在那里找到了（以及更多的）信息。

感谢Shlomi Noach。希望它能有所帮助。以防万一，我也将其粘贴在此处：

在使用InnoDB时，您有两种管理表空间存储的方式：

把所有东西都放在一个大文件中（可选择分割）。 每个表一个文件。 我将讨论两个选项的优缺点，并力图说服innodb_file_per_table是更可取的。

单个表空间

将所有内容放在一个大文件中意味着来自所有方案的所有表和索引都混合在同一个文件中。

这可以实现以下良好特性：空闲空间可以在不同的表和不同的方案之间共享。因此，如果我从日志表中清除许多行，则现在未使用的空间可以被任何其他表的新行占用。

这个良好特性也转化为一个不太好的特性：数据可能在整个表空间中被大量碎片化。

InnoDB表空间的一个烦人特性是它们永远不会缩小。因此，在从日志表中清除这些行后，表空间文件（通常为ibdata1）仍然保持相同的存储。它不会将存储释放给文件系统。

我曾多次看到某些表被忽略，直到磁盘空间达到90％并且SMS通知开始响起。

在这种情况下，有很少可做的事情。好吧，可以始终清除行。当然，InnoDB会重复使用空间。但是拥有一个消耗80-90％磁盘空间的文件是一场性能灾难。这意味着磁盘针需要移动大距离。整体磁盘性能非常低。

解决此问题的最佳方法是设置新的从属（在清除行之后），并将数据转储到该从属中。

InnoDB热备份

有趣的是，ibbackup实用程序将按原样复制表空间文件。如果它的大小为120GB，其中只使用了30GB，则您仍将获得120GB的备份和恢复。

mysqldump，mk-parallel-dump

如果您只有原始机器要处理，那么mysqldump将是您最好的选择。假设您只使用InnoDB，则使用–single-transaction的转储将完成工作。或者您可以利用mk-parallel-dump加快速度（根据转储方法和可访问性需求，请注意锁定）。

innodb_file_per_table

设置此参数后，每个表都会创建一个.ibd文件。我们得到了这个：

表空间不在不同的表之间共享，当然也不在不同的方案之间共享。 每个文件都被视为其自己的表空间。 再次强调，表空间永远不会减小。 可以重新获得每个表空间的空间。 等等。最后两个看起来

- Skeletor

---

ALTER TABLE YOURTABLE ENGINE=InnoDB这个语句似乎对表的磁盘空间没有影响，即使在删除行、运行此语句和重启MySQL（8）之后也是如此。 - undefined

Answer 6

今天我遇到了这个问题（11年后提出这个问题），通过删除表并重新创建表，我成功解决了这个问题。我不需要重新安装数据库或备份和恢复数据，修改存储空间，更改表空间等等 - 这些操作都不需要。
我使用的是InnoDB引擎，但没有设置innodb_file_per_table选项，因此即使我从表中删除了90万行，数据库大小也没有改变。所以我删除了表并重新创建了它。
在我的情况下，我的表已被清空为零行，因此很容易删除该表，但为了保留其结构，我运行了以下命令。

create table mynewtable as select * from myoldtable where 1=2;

接下来是

drop table myoldtable;

这使我的数据库大小从5G降至400MB。

- SuhasM

---

欢迎来到Stack Overflow。数据库的本质是由于已删除的记录，数据不会返回系统。从我的研究来看，INNODB没有重新获取资源命令或类似命令，像一些其他的数据库一样。但在您的情况下，我会尝试TRUNCATE TABLE myoldtable; ... 我相信这将是一个更简单的解决方案。 - Dennis

谢谢 - 我忘记了TRUNCATE。我在Oracle中广泛使用它，但没有想到在这里尝试它。将其存档以备将来参考 :) - SuhasM

TRUNCATE TABLE在我的情况下也不会释放磁盘空间。 - XuDing

我尝试了这个，但仍然无法释放我的数据 :( - Louis M.

SQL语句“create table mynewtable as select * from myoldtable where 1=2;”并不能创建完全相同的表，主键等信息会丢失。最好使用“show create table myoldtable;”命令查看表的结构，在此基础上创建新表。 - mark

Answer 7

对于InnoDB引擎，您可以尝试以下方法：

1. 根据this文章所述：

   ALTER TABLE tbl_name ENGINE=INNODB;

2. 然后在information_schema中刷新有关表大小和索引的信息：

   ANALYZE TABLE tbl_name;

- sNICkerssss

---

在我的MySQL 8.0.33版本中，只需要执行ANALYZE TABLE tbl_name;命令就能很好地完成任务，而不需要执行ALTER TABLE...命令。 - undefined

Answer 8

如果优化无法解决您的问题，请尝试：

ALTER TABLE tbl_name ENGINE=INNODB, ALGORITHM=INPLACE, LOCK=NONE;

或者

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

- Wellington1993

Answer 9

另一种解决空间回收问题的方法是，在表内创建多个分区-基于范围、基于值的分区，只需删除/截断分区即可回收空间，这将释放特定分区中存储的整个数据使用的空间。

当您为表引入分区时，需要对表模式进行一些更改，例如-包括分区列的唯一键、索引等。

- Anand Immannavar

Answer 10

我遇到了同样的问题。我从logging_event表中删除了所有数据。但是，logging_event.ipd仍然占用超过15GB的数据。
所以，我按照以下步骤解决了这个问题。

1. 从现有表创建新表。
   CREATE TABLE logging_event_new like logging_event;

2. 删除实际表。
   DROP TABLE logging_event;

3. 将新表重命名为旧表。
   RENAME TABLE logging_event_new to logging_event;

这对我起作用了。
现在，我有超过15GB的可用空间。

- Sarwan Kumar