正文

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;

自增值保存在哪？

先插入一条数据。

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

然后通过 show create table t; 可以看到下一条会生成的值，但是这个输出结果会引起误解。

• MyISAM 引擎的自增值保存在数据文件中。
• InnoDB 的自增值一开始的版本都是保存在内存中，直到 8.0 版本后才引入 “自增值持久化” 的能力。
  • 在之间的版本中，都是找 max (id)，然后根据步长来算出下一个值。比如说，现在 max (id)=10，然后将其行删除，数据库崩溃重启，再插入一条发现 id 为 10。
  • 8.0 之后的版本，自增值记录在了 redolog 中，不论删除不删除，下一条也一定是曾经的 max (id)+ 步长。

自增值修改机制

1. 如果插入的 id 字段指定为 0、null 或者不写，表就会用 AUTO_INCREMENT 值当做 id 的值。
2. 如果插入的时候指定了 id 的值，就直接使用指定的值。
   • 如果指定值 < 自增值，那么这个表的自增值不变。
   • 如果指定值≥自增值，那么这个表的自增值为指定值 + 步长。

新的自增值生成算法是：从 auto_increment_offset（初始值）开始，以 auto_increment_increment（步长），持续叠加，直到找到第一个大于输入的值，作为新的自增值。

自增值的修改时机

由于自增值的确认在真正的 insert 语句之前，如果说这个 insert 语句报错比如 Duplicate key 等等，那么这个自增值还是增长了。所以说可能会出现不连续的情况。事务回滚也会导致这种情况。

这样做的原因是防止两个事务申请到同一个自增值。

1. 有两个事务 A 和 B，A 申请到 id=2，B 申请到 id=3。
2. 然后 A 出现了 Duplicate key，但是 B 成功插入。
3. 如果允许 A 的自增 id 退回，那么也就意味着把现在表的自增 ID 变成 2。
4. 一个新的新增请求进来后申请到 id=2，成功，再下来 id=3 出现主键冲突。

如果为了解决这个问题采用下面两种方式，会发现不管哪一种系统的开销都太大了。所以 MySQL 就采用了不回退的方式。

• 每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。
• 把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。

自增锁的优化

在 MySQL 5.0 版本的时候，自增锁的范围是语句级别。也就是说如果一个语句申请了自增锁，那么到这个语句执行完成后才能够释放这个锁。会较大程度的影响并发。

MySQL 5.1.22 版本引入了一个新策略，innodb_autoinc_lock_mode，默认值是 1。
innodb_autoinc_lock_mode 是 MySQL 中的一个参数，它用于控制 InnoDB 引擎中自增 ( AUTO_INCREMENT ) 列的锁定行为。这个参数有三个可能的值：0、1 和 2。

• 0 ：传统模式（ traditional ）。在这种模式下，所有的插入语句都会在语句开始时获取一个表级的自增锁，只有在语句结束或回滚后才会释放这个锁。这种模式可以保证自增值的连续性和可预见性，但在高并发情况下可能会导致自增锁的竞争。
• 1 ：连续模式（ consecutive ）。这是 MySQL 的默认模式。在这种模式下，如果是单一的插入语句，可以立即获取并释放该锁。但是，如果是批量插入语句（如 INSERT INTO ... SELECT ... 或 LOAD DATA ），则仍然需要等待当前语句执行完毕才能释放锁。这种模式的优点是它可以提前释放锁，从而提高并发性能。但是，由于锁的提前释放，可能会导致自增值不连续。
• 2 ：交错模式（ interleaved ）。在这种模式下，所有的插入语句都可以立即获取并释放锁。这种模式的性能最高，但是可能会导致自增值不连续。此外，如果二进制日志格式 ( binlog_format ) 为 STATEMENT ，则可能会导致复制错误。

为什么说 innodb_autoinc_lock_mode=2 会导致复制错误呢？比如

Session A	Session B
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 VALUES(null,5,5);	INSERT INTO t2(c,d) SELECT c,d FROM t;

• 如果说 Session B 还没有完全插入完成，只插入了 (1,1,1)、(2,2,2)。
• Session A 申请到了自增锁 id=3 并且立刻释放了，插入了 (3,5,5)。
• 然后 Session B 继续执行插入 (4,3,3)、 (5,4,4)。

如果说 binlog_format = statement ，那么 binlog 中记录的就是 sql 语句，比如 (null,5,5)，恢复或同步 t2 的时候，如果 Session B 先执行了，那么就变成 (1,1,1)、(2,2,2)、(3,3,3)、(4,4,4)。这样就会导致数据不一致。

所以解决这个问题，有两个思路

• 一种思路是，让原库的批量插入数据语句，固定生成连续的 id 值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的。
• 另一种思路是，在 binlog 里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。这种情况，其实就是 innodb_autoinc_lock_mode 设置为 2，同时 binlog_format 设置为 row。

注意

在普通的 insert 语句里面包含多个 value 值的情况下，即使 innodb_autoinc_lock_mode 设置为 1，也不会等语句执行完成才释放锁。因为这类语句在申请自增 id 的时候，是可以精确计算出需要多少个 id 的，然后一次性申请，申请完成后锁就可以释放了。

可能你会觉得，如果 select … insert 语句要插入 10 万行数据，按照这个逻辑插入一条申请一条，那么就需要申请 10 万次，这样太影响性能了。这里了 MySQL 又做了一个优化。

1. 语句执行过程中，第一次申请自增 id，会分配 1 个。
2. 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个。
3. 2 个用完以后，这个语句第三次申请，会分配 4 个。
4. 以此类推，每次申请会分配的个数都是之前的 2 倍。

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

1. 第一次申请得到 id=1。
2. 第二次申请得到 id=2-3。
3. 第三次申请得到 id=4-7。

然而只用到了 1、2、3、4，那么 5-7 就浪费掉了，新增的这个为 (8,5,5)。

总结

id 不连续的三种原因

1. 主键冲突导致的不连续。
2. 事务回滚导致的不连续。
3. 乘 2 分配导致的不连续。

上一章答案

假设你刚刚接手的一个数据库上，真的发现了一个内存表。备库重启之后肯定是会导致备库的内存表数据被清空，进而导致主备同步停止。这时，最好的做法是将它修改成 InnoDB 引擎表。 假设当时的业务场景暂时不允许你修改引擎，你可以加上什么自动化逻辑，来避免主备同步停止呢？

如果说不能修改主库引擎，那么就把备库和内存表的引擎改成 InnoDB。

set sql_log_bin=off;
alter table tbl_name engine=innodb;

由于主库重启后会把 delete from t 这种命令传到备库，备库的同名的表数据也会被清空。因此，就不会出现主备同步停止的问题。