MySQL系统变量innodb_autoinc_lock_mode

innodb_autoinc_lock_mode 是 MySQL InnoDB 存储引擎的一个系统变量，它控制着自增（AUTO_INCREMENT）值的锁定机制。这个设置对于高并发的数据库操作，特别是插入（INSERT）操作，有着显著的性能影响。

1、insert 语句分类

insert 语句分三种类型：simple insert, bulk insert, mixed insert

• simple insert
  insert 时可以预先知道插入的行记录数量。例如insert into t values(a), (b), 这个语句插入时，mysql就可以预先知道插入的行记录数量为2.
  insert … on duplicate…语句不属于simple insert
• bulk insert
  insert 时可以预先知道插入的行记录数量。
  例如load data语句、INSERT … SELECT语句, REPLACE … SELECT语句都属于bulk insert。
• mixed insert
  simple insert中有一部分指定了AUTO_INCREMENT的值。

2、innodb_autoinc_lock_mode可以设置的值

innodb_autoinc_lock_mode 可以设置为以下三个值之一：

1. 0（traditional传统模式）：

   • 在这种模式下，InnoDB 使用表级锁来维护自增值的连续性。这意味着，在插入新记录时，InnoDB 会锁定整个表，以确保自增值的唯一性和连续性。这在高并发的环境下可能会导致性能瓶颈。

2. 1（consecutive连续模式）：

   • 在这种模式下，InnoDB 仍然保证自增值的连续性，但使用了一种更轻量级的锁定机制。它只在需要生成新的自增值时锁定自增值的计数器，而不是锁定整个表。这减少了锁的竞争，提高了并发性能。但是，如果事务被回滚，那么已经分配但未被使用的自增值将丢失，导致自增值出现“空洞”。
   • MySQL5.7.28为默认模式 ,会产生一个轻量锁机制，simple insert 会获得批量的锁，保证连续插入

3. 2（interleaved交错模式）：

   • 这是 MySQL 5.6 及以上版本中引入的模式，旨在进一步提高并发性能。在这种模式下，InnoDB 允许不同的插入操作在不同的自增值区间内并发进行，从而减少了锁的竞争。但是，这同样可能导致自增值出现“空洞”。此外，交错模式还允许在批量插入操作中预先分配多个自增值，这进一步提高了性能。
   • MySQL8.0.18为默认模式不会锁表，来一个处理一个，并发性能最高

3、如何设置 innodb_autoinc_lock_mode

你可以通过以下 SQL 命令来查看当前的 innodb_autoinc_lock_mode 设置：

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
1

要修改这个设置，你需要在 MySQL 的配置文件（如 my.cnf 或 my.ini）中设置它，或者在 MySQL 服务启动时通过命令行参数指定。但是，请注意，一旦 InnoDB 表被创建，改变 innodb_autoinc_lock_mode 的值可能不会影响已经存在的表。

4、注意

• 在选择 innodb_autoinc_lock_mode 的值时，需要根据你的应用场景和性能需求来权衡。如果你需要保证自增值的连续性，并且可以接受较低的并发性能，那么可以选择模式 0 或 1。如果你更关心并发性能，并且可以接受自增值出现“空洞”，那么模式 2 是一个更好的选择。
• 在高并发的生产环境中，建议进行充分的测试，以确定最适合你的系统的 innodb_autoinc_lock_mode 设置。