XtraDB/InnoDB中的AUTO_INCREMENT处理方式

XtraDB和InnoDB存储引擎中的AUTO_INCREMENT处理方式有着显著的性能改进。系统变量innodb_autoinc_lock_mode决定了auto-increments的锁模式。

传统的锁模式

设置innodb_autoinc_lock_mode=0，此为传统锁模式。在INSERT操作过程中，它会一直持有表级锁，直到插入语句结束。

连续锁模式

设置innodb_autoinc_lock_mode=1，该连续锁模式对大批量插入语句(例如LOAD DATA INFILE、INSERT ... SELECT语句)生效，对于这些插入语句，它会持有表锁直到插入操作结束。而对于单独的INSERT语句，它不会持有表锁这样重量级的锁，而是持有一个轻量级的、粒度更细的互斥锁，这显然更适合绝大多数插入环境。这是默认设置。

隔行交叉扫描锁模式

设置innodb_autoinc_lock_mode=2，插入操作不会持有任何表级锁。这是最快的插入模式，但对于statement-based replication来说不够安全。

初始化

直到MariaDB 10.2.3，InnoDB/XtraDB使用一个存储在内存中的自增长计数器。当mysqld服务重新启动的时候，计数器会重置，因此所有表的AUTO_INCREMENT = N也会重置。从MariaDB 10.2.4开始，该限制已经被移除了，AUTO_INCREMNT的值可以是持久的。虽然持久，但不意味着是事务安全的，某些情况下AUTO_INCREMENT的值会出现断层。例如，当INSERT IGNORE忽略了某些错误记录时，手动执行了ROLLBACK或ROLLBACK TO SAVEPOINT操作。

例如：

CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, i INT, UNIQUE (i)) ENGINE=InnoDB;

INSERT INTO t1 (i) VALUES (1),(2),(3);
INSERT IGNORE INTO t1 (pk, i) VALUES (100,1);
Query OK, 0 rows affected, 1 warning (0.099 sec)

SELECT * FROM t1;
+----+------+
| pk | i    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `i` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

如果此时重启mysqld，auto_increment的值将转换为101，因为设置auto_increment的值是INSERT IGNORE的一个过程，在插入失败时就已经设置好了。

# 重启服务
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `i` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

See Also

• AUTO_INCREMENT
• AUTO_INCREMENT FAQ
• LAST_INSERT_ID
• Sequences - an alternative to auto_increment available from MariaDB 10.3