MySQL -- Innodb是如何处理自增列的

对于那些向带有自增列的表中插入行的语句，Innodb提供一种可配置的锁定机制，这种锁定机制可以显著提高SQL语句的可伸缩性和性能。

Innodb中为了使用自增机制，自增列必须是索引的部份，从而可以使用等价查询。典型的做法是将自增列放在表的索引的第一个位置。

 

Innodb自增锁模式

自增锁模式是在启动的时候由参数innodb_autoinc_lock_mode指定的。

 

在讲innodb_autoinc_lock_mode之前，先了解一下以下名词：

--"insert-like"语句

　　在表中产生新行的语句，比如insert、insert...select、replace、replace...select、load data。也包含"simple-inserts"、"bulk-inserts"、"mixed-mode"插入。

 

--"simple-inserts"

　　语句执行前可以提前知道要插入多少行。包含不含有子查询的单行插入、多行插入和单行replace、多行replace。但是不包含insert... on duplicate key update。

 

--"bulk inserts"

　　插入的行数数量是不知道的。包含insert... select,replace...select,load data，而不是简单的插入。innodb没处理一行就给自增列指定一个新值。

 

--"mixed-mode inserts"

　　类似simple-inserts，但是不是所有的自增列都被指定值。例如t1表中c1列是自增列：　　

insert into t1(c1,c2) values(1,'a'),(null,'b'),(5,'c'),(null,'d')

　　另一种"mixed-mode inserts"是insert...on duplicate key update,最差的场景就是一条insert后跟着一条update，产生的自增列的值可能都没有用到。

 

innodb_autoinc_lock_mode有三种配置值：0、1、2

-0表示"traditional"

　　传统锁模式的行为跟5.1之前一样,即没有出现该参数的时候一样。主要是为了向后兼容，性能测试等。

　　在这种模型下，insert-like语句使用 表级别的auto-inc锁来插入自增值。锁通常是持有到语句结束(不是事务结束)。

　　对于给定的一些列插入语句，自增值的产生是可预测和可重复有序的，且是连贯的。

 

　　对于基于语句的复制，在slave端的一条语句的自增值和在master端是一样的。如果多个语句是交错的，那么slave端两个并发语句的结果可能是不一致的。

 

示例：

首先有张表：

create table t1(
    c1 int(11) not null auto_increment,
    c2 varchar(10) default null,
    primary key(c1)
) engine = innodb;

假设有两个事务插入数据：

tx1: insert into t1 (c2) select 1000 rows from another table ...  #事务1插入1000行
tx2: insert into t1 (c2) values ('xxx');

innodb无法提前知道tx1会插入多少条数据，只好每插入一条数据就产生一个自增值。对于表级锁，每次执行执行一条sql语句，所以对于不同的sql语句，自增值的产生不是交错的。

tx1产生的自增值是连续的，tx2中单独的自增值要么比tx1中的小，要么比其大。取决于哪个事务先执行。

 

在基于语句的复制或恢复场景，只要二进制中的sql执行是按照相同的顺序，结果就会和tx1和tx2的结果一致。所以表级锁对基于语句的复制时是安全的。但是表级锁会限制并发性和扩展性。

 

在上面的例子中，如果没有表级锁，tx2中产生的自增值的准确性取决于其运行时间，如果运行时恰好tx1也在运行，最后结果是不可确定的，多次测试的结果也是不同的。

 

在consecutive锁模式下，innodb避免了使用表级别的auto-inc锁来控制"simple-insert"语句。

如果不使用二进制日志去replay语句来恢复或者复制，可以使用"interleaved"模式的锁，从而避免使用表级auto-inc锁，而且能够支持大的并发和提升性能，代价是自增值会产生gap。

 

-1表示"consecutive"锁模式
这是默认的模式。在连续模式下，"bulk inserts"使用特殊的auto-inc表级别锁，直到事务结束才会释放锁。也使用于所有insert...select、replace...select、load data语句。每次只有一个sql语句可以持有该锁并执行。
如果批量插入的原表和目标表不同，当在源表上加上一个共享锁取出一条数据后，就会对目标表加上auto-inc表。如果二者相同，取完源表上的所有数据后，才会对目标表加上auto-inc锁。

"Simple inserts"可以提前知道需要哪些自增值，在mutex的帮助下，不在需要对表加上auto-inc锁。mutex只是在分配自增值得时候存在，不需要等整个sql语句结束才释放。如果有另外的事务持有auto-inc锁得时候，就需要获取表级auto-inc锁了。

这种锁模式，可以保证在插入的行数不能提前确定的时候，"insert-like"的插入语句的自增值是连续的，同时保证基于语句的复制是安全的。

 

-2表示"interleaved"锁模式
该模式下，所有insert-like语句都不使用表级auto-inc锁，多个语句可以同时执行。这是最快、最可扩展的模式。。
但是对基于语句的复制是不安全的，对基于语句的恢复也是不安全的。

自增列的值会被保证是唯一的。因为并发插入的原因，每次插入时，自增长的值可能不是连续的。对基于语句的复制也是不安全的。

 

Innodb自增锁的使用
1.使用自增列进行复制
如果使用基于语句的复制，innodb_autoinc_lock_mode设置为0或1(主、备都使用相同的配置)。如果是设置为2或者主备设置不一样，不能保证准备端一致。

如果是使用基于行的、混合模式的复制，所有自增锁模式都是安全的，因为基于行的复制对sql的执行顺序是不敏感的(混合模式会将基于语句的不安全的复制转换成行复制)。

2.自增值的丢失和序列空隙(sequence gaps)
在所有自增锁模式中(0,1,2),如果使用自增值得事务发生了回滚，这些自增值就丢失了。这些值是不可以重用的，这样就产生了gap。

3.自增列指定null或0值
在所有自增锁模式中(0,1,2),如果将自增列指定为null或0，innodb会自行给自增列赋值

4.自增列指定为负值
可以插入赋值，但是不能在负值得基础上进行自增

5.超过自增列指定的最大值
超过自增列指定的最大值就不能自增了。

6."mixed-mode inserts"中自增值得使用
"mixed-mode inserts"中有"simple insert"指定的自增值，也有没有指定的。在不同的自增锁模式下结果是不同的。

7.在插入的过程中修改自增值
会导致重复的值。

 

Innodb自增计数器的初始化
创建了自增列后，对应的数据字典中包含一个自增计数器，用来为列赋值。这个计数器是存储在内存中，而不是磁盘上。

在实例启动后，innodb会执行类似下面的语句来为计数器初始化：
select max(ai_col) from table_name for update;
缺省情况下，会将获取的值增加1。也可以修改参数auto_increment_increment进行配置。

如果表是空的，计数器的值就是1。