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


为什么这么规划(Why’s THE Design)是一系列关于计算机范畴中程序规划决议计划的文章,咱们在这个系列的每一篇文章中都会提出一个详细的问题并从不同的视点评论这种规划的优缺点、对详细完成造成的影响。假如你有想要了解的问题,能够在文章下面留言。

当咱们在运用联系型数据库时,主键(Primary Key)是! W _ * d S无法避开的概念,主键的效果便是充当记载的标识符? 9 – w,咱们能够通过标识符在一张表中定位到仅有的记g 5 9 ~ x载,作者在 为什么总是需求无意义的 ID 曾经介绍过为什么不应该运用有意义的字段来充当仅有标识符,感爱好的读者能够了解一下。

在联系型数据库中,咱们会挑选记载中多个字段的最小子/ U & (Q P D 1 9 b作为该记载在表中的仅有标识符1,依据联系型数据库对主键的界说,咱们既能够挑选x _ 3 { E J O k单个列作为主V 6 Y –键,也能够挑选多个列作为主键,可是主键在整个记载中必须存在而且仅有。最常见的办法当然是运用 MySQL 默许的自增 ID 作为主键,尽管运用其他战略设置的主键也是合法的,可是不是通用的以及引荐的做法。

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

图 1 – MySQL 的主键

MySQL 中默许的 AUTO_INCREMENT 特点在大都情况下能够确保主键的接连性,咱们通过 show create table 指令能够在表的界说i c J m H中能够看到 AUTO_IN[ * 6 F Q nCREMENT 特点的其时值,当咱们向其时表中刺进数据时,它会运用该特点的值作为刺进记载的主键,而每次获取该值也都会将它加一。

CREATE TABLE `trades` (
`id` bigint(20) NOT NULL AUTn I N 1 ! T 3 =O_INCREMENT,
..G m =.
`created_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
)^ # i - n ENGINE=InnoDB AUTO_INCREMENT=17130 DEFAULT CHARSET=utf8mb4

在很多开发者的认知中c z V R,MySQL 的主键都应该是单调递加的,可是在咱们与 MySQL 打交道的进程中会遇到两个问题,首先3 T +是记M w L D q M A载的主键并不接连,其次是或许会创建多个主键相同的记载z ` 7 O d { r,咱们将从以下的两个视点回答 MySQL 不单调和不接连的原因:

  • 较早版别的 MySQL 将 AUTO_INCREMENT 存储在内存中K ( 5 ! J | R,实例重启后会依据表中的数据从头设置该值;
  • 获取 AUTO_INCREMENT 时不会运用业务锁,并发的刺进业务或许呈现部分字段抵触导致w Z .刺进失利;

需求注意的是,咱们在这篇文章中评论的是Y i $ ( – o r MySQL 中最常见的 InnoDB 存储引擎,MyISAM 等其他引擎供给的 AUTO_INCREMENT 完成原理不在本文的评论规模中。

删去记载

AUTO_INCREMENT 特点尽管在) K W A q ; MySQL 中十分常见,可是在较早的 MySQL 版别中,它的完成还4 R ; x K + s比较粗陋,InnoDB 引擎会在内存中存储一个整数表示下一个被分配到的 ID,当客户端向表中刺进数据时会获取 AUTO_INCREMENT 值并将其加一。

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

N $ O V t E 2 – AUTO_INCREMENT 的运用

由于该值存储在内存中,所以在每次 MySQL 实例从头启动后,当客户端第一次向 table_name 表中刺进记载时,MySQL 会运用如下所示的 SQL 句子查找其时表中 ido C T – j P ]最大值,将其加一后作为待刺进记载的主键,并作为其时表中 AUTO_INCREMENT 计数器的初始值2

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

假如让作者完成 AUTO_INCREMENT,在最开端也会运用这种办法。不过这种完成尽管十分简略,可是假如运用者不严厉遵从x B s _ t w _ x A联系型数据库的规划规范,就会呈现如下所示的数据/ ) F l ^ Q M T不一致的问题:

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

图 3Y U ` z 9 $ – 5.7 版别之前的 AUTO_INCMRENT

由于重启了 MySQL 的实例,所以内存中的 AUTO_INCREMENT 计数器会被重置成表中的最大值] Y i,当咱们再向表中刺进新的 trades 记载时会从头运用 10 作为主键,主键也就不是单调的了。在新的 trades 记载刺进之后,eA y , 4 Y w bxecutions 表中的记载就过错的引用了新的 trades,这其实是一个比较严重的过错。

可是这也不彻底是 MySQL 的问题,假如咱们严厉遵从联系型数据库的规划规范,运用外键处理不同表之间的联系,就能够防止上述问题,由于其时 trades# + B G Q w 0 A 载依然有外部的引用,所以外键会制止 tra& Y & U 6 6 8 ades 记载的删去,不过大都公司内部的 DBA 都不引荐或许制止运用外键,所以的确存在呈现这种问题的或许。

可是在 MySQL 8.0 中,AUTO_INCREMENT 计数器的初始化行为发生了改变,每次计数器的变化都会写入到体系的重做日志(Redo log)并在每个检查点存储在引擎私有的体系表中3

In MySQL 8.0, this behavior is changed. The cB 0 V 8 o 3 J @urrent 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 changes4 y 7 d M q m make the current maximum auto-increment counter value persistent across server restarts.

当 MySQL 服务被重启或许处于溃散康复时,它能够从耐久化的检查点和重做日志中康复出最新的 AUu O ?TO_INCREMENT 计数器,防止呈现不单调的主键也处理了这儿提到的问题。

并发业务

为了进步业务的吞吐量,MySQL 能够处理并发履行的多个业务,可是假如并发履行多个刺进新记载的 SQL 句子,或许会导致主键的不接连。如下图所示,业务 1 向数据库中刺进 id = 10 的记载,业务 2 向数据库中刺进 id = 11id =q m , + a v 12 的两条记载:

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

图 4 – 并发业务的履行

不过假如在最后业务 1 由于刺进的记载发生了仅有键抵触导致了回滚,而业务 2 没有发生过错而正常提交,在这时咱们会发现其时表中的主键呈现了不接连的现象,后续新刺进的数据也不再会运用 10 作为记载的主键。

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

图 5 – 不接连的主键

这个现象背面的原因也很简略,尽管在获取 AUTO_INCREMENT 时会加锁,可是该锁是i g , 4 . j 3 6 %句子锁,它的意图是确保 AU@ , q 9 o 1 _ ? ;TO_INCREMENT 的获取不会导致线程竞争,而不是确保 MySQL 中主键的接连4

上述行为是由 InnoDB 存储引擎供给的 innodb_autoinc_lock_mode 装备控制的,该装备决议了获取 AUTO_c I * | q /INCREMENT 计时器时需求先得到的锁,该装备存在三种不同的形式,D u ) v o . W n 1分别是传统形式(? o 7 )Traditional)、接连形式(Consecutive)和穿插形式(Inter* z : I [leaved)m C R t b P v5,其间 MySQL 运用接连形式作为默许的锁形式:

  • 传统形式 innodb_autoinc_lock_mode = 0
    • 在包括 AUTO_INCREMENT 特点的表中刺进数据时,一切INSERT 句子都会获取表等级AG v = d GUTO_INCREMENT 锁,该锁会在其时句子履w ] : c行后释放;
  • 接连形式 innodb_autoinc_lock_mode = 1
    • INSERT ... SELECTREPLACE ... SE. X b u A t zLECT 以及 LOAD DATA 等批量的刺进操作需求获取表等级AUTO_INCREMENT] B Q 锁,该锁会在其时句子履行后释放;
    • 简略的刺进: K 2 y句子(预先知道刺进多少条记载的句子)只需求获取获取 AUTw y ? ) Q y [ 5O_INCREMENT 计数器的互斥锁并在获取主键后直接释放,不需_ g m 4求等候其时句子履行完成;
  • 穿插$ v 2 +形式 innodU s K Pb_autoinc_lock_mode = 2
    • 一切的& ? J [ ?刺进句子都不需求获取表等级AUTO_INCREMENT 锁,可是b w B ~当多个句子刺进的数据行数不确定时,或许存在分配相l v { } [同主键的危险;

这三种形式都不能处理 Mf t # s YySQL 自增主键不接连的问题,想要处理这个问题的终极方案是串行履行一切包括刺进操作的业务,也便是运用数据库的最高阻隔等级 —— 可串行化(Serialiable)。当然直接修正数据库的阻隔等级相对来说有些简略粗暴,基于 MySQL 或许其他存储体系完成彻底串行的刺进也能够确保主键在刺Z F $ i w m D ?进时的接连,可是依然不能防止删去数据导致的不接连。

总结

前期 MySQL 的主键既不是单调的,也不是接连的,这些都是在Z # p ( c w 4 j /其时工程上做出的一些挑选,假如严厉地依照联系型数据库的规划规范,MySQL 开始的规划造成问题的概率也比较低,只有当被删去的主键被外部体系@ A P ? ? j O引用时才会影响数据的一致性,可是今日运用办法的不同却增加出错的或许性,而 MySQL 也在 8.0 中耐久化了 AUTO_INCREMEN- , ET 以防止该问题的呈现。

MySQL 中不接连– u H q ` L |的主键又是一个工程规划向性能低头的比如,牺牲主键的接连性来支持数据的并发刺进,终究进步了 MySQL 服务的吞吐量,作者在几年前刚刚运用 MySQL 时就遇到过这个问题,可是其T k N % g时并没有深究背面的原因,今日从头了解该W p Z a l D问题背面s j 3 f ! i + !的规划决议计划也是个十分风趣D T 4 Z O的进程。咱们在这儿简略总结一下本文的内容,从头回到E m S X今日的问题 — 为什么 MySQL 的自增主键不单调也不接连:

  • MySQL 5.7: r g M _ J 版别之前在内存中存储 AUTO_INCREMENT 计数器,实例重启后会依据表中的数据从头设置,在删去记载M / H 4 g后重启就或许呈现重复的主键,该问题在 8A ? B : a Y / (.0 版别运用重做日志处理,确保了主键的单调性;
  • MySQL 刺进数据获取 AUTOX R O 9 p_INCREMENT 时不会运用业务锁,而是会运用互斥锁,并发的刺进业务或许呈现部分字段抵触导致刺进失利,想要确@ t t保主键的接连需求? W , Z 0 E w U串行地履行刺进句子;

到最后,咱们仍是来看一些比较敞开的相关问题,有爱好的读者能够仔细思考一下下面的问题:

  • M| A / p [ :yISAM 和其他的存储引擎如何s ^ v B y s Q Y C存储 AUTO_INCREMENT 计数器?
  • MySQL 中的 auto_increment_incrementaute # L y - h . ) wo_increment_offset 是用来做什? 7 # H R 2 z 3么的?

假如对文章中的内容有疑问或许想要了解更多软件工程上一些规划决议计划背面的原因,能够在博客下面留言,作者U x j s会及时回复本文相关的疑问并挑选其间适宜的主题作为后续的内容。

引荐阅览

  • 为什么总是需| X 0 j ) = ^ + 4求无意义的 ID
  • 为什么 MySQL 运用 B+ 树
  • 『浅入浅出』MySQL 和 InnoDB

  1. Wikipedia: Primary key h8 V O W [ j 8ttps://en.wikipedia.org/wikiy , 2 Q/Prima^ b xry_key ↩︎

  2. Inn@ k ; I doDB AUTO_INCREMENT CJ A # m C f counter Initialization MySQL 5.7 Reference Manual / 14.6.1.6 AUTO_INCREMENT Handlik J ; v _ # ~ @ng in InnoDB https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html ↩︎

  3. InnoDB AUTO_INCREMENT Counter Initialization MySQL 8.0 Reference Manual / AUTO_INCREMENT Handling in InnoDB https://dev.~ Z 2 i p p ` 7mysql.com/doc/refman/8.0/en/innodb-auto-incremen= R H ht-handling.html ↩︎

  4. auto incremen@ 2 Y $ ! c ~t primary leaving gaps in counting https://stackoverflow.com/questions/16582704/auto-increment-primary-leavingf + z d m 6-gaps-in-counting ↩︎

  5. InnoDB AUTO_INCREMENT Lock Modes MySQL 8.0 Ref| F terence Manual / AUTO_INCREMENT Handling in InnoD{ % I j l V r `B https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-hands L D oling.html ↩︎

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

转载申请

本著作采用知识同享署名 4.0 世界答应协议进行答应,转载时请注明原文链接,图片在运用时请保留全部内容,可适当缩放# U N e并在引用途附上图片地点的文章链接。

文章图片

你能够在 技术文章配图指南 中找到画图的办法和素材。

发表评论

提供最优质的资源集合

立即查看 了解详情