社区首页 >专栏 >自增主键不连续的几种情况

自增主键不连续的几种情况

AsiaYe

发布于 2020-05-27 09:35:42

3.6K00

代码可运行

文章被收录于专栏：DBA随笔DBA随笔

运行总次数：0

代码可运行

//自增主键不连续的几种情况//

最近在极客时间上学习丁奇大佬的《MySQL 45讲》，这里结合自己的理解分享出来，喜欢的同学可以购买原版课程进行学习，里面的内容很丰富。

自增主键介绍

MySQL中的自增主键想必大家都不陌生，它是通过两个参数来控制的，分别是auto_increment_offset和auto_increment_increment，其中offset代表的是自增主键的开始值，而increment代表的是自增主键每次的增长值。常见的设置，是将这两个参数都设置为1。可以使用show create table语法来查看当前表的自增值。

自增值得生成规则如下：

如果即将插入的值>=当前自增值，那么新的自增值等于当前即将插入的值+1，否则自增值不变。

有的时候，我们会发现自增值

唯一键冲突导致的自增键修改

当我们给表里面插入数据的时候，如果没有指定主键的值，自增长的属性会自动为我们定义主键的值，如下示例:

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` (`c`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
#创建一个表t，其中主键是id，还有唯一键c#先插入一条数据，此时表中的数据是1,1,1。mysql> insert into t values(null, 1, 1);
Query OK, 1 row affected (0.01 sec)

#再插入数据，发现唯一键冲突，自增值已经发生了改变mysql> insert into t values(null, 1, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'c'
#此时表的自增值已经变为3
mysql> show create table t\G*************************** 1. row ***************************
       Table: t
Create Table: 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` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
#此时再重新插入null,2,2这条记录，发现结果跟我们想象的不一样
mysql> insert into t values(null, 2,2);
Query OK, 1 row affected (0.00 sec)

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

上述案例中，虽然第二次插入(null,1,1)失败，但是其实已经浪费了一个主键值了。再次插入(null,2,2)结果就不是我们想象的(2,2,2)了，而是(3,2,2)。

事务回滚导致的自增键不连续

当我们使用回滚事务的时候，如果该事务内部使用了自增值，那么同样会导致表自增主键出问题，示例如下：

mysql> insert into t values (null,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: 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` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (null,2,2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (null,2,2);
Query OK, 1 row affected (0.00 sec)

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

那么为什么MySQL之所以在rollback模式下，不回滚自增主键呢？

其实这个问题的本质是为了避免其他并发事务报主键冲突的错误。假设会话A申请到的自增值是2，会话A提交前，会话B申请了自增值3，并提交了事务。如果会话A可以将自增属性回滚，那么其他事务就会接着从2开始申请自增值，而主键等于3的记录已经存在，那么必然会报错。

如果要解决这个问题，就只能在每次申请自增值之前，查询表里面的数据，然后分配自增值，这样会严重影响MySQL的性能。

MySQL自增锁优化带来的不连续

在MySQL5.7中，参数innodb_autoinc_lock_mode被用来控制自增锁的模式，该参数可以设置为三个值：0、1、2.

a、当该值为0的时候，是等insert语句执行完成之后才释放自增锁；

b、当该值为1的时候，普通的insert语句，自增锁在申请之后马上释放，insert into select语句，自增锁还是要等语句结束之后才释放

c、该值为2的时候，所有的申请自增主键的动作都是申请完成之后就释放锁

之所以对insert into select语句单独处理，是因为这种语句"预先不知道要申请多少个id"，如果我们要select的表有10w行记录，那么要做10w次的申请自增id的动作。MySQL认为这是欠妥当的，因此，对这种批量insert语句，包括load data等，它在内部做了一个自增值生成策略的优化：

1、批量执行的insert语句，第一次申请1个自增id

2、一个id用完了，第二次申请2个自增id

3、2个id用完了，第三次申请4个自增id

例如，我们看下面这个例子：

mysql> truncate table t;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(null, 1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null, 2,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null, 3,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null, 4,4);
Query OK, 1 row affected (0.00 sec)

mysql> create table t2 like t;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2(c,d) select c,d from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(null, 5,5);
Query OK, 1 row affected (0.01 sec)

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