CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
自增主键好处?
自增主键可以让主键索引尽量的保持递增顺序插入,避免页分裂,索引更加紧凑。
自增主键保存在何处?
insert into t values(null, 1, 1);
show create table t\G
可以看到表定义中出现了AUTO_INCREMENT=2,表示下一次插入数据时如果需要自动生成自增值,那么id便是2。
不同的引擎对于自增值的保存策略不同:
自增值的修改机制
自增值的修改行为如下:
自增值的变更情况如下,假设某次要插入的值是X,当前的自增值是Y:
新的自增值生成算法
从auto_increment_offset(默认值是1)开始,以auto_increment_increment(默认值是1)为步长,持续叠加,直到找到第一个大于X的值,作为新的自增值
唯一键冲突导致自增主键不连续
insert into t values(null, 1, 1);
上述执行过程可以看出,自增值的修改是在真正插入数据的操作之前,如果数据真正插入的时候异常,也不会将自增值改回去。
事务回滚导致自增主键不连续
set autocommit=0;
begin;
insert into t values(null, 2, 2);
rollback;
show create table t\G
可以看到自增主键已经从3变成4,但是并没有因为事务的回滚回退。
事务回滚为什么自增值不能回退
两个并行的事务在申请自增值的时候,为了避免两个事务申请到相同的自增id,需要加锁按照顺序申请,如果自增值可以回退需要做一些特殊处理:
为了避免上述的性能消耗,InnoDB即使语句执行失败也不回退自增id。
批量插入导致自增值不连续
自增值锁不是一个事务锁,每次申请完就释放,方便其他事务获取自增值。
参数innodb_autoinc_lock_mode的不同会影响锁的释放时机:
show global variables like 'innodb_autoinc_lock_mode';
如果innodb_autoinc_lock_mode值设置为1,对于insert...select这种无法提前知道申请多少个id,那就在需要的时候申请1个,如果有10w行数据那就需要申请10万次,对于批量插入数据的语句,MySQL有一个批量申请自增id的策略:
在innodb_autoinc_lock_mode参数为1的情况下,如果大批量插入数据也会造成自增id的不连续。