自增值保存在哪儿
实际上表的结构定义保存在 .frm 文件里、但是不会保存自增值、自增值的保存跟具体引擎有关:
MySIAM 引擎保存在数据文件中.
Innodb的自增保存在内存, 8.0版本之后、提供持久化.
8.0 之前的版本只保存在内存中、每次重启、第一次打开表的时候、会找自增值的最大值 max(id), 将 max(id) + 1, 作为当前表的自增值.
8.0将自增值的变更记录在了redo log中、重启时依靠redo log恢复重启之前的值.
自增值修改机制
MySQL、若字段id被定义为 auto_increment, 在插入一行数据的时候、自增值的行为如下:
- 若插入数据时id字段指定为0、null或者未指定值、那么就把这个表当前 auto_increment值填到自增字段.
- 若插入数据时、id字段指定了具体值 、直接使用语句指定值.
若待插入值是 X、当前自增是 Y
1) X<Y, 自增保持不变
2) 若X>=Y, 将自增修改为新的值
从auto_increment_offset 开始、以 auto_increment_increment为步长、持续叠加、直到找到第一个大于X的值、作为新的自增值
自增值的修改时机
若表t已有记录(1,1,1)、再执行
1 | insert into t values(null, 1, 1); |
语句执行流程是:
- 调用innodb引擎接口、写入一行、传入值(0, 1, 1);
- innodb发现用户没有指定自增id值、获取表t当前的自增值为2;
- 将传入行的值改为 (2,1,1);
- 将表的自增改为3
- 继续执行插入、由于已经存在c=1的记录、出现唯一键冲突、报错返回.
所以、主键自增修改是在真正的插入操作之前、真正执行的时候唯一键冲突、id=2插入失败、但自增值不会回退. 这之后的数据插入自增基础就是3、所以, 唯一键冲突是导致自增id不连续的第一种原因.
事务回滚也会产生类似的现象、这是第二种原因.
那么为什么MySQL不设计自增值可以回滚呢 ?
假设有两个并行事务、在申请自增值的时候、为了避免两个事务申请到相同的自增id、就需要加锁、顺序申请.
- 假设事务A申请到id=2、事务B申请到id=3, 此时表t的自增值是4, 之后继续执行
- 事务B正确提交、事务A出现唯一键冲突.
- 若允许事务A将自增值回退、则出现、表里已有id=3的行、而当前自增是2 的现象
- 接下来其它语句申请到 id=2、然后再申请到id=3、就会出现主键冲突.
解决这个冲突有两个办法: - 每次申请id之前、先判断表里是否已存在这个id、若存在、就跳过、但成本较高、本来申请id是一个很快的操作、现在还要去主键索引上判断id是否存在
- 把自增id的锁范围扩大、必须等上一个事务执行完成、下一个才能申请. 但会大大的影响并发性能.
所以、MySQL放弃了自增值回退、只保证自增、不保证连续.
自增锁优化
自增锁并不是事务锁、每次申请完就释放、以便别的事务再申请. 一起看下自增锁演进的过程:
5.0版本、自增锁是语句级别、若一个语句申请了一个表自增锁、锁会等语句结束才释放. 影响并发度.
5.1.22新增参数 innodb_autoinc_lock_mode, 默认1
设为0: 表示采用5.0版本策略、语句执行结束释放锁
设为1: 普通insert、自增锁申请之后、立即释放; 类似insert…select批量插入语句、要等语句结束才释放
设为2: 所有语句都是申请后就释放.
批量插入语句、预先不知道要申请多少个自增id、一种直接想法是: 需要是就申请一个. 但若需要10w个自增的话、就需要申请10w次、会速度很慢、且影响并发插入性能.所以、批量申请的话、Mysql会使用指数级分配.
这是自增出现不连续的第三个原因.