mysql - 如何优化 "optimize"耗时较长的MYSQL查询

Question

我有一个表 (innodb)，每周有 100 万个新插入 (20GB)。我只需要 1 周的数据，所以我在 7 天后删除它，所以每天我们删除大约 3GB 并插入 3GB 新数据。该表已经与其他表位于单独的数据库中。

问题是磁盘空间仅在优化查询后才会被释放，因此我们每隔几周在晚上运行一次。它可以工作，但需要 30 分钟，并且当时会卡住整个数据库服务器，而不仅仅是特定的数据库。

有什么方法可以更快地优化吗？

如果我们每次删除数据时都运行一次优化，会比每隔几周运行一次优化更快吗？我认为当只需要从磁盘中删除 3GB 的已删除行时，运行它可能会更快，如果我们在 20 天后运行它，则为 60GB。是对的吗？还有其他方法可以优化吗？

Answer 1

不用担心OPTIMIZE TABLE的速度，让我们摆脱对它的需要。

按范围分区(TO_DAYS(...)) ...

然后每晚DROP PARTITION；这比使用 DELETE 快得多，并且避免了 OPTIMIZE 的需要。

请务必设置innodb_file_per_table=ON。

每晚，使用REORGANIZE PARTITION 将 future 分区转变为明天的分区和一个新的空分区。

详细信息请参见:http://mysql.rjweb.org/doc.php/partitionmaint

请注意，每个PARTITION实际上是一个单独的表，因此DROP PARTITION实际上是一个删除表。

应该有 10 个分区:

• 1 个起始表，以避免按 DATETIME 分区时出现故障的开销。
• 7 个每日分区
• 额外 1 天，这样就有完整 7 天的值(value)。
• 1 个空的 future 分区，以防您的夜间脚本无法运行。