mysql - 将 MySQL 表列类型从 INT 更改为 BIGINT

Question

我有一个包含不到 5000 万行的表。它达到了 INT 的极限 (2147483647)。目前该表未被写入。

我计划将 ID 列从 INT 更改为 BIGINT。我正在使用 Rails 迁移通过以下迁移来执行此操作:

  def up
    execute('ALTER TABLE table_name MODIFY COLUMN id BIGINT(8) NOT NULL AUTO_INCREMENT')
  end

我已经在 2000 行的数据集上进行了本地测试，它运行正常。对 5000 万个对象运行 ALTER TABLE 命令应该没问题，因为该表目前未被使用？

我想在运行迁移之前进行检查。任何输入将不胜感激，谢谢!

Answer 1

我们有完全相同的场景，但使用的是 postgresql，我知道 50M 如何填满 int 的整个范围、它在 id 中的间隙、随着时间的推移删除行产生的间隙或涉及不完整事务的其他因素等。

我将解释我们最终做了什么，但首先，严肃地说，在 2k 行上测试 5000 万行的数据迁移并不是一个好的测试。

这个问题可以有多种解决方案，具体取决于您使用的是哪个数据库提供商等因素？我们使用的是 mazon RDS，它对运行时间和他们所说的 IOPS(输入/输出操作)如果我们在具有此类限制的数据库上运行如此密集的查询，它将在中途用完其 IOPS 配额，并且当 IOPS 配额用完时，数据库最终变得太慢并且有点无用。我们不得不取消查询，让 IOPS catch 来，这需要大约 30 分钟到 1 小时。

如果您没有此类限制并且在内部部署了数据库或类似的东西，那么还有另一个因素，即您是否可以承受停机时间？**

如果您可以承受停机时间并且您的数据库没有 IOPS 类型限制，您可以直接运行此查询，这将花费很多时间(可能半小时左右，取决于很多的因素)，同时

• 表将被锁定，因为行正在更改，所以不仅要确保该表没有任何写入，而且在此过程中也没有读取，以确保您的进程进入顺利结束，没有任何死锁类型的情况。

我们为避免停机和 Amazon RDS IOPS 限制所做的工作:

在我的例子中，当我们意识到这将用完时，表中还剩下大约 40M 个 ID，我们希望避免停机。所以我们采取了多步骤的方法:

1. 创建一个新的 big_int 列，将其命名为 new_id 或其他名称(从一开始就对其进行唯一索引)，这将可以为 null，默认为 null。
2. 编写每晚运行几次的后台作业，并从 id 列回填到 new_id 列。我们每晚回填大约 4-500 万行，周末回填更多(因为我们的应用在周末没有流量)。
3. 当你遇到回填时，现在我们将不得不停止对该表的任何访问(我们刚刚在晚上关闭了我们的应用程序几分钟)，并从 max(new_id) 值开始创建一个新序列，或使用现有序列并将其绑定(bind)到 new_id 列，默认值为该序列的 nextval。
4. 现在将主键从 id 切换到 new_id，在此之前使 new_id 不为空。
5. 删除 id 列。
6. 将 new_id 重命名为 id。
7. 并恢复您的数据库操作。

以上是我们所做工作的最小记录，您可以用谷歌搜索一些关于它的好文章，one is this .这种方法并不新鲜，而且很常见，所以我相信您甚至还会找到特定于 mysql 的方法，或者您可以在上面的这篇文章中调整一些内容，然后就可以开始了。