MySQL自增主键id跳跃问题

目录

• 0. 背景
• 1. 模拟
  • 1.1 初始化
  • 1.2 数据迁移
• 2. MySQL自增主键
  • 2.1 自增主键的分配机制
  • 2.2 为什么是2ⁿ-1
  • 2.3 innodb_autoinc_lock_mode参数
  • 2.4 核心源码文件
    • 2.4.1 预分配算法
    • 2.4.2 锁机制与并发控制
• 3. 总结&建议

0. 背景

最近同步数据遇到一个MySQL自增主键缺失的现象，使用INSERT INTO SELECT将table_source的数据分批次同步到table_target，table_target表的主键为自增主键，由系统自动维护，insert的时候没有指定。

在多次执行INSERT INTO SELECT后，却发现有些主键不见了，但迁移的数据并没有缺失。

1. 模拟

1.1 初始化

省略无关的逻辑，简单模拟下，存在两张表：

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户id',
  `user_name` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户信息表';

CREATE TABLE `user_info_temp` (
  `user_id` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '用户id',
  `user_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户信息表';

user_info_temp为源表，主键为user_id；user_info为目标表，主键为自增主键。

user_info_temp插入200条测试数据。

1.2 数据迁移

使用下面语句分批次对数据进行迁移

insert into user_info(user_id, user_name)
select user_id, user_name
from user_info_temp
limit #{size};

在数据表初始化后，执行以下语句，即size依次为：1、2、3、4、7、8、1（分7个批次，一共26条数据）

insert into user_info(user_id, user_name) select user_id, user_name from user_info_temp limit 1;
insert into user_info(user_id, user_name) select user_id, user_name from user_info_temp limit 2;
insert into user_info(user_id, user_name) select user_id, user_name from user_info_temp limit 3;
insert into user_info(user_id, user_name) select user_id, user_name from user_info_temp limit 4;
insert into user_info(user_id, user_name) select user_id, user_name from user_info_temp limit 7;
insert into user_info(user_id, user_name) select user_id, user_name from user_info_temp limit 8;
insert into user_info(user_id, user_name) select user_id, user_name from user_info_temp limit 1;

查询user_info

id	user_id	user_name
1	USER0001	赵0001
2	USER0001	赵0001
3	USER0002	钱0002
5	USER0001	赵0001
6	USER0002	钱0002
7	USER0003	孙0003
8	USER0001	赵0001
9	USER0002	钱0002
10	USER0003	孙0003
11	USER0004	李0004
15	USER0001	赵0001
16	USER0002	钱0002
17	USER0003	孙0003
18	USER0004	李0004
19	USER0005	周0005
20	USER0006	吴0006
21	USER0007	郑0007
22	USER0001	赵0001
23	USER0002	钱0002
24	USER0003	孙0003
25	USER0004	李0004
26	USER0005	周0005
27	USER0006	吴0006
28	USER0007	郑0007
29	USER0008	王0008
37	USER0001	赵0001

可以看到主键4缺失了，主键11直接跳到了15，主键29直接跳到了37。

26条数据均同步成功，但自增主键却来到了37！！！

通过观察发现这样的规律：

系统每次会分配 2ⁿ-1 个主键，而如果插入数据小于分配的主键个数，则剩余的主键会被直接丢弃。

因此，对于上面的模拟，size为2的时候，系统会分配3个主键id（2、3、4），但是因为只有2条数据，所以主键4被丢弃了。并且单批次同步的数据越大，丢弃的主键数可能会越多，比如每次同步100条数据，系统会分配127个主键id，这个时候就会有27个主键被丢弃。

2. MySQL自增主键

好吧，以上是观察到的现象。一开始检索这个问题，有帖子说是因为事务回滚，导致自增主键id被跳过了。但很明显这个现象不是这个问题造成的。

笔者推测是MySQL系统为了确保主键唯一，减少在多线程插入时频繁分配主键id所带来的系统开销，于是干脆一次性分配更多主键id。但检索了好久查到的文章说法不一或者毫无关联。于是直接问DeepSeek，让它帮忙检索。

2.1 自增主键的分配机制

在MySQL中，当使用INSERT INTO SELECT语句进行批量数据迁移时，如果目标表的主键是自增的，InnoDB存储引擎会通过预分配自增主键区间来优化性能。InnoDB的自增主键分配策略通过innodb_autoinc_lock_mode参数控制（默认值为1，即“连续模式”）。在该模式下：

• 单行插入：按需分配自增值，每次分配一个。

• 批量插入（如INSERT INTO SELECT）：预分配自增区间，以减少锁竞争。

2.2 为什么是2ⁿ-1

InnoDB根据SELECT结果的行数（假设为N）预分配自增区间。InnoDB的预分配策略采用指数增长算法，目的是通过指数级扩展预分配区间，减少频繁申请自增值的次数，从而降低锁争用。

2.3 innodb_autoinc_lock_mode参数

• innodb_autoinc_lock_mode=0（传统模式）：使用表级锁，每次分配1个自增值，性能差。

• innodb_autoinc_lock_mode=1（默认）：批量插入时预分配区间（2ⁿ-1）。

• innodb_autoinc_lock_mode=2（交错模式）：自增值可能不连续，但并发性能更高。

2.4 核心源码文件

自增主键的分配逻辑主要集中在以下文件中：

• storage/innobase/handler/ha_innodb.cc：InnoDB存储引擎的核心处理逻辑。

• storage/innobase/row/row0mysql.cc：处理行级操作的模块，包括自增主键的生成。

• storage/innobase/include/dict0dict.h：定义自增主键相关的数据结构。

2.4.1 预分配算法

在ha_innodb.cc中，函数handler::update_auto_increment负责处理自增主键的分配。当检测到批量插入时，会调用ha_innobase::get_auto_increment方法，触发预分配逻辑：

// 预分配自增主键的逻辑
if (increment > 1) {
	// 计算需要预分配的数量
	ulonglong next_auto_inc = ...; // 基于当前自增值
	ulonglong need = increment * 2 - 1; // 2^n -1的算法

	// 更新自增计数器
	dict_table_autoinc_update_if_greater(table, next_auto_inc + need);
}

这里的need变量即为 2ⁿ-1 的计算结果，用于扩展预分配区间。

2.4.2 锁机制与并发控制

在row0mysql.cc中，函数row_insert_for_mysql会通过dict_table_autoinc_lock获取自增锁。当检测到批量插入时，锁的释放策略由innodb_autoinc_lock_mode参数控制：

• 模式1（默认）：批量插入语句（如INSERT INTO SELECT）执行完成后才释放锁，确保预分配的连续性。

• 模式2：立即释放锁，但可能导致自增值不连续。

3. 总结&建议

1. 在MySQL中，当使用INSERT INTO SELECT语句进行批量数据迁移时，如果目标表的主键是自增的，InnoDB存储引擎会通过预分配自增主键区间来优化性能。通过2ⁿ-1的预分配策略来平衡了性能与锁开销。

   • 优点：减少自增锁的请求次数，提升高并发下的吞吐量。

• 代价：可能产生自增值的“空洞”（如事务回滚或预分配未用完，即本文一开头描述的现象）。

2. 如果使用的是以下批量插入方式，则不会触发该策略

   insert into user_info(user_id, user_name)
   values
       ('USER0001', '赵0001'),
       ('USER0002', '钱0002'),
       ('USER0003', '孙0003'),
       ('USER0004', '李0004');