注意:mysql8.0之前的版本,InnoDB存储引擎的表自增id可能出现重复利用的情况。


    这在很多场景下可能导致问题,包括但不限于:主备切换、历史数据迁移等场景。在bug#199下面一大堆的回复里,可以看到大量的同行抱怨。如,假设t1有个历史表t1_history用来存t1表的历史数据,那么mysqld重启前,ti_history表中可能已经有了(2,2)这条数据,而重启后我们又插入了(2,2),当新插入的(2,2)迁移到历史表时,会违反主键约束。这类问题是否在数据迁移中会出现呢,我们也需要注意一下。比如我们使用mysqldump导出数据,然后导入到另外一个环境。mysqldump导出数据里面是指定了自增长值的方式,而非空。


建表时可以指定AUTO_INCREMENT值,不指定时默认为1,这个值表示当前自增列的起始值大小,如果新插入的数据没有指定自增列的值,那么自增列的值即为这个起始值。对于InnoDB表,这个值没有持久到文件中。而是存在内存中(dict_table_struct.autoinc)。那么又问,既然这个值没有持久下来,为什么我们每次插入新的值后,show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的(ha_innobase::update_create_info)。


   知道了AUTO_INCREMENT是实时存储内存中的。那么,mysqld重启后,从哪里得到AUTO_INCREMENT呢?内存值肯定是丢失了,实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT,而这种方法就是造成自增id重复的原因。


InnoDB AUTO_INCREMENT锁定模式:

   innodb_autoinc_lock_mode 配置参数 有三种可能的设置 。对于“ 传统 ”,“ 连续 ”或 “ 交错 ”锁定模式,设置分别为0,1或2 。从MySQL 8.0开始,交错锁定模式(innodb_autoinc_lock_mode=2)是默认设置。在MySQL 8.0之前,连续锁定模式是默认值(innodb_autoinc_lock_mode=1)。


《在MySQL8.0的解决思路:》

   将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响,计数器写入到redo log中,并不会马上刷新。


--因自增主键没有持久化而出现问题的常见场景:

1. 业务将自增主键作为业务主键,同时,业务上又要求主键不能重复。

2. 数据会被归档。在归档的过程中有可能会产生主键冲突。

 

所以,强烈建议不要使用自增主键作为业务主键。刨除这两个场景,其实,自增主键没有持久化的问题并不是很大,远没有想象中的”臭名昭著“。

 

--:最后,给出一个归档场景下的解决方案,

 

创建一个存储过程,根据table2(归档表)自增主键的最大值来初始化table1(在线表)。这个存储过程可放到init_file参数指定的文件中,该文件中的SQL会在数据库启动时执行。

DELIMITER ;;

CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))

BEGIN

set @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');

    prepare stmt from @qry;

execute stmt;

deallocate prepare stmt;

    set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');

    prepare stmt from @qry;

execute stmt;

deallocate prepare stmt;

IF @max1 < @max2 THEN

    set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;

SELECT 'updated' as `status`;

else

SELECT 'no update needed' as `status`;

END IF;

END ;;

DELIMITER ;

###################################################################


下面复现MySQL8.0之前,自增主键复用的情况:


----创建测试表emp:


MySQL [test]> create table emp (id int auto_increment,name varchar(10),primary key (id));
MySQL [test]> insert into emp values (1,'zhang');
MySQL [test]> insert into emp values (null,'liu');
MySQL [test]> insert into emp values (null,'huang');
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | liu   |
|  3 | huang |
+----+-------+
3 rows in set (0.00 sec)


---从下面的信息可以看出,emp表id自增的下一个数字是 4

MySQL [test]> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

---删除id=2和3的数据,然后再插入一条数据

MySQL [test]> delete from emp where id=2 or id=3;
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
+----+-------+
1 row in set (0.01 sec)
MySQL [test]> insert into emp values (null,'hhhh');
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  4 | hhhh  |
+----+-------+
2 rows in set (0.00 sec)

(从上面的结果可以看出,如果不重启数据库的情况下,虽然把前面的数据删除了,但是在此插入数据 它的自增id还是和没删除数据之前的顺序递增。)


---删除id为4的数据,只保留第一行数据,然后重启MySQL如下:

MySQL [test]> delete from emp where id=4;
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
+----+-------+
1 row in set (0.00 sec)

----重启MySQL后,再次插入一条数据:

MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
+----+-------+
1 row in set (0.00 sec)
MySQL [test]> show create table emp\G    ---(重启完MySQL在此查看该表的自增id,这时候就是2了)
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

---此时再插入一条数据,会发现自增id重复利用了:

MySQL [test]> insert into emp values (null,'feng');
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | feng  |
+----+-------+
2 rows in set (0.00 sec)

从上面的测试,我们看到在插入新的数据之前AUTO_INCREMENT为2,然后插入了(null,'feng'), 上面的测试反映了mysqld重启后,InnoDB存储引擎的表自增id可能出现重复利用的情况。如果存在从库可能会导致数据库不一致的情况!!!!!!!


注意:

  另外,当MySQL开启一个事务后,有类INSERT操作,自增值就会增加;但是当事务回滚后,自增值并不会减小。也就是说自增值会有空洞。


¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥¥


二、不同的insert插入方式,会对有自增id有不同影响,如下:


1、第一种:带有 null 值的写法

mysql> create table emp(id int auto_increment, a int, primary key (id)) engine=innodb;
mysql> insert into emp values (1,2),(2,2),(3,2);
MySQL [test]> show create table emp\G     ---这时候查看该表的自增值是4
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)     
MySQL [test]>  insert into emp values (4,2),(null,2),(null,2);   ---使用null的方式插入值
MySQL [test]> show create table emp\G   ----这时候会发现自增值变成了8,但是查看该表的数据id列最大是6,
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MySQL [test]> select * from emp;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
|  4 |    2 |
|  5 |    2 |
|  6 |    2 |
+----+------+
6 rows in set (0.01 sec)
MySQL [test]> insert into emp values (null,3);   ---此时,向该表插入一条数据,id的值就变成了8
MySQL [test]>  select * from emp;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
|  4 |    2 |
|  5 |    2 |
|  6 |    2 |
|  8 |    3 |
+----+------+
7 rows in set (0.00 sec)
从上面的测试可以看出,采用null写法的时候,自增长值会多增加一个值。
2、第二种:使用insert into ...select 方式插入数据,如下:
mysql> create table t1(id int auto_increment primary key,name varchar(255));
mysql> create table t2(name varchar(255))engine=innodb;
mysql> insert into t2 values('aa'),('bb');
mysql> insert into t1(name) select *from t2;   ---将t2表的数据插入到t1
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)
mysql> show create table t1;   ---然后查看下t1表的自增值,发现现在是4了,而数据只有2条
| Table | Create Table                                                                                           
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

这也说明了,采用insert .. select方式,自增长也会多增加一个值。


注意:

   我们可以看到第一种带NULL值的写法,自增长值会多增加一个值;第二种insert .. select,自增长也会多增加一个值。这个会带来什么问题呢?你会发现从库自增长值却是正常的(当复制格式为ROW时),这个时候其实也就是主从数据不一致了,但影响不大,除非出现记录ID大于自增长ID,那样插入数据重复会报错。


究其原因,和insert语句的定位也有关系,目前有这几类insert语句。


1、simple insert,如insert into t(name) values(‘test’)

2、bulk insert,如load data | insert into … select …. from ….

3、mixed insert,如insert into t(id,name) values(1,’a’),(null,’b’),(5,’c’);


这个和参数innodb_autoinc_lock_mode有很大的关系,默认参数值为1。innodb_autoinc_lock_mode这个参数控制着在向有auto_increment列的表插入数据时,相关锁的行为,有三个取值:


0:这个表示tradition(传统)

   它提供了一个向后兼容的能力,在这一模式下,所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁。注意呀,这里说的是语句级而不是事务级的,一个事务可能包涵有一个或多个语句。


   它能保证值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入。


1:这个表示consecutive(连续)

   这一模式下对simple insert做了优化,由于simple insert一次性插入值的个数可以立马得到确定,所以MySQL可以一次生成几个连续的值,用于这个insert语句;总的来说这个对复制也是安全的(它保证了基于语句复制的安全)。由于现在MySQL已经推荐把二进制的格式设置成ROW格式,所以没有复制安全问题了。


这一模式也是MySQL的默认模式,这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁。


2:这个表示interleaved(交错)

   由于这个模式下已经没有了auto_inc锁,所以这个模式下的性能是最好的;但是它也有一个问题,就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。


注意:在MySQL8.0版本中已经将innodb_autoinc_lock_mode该参数的默认值改为2!!!!!