自增主键优化

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

自增值保存在哪儿？

• MyISAM引擎的自增值保存在数据文件中。

• InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：
• 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。
每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。
举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMET还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。
• 在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

自增值修改机制

1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段；

2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

1. 如果X<Y，那么这个表的自增值不变；

2. 如果X≥Y，就需要把当前自增值修改为新的自增值。

备注：在一些场景下，使用的就不全是默认值。比如，双M的主备结构里要求双写的时候，我们就可能会设置成auto_increment_increment=2，让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突。

1. 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；

2. 否则，自增值不变。

自增值的修改时机

唯一索引导致自增id不连续

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

1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);

2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；

3. 将传入的行的值改成(2,1,1);

4. 将表的自增值改成3；

5. 继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。

事务回滚导致的id不连续

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

自增值不能回滚原因

1. 假设事务A申请到了id=2， 事务B申请到id=3，那么这时候表t的自增值是4，之后继续执行。

2. 事务B正确提交了，但事务A出现了唯一键冲突。

3. 如果允许事务A把自增id回退，也就是把表t的当前自增值改回2，那么就会出现这样的情况：表里面已经有id=3的行，而当前的自增id值是2。

4. 接下来，继续执行的其他事务就会申请到id=2，然后再申请到id=3。这时，就会出现插入语句报错“主键冲突”。

1. 每次申请id之前，先判断表里面是否已经存在这个id。如果存在，就跳过这个id。但是，这个方法的成本很高。因为，本来申请id是一个很快的操作，现在还要再去主键索引树上判断id是否存在。

2. 把自增id的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

自增锁的优化

1. 这个参数的值被设置为0时，表示采用之前MySQL 5.0版本的策略，即语句执行结束后才释放锁；

2. 这个参数的值被设置为1时：
• 普通insert语句，自增锁在申请之后就马上释放；
• 类似insert … select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

3. 这个参数的值被设置为2时，所有的申请自增主键的动作都是申请后就释放锁。

批量更新导致id不连续

• session B先插入了两个记录，(1,1,1)、(2,2,2)；

• 然后，session A来申请自增id得到id=3，插入了（3,5,5)；

• 之后，session B继续执行，插入两条记录(4,3,3)、 (5,4,4)。

批量更新自增锁优化

1. 一种思路是，让原库的批量插入数据语句，固定生成连续的id值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的。

2. 另一种思路是，在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。这种情况，其实就是innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row。

1. 语句执行过程中，第一次申请自增id，会分配1个；

2. 1个用完以后，这个语句第二次申请自增id，会分配2个；

3. 2个用完以后，还是这个语句，第三次申请自增id，会分配4个；

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);

自增id用完怎么办？

表定义自增值id

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'

InnoDB系统自增row_id

1. row_id写入表中的值范围，是从0到2的48次-1；

2. 当dict_sys.row_id=2的48次时，如果再有插入数据的行为要来申请row_id，拿到以后再取最后6个字节的话就是0。

Xid

1. 执行一个事务，假设Xid是A；

2. 接下来执行2的64次查询语句，让global_query_id回到A；

3. 再启动一个事务，这个事务的Xid也是A。

Innodb trx_id

1. 在T1时刻，trx_id的值其实就是0。而这个很大的数，只是显示用的。一会儿我会再和你说说这个数据的生成逻辑。

2. 直到session A 在T3时刻执行insert语句的时候，InnoDB才真正分配了trx_id。所以，T4时刻，session B查到的这个trx_id的值就是1289。

1. update 和 delete语句除了事务本身，还涉及到标记删除旧数据，也就是要把数据放到purge队列里等待后续物理删除，这个操作也会把max_trx_id+1， 因此在一个事务中至少加2；

2. InnoDB的后台操作，比如表的索引信息统计这类操作，也是会启动内部事务的，因此你可能看到，trx_id值并不是按照加1递增的。

1. 因为同一个只读事务在执行期间，它的指针地址是不会变的，所以不论是在 innodb_trx还是在innodb_locks表里，同一个只读事务查出来的trx_id就会是一样的。

2. 如果有并行的多个只读事务，每个事务的trx变量的指针地址肯定不同。这样，不同的并发只读事务，查出来的trx_id就是不同的。

• 一个好处是，这样做可以减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务，是不影响数据的可见性判断的。所以，在创建事务的一致性视图时，InnoDB就只需要拷贝读写事务的trx_id。

• 另一个好处是，可以减少trx_id的申请次数。在InnoDB里，即使你只是执行一个普通的select语句，在执行过程中，也是要对应一个只读事务的。所以只读事务优化后，普通的查询语句不需要申请trx_id，就大大减少了并发事务申请trx_id的锁冲突。

thread_id

do {
  new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

1. 表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。

2. 00达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据。

3. Xid只需要不在同一个binlog文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。

4. InnoDB的max_trx_id 递增值每次MySQL重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的bug，好在留给我们的时间还很充裕。

5. thread_id是我们使用中最常见的，而且也是处理得最好的一个自增id逻辑了。