insertintoselect主键自增_为什么MySQL的自增主键不单调也不连续

为什么这么设计&＃xff08;Why’s THE Design&＃xff09;是一系列关于计算机领域中程序设计决策的文章&＃xff0c;我们在这个系列的每一篇文章中都会提出一个具体的问题并从不同的角度讨论这种设计的优缺点、对具体实现造成的影响。如果你有想要了解的问题&＃xff0c;可以在文章下面留言。

当我们在使用关系型数据库时&＃xff0c;主键&＃xff08;Primary Key&＃xff09;是无法避开的概念&＃xff0c;主键的作用就是充当记录的标识符&＃xff0c;我们能够通过标识符在一张表中定位到唯一的记录&＃xff0c;作者在 为什么总是需要无意义的 ID 曾经介绍过为什么不应该使用有意义的字段来充当唯一标识符&＃xff0c;感兴趣的读者可以了解一下。

在关系型数据库中&＃xff0c;我们会选择记录中多个字段的最小子集作为该记录在表中的唯一标识符1&＃xff0c;根据关系型数据库对主键的定义&＃xff0c;我们既可以选择单个列作为主键&＃xff0c;也可以选择多个列作为主键&＃xff0c;但是主键在整个记录中必须存在并且唯一。最常见的方式当然是使用 MySQL 默认的自增 ID 作为主键&＃xff0c;虽然使用其他策略设置的主键也是合法的&＃xff0c;但是不是通用的以及推荐的做法。

图 1 - MySQL 的主键

MySQL 中默认的 AUTO_INCREMENT 属性在多数情况下可以保证主键的连续性&＃xff0c;我们通过 show create table 命令可以在表的定义中能够看到 AUTO_INCREMENT 属性的当前值&＃xff0c;当我们向当前表中插入数据时&＃xff0c;它会使用该属性的值作为插入记录的主键&＃xff0c;而每次获取该值也都会将它加一。

在很多开发者的认知中&＃xff0c;MySQL 的主键都应该是单调递增的&＃xff0c;但是在我们与 MySQL 打交道的过程中会遇到两个问题&＃xff0c;首先是记录的主键并不连续&＃xff0c;其次是可能会创建多个主键相同的记录&＃xff0c;我们将从以下的两个角度回答 MySQL 不单调和不连续的原因&＃xff1a;

• 较早版本的 MySQL 将 AUTO_INCREMENT 存储在内存中&＃xff0c;实例重启后会根据表中的数据重新设置该值&＃xff1b;
• 获取 AUTO_INCREMENT 时不会使用事务锁&＃xff0c;并发的插入事务可能出现部分字段冲突导致插入失败&＃xff1b;

需要注意的是&＃xff0c;我们在这篇文章中讨论的是 MySQL 中最常见的 InnoDB 存储引擎&＃xff0c;MyISAM 等其他引擎提供的 AUTO_INCREMENT 实现原理不在本文的讨论范围中。

删除记录

AUTO_INCREMENT 属性虽然在 MySQL 中十分常见&＃xff0c;但是在较早的 MySQL 版本中&＃xff0c;它的实现还比较简陋&＃xff0c;InnoDB 引擎会在内存中存储一个整数表示下一个被分配到的 ID&＃xff0c;当客户端向表中插入数据时会获取 AUTO_INCREMENT 值并将其加一。

图 2 - AUTO_INCREMENT 的使用

因为该值存储在内存中&＃xff0c;所以在每次 MySQL 实例重新启动后&＃xff0c;当客户端第一次向 table_name 表中插入记录时&＃xff0c;MySQL 会使用如下所示的 SQL 语句查找当前表中 id 的最大值&＃xff0c;将其加一后作为待插入记录的主键&＃xff0c;并作为当前表中 AUTO_INCREMENT 计数器的初始值2。

如果让作者实现 AUTO_INCREMENT&＃xff0c;在最开始也会使用这种方法。不过这种实现虽然非常简单&＃xff0c;但是如果使用者不严格遵循关系型数据库的设计规范&＃xff0c;就会出现如下所示的数据不一致的问题&＃xff1a;

图 3 - 5.7 版本之前的 AUTO_INCMRENT

因为重启了 MySQL 的实例&＃xff0c;所以内存中的 AUTO_INCREMENT 计数器会被重置成表中的最大值&＃xff0c;当我们再向表中插入新的 trades 记录时会重新使用 10 作为主键&＃xff0c;主键也就不是单调的了。在新的 trades 记录插入之后&＃xff0c;executions 表中的记录就错误的引用了新的 trades&＃xff0c;这其实是一个比较严重的错误。

然而这也不完全是 MySQL 的问题&＃xff0c;如果我们严格遵循关系型数据库的设计规范&＃xff0c;使用外键处理不同表之间的联系&＃xff0c;就可以避免上述问题&＃xff0c;因为当前 trades 记录仍然有外部的引用&＃xff0c;所以外键会禁止 trades 记录的删除&＃xff0c;不过多数公司内部的 DBA 都不推荐或者禁止使用外键&＃xff0c;所以确实存在出现这种问题的可能。

然而在 MySQL 8.0 中&＃xff0c;AUTO_INCREMENT 计数器的初始化行为发生了改变&＃xff0c;每次计数器的变化都会写入到系统的重做日志&＃xff08;Redo log&＃xff09;并在每个检查点存储在引擎私有的系统表中3。

In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

当 MySQL 服务被重启或者处于崩溃恢复时&＃xff0c;它可以从持久化的检查点和重做日志中恢复出最新的 AUTO_INCREMENT 计数器&＃xff0c;避免出现不单调的主键也解决了这里提到的问题。

并发事务

为了提高事务的吞吐量&＃xff0c;MySQL 可以处理并发执行的多个事务&＃xff0c;但是如果并发执行多个插入新记录的 SQL 语句&＃xff0c;可能会导致主键的不连续。如下图所示&＃xff0c;事务 1 向数据库中插入 id &＃61; 10 的记录&＃xff0c;事务 2 向数据库中插入 id &＃61; 11 和 id &＃61; 12 的两条记录&＃xff1a;

图 4 - 并发事务的执行

不过如果在最后事务 1 由于插入的记录发生了唯一键冲突导致了回滚&＃xff0c;而事务 2 没有发生错误而正常提交&＃xff0c;在这时我们会发现当前表中的主键出现了不连续的现象&＃xff0c;后续新插入的数据也不再会使用 10 作为记录的主键。

图 5 - 不连续的主键

这个现象背后的原因也很简单&＃xff0c;虽然在获取 AUTO_INCREMENT 时会加锁&＃xff0c;但是该锁是语句锁&＃xff0c;它的目的是保证 AUTO_INCREMENT 的获取不会导致线程竞争&＃xff0c;而不是保证 MySQL 中主键的连续4。

上述行为是由 InnoDB 存储引擎提供的 innodb_autoinc_lock_mode 配置控制的&＃xff0c;该配置决定了获取 AUTO_INCREMENT 计时器时需要先得到的锁&＃xff0c;该配置存在三种不同的模式&＃xff0c;分别是传统模式&＃xff08;Traditional&＃xff09;、连续模式&＃xff08;Consecutive&＃xff09;和交叉模式&＃xff08;Interleaved&＃xff09;5&＃xff0c;其中 MySQL 使用连续模式作为默认的锁模式&＃xff1a;

• 传统模式 innodb_autoinc_lock_mode &＃61; 0&＃xff1b;在包含 AUTO_INCREMENT 属性的表中插入数据时&＃xff0c;所有的 INSERT 语句都会获取表级别的 AUTO_INCREMENT 锁&＃xff0c;该锁会在当前语句执行后释放&＃xff1b;
• 连续模式 innodb_autoinc_lock_mode &＃61; 1&＃xff1b;INSERT ... SELECT、REPLACE ... SELECT 以及 LOAD DATA 等批量的插入操作需要获取表级别的 AUTO_INCREMENT 锁&＃xff0c;该锁会在当前语句执行后释放&＃xff1b;简单的插入语句&＃xff08;预先知道插入多少条记录的语句&＃xff09;只需要获取获取 AUTO_INCREMENT 计数器的互斥锁并在获取主键后直接释放&＃xff0c;不需要等待当前语句执行完成&＃xff1b;
• 交叉模式 innodb_autoinc_lock_mode &＃61; 2&＃xff1b;所有的插入语句都不需要获取表级别的 AUTO_INCREMENT 锁&＃xff0c;但是当多个语句插入的数据行数不确定时&＃xff0c;可能存在分配相同主键的风险&＃xff1b;

这三种模式都不能解决 MySQL 自增主键不连续的问题&＃xff0c;想要解决这个问题的终极方案是串行执行所有包含插入操作的事务&＃xff0c;也就是使用数据库的最高隔离级别 —— 可串行化&＃xff08;Serialiable&＃xff09;。当然直接修改数据库的隔离级别相对来说有些简单粗暴&＃xff0c;基于 MySQL 或者其他存储系统实现完全串行的插入也可以保证主键在插入时的连续&＃xff0c;但是仍然不能避免删除数据导致的不连续。

总结

早期 MySQL 的主键既不是单调的&＃xff0c;也不是连续的&＃xff0c;这些都是在当时工程上做出的一些选择&＃xff0c;如果严格地按照关系型数据库的设计规范&＃xff0c;MySQL 最初的设计造成问题的概率也比较低&＃xff0c;只有当被删除的主键被外部系统引用时才会影响数据的一致性&＃xff0c;但是今天使用方式的不同却增加出错的可能性&＃xff0c;而 MySQL 也在 8.0 中持久化了 AUTO_INCREMENT 以避免该问题的出现。

MySQL 中不连续的主键又是一个工程设计向性能低头的例子&＃xff0c;牺牲主键的连续性来支持数据的并发插入&＃xff0c;最终提高了 MySQL 服务的吞吐量&＃xff0c;作者在几年前刚刚使用 MySQL 时就遇到过这个问题&＃xff0c;但是当时并没有深究背后的原因&＃xff0c;今天重新理解该问题背后的设计决策也是个非常有趣的过程。我们在这里简单总结一下本文的内容&＃xff0c;重新回到今天的问题 — 为什么 MySQL 的自增主键不单调也不连续&＃xff1a;

• MySQL 5.7 版本之前在内存中存储 AUTO_INCREMENT 计数器&＃xff0c;实例重启后会根据表中的数据重新设置&＃xff0c;在删除记录后重启就可能出现重复的主键&＃xff0c;该问题在 8.0 版本使用重做日志解决&＃xff0c;保证了主键的单调性&＃xff1b;
• MySQL 插入数据获取 AUTO_INCREMENT 时不会使用事务锁&＃xff0c;而是会使用互斥锁&＃xff0c;并发的插入事务可能出现部分字段冲突导致插入失败&＃xff0c;想要保证主键的连续需要串行地执行插入语句&＃xff1b;

到最后&＃xff0c;我们还是来看一些比较开放的相关问题&＃xff0c;有兴趣的读者可以仔细思考一下下面的问题&＃xff1a;

• MyISAM 和其他的存储引擎如何存储 AUTO_INCREMENT 计数器&＃xff1f;
• MySQL 中的 auto_increment_increment 和 auto_increment_offset 是用来做什么的&＃xff1f;

原文链接&＃xff1a;https://www.toutiao.com/a6817982962324210183/