MySQL性能（4）—AUTO_INCRMENT锁机制（inno

在mysql的InooDB存储引擎中，常用的是自增（AUTO_INCRMENT）主键，因为InnoDB是聚簇索引。

使用自增主键可以保证数据行是按顺序写入的。若是使用无序的聚簇索引，例如UUID，不仅索引占用的空间更大，而且也会导致页分裂和碎片化现象。

引用自—高性能mysql第三版.png

使用自增索引，在高并发的情况下可能存在性能问题，一个就是间隙锁影响性能，而另一个就是AUTO_INCREMENT锁机制会影响性能。

1. InnoDB中AUTO_INCREMENT处理

若是在InnoDB中使用AUTO_INCREMENT机制，那么自增列必须是索引的一部分，以便可以等效于对表进行索引查找以获取最大列值。

innodb_autoinc_lock_mode配置可以控制在向auto_increment列表插入数据时相关锁的行为以及主从数据一致性的平衡。

1.1 插入（INSERT LIKE）的分类

1.1.1. Simple inserts（简单插入）

是可以预先确定将要插入行数的insert语句，包括单行和多行INSERT语句，例如INSERT, INSERT … VALUES(),VALUES()语法。

1.1.2. Bulk inserts（大量插入）

事先不知道要插入的行数的语句，包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句，但不是纯INSERT，每处理一行时，一次为一列分配新增的AUTO_INCRMENT。

1.1.3. Mixed inserts（混合模式插入）

• 一些特殊的“简单插入”语句，用于指定部分（但不是全部）新行的自动增量值，例如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');；
• 例如INSERT … ON DUPLICATE KEY UPDATE，在最坏的情况下实际上是INSERT紧跟着UPDATE，其中AUTO_INCREMENT在更新阶段可能会或者不会使用为列分配的值。

1.2 innodb_autoinc_lock_mode配置

1.2.1. 版本更新：

• 在mysql5.1.22版本之前，是不支持innodb_autoinc_lock_mode配置的。
• 在mysql8.0之前，默认innodb_autoinc_lock_mode=1，即连续锁定模式。
• 在mysql8.0开始，默认innodb_autoinc_lock_mode=2，即交错锁定模式。

因为Mysql8.0之前，备份（binlog）默认是基于语句（statement模式）的复制。而Mysql8.0开始，binlog默认是基于行（row模式）的复制。

基于语句的复制需要连续的自动增量锁定模式，以确保为给定的SQL语句序列以可预测和可重复的顺序分配自动增长值。而基于行的复制对SQL语句的执行顺序不敏感。

1.2.2. 配置详情

innodb_autoinc_lock_mode = 0 （“ 传统 ”锁定模式）

该模式是Mysql5.1之前未引入innodb_autoinc_lock_mode配置参数时的行为，提供了传统的锁定模式选项以实现向后兼容。

在这种模式下，所有INSERT语句（INSERT LIKE）都会获得特殊的表级AUTO-INC锁定，将其插入具有AUTO_INCREMENT列的表中。此锁定通常保持在语句的末尾（而不是事务的末尾）。

举例分析：假设有两个事务正在运行，每个事务都将行插入具有AUTO_INCREMENT列的表中。一个事务使用insert ... select插入1000行的语句，另一事务使用insert插入一个行的简单语句：

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InooDB无法预先判断Tx1会插入多少行，Tx1使用表级锁（保持在该语句的末尾）去阻塞Tx2的并发执行，Tx1每次插入时均分配一个自增id，最终t1语句生成的递增主键是连续的。在主从复制或者故障恢复时，若binlog使用statement模式（基于语句的复制），主从服务器执行insert语句得到的结果是完全相同的。

innodb_autoinc_lock_mode = 1 （“ 连续 ”锁定模式）

是Mysql5.1.2-Mysql8.0中innodb_autoinc_lock_mode的默认配置。

• simple inserts：直接通过分析语句，获取要插入的的数量，然后一次性分配足够的auto_increment id，只会将整个分配过程锁住。
• Bulk inserts：[[bʌlk]]因为不能确定插入的数量，因此还会将表锁住；
• Mixed-mode inserts：直接分析语句，获取最坏情况下需要插入的数量，然后一次性分配足够的auto increment id，只会将整个分配过程锁住。

innodb_autoinc_lock_mode = 2 (“交叉”锁定模式)

这种模式下插入语句（INSERT LIKE）来一个分配一个，而不会锁表，只会锁住分配ID的过程，和innodb_autoinc_lock_mode = 1区别bulk insert是不会预分配多个，这种方式的并发性最高。

若是binlog的模式为statement模式，由于并发情况下Bulk inserts在分配的时会同时向其他insert分配，会出现主从不一致的情况。

所以需要配合binlog的row模式使用。

2. InnoDB的自增主键特性

1. 主从复制下锁定策略

如果binlog为statement模式（基于语句的复制），请将innodb_autoinc_lock_mode设置为0或1，并在主设备及从设备上使用相同的值。
如果binlog是row模式或者mixed模式，则innodb_autoinc_lock_mode均是安全的。因为基于行的复制对于SQL语句的执行顺序不敏感。

2. “丢失”自增值和序列间隙

在所有锁定模式（0,1和2）中，如果生成自增值的事务回滚，则这些自增值将“丢失”。为自动递增列生成值后，无论“INSERT like”语句是否完成，以及包含的事务是否回滚，都无法回滚该值。这样丢失的值不会被重用。因此，自增列中的值有间隙。

3. 自增列上的NULL和0

在所有锁模式（0,1和2）中，如果用户未INSERT中的AUTO_INCREMENT列指定NULL或0，InnoDB会将该行视为未指定值并为其生成新值。

4. 为自动递增列指定负值

在所有锁定模式（0,1和2）中，如果为“auto_increment”列指定负值，则自增特性不会生效（持久化的为负值）。

5. “大量插入（bulk inserts）”下自增列与间隙

当innodb_autoinc_lockm_mode设置为0或1时，任何给定语句生成的自增值都是连续的，没有间隙。因为会使用表锁一直到语句结束，并且一次只能执行一个这样的语句。

在innodb_autoinc_lockm_mode=2的情况下，在“bulk inserts”并发执行时，生成的自增列可能存在间隙。

6. 更新自增列的值

第一步：插入数据（0和null会执行自增策略）

create table t(x int auto_increment not null primary key);
insert into t(x) values(0),(null),(3);
select * from t;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+

第二步：修改sql中的自增列。

update t set x=4 where x=1;
select * from t;
+---+
| x |
+---+
| 2 |
| 3 |
| 4 |
+---+

第三步：再次插入

insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

第四步：分析

执行完第一步后，mysql的自增序列生成器知道下一个自增值为4；

执行第二步，mysql并不知道自增列4已经被人占用，所以执行第三步的时候就出现异常。

’

官方文档

官方文档—InnoDB中的AUTO_INCREMENT处理