主页 / dba / 问题 / 157305
Accepted
Collin Dauphinee
Asked: 2016-12-06 22:55:55 +0800 CST

如何在不导致停机的情况下设置现有 InnoDB 表中列的 auto_increment 值？

  • 772

我有一个 InnoDB 表，其主键列定义如下：

id INT NOT NULL AUTO_INCREMENT

此列的 auto_increment 值已达到 INT_MAX，因此所有插入都因重复键错误而失败。但是，此表有从 0 到 180 万的未使用 ID 值，因此我想将 auto_increment 值重置为 0，以便为实施长期修复争取时间。

我找不到任何方法来完成此操作而不会导致大量停机时间。如果我的应用程序服务器无法从该表中读取（但不能写入，就像现在一样，没关系），它们的核心功能就会被破坏。

我考虑过以下两种解决方案，但没有成功：

使用 设置 auto_increment 值ALTER TABLE。这是不可能的，因为它会重建表，这需要很长时间。

创建一个具有相同模式的新表，并使用RENAME TABLE从 0 开始的列交换新表，然后用旧表中的数据回填它。这也是不可能的，因为回填过程会破坏 auto_increment 值（而且我相信它会在回填时阻止来自应用程序的插入）。

我可以在这里采取任何其他方法，还是我完全不走运？

mysql

1 个回答

  1. Best Answer

    硬着头皮接受停机时间。

    MAX(id)+1为表设置自动增量值是没有用的，因为它无论如何都会被重置。

    ALTER TABLE-- 我认为INPLACE在这种情况下行不通，所以它将是全表副本。（特别是如果 theid是PRIMARY KEY.）

    pt-online-schema-change——全文。它的好处是该表继续可用。但是由于另一个原因，您的表不可用，即无法添加另一个 id。

    改什么？如果你有INT，你有一个20亿的限制；更改为INT UNSIGNED会给你一个 40 亿的限制。表的大小不会改变（除了 BTrees 的洗牌）。 BIGINT是 8 个字节，给你一个无法达到的限制。注意：所有引用此表的表都需要同时更改数据类型。这涉及到更多ALTER TABLEs。

    重用0..1.8M？您可以像您一样显式设置 id INSERTs，但这可能需要更改代码（和风险）。

    这可能是最快的解决方法：通过执行以下操作将前 180 万行向下移动

    UPDATE tbl
    SET id = id - (some constant: about 2B -1.8M)
    WHERE id > (the same constant);

    并重新启动服务器，以便它可以发现新的MAX(id). 注意：此技术不适用于 8.0 版。注意：如果其他表使用此 id（作为 FK 或 for JOINing，您还必须在他们的表上执行类似UPDATE操作以调整其中foo_id的。

    注意：不要将任何 id 设置为 0 或小于 0。（我认为UPDATE在这方面是正确的。）在您再次遇到麻烦之前，您将只有 180 万行的空间。

    差距？你的ID有很多差距吗？它们是由INSERT IGNORE,REPLACE和其他几种类型的INSERT, 加上（当然）引起的DELETE。许多“烧录”ID 的“插入”方法可以重新编码以消除烧录，并且通常不会变慢。

    AUTO_INCREMENT PRIMARY KEY展望未来......在每张桌子上都有一个并不总是“正确的” 。我估计 2/3 的表最好使用“自然 PK”——即某些列（或列的组合）UNIQUE既不笨重又不笨重。

    • 1

相关问题