MySQL - 更改表列 vs mysqldump + 加载数据文件

Question

我有一张大表（~250 Gb），我想将主键列从 更改为INT。BIGINT假设服务器实例具有足够的资源以最佳方式为这两种方法配置 MySQL，哪种方法将在最短的时间内运行？1. 修改表 2. 创建新表，使用 导出旧表mysqldump并使用 插入新表LOAD DATA INFILE。

笔记：我知道pt-在线-架构更改，这可能是在不影响现有数据库使用的情况下修改表的最佳选项，但我想知道哪个选项需要最少的运行时间。

奖金：我怀疑 MySQL 和 MariaDB 的答案是否相同？

Answer 1

最好的、更快、更安全的方法是创建一个包含更改的新表，然后将数据加载到其中。即使您有资源来运行 alter table，这也会给服务器带来压力，并可能因此崩溃。

Answer 2

每种方法都需要读取和写入整个表。这就对所涉及的时间设置了一个下限——即完成所有 I/O 的时间。

转储和重新加载可能需要多次读取和写入，具体取决于您对转储执行的操作。

ALTER并且 dump+reload 需要整个过程中锁定表，或者至少必须避免写入表。

PTOSC 可能稍慢一些，但您不能在很短的时间内对其进行写入。因此，如果您真正关心的是写入，而不是耗时，这可能是最佳选择。

PTOSC 确实

1. 创建一个新表，进行更改（INT 到 BIGINT）。
2. 添加TRIGGER以捕获任何写入。
3. 循环遍历表格，复制大块的行。
4. 几乎完成时，会出现短暂停顿（锁定）来处理触发器捕获的信息、交换表并清理。

我期望 MySQL 和 MariaDB 之间在任何方法上都没有区别。但是，ptosc 可能在 Windows 上不可用。

至于资源，每个都需要空间来存放表的第二个副本，甚至更多。请注意的影响innodb_file_per_table。如果表当前位于其自己的文件中，并且设置为“1”，则磁盘处理干净。否则，ibdata1 或表空间或任何其他可能大幅增长并且旧空间无法释放。