被删除的行占用的空间是否会被重新使用？
我已经读过几次，在MySQL中的InnoDB表中删除一行后，它的空间不会被重用，所以如果你对表进行大量插入，然后定期删除一些行，表将使用越来越多的空间磁盘，就像根本没有删除行一样。
最近我被告知，被删除的行占用的空间会被重新使用，但只有在某些事务完成后才能重新使用，即使如此 - 也不会完全使用。 我现在很困惑。
有人能给我解释一下吗？ 我需要对 InnoDB 表进行大量插入，然后每隔 X 分钟我需要删除超过 Y 分钟的记录。 我这里有InnoDB表不断增长的问题吗，还是偏执？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是偏执:)
数据库不会不必要地增加大小，但由于性能问题，空间也不会被释放。
您最有可能听到的是，如果删除记录，空间不会返还给操作系统。 相反，它被保留为空白空间，供数据库随后重复使用。
这是因为：
正如您所看到的，空间被重复使用，但从未归还。 这就是你问题的关键点。
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:
As you can see, space is re-used, but never given back. That's the key point to your question.
在 innodb 中，没有实际的方法来释放空间。
使您能够删除记录复制
数据到新表并删除旧表
表，从而恢复记录。
收据清理整个
服务器。 检查以下内容：
http://dev.mysql.com/doc/ refman/5.0/en/adding-and-removing.html
当您使用巨大的表（在我的例子中它们超过 250GB）时，所有这些方法都变得不切实际，并且您必须让它们删除记录以获得更好的性能。
你必须认真思考，你的硬盘上是否有足够的空间来执行上述功能之一（在我的情况下，我认为 1TB 不足以完成所有这些操作），
对于 Innotab 表（和 mysql 本身），这个选项是相当不错的如果数据库大小很大，则受到限制。
in innodb, there is no practical way of freeing up the space.
enable you to delete record copy the
data to a new table and delete old
table, thus recovering records.
receipe to clean up the whole
server. Check following:
http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
All of these methods become impractical when you are using huge tables(in my case they are more than 250GB) and you must keep them deleting records to better performance.
You will have to seriously think, whether you have enough space on your harddisk to perform one of the above function (in my case I do not think 1TB is enough for all these actions)
with Innotab table (and mysql itself) the option are fairly limited if have serious database size.
我有一个建议，因为有很多未知数。 其中最主要的是您正在使用的 sql 版本。
知道需要多少开销来跟踪被删除的小条目所使用的空间，然后 SQL 查找将使用部分或全部空间的新条目，在我看来，一些优化代码可能认为该空间应该变成死区并且不被重复使用。 如果用于标识已删除空间的条目占用的临时空间多于从已删除区域恢复的临时空间，则可能会在某个时间点完成删除，但该空间不会留下任何链接，这将导致数据库大小增长。
我建议创建一个临时数据库，并在周末运行一些测试代码，重现您正在做的事情，即删除一个或多个条目，添加可能/可能不完全相同大小的新条目，然后让它运行周末。
第二天检查数据库大小。
这样你就 100% 知道会发生什么。
I have a suggestion, because there are so many unknowns. Chief among them being version of sql that you are using.
Knowing how much over head would be required to track space used by small entries that are deleted and then SQL looking for some new entry that would use some or all of that space, it seems to me some optimization code might deem the space should just become dead space and not re-used. if entry to identify the deleted space takes more temporary space than is recovered from the deleted area, there might be a point where deletes are done, but that space is left without any links to it, which would result in Database growing in size.
I'd recommend creating a temporary database, and run some test code over the weekend that reproduced what you are doing which is deleting an entry or many, adding new entries that might/might not be exactly the same size, and let it run over the weekend.
Check database size the next day.
This way you know 100% what will happen.