本文共 2442 字，大约阅读时间需要 8 分钟。
MySQL自增主键(AUTO_INCREMENT)的行为在不同版本中存在显著差异，这种差异可能对数据库的稳定性和数据一致性产生深远影响。本文将从MySQL5.7和MySQL8.0两个版本入手，详细分析自增主键的工作原理及其在数据库重启时的行为变化。
在MySQL5.7版本中，自增主键的分配规则主要由InnoDB数据字典中的一个内存计数器控制。该计数器的工作原理是基于数据库表中已有的记录数进行动态计算。具体来说，当数据库运行时，自增主键的计数器会根据当前表中最大的主键值来决定下一个自增值。
MySQL5.7版本的自增主键计数器是基于内存中的一个变量来维护的。当数据库运行时，计数器会根据表中记录的最大值来动态调整。然而，这个计数器的值并不会持久化到磁盘中。因此，在数据库重启时，InnoDB会根据以下逻辑重新初始化计数器：
select max(ai_col) from table_name for update;
这一步操作会读取表中当前的最大自增值，并将其作为新的计数器起点。这种方式虽然能够确保自增主键的正确性，但也带来了一个潜在的问题：在数据库重启时，自增主键的值会被重置为max(primary key) + 1，这可能导致业务逻辑中的主键冲突或其他不易察觉的问题。
为了更好地理解这一现象，我们可以通过以下步骤进行实验：
创建测试表
create table test_1 (id int auto_increment primary key, name varchar(50));
插入数据
插入三条记录：insert into test_1 (name) values ('zhangsan'), ('lisi'), ('wangwu');此时，表中记录为：
select * from test_1;
输出显示自增id依次为1、2、3。
删除记录
删除id=3的记录：delete from test_1 where id = '3';
删除后，表中记录为：
select * from test_1;
自增id依然为1、2。
重新插入数据
插入一条新记录：insert into test_1 (name) values ('zhaoliu');此时，表中记录为：
select * from test_1;
自增id变为4。
数据库重启后的测试
重启数据库后，再次插入一条记录：insert into test_1 (name) values ('xiaoqi');输出显示自增id变为5。
从实验结果可以看出，在MySQL5.7版本中，自增主键的值会在数据库重启后重置为max(primary key) + 1。这意味着在某些情况下，自增主键的值可能会跳过原本删除的记录的id值，导致业务逻辑中的主键冲突或其他潜在问题。
在MySQL8.0版本中，自增主键的行为得到了显著改进。随着InnoDB存储引擎的优化，自增主键的计数器已经能够持久化到重做日志中。具体来说，每次自增值发生变化时，InnoDB会将计数器的值写入重做日志中。这样，在数据库重启时，InnoDB可以根据重做日志中的信息来恢复计数器的内存值，确保自增主键的正确性和一致性。
MySQL8.0版本引入了对自增主键计数器持久化的支持。每当自增主键的值发生变化时，InnoDB都会将新的计数器值写入重做日志中。重做日志的持久化机制确保了计数器的值在数据库重启时能够被正确恢复。
在MySQL8.0版本中，我们可以通过以下步骤观察自增主键的行为：
创建测试表
create table test_1 (id int auto_increment primary key, name varchar(50));
插入数据
插入三条记录：insert into test_1 (name) values ('zhangsan'), ('lisi'), ('wangwu');此时，表中记录为：
select * from test_1;
自增id依次为1、2、3。
删除记录
删除id=3的记录：delete from test_1 where id = '3';
删除后，表中记录为：
select * from test_1;
自增id依然为1、2。
重新插入数据
插入一条新记录：insert into test_1 (name) values ('zhaoliu');此时，表中记录为：
select * from test_1;
自增id变为4。
数据库重启后的测试
重启数据库后，再次插入一条记录：insert into test_1 (name) values ('xiaoqi');输出显示自增id变为5。
与MySQL5.7版本相比，MySQL8.0版本在自增主键的行为上展现出明显的改进。自增主键的计数器已经能够持久化到重做日志中，从而在数据库重启时能够正确恢复计数器的内存值。这意味着在MySQL8.0版本中，自增主键的值不会因数据库重启而重置为max(primary key) + 1，从而避免了可能导致的业务主键冲突或其他难以发现的问题。
MySQL自增主键的行为在不同版本中存在显著差异。MySQL5.7版本由于其计数器的内存性质，在数据库重启时会重置自增主键的值，可能导致业务逻辑中的主键冲突或其他问题。而MySQL8.0版本通过对自增主键计数器的持久化实现，确保了自增主键的值在数据库重启时能够被正确恢复，从而提升了数据库的稳定性和一致性。
这些改进对于系统的可靠性和数据一致性具有重要意义，特别是在高并发或高可用性的场景中，MySQL8.0版本的自增主键行为能够更好地满足业务需求。
转载地址：http://snbfk.baihongyu.com/