在优化10亿行表和转储恢复表之间，哪个会更快呢？

Question

有超过10亿行的多个表。存档和删除了其中的许多，现在想恢复磁盘空间。

可以选择优化表，或者转储表并还原它。

为了恢复，我将将转储的sql文件分割成多个文件，并运行并行恢复，直到CPU在DiskIO上阻塞不多的程度，以便以最好的速度进行恢复。

现在，从mysql的角度来看，这会更快，为什么呢？

我知道DiskIO将是一个重要的因素，但我希望从mysql的角度看技术要点。

注意: mysql所在的磁盘，它是一个高性能的SSD磁盘。

Answer 1

OPTIMIZE可能更好，因为它所做的是

1. CREATE TABLE ...
2. 复制所有现有行
3. RENAME ...

(在步骤2期间或之后的一段时间内，将重建索引。)

转储和重新装载：

1. 读取整个表，写入磁盘
2. DROP TABLE或TRUNCATE TABLE与CREATE TABLE ...
3. 读取转储，插入表

这比较慢，因为转储需要额外的I/O。

如果磁盘空间紧张，则从远程计算机执行转储(mysqldum/xtrabackup/etc)和恢复(mysql)，并将转储文件放在该远程机器上。这可能是在不耗尽磁盘空间的情况下进行清理的唯一方法。

更好的..。

下次你需要做一个大的DELETE..。

1. CREATE TABLE new LIKE real
2. 将不想删除的行复制到new中
3. RENAME TABLE real TO old, new TO real
4. DROP TABLE old

这“消除”了做DELETEs的努力。

删除大块是一个热门话题，请参阅我关于这个话题的博客。

更好的是..。

如果这是一个时间序列，并且您正在删除“旧”数据，则在日期上使用PARTITION。然后DROP PARTITION‘立即’放弃旧的数据。详细信息。

警告

如果您只有20 of的空闲磁盘，而且InnoDB表中剩余的数据+索引超过20 of，那么OPTIMIZE将耗尽磁盘空间并失败(不执行“任何操作”)。除非您可以使用另一台机器进行转储，否则dump+reload也是如此。(“更好的”解决方案需要设置；这一次两者都做不到。)

如果该表位于ibdata1 (而不是innodb_file_per_table)中，则不会向操作系统提供任何空间。它将用于InnoDB表中的未来增长。

增编

它们的区别可能是恢复和OPTIMIZE之间的大小或速度不同。索引的重建可能有所不同。

如果您这样做是为了收回磁盘上的空间，那么首先从最小的表开始，然后按您的方式工作。请记住，InnoDB试图清理其BTrees中的漏洞。理论上，即使在经历了大量的搅动之后，一个保存良好的BTree平均将有31%的空置空间。因此，这是您可以期望从OPTIMIZE (或等效的)中收回的最多的部分。然而，一个巨大的删除除了31%的空间外，还留下了空闲空间。