MySQL8.0默认为交错模式

发布时间：2025-06-24 20:01:02 作者：北方职教升学中心阅读量：369

自增值生成模式

上面的都是单线程下自增值的生成示例，但在并发时，多个事务可能会同时向表中插入数据，事务之间存在争用。

MySQL8.0默认为交错模式。

MySQL8.0以后，这个计数器的值会在每次变更时写入重做日志和数据字典（保存到磁盘上）。

三、但在多主复制、MySQL为并发场景下自增值的生成提供了3种不同的模式。服务器重启时直接读取数据字典即可，不必再通过查询表初始化。

insertintot3 values(null);

在这里插入图片描述

监控自增值的使用比例

由于数据类型最大值的限制，自增并不是没有上限的。

setsql_mode=concat(@@session.sql_mode,',no_auto_value_on_zero');insertintot values(0,'Tom');select*fromt;

在这里插入图片描述

1.2 自增列特性

自增列除了让其自动生成值之外，也可以显式赋值，使用中注意以下几点：

显式赋值可能导致大量值被浪费
事务回滚不会回滚已使用的自增值
truncate table清除数据的同时也会让自增值初始化
alter table … auto_increment=1 可以让自增值恢复到最大可用值，消除间隙（并不会设定成1）

自增列可以显式赋值，但如果指定的值超过目前auto_increment的最大值，则MySQL会从你指定的值之后开始继续递增，即使前面有可用的值也不再使用，示例中显式指定id列为1000，那么下一条数据会从1001开始自增。

示例：这里又新建2张表，设置自增列类型为tinyint（8位有符号整数，范围为-128~127），然后将自增值分别设置为64和127

createtablet2(id tinyintprimarykeyauto_increment);createtablet3(id tinyintprimarykeyauto_increment);altertablet2 auto_increment=64;altertablet3 auto_increment=127;

在这里插入图片描述

使用下面的SQL即可查询test数据库下所有表的自增列使用比例（可根据情况调整，去除t.table_schema='test’可以查询所有库）：

selectt.table_schema,t.table_name,t.auto_increment,c.column_type,concat(round((t.auto_increment/(casedata_typewhen'tinyint'thenif(column_type like'%unsigned',255,127)when'smallint'thenif(column_type like'%unsigned',65535,32767)when'mediumint'thenif(column_type like'%unsigned',16777215,8388607)when'int'thenif(column_type like'%unsigned',4294967295,2147483647)when'bigint'thenif(column_type like'%unsigned',18446744073709551615,9223372036854775807)end))*100,2),'%')used_percentagefrominformation_schema.tablestjoininformation_schema.columnsc ont.table_schema =c.table_schema andt.table_name =c.table_namewheret.auto_incrementisnotnullandc.extra='auto_increment'andt.table_schema='test';

在这里插入图片描述

从结果可以看到t3表的自增列已经使用100%，再插入新的数据就会报错了。

在基于语句的主从复制（Statement-Based Replication）模式下insert语句在主从可以生成相同的值。

对于混合插入类型（多行简单插入中，部分行显式指定自增值，部分行未指定），连续模式下会预先生成比要插入行更多的自增值，然后以连续方式分配给需要自增的行，多余的值就丢弃了。Insert语句对该列即使不提供值，MySQL也会自动为该列生成递增的唯一标识，因此这个特性广泛用于主键的自动生成。

3.3 交错模式

交错模式下（innodb_autoinc_lock_mode=2），不使用表锁，任何并发insert都可以同时执行，这意味着多条insert语句生成的自增值是可能是交错的，单条insert语句无法保证生成连续的自增值，但这种模式并发性能是最好的。

一张表中只能指定一个自增列，且必须建立索引，示例中 id列没有指定为索引列，建表报错（must be defined as a key）：

createtablet2(id intauto_increment,name varchar(32));

在这里插入图片描述

指定自增列为主键，创建成功：

createtablet (id intprimarykeyauto_increment,name varchar(32));

在这里插入图片描述

插入数据时，即使insert语句未包含自增列，MySQL也会自动为该列生成值：

insertintot(name)values('Vincent');-- 未指定id列select*fromt;

在这里插入图片描述

如果指定了0或null，同样也可以生成自增值：

insertintot values(0,'Victor'),(null,'Grace');-- 指定0和nullselect*fromt;

在这里插入图片描述

注意：由于0会触发自增，如果ID列本来保存的数据就包含0，那么在数据导出和导入过程中，数字0可能会因此触发自增而被修改，导致数据不一致。

MySQL数据库为列提供了一种自增属性，当列被定义为自增时。

3.2 连续模式

连续模式（innodb_autoinc_lock_mode=1）是对传统模式的优化，对于批量插入这种不确定需要需要多少自增值的insert，会和传统模式一样，使用表级锁直至insert语句执行完成。传统模式只是为了向前兼容，现在已经不会使用了。

为了保证不出现冲突，可以设置auto_increment_offset和auto_increment_increment来修改自增的初始值和步长，使各个写入点产生的自增值不重叠（可以在会话和全局级别修改）。

MySQL8.0以前的版本默认为连续模式。3种模式由innodb_autoinc_lock_mode控制（只读变量，修改需要重启），对应的值分别为0, 1, 2：

0, 传统模式（Traditional Lock Mode）
1, 连续模式（Consecutive Lock Mode）
2, 交错模式（Interleaved Lock Mode）

在解释3种模式的区别前，先了解一下insert语句的分类，insert语句可以分为以下3类：

简单插入（Simple Inserts），如单记录insert，或者多记录insert，在解析SQL时就可以确定要加载的记录数（即要生成自增值数量）
批量插入（Bulk Inserts），如insert … select, load data等，在解析SQL时不确定需要加载的记录数
混合插入（Mixed-Mode Inserts），在多记录简单插入中，为自增列显式指定了部分值，如 insert into … values (null,‘a’), (5, ‘b’), (null, ‘c’)

3.1 传统模式

在传统模式下（innodb_autoinc_lock_mode=0），所有类型的insert都会使用表级X锁，并且持有到insert语句结束，这意味着同一时间只有1条insert语句可以执行，但可以保证单条insert语句产生的自增值是连续的。自增计数器

三、

四、自增列的用法

1.1 基本用法
1.2 自增列特性
1.3 通过last_insert_id()获取自增值

二、自增值生成模式

3.1 传统模式
3.2 连续模式
3.3 交错模式

四、调整自增偏移

五、

一、
1.1 基本用法
在创建表时，只需在某个整型列（tinyint,smallint, mediumint, int, bigint）上指定auto_increment，即可打开自增属性。
而对于可以事先确定插入记录数的简单插入，MySQL会用mutex（闩，更轻量级的锁）仅在预先分配自增值时锁定，在insert语句执行完成前就已经释放了。
```
insertintot values(1000,'Jerry');insertintot values(null,'Spike');
```
事务中如果使用了自增值，即使回滚，自增值也不会恢复，示例中的事务消耗了2个自增值(1002, 1003)，然后事务回滚了，但是下一条insert语句自增值是从1004开始的：
```
begin;insertintot values(null,'Spike');insertintot values(null,'Spike');rollback;-- 事务回滚insertintot values(null,'Tyke');-- 自增值不会回滚select*fromt;
```
示例删除了id为1000及以上的数据后，使用alter table … auto_increment=1使自增值恢复到当前数据的最大值：
```
deletefromt whereid>=1000;altertablet auto_increment=1;insertintot values(null,'Jerry');select*fromt;
```
1.3 通过last_insert_id()获取自增值
MySQL提供了函数last_insert_id()，用于获取上一个成功执行的insert语句所生成的第一个自增值：
```
truncatetablet;insertintot values(null,'Vincent');selectlast_insert_id();
```
单一insert语句如果插入多行，获取的是语句中第一个产生的自增值，而不是最后一个，下面insert语句插入了2条记录，但last_insert_id()返回的是2而不是3（虽然表中id增长到3）：
```
insertintot values(null,'Victor'),(null,'Grace');selectlast_insert_id();select*fromt;
```
如果在事务中手动回滚，last_insert_id()的值也是不会回滚的，其代表的是曾经成功插入的自增值，而不判断事务是否最终提交（有一定误导性，不能用作判断实际插入的值）：
```
begin;insertintot values(null,'Vincent');insertintot values(null,'Vincent');-- 成功插入，last_insert_id()为5rollback;-- 事务回滚selectlast_insert_id();-- last_insert_id()依然是5，但数据实际未插入
```
last_insert_id(expr)还有个可选的参数，如果提供参数expr，则会返回该值，并将expr记录为下一个last_insert_id()的返回值：
```
selectlast_insert_id(100);selectlast_insert_id();
```
二、组复制这类可以多点写入的环境，可能会产生冲突。自增计数器
在MySQL8.0之前，对于auto_increment的值会在内存中维护一个计数器（不保存在磁盘上），在服务器启动时会对每张表执行类似select max(auto_column) from t for update;语句获取当前表中的最大自增值，用于初始化这个计数器。当到达上限时数据无法继续插入，导致业务中断，因此DBA需要监控自增值的使用情况，在达到上限之前及时采取扩容措施。调整自增偏移
自增列的默认初始值为1，步长为1。
例如现在有一个双主环境，可以在一台主机上配置初始值为1（默认），步长为2，这样生成的自增值都是单数：
```
setauto_increment_increment=2;
```
而在另一台主机上配置初始值为2，步长为2，生成自增值都是双数，这样可以避免并发写入时发生冲突：
```
setauto_increment_offset=2;setauto_increment_increment=2;
```
五、连续模式也可以保证基于语句的复制主从可以生成相同的自增值，但性能比传统模式更好。这种情况可以打开sql_mode参数中的no_auto_value_on_zero选项（可以在会话和全局修改），打开该选项后，只有null可以触发自增，0不再触发。
因为缺乏了表锁控制，多条insert并发插入，在主从执行时无法保证自增值完全相同，此模式对基于语句的复制（应该没人用了吧？）是不安全的，建议配合基于行的复制（Row-Based Replication）使用（MySQL8.0默认）。监控自增值的使用比例