Light

Dark

MySQL主键自增有几种模式？

MySQL主键自增有几种模式？

Sep 25, 2024 About 2 mins

banner

前言

在大多数的业务场景下，我们的数据表的一般会默认使用主键自增长(AUTO_INCREMENT)模式，在执行insert语句时，MySQL会自动为数据行生成主键ID，保证其单调递增和不重复的。一般情况下，我们很少会关注AUTO_INCREMENT的策略模型，事实上，InnoDB提供了一个可配置的自增长模型机制，可以显著提高向带有AUTO_INCREMENT列的表添加记录的SQL语句的可扩展性和性能。

本篇，我们就来简要了解一下MySQL InnoDB的主键自增长策略模式。

INSERT种类

MySQL将INSERT操作分为三类：

1、Simple inserts

插入的记录行数是可以事先确定的。包括最常见的单条INSERT、REPLACE，但不包括INSERT ... ON DUPLICATE KEY UPDATE

2、Bulk inserts

插入的记录行数是无法事先确定的。包括 INSERT ... SELECT, REPLACE ... SELECT, LOAD DATA，不包括普通的INSERT操作

3、Mixed-mode inserts

该种类型也属于Simple inserts类型，插入的记录行数是可以事先确定的，但与Simple inserts不同的是，INSERT语句中有部分记录自行指定了自增长字段的值，例如：

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

该模式也包括了INSERT ... ON DUPLICATE KEY UPDATE

自增长锁模型（AUTO_INCREMENT Lock Modes）

InnoDB引擎提供了三种锁模型来支持AUTO_INCREMENT ，可以通过innodb_autoinc_lock_mode参数来进行指定，取值范围为(0，1，2)，分别对应”传统模式”、”连续模式”、”交错模式”，下面我们来依次看一下三种锁模式。

传统锁模式(Traditional lock mode)

innodb_autoinc_lock_mode = 0

该种模式下，它会对所有的Insert模式操作都去获取一个特殊的表级别的自增长锁(AUTO-INC)，这种锁会持续到语句结束，以确保执行的Insert语句以可预测且可重复的顺序分配自动递增值。

这种模式更多是为了兼容老版本的MySQL运行。实际生产环境中不推荐使用。

由于对于所有的Insert执行时，都会尝试去获取表级别的自增锁，可以预见该种模式的并发性会非常的差，当同时存在多个事务同时执行Insert操作，只有其中一个事务可以获取到自增锁，而其他的事务必须等待其执行完毕释放锁后，才可以进行执行Insert操作。

我们来看一个例子：

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

我们有一张表t1，假定同时存在两个事务执行，并都使用自增长字段：

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

对于上面的场景，两个事务无法同时并行操作，如果Tx1先执行，则会获取t1的自增锁，直到该条语句执行结束，然后Tx2才可以重新获取锁进行执行插入，需要注意的是，表级自增锁的持有时间范围是语句级别，并非事务级别。

该种模式下，虽然对于Insert的并发度非常差，但可以保证自增字段的连续性，并且对于基于SQL BINLOG的主从同步，可以保证从库的自增字段与主库一致。

连续锁模式(Consecutive lock mode)

innodb_autoinc_lock_mode = 1

在MySQL8.0之前，该种模式是InnoDB的默认自增模式。

该种模式下，对于普通Insert语句，申请到自增锁后就马上释放，并非语句执行结束。但是对于bulk insert这样的批量插入数据语句，仍然会申请表级别的自增锁，会持有锁直到语句结束后才被释放。

需要注意的是，虽然单条Insert语句会避免使用表级自增锁，但是当其他的事务正在持有表级自增锁，那么在当前事务内，Insert操作时也会被阻塞，等待其他事务释放锁资源。

相较于传统锁模式，连续锁模式在语义上没有大的区别，但对于普通单条的Insert并发度有一定的提升。

交叉锁模式(Interleaved lock mode)

innodb_autoinc_lock_mode = 2

在MySQL8.0中，该种模式是InnoDB的默认自增模式。

该种模式下，对于Insert语句的执行不会使用表级别的自增锁，这意味着多个事务的多个Insert可以并发执行，是所有模型中执行效率最高的模式。

但在该模式下，存在两个问题：

1、在这种模式下，如果binlog格式为statement (SBR)，可能会引起数据不一致。也就是说，在主库的自增字段的值，同步到从库时，可能会不一致。

2、对于bulk inserts语句来说，它得到的自增值可能不是连续的。但是对于普通的Insert语句，则不会出现这个问题。

总结

OK，我们了解了MySQL InnoDB中自增长字段的三种执行模式，我们简单进行一下总结：

Traditional lock mode传统锁模式：

此种模式可以说是最保守的执行模式，可以完全保证自增长键的连续性，但由于是通过表级锁进行的实现，性能较差，主要是为了兼容老版本，在目前的生产环境中不可能采用

Consecutive lock mode连续锁模式：

传统锁模式的进阶版本，MySQL5.7及其之前的默认模式，该种模式对于普通的Insert语句进行了优化，相较于传统锁模式大大的提升了并发度，但对于bulk inserts等批量操作，仍会进行等待锁资源，如果可以保证不会使用bulk inserts等操作的话，该种模式是没有问题的，对于主从复制也较为友好，对于任何种类的binlog同步类型，都可以保证主从一致性

Interleaved lock mode交叉锁模式：

MySQL8.0之后版本的默认模式，目前看起来的最优解模式，该种模式完全不使用表级锁，可以真正的实现多事务Insert并发执行，缺点是对于bulk inserts语句执行时可能会出现自增长值不连续的情况，同时对于主从复制的statement binlog，无法保证主从的一致性。

本篇关于MySQL自增长键的介绍就到这里，关于更多的细节，可以查阅官方文档的解释：

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html