数据库极客时间-mysql索引
Source
Edit
History

自增id详解

自增主键好处：可以让主键索引尽量的保持递增顺序插入，避免了页分裂，让索引更加紧凑。

一、自增主键的“空洞”

1. 自增主键存储位置

表的结构定义存放在后缀名为 .frm 的文件中，但是并不会保存自增值。不同的存储引擎对于自增值的保存策略不同。

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

  • InnoDB 引擎的自增值，不同的版本有不同的处理方法。

    • 在 MySQL 5.7 之前的版本（包含 5.7），自增值保持在内存中，并没有持久化。MySQL 在重启后，第一次打开表时，会先去找自增值的最大值，然后将这个最大值加 1 后作为这个表当前的自增值。

      注意：如果一个表当前数据行中最大的 id 是 10，AUTO_INCREMENT=11。这时候我们删除 id=10 这一行，AUTO_INCREMENT 还是 11，但如果现在重启 MySQL，重启后的 MySQL 中这张表的 AUTO_INCREMENT 就为 10。

    • 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

2. 自增值的修改策略

在 MySQL 中，如果字段 id 被定义为 AUTO_INCREMNT，在插入一行数据的时候，自增值的行为如下：

  • 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMNT 值填到自增字段
  • 如果插入数据时 id 字段指定了具体的值，就直接使用语句中指定的值。根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，要插入的值是 X，当前的自增值是 Y。
    • 如果 X < Y，那么这个表的自增值不变
    • 如果 X >= Y，就需要把当前自增值修改为新的自增值

新的自增值的修改算法是：从自增的初始值开始，按照自增步长为单位，持续增加。知道找到第一个大于 X 的值，即可作为新的自增值。默认情况下，自增的初始化和步长都为 1。

在一些场景中，可能不使用自增的默认值。比如双 Master 的主备架构中，要求双写，我们就可以让一个库的自增步长设置为 2。然后两个库的自增 id 分别是偶数和奇数。避免两个库生成的主键发生冲突。

因此有可能出现自增主键 id 不连续的情况。

3. 自增值出现空洞的场景

假如我们有一张表：

1
2
3
4
5
6
7
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;

假设表 t 中已经有 (1,1,1) 这条记录了，然后我再执行一条插入数据的命令：insert into t values(null, 1, 1);

那么这条语句的执行流程为：

  • InnoDB 发现用户没有指定自增 id 的值，则获取表 t 中当前的自增值为 2。
  • 将要插入的行改为 (2,1,1)，并且将表的自增 id 值改为 3。
  • 继续执行插入数据操作，但是由于存在 c=1 的记录，造成唯一键冲突。报错返回

所以就产生了问题，这行语句并没有执行成功，但是却更改了自增 id。并且在此之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是出现了自增主键不连续的情况。

造成的原因就是：执行语句时发生了唯一键冲突，导致自增主键不连续。同样的，事务回滚也会产生类似现象。

为什么 MySQL 没有将自增值回退？

假设有两个并行的事务，在申请自增 id 时，肯定要加锁，然后顺序申请。假设事务 A 申请到了 id=2，事务 B 申请到了 id=3，此时表中的自增值为 4。之后事务 B 正确提交了，但事务 A 出现唯一键冲突，如果允许事务 A 回退自增 id，也就是把表当前的自增值改为 2。就会出现这样的情况，表中已经有 id=3 的行，但当前的自增 id 值为 2。接下来其他事务继续执行就会申请 id=2，然后在申请到 id=3 时，就会出现主键冲突。

而如果要解决这个问题，有两种方法：

  • 每次申请自增 id 之前，先判断表中是否已经存在这个 id，如果存在，则跳过。但是这个方法的成本很高。因为这样做的话，申请自增 id 还要去主键索引树上判断 id 是否存在。
  • 把自增 id 的锁范围扩大，必须等待一个事务执行完成并提交后，下一个事务才能申请自增 id。这个方法的成本也很高，锁的粒度太大，系统并发能力大大下降。

因此，允许自增 id 回退，可能会造成 MySQL 性能的下降。所以 MySQL 在语句执行失败也不回退自增 id。

于是自增 id 只保证是递增的，而不保证是连续的。

二、MySQL 中不同的自增 id

自增 id 有上限，有可能被用完。自增 id 用完了会怎么样？

1. 表中定义的自增 id

1
2
3
4
CREATE TABLE `t` ( 
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
	PRIMARY KEY (`id`)) 
	ENGINE=InnoDB AUTO_INCREMENT=4294967295;

表中定义的自增 id 达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。

当这个 id 字段是主键时，并且 id 字段的值达到了类型的上限。再进行插入的时候，由于得到的自增 id 仍为上限值，就会报主键冲突错误，由用户处理。

2. InnoDB 系统自增 row_id

如果创建的 InooDB 表没有指定主键，那么 InnoDB 会创建一个用户不可见的，长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值，所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后把 dict_sys.row_id 的值加 1。

因为 row_id 是 6 字节，范围为 [0, 2^48-1]。当 dict_sys.row_id=2^48 时，如果再有插入数据的行为来申请 row_id，会返回 0。也就是说，写入表的 row_id 是从 0 开始到 2^48-1，达到上限后，下一个值就是 0，然后继续循环。

造成的后果就是，如果表中已经存在 row_id=N 的行，新写入的行就会覆盖原有的行。使用系统自增 row_id 有可能覆盖数据，而这意味着数据丢失，影响的是数据可靠性。因此，我们最好主动创建自增主键，当超过上限时，报主键冲突，影响的可用性。而一般情况下，可靠性要优于可用性的。

3. XID

XID 是 redo log 和 binlog 中一个共同的数据字段。崩溃恢复时，会按顺序扫描 redo log：

  • 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交
  • 如果碰到只有 prepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。（会判断这个事务是否存在且完整，如果是则提交事务，如果不是，则回滚事务）

MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将他赋值给 query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 query_id 赋值给这个事务的 XID。那么 XID 其实就是用来对应一个事务的。

global_query_id 是一个纯内存变量，MySQL 重启会清零。而 MySQL 重启后会生成新的 binlog 文件，这也保证了同一个 binlog 文件中，XID 一定是唯一的。

如果 global_query_id 达到上限后，就会继续从 0 开始计数。那么从理论上说，还是有可能出现同一个 binlog 中出现相同 XID 的情况。但是出现这种情况的条件很苛刻，因为 global_query_id 类型是 8 个字节，上限是 2^64-1。要出现这种情况，必须是如下的过程：

  • 执行一个事务，假设 XID 为 A
  • 接下来执行 2^64 次 SQL 语句，让 global_query_id 回到 A
  • 在启用一个事务，这个事务的 XID 才会为 A

一般我们认为，一个事务中不会有 2^64 个 SQL 语句。这种可能性只会存在于理论中。

4. thread_id

线程 id 是 MySQL 中最常见的一种自增 id。系统中保存了一个全局变量 thread_id_count，每新建一个连接，就将 thread_id_count 赋值给这个新连接的线程变量。

thread_id_count 大小为 4 字节，达到 2^32-1 后，会被重置为 0，然后继续循环。

但是我们不会在 show processlist 命令中看到两个相同的线程 id。因为在给新线程分配 thread_id 时，会检测这个 thread_id 是否被分配，如果已经分配的话，会继续自增循环，直到找到一个可用的 thread_id。

5. 小结

  • 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
  • row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
  • Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
  • thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。