不连续的自增主键
-
由于自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑
-
自增值保存在哪儿
- 表的结构定义存放在后缀名为.frm 的文件中,但是并不会保存自增值。
- 不同的引擎对于自增值的保存策略不同
- MyISAM 引擎的自增值保存在数据文件中
- InnoDB 引擎的自增值,其实是保存在了内存里(MySQL 8.0 版本后才有自增值持久化的能力
- 在 MySQL 5.7 及之前的版本
- 自增值保存在内存里,并没有持久化,每次重启后主动将当前 max(id) + 1作为自增值
- MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值(作关联外键时需当心
- 在 MySQL 8.0 版本
- 自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值
- 在 MySQL 5.7 及之前的版本
-
自增值修改机制
-
如果字段 id 被定义为 AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下
- 如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
- 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值(插入值 X,自增值是Y
- 如果 X<Y,那么这个表的自增值不变;
- 如果 X≥Y,就需要把当前自增值修改为新的自增值;
-
自增值生成算法
- 从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值
- auto_increment_offset:自增的初始值(默认值是1
- auto_increment_increment:步长(默认值是1
- 在一些场景下,使用的就不全是默认值,双 M 主备结构要求双写时,步长设置为2
- 让一个库的自增 id 都是奇数,另一个库都是偶数,避免两个库生成主键发生冲突
- 从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值
-
自增值的修改时机
-
模拟唯一键冲突时流程来演示自增值的修改时机
- 这个表的自增值改成 3,是在真正执行插入数据的操作之前
- 碰到唯一键冲突时,并未自增值再改回去
- 唯一键冲突是导致自增主键 id 不连续的第一种原因
-
事务回滚也会产生类似的现象,这就是第二种原因
-
自增值不回退主要原因是为了提高性能(若要实现回退,将极大降低系统并发能力
-
-
自增锁的优化
- 自增 id 锁并不是一个事务锁,而是每次申请完就马上释放,以便允许别的事务再申请
- 在 MySQL 5.0 版本的时候,自增锁的范围是语句级别(影响并发度
- 果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放
- MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode,默认值是 1
- 这个参数的值被设置为 0 时,表示采用之前 MySQL 5.0 版本的策略
- 这个参数的值被设置为 1 时
- 普通 insert 语句,自增锁在申请之后就马上释放
- 类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才释放
- 这个参数的值被设置为 2 时,所有的申请自增主键的动作都是申请后就释放锁
- 在生产上,尤其是有 insert … select 这种批量插入数据的场景时,从并发插入数据性能考虑
- 建议设置为:innodb_autoinc_lock_mode=2 ,并且 binlog_format=row
- 既能提升并发性,又不会出现数据一致性问题
- 批量插入数据包含的语句类型是 insert … select、replace … select 和 load data 语句
- 对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略
- 语句执行过程中,第一次申请自增 id,会分配 1 个;
- 1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;
- 2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个;
- 依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。
- 批量申请自增 id 的策略也正是出现自增 id 不连续的第三种原因
-
insert 锁情况
-
insert … select 语句
- 栗子(可