mysql主键自增策略_MySQL 自增主键机制

自增主键：特指在自增列上定义的主键。 自增主键的优点是让主键索引保持递增顺序的插入，避免页分裂，索引更加紧凑。

1. 自增值保存在哪？

不同的存储引擎保存自增值的策略不一样；

a. 对于MyISAM引擎，自增值保存在数据文件中；

b. Innodb引擎，mysql5.7之前，自增值保存在内存中，而且不会持久化自增值。每次重启后第一次打开表，都会去查找自增值的最大值max(id), 并设置表当前自增值为max(id) + 1; mysql8.0, 自增值变更记录在了redo log中，重启时依靠redo log恢复重启之前的值。

2. 自增值修改策略？

a. 在插入时如不指定或指定为0，null，则使用当前的自增值填到自增字段；并设置当前自增值=当前自增值+auto_increment_increment 步长；

b. 指定了具体的值，若指定值小于当前自增值，则自增值不发生改变；若大于当前自增值，则自增值为指定值+auto_increment_increment;

3. 自增值的修改时机？

a. 自增值修改发生在插入数据的操作之前，如果插入失败，自增值不会再修改回去；

b. 事务回滚也不会将自增值修改回去；

c. 为了减少自增id锁带来的性能影响，mysql不会修改回去之前的自增值；

4. 自增锁的优化

a. 自增锁不是事务锁，在每次申请完就释放；在5.0版本会在语句执行完才释放，后续版本无须在语句执行完才释放；支持批量申请；

b. 一次性insert多条记录时，如果能计算出需要多少个id，就一次性申请，申请完就释放；

c. insert … select 默认会使用语句级的锁，只有语句执行完才会释放自增锁，为了数据的一致性；

d. 而对于批量插入数据的语句(select … insert，replace … select 和 load data 语句)，MySQL 有一个批量申请自增 id 的策略(注：该策略是导致自增 id 不连续的第三种原因。)：语句执行过程中，第一次申请自增 id，会分配 1 个；1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

e. 对于批量插入数据，使用insert … select, replace … select 和load data语句时，可设置innodb_autoinc_lock_mode=2, binlog_format=row提高并发性，保证一致性；

5. 自增值到达上限

自增值不再变化，再次申请时，得到的值保持不变。