当数据从mysql中删除时，它占用的空间会发生什么？ [英] When data deleted from mysql, what happens to space it took up?

问题描述

我的理解是（如果我错误或不准确，请纠正我），当你从MySQL表中删除数据（例如，整行），数据库文件的文件大小不缩小。如果你有一个20Gb的数据库并删除了19Gb的数据，你不会有一个1Gb的数据库文件，它仍然是20Gb。

My understanding is (correct me if I'm wrong or inaccurate please) that when you delete data (say, a whole row) from a MySQL table, the file size of the database file does not shrink. If you had a 20Gb database and deleted 19Gb of data from it, you wouldn't have a 1Gb database file, it would still be 20Gb.

空间在文件中的数据是什么？它是否被空数据替换？或者删除的数据是否保留在文件中，但被标记为已删除，不能再通过常规手段（例如运行选择查询）查看？如果我向数据库添加更多的数据，它会进入旧的已删除数据的空间，或者它将总是占用新的空间？

What happens in the "space" within the file where the data used to be? Does it get replaced by empty data? Or does the deleted data remain in the file but is "flagged" as having been deleted and can't be seen any more by regular means like running a select query? If I add more data to the database, will it go in the space where old deleted data used to be, or will it always take up new space?

例如，在上面的例子中，如果我从20Gb数据库删除19Gb，然后添加5Gb的新数据，数据库将是20Gb还是25Gb？

For instance, in the example above, if I delete 19Gb from a 20Gb database, then add 5Gb of new data, will the database be 20Gb or 25Gb?

推荐答案

它成为可用空间，在插入完成时重复使用。它可以通过OPTIMIZE TABLE命令释放到操作系统。

It becomes free space to be reused whenever an insert is done. It can be released to the operating system via the OPTIMIZE TABLE command.

这篇关于当数据从mysql中删除时，它占用的空间会发生什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！