是否有可能在不存在复制滞后/停机的情况下运行优化表？

Question

我有一个有1亿条记录和500 of数据的表。我一直在备份许多旧记录到备份DB中，并将它们从主DB中删除。但是磁盘空间并没有减少，我注意到该表的data_free增长了很多。

我的理解是，我需要运行OPTIMIZE TABLE来减少磁盘大小，但是我读到了这会导致复制滞后。我正在使用MySQL5.7 InnoDB。

所以我的问题是，我能在不引起复制滞后的情况下运行OPTIMIZE TABLE吗？例如，在主服务器上运行OPTIMIZE TABLE，例如：

OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname;

然后对奴隶一个一个地运行相同的命令。那能行吗？这样做有什么风险吗？或者还有别的办法吗？

Answer 1

在我的公司，我们使用Percona的免费工具pt-在线-模式更改。

它不按字面意思做优化表，但是对于InnoDB表，任何表复制操作都会实现相同的结果。也就是说，它生成一个新的InnoDB表空间，将所有行复制到该表空间，并为该表重新构建所有索引。新的表空间将是原始表空间的碎片化版本。

任何更改都会起作用，您不必更改表中的任何内容。我用的是非操作ALTER TABLE <name> FORCE。

pt-online模式更改的优点是，当它工作时，您可以继续读和写表。它只需要一个简短的元数据锁就可以在启动时创建触发器，而在结束时需要另一个简短的元数据锁来将新表替换为旧表。

如果使用优化表，这会导致很长的复制滞后，因为在复制完成之前，它不会在副本上开始运行。

而对于pt-online模式更改，它立即开始运行表复制，这与其他并发事务一起继续，当它在源上完成时，它也只需要在副本上完成一段时间。

它实际上比优化表花费的时间更长，但由于它并不妨碍您使用该表，所以这并不重要。

Answer 2

最后，我通过设置一个复制环境在本地进行测试。

似乎可以在不造成任何停机或复制滞后的情况下运行OPTIMIZE TABLE tblname;。

您需要在主服务器上运行OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname;，以避免写入bin日志和将查询复制到从服务器。

然后，您必须在每个奴隶中单独运行OPTIMIZE TABLE tblname;。

以下是对发生情况的更详细解释：https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html#optimize-table-innodb-details

上面写着：

只有在操作的准备阶段和提交阶段，才会短暂地使用独占表锁。

所以几乎没有锁定时间。

有一些边缘情况需要担心，这可能会导致停机时间(由于联机DDL上的复制方法导致的表锁)，其中一些情况列在上面的链接中。

另一件需要考虑的是磁盘空间。使用InnoDB，我观察到它重新创建了表。因此，如果表的内容加起来达100 to，则至少需要额外100 to的空闲空间才能成功运行该命令。

正如Bill所建议的那样，使用pt-在线-模式更改可能是一种更安全的选择，但是如果您不能使用它，仔细的操作似乎没有复制滞后，也没有可能停机。