MySQL自增主键id重启后重复使用问题解析

随着业务数据的增长,MySQL表的主键ID也会不断增大。如果在此过程中删除部分数据,那么MySQL重启后再插入数据,自增主键ID是否会重复使用呢?本文将通过具体示例,解析MySQL自增主键id在重启后是否重复使用的问题。

一、设定初始实验表结构

这里首先创建一个用于测试的表:

sql

CREATE TABLE t(

id INT AUTO_INCREMENT,

num INT,

PRIMARY KEY (id)

);

包含一个自增主键id和一个num字段。

二、插入和删除测试数据

接下来插入1-17条记录:

sql

INSERT INTO t(num) VALUES (1);

…

INSERT INTO t(num) VALUES (17);

然后删除最后3条记录,也就是id=15,16,17的数据:

sql

DELETE FROM t WHERE id IN (15, 16, 17);

查询表记录,目前应有id从1-14的记录:

sql

SELECT * FROM t;

三、重启MySQL服务并插入新记录

接下来重启MySQL服务器,然后插入一条新记录,测试插入记录的id值:

sql

INSERT INTO t(num) VALUES (18);

SELECT * FROM t;

可以看到,重启MySQL后插入的新记录id为18,并没有重复使用已经删除的15,16,17。

四、原理解析

MySQL的自增主键id重启后为什么没有重复使用呢?这是因为MySQL会记录下自增id的最大值,即auto_increment值,它实际存储在信息架构表中:

sql

SHOW TABLE STATUS LIKE ‘t’\G

我们可以看到,当前auto_increment的值是18,就是表中下一个可用的id最大值。

MySQL服务器重启后,会读取信息架构表中的auto_increment值,以确定下一个自增id,从而避免了已经使用的id重复分配问题。

五、自增主键优化策略

针对自增主键id,我们还可以通过以下措施进行优化:

1. 定期使用OPTIMIZE TABLE重建表,回收删除记录的自增id
2. 通过设置更大的自增步长,使id增长缓慢
3. 分表分库后,控制每个表的自增idIncrement,避免单表过大
4. vivo_tmp_xxx临时表可用于生成id,避免影响线上表自增值六、总结MySQL的自增主键id在重启后不会重复使用已经删除的id,这是由其自动保存并恢复auto_increment值的机制保证的。但过度删除会造成自增id过快增长,需要通过优化策略避免。