注意: 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!!!!!
来源: http://blog.51cto.com/fengfeng688/2141772