目录 AUTO_INCREMENT锁模式 传统锁模式 (traditional) 连续锁模式 (consecutive) 交叉锁模式 (interleaved) AUTO_INCREMENT锁模式使用说明 用于复制 自增值缺失与间隙 为自增列指定NULL或者0 为自增列指定负值 自增列值大于列类型最大的整型值 大量插入导致的间隙 混合模式插入自增列值分配 修改自增列当中的值 AUTO_INCREMENT计数器的初始化 参考 正文 之前有碰到过开发同事指出一张InnoDB表的自增列 AUTO_INCREMENT 值莫明的变大，由于这张表是通过mysqldump导出导入的。 问题排查： 1、首先，查看表表义的sql部分的 auto_increment 值部分是正常，所以排除是导入表问题所引起的； 2、最后，经过沟通了解怀疑是插入时指定自增列的值，并且值过大，随之发现自增列的值出错时又进行大量删除时引起的问题。 为了验证这个怀疑的准确性，同时学习下InnoDB处理 AUTO_INCREMENT 的机制，因此在测试环境做了测试总结。 本文使用的MySQL版本为官方社区版 5.7.24。 (root@localhost) [test] > select version(); +------------+ | version() | +------------+ | 5.7.24-log | +------------+ 1 row in set (0.00 sec) 测试环境测试表参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html (root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) (root@localhost) [test] > show columns from t1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | c1 | int(11) | NO | PRI | NULL | auto_increment | | c2 | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) 返回目录 AUTO_INCREMENT锁模式 InnoDB自增锁的模式由参数 innodb_autoinc_lock_mode 在启动时指定，这是一个只读参数，并不能在实例运行中动态修改参数值。参数值选项如下： innodb_autoinc_lock_mode = {0|1|2} InnoDB表insert语句主要可以分为三种类型： Simple inserts(简单插入) 可以通过语句预先判断插入的行数。包括不包含子查询的单行、多行 INSERT 和 REPLACE 语句，还有语句 INSERT ... ON DUPLICATE KEY UPDATE。 Bulk inserts(大量插入) 无法通过语句预先判断插入的行数。包括 INSERT ... SELECT 、 REPLACE ... SELECT 和 LOAD DATA 语句。InnoDB每处理一行才会为 AUTO_INCREMENT 列分配一个值。 Mixed-mode inserts(混合模式插入) 在简单插入语句当中，有的行有为自增列指定值，而有的行没有为自增列指定值。例如： INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 其中c1为自增列。 还有一种混合模式插入语句 INSERT ... ON DUPLICATE KEY UPDATE ，这类语句InnoDB为自增列预分配的值有可能被使用，也有可能不被使用。 传统锁模式 (traditional) innodb_autoinc_lock_mode = 0 在这个模式下，所有的 INSERT 语句在插入有自增属性的列时都要获取一个特殊的 AUTO-INC 表级锁。该锁的持有时间到语句结束(而不是到事务结束，一个事务中可能包含多条语句)，它能够确保为有自增属性列在 INSERT 一行或者多行数据时分配连续递增的值。 连续锁模式 (consecutive) innodb_autoinc_lock_mode = 1 这是默认的锁模式。在这个模式下，大量插入每条语句执行时都将获得特殊的表级 AUTO-INC 锁，语句执行完成后释放。每次只有一条语句可以执行并持有 AUTO-INC 锁。 Bulk inserts(大量插入) 如果大量插入的源表和目标表是不同的，则在源表第一行数据获得共享锁之后，目标表就加上 AUTO-INC 表锁； 如果大量插入的源表和目标表是同一张表，当源表选取所有行获得共享锁之后，目标表才加上 AUTO-INC 表锁。 Simple inserts(简单插入) 通过 MUTEX(轻量级的锁) 而不是 AUTO-INC特殊表锁控制插入分配自增属性列的值； MUTEX 只在分配值的过程中持有，而无需等到语句结束，并且性能花销更少； 简单插入不持有 AUTO-INC 锁，但如果其他事务持有，需等待其他事务释放，就像大量插入那样。 交叉锁模式 (interleaved) innodb_autoinc_lock_mode = 2 在这种锁模式下，没有插入语句使用 AUTO-INC 表级锁，并且多条语句可以并发执行。这是最快并且扩展性最好的锁模式，但是如果binlog使用基于语句级复制的在从库重放SQL语句时是不安全的。 AUTO_INCREMENT锁模式使用说明 用于复制 基于语句级别的复制，当 innodb_autoinc_lock_mode = 0 | 1 时，主从使用的是相同的自增列值。当 innodb_autoinc_lock_mode = 2 或者主从使用不同的 innodb_autoinc_lock_mode 时，主从无法保证使用相同的自增列值； 基于行级别和复合模式的复制，innodb_autoinc_lock_mode 的所有取值都是安全的，因为SQL语句执行顺序对基于行级别的复制没影响。 自增值缺失与间隙 无论 AUTO_INCREMENT 处于哪种锁模式下，即 innodb_autoinc_lock_mode 的所有取值情况下，在一个事务当中自增列分配的值是不能被回滚的。这会导致事务回滚了，但是自增列分配的值却消失了，自增列分配的值是无法随着事务回滚而重复利用，这样就自增列上的值就产生了间隙。 测试： --1、 开启一个事务 (root@localhost) [test] > begin; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test] > insert into t1(c2) values('aa'); Query OK, 1 row affected (0.00 sec) (root@localhost) [test] > select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aa | +----+------+ 1 row in set (0.00 sec) (root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) --2、事务回滚，不提交 (root@localhost) [test] > rollback; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test] > select * from t1; Empty set (0.00 sec) (root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) --3、再次开启事务，插入值并提交 (root@localhost) [test] > begin; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test] > insert into t1(c2) values('aaa'); Query OK, 1 row affected (0.02 sec) (root@localhost) [test] > select * from t1; +----+------+ | c1 | c2 | +----+------+ | 2 | aaa | +----+------+ 1 row in set (0.00 sec) (root@localhost) [test] > commit; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 可以看出自增列分配的值是不会再出现 c1=1 的。 为自增列指定NULL或者0 无论 AUTO_INCREMENT 处于哪种锁模式下，如果在 INSERT 语句为自增列指定 NULL 或者 0 时，InnoDB认为并没有为自增列指定值，同时也会为其分配值。 测试： (root@localhost) [test] > select * from t1; +----+------+ | c1 | c2 | +----+------+ | 2 | aaa | +----+------+ 1 row in set (0.00 sec) (root@localhost) [test] > insert into t1 values(NULL,'bbb'),(0,'ccc'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 (root@localhost) [test] > select * from t1; +----+------+ | c1 | c2 | +----+------+ | 2 | aaa | | 3 | bbb | | 4 | ccc | +----+------+ 3 rows in set (0.00 sec) (root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 为自增列指定负值 无论 AUTO_INCREMENT 处于哪种锁模式下，自增列分配值机制不会生效，即为自增列指定负值是不影响 AUTO_INCREMENT 值的。 测试： (root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec) (root@localhost) [test] > insert into t1 values(-1,'ddd'); Query OK, 1 row affected (0.01 sec) (root@localhost) [test] > select * from t1; +----+------+ | c1 | c2 | +----+------+ | -1 | ddd | | 2 | aaa | | 3 | bbb | | 4 | ccc | +----+------+ 4 rows in set (0.00 sec) (root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec) 自增列值大于列类型最大的整型值 无论 AUTO_INCREMENT 处于哪种锁模式下，自增列分配的值如果大于自增列所属字段类型的最大值，则自增列分配值机制就不会生效。 测试： 在MySQL当中，INT类型的最大值为 -2147483648~2147483647 (root@localhost) [test] > select * from t1; +----+------+ | c1 | c2 | +----+------+ | -1 | ddd | | 2 | aaa | | 3 | bbb | | 4 | ccc | +----+------+ 4 rows in set (0.00 sec) (root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec) (root@localhost) [test] > insert into t1 values(2147483647,'eee'); Query OK, 1 row affected (0.02 sec) (root@localhost) [test] > select * from t1; +------------+------+ | c1 | c2 | +------------+------+ | -1 | ddd | | 2 | aaa | | 3 | bbb | | 4 | ccc | | 2147483647 | eee | +------------+------+ 5 rows in set (0.00 sec) (root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 2147483647 | +----------------+ 1 row in set (0.00 sec) 可以看出自增列 AUTO_INCREMENT 并不会分配。 大量插入导致的间隙 当参数值 innodb_autoinc_lock_mode 设置为 0 或 1 时，每条语句生成的自增列值都是连续的，不会产生间隙。因为表级 AUTO-INC 锁会一直持有直到语句结束，并且同一时间只有一条语句在执行； 当参数值 innodb_autoinc_lock_mode 设置为 2 时，在大量插入时有可能会产生间隙，但是只有当并发执行 INSERT 语句时。 对于设置成 1 或者 2 情形下，在连续的语句之间可能产生间隙，因为对于大量插入InnoDB并不清楚每条语句所需自增量值数量。 混合模式插入自增列值分配 测试表： -- t1表：表中无数据，但自增列下一个分配值从101开始 (root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) (root@localhost) [test] > select count(*) from t1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) -- t2表：表中有100万行数据，并且自增列值是连续的 (root@localhost) [test] > show create table t2\G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) (root@localhost) [test] > select count(*) from t2; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.22 sec) (root@localhost) [test] > select min(c1),max(c1) from t2; +---------+---------+ | min(c1) | max(c1) | +---------+---------+ | 1 | 1000000 | +---------+---------+ 1 row in set (0.01 sec) innodb_autoinc_lock_mode = 0 (root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 0 | +--------------------------+-------+ 1 row in set (0.02 sec) -- 1、开启session 1执行TRX1 TRX1: insert into t1(c2) select c2 from t2; (root@localhost) [test] > insert into t1(c2) select c2 from t2; Query OK, 1000000 rows affected (6.37 sec) Records: 1000000 Duplicates: 0 Warnings: 0 -- 2、在TRX1执行期间开启session 2执行TRX2 TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); (root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); Query OK, 4 rows affected (5.01 sec) Records: 4 Duplicates: 0 Warnings: 0 -- 3、查看TRX2插入值的记录 (root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d'); +---------+------------+ | c1 | c2 | +---------+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 1000101 | test_inc_b | | 1000102 | test_inc_d | +---------+------------+ 4 rows in set (0.34 sec) -- 4、查看当前AUTO_INCREMENT值 (root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 1000103 | +----------------+ 1 row in set (0.00 sec) TRX1：持有了 AUTO-INC 表锁，自动分配了101~1000100的自增值，并保证是连续的； TRX2：需等待 AUTO-INC 锁的释放，自动分配了1000101~1000102的自增值。 可以看出下一个自增列值为 103，因为自增列的值是在每条插入语句执行时分配的，而不是一开始就分配完的。 innodb_autoinc_lock_mode = 1 (root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 1 | +--------------------------+-------+ 1 row in set (0.01 sec) -- 1、开启session 1执行TRX1 TRX1: insert into t1(c2) select c2 from t2; (root@localhost) [test] > insert into t1(c2) select c2 from t2; Query OK, 1000000 rows affected (5.88 sec) Records: 1000000 Duplicates: 0 Warnings: 0 -- 2、在TRX1执行期间开启session 2执行TRX2 TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); (root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); Query OK, 4 rows affected (4.38 sec) Records: 4 Duplicates: 0 Warnings: 0 -- 3、查看TRX2插入值的记录 (root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d'); +---------+------------+ | c1 | c2 | +---------+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 1048661 | test_inc_b | | 1048662 | test_inc_d | +---------+------------+ 4 rows in set (0.32 sec) -- 4、查看当前AUTO_INCREMENT值 (root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 1048665 | +----------------+ 1 row in set (0.00 sec) -- 5、查看t1表的前10行记录、后10行记录和总行数 (root@localhost) [test] > select * from t1 order by c1 asc limit 10; +-----+------------+ | c1 | c2 | +-----+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 101 | CwAkHbEWs | | 102 | re | | 103 | uqrQbj | | 104 | SQ | | 105 | ojyPkMA | | 106 | 03qNqZ | | 107 | G8J | | 108 | Uo3 | +-----+------------+ 10 rows in set (0.00 sec) (root@localhost) [test] > select * from t1 order by c1 asc limit 999994,10; +---------+------------+ | c1 | c2 | +---