如何在大型mysql DataBase中更改表

Question

我有非常大的mysql (超过100 G)，我想在表中迁移一些更改。它需要摆出我没有空间为这个大小做备份，作为结果，它回滚所有的变化。显然，当我想在mysql中使用alter表时，它在本地磁盘中为它自己备份，并且由于磁盘上没有空间，它正在回滚。有谁可以帮助解决这个问题吗？

Answer 1

这取决于更改的类型。自从MySQL 5.6以来，一些修改可以进行“内置”，而不需要额外的存储。详细信息请阅读https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html。

但是，许多类型的更改(基本上是影响一行存储大小的任何更改)仍然需要“表重建”，这意味着它在执行alter的过程中生成表的副本。这需要额外的存储，通常与原始表的大小大致相同。如果您的服务器没有足够的存储空间来执行此操作，那么您就不能更改表，即句号。您应该在表增长到这个大小之前就已经解决了这个问题。监视数据库的大小并确保有足够的存储空间是您的责任。

您在上面的评论中说，不可能增加更多的存储空间。

有时，InnoDB表空间可能会变得“支离破碎”。这意味着它们可以使用稍微少一点的存储空间来存储相同的行。您可以使用OPTIMIZE TABLE <name>;或ALTER TABLE <name> FORCE;完成碎片整理。

一种可能的策略是优化每个表，从最小的表开始，并按大小顺序逐个执行。您希望您有足够的空间来执行此操作所需的复制表，通过优化小表而释放出来的任何空间都将帮助您优化中等大小的表，等等。这是一个很长的机会，因为这也有可能你没有足够的“浪费”的空间来恢复，以发挥作用。完成此过程后，您可能仍然无法更改最大的表。不幸的是，MySQL无法报告表的碎片，所以您只能尝试并查看恢复的空间(如果有的话)。

在此之后，唯一的其他选择是从表中删除数据，直到可以进行更改为止。修改仍然需要额外的存储，但只需要复制剩余的数据行所需的内存。也就是说，如果删除50%的行，那么存储副本的表空间文件只需要大约50%的存储空间。

您还可以尝试在进行更改时删除表中的一些索引。索引在相同的表空间中占用空间，如果有多个索引来占用比数据本身更多的空间，这并不少见。如果在ALTER语句中包含一些DROP操作，则生成的副本将占用较少的空间。另一方面，这些索引对于适当的查询优化是必要的，如果没有这些索引，您的应用程序就无法使用。