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

MySQL InnoDB not releasing disk space after deleting data rows from table

提问by Sumit Deo

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 tablecommand.

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

Is there any way to reclaim disk space from MySQL?

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

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.

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

采纳答案by Leonel Martins

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

MySQL 不会减少 ibdata1 的大小。曾经。即使您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.

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

回答by gilm

Just had the same problem myself.

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

What happens is, that even if you drop the database, innodb will still not release disk space. I had to export, stop mysql, remove the files manually, start mysql, create database and users, and then import. Thank god I only had 200MB worth of rows, but it spared 250GB of innodb file.

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

Fail by design.

设计失败。

回答by FlipMcF

If you don't use innodb_file_per_table, reclaiming disk space is possible, but quite tedious, and requires a significant amount of downtime.

如果您不使用innodb_file_per_table，则可以回收磁盘空间，但非常乏味，并且需要大量停机时间。

The How Tois pretty in-depth - but I pasted the relevant part below.

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

Be sure to also retain a copy of your schema in your dump.

请务必在转储中保留架构的副本。

Currently, you cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:

Use mysqldump to dump all your InnoDB tables.

Stop the server.

Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.

Remove any .frm files for InnoDB tables.

Configure a new tablespace.

Restart the server.

Import the dump files.

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

使用 mysqldump 转储所有 InnoDB 表。

停止服务器。

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

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

配置一个新的表空间。

重新启动服务器。

导入转储文件。

回答by Matheus Douglas

Ten years later and I had the same problem. I solved it in the following way:

十年后，我遇到了同样的问题。我通过以下方式解决了它：

• I optimized all the databases remained.
• I restarted my computer and MySQL on services (Windows+r --> services.msc)

• 我优化了所有的数据库。
• 我在服务上重新启动了我的计算机和 MySQL (Windows+r --> services.msc)

That is all :)

就这些 ：）

回答by Anand Immannavar

Other way to solve the problem of space reclaiming is, Create multiple partitions within table - Range based, Value based partitions and just drop/truncate the partition to reclaim the space, which will release the space used by whole data stored in the particular partition.

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

There will be some changes needed in table schema when you introduce the partitioning for your table like - Unique Keys, Indexes to include partition column etc.

当您为表引入分区时，表架构将需要进行一些更改，例如 - 唯一键、包含分区列的索引等。

回答by Gajendra

Year back i also faced same problem on mysql5.7 version and ibdata1 occupied 150 Gb. so i added undo tablespaces

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

Take Mysqldump backup
Stop mysql service
Remove all data from data dir
Add below undo tablespace parameter in current my.cnf

取Mysqldump备份
停止mysql服务
从data目录中删除所有数据
在当前my.cnf中的undo表空间参数下面添加

 #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

Start mysql service
store mysqldump backup

启动mysql服务
存储mysqldump备份

Problem resolved !!

问题解决了！！

回答by Bùi ??c Khánh

There are several ways to reclaim diskspace after deleting data from table for MySQL Inodb engine

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

If you don't use innodb_file_per_table from the beginning, dumping all data, delete all file, recreate database and import data again is only way ( check answers of FlipMcF above )

如果你从一开始就没有使用 innodb_file_per_table，转储所有数据，删除所有文件，重新创建数据库并再次导入数据是唯一的方法（检查上面 FlipMcF 的答案）

If you are using innodb_file_per_table, you may try

如果您使用的是 innodb_file_per_table，您可以尝试

1. If you can delete all data truncate command will delete data and reclaim diskspace for you.
2. Alter table command will drop and recreate table so it can reclaim diskspace. Therefore after delete data, run alter table that change nothing to release hardisk ( ie: table TBL_A has charset uf8, after delete data run ALTER TABLE TBL_A charset utf8 -> this command change nothing from your table but It makes mysql recreate your table and regain diskspace
3. Create TBL_B like TBL_A . Insert select data you want to keep from TBL_A into TBL_B. Drop TBL_A, and rename TBL_B to TBL_A. This way is very effective if TBL_A and data that needed to delete is big (delete command in MySQL innodb is very bad performance)

1. 如果您可以删除所有数据，truncate 命令将为您删除数据并回收磁盘空间。
2. 更改表命令将删除并重新创建表，以便它可以回收磁盘空间。因此，在删除数据后，运行没有改变任何内容的alter table 以释放硬盘（即：表TBL_A 具有字符集uf8，删除数据后运行ALTER TABLE TBL_A 字符集utf8 -> 此命令不会从您的表中更改任何内容，但它使mysql 重新创建您的表并重新获得磁盘空间
3. 像 TBL_A 一样创建 TBL_B 。将要从 TBL_A 保留的选择数据插入到 TBL_B 中。删除 TBL_A，并将 TBL_B 重命名为 TBL_A。如果TBL_A和需要删除的数据很大，这种方式非常有效（MySQL innodb中的delete命令性能很差）