当前位置 博文首页 > m0_37389157的博客：MySQL自增id不连续问题
又是一个奇葩的例子，今天接到了一个需求，要将1000多万的数据导入到MySQL里面，
具体数据量如下
然后将文件中的数据导入到MySQL中的空表中，文件中不包含自增id列
导入完成后查看数据量
这里看到数据导入成功，和预计的一样，但是
奇葩的来了，老任本来觉得应该最大的自增id应该等于数据条数，毕竟id是自增的
但是查询最大id发现比数据量大了30多万
到这里老任就觉得有点奇葩了，why？为啥会多出30w的自增ID。
老任突然想起了很久之前看过林晓斌的一篇文章，好像有点印象是和自增锁有关系
所以就去查看了一下自增锁的参数值，含义如下
MySQL5.7
innodb_autoinc_lock_mode:默认值是1
0:语句执行结束完成之后再释放自增锁
1：
普通insert，自增锁在申请之后马上释放
类似于insert....select,load data 等批量导入的SQL语句，自增锁还是要等语句结束后才被释放
2.所有类型的语句都是自增锁在申请之后马上释放
看完这个大家可能还有点迷，简单和大家说一下，其实很简单
在innodb_autoinc_lock_mode=1的时候，
就是当大家进行插入的时候，
如果确定插入多少数据，那么MySQL可以很快的确定需要多少自增id，类似于普通插入insert
但是如果插入的数量未知呢？类似于insert …select.load data,一个一个去申请的话会非常慢，如果按照这个逻辑去申请，我的1000w的数据岂不是要申请1000w次，所以MySQL为了优化这个问题，
采取了预留ID，而且预留id，MySQL通过倍数申请的方式，简单来说第一次申请一个id，第二次申请2个id，第三次申请4个id，每次申请的是上面的两倍，这样的话就减少了申请次数,由于指数级增长，大大减少了消耗次数。
可能文字有点说不清楚，不废话，上例子
root@db1 10:36:08>select * from t7;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 1 |
+----+------+
5 rows in set (0.01 sec)
root@db1 10:36:43>create table t8 like t7;
Query OK, 0 rows affected (0.00 sec)
root@db1 10:38:38>show create table t8;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| t8 | CREATE TABLE `t8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@db1 10:38:41>insert into t8(name) select name from t7;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
root@db1 10:39:12>show create table t8;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t8 | CREATE TABLE `t8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 | //注意这里自增id已经变成8，也就是申请了1，2，4，8
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@db1 10:39:14>select * from t8;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 1 |
+----+------+
5 rows in set (0.00 sec)
这里其实也很简单，就是创建了一个相同表结构的空表，把数据导入进去
大家可以看到t8表插入了五条数据之后自增id变成了8，为什么这么奇怪？
别着急看我分析
首先在参数还是innodb_autoinc_lock_mode:默认值是1
所以在插入五条数据的时候申请了3次ID
第一次申请： 1
第二次申请: 2 3
第三次申请：4 5 6 7
可以看出来我们的自增id第三次申请的时候只用了前两个，在下次申请的时候，因为第三次申请的id处于一个预留状态，并没有使用，但是下次重新插入的时候，需要进行下一次申请，
在下一次插入的时候，需要进行新的批申请，而第三次申请的67 已经被上一次批量申请预留，所以下次直接变成了8
也就是表的自增值变成8的原因。
再回到我们问题本身，我们导入数据的时候是将数据分割成多个小文件，然后批量插入，这样就导致了多次自增id预留未使用所以导致自增id不连续。
导致MySQL自增不连续的问题总结：
1.预留id
2.uk冲突(感兴趣的可以测试一下)
最后对于大家如果线上有很多类似于insert … select，load data
建议将innodb_autoinc_lock_mode设置为2，binlog_format记得设置为_row
这样可以更高效的提高并发
附上官方对自增的模型理解：
AUTO_INCREMENT Handling in InnoDB
