MySQL 自增主键的原理和执行流程

MySQL 的自增主键（AUTO_INCREMENT）是一种非常常用的主键生成策略，尤其适用于整数类型（如 INT、BIGINT、SMALLINT、TINYINT）主键字段，且具有唯一性和连续性。

MySQL 的自增主键是一种高效、简单、可靠的主键生成机制，特别适合单机或主从架构下的业务场景。但在分布式、高并发或需要全局唯一 ID 的系统中，需结合其他方案（如雪花 ID、分布式 ID 服务）。

基本用法：

CREATE TABLE users (
    # 一张表只能有一个 AUTO_INCREMENT 字段
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

特别说明：long 长度是不可能用完的。

每毫秒一百万条数据，也需要 292 年才能用完（292.47 * 365 * 24 * 60 * 60 * 1000 * 1000000）

// hutool实现的雪花算法
//参数1：workerId 终端ID
//参数2：dataCenterId 数据中心id
Snowflake snowflake = IdUtil.createSnowflake(1,1);
long id = snowflake.nextId();
System.out.println("雪花算法1（每次生成一个新对象）实现："+id);

innodb为什么要用自增id作为主键？

由于数据存储、分页都是顺序存储，如索引叶子节点顺序连接。如果使用自增主键，每次插入新记录就会顺序添加到当前索引节点的后续位置，当一页16k写满，就会自动开辟一个新的页。

如果使用非自增主键（如身份证、学号或UUID），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，会导致聚簇索引频繁分裂，频繁的移动、排序、重组树结构和重新分页，导致大量内存碎片和性能低下，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table） 重建表并优化填充页面。

核心原理

自增主键功能的实现主要包含 ID计数器和并发控制，保证ID的原子性、唯一性。

计数器

MySQL 为每个包含自增列的表维护一个计数器，存储在内存和磁盘上。

每个使用 AUTO_INCREMENT 的表在 MySQL 内部都会维护一个计数器，记录下一个将要分配的值。

该计数器的值存储在内存中（对 InnoDB 来说，在 MySQL 8.0 之前存储在数据字典表中，8.0+ 存储在重做日志和系统表空间中，支持持久化）。

-- 查看自增计数器的当前值
SELECT
	AUTO_INCREMENT 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'test' 
	AND TABLE_NAME = 'test';

-- 返回刚插入的自增ID
SELECT LAST_INSERT_ID();

不同的引擎对于自增值的保存策略不同：

MyISAM 引擎：存储在数据文件中。

InnoDB 引擎：不同版本AUTO_INCREMENT 值处理不同。

MySQL 5.7 及之前：存储在内存中，没有持久化，在重启时通过 SELECT MAX(id) FROM table for update 重新初始化。第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+步长作为这个表当前的自增值。可能重复（若之前插入未提交）。

MySQL 8.0+：将自增值持久化到 redo log 和系统表空间，重启后能准确恢复，避免重复。

分配逻辑

插入记录时，若未提供主键值，MySQL 会：

读取当前 AUTO_INCREMENT 计数器值；
将该值作为新记录的主键；
将计数器 +1，准备为下次分配。

-- 修改已有表的自增起始值
ALTER TABLE users AUTO_INCREMENT = 1000;

-- 设置全局自增步长（需要在配置文件中设置）
-- [mysqld]
-- auto_increment_increment = 2    -- 每次增加2
-- auto_increment_offset = 1       -- 从1开始

并发控制

InnoDB 引擎使用表级自增锁（AUTO-INC lock） 来保证并发插入时自增值的唯一性（MySQL 5.1.22 之后引入了更灵活的锁模式，可通过 innodb_autoinc_lock_mode 配置）。

MySQL 8.0 之前有三种自增锁模式，不同的 innodb_autoinc_lock_mode 值影响并发性能和主键连续性：

0（传统模式）：表级锁，保证连续但并发差；
1（默认，连续模式）：批量插入时用表锁，简单插入用轻量锁；
2（交错模式）：无表锁，高并发但可能不连续（申请自增ID → 获取互斥量 → 分配ID → 释放互斥量）。

-- 查看当前自增锁模式
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

-- 0: traditional（传统模式）
-- 1: consecutive（连续模式，默认）
-- 2: interleaved（交错模式）

模式	描述	优缺点
0 - Traditional	表级锁，保证连续性和确定性	安全但性能较差
1 - Consecutive	轻量级锁，批量插入时使用表级锁	平衡性能和连续性
2 - Interleaved	无锁模式，最高性能（类似于CAS）	性能最好但不保证连续性

执行流程

新的自增值生成算法是：从auto_increment_offset（初始值）开始，以auto_increment_increment（步长）为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值

1.如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段。

2.如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

假设，某次要插入的值是X，当前的自增值是Y

1.如果X<Y，那么这个表的自增值不变

2.如果X>=Y，就需要把当前自增值修改为新的自增值

执行流程如下：

insert into t values(null, 1, 1); 插入第一条数据

解析 SQL 语句，判断是否显式提供了主键字段 id 的值。

执行器调用InnoDB引擎写入一行，传入的这一行的值是(0,1,1)
- 显式指定 id，直接使用指定值，跳过自增逻辑。此时会先检查是该值是否已存在于表中。
  - 如果存在 → 报错（主键冲突）；
  - 如果不存在 → 允许插入（无论这个值是否小于当前最小主键、是否为负数、是否小于当前计数器值）；
  - 当显式插入一个大于当前 AUTO_INCREMENT 值的主键时，MySQL 会自动将计数器调整为 MAX(id) + 1。
    但如果插入的值 ≤ 当前计数器，则计数器保持不变。
- 未指定 id 或为 NULL 或为0，触发 AUTO_INCREMENT 机制。
获取下一个自增值。

获取表t当前的自增值1，将传入的值改成(1,1,1)
1. 访问表的 AUTO_INCREMENT 计数器（内存中的值）；
2. 根据 innodb_autoinc_lock_mode 决定锁策略：
  - 简单插入（如单行 INSERT）：使用轻量级互斥锁（mutex），快速获取并递增计数器；
  - 批量插入（如 INSERT ... SELECT）：可能申请表级 AUTO-INC 锁，预分配一批值；
3. 将当前计数器值作为新记录的 id；
4. 将计数器 +1（或 +N，批量时）。
写入记录到聚簇索引

InnoDB 使用 聚簇索引（Clustered Index），数据行按主键物理存储。
- 将新记录（含自动生成的 id）写入聚簇索引的适当位置；
- 由于自增主键是递增的，新记录总是追加到索引末尾，避免页分裂，写入性能高。
将表的自增值改成2
事务处理

如果 INSERT在事务中，自增值一旦分配，即使事务回滚也不会回收（防止并发冲突）。

因此可能出现 ID 不连续（例如事务 A 分配了 id=5，回滚后下一条记录是 id=6）。

优化自增锁

自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。

在MySQL5.0版本之前，自增锁的范围是语句级别，生命周期不跟随事务，而是跟随SQL 语句的执行。如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放。

任何包含 AUTO_INCREMENT 的 INSERT 语句（包括 INSERT ... SELECT、LOAD DATA 等）会获取一个表级 AUTO-INC 锁；

该锁一直持有到整个语句执行完毕才释放；即使是简单的单行插入，也会阻塞其他并发插入。

MySQL5.1.22 版本后引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是1

innodb_autoinc_lock_mode=0，表示采用之前MySQL5.0版本的策略，即语句执行结束后才释放锁。

innodb_autoinc_lock_mode=1

简单插入（普通insert语句）：申请完自增值就立即释放锁（甚至在语句执行完之前）；

批量插入（insert … select）：锁持有到语句结束后才被释放。

InnoDB 会 在语句开始时尝试估算需要多少行，然后 一次性分配足够多的自增值

整个语句持有 AUTO-INC 表锁，直到语句结束；

因此，即使事务未提交，其他会话也可以继续申请新的自增值。

innodb_autoinc_lock_mode=2，所有申请自增主键的动作都是申请后就释放锁。

对于简单插入，在语句执行前就能确定插入行数的语句，预分配所需数量的自增值（如 2 行就申请 2 个）；

对于批量插入数据的语句，MySQL通过 原子计数器，按需一次性分配所需数量。多个并发语句的 ID 可能交错， ID 可能出现不连续。

-- 查看当前模式（默认值是1）
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

-- 设置（需在 my.cnf 中配置，重启生效）
[mysqld]
innodb_autoinc_lock_mode = 1

在 主从复制（statement-based） 环境中，mode=2 可能导致主从 ID 不一致，建议使用 ROW 格式复制时再启用 mode=2

语句类型	传统模式	连续模式	交错模式
简单插入	连续	连续	可能不连续
批量插入	连续	连续	可能不连续
混合插入	连续	连续	可能不连续

应用与不足

不适用于分布式系统：多个 MySQL 实例无法保证全局唯一（需用雪花算法、UUID 等）；
可能不连续：事务回滚、批量插入失败、手动指定值等都会导致 ID 跳跃；
有上限：如 INT 最大为 2147483647，超限会报错（可改用 BIGINT）；
重复风险：在极端情况（如手动设置 AUTO_INCREMENT 值小于当前最大值）或旧版本 MySQL 重启可能重复（8.0+ 已解决）。

支持多主键

mysql 可以存在多个主键。但是唯一性是多个主键的组合（组合索引）。

单个键不再有唯一性，且只能有一个自增键

空洞问题

唯一键冲突和事务回滚都会导致自增主键id不连续，造成ID浪费的情况

-- 插入数据
INSERT INTO users (name) VALUES ('用户1');  -- id=1
INSERT INTO users (name) VALUES ('用户2');  -- id=2

-- 删除一条记录
DELETE FROM users WHERE id = 2;

-- 再插入新记录
INSERT INTO users (name) VALUES ('用户3');  -- id=3，不是2

-- 回滚事务也会产生空洞
START TRANSACTION;
INSERT INTO users (name) VALUES ('用户4');  -- id=4
ROLLBACK;
INSERT INTO users (name) VALUES ('用户5');  -- id=5

重置自增值

-- 重置自增值为当前最大值+1
ALTER TABLE users AUTO_INCREMENT = 1;

-- 清空表并重置自增值
TRUNCATE TABLE users;

主从复制

在复制环境中，需要确保自增值的一致性：

-- 设置自增偏移和步长，避免多主复制冲突
-- 服务器1配置：
auto_increment_increment = 2
auto_increment_offset = 1

-- 服务器2配置：
auto_increment_increment = 2
auto_increment_offset = 2

实际应用

分表场景下的自增ID，设置不同的起始值，如：第二张表 AUTO_INCREMENT = 1000000;
复合主键中的自增设置 CREATE TABLE orders ( user_id INT, order_seq INT AUTO_INCREMENTPRIMARY KEY ( user_id, order_seq ) );
选择合适的自增类型，避免自增值耗尽，导致自增失败
尽量使用批量插入方式，可以减少自增锁的获取次数

自增值不连续排查

-- 查找空洞
SELECT
	t1.id + 1 AS missing_id 
FROM
	test t1
	LEFT JOIN test t2 ON t1.id + 1 = t2.id 
WHERE
	t2.id IS NULL 
	AND t1.id < ( SELECT MAX( id ) FROM test );

将会查出所有丢失的所有ID（最近一条）