mysql 主从自增id 不连续 mysql 自增主键为何不连续

转载

mob64ca13fe62db 2024-04-10 05:04:48

文章标签 mysql 主从自增id 不连续数据库自增主键 mysql 文章分类 MySQL 数据库

39 自增主键为什么不连续

Mysql的innodb的自增主键，由于自增主键可以让主键索引尽量得保持递增顺序插入，避免了页分裂，因此索引更紧凑。

在设计的时候，自增主键是不能保证连续的。

| t39   | CREATE TABLE `t39` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

自增值保存在什么地方

上表插入一行值

(system@127.0.0.1:3306) [test]> insert into t39 values(null,1,1);
在执行show create tables 
| t39   | CREATE TABLE `t39` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |

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

实际上，表的结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值。

不同的引擎对于自增值的报错策略不同

--myisam引擎的自增值保存在数据文件中

--innodb引擎的自增值，其实是保存在内存里，并且到了mysql8.0版本后，才有了”自增值持久化”的能力，如果发生重启，表的自增值可以恢复到mysql重启前的值

---mysql5.7及之前的版本，自增值保存在内存中，并没有持久化，每次重启，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前自增的值。

---在mysql8.0版本后，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

自增值修改机制

在mysql里面，如果字段id被定义为auto_increment，在插一行数据的时候，自增值的行为如下：

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

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

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同，假设，某次要插入的值是x，当前自增的值是y

---1 x<y,那么这个表的自增值不变

---2 x>=y，就需要把当前自增值修改为新的自增值。

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

自增值的修改时机

假设表t39已经有一条记录(1,1,1)，我们在执行一个insert

(system@127.0.0.1:3306) [test]> insert into t39 values(null,1,1);
ERROR 1062 (23000): Duplicate entry '1' for key 'c'

这个语句的执行流程：

--1执行器调用innodb引擎接口写入一行，传入的这一行的值是(0,1,1)

--2 innodb发现用户没有指定自增id的值，获取表t39当前的自增值是2

--3 将传入的行的值改成(2,1,1,)

--4 将表的自增值改成3

--5继续执行插入数据操作，由于已经存在c=1，所以报错duplicate值

可以看到，这个表的自增值改成了3，是在真正执行插入操作的时候，但是这个语句由于报错，id=2的这一行并没有插入成功，但也没有将自增值改回去。

mysql 主从自增id 不连续 mysql 自增主键为何不连续_mysql

当我们在插入一行的时候，自增值变成了3

可以看到，这个操作序列复现了一个自增主键id不连续的现场，可见，唯一键冲突是导致自增主键id不连续的一个原因。

同样的，事务回滚也会产生类似的现象。

insert into t39 values(null,1,1);
begin;
insert into t39 values(null,2,2);
rollback;
insert into t39 values(null,2,2);
// 插入的行是 (3,2,2)

其实，mysql不把自增是回退，是为了提升性能。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯定是要加锁，然后顺序申请

--1 加锁事务a申请到了id=2，事务b申请到了id=3，这时候表t的自增是4，之后继续执行

--2 事务b正确提交了，但是事务a出现了唯一性冲突

--3 如果允许事务a把自增id退回，也就是把表t当前的自增值改回2，那么表里的情况，表里已经有了id=3的行，而当前的自增id是2

--4 接下来，继续执行的其他事务就会申请到id=2，然后在申请到id=3时，就会报错主键冲突。

而为了解决这个主键冲突，有两种办法

--1 每次申请id之前，先判断表里面是否已经存在这个id，如果存在，就跳过这个id，但这个方法成本很高，要去主键索引树上判断id是否存在

--2 把自增id的锁范围扩大，必须等到一个事务执行完并提交，下一个事务才能申请id，这个锁的粒度太大，系统并发能力大大下降。

所以innodb的自增id保证了递增，但不保证是连续

自增锁的优化

Mysql参数innodb_autoinc_lock_mode，默认是1

--1 这个参数为0是，表示采用之前的mysql 5.0版本的设计，即语句执行结束后才释放锁

--2 这个参数位1

---普通的insert语句，自增锁在申请后就立马释放

---类似insert。。。Select这样批量插入，自增锁还是要等语句结束后才被释放

--3 参数为2时，所有的申请自增主键的动作都是申请后就释放。

SESSION A	SESSION B
> insert into t39 values(null,1,1); > insert into t39 values(null,2,2); > insert into t39 values(null,3,3); > insert into t39 values(null,4,4);
	> create table t39_1 like t39;
> insert into t39 values(null,5,5);	> insert into t39_1(c,d) select c,d from t39;

--1 在原库的批量插入数据语句，固定生成连续的id值，所以，自增锁直到语句执行结束后才释放，

--2 在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语句，是因为不知道预先申请多少个id

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

--1 语句执行过程中，第一次申请自增id，会分配1个

--2 1个用完以后，这个语句第二次申请自增id，会分配2个

--3 2个用完以后，还是这个语句，第三次申请自增id，会分配4个

--4 依次类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍

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);

mysql 主从自增id 不连续 mysql 自增主键为何不连续_主键_02