为什么与 PostgreSQL 相比，Mysql 不需要真空？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25153532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is it a vacuum not needed with Mysql compared to the PostgreSQL?
提问by RunningAdithya
I am more familiar with PostgreSQL than MySQL. Have encountered wraparound Id failure once with the PostgreSQL db and then understood the importance of vacuuming in the db. Actually, that was such a massive overhead work to deal with(and it was with an old version 7.4.3 which is updated a few months back to have the autovacuum). When comparing MySQL with PostgreSQL, assume that MySQL does not have to deal with such overheads like vacuum in PostgreSQL. Is this assumption right?
我对 PostgreSQL 比 MySQL 更熟悉。曾在 PostgreSQL 数据库中遇到过一次环绕 Id 失败，然后了解在数据库中清空的重要性。实际上，这是一项巨大的开销工作要处理（并且是使用旧版本 7.4.3 进行的，该版本在几个月前更新为具有 autovacuum）。将 MySQL 与 PostgreSQL 进行比较时，假设 MySQL 不必处理诸如 PostgreSQL 中的vacuum之类的开销。这个假设正确吗？
Also why is it a vacuum not needed with MySQL Dbs compared to PostgreSQL? Is there any other optimization alternatives similar to vacuum exist for MySQL dbs?
另外，与 PostgreSQL 相比，为什么 MySQL Dbs 不需要真空？MySQL dbs 是否还有其他类似于真空的优化替代方案？
采纳答案by Craig Ringer
Robert Haas wrote on this topic.
罗伯特·哈斯 (Robert Haas) 写了关于这个话题的文章。
The short version is that InnoDB uses rollback logs, more like Oracle's design. Only the most recent version of a row is kept on the main table. It must manage log purging, an asynchronous/delayed operation with a related function to PostgreSQL's VACUUM.
简而言之，InnoDB 使用回滚日志，更像 Oracle 的设计。主表中仅保留行的最新版本。它必须管理日志清除，这是一种异步/延迟操作，具有与 PostgreSQL 的VACUUM.
This means more writes to do on updates and makes access to old row versions quite a lot slower, but gets rid of the need for asynchronous vacuum and means you don't have table bloat issues. Instead you can have huge rollback segments or run out of space for rollback.
这意味着要在更新上进行更多写入，并使访问旧行版本的速度变慢很多，但不需要异步真空，这意味着您没有表膨胀问题。相反，您可以拥有巨大的回滚段或用完回滚空间。
So it's a trade-off, a design with a different set of advantages and problems.
所以这是一种权衡，一种具有不同优势和问题的设计。
If you're talking about MyISAM tables, then it's totally different. PostgreSQL's tables won't eat your data. MyISAM will. PostgreSQL's tables are transactional. MyISAM isn't. A flat file doesn't require VACUUMeither, that doesn't make it a good idea.
如果你在谈论 MyISAM 表，那么它是完全不同的。PostgreSQL 的表不会吃掉你的数据。MyISAM 会。PostgreSQL 的表是事务性的。MyISAM 不是。平面文件也不需要VACUUM，这并不是一个好主意。
回答by j.w.r
The MySQL approximation of PostgreSQL's vacuumis OPTIMIZE TABLE tablename(MySQL docs). It performs a similar function in MySQL as PostgreSQL in that, depending on the storage engine used, it reclaims unused space, reorganizes indexes and tables, and defragments data files. You should definitely run it periodically just like vacuumin PostgreSQL.
PostgreSQL 的 MySQL 近似值vacuum是OPTIMIZE TABLE tablename( MySQL docs)。它在 MySQL 中执行与 PostgreSQL 类似的功能，根据所使用的存储引擎，它回收未使用的空间，重新组织索引和表，并对数据文件进行碎片整理。您绝对应该像vacuum在 PostgreSQL 中一样定期运行它。
