mysql 检查id是否连续 mysql查询不连续的id

转载

mob64ca1417b0c6 2023-12-02 13:30:02

文章标签 mysql 检查id是否连续 mysql 数据库 sql 自增 文章分类 MySQL 数据库

问题现象

又是一个奇葩的例子，今天接到了一个需求，要将1000多万的数据导入到MySQL里面，

具体数据量如下

mysql 检查id是否连续 mysql查询不连续的id_自增

然后将文件中的数据导入到MySQL中的空表中，文件中不包含自增id列

导入完成后查看数据量

mysql 检查id是否连续 mysql查询不连续的id_mysql 检查id是否连续_02

这里看到数据导入成功，和预计的一样，但是

奇葩的来了，老任本来觉得应该最大的自增id应该等于数据条数，毕竟id是自增的

但是查询最大id发现比数据量大了30多万

mysql 检查id是否连续 mysql查询不连续的id_数据库_03

到这里老任就觉得有点奇葩了，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不连续。