AWS Aurora Serverless(MySQL5.7)已删除主键ID偶现复用问题咨询

问题本质 #

你遇到的情况大概率和AWS RDS的实例切换/自增计数器未持久化有关：

• InnoDB在MySQL 8.0之前，自增计数器仅存储在内存中，若实例重启或发生故障切换（只读副本升为主库），新实例会遍历表中数据取最大ID再加1作为新的自增起始值。
• 当你删除staff_entries的最新行（如ID385），主库的自增计数器仍保持为386，但只读副本同步后表内最大ID是384，其自增计数器会设为385。若此时触发故障切换，副本变为主库，新插入的行就会复用385这个已删除的ID。
• 间隔1-2小时才出现问题，大概率是这段时间内发生了实例重启、维护操作或故障切换。

具体解决方案 #

1. 升级到MySQL 8.0+（最优解） #

MySQL 8.0开始，InnoDB的自增计数器会持久化到redo log，同时binlog会记录自增计数器的变化，只读副本同步时会保持和主库一致的计数器值。即使发生故障切换或重启，自增起始值也不会回退到表内最大ID，从根源上避免ID复用。

2. 删除最新行后强制重置自增计数器（兼容低版本） #

如果暂时无法升级，每次删除staff_entries的最新行后，立即执行以下SQL确保自增起始值正确：

SET @max_id = (SELECT MAX(id) FROM staff_entries);
ALTER TABLE staff_entries AUTO_INCREMENT = IFNULL(@max_id, 0) + 1;

• 该语句会将自增起始值设为当前表内最大ID+1（空表时设为1），避免后续插入复用已删除的ID。
• 注意：ALTER TABLE会锁表，若表数据量较大，需评估对业务的影响。

3. 改用自定义ID生成策略（彻底规避） #

完全脱离数据库自增ID的限制，改用更可靠的ID生成方式：

• 雪花算法（Snowflake）：生成包含时间戳、机器标识、序列号的64位全局唯一ID，既保证递增又不会复用。
• 独立ID表维护：创建一张单独的表存储当前最大ID，每次插入前通过事务原子性地获取并递增ID，确保唯一性。

4. 排查AWS RDS配置 #

• 检查RDS实例的维护窗口，避免在业务高峰期自动重启实例。
• 监控实例的故障切换记录，确认是否有意外的主备切换导致计数器重置。

内容的提问来源于stack exchange，提问作者Ganapat