人人都会AI编程

25.4 自增主键耗尽、空洞问题

更新时间：2026-07-11

自增主键（AUTO_INCREMENT）是 MySQL 中最常用的主键生成方式。它简单、高效，能保证主键唯一且大致递增，有助于减少 B+ 树索引的页分裂。但在长期运行的系统中，自增主键也可能带来两个麻烦：值耗尽和空洞。理解它们的成因和应对方式，是数据库设计的基本功。

自增主键耗尽：数据还没满，主键先不够用了

自增主键的取值上限取决于列类型。常用的是 INT 和 BIGINT：

  • INT（有符号）：范围是 -2,147,483,648 到 2,147,483,647，约 21 亿。如果设为无符号（INT UNSIGNED），则范围是 0 到 4,294,967,295，约 42 亿。
  • BIGINT（有符号）：约 ±9.22 亿亿，无符号则为 0 到 18,446,744,073,709,551,615，约 1844 亿亿。

对于大多数业务系统，BIGINT 的主键上限大到几乎可以忽略耗尽问题。但如果使用了 INT，在数据量快速增长的系统（比如日均百万级写入的日志表、交易流水表）中就有可能撞上这个天花板。一旦自增主键到达上限，再插入数据就会报错：

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

此时数据库无法写入，服务直接中断，这在高并发线上环境是致命的。

如何判断自己是否面临耗尽风险？

最直接的方法是查询当前最大自增值：

SELECT MAX(id) FROM your_table;
SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

然后结合业务增长速度（比如每天增长多少条，折合每年多少），估算出耗尽时间。例如，每日新增 500 万条记录，使用 INT 有符号类型（21 亿），大约 420 天就会耗尽。这个计算很简单，但很多团队在设计表结构时容易忽略，直到告警响起才意识到问题。

应对策略：

  • 优先使用 BIGINT：这是最稳妥的做法。在存储成本几乎无差别的今天（BIGINT 只比 INT 多占 4 字节），从设计之初就用 BIGINT 作为主键，可以一劳永逸。现在新建表，绝大部分都应直接上 BIGINT。
  • 已有 INT 主键表的补救：如果表已经用 INT 且数据量很大，修改列类型（ALTER TABLE ... MODIFY COLUMN id BIGINT）会锁表并在后台进行数据拷贝，耗时可能很长。可以在业务低峰期执行，或者借助在线 DDL 工具（如 gh-ost、pt-online-schema-change）来减少影响。
  • 另辟蹊径：对于巨量流水表，可以使用分布式 ID 方案（如雪花算法、美团 Leaf 等）生成全局唯一的字符串或数值主键，绕开自增机制。

自增主键的空洞：不连续是正常现象，但需要正确对待

许多开发者存在一种执念：主键 ID 必须连续且无缺号。其实，自增主键出现空洞是 MySQL 的正常行为，它不保证连续。空洞的来源主要有以下几种：

  1. 事务回滚：一个事务获取了自增值（比如 INSERT 语句），但后续回滚了，这个值不会退回给自增计数器，造成空洞。这是因为如果允许重用回滚的 ID，在并发场景下需要维护复杂的锁，代价远大于浪费几个数值。
  2. 删除数据：DELETE 删除某些行后，后续插入不会回填这些值。只会从当前最大自增值继续递增。
  3. INSERT ... ON DUPLICATE KEY UPDATE：当键冲突发生时，即使语句最终执行的是更新而不是插入，自增列也会被请求一个新的值（在特定处理模式下更新也会消耗值）。
  4. 批量插入分配过多：InnoDB 在执行不确定行数的批量插入时（如 INSERT ... SELECT），会预先分配一段自增值区间。如果实际插入的行数比预留的少，多余的 ID 就被“浪费”了。
  5. innodb_autoinc_lock_mode 的影响：不同锁定模式下自增值的分配策略不同，可能导致空洞程度不同。默认模式 2（交叉锁）性能最好，但空洞也更容易出现。

空洞是不是问题？

从数据库功能上讲，主键的唯一性和索引排序的稳定性才是关键，连续性不是必需品。只要你不需要用主键 ID 的数值做业务含义（比如“第 9527 号用户”），空洞就完全无所谓。即使你用主键做分页（LIMIT 100000, 10），也不依赖连续性。

但是，如果业务逻辑中隐含了对连续性的期待，比如用自增 ID 做订单号的唯一标识，并且要求订单号严格递增且无缺号（虽然这种设计本身就是不推荐的），你就会认为空洞是个 bug。这种情况表明设计有问题：订单号应该单独用业务序列生成，不依赖数据库自增主键。

实际建议：

  • 不要把业务含义绑定在自增主键上。自增主键只作为逻辑上的唯一标识，只用于内部关联和索引，不对外暴露给用户，也不作为订单号、流水号等业务含义。对外标识可以用独立的字符串或数值列，通过 Redis 自增、雪花算法等生成。
  • 接受空洞，不试图修补。不要尝试 ALTER TABLE t AUTO_INCREMENT = 100 来填洞，这在高并发下几乎无法做到不留空，还可能引入主键冲突。
  • 监控自增使用率：对 INT 型自增列，可以通过 (MAX(id) / 类型上限) 计算使用率并设置告警（比如使用超过 70% 时预警），而不是等耗尽时才处理。

简而言之，自增主键是数据库最常见的主键方案，只要选对类型、不绑定业务语义，它就十分可靠。设计时记牢两条：能上 BIGINT 就别用 INT，能接受不连续就别纠结空洞。 这两条原则可以让你避开绝大多数自增相关的坑。