【mysql45讲】自增主键

1.自增主键的作用

使主键索引尽量递增的顺序，避免了页分裂，因此索引更紧凑。
但不能保证是连续递增的。

2.自增值的保存位置

1.MyISAM 引擎的自增值保存在数据文件中
2.InnoDB的自增值保存在内存里，在Mysql8.0之后，将自增值的变更记录存在了redo log中，重启时依靠redo log恢复之前的值。

3.自增值的修改机制

当主键id被设为自增后，新插入记录的id按以下规则：
1.插入记录的id为0，null，或未指定值时，使用自增值。
2.插入记录指定了id，就使用此id。
自增值的变化规则：
假设插入的id为X，自增值为Y：
1.X<=Y时，不变。
2.X>Y时，Y要变大。

具体算法是从 auto_increment_offset （默认为1）开始，以 auto_increment_increment（默认为1）
为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

4.自增值不能保证连续递增的原因

4.1 唯一索引冲突

要插入的记录与已存在的记录存在唯一索引冲突，所以插入失败，自增值并不会再改回去。

4.2 事务回滚

事务回滚时，自增值并不回退。

4.3 批量申请自增id的策略

对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：
语句执行过程中，第一次申请自增 id，会分配 1 个；1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍

举例：

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert…select，实际上往表 t2 中插入了 4 行数据。但是，这四行数据是分三次申请的自增 id，第一次申请到了 id=1，第二次被分配了 id=2 和 id=3， 第三次被分配到 id=4 到 id=7。由于这条语句实际只用上了 4 个 id，所以 id=5 到 id=7 就被浪费掉了。之后，再执行 insert into t2 values(null, 5,5)，实际上插入的数据就是（8,5,5)。

5.自增锁的策略

MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1。
①这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；
②这个参数的值被设置为 1 时：普通 insert 语句，自增锁在申请之后就马上释放；
类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
③这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。

6.其他关于insert…select

insert … select 是很常见的在两个表之间拷贝数据的方法。
你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。
而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。