MySQL45讲第三十九讲自增主键为什么不是连续的？——阅读总结_为什么 mysql 的自增主键不单调也不连续-CSDN博客

文章目录

MySQL45讲第三十九讲自增主键为什么不是连续的？——阅读总结

MySQL45讲第三十九讲自增主键为什么不是连续的？——阅读总结

在 MySQL 数据库的使用中，自增主键是一个常见的概念。它通常被设计为在插入数据时自动生成唯一的标识符，以确保数据的完整性和一致性。然而，你可能会发现，自增主键并不总是连续的，看看什么情况下自增主键会出现 “空洞”？

一、自增主键不连续的原因

表：

CREATE table `t` (
  `id` int(11) NOT NULL auto_increment,
  `c` INT(11) DEFAULT NULL,
  `d` INT(11) DEFAULT NULL,
  PRIMARY KEY(`id`),
  UNIQUE KEY(`c`)
)ENGINE = InnoDB;

1. 唯一键冲突

当插入数据时，如果表中存在唯一键约束，并且插入的值违反了该约束，就会导致插入失败。但此时，自增值已经递增，不会回退。例如，我们有一个表 t，其中 id 是自增主键，c 是唯一索引。执行以下插入语句：

insert into t values(null, 1, 1);
insert into t values(null, 1, 1);

第一条插入语句成功，此时自增值变为 2。但第二条插入语句因 c 字段的唯一键冲突而失败，然而自增值不会变回 1，后续插入将从 3 开始，导致主键不连续。

在这里插入图片描述

2. 事务回滚

在事务中，如果插入数据后发生回滚，自增值同样不会回退。例如：

insert into t values(null, 1, 1);
begin;
insert into t values(null, 2, 2);
rollback;
insert into t values(null, 2, 2);

在这个例子中，事务中的插入操作回滚了，但自增值在事务执行过程中已经递增，所以最后插入的数据行的 id 为 3，而不是 2，出现了主键不连续的情况。

在这里插入图片描述

3. 批量插入数据

对于批量插入数据的语句，如 insert … select、replace … select 和 load data 等，MySQL 有其特殊的自增 id 分配策略。它会在语句执行过程中逐步分配自增 id，且每次分配的数量呈指数增长。如果实际使用的 id 数量小于分配的数量，就会造成自增 id 的浪费，进而导致后续插入的主键不连续。

因此，对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

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

假设我们有表 t 和 t2，执行以下语句：

insert into t values(null, 1, 1);
insert into t values(null, 2, 2);
insert into t values(null, 3, 3);
insert into t values(null, 4, 4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5, 5);

insert into t2 (c,d) select c,d from t; 这条语句实际插入了 4 行数据，但它分三次申请自增 id，第一次申请到 id = 1，第二次分配了 id = 2 和 id = 3，第三次分配到 id = 4 到 id = 7。由于只用了 4 个 id，id = 5 到 id = 7 被浪费，后续插入 t2 的值 (null, 5, 5) 时，实际插入的行的 id 为 8，导致主键不连续。

在这里插入图片描述

二、自增值的存储与修改机制

1. 自增值的存储位置

在 MySQL 中，不同存储引擎对自增值的存储策略有所不同。

MyISAM 引擎将自增值保存在数据文件中，而 InnoDB 引擎在 MySQL 8.0 版本之前，自增值是保存在内存中的，并且没有持久化。这意味着每次重启 MySQL 实例后，InnoDB 表的自增值会根据表中当前最大的 id 值重新计算。例如，如果一个表当前最大的 id 是 10，重启后，自增值将从 11 开始。
从 MySQL 8.0 版本开始，InnoDB 引擎实现了自增值持久化，通过 redo log 记录自增值的变更，重启时依靠 redo log 恢复之前的值。

2. 自增值修改时机

当插入数据时，如果 id 字段指定为 0、null 或未指定值，InnoDB 引擎会获取当前表的自增值，并将其填入自增字段，然后将自增值递增。但如果在这个过程中发生唯一键冲突或事务回滚，自增值不会回退。这是因为 MySQL 为了提升性能，避免了在冲突或回滚时对自增值进行回退操作。如果回退自增值，可能会导致并发事务申请到相同的自增 id，从而引发主键冲突，解决主键冲突的两种方法（每次申请 id 前判断是否存在或扩大自增 id 锁范围）都会带来性能问题。

三、自增锁的优化

1. 自增锁的历史演变

在 MySQL 5.0 版本时，自增锁的范围是语句级别，即一个语句申请了自增锁后，要等语句执行结束才释放，这严重影响了并发度。MySQL 5.1.22 版本引入了新的参数 innodb_autoinc_lock_mode，默认值为 1，对自增锁的行为进行了优化。

2. 不同参数值下的自增锁行为

当 innodb_autoinc_lock_mode = 0 时，采用 MySQL 5.0 版本的策略，语句执行结束后释放自增锁。
当 innodb_autoinc_lock_mode = 1 时（默认值），普通 insert 语句申请自增锁后会立即释放，而类似 insert … select 这样的批量插入数据语句，自增锁仍需等语句结束后释放。
当 innodb_autoinc_lock_mode = 2 时，所有申请自增主键的动作都是申请后立即释放锁。

3. 批量插入数据与数据一致性

对于批量插入数据的语句，如 insert … select，在 innodb_autoinc_lock_mode = 1 的默认设置下，使用语句级锁是为了保证数据的一致性。考虑以下场景：

session A	session B
insert into t values(null, 1,1); insert into t values(null, 2,2); insert into t values(null, 3,3); insert into t values(null, 4,4);
	create table t2 like t;
insert into t2 values(null, 5,5);	insert into t2(c,d) select c,d from t;

如果 session B 在申请自增值后立即释放自增锁，可能会导致生成的 id 不连续。在 binlog_format = statement 的情况下，binlog 记录的更新日志可能会导致备库或临时实例在执行时出现数据不一致。为解决此问题，一种思路是让批量插入数据语句固定生成连续的 id 值（即使用语句级锁），另一种思路是在 binlog 中如实记录插入数据的操作，到备库执行时不再依赖自增主键生成（此时需将 innodb_autoinc_lock_mode 设置为 2 且 binlog_format 设置为 row）。