关于MySQL数据库自增主键，说几个容易被忽略的点

作者: 简书冷雨 | 来源:发表于2019-10-21 13:46 被阅读0次

自增主键是指在自增列上定义的主键。自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，索引也更紧凑。

在建表语句中一般是这么定义的：

NOT NULL PRIMARY KEY AUTO_INCREMENT

如：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `step` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

一、自增值保存在哪里

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

MyISAM 引擎：自增值保存在数据文件中；
InnoDB引擎：
- 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。
- 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

二、自增值修改机制

假设数据表当前的自增值是 Y，在插入一行数据的时候，数据库的执行情况如下：

如果插入数据时自增字段指定了具体的值 X，就直接使用值 X 填到该自增字段，该表的自增值是否修改的判断如下：
- 若 X<Y，则该表的自增值不变；
- 若 X≥Y，则需要修改该表的自增值为 X + auto_increment_increment（步长）。
如果插入数据时自增字段未指定值或指定为 0、null，则把该表当前的自增值（即 Y）填到自增字段，并修改该表的自增值为 Y + auto_increment_increment（步长）。

实际上，MySQL 维护着两个系统参数：auto_increment_offset 和 auto_increment_increment，分别用来表示自增的初始值和步长，默认值都为 1。MySQL 修改数据表自增值是从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

在双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突。

三、自增值的修改时机

数据表的自增值修改实际上是在真正插入数据的操作之前。如果没有插入成功，MySQL 也不会将自增值再改回去。也就是说，自增主键有可能会出现不连续的情况。比如在插入数据时出错（如违反唯一键约束），或者事务回滚的情况下，都会导致自增主键不连续。

可通过以下步骤验证：

创建表 t_auto，包含主键、手机号和姓名三个字段，其中主键自增，手机号限制唯一。表结构如下：

CREATE TABLE `t_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `m` (`mobile`)
) ENGINE=InnoDB;

插入一条记录：

insert into t_auto values(null, '18500009999', '小明');

执行 show create table 命令：

show create table t_auto \G;

输出结果如下，可以看到 AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2。

*************************** 1. row ***************************
       Table: t_auto
Create Table: CREATE TABLE `t_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `m` (`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)

ERROR: 
No query specified

查询表数据：

select * from t_auto;

结果如下：

id	mobile	name
1	18500009999	小明

再插入一条数据，手机号和上一条数据一样：
```
insert into t_auto values(null, '18500009999', '小红'); 
```
因为碰到唯一键 m 冲突（手机号要求唯一），所以 id=2 这一行并没有插入成功，错误信息如下：
```
ERROR 1062 (23000): Duplicate entry '18500009999' for key 'm'
```
这个语句的执行流程就是：
1. 执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0, '18500009999', '小红');
2. InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；
3. 将传入的行的值改成 (2, '18500009999', '小红');
4. 将表的自增值改成 3；
5. 继续执行插入数据操作，由于已经存在 mobile=18500009999 的记录，所以报 Duplicate key error，语句返回。

执行 show create table 命令：

show create table t_auto \G;

输出结果如下，可以看到 AUTO_INCREMENT=3，MySQL 没有将自增值再改回去：

*************************** 1. row ***************************
       Table: t_auto
Create Table: CREATE TABLE `t_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `m` (`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)

ERROR: 
No query specified

修改手机号，再重新插入：
```
insert into t_auto values(null, '18500009998', '小红'); 
```
此时查询表数据，
```
select * from t_auto;
```
结果如下：

id mobile name

1 18500009999 小明

3 18500009998 小红

也就是说，出现了自增主键不连续的情况。

同样地，事务回滚也会出现自增主键不连续的情况。比如，

先执行以下事务：

begin;
insert into t_auto values(null, '18500009997', '小江');
rollback;

再执行 how create table 命令：

show create table t_auto \G;

输出结果如下，可以看到 AUTO_INCREMENT=5，MySQL 没有将自增值改回去：

*************************** 1. row ***************************
       Table: t_auto
Create Table: CREATE TABLE `t_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `m` (`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)

ERROR: 
No query specified

再插入数据：
```
insert into t_auto values(null, '18500009996', '小青');
```
查询数据表，
```
select * from t_auto;
```
结果如下：

id mobile name

1 18500009999 小明

3 18500009998 小红

5 18500009996 小青

在数据插入失败时，MySQL 不将自增值改回去是为了提升性能。在申请自增值时，为避免多个并行执行的事务申请到同一个自增 id，在申请的过程中会加锁，然后按顺序执行。自增 id 锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。

继续以上述的表 t_auto 为例，此时表当前自增值为 6：

假设现在有两个并行执行的事务 A 和 B，其中事务 A 申请到了 id=6，事务 B 申请到了 id=7，这时数据表的自增值是 8（即 AUTO_INCREMENT=8），继续执行；

事务 B 正确提交，但事务 A 提交失败，比如出现唯一键冲突；

如果允许自增 id 回退的话，表 t_auto 会把当前的自增值改为 6，此时会出现表的自增值为 6，但表中却存在 id=7 的数据；

接下来其他事务申请自增值就会申请到 id=6，然后再申请到 id=7，这时就会出现插入语句报错“主键冲突”。

解决这个主键冲突，有两种方法：一种是每次申请 id 时，判断这个 id 是否已经存在于表中，若存在，则跳过这个 id；另一种是把自增 id 的锁范围扩大，必须等到事务完成提交，再释放锁，下一个事务才能再申请自增 id。这两种方法都存在性能问题：一个是每次申请 id 都要去主键索引树判断 id 是否存在，另一个是锁的粒度太大，导致系统并发能力大大下降。

因此，为保证 MySQL 的性能，InnoDB 放弃了这个设计，语句执行失败也不回退自增 id。也正是因为这样，所以才只保证了自增 id 是递增的，但不保证是连续的。

四、自增锁的优化

自增 id 锁不是事务锁，在每次申请完就立马释放，以便允许其他事务可以申请。

其实，在 MySQL 5.0 版本的时候，自增锁的范围是语句级别，即一个语句申请了表自增锁，这个锁要等到该语句执行完以后才释放，在此之前其他需要申请表自增锁的语句会被阻塞。此举虽然保证了数据一致性，但是降低了并发度。

在 MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，用户可根据实际情况定制锁策略，该参数有如下值（默认值是 1）：

值为 0（Traditional，传统）时，采用传统锁模式，即采用之前 MySQL 5.0 版本的策略，所有 insert 操作都要申请自增锁，语句执行结束后才释放锁；
值为 1（Consecutive，连续）时：
- 普通 insert 语句，自增锁在申请之后就马上释放；
- 批量 insert 语句，类似 insert … select、replace...select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
值为 2（Interleaved，交错）时，所有的申请自增主键的动作都是申请后就释放锁。

在普通的 insert 语句里面包含多个 value 值的情况下，如以下的 insert 语句：

INSERT INTO `t_auto` (`mobile`, `name`)
VALUES
    ('13987890987', '王朝'),
    ('13987890988', '马汉'),
    ('13987890989', '展昭');

因为这类语句在申请自增 id 的时候，是可以精确计算出需要多少个 id 的，然后一次性申请，申请完成后锁就可以释放了。

而对于批量插入数据的语句（select … insert，replace … select 和 load data 语句），MySQL 有一个批量申请自增 id 的策略（注：该策略是导致自增 id 不连续的第三种原因。）：

语句执行过程中，第一次申请自增 id，会分配 1 个；
1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

我们可以通过以下这个语句序列验证：

-- 创建表step_log
CREATE TABLE `step_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `step` int(11) NOT NULL DEFAULT 1 COMMENT '步长',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert into step_log values(null, 1);
insert into step_log values(null, 2);
insert into step_log values(null, 3);
insert into step_log values(null, 4);

-- 创建表step_log_2，其结构与表step_log相同
create table step_log_2 like step_log;

-- 批量插入数据
insert into step_log_2(step) select step from step_log;

-- 插入数据
insert into step_log_2 values(null, 5);

-- 查询此时表step_log_2数据：
select * from step_log_2;

-- 输出如下，id=5、id=6、id=7 就被跳过了：
+----+------+
| id | step |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  8 |    5 |
+----+------+

引申：为什么默认设置下，insert … select 要使用语句级的锁？为什么这个参数的默认值不是 2？

这么设计还是为了数据的一致性。

新建数据表 step_log，一起来看一下这个场景：往表 step_log 中插入了 4 行数据，然后创建了一个相同结构的表 step_log_2，然后两个 session 同时执行向表 step_log_2 中插入数据的操作。
CREATE TABLE `step_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `step` int(11) NOT NULL DEFAULT 1 COMMENT '步长',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
session A session B

insert into step_log values(null, 1);
insert into step_log values(null, 2);
insert into step_log values(null, 3);
insert into step_log values(null, 4);

create table step_log_2 like step_log;

insert into step_log values(null, 5); insert into step_log_2(step) select step from step_log;

设想一下，如果 session B 是申请了自增值以后马上就释放自增锁，那么就可能出现这样的情况：

session B 先插入了两个记录，(1,1,1)、(2,2,2)；

然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；

之后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4)。

你可能会说，这也没关系吧，毕竟 session B 的语义本身就没有要求表 t2 的所有行的数据都跟 session A 相同。

是的，从数据逻辑上看是对的。但是，如果我们现在的 binlog_format=statement，你可以设想下，binlog 会怎么记录呢？

由于两个 session 是同时执行插入数据命令的，所以 binlog 里面对表 t2 的更新日志只有两种情况：要么先记 session A 的，要么先记 session B 的。

但不论是哪一种，这个 binlog 拿去从库执行，或者用来恢复临时实例，备库和临时实例里面，session B 这个语句执行出来，生成的结果里面，id 都是连续的。这时，这个库就发生了数据不一致。

你可以分析一下，出现这个问题的原因是什么？

其实，这是因为原库 session B 的 insert 语句，生成的 id 不连续。这个不连续的 id，用 statement 格式的 binlog 来串行执行，是执行不出来的。

而要解决这个问题，有两种思路：

一种思路是，让原库的批量插入数据语句，固定生成连续的 id 值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的。

另一种思路是，在 binlog 里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。这种情况，其实就是 innodb_autoinc_lock_mode 设置为 2，同时 binlog_format 设置为 row。

因此，在生产上，尤其是有 insert … select 这种批量插入数据的场景时，从并发插入数据性能的角度考虑，我建议你这样设置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row. 这样做，既能提升并发性，又不会出现数据一致性问题。

需要注意的是，我这里说的批量插入数据，包含的语句类型是 insert … select、replace … select 和 load data 语句。

session A	session B
insert into step_log values(null, 1); insert into step_log values(null, 2); insert into step_log values(null, 3); insert into step_log values(null, 4);
	create table step_log_2 like step_log;
insert into step_log values(null, 5);	insert into step_log_2(step) select step from step_log;

五、自增值达到上限的逻辑

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。

可通过以下步骤验证：

创建表 t，其中只包括一个主增主键，并设置 auto_increment=4294967295

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;

插入一行数据：
```
insert into t values(null);
```

查看此时表 t 的创建语句:

show create table t;

结果如下：

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

再插入一行数据：

insert into t values(null);

则提示错误信息：

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

可以看到，第一次插入数据成功后，这个表的 AUTO_INCREMENT 没有改变（还是 4294967295），导致了第二次插入的时候又拿到相同的自增 id 值，再试图执行时，报主键冲突错误。

（END）

关于MySQL数据库自增主键，说几个容易被忽略的点

一、自增值保存在哪里

二、自增值修改机制

三、自增值的修改时机

四、自增锁的优化

五、自增值达到上限的逻辑

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

id	mobile	name
1	18500009999	小明
3	18500009998	小红
5	18500009996	小青