05-五、MySQL 面试题 InnoDB AUTO_INCREMENT ( 末 )

经过前面上中下三个章节的学习，我们终于了解了 MySQL Innodb 中的 AUTO_INCREMENT 约束和 AUTO_INCREMENT 锁的模式。也了解了各种模式的影响和缺点。

当然了，上面的章节，很多人应该是不关心的。大家最关心的还是本章节的内容：「 MySQL InnoDB AUTO_INCREMENT 计数器如何初始化 」

MySQL InnoDB AUTO_INCREMENT 计数器如何初始化

如果为 InnoDB 表指定了 AUTO_INCREMENT 列，那么内存中，该表对象会包含一个称为 「 自增值计数器 」 的特殊计数器，用于为 AUTO_INCREMENT 列分配新值时使用。

在 MySQL 5.7 及更早版本中，自增值计数器仅存储在内存中，而不是磁盘上。

为了在 MySQL 启动或重新启动后初始化 自增值计数器，在往含有 AUTO_INCREMENT 列的 InnoDB 表中第一次插入数据之前，会执行以下语句获取当前的 自增值

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

这个操作是隐式执行的，就是在插入时，如果 Innodb 发现内存中该表对象不包含自增计数器的时候，会执行上面的 SQL 语句来获取这个自增值

在 MySQL 8.0 中，此行为已经变更了。每次更改时，会将当前自增值计数器的最大值将写入重做 ( redo ) 日志，并保存到每个检查点上的引擎专用系统表中。这种变更使得自增值计数器的当前最大值会在重启时保持不变。

在原来的模式中，重启前，可能内存中的自增值计数器已经到了 1000+ ，但表中的实际最大值可能只有 100，那么重启后，自增值就会停留在 100，而 8.0 的变更中，最大值仍会保持重启前的 1000+

当正常关闭 MySQL 服务器后再重新启动它时，InnoDB 会使用存储在数据字典系统表中的当前最大自增值来初始化内存中的自增值计数器。

如果服务器发生了非正常崩溃，在崩溃恢复期间重新启动服务器时，InnoDB 仍会使用存储在数据字典系统表中的当前最大自增值初始化内存中自增值计数器，并且会扫描重做日志 ( redo log ) 以查找自上一个检查点以来写入的自增计数器的值。如果重做日志值大于内存中计数器值，则会应用重做日志值。

因此，在服务器崩溃的情况下，无法保证重用先前分配的自增值

当每一次执行 INSERT 或 UPDATE 操作会更改当前最大自增值时，会将新值将写入重做日志。但如果在将重做日志刷新到磁盘之前发生崩溃，那么在重新启动服务器后初始化自增计数器时可能会重用以前分配的值。

当然了，MySQL 8.0 或更高的版本中也可能会使用等效下面的 SQL 语句来初始化自增计数器

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

但唯一可能发生的情况是: **导入没有 .cfg 元数据文件的表空间。 否则，会从 .cfg 元数据文件中读取当前最大自增计数器值

在 MySQL 5.7 及更早版本中，服务器重新启动时会忽略执行表选项中的 AUTO_INCREMENT = N，该选项一般用来在 CREATE TABLE 或 ALTER TABLE 语句中用于设置初始计数器值或更改现有计数器值。

但在 MySQL 8.0 及更高的版本中，服务器重新启动时并不会忽略表选项 AUTO_INCREMENT = N。 如果将自增计数器初始化为特定值，或者将自增计数器值更改为更大的值，则新值会在服务器重新启动时保持不变。

注意

ALTER TABLE ... AUTO_INCREMENT = N 语句仅仅只能用于将自增计数器的值修改为大于当前计数器的最大值，如果小于则是没有任何效果的。

在 MySQL 5.7 及更早版本中，服务器在 ROLLBACK 操作之后立即重新启动可能会导致重用先前分配给回滚事务的自增值，从而可以有效地回滚当前最大自增值。

但在 MySQL 8.0 中，当前的最大自增量值是持久的，从而阻止了重用以前分配的值。

如果使用 SHOW TABLE STATUS 语句在初始化自增计数器之前检查表，InnoDB 将打开表并使用存储在数据字典系统表中的当前最大自增值初始化计数器值。并将该值存储在内存中以供以后插入或更新使用。

启动或重新启动服务器时，初始化自增计数器会使用表上的常规独占锁 ( for update ) 读取，并且会持有该独占锁直到事务结束。在初始化新创建的表的自增计数器时，InnoDB 遵循相同的过程。当然了，新创建的表可以使用 AUTO_INCREMENT = N 选项指定一个大于 0 的自增值

初始化自增计数器完成后，如果在插入行时未显式指定自增值，InnoDB 会隐式递增计数器并将新值分配给 AUTO_INCREMENT 列

只要服务器一直在运行，InnoDB 就会使用内存中的自增计数器。当服务器停止并重新启动时，InnoDB 会重新初始化自增计数器，如前所述。

my.cnf 配置文件中的 AUTO_INCREMENT

• 在 my.cnf 配置文件中，可以使用 auto_increment_offset 配置选项确定 AUTO_INCREMENT 列值的起始点。默认设置为 1
• 在 my_cnf 配置文件中，可以使用 auto_increment_increment 配置选项控制连续列值之间的间隔。默认设置为 1

一般情况下，在双主互相备份时，我们一般会指定一台服务器的两个配置项为

auto_increment_offset=1;
auto_increment_increment=2;

这样，这台服务器的自增值将会遵循 1 3 5 7 9 11 13 ，奇数数列

而在另一台服务器上指定

auto_increment_offset=2;
auto_increment_increment=2;

这样，这台服务器的自增值将会是 2 4 6 8 10 …. 偶数数列

这样，在互为主从的时候，就不会出现自增值重复的问题

希望读者能够给小编留言，也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")