InnoDB中如何处理AUTO_INCREMENT
InnoDB提供了一种可配置的锁定机制,该机制可以显着提高将行添加到具有AUTO_INCREMENT列的表中的SQL语句的可伸缩性和性能。** 要将AUTO_INCREMENT机制用于InnoDB表,必须将AUTO_INCREMENT列定义为索引的一部分**,以便可以对表执行与索引SELECT MAX(ai_col)
查找等效的操作,以获得最大列值。 通常,这是通过使该列成为某个表索引的第一列来实现的。
本节描述了AUTO_INCREMENT锁定模式的行为,不同AUTO_INCREMENT
锁定模式设置的使用含义以及InnoDB如何初始化AUTO_INCREMENT计数器。
自增锁定模式
本节介绍用于生成自动增量值的AUTO_INCREMENT
锁定模式的行为,以及每种锁定模式如何影响复制。 在启动时使用innodb_autoinc_lock_mode 配置参数配置 AUTO_INCREMENT 锁定模式。
以下术语用于描述innodb_autoinc_lock_mode
设置:
- INSERT-like 语句
所有在表中生成新行的语句,包括INSERT,INSERT ... SELECT,REPLACE,REPLACE ... SELECT和LOAD DATA
。 包括simple-inserts,bulk-inserts和mixed-mode插入。
- Simple inserts
可以预先确定要插入行数的语句(最初处理该语句时)。 这包括单行和多行 INSERT 和没有嵌套的子查询的 REPLACE 语句,但是 INSERT ... ON DUPLICATE KEY UPDATE
并不算简单插入。
- Bulk inserts
事先不知道要插入行数(以及所需的自动增量值的数目)的语句。 这包括INSERT ... SELECT,REPLACE ... SELECT和LOAD DATA语句,但不包括普通INSERT。 在处理每一行时,InnoDB 为每行的AUTO_INCREMENT
列分配一个新值。
- Mixed-mode inserts
这些是Simple inserts语句,用于指定一些(但不是全部)新行的自动增量值。下面是一个示例,其中c1是表t1的AUTO_INCREMENT 列:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另一种类型的Mixed-mode inserts是INSERT ... ON DUPLICATE KEY UPDATE,在最坏的情况下,实际上是先进性INSERT
,然后是 UPDATE
,在此期间,更新阶段可能会也可能不会使用AUTO_INCREMENT
列的分配值。
innodb_autoinc_lock_mode配置参数有三种可能的设置。 traditional,consecutive或interleaved锁定模式的设置分别为0、1或2。 从MySQL 8.0开始,interleaved锁定模式(innodb_autoinc_lock_mode = 2
)是默认设置。 在MySQL 8.0之前,consecutive锁定模式是默认设置(innodb_autoinc_lock_mode = 1
)。
MySQL 8.0 中Mixed-mode inserts模式的默认设置反映了从基于语句的复制到基于行的复制作为默认复制类型的变化。 基于语句的复制需要连续的AUTO_INC锁定模式,以确保为给定的SQL语句序列以可预测和可重复的顺序分配自动增量值,而基于行的复制对SQL语句的执行顺序不敏感 。
innodb_autoinc_lock_mode = 0
(传统锁模式)
传统的锁定模式提供了与 MySQL 5.1 中引入 innodb_autoinc_lock_mode 配置参数之前相同的行为。提供了传统的锁定模式选项是为避免语义上存在的差异而造成的向后兼容,性能测试以及解决Mixed-mode inserts可能产生的问题。
在这种锁定模式下,所有INSERT-like
的语句都将获得特殊的表级AUTO-INC锁,以便将其插入具有AUTO_INCREMENT 列的表中。此锁通常保持在语句的末尾(而不是事务的末尾),以确保为给定的INSERT
语句序列以可预测和可重复的顺序分配自动增量值,并确保AUTO_INCREMENT
为任何给定语句分配的值都是连续的。
对于基于语句的复制,这意味着在副本服务器上复制SQL语句时,自动增量列使用与源服务器相同的值。执行多个INSERT
语句的结果是确定性的,并且副本将复制与源上相同的数据。如果对由多个INSERT
语句生成的自动增量值进行交织,则两个并发INSERT
语句的结果将是不确定的,并且无法使用基于语句的复制可靠地传播到副本服务器。
为了清楚起见,请考虑使用该表的示例:
CREATE TABLE t1 ( c1 INT(11) NOT NULL AUTO_INCREMENT, c2 VARCHAR(10) DEFAULT NULL, PRIMARY KEY (c1)) ENGINE=InnoDB;
假设有两个事务正在运行,每个事务都将行插入到具有AUTO_INCREMENT
列的表中。 一个事务使用插入1000行的INSERT ... SELECT
语句,另一事务使用插入一行的简单INSERT
语句:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
InnoDB
无法预先告知Tx1
的INSERT
语句中从SELECT
检索了多少行,并且随着语句的进行,它每次为一行分配一个自动递增值。使用表级锁(保持在该语句的末尾),一次只能执行一个引用表t1
的INSER
T语句,并且不同语句生成的编号也不是交错的。 Tx1 INSERT ... SELECT
语句生成的自动增量值是连续的,并且Tx2
中INSERT语句使用的(单个)自动增量值小于或大于用于Tx1
的所有增量,这具体取决于哪个语句首先执行。
只要从二进制日志重播时,SQL语句以相同的顺序执行(使用基于语句的复制时,或在恢复方案中),结果与Tx1
和Tx2
首次运行时的结果相同。因此,在语句结束之前保持的表级锁使使用自动增量的INSERT
语句可以安全地用于基于语句的复制。但是,当多个事务同时执行insert
语句时,这些表级