定期调整MySQL表AUTO_INCREMENT值的弊端及更优方案咨询

• 性能损耗明显：ALTER TABLE 哪怕仅修改自增值，也会触发表元数据锁，锁持有期间所有对该表的读写请求都会被阻塞。MySQL 8.0虽优化了元数据锁粒度，但高频DDL仍会产生性能毛刺，业务高峰期执行极易引发请求堆积。
• 存储浪费+索引效率下降：如果单10分钟周期内写入量远低于预留步长，会产生大量自增值空洞，不连续的主键会让主键索引体积变大，间接拖慢索引查询效率。
• 高可用风险：主从架构下，该DDL同步到从库时也会执行，容易引发主从延迟；若步长设置和实际写入速度不匹配，还可能出现主从自增值冲突。
• 效果可靠性差：如果定时任务执行失败、漏执行，自增列会回到连续递增状态，防猜测逻辑直接失效；如果误操作将自增值调小，还会直接触发主键冲突。如果单周期写入量超过预留步长，该周期内的自增值仍会连续，防猜测效果完全不达标。

所有方案均满足「自增值整体随时间递增、降低可猜测性」的要求：

方案1：会话级自增偏移配置 #

无需执行DDL修改表属性，仅在写入请求的会话中临时调整自增规则即可：

-- 可根据需要调整随机范围的大小，控制可猜测性
SET @@auto_increment_offset = FLOOR(RAND()*200) + 1;
SET @@auto_increment_increment = 200;

相邻自增值的差为1~200之间的随机值，整体保持递增，对性能几乎无影响，主从同步也不会出现异常。

方案2：自定义时间戳主键 #

放弃数据库自带自增主键，业务层自行生成主键：规则可设为「13位毫秒级时间戳 + 3位随机数 + 2位服务节点标识」，总长度18位，天然满足随时间递增的要求，随机位足够多完全无法猜测，分布式场景下也可直接使用，写入性能比依赖数据库自增更高。

方案3：用MySQL原生随机自增特性 #

如果使用MySQL 8.0.24及以上版本，可直接开启原生自增随机偏移配置：设置innodb_autoinc_random_max参数为你需要的最大随机偏移值，数据库会自动在当前自增值基础上增加不超过该值的随机偏移，保证整体递增，不需要额外开发定时任务，原生实现性能最优。

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