MySQL 自增主键一定是连续的吗？

在 MySQL 数据库中，自增主键（AUTO_INCREMENT）是一种常用的主键生成策略，它能够为每条新记录自动分配一个唯一的标识符。然而，许多开发者在使用过程中发现，MySQL 的自增主键并不总是连续的。

一、自增主键的基本概念

自增主键是通过 AUTO_INCREMENT 属性实现的，通常用于表的主键字段。当向表中插入新记录时，如果没有明确指定该字段的值，MySQL 会自动为该字段分配一个比当前表中该字段的最大值大1的值。

二、自增值的存储机制

MySQL 的自增值存储机制因存储引擎的不同而有所差异：

1. MyISAM 引擎：自增值保存在数据文件中。

2. InnoDB 引擎：自增值保存在内存中，并不会持久化。在 MySQL 重启后，自增值会重新计算为表中最大值加1。

3. MySQL 8.0：自增值的变更记录被放在 redo log 中，重启后可以通过 redo log 恢复自增值。

三、自增主键不连续的原因

尽管自增主键通常是递增的，但并不能保证连续性。以下是导致自增主键不连续的常见场景：

1. 自增初始值和步长设置不为 1

MySQL 提供了 auto_increment_offset 和 auto_increment_increment 参数，分别用于设置自增的初始值和步长。默认值为 1，但如果设置为其他值，自增值会跳过某些数字，从而导致不连续。

2. 唯一键冲突

当插入数据时，如果违反了唯一键约束，插入操作会失败，但自增值已经递增且不会回退。例如，插入两条具有相同唯一键值的记录时，自增值会增加，但第二条记录会因冲突而失败。

3. 事务回滚

在事务中，如果插入操作后发生回滚，自增值不会回退。例如，事务中插入一条记录后回滚，自增值仍然会增加。

4. 批量插入数据

对于 INSERT...SELECT、REPLACE...SELECT 和 LOAD DATA 等批量插入语句，MySQL 会采用批量申请自增 ID 的策略。每次申请的 ID 数量是上一次的两倍，这可能导致某些 ID 被浪费，从而导致不连续。

四、性能优化与实践

1. 自增锁优化

在批量插入数据时，MySQL 的自增锁机制会影响性能和数据一致性。默认情况下，InnoDB 使用语句级锁来保证数据一致性，但可以通过调整 innodb_autoinc_lock_mode 参数来优化并发性能。

• innodb_autoinc_lock_mode = 2：在高并发场景下，建议将 innodb_autoinc_lock_mode 设置为 2，同时将 binlog_format 设置为 ROW，以提高并发性能并避免数据一致性问题。

2. 批量插入的优化

批量插入数据时，应尽量减少数据库的 I/O 操作和网络延迟。以下是一些优化技巧：

• 使用批量插入而非单条插入，减少事务提交次数。

• 手动控制事务，将多个插入操作放在一个事务中执行。

• 使用 LOAD DATA INFILE 指令快速插入大量数据。

• 调整 MySQL 参数，如 bulk_insert_buffer_size 和 innodb_buffer_pool_size，以提升插入性能。

MySQL 的自增主键虽然能够为每条记录生成唯一的标识符，但并不保证连续性。自增主键的不连续性是由多种因素共同作用的结果，包括自增初始值和步长的设置、唯一键冲突、事务回滚以及批量插入的自增 ID 分配策略。