ON DUPLICATE KEY UPDATE 消耗自增 ID，何时迁移 BIGINT ​

INT 主键的耗尽速度远比行数增长所暗示的更快。本文探讨 MySQL 自增 ID 的无谓消耗，以及何时应当迁移到 BIGINT。

某团队正在排查其 products 表性能缓慢的问题。该表约有 100,000 行数据，索引设置合理，因此性能下降出乎意料。有人运行 SELECT MAX(id) FROM products 做了一次合理性核对，结果：847,392,105。接近十亿。主键为 INT UNSIGNED，上限约为 43 亿——尽管实际只有 100K 行，表却已走完通向溢出的五分之一路程。该团队的 INSERT ... ON DUPLICATE KEY UPDATE upsert 模式，随着商品在全天不断补货和更新而高频运行，多年来一直在悄悄烧掉自增 ID 值。直到数字大到足够显眼，才有人注意到这个问题。

这是 MySQL 最隐蔽的浪费行为之一。INSERT ... ON DUPLICATE KEY UPDATE 有文档记载，其行为也确实如文档所述——问题的模糊之处在于"能正常工作"究竟意味着什么。当 INSERT 部分成功（未发现重复键）时，会插入新行，自增计数器加一。当 ON DUPLICATE KEY UPDATE 分支触发（发现重复键，更新已有行）时，不会插入新行……但自增计数器照样加一。每一次"插入失败转为更新"都会烧掉一个永远不会被使用的自增 ID 值。对于重复率高的应用（upsert、库存更新、事件聚合），自增计数器的增长速度可能比实际行数快出几个数量级。

本文逐一讲解 ON DUPLICATE KEY UPDATE 下自增行为的实测验证结果、MySQL 为何连 UPDATE 分支执行也会烧掉 ID、innodb_autoinc_lock_mode 如何影响该行为、同样存在此问题的相关模式（INSERT IGNORE、REPLACE INTO）、常见整数类型的耗尽时间计算，以及从 schema 变更到应用层模式的各类修复方案。全部内容均在 MariaDB 10.11 上验证（行为与 MySQL 8.0 一致）。

速览 ​

核心意外：即使触发 UPDATE 分支，INSERT ... ON DUPLICATE KEY UPDATE 也会推进自增计数器。实测验证：在插入一行后自增计数器为 AUTO_INCREMENT=2 的基础上，六条重复键 INSERT 语句（全部触发 UPDATE）将计数器推进到 AUTO_INCREMENT=8。没有插入任何新行，却消耗了六个自增 ID 值。表中仍只有一行 id=1。

机制原理：InnoDB 在 INSERT 语句开始时、尚未知道该行究竟会被真正插入还是会触发 UPDATE 分支之前，就先预留自增 ID 值。若 UPDATE 分支执行，预留的自增 ID 值即被丢弃——不会归还。计数器已经前移，无法回头。

innodb_autoinc_lock_mode 控制具体表现，但无法消除空洞的产生。模式 0（traditional）：表级锁，ID 连续，但对并发插入速度慢。模式 1（consecutive，默认）：单条 INSERT 语句内预留值连续；空洞发生在语句之间。模式 2（interleaved）：完全并发，空洞大量产生。实测 MariaDB 10.11 上默认模式为 1。

相关模式同样烧掉 ID。INSERT IGNORE 遇重复键时，即使什么都没插入，也会推进计数器。REPLACE INTO 对已有行会删除旧行并插入新行——新行获得一个全新的自增 ID（旧 ID 随之消失），这比单纯产生空洞更糟。实测验证：对重复键执行三条 INSERT IGNORE 语句，自增计数器推进了 3。

耗尽时间计算：INT UNSIGNED 最大值为 4,294,967,295（约 43 亿）。对每秒 1000 次 upsert、重复率 90% 的应用，计数器以每秒 1000 的速度推进，而行数每秒只增长 100。若只统计新行，INT UNSIGNED 约 50 天耗尽；但由于空洞也计入，计数器远更早触及上限。INT SIGNED（最大值 2,147,483,647）耗尽时间减半。

修复方案按实施工作量排序：(1) 将列迁移为 BIGINT UNSIGNED（容量约 1844 亿亿——对典型工作负载而言实际上无穷大）。(2) 采用应用层"先检查再插入"（存在竞态条件，但可避免计数器烧损）。(3) 将模式从 ON DUPLICATE KEY UPDATE 改为显式 SELECT ... FOR UPDATE + 条件化 INSERT/UPDATE（锁更重，但不浪费 ID）。(4) 对时间序列或事件数据，完全不要使用自增——改用 UUID 或外部生成的 ID。迁移到 BIGINT 是最常见、风险最低的修复方案。

你将学到什么 ​

• ON DUPLICATE KEY UPDATE 下自增 ID 烧损的实测机制
• 存在同样问题的相关模式（INSERT IGNORE、REPLACE INTO）
• innodb_autoinc_lock_mode 的三种取值及其权衡
• INT/BIGINT 类型的耗尽时间计算
• 从 schema 迁移到应用层模式的修复方案

实测验证的行为 ​

测试环境：一张简单的 products 表，主键为 INT UNSIGNED AUTO_INCREMENT，并在 sku 上建有唯一索引。

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    stock INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
-- Verify the default lock mode
SELECT @@innodb_autoinc_lock_mode;
-- Result: 1 (consecutive mode, MariaDB 10.11 and MySQL 8.0 default)

验证序列：

-- Step 1: Insert a new row
INSERT INTO products (sku, name, stock) VALUES ('SKU-A', 'Product A', 10)
    ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock);
-- Result: id=1, stock=10, AUTO_INCREMENT=2 (next value)
-- Step 2: Insert with duplicate SKU (triggers UPDATE branch)
INSERT INTO products (sku, name, stock) VALUES ('SKU-A', 'Product A', 5)
    ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock);
-- Result: id=1 (unchanged), stock=15 (10+5), AUTO_INCREMENT=3
-- Step 3-7: Five more duplicates
-- (Each triggers UPDATE branch, no new rows)
-- Result: id=1, stock=20 (15+5), AUTO_INCREMENT=8

共执行六条 INSERT 语句。表中只有一行（id=1）。自增计数器停在 8。计数器从 1 推进到 8，共前进 7，其中一次推进对应真实插入，六次对应未创建任何行的 UPDATE 分支执行。

该行为具有确定性，多次执行结果一致。每条 INSERT ... ON DUPLICATE KEY UPDATE 都会在语句开始时预留一个自增 ID 值，无论该行最终被真正插入，还是触发 UPDATE 分支。

为什么会这样 ​

InnoDB 的自增机制（MySQL/MariaDB 参考手册中有记载）必须在得知行是否会被插入之前就预留值。预留之所以发生，是因为：

• 自增计数器是各连接间共享的资源
• 提前预留可避免行处理期间的竞争与锁等待
• 优化器可能以某种方式重排或批量处理操作，使"在确认行会被插入后再预留"变得不切实际

当 UPDATE 分支触发（发现重复键）时，预留的自增 ID 值不会归还到"池"中。要做到归还，需要：

• 追踪每条语句各自预留了哪些值
• 在并发事务之间安全地回滚计数器
• 与复制机制协调（二进制日志需要处理"已预留但未使用"的情形）

正确实现这一切的成本（性能、复杂度及各种边界情况）高于放任这些值泄漏的成本。MySQL/MariaDB 选择了泄漏行为；应用只能承受这一后果。

innodb_autoinc_lock_mode 设置影响预留的进行方式，但不改变值是否泄漏。三种模式：

模式 0（traditional）：每条 INSERT 执行期间持有表级自增锁。值严格连续（单条 INSERT 语句内无空洞）。并发插入时速度慢，因为所有连接在锁上串行化。生产中很少使用。

模式 1（consecutive，默认）：对简单 INSERT，一次分配一个值。对批量 INSERT（INSERT … SELECT 或多行 VALUES），根据行数预先分配一段区间。单条 INSERT 语句内的值连续；空洞发生在语句之间。在并发与可预测性之间取得平衡。实测确认是 MariaDB 10.11 的默认模式。

模式 2（interleaved）：每行的自增 ID 值在行插入的那一刻分配。完全并发——连接之间无需等待。空洞大量产生，语句内与语句间皆有。吞吐量最佳，但 ID 可预测性最差。MySQL 8.0+ 基于行的二进制日志复制要求此模式（有资料称模式 2 是 MySQL 8.0+ 的默认值；具体版本行为各异）。

没有任何一种模式能阻止"UPDATE 分支消耗一个自增 ID 值"的行为。这与锁模式无关。

INSERT IGNORE 存在同样的问题 ​

INSERT IGNORE 将某些错误（包括重复键）当作警告并继续执行。遇到重复键时，INSERT 被跳过——不插入行、不执行更新，只是静默。但自增 ID 值仍然被消耗。

实测验证模式：

-- Reset state
TRUNCATE products;
INSERT INTO products (sku, name, stock) VALUES ('SKU-X', 'PX', 1);
-- AUTO_INCREMENT is now 2
INSERT IGNORE INTO products (sku, name, stock) VALUES ('SKU-X', 'PX', 5);
INSERT IGNORE INTO products (sku, name, stock) VALUES ('SKU-X', 'PX', 5);
INSERT IGNORE INTO products (sku, name, stock) VALUES ('SKU-X', 'PX', 5);
-- All three IGNORE the duplicate SKU-X, do nothing to the table
-- Table still contains only id=1
-- AUTO_INCREMENT is now 5

三条"被忽略"的 INSERT 将计数器推进了 3。机制相同——自增 ID 值在重复检查之前就已预留；被忽略的 INSERT 不会归还该值。

REPLACE INTO 则不同（而且通常更糟） ​

REPLACE INTO 的语义是"若键重复，则删除旧行并插入新行"。这意味着行的 ID 会改变：

INSERT INTO products (sku, name, stock) VALUES ('SKU-Y', 'PY', 1);
-- id=1, AUTO_INCREMENT=2
REPLACE INTO products (sku, name, stock) VALUES ('SKU-Y', 'PY', 5);
-- Old row (id=1) deleted
-- New row inserted with the next auto-increment value (id=2)
-- AUTO_INCREMENT=3

行的 id 从 1 变成 2。所有对旧 id 的外键引用现在都已断裂（除非配置了 ON DELETE CASCADE/SET NULL）。所有指向 id=1 的外部引用（URL、外部系统、缓存对象）现在都已失效。

REPLACE INTO 有合理的应用场景（例如行的身份由自然键而非代理 id 决定的会话表），但当代理 id 被其他场合使用时，它就变得危险。自增烧损确实存在，身份变更则令其雪上加霜。

耗尽时间 ​

计算自增计数器何时到达其类型的上限：

对于 INT UNSIGNED（最大值 4,294,967,295，约 43 亿）：

• 应用每秒插入 100 行、重复率 0%：约 1.4 年耗尽
• 应用每秒 1000 次 upsert、重复率 90%：计数器每秒推进 1000；约 50 天耗尽
• 应用每秒 10,000 次 upsert、重复率 99%：计数器每秒推进 10,000；约 5 天耗尽

对于 INT SIGNED（最大值 2,147,483,647，约 21 亿）：以上所有时间减半。

对于 BIGINT UNSIGNED（最大值 18,446,744,073,709,551,615，约 1844 亿亿）：以每秒 10,000 次操作计，约 5800 万年耗尽。对任何真实工作负载而言实际上不受限制。

要点：INT（32 位）只对插入/upsert 量适中的表安全。upsert 模式繁重的表应从一开始就使用 BIGINT（64 位）。从 INT 迁移到 BIGINT 可行，但需要锁定表（除非使用 pt-online-schema-change 之类的工具或 MySQL 8.0 的在线 DDL）。

检测：找出存在风险的表 ​

对现有数据库而言，可用以下查询找出接近自增耗尽状态的表：

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    AUTO_INCREMENT,
    (SELECT MAX(id) FROM ... ) AS actual_max_id,  -- per table
    CASE 
        WHEN DATA_TYPE = 'int' AND COLUMN_TYPE LIKE '%unsigned%' THEN 4294967295
        WHEN DATA_TYPE = 'int' THEN 2147483647
        WHEN DATA_TYPE = 'bigint' AND COLUMN_TYPE LIKE '%unsigned%' THEN 18446744073709551615
        WHEN DATA_TYPE = 'bigint' THEN 9223372036854775807
    END AS max_value,
    ROUND(100.0 * AUTO_INCREMENT / CASE 
        WHEN DATA_TYPE = 'int' AND COLUMN_TYPE LIKE '%unsigned%' THEN 4294967295
        WHEN DATA_TYPE = 'int' THEN 2147483647
        WHEN DATA_TYPE = 'bigint' AND COLUMN_TYPE LIKE '%unsigned%' THEN 18446744073709551615
        WHEN DATA_TYPE = 'bigint' THEN 9223372036854775807
    END, 4) AS pct_used
FROM information_schema.tables
JOIN information_schema.columns USING (TABLE_SCHEMA, TABLE_NAME)
WHERE AUTO_INCREMENT IS NOT NULL
  AND EXTRA LIKE '%auto_increment%'
ORDER BY pct_used DESC;

INT 类型使用率超过 10% 的表值得调查。使用率超过 50% 的表需要紧急关注。使用率接近 90% 的表应当已有迁移计划在推进。

actual_max_id 与 AUTO_INCREMENT 之间的差距反映了重复处理烧掉了多少个值。一张 AUTO_INCREMENT = 1,000,000 而 MAX(id) = 100,000 的表，已烧掉 900,000 个值——10 倍的浪费比。

修复方案 ​

修复 1：迁移到 BIGINT（最常见） ​

ALTER TABLE products MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;

简单、有效，覆盖大多数情况。在标准 MySQL/MariaDB 上，迁移期间 ALTER 会阻塞表；在线 schema 变更工具（Percona pt-online-schema-change、GitHub 的 gh-ost、MySQL 8.0 对某些变更的 ALGORITHM=INSTANT）可以做到不阻塞。迁移是单向的（难以回退），这没问题——BIGINT 基本上永远安全。

引用该 ID 的外键列也必须同步迁移为 BIGINT UNSIGNED 以保持一致。使用 PHP 整数的应用代码应能正确处理 BIGINT——现代系统上 PHP 整数是 64 位的。

修复 2：应用层"先检查再更新" ​

用应用层模式取代 INSERT ... ON DUPLICATE KEY UPDATE：

$existing = $pdo->query("SELECT id, stock FROM products WHERE sku = ?", [$sku])->fetch();
if ($existing) {
    $pdo->exec("UPDATE products SET stock = stock + ? WHERE id = ?", [$delta, $existing['id']]);
} else {
    $pdo->exec("INSERT INTO products (sku, stock) VALUES (?, ?)", [$sku, $delta]);
}

该模式避免了自增烧损，但引入了竞态条件：在 SELECT 与 INSERT 之间，另一个连接可能插入相同 SKU 的行，导致 INSERT 因重复键而失败。

处理竞态需要以下任一方式：

• 用 SERIALIZABLE 隔离级别的事务包裹（代价高）
• 检查时使用 SELECT ... FOR UPDATE（锁定空洞（gap lock）；竞态依然存在）
• 遇到重复键错误时重试（可行，但增加复杂度）
• 在操作周围使用应用层分布式锁（Redis SETNX、数据库 advisory locks）

对低并发场景，朴素的模式就够用。对高并发场景，竞态处理增加的复杂度，可能让人觉得为了避开自增烧损而这样做得不偿失。

修复 3：使用 UUID 或外部 ID ​

如果代理 ID 无需连续或简短，可以使用 UUID：

ALTER TABLE products 
    DROP PRIMARY KEY,
    MODIFY id CHAR(36) NOT NULL,  -- UUID as string
    ADD PRIMARY KEY (id);

应用在客户端生成 UUID，并以显式 ID 的方式 INSERT。没有可烧损的自增计数器。权衡：ID 更大（36 个字符，而 int 为 4–8 字节）、UUID 天然无序（可能影响聚簇索引下的插入性能）、URL 与外部引用变长。

MySQL 8.0 的 UUID_TO_BIN() 函数能更高效地存储 UUID（二进制 16 字节，而字符串 36 字节），解决了体积问题。

对真正的高吞吐系统，ULID 或 snowflake ID（按时间排序、由应用生成）提供了"近似连续以利于索引性能"的特性，且没有可烧损的中央计数器。

修复 4：upsert 表不使用自增 ​

如果表的用途是按键存储一行（SKU → 商品数据），自增 ID 可以说纯属冗余。自然键（SKU）已经足够：

CREATE TABLE products (
    sku VARCHAR(50) NOT NULL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    stock INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

基于 SKU 的 upsert 正常工作（VALUES() 或使用 SKU 的 ON DUPLICATE KEY UPDATE）；不存在自增计数器。权衡：其他表的外键引用必须使用字符串 SKU（更大，可能更难高效索引），且自然键必须永远保持唯一（没有级联更新就无法重命名 SKU）。

对查找表和参考数据，这种模式通常很有意义。对外键引用繁重的实体表，通常值得保留自增代理键。

需要避免的陷阱 ​

以为没有 INSERT 发生 AUTO_INCREMENT 就不会增长。实测验证：只要执行了 INSERT 语句它就会增长，无论是否真正插入了行。"自增跟踪下一个未使用的 ID"的心智模型是错的；正确的模型是"自增跟踪的是下一个已预留、但可能用也可能没用的值"。

对 upsert 繁重的表使用 INT。计数器的填满速度快于行数。对任何 upsert 频繁的表，从一开始就用 BIGINT 可避免日后的迁移之痛。

在不了解 ID 代价的情况下依赖 INSERT IGNORE 去重。其自增行为相同。如果模式是"插入 1000 条记录，大部分是重复的，忽略错误"，那就是烧掉 1000 个 ID，只为插入大约 10 行。

在不了解 REPLACE INTO 会删除旧行的情况下使用它。每次替换，行的 ID 都会改变。外键引用、缓存数据、外部引用全部失效。只有当行的身份由自然键决定时才应使用 REPLACE INTO。

想通过设置 innodb_autoinc_lock_mode 来解决问题。没有任何设置能消除重复分支执行造成的 ID 烧损。锁模式影响并发与空洞形态；根本行为不变。

忽视 SHOW TABLE STATUS 的 Auto_increment 值。它是"下一个值"的指示器，而非"当前最大 id"。若该值显著高于 MAX(id)，说明表一直在烧掉 ID。

只迁移 BIGINT 而不迁移外键。连接条件上不一致的列类型会阻止索引使用、损害查询性能。所有引用列应一并更新。

认为自增空洞总是可以安全填补。有些应用把自增 ID 用作外部引用（如 /product/12345 这样的 URL）。填补空洞（通过 ALTER TABLE t AUTO_INCREMENT = <lower>）可能导致旧 URL 指向不同的商品。在操作自增值之前，先弄清外部使用情况。

迷你问答 ​

为什么 MySQL 不把未使用的自增 ID 值归还到池中？

因为正确实现它代价高昂且复杂。自增计数器在连接间共享；归还值需要协调，而协调会产生锁与竞争。计数器还与复制机制交互（二进制日志需要处理"已预留但未使用"的值）。MySQL 的设计选择了"让未使用的值泄漏"这一更简单的行为。大多数工作负载不会触及耗尽；会触及的可以迁移到 BIGINT。

innodb_autoinc_lock_mode = 2 会让问题更严重吗？

略有。模式 2（interleaved）完全并发地分配自增 ID 值，在高并发下可能比模式 1（consecutive）产生更多空洞。但根本行为——UPDATE 分支消耗一个自增 ID 值——在所有模式下都存在。应根据并发需求选择锁模式，而不是为了回避烧损（也回避不了）。

INSERT ... SELECT 遇到重复键会怎样？

行为相同。每行命中重复键都会消耗一个自增 ID 值。含大量重复数据的批量导入会迅速烧掉 ID。需要去重的批量导入模式：要么导入到没有自增的暂存表，再把唯一行复制到主表；要么接受 ID 烧损并规划 BIGINT。

如何安全地将 INT 迁移为 BIGINT？

迁移本身是一次 schema 变更。标准的 ALTER TABLE ... MODIFY id BIGINT UNSIGNED AUTO_INCREMENT 在执行期间会阻塞表。对大型表，请使用在线 schema 变更工具：pt-online-schema-change（Percona）、gh-ost（GitHub），或 MySQL 8.0 内置的在线 DDL（对受支持的变更使用 ALGORITHM=INPLACE、LOCK=NONE）。在同一迁移或协调安排的一系列迁移中更新外键列。

对高吞吐表，UUID 比自增更好吗？

各有取舍。UUID 完全绕开自增计数器——不存在烧损。但 UUID 更大（二进制 16 字节，而 int 为 4–8 字节）、天然无序（可能损害 InnoDB 聚簇索引性能），对 URL 与外部引用也不够友好。对需要分布式生成的真正庞大的表，UUID 通常是正确的选择。对典型 Web 应用，坚持 BIGINT 自增并监控接近上限的情况则更简单。

PostgreSQL 也受此影响吗？

PostgreSQL 的 SERIAL（新版本中的 IDENTITY）使用序列，行为类似——序列值会被中止的事务和不产生行的 INSERT 语句消耗。具体机制不同（PostgreSQL 使用命名序列，MySQL 使用每表计数器），但"插入未发生时 ID 被烧掉"的总体模式相同。PostgreSQL 的 BIGSERIAL（64 位）也常常出于同样原因被使用。

多大的自增使用率百分比适合作为"安全"告警阈值？

取决于增长率。对缓慢增长的表（已用 10%，每年增长 1%）：没问题，无需紧急。对快速增长的表（已用 10%，每月增长 5%）：尽快规划迁移。对任何表（无论增长速度如何，已用 50%）：安排迁移计划。超过 80%：迁移刻不容缓。监控应同时跟踪已用百分比和随时间的增长率。

可以重置自增计数器吗？

可以，通过 ALTER TABLE t AUTO_INCREMENT = <value>。该 <value> 必须大于当前 MAX(id)——MySQL 不允许将其设得低于某个已有 ID。对烧损严重的表，这无济于事——无法把计数器降到当前最大 ID 以下，被烧掉的值已是既成事实。该命令的用途在于预先分配 ID 区间（例如"为管理员创建的行预留 1000–2000 的 ID"），而非回收已烧损的值。

总结 ​

ON DUPLICATE KEY UPDATE 造成的自增烧损是一种微小而隐蔽的浪费，对特定负载模式而言会随时间的推移变得显著。大多数应用从未察觉；upsert 繁重的应用迟早会察觉。一旦识别出来，修复（BIGINT 列类型）就很简单；在触及上限之前识别出问题，才是真正的运维功力。

对使用 MySQL/MariaDB 的 PHP 应用，务实的姿态是：任何 upsert 频繁的表都使用 BIGINT；把自增使用率百分比作为标准数据库健康检查的一部分来监控；在使用率达到 50% 之前规划从 INT 到 BIGINT 的迁移。迁移虽有不便，但为人熟知；触及上限则是一场生产事故。

对接手存在此类问题数据库的团队，审计很简单：运行检测查询，优先处理使用率超过 10% 的表，迁移优先级最高的那些。每次迁移通常只需在一个维护窗口内协调几分钟（大型表用 pt-online-schema-change 则需数小时），并永久消除未来的耗尽风险。

更深的启示：数据库实现因其内部设计选择而存在种种怪癖。有些有明确文档（VARCHAR 上限、JOIN 算法偏好）；有些则很隐蔽（自增烧损、索引前缀限制、字符集排序规则）。建立运维直觉，意味着要能识别数据库的具体行为何时偏离抽象的"关系数据库"模型——并且知道去何处查找针对具体数据库的具体文档化行为。MySQL 的自增行为是有文档的；问题在于开发者默认了一个更理想化的模型。

收尾 ​

products.id 计数器冲到 8.47 亿的那个团队，最终将该列迁移为 BIGINT UNSIGNED。迁移通过 pt-online-schema-change 大约耗时 15 分钟（零停机），另外还对相关表中的外键列做了相应迁移。计数器继续以同样的速度推进（upsert 模式没有改变），但 64 位的上限意味着耗尽"实际上永远不会"。

团队的数据库健康检查中多了一个自增监控仪表盘。该查询每晚在所有数据库上运行；32 位类型上使用率超过 10% 的表会被标记出来供审查。六个月后，另外三张表被发现接近上限；每张都在酿成事故前被主动迁移。

一年后，团队的新项目模板把自增列的默认类型定为 BIGINT UNSIGNED。这一理由被写进了文档："大型表从 INT 迁移到 BIGINT 的代价，远高于从一开始就使用 BIGINT 的字节成本。对任何可能增长的表，BIGINT 都是更安全的默认选择。"新服务直接继承这一约定，无需每个团队重新发现其中的道理。

两年后，团队收购了一家小公司的数据库。对收购的数据库运行自增审计，发现两张表在 INT 列上的使用率已达 80% 以上——按当前增长速度，两者都将在数月内接近耗尽。迁移被作为收购整合的一部分优先安排。"对收购的系统审计已知问题类别"的模式成为标准做法。团队在数据库怪癖方面的运维成熟度延伸到了收购尽职调查中。

"常见问题" ​

1. 为什么 INSERT ... ON DUPLICATE KEY UPDATE 会增加自增计数器？因为 InnoDB 在每条 INSERT 语句开始时、在确定该行究竟会被真正插入还是触发 UPDATE 分支之前，就先预留了自增 ID 值。若 UPDATE 分支执行（发现重复键），预留的值即被丢弃——不会归还到池中。实测验证：连续六条全部触发 UPDATE 分支的 INSERT 语句，将自增计数器推进了六，尽管没有创建任何新行。该行为有文档可查且表现一致；计数器跟踪的不是实际插入的行，而是已预留的值。

2. INSERT IGNORE 也会烧掉自增 ID 值吗？会。INSERT IGNORE 遇到重复键时，会推进自增计数器但不插入任何行。实测验证：对已有 SKU 执行三条 INSERT IGNORE 语句，计数器推进了三。机制与 ON DUPLICATE KEY UPDATE 相同——自增 ID 值在重复检查之前就已预留，INSERT 被跳过时该值被丢弃，而不是归还到池中。

3. REPLACE INTO 与 ON DUPLICATE KEY UPDATE 有什么区别？ON DUPLICATE KEY UPDATE 保留已有行（及其原始 id）并更新指定列。REPLACE INTO 则完全删除已有行，并插入带新自增 id 的新行。当 id 被其他场合使用时，这一区别至关重要：ON DUPLICATE KEY UPDATE 保持外键引用与外部引用不变；REPLACE INTO 使它们断裂，因为 id 变了。两者都会烧掉自增 ID 值，但 REPLACE INTO 还会让身份在每次变更中支离破碎。

4. 什么是 innodb_autoinc_lock_mode，改变它对问题有帮助吗？它控制 InnoDB 在并发访问下如何分配自增 ID 值。模式 0（traditional）：表级锁，ID 连续，并发性能差。模式 1（consecutive，MariaDB 10.x 上的默认值）：按 INSERT 分配区间，并发均衡。模式 2（interleaved，MySQL 8.0+ 基于行的 binlog 下的默认值）：完全并发，空洞更多。改变模式会影响空洞形态，但不会消除 ON DUPLICATE KEY UPDATE 带来的烧损。根本问题——预留值不归还到池中——与锁模式无关。

5. 如何检查自增计数器是否接近上限？查询 information_schema.tables 中的 AUTO_INCREMENT 列，并与该列数据类型的最大值比较：INT UNSIGNED = 4,294,967,295；INT SIGNED = 2,147,483,647；BIGINT UNSIGNED = 约 1844 亿亿。32 位类型使用率超过 10% 的表值得调查；超过 50% 需要积极规划迁移；超过 80% 则刻不容缓。SHOW TABLE STATUS LIKE 'tablename' 命令可直接显示当前的 Auto_increment 值。

6. 如何安全地将 INT 自增列迁移为 BIGINT？对可接受停机的小型表（几百万行以内）：在维护窗口内执行 ALTER TABLE t MODIFY id BIGINT UNSIGNED AUTO_INCREMENT。对大型表或有零停机要求：使用 pt-online-schema-change（Percona toolkit）、gh-ost（GitHub）或 MySQL 8.0 的在线 DDL。引用表中的外键列要一致地迁移——连接中的混合类型会损害查询性能。先在预发布环境用有代表性的数据量测试迁移。

7. 应该用 UUID 代替自增吗？各有取舍。UUID 完全避开自增——不会烧掉，不会耗尽。但 UUID 更大（二进制 16 字节或字符串 36 字节，而 int 为 4–8 字节）、天然无序（可能损害 InnoDB 聚簇索引性能），并产生更不友好的 URL。对需要在多个节点间无需协调地生成 ID 的分布式系统，UUID（或 ULID/snowflake ID）通常是正确的选择。对典型的单数据库应用，BIGINT 加监控通常更简单。

8. PostgreSQL 存在这个问题吗？行为相似，机制不同。PostgreSQL 使用命名序列（通过 SERIAL 或 IDENTITY 列），其值会被中止的事务和被拒绝的插入消耗。具体细节不同（PostgreSQL 的序列是数据库级对象；MySQL 的自增是每表机制），但"序列值被未产生插入行的操作消耗"这一总体模式相同。PostgreSQL 的 BIGSERIAL 对应 MySQL 的 BIGINT AUTO_INCREMENT——同样是对预期高序列消耗的标准修复。

注：本文所有自增行为均在 MariaDB 10.11.14、innodb_autoinc_lock_mode = 1（默认值）下验证。MySQL 8.0 的默认锁模式为 2（interleaved），会产生额外的空洞形态，但不会消除本文所述的 UPDATE 分支烧损。具体自增值与行为在近期 MySQL 与 MariaDB 版本中保持一致；较老的 MySQL 版本（5.6 及更早）默认锁模式不同，但烧损行为类似。耗尽时间计算假设的是理想化负载；实际耗尽时间取决于具体的 INSERT/UPDATE 模式、并发情况以及是否有额外的自增重置。对生产系统，监控实际自增增长速率是预测耗尽的可靠方式；理论计算对初始容量规划有用，但应以真实负载数据加以验证。