删除的行占用的空间是否被重用？ [英] Does the space occupied by deleted rows get re-used?

问题描述

我已经读了几次，你删除MySQL中的InnoDB表中的一行后，它的空间不会重复使用，所以如果你做了很多INSERTs到一个表，然后定期删除一些行表将使用更多，

最近我被告知，被删除的行所占用的空间被重用，但只有后一些交易完成，甚至那么 - 不完全。我现在很困惑。

有人可以对我有意义吗？我需要做一个InnoDB表中的很多INSERT，然后每X分钟我需要DELETE超过Y分钟的记录。

解决方案

这是偏执狂：）

p>

DB不会不必要地增大大小，但是对于性能问题，空间也不会释放。

最可能的原因是，如果您删除记录未向操作系统发送空间。

这是因为：

• DB需要有一些高清空间来保存其数据;


• 插入新行时，会使用该空格。
$ b $

• 现在，当您删除一些行时，按顺序排列为了防止预留更多的块，它的空间是免费的，但是不会返回到操作系统，所以你可以稍后再次使用它，而不需要预留新的块。

如您所见，空格 重复使用，但从未回复。这是您问题的关键。

I have read several times that after you delete a row in an InnoDB table in MySQL, its space is not reused, so if you make a lot of INSERTs into a table and then periodically DELETE some rows the table will use more and more space on disk, as if the rows were not deleted at all.

Recently I've been told though that the space occupied by deleted rows is re-used but only after some transactions are complete and even then - not fully. I am now confused.

Can someone please make sense of this to me? I need to do a lot of INSERTs into an InnoDB table and then every X minutes I need to DELETE records that are more than Y minutes old. Do I have a problem of ever-growing InnoDB table here, or is it paranoia?

解决方案

It is paranoia :)

DB's don't grow in size unnecessarily, but for performance issues space is not freed either.

What you've heard most probably is that if you delete records that space is not given back to the Operating System. Instead, it's kept as an empty space for the DB to re-use afterwards.

This is because:

• DB needs to have some HD space to save its data; if it doesn't have any space, it reserves some empty space at first.
• When you insert a new row, a piece of that space is used.
• When you run out of free space, a new block is reserved, and so on.
• Now, when you delete some rows, in order to prevent reserving more and more blocks, its space is kept free but never given back to the Operating System, so you can use it again later without any need of reserving new blocks.

As you can see, space is re-used, but never given back. That's the key point to your question.

这篇关于删除的行占用的空间是否被重用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！