mysql中的自增ID

表自增ID

自增ID的保存

innodb中表结构存在.frm文件中（MyISAM 把自增ID值保存在数据文件中)，但不会保存自增值。mysql 8.0之前，自增值不会被持久化。

没有被持久化：自增值保存在内存中，重启后第一次打开表，回去计算当前max(id)，将max(id)+1作为下一个自增值。比如：当前表中； id=10, auto_increment=11，如果这时删除id=10的数据，auto_increment=11，如果此时插入数据，新数据的id=11(// 注意：表中id不是连续的)；如果此时重启数据库，再插入数据，新数据的id=10

持久化：8.0之后，将自增值的变更记录保存在redo log中，重启后依靠redo log恢复。

自增ID不意味着每次+1

auto_increment_offset 开始位置，auto_increment_increment 每次增长的步长，默认值都是1。

也有步长是2的情况，比如双主的主备结构，一个机器的自增ID用奇数，一个机器的自增ID用偶数，步长都是2。原因：避免两个库生成的主键发生冲突。

自增ID达到最大值

达到最大值后，再insert新数据，自增ID不变，报主键错误

自增ID的执行流程

insert into t values(null, 1, 1); 

自增锁用完就释放+insert出错也不会回退，提高了事务的并发性。会发现ID不连续，导致ID不连续的场景，比如：

• 删除一个元素，后面再插入不会再用这个ID
• insert失败，自增ID不会回滚，不然会影响其他事务。失败举例：唯一键冲突、事务kill回滚
• 批量申请ID（不知道申请几个）时，为了提高效率，第一次申请给1个，第二次申请给2个，第三次申请给4个，就算最后一次申请没用完也不能还回来

批量申请自增ID

insert…select用的是语句级的自增ID锁，不是申请完一次就释放。因为insert…select不像insert…values知道一次要申请多少个，所以要渐进式的多次申请。而且为了保证在并发场景下，主从数据一致性 => 主库执行插入操作和binlog回放结果一样，所以insert…select需要是一个整体。所以insert…select用的是语句级的自增ID锁。

sessionA	sessionB
insert into t2 values(null,1,1) insert into t2 values(null,2,2) insert into t2 values(null,3,3) insert into t2 values(null,4,4)
insert into t2 values(null,5,5)	insert into t2(c,d) select c,d from t;

如果sessionB的insert…select不是一个整体，插入的数据可能就是：(1,1,1),(2,2,2),(3,5,5),(4,3,3),(5,4,4)；sessionA的语句查到中间了。binlog是没有办法回放出来的。sessionA的insert t2和sessionB的insert t2有冲突(要保证主备一致)，需要insert…select加锁执行。

innodb_autoinc_lock_mode

0：语句结束后释放锁

1：普通insert，自增ID申请到就释放；类似insert…select、load data、replace…select等批量插入语句，自增锁要等语句结束后才释放

2：所有自增ID都申请完立即释放

为提高并发插入能力，可行的配置：innodb_autoinc_lock_mode=2 + binlog=row(保证数据一致)

并发事务 自增ID binlog如何记录

在binlog=statement，binlog只记录语句的情况下

sessionA	sessionB
insert into t2 values(null,1,1) // 实际插入时申请的id=1 insert(1,1,1)	insert into t2 values(null,2,2) // 实际插入时申请的id=2 insert(2,2,2)

但sessionB先执行完记录到binlog，那binlog的回放的时候id不就等于=1的给sessionB了吗？

不会，事务记录的时候insert_ID会跟着事务

// sessionB先完成，sessionA后完成，binlog的记录：
SET INSERT_ID=2;
语句 B；
SET INSERT_ID=1;
语句 A；

时间戳同步备库也是相似的问题

insert into t values(1,1,now())

binlog=statement时，binlog也会记录当前时间戳对应的实际值

row_id

表没定主键，系统会默认生成一个主键，实际占6个字节；达到上限后，下一个就是0，循环。

xid

redo log关联对应的binlog语句时使用xid。mysql有全局变量global_query_id，每次执行语句会给它发一个query_id，然后把这个变量+1。如果这个语句是事务的第一条语句，就会把这个query_id给xid。

每次sql重启都会清空global_query_id。global_query_id有8个字节，所以一般不会到达极限

事务ID：trx_id

xid是关联redo log和binlog用的，由server层维护。trx_id是innodb 事务单独维护的，trx_id是为了处理读写事务一致性的。

只读是不分配事务ID的，但在innodb_trx能看到一个很大的数，这个数是系统临时算出来的=当前事务trx变量的指针地址转整数+2的48次方；不分配事务ID好处：不需要，能减少trx_id的申请次数和相应的逻辑处理，比如：事务视图的活跃列表数组的大小。

trx_id到达最大值之后，就会重置为0。按照现在的TPS，在十几年后会出现。那就会出现脏读。

线程ID：thread_id

用完会重置到0，但mysql分配线程ID时加了逻辑限制要求唯一：重置0之后，mysql会遍历thread_id_counter，直至找到一个没有被正在用的thread_id，分配给新线程。