InnoDB 的AUTO_INCREMENT处理

从MySQL 5.1.22开始，InnoDB提供一个加锁策略来显著的提高SQL语句的可扩展性，以及向有AUTO_INCREMENT列的表添加行的效率。在InnoDB表中使用AUTO_INCREMENT机制，有AUTO_INCREMENT属性的ai_col列必须被定义为索引的一部分，这样在表上执行等效的SELECT MAX(ai_col)来获取列的最大值才有可能。典型地，这通过将AUTO_INCREMENT列作为表的某个索引的第一列来实现。

这章节提供InnoDB auto-increment锁的原始（“traditional”传统）实现的背景知识，解释可配置的锁机制，介绍可配置机制的参数，描述它的行为和在复制时的交互。

传统InnoDB Auto-Increment锁

InnoDB的auto-increment锁处理的原始实现使用如下的策略来确保使用二进制日志做基于语句的复制 或者在某些场景做恢复。

如果你在InnoDB中指定了AUTO_INCREMENT列，在InnoDB数据字典中的表句柄（handle）有一个叫做auto-increment计数器，这是用于给auto-increment列赋值的特殊的计数器。这个计数器只存在于内存中，而不是硬盘中。

InnoDB使用如下的算法来初始化表t的 auto-increment计数器， 表t含名为ai_col的auto-increment列。服务器启动之后，为了第一次向表t插入，InnoDB执行和如下等效语句。

SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB增加从该语句获取的值，将增加后的值赋值给auto-increment列和该表的auto-increment计数器。默认，这个值每次加1，这个行为可以通过auto_increment_increment配置参数来改变。如果表为空，InnoDB使用的第一个值为1，这也可以通过auto_increment_offset配置参数改变。

如果在auto-increment计数器初始化之前执行SHOW TABLE STATUS检查表，InnoDB初始化auto-increment计数器但是不会增加该值，并保存auto-increment计数器的值为了以后插入使用。初始化使用表标准的排它读锁，直到事务结束才解锁。

对于新创建的表InnoDB也使用相同的过程来初始化auto-increment计数器。

auto-increment计数器初始化以后，如果你没有显示地给auto-increment列指定值，InnoDB对auto-increment计数器执行自增，然后将自增后的值赋给该列，如果你插入的行的auto-increment列被显示赋值，且这个值比当前计数器值大，计数器更新为该值。如果插入时auto-increment列指定为NULL或者0，InnoDB将其和没有赋值一样对待。

赋负值或者赋一个超出某个整数类型的范围的值给该列，auto-increment机制的行为是不确定的。

当访问auto-increment计数器，InnoDB使用表级别的特殊的AUTO-INC锁，到语句结束解锁，而不是事务结束时解锁。这个特殊的锁释放策略用于提高并发地向含有AUTO-INC列的表插入数据。然而，两个事务不能同时获取某个表的auto_inc锁，如果auto_inc锁被长时间持有将影响效率。比如INSERT INTO t1 ... SELECT ... FROM t2，将一个表的数据插入另一张表就是这样的情况。

当服务器运行时InnoDB使用存在于内存的自增计数器。当服务器停止后重启，如前所述，InnoDB在第一次插入时重新初始化该表的计数器。

服务器重启也取消CREATE TABLE和ALTER TABLE语句指定的AUTO_INCREMENT = N选项。AUTO_INCREMENT可用于初始化或者改变当前计数器的值。

如果事务回滚时，已经使用计数器生成AUTO_INCREMENT列值（译者注：这些值将丢弃），那么赋给AUTO_INCREMENT列的值可能不连续。

14.6.5.5.2配置InnoDB Auto-Increment 锁

如前所述，InnoDB使用一个表级别的叫做AUTO-INC特殊的锁来向含有AUTO_INCREMENT列的表插入数据。这个锁一般持续到语句结束（而不是事务结束），来确保对于一个插入序列，自增产生的数的顺序是可预测和可复验的。

对于基于语句的复制，这意味着在从服务器上重放语句，AUTO_INCREMENT将赋予同主服务器一样的值。执行多个insert语句的结果是确定的，如果多个插入语句交叉地产生自增值，那么在从服务器（slave server）上使用基于语句的复制是不可靠的。

为了彻底解释这个问题，考虑使用这张表的一个例子：

CREATE TABLE t1 (

c1 INT(11) NOT NULL AUTO_INCREMENT,

c2 VARCHAR(10) DEFAULT NULL,

PRIMARY KEY (c1)

) ENGINE=InnoDB;

假设有两个事务正在运行，每个都向有AUTO_INCREMENT 列的表插入数据，一个事务使用INSERT ... SELECT语句插入100行，另一个使用简单的INSERT插入一行。

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...

Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB 不能确定TX1的inert语句的SELECT部分会获取多少行，语句每产生一行赋于一个自增值。占用表级别的锁直到语句结束，某一时刻只能有一个inert插入语句在表 t1上执行，不同语句产生的自增值不会交叉。事务tx1的INSERT ... SELEC语句产生的自增值是连续的，tx2使用的单个自增值要么比tx1中的值小，要么大，决定于哪个语句先执行。

如果来自二进制日志的SQL语句以相同的顺序重放（当使用基于语句的复制，或恢复），结果与tx1和tx2第一次执行一样，因此，持有表级别锁直到语句结束，确保含增列的insert语句执行基于语句的复制安全，然而，这些锁限制了多个事务同时执行插入语句的并发性和扩展性。

在以前的例子，如果没有表级别的锁，tx2的自增列的值由该语句何时执行决定，如果tx2的插入语句在tx1的插入语句执行期间执行（而不是在其开始之前或者结束之后），这两个插入语句赋予的自增值是非确定的，且每一次执行都不一样。

MySQL 5.1.22，对于行数可以事先确定的这类insert语句，InnoDB能避免使用表级别的auto-inc锁，却仍然能确保执行的确定性和基于语句复制的安全性。进一步地，如果你没有使用二进制日志来重放SQL语句做恢复和复制，你可以完整的消除表级别auto-inc锁来达到更好的并发性和性能，代价是允许语句赋予的自增数不连续（gaps）和潜在的并发执行语句时交叉地产生值。

对于在语句处理之前待插入行数确定的插入语句 ，InnoDB快速分配需要的自增值个数而不用任何锁，前提是没有并发的会话（session）已经占有了表级别的auto-inc锁（因为其他语句将逐一分配自增值）。更精确地，这样的插入语句在一个互斥量的控制下（轻量级锁）获得自增值不会一直占有直到语句结束，只在自增值分配期间占有。

这个新的加锁模式允许更大的扩展性，但是比起古老的机制，自增值如何赋值它也引人了一些细微的不同。为了描述InnoDB自增值的机制，以下的讨论定义一些条目，解释innodb_autoinc_lock_mode配置参数不同时InnoDB行为。

首先一些定义

• “INSERT-like” 语句

所有产生新行的语句，包括insert , insert…select, select, replace, replace … select, load data。

• “简单 inserts”

事先确定语句待插入的行数(语句初始处理的时候)。这包含没有嵌套子查询的单行和多行insert 和replace语句，但是不包含insert… on duplicate key update.

• “块 inserts”

事先不能确定插入行数目（自增值的个数）. 包括 INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA语句, 排除简单 INSERT。 当语句处理的时候，InnoDB为每行的auto_increment列赋新的值。

• “混合插入模式”

简单插入语句的某些行的自增列被指定值（而不是全部），下面例子中，c1是t1表的auto_increment列：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一个类型的“混合插入模式”是insert…on duplicate key dupdate,事实上这是一个插入后紧跟更新语句，这是最糟糕的情况，分配的自增值在更新阶段可能根本不用。

innodb_autoinc_lock_mode参数三种可能得取值:

• innodb_autoinc_lock_mode = 0 (“traditional” 锁模式)

这种锁模式和没有innodb_autoinc_lock_mode前的行为一样。对于所有“insert 等效”的语句，获取一个特殊的表级别的auto-inc锁持有它直到语句结束。这确保所有语句的自增值都是连续的（consecutive）。

这个锁模式的功能：:

• 向后兼容.
• 性能测试.
• 绕开“混合模式”的问题，由于稍后描述语义可能不同 。
• innodb_autoinc_lock_mode = 1 (“consecutive” 锁模式)

这是默认的锁模式，在这种锁模式下，“块插入”使用特殊的auto-inc表级别锁且持有它直到语句结束，适用于所有的INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA。只有拥有该锁的语句才能执行。

利用这种锁模式，“简单插入”使用一个新的锁模式--轻量级互斥量，当分配自增值，没有使用表级别的auto-inc锁，除非auto-inc锁被另一个事务占有。如果另一个事务确实占有了auto-inc锁，“简单插入”等待auto-inc锁， 就如同它是一个“块插入”一样。

这种锁模式确保那些事先不能确定插入行数的insert 语句（语句产生一行赋予相应的auto-inc值），所有“insert like”语句赋予的自增值都是连续的，基于语句的复制也是安全的。

简而言之，这种锁的重要意思是显著的可扩展性。对于基于语句的复制也是安全的。更进一步，相对于传统锁模式，语句赋予的自增值是连续的，在这种锁模式下，比起传统的锁模式自增值没有语义改变，一个除外：

例外就是“混合模式”，用户显示地为某些AUTO_INCREMENT行提供值，但是不是全部，这是多行“简单 插入”。对于这种插入， InnoDB将分配比插入行更多自增值数量。然而，自动赋予的值是连续产生的（因此更大），这些值比最近执行的语句产生的更大，超过的值将丢弃。

• innodb_autoinc_lock_mode = 2 (“interleaved” 锁模式)

这种锁模式， 没有“INSERT LIKE”语句使用表级别的auto-inc锁，多个语句能同时执行。这是最快和最高扩展性的锁模式，但是它对于使用二进制日志进行基于语句的复制和恢复场景是不安全的，

这种锁模式，自增值在所有并发执行的“insert-like”语句间唯一且单调地产生 。然而，因为多个语句能同时产生值（那就是说，分配值在语句间交叉地进行），为任何语句的多行产生的值可能不是连续的

如果执行的语句是事先可以确定行数的“简单 插入”，对于简单语句产生的数值是不会有差异的，除了“混合模式”。然而当执行“块插入”，赋值给语句的自增值可能有差异。

innodb_autoinc_lock_mode提供的auto-increment 锁模式有如下的应用场景暗示:

• 复制（replication）时含自增列

如果你使用基于语句复制，设置innodb_autoinc_lock_mode为0或者1，且在主服务器和从服务器使用相同的值。自增值在从服务器不一定和主服务器一样如果你使用innodb_autoinc_lock_mode=2（interleaved）,或者配置主服务器和从服务器使用不一样的锁模式。

如果你使用基于行或者混合格式（mixed-format）的复制，所有的自增锁模式都是安全的，由于基于行复制时对SQL的执行顺序不敏感（混合格式是：对于基于语句复制不安全的语句使用基于行复制）

• 丢失自增值和序列间隙

所有的锁模式（0,1， 2），如果产生自增值的事务回滚，这些自增值将丢弃，一旦为某个auto-increment列产生了一个值，这个值是不能回滚的，无论“insert like”语句是否完成，包含它的事务是否回滚，这些丢失的值是不能重用的，因此表的AUTO_INCREMENT 列的值可能不是连续的。

• 块插入时自增值间隙

当innodb_autoinc_lock_mode=0(传统模式)或者1（连续模式），任何语句产生的自增值都是连续的，没有间隙，因为表级别auto-inc锁持有直到语句的结束，任何时候只有一个语句能执行。

Innodb_autoinc_lock_mode=2(交叉模式)，仅当并发地执行“insert-like”语句时，块插入产生的自增值可能有间隙。

对于锁模式1 或2，对于连续批量插入语句间可能有间隙，因为每个语句准确的自增值的个数是未知的，所以可能过多估计

• 混合插入模式赋予的自增值

考虑一个混合插入例子，一些行的auto-increment列指定了值，而另一些没有，这种语句在锁模式0,1，2下是不同的， 比如假设表t1的c1是AUTO_INCREMENT 列，最近产生的序列号是100。考虑如下的“混合模式插入”语句 。

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

当innodb_autoinc_lock_mode=0(传统模式)，四个新行为：

+-----+------+

| c1???? | c2? ?? |

+-----+------+

|?? 1 ? ? | a?????? |

| 101 ? | b? ? ? |

|?? 5???? | c? ? ? |

| 102?? | d???? |

+-----+------+

下一个可用的自增值为103，因为自增值一次分配一个，不是在语句开始执行时全部分配，无论是否有并发执行的“insert-like”语句（任何类型）都是如此。

当 innodb_autoinc_lock_mode =1（连续模式），四新行为：

+-----+------+

| c1 ? ? | c2???? |

+-----+------+

|?? 1???? | a?????? |

| 101 ? | b? ? ?? |

|?? 5 ? ? | c?????? |

| 102? | d ? ? ? |

+-----+------+

然而这个例子中，下一个可用的自增值是105，而不是103，因为语句开始处理的时候分配了4个自增值，但是只使用了两个，无论是否有“insert-like”（任何类型）语句并发执行结果都是这样的。

当innodb_autoinc_lock_mode =2 (“交叉模式”), 新的四行为:

+-----+------+

| c1 ?? | c2????? |

+-----+------+

|?? 1???? | a? ? ?? |

|?? x ? ? | b????? |

|?? 5 ? ? | c? ? ? |

|?? y ? ? | d???? |

+-----+------+

X和y是唯一的且比以前产生的值都大，然而x和y的具体值决定于并发执行的语句产生的自增值的个数 。

最后，考虑下面的语句，假设最近产生的序列号是4：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

对于任何的innodb_autoinc_lock_mode设置，这个语句将产生一个key重复的错误2300（不能写入，表中key重复），因为5已经分配给行（NULL, 'b'），所以插入行(5, 'c')失败。

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Posted in: MySQL practise | Tags: AUTO_INCREMENT, InnoDB, MySQL