MySQL:自增不连续的几种情况总结

自增实际上是单个表上的一个计数器，对于简单的insert语句来讲肯定都是每次+1的，但是对于批量就有可能预先分配一些。一旦抬升不会因为错误或者回滚而降低，简单总结一下常见的情况，

事务回滚，计数器增加
语句报错，计数器增加
自己填充自增值，抬高计数器
参数innodb_autoinc_lock_mode为2的时候(8.0默认)，insert select 类似批量导入数据，可能导致自增浪费一部分，但是却避免了自增锁，类似

| 1198 | g    |
| 1199 | g    |
| 1200 | g    |  ->gap
| 1256 | g    |  ->gap
| 1257 | g    |

大概的算法为第一次申请1个，第二次申请2个，第三次申请4个 …每次为2的N次方，但是每次分配不能超过AUTO_INC_DEFAULT_NB_MAX(65535)，也就是最大一次分配65535个自增。代码如下，

 nb_desired_values = AUTO_INC_DEFAULT_NB_ROWS * (1 << auto_inc_intervals_count);//每次左移1位，也就是2的auto_inc_intervals_count次方
 nb_desired_values = std::min(nb_desired_values, ulonglong(AUTO_INC_DEFAULT_NB_MAX));

但是值得注意的是，如果有大量insert on duplicate key update的语法，这种语法当插入唯一值的时候在本应该报错唯一键冲突的时候屏蔽报错，转走update流程，但是实际上报错是存在的因此自增也随之提升，当执行下一次插入的时候就会由于自增已经提升而导致很多无谓的gap，这实际上就是第2点。 replace语法也有类似的问题，replace的问题在于会如果触发update机会修改主键自增的值，导致gap。下面是insert on duplicate key update的测试(8.0版本)，

mysql> show create table test123 G
*************************** 1. row ***************************
       Table: test123
Create Table: CREATE TABLE `test123` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `a` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> insert into test123(name,a) values('g',1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test123(name,a) values('g',2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test123(name,a) values('g',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test123(name,a) values('g',4);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test123(name,a) values('g',5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test123(name,a) values('g',6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test123(name,a) values('g',7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test123;
+-----+------+------+
| id  | name | a    |
+-----+------+------+
| 120 | g    |    1 |
| 121 | g    |    2 |
| 122 | g    |    3 |
| 123 | g    |    4 |
| 124 | g    |    5 |
| 125 | g    |    6 |
| 126 | g    |    7 |
+-----+------+------+
7 rows in set (0.00 sec)

mysql> insert into test123(name,a) values('g',1) on duplicate key update name='g';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test123(name,a) values('g',1) on duplicate key update name='g';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test123(name,a) values('g',1) on duplicate key update name='g';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test123(name,a) values('g',1) on duplicate key update name='g';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test123(name,a) values('g',1) on duplicate key update name='g';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test123;
+-----+------+------+
| id  | name | a    |
+-----+------+------+
| 120 | g    |    1 |
| 121 | g    |    2 |
| 122 | g    |    3 |
| 123 | g    |    4 |
| 124 | g    |    5 |
| 125 | g    |    6 |
| 126 | g    |    7 |
+-----+------+------+
7 rows in set (0.00 sec)

这里数据虽然没有变化但是自增最大值变了，再次插入一条数据如下，
mysql> insert into test123(name,a) values('g',8);
Query OK, 1 row affected (0.00 sec)

mysql> select *from test123;
+-----+------+------+
| id  | name | a    |
+-----+------+------+
| 120 | g    |    1 |
| 121 | g    |    2 |
| 122 | g    |    3 |
| 123 | g    |    4 |
| 124 | g    |    5 |
| 125 | g    |    6 |
| 126 | g    |    7 |  ->gap
| 132 | g    |    8 |  ->gap
+-----+------+------+
8 rows in set (0.00 sec)