相关链接:
- 《MySQL自增变量auto_increment踩坑,重启后值丢失》
- 《Be Careful With MySQL's auto_increment. How We Ended Up Losing Data》
- 《AUTO_INCREMENT Handling in InnoDB》
背景描述
与相关链接《Be Careful...》中的案例很类似,某个业务系统中也有两张表,暂且称其为 pending_task 和 task_archive。它们的存储引擎都为 InnoDB;MySQL 版本为 5.7。
- 业务系统会先在 pending_task 表中创建一个新的 task 记录;
- 此表使用自增主键,作为 task 的唯一标识 —— task_id;
- task 结束后 pending_task 表中的相应记录会被删除;
- task_archive 表用于记录 task 的最终存档信息,主键就是 task_id。
先不讨论这种设计是否恰当。
该系统平时一切正常,但是一旦 MySQL 被重启,就很可能在向 task_archive 插入数据时引发重复主键的问题。
很容易就想到是 pending_task 表中的新生成的 task_id 与以前的记录重复了。
一般人都是去复盘系统维护过程是否有不当操作,导致自增字段出错,很少会去怀疑 MySQL。
但事实就是 MySQL 处理自增字段的逻辑导致了重复ID。
(后来看了 MySQL 官方的说明措辞,居然隐约把这个“逻辑”当作了“特性”,而不是Bug...)
问题原因
在 MySQL 5.7 及 以前的版本中,InnoDB 表中的 auto-increment计数器 会把值存放在内存中,不会写入磁盘。一旦 MySQL 服务重启,这个值就丢了,InnoDB 引擎会根据表中现有的数据重新计算该计数器的值:
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
因为上述业务系统会把 pending_task 中已结束task的记录删除,所以MySQL服务重启后,自增字段计数器重新计算,并得到了一个小于被删除 task_id 的值。如:当 pending_task 表中没有记录时,计算得到的结果为 0。
这种“特性”是不是很违背普通人的直觉?!
MySQL 8.0 auto-increment 计数器逻辑有变!
在 MySQL 8.0 中,这个计数器的逻辑变了:每当计数器的值有变,InnoDB 会将其写入 redo log,保存到引擎专用的系统表中。
MySQL 正常关闭后重启:从系统表中获取计数器的值。
MySQL 故障后重启:从系统表中获取计数器的值;从最后一个检查点开始扫描 redo log 中记录的计数器值;取这两者的最大值作为新值。
但是这个处理逻辑不能保证最后拿到的值是正确的:如果在计数器 redo log 落盘前服务崩溃,那么就可能拿到一个之前被使用过的值。
另,MySQL 8.0 开始,只有在 拷贝表空间 且 未指定.cfg元文件 时,才会使用以下语句确定计数器的值:
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
解决方案
方案1:定制 MySQL
修改 MySQL 源码,自定义 auto-increment计数器 的运行逻辑。
显然这种方案成本很大,设计研发及后续的兼容性保障都是大手笔。
一般只有配备专职数据库研发团队的组织才会考虑。
当然也不排除某些不自量力、不负责、自私的决策人故意入坑。
方案2:升级到 MySQL 8.0
这个方案的影响得根据实际项目情况进行评估。
如果改动牵扯的范围比较广,回归测试不够彻底,很可能埋下一些大坑。
如果牵扯范围很小,具体影响边界很清晰,那也不失为一个好方案。
当然,你还是得评估极端情况 “计数器 redo log 落盘前 MySQL 崩溃” 对业务的影响,并设计相应的处理方案。
方案3:使用 MyISAM
MyISAM 中的 auto-increment计数器 数值是落盘的(.MYI文件头部),所以不存在重启后丢失的问题。
是否应该使用 MyISAM 又牵扯到它与 InnoDB 的优缺点比较:《MyISAM vs InnoDB》。
很多人因为 MyISAM 不支持行锁、不支持事务 而坚决使用 InnoDB。
方案4:弃用自增字段,选用其它唯一标识生成方案
既然 MySQL 的自增字段不靠谱,那我们就选一个靠谱的方案来生成记录的唯一标识。
UUID、Snowflake(分布式ID)是比较常见的方案。相应的性能、成本也都需要根据实际情况评估。
(理论上,UUID是有可能重复的。在实际合适的应用场景中,其重复几率低到可以忽略。)
注意:MySQL 中的 UUID() 方法是 version 1 —— 基于时间和节点ID(Mac地址或一个随机数)
方案5:保留自增字段值最大的记录
根据 MySQL 5.7 中的 auto-increment计数器逻辑,只要我们保留 自增字段值 最大的记录,MySQL 重启后重新计算得到的计数值就不会“回退”。
当然,此方案需要更改业务逻辑,甚至影响到相关干系人对系统设计的认知。
如,对于前述的业务系统,如果某个 task 已结束,但因其 task_id 是 pending_task 中最大的,而一直驻留在此表中,直到有新的 task 产生。这种行为在语义上和 “pending” 不符。
但是在实际项目中,这种偏 hack 的方案很可能是短时间内成本最低的。这个“短时间”甚至会长到超出所有人的预期,甚至让这种方案成为“标准”方案(中性语气,不是反讽,没有任何贬义)。
前述业务系统最后采用了该方案。
相关推荐
MySQL AUTO_INCREMENT 和主键等解释
MySQL 序列 AUTO_INCREMENT详解及实例代码 MySQL序列是一组整数:1, 2, 3, …,由于一张数据表只能有一个字段自增主键, 如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现。 本章我们将介绍如何使用...
主要介绍了MySQL查询和修改auto_increment的方法,实例分析了select查询auto_increment及ALTER修改auto_increment的技巧,需要的朋友可以参考下
主要介绍了python mysql自增字段AUTO_INCREMENT值的修改方式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
重置 MySQL 自增列 AUTO_INCREMENT 初时值 注意, 使用以下任意方法都会将现有... (好处, 简单, AUTO_INCREMENT 值重新开始计数.) 怎么重置mysql的自增列 1. 支持设置自增列的值 ALTER TABLE table_name AUTO_INCRE
当清空一个表的时候,重新插入数据,发现auto_increment属性的字段计数不是从1开始的时候,可以使用以下命令
04_auto_increment.avi MYSQL应用 MYSQL视频 MYSQL教程,讲解中连接上一集03集
本篇文章是对mysql中的auto_increment的问题进行了详细的分析介绍,需要的朋友参考下
blog_auto_increment
mysql中的自增auto_increment功能相信每位phper都用过,也都知道如何设置字段为自增字段,但并不是所有phper都知道auto_increment的起始值和递增量是如何设置的!本文就为大家分享一下mysql字段自增功能的具体查看及...
像MySQL一样,将Mongoid id字段更改为Integer的宝石。 MongoDB文档中的想法: 注意! 这个宝石已经帮助超过了4年,并产生了超过一百万行,这是非常...gem 'mongoid_auto_increment_id' , "0.6.1" # Mongoid 3.1.x gem
`id` bigint(20) NOT NULL AUTO_INCREMENT, `category_id` bigint(20) NOT NULL COMMENT '内容类目ID', `title` varchar(200) DEFAULT NULL COMMENT '内容标题', `sub_title` varchar(100) DEFAULT NULL COMMENT...
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时,相关锁的行为; 通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡 【0】我们先对insert做一下分类 首先insert大致上可以...
mongoose_auto_increment 创建一个Counter用来记录所有Collections增长数,以Mongoose plugin的方式与对应的Scheme相关联,以此实现Mongoose自动增长列
表名:chart2010 语句:id int primary key auto_increment, num int 表名:menu 语句: id int primary key auto_increment, name varchar(20), cost varchar(10) 表名:uid 语句: id int primary key auto_increment, ...