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 INFILEINSERT ... 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忽略了某些错误记录时,手动执行了ROLLBACKROLLBACK 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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.