MySQL中InnoDB 表的 自增（AUTO_INCREMENT ）列详解

innodb表必须将 AUTO_INCREMENT 列定义为某个索引的第一个或唯一列。建议将 AUTO_INCREMENT 列设置为 PRIMARY KEY（主键）或 UNIQUE（唯一键）索引的一部分，以防止出现重复值

InnoDB AUTO_INCREMENT 锁模式

InnoDB 使用不同的锁模式来生成自增值：

innodb_autoinc_lock_mode =0 或 traditional：

• 在这种模式下，InnoDB使用类似于MyISAM存储引擎的锁定策略。
• 对于包含自增列的每个INSERT语句，InnoDB会对表加上排他锁（auto-inc锁），直到当前语句完成才释放。
• 这是最安全的模式，因为它可以保证在任何情况下都不会生成重复的自增值。
• 但由于需要持续地获取和释放锁，所以可能会导致性能问题，特别是在高并发的插入操作时。

innodb_autoinc_lock_mode =1 或 consecutive：

• 这种模式针对单个INSERT操作仍然使用锁，但如果是批量插入（比如INSERT ... SELECT或者LOAD DATA INFILE），则采用不同的策略，只在批量插入开始时获取一次锁，然后计算出所有行所需的自增值范围，接着释放锁。
• 这种模式可以减少锁的竞争，提高并发插入的性能，同时依旧保证不会产生重复的自增值。
• 是MySQL 5.1版本以后的默认设置。

innodb_autoinc_lock_mode =2 或 interleaved：

• 在这种模式下，InnoDB不会对自增列使用表级锁。
• 自增值的分配是在每行插入的时候动态进行的，而不是预先计算出一个范围。
• 这种模式允许高度并发的插入操作，因为不同的事务可以同时插入数据而不会等待其他事务释放自增锁。
• 但在某些场合，比如复制环境或者某些类型的备份操作中，可能会产生间隔的自增值，因此它适用于那些对自增值连续性没有要求的场景。
• 需要注意的是，在这种模式下，如果发生回滚，已经分配的自增值将不会被回收，可能会存在自增值的跳跃。
• 是MySQL 8.0版本以后的默认设置。

MySQL 8.0 默认使用交错锁模式（2），反映了从基于语句的复制默认更改为基于行的复制。

AUTO_INCREMENT 计数器初始化

• MySQL 5.7 及更早版本在内存中存储自增计数器；服务器重启后，使用 SELECT MAX(ai_col) FROM table_name FOR UPDATE 初始化。
• MySQL 8.0 中，自增计数器值写入重做日志并在每次检查点保存到数据字典，使得值在服务器重启后持久。
• 在正常关闭后的服务器重启，或者在崩溃恢复过程中，InnoDB 使用数据字典中的当前最大自增值初始化内存中的计数器。
• 如果没有 .cfg 元数据文件导入表，则还是会使用 SELECT MAX(ai_col) 来初始化计数器值。

注意：

• 修改 AUTO_INCREMENT 列的值或设置 ALTER TABLE ... AUTO_INCREMENT = N 只能将自增计数器值更改为当前最大值以上的值。

AUTO_INCREMENT 锁模式使用影响

• 基于语句的复制时，应将 innodb_autoinc_lock_mode 设置为 0 或 1，并在源服务器和副本上使用相同的值。
• 在基于行或混合格式复制中，所有自增锁模式都是安全的。

其他注意事项：

• 在任何锁模式下，回滚事务生成的自增值都将“丢失”，且不会被重用。
• 在所有锁模式下，如果用户为 AUTO_INCREMENT 列指定 NULL 或 0，InnoDB 将为其生成新值。
• 如果 AUTO_INCREMENT 值超过指定整数类型的最大值，自增机制的行为是未定义的。
• 对于“混合模式插入”，不同的锁模式有不同的结果，如上所述的例子所示。

设置自增列的相关变量

• auto_increment_offset 决定 AUTO_INCREMENT 列值的起始点，默认为 1。
• auto_increment_increment 控制连续列值之间的间隔，默认为 1。