从表中删除数据行后 MySQLInnoDB 不释放磁盘空间

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

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

我的情况很糟糕; 这个应用程序在大约50个不同的位置运行，现在几乎所有的位置都出现了磁盘空间不足的问题。

189589 次浏览
小开
最佳答案

MySQL 不会减少 ibdata1的大小。永远不会。即使您使用 optimize table从已删除的记录中释放所使用的空间，以后也会重用它。

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

小开

我自己也有同样的问题。

发生的情况是，即使删除数据库，inodb 仍然不会释放磁盘空间。我必须导出，停止 mysql，手动删除文件，启动 mysql，创建数据库和用户，然后导入。感谢上帝，我只有200MB 的行，但它节省了250GB 的创新数据库文件。

故意失败。

小开

如果不使用 Inodb _ file _ per _ table，回收磁盘空间是可能的，但是相当繁琐，并且需要大量的停机时间。

如何是相当深入的-但我粘贴了下面的相关部分。

一定要在转储中保留模式的一个副本。

目前，无法从系统表空间中删除数据文件。 若要减小系统表空间大小，请使用以下过程:

使用 mysqldump 转储所有 InnoDB 表。

停止服务器。

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

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

配置新的表空间。

重启服务器。

导入转储文件。

小开

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

当您为表引入分区时，表模式需要做一些修改，比如-Unique Keys、 Indexs 以包含分区列等。

小开

在从 MySQLInodb 引擎的表中删除数据之后，有几种方法可以回收磁盘空间

如果您不从一开始就使用 inodb _ file _ per _ table，那么转储所有数据、删除所有文件、重新创建数据库并再次导入数据是唯一的方法(请检查上面 FlipMcF 的答案)

如果您正在使用 inodb _ file _ per _ table，您可以尝试

  1. 如果您可以删除所有的数据截断命令将删除数据和回收磁盘空间为您。
  2. Altertable 命令将删除并重新创建表，以便它可以回收磁盘空间。因此，在删除数据之后，运行 ALTER TABLE，不做任何改变来释放硬盘(即: TABLE TBL _ A has charset uf8，在删除数据之后运行 ALTER TABLE TBL _ A charset utf8-> 这个命令不会改变表中的任何内容，但是它会让 mysql 重新创建表并重新获得磁盘空间
  3. 创建类似于 TBL _ A 的 TBL _ B。将要从 TBL _ A 保存的选择数据插入到 TBL _ B 中。删除 TBL _ A，并将 TBL _ B 重命名为 TBL _ A。如果 TBL _ A 和需要删除的数据很大，这种方法是非常有效的(在 MySQL injodb 中删除命令的性能非常差)
小开

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

  • 我优化了剩下的所有数据库。
  • 我在服务上重启了我的电脑和 MySQL (Windows + r —— > services.msc)

仅此而已:)

小开

几年前，我在 mysql5.7版本上也遇到了同样的问题，ibdata1占用了150Gb。 所以我添加了撤消表空间

带上 Mysqldump 备份
停止 mysql 服务
从数据目录中删除所有数据
在当前 my.cnf < br >

中添加如下撤消表空间参数 
 #undo tablespace
innodb_undo_directory =  /var/lib/mysql/
innodb_rollback_segments = 128
innodb_undo_tablespaces = 3
innodb_undo_logs = 128
innodb_max_undo_log_size=1G
innodb_undo_log_truncate = ON

启动 mysql 服务
存储 mysqldump 备份

问题解决了！

小开

今天遇到了这个问题(这个问题最初提出11年后) ，是 能够通过删除表并重新创建来修复它。我没有重新安装数据库或转储和恢复，修改存储，更改表空间等-没有这些。

我使用的是 InnoDB，而不是 Innovdb _ file _ per _ table，所以即使我从表中删除了900K 行，DB 的大小也不会改变。所以我放弃了表，重新创建了它。

在我的例子中，我的表被清空为零行，因此删除表对我来说很容易，但是要保留我运行的结构

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

接下来

drop table myoldtable;

这使我的数据库大小从5G 下降到400MB

小开

如果优化不能解决你的问题，试试:

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

或者

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

我找到的捷径是:

ALTER TABLE YOURTABLE ENGINE=InnoDB

在截断或删除未提供的记录之后，我运行了这个命令，表空间收缩了。

这是 Shlomi Noach 写的一篇很好的文章，我在其中发现了(这篇文章以及更多相关信息)。

感谢 Shlomi Noach。希望能有所帮助。以防万一我也要把它粘贴到这里:

在使用 InnoDB 时，有两种方法来管理表空间存储:

将所有内容放在一个大文件中(可选分割)。 每个表有一个文件。 我将讨论这两个选项的优点和缺点，并努力说服人们相信植入式文件表是更好的选择。

单个表空间

将所有内容放在一个大文件中意味着所有方案中的所有表和索引都在该文件中“混合”在一起。

这允许有以下优秀的属性: 可以在不同的表和不同的方案之间共享空闲空间。因此，如果从日志表中清除许多行，现在未使用的空间可以被任何其他表的新行占用。

这个很好的属性也可以转化为一个不那么好的属性: 数据可以在整个表空间中被大大分割。

InnoDB 表空间的一个恼人的特性是它们从不收缩。因此，在从日志表中清除这些行之后，表空间文件(通常为 ibdata1)仍然保持相同的存储。它不会将存储释放到文件系统。

我不止一次看到某些表是如何不被监视的，一直增长直到磁盘空间达到90% ，短信通知开始响个不停。

这种情况下没什么可做的。人们总是可以清理行。当然，InnoDB 会重用这个空间。但是，拥有一个消耗大约80-90% 磁盘空间的文件是一个性能灾难。这意味着磁盘指针需要移动很远的距离。整体磁盘性能非常低。

解决这个问题的最佳方法是设置一个新的从服务器(在清除行之后) ，并将数据转储到该从服务器中。

InnoDB 热备份

有趣的是，ibbackup 实用程序将按原样复制表空间文件。如果它是120GB，其中只有30GB 被使用，您仍然可以得到120GB 的备份和恢复。

Mysqldump mk 并行转储

如果只使用原始机器，mysqldump 将是最佳选择。假设您只使用 InnoDB，那么使用-single-event 转储就可以完成这项工作。或者可以利用 mk-並-dump 来加快速度(取决于 dump 方法和可访问性需求，注意锁定)。

Inodb _ file _ per _ table

设置了这个参数之后，每个表都会创建一个. ibd 文件:

表空间不在不同的表之间共享，当然也不在不同的方案之间共享。 每个文件都被视为自己的表空间。 同样，表空间的大小永远不会减少。 可以重新获得每个表空间的空间。 等等，最后两个看起来矛盾，不是吗? 我们来解释一下。

在我们的日志表示例中，我们清除了许多行(删除了多达90GB 的数据)。那个。Ibd 文件不收缩。但我们可以做到:

日志引擎 = InnoDB

将会发生的情况是创建一个新的临时文件，并将表重新构建到该文件中。只将现有数据添加到新表中。完成后，将删除原始表，并将新表重命名为原始表。

当然，这需要很长时间，在此期间表是完全锁定的: 不允许写和不允许读。但它仍然允许我们重新获得磁盘空间。

使用新的 InnoDB 插件，在执行 TRUNCATETABLE 日志语句时也会恢复磁盘空间。

碎片化并不像在单个表空间中那样糟糕: 数据被限制在较小文件的边界内。

监察

智能表的另一个优点是可以在文件系统级监视表的大小。您不需要访问 MySQL，也不需要使用 SHOW TABLE STATUS 或查询 INFORATION _ SCHEMA。您只需查找 MySQL 数据目录(和子目录)下最大的10个文件，并监视它们的大小。您可以看到哪个表增长最快。

后援

最后，还不能通过复制。IBD 文件。但希望工作能朝这个方向进行。

小开

我也有同样的问题。我删除了 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空间可用。


提交答案