自增主键

原创徐佩怡 2020-08-14

842

自增主键

控制自增字段的变量：

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 3     |
+--------------------------+-------+
2 rows in set (0.01 sec)
复制

参数的解释如下：
auto_increment_increment和auto_increment_offset 的设计目的是用于循环(source-to-source) 复制,可以用于控制AUTO_INCREMENT列的操作，这两个变量都具有全局值和会话值，并且每个变量都可以采用1到65535（含）之间的整数值。将这两个变量中的任何一个设置为0都会导致其值设置为1。尝试将这两个变量的值设置为大于65,535或小于0的整数会导致其值设置为65,535。

auto_increment_increment
控制连续列值之间的间隔，此参数为N时，自增主键P的下一行将被设置为P+N。
auto_increment_offset
确定AUTO_INCREMENT列值的起点。
::当 auto_increment_offset > auto_increment_increment , auto_increment_offset的值会被忽略。::

自增值的裂缝

设定这两个参数的值：

set auto_increment_increment    = 5   ;  
set auto_increment_offset       = 3   ; 
复制

创建一个如下的表并插入几条数据：

create table t(id int auto_increment primary key);
insert into t values(null),(null),(null);
复制

mysql> select * from t;
+----+
| id |
+----+
|  3 |
|  8 |
| 13 |
+----+
3 rows in set (0.00 sec)
复制

自增字段插入一个指定的值N，下一个自动插入的id会按照3，8，13，18，23的规律继续吗？
当 N < max(id) 时，会成功插入数据，但是下一个分配的id=max(id)+auto_increment_increment
当 N > max(id) 时，会成功插入数据，下一个分配的id=max(id)+auto_increment_increment
当 N = max(id)+5 时，会成功插入数据，下一个分配的id = N+auto_increment_increment

原因一：插入并删除一条数据

如果在执行的插入的时候，发生回滚或者中断，此时id的值不在连续的增加5，会出现裂缝：

对一次insert into t values(null)进行回滚，再次执行insert into t values(null);
复制

原因二：插入冲突

考虑这样的情况：

set    auto_increment_increment= 1    
set    auto_increment_offset   = 1
复制

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
)

insert into table (null,1,1);

查看表结构:
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
复制

表结构中AUTO_INCREMENT=2表示下一次的自增主键的值为2

此时插入一条与唯一索引冲突的数据
insert into t value(null,1,2);
会报错ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘t.c’
再次查看主键的情况：

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
复制

此时下一个自增索引已经变成3。插入一条数据表并且查看表：

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  1 |    1 |    1 |
|  3 |    2 |    2 |
+----+------+------+
2 rows in set (0.00 sec)
复制

自增字段出现了裂缝。

原因三：批量插入

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 的策略：
语句执行过程中，第一次申请自增 id，会分配 1 个；
1 个已经分配，这个语句第二次申请自增 id，会分配 2 个；
2 个已经分配，再次申请，第三次申请自增 id，会分配 4 个；
依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

这个例子里，
第一次申请并分配主键后，得到(1,1,1),
第二次申请并分配主键后，得到(3,2,2),(5,3,3),
第三次申请并分配主键(7,4,4)，(9,null,null),(11,null,null),(13,null,null),但是我门之要求分配一个(7,4,4)，其他三个丢弃。
第四次申请插入(null, 5,5),得到的数据为（15,5,5）

mysql> select * from t2;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  1 |    1 |    1 |
|  3 |    2 |    2 |
|  5 |    3 |    3 |
|  7 |    4 |    4 |
| 15 |    5 |    5 |
+----+------+------+
5 rows in set (0.00 sec)
复制

自增主键是在不断的递增，但是并不是完全符合auto_increment_increment的设置，在有并发量的时候，事务的回滚（rollback）和中断（killed）会造成自增主键的出现裂缝。

复制中的自增主键如何实现？

情况一：binlog_format =ROW，transaction_isolation=READ-COMMITTED

测试库的备库复制的设置如图所示：

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> select * from t;
+----+
| id |
+----+
|  3 |
|  8 |
| 13 |
+----+
3 rows in set (0.00 sec)
复制

此时auto_increment_increment和auto_increment_offset 的设置和主库不同，但是数据是一致，为什么可以保持数据一致性？
解析主库的二进制日志：
show binlog events in 'mysql-bin.000002';
mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000006>6.log
在binlog_format =ROW的格式中，binlog记录的是确切的值，不是SQL语句，所以从库保持了数据一致性，和主机的数据一致。
截屏20200813 下午5.08.19.png

情况二：binlog_format =statement，transaction_isolation=REPEATABLE-READ

mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000008>8.log
截屏20200813 下午10.00.40.png
在binlog_format =statement时，binlog中记录的是SQL语句，主键自增值的一致性依赖insert_id完成，insert_id的意思是，无论之前的自增主键是s什么，都固定使用这个值作为主键值。

如果在RC隔离级别尝试使用binlog_format =statement，mysql会报错并且返回错误1665：ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

auto_increment的保存机制

在MySQL 5.7和更早版本中，自动递增计数器仅存储在主存储器中，而不存储在磁盘上。要在服务器重启后初始化自动增量计数器， InnoDB将在包含AUTO_INCREMENT列的表中的第一次插入时执行以下语句的等效项。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力。每次更改时，当前最大自动增量计数器值都会写入重做日志，并保存到每个检查点的引擎专用系统表中。这些更改使当前的最大自动增量计数器值在服务器重新启动后保持不变。
这才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”。

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

举例来说，
1.如果一个表当前数据行里最大的 id 是 11，AUTO_INCREMENT=16。
2.这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。
3.马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 7。
也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。
截屏20200814 下午5.05.23.png

截屏20200814 下午5.06.17.png
可以看到，在MySQL 5.7的版本中，MySQL重启之后，自动递增计数器仅存储在主存储器中，而不存储在磁盘上。要在服务器重启后初始化自动增量计数器，所以自增主键auto_increment=16重启之后变成了auto_increment=7，并且在下一次写入重新覆盖了id=11的行。

这个测试在MySQL8.0中因为每次更改时，最大自动增量计数器值都会写入重做日志，并保存到每个检查点的引擎专用系统表中。所以id将从16开始，11的id因为之前的delete操作被弃用。

截屏20200814 下午5.15.13.png

总结

对于auto_increment的自增主键，主键时递增的但是可能因为三种原因造成主键不连续：
1.有更改表的事务进行回滚，auto_increment列丢弃对应的auto_increment值。
2.由于表中其他列冲突造成的插入失败，auto_increment列丢弃对应的auto_increment值。
3.由于批量更新，auto_increment列自动分配id造成的auto_increment列分配过多，丢弃多余的auto_increment值。

在MySQL 5.7的版本中，MySQL重启之后，自动递增计数器仅存储在主存储器中，重启后会进行回滚。MySQL8.0中因为每次更改时，最大自动增量计数器值都会写入重做日志，并保存到每个检查点的引擎专用系统表中。

binlog_format的值为statement和row时候，对auto_increment列在binlog记录中略有不同：
binlog_format=row时，binlog准确详细记录每一个列的值。
binlog_format=statement时，通过insert_id的值确定自增主键的值。

影响auto_increment的有关参数：
auto_increment_increment
auto_increment_offset
innodb_autoinc_lock_mode(从MySQL 8.0开始，默认设置为2，在此之前为1。)
binlog_format

在存在复制的架构下，auto_increment_increment 这个值的设计意义也在于控制循环复制，这种复制模式下，强烈推荐binlog_format =ROW，这样可以保证数据一致性，使用innodb_autoinc_lock_mode=2，提高自增主键的并发量，提高性能。

但是我还是不理解“insert… values(null),(null),(null)”。类似这样，插入连续的值的时候，binlog仅仅记录列insert_id，并没有记录此时auto_increment_increment的值，为什么从库可以正确感知主库的auto_increment的间隔？这个还有待继续学习。

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

自增主键

自增主键

控制自增字段的变量：

自增值的裂缝

原因一：插入并删除一条数据

原因二：插入冲突

原因三：批量插入

复制中的自增主键如何实现？

情况一：binlog_format =ROW，transaction_isolation=READ-COMMITTED

情况二：binlog_format =statement，transaction_isolation=REPEATABLE-READ

auto_increment的保存机制

总结

评论

相关阅读