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

问题描述

我有一个使用 InnoDB 存储引擎的 MySQL 表；它包含大约 2M 数据行.当我从表中删除数据行时，它没有释放分配的磁盘空间.运行优化表命令后，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 的大小.曾经.即使您使用优化表从已删除的记录中释放使用的空间，它也会在以后重复使用.

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 文件在 优化表格.

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屋！