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