MySQL：对于具有固定列的表，需要进行OPTIMIZE TABLE操作吗？

Question

我有一个每周运行的脚本，将数据从我们的实时数据库移动到归档数据库，然后从实时数据库中删除刚刚归档的数据。由于这是一个相当大的删除操作（表中约10％的数据被修剪），我认为在此删除操作之后应该运行OPTIMIZE TABLE。然而，我正在阅读mysql文档，并不知道如何解释它：http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html。"如果您已删除表的大部分或对具有可变长度行的表进行了多次更改（具有VARCHAR、VARBINARY、BLOB或TEXT列的表），则应使用OPTIMIZE TABLE。已删除的行会维护在链接列表中，并且随后的INSERT操作会重用旧行位置。您可以使用OPTIMIZE TABLE来回收未使用的空间并碎片整理数据文件。"

第一句话对我来说有歧义。它的意思是： A) 如果您已经删除了具有可变长度行的表的大部分或者您已经对具有可变长度行的表进行了许多更改，则应运行它 或者 B) 如果您已经删除了任何表的大部分或者您已经对具有可变长度行的表进行了许多更改，则应运行它。

这样是否有意义？所以如果我的表没有VAR列，我还需要运行它吗？

顺便提一下 - 有没有任何指标告诉我一个表适合进行OPTIMIZE调用？

此外，我读到这篇文章http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/说仅运行OPTIMIZE table对于主键是有用的。如果我的大多数选择都来自其他索引，那么对于具有替代键的表而言，我是不是在浪费精力？

非常感谢！

- Shane N

Answer 1

在您的情况下，我不认为定期优化表会有明显的差异。

首先，您对文档的第二种解释（B）是正确的 - “如果您已删除任何表的大部分或对具有可变长度行的表进行了许多更改。”

如果您的表没有VAR列，则无论记录包含什么数据，每个记录在表中占用的空间都完全相同。如果从表中删除记录，并且DB选择重复使用存储前一个记录的确切区域，则可以这样做而不浪费任何空间或破碎数据。

至于OPTIMIZE是否仅改善利用主键索引的查询的性能，这个答案几乎肯定会因使用的存储引擎而异，恐怕我无法回答这个问题。

然而，提到存储引擎，如果您最终使用OPTIMIZE，请注意它不喜欢在InnoDB表上运行，因此该命令映射到ALTER并重建表，这可能是一项更昂贵的操作。无论哪种方式，在优化期间表格被锁定，因此请非常小心地运行它的时间。

- Ryan Tenney

---

谢谢，Ryan。我正在使用InnoDB，并且肯定注意到了锁定问题，这就是为什么我想确保我没有过度使用它。所以如果我理解你的意思正确的话，由于我的表没有使用任何VAR列，因此它不会像碎片化那样。好的，那么很好知道，谢谢！ - Shane N

Answer 2

MyISAM和InnoDB之间存在很多差异，我将这个答案分成两部分:

MyISAM

• FIXED 在MyISAM中具有一定的意义。
• "已删除的行将在链表中维护，后续的INSERT操作会重用旧的行位置"适用于MyISAM，而不是InnoDB。因此，对于MyISAM表格中的大量更新，OPTIMIZE可以起到积极的作用。
• 在MyISAM中，VAR加上DELETE / UPDATE会导致碎片化。
• 由于链接列表和VAR，单个行可以在数据文件（.MYD）中被分割。 （否则，MyISAM行在数据文件中是连续的。）

InnoDB

• FIXED对于InnoDB表没有意义。
• 对于InnoDB中的VAR，存在“块分裂”，而非链表。
• 在BTree中，块分裂稳定在平均填充率为69%左右。因此，使用InnoDB几乎任何滥用都不会使表过度膨胀。也就是说，DELETE/UPDATE（带或不带VAR）会导致更有限的BTree“碎片化”。
• 在InnoDB中，已清空的块（每个16KB）被放置在“空闲列表”上以供重复使用；它们不会归还给操作系统。
• InnoDB中的数据按PRIMARY KEY排序，因此在表的某个部分删除一行并不会为表的另一部分提供空间以插入新行。但是，当一个块被释放时，它可以在其他地方使用。
• 相邻的两个半空块将合并，从而释放一个块。

两者都需要翻译

• 如果你要删除“旧”的数据（10%），那么使用PARTITIONing是一种更好的方法。请参见我的博客。它包括DROP PARTITION，这是瞬间完成的，并将空间返还给操作系统，此外还有REORGANIZE PARTITION，可以瞬间完成。
• OPTIMIZE TABLE几乎永远不值得进行。

- Rick James

---

"OPTIMIZE TABLE 几乎没有什么意义" 为什么？ - lonix

@lonix - 索引（和数据）存储在B树中。（阅读相关内容。）访问行的速度不取决于表中块数，而是取决于树的深度。优化会减少块的数量（一些），但很少改变深度。 维护良好的B树在optimize-table之后不会比其大小增加一倍。（完整答案需要更多讨论。） - Rick James

谢谢，这很有道理。因此，除非您在企业环境中运行庞大的数据库，否则您不需要考虑为您的WordPress网站进行优化 - 在任何情况下，OPTIMIZE都不会有太大的区别，因为它基本上是过早优化。 - lonix

@lonix - 还记得软盘的日子吗？那时候，每个人都在不断寻找“碎片整理”和其他方式来缩小所需的磁盘空间。我怀疑OPTIMIZE是因为这样的概念而发明的。随着磁盘变得巨大和快速，压缩、碎片整理和OPTIMIZE已经基本上变得无用了。 - Rick James