一入职就遇到MySQL这么大Bug！差点背锅走人！

最新推荐文章于 2024-07-07 23:10:31 发布

程序员小乐

最新推荐文章于 2024-07-07 23:10:31 发布

阅读量205

点赞数

文章标签： character portlet etag xhtml innodb

推荐大家关注一个公众号

点击上方 "编程技术圈"关注, 星标或置顶一起成长

后台回复“大礼包”有惊喜礼包！

每日英文

It's true that we don't know what we've got until we lose it, but it's also true that we don't know what we've been missing until it arrives.

的确只有当我们失去时，才知道曾经拥有的是什么。同样的，只有当我们拥有了才知道从前失去了什么。

每日掏心话

我们都不是很完美的人，但我们要接受不完美的自己。一辈子不长，对自己好点。

责编：乐乐 | 来自：程序IT圈

编程技术圈(ID:study_tech)第 1291次推文

往日回顾：为什么 sin(x²)+sin(y²)=1 的图像这么复杂？

正文

群里一网友这两天刚入职新公司，遇到一个重启 MySQL 服务后，自动增长值丢失问题，差点背锅走人。下面我们一起来回顾一下这个问题。
在 mysql 中用自增列作为主键时，先往表里插入 5 条数据，此时表里数据 id 为 1、2、3、4、5，如果此时删除 id=4、5 的数据后，再重启数据库，重启成功后向表里 insert 数据的时候，INNODB、MyISAM 引擎下 ID 分别是从几开始增加？如果你没经历过，或者当面试时被问到这个问题时，相信多数人都是一脸懵逼。MD 谁有事没事去重启线上数据库嘛。最主要的是很多没有测试过这个场景，没有这方面的经验，我在这里做个笔记，大家轻点喷！
MySQL 通常使用的引擎都是 INNODB，在建表时，一般使用自增列作为表的主键，这样的表对提高性能有一定的帮助。但是自增列有一个坑，并且这个坑存在了很久，一直到 MySQL 8.0 版本，才修复了这个坑，这个坑就是表的自增列变量 auto_increment 在 MySQL 重启后，有可能丢失。
「innodb 引擎(低版本)：Innodb 表中把自增列作为主键 ID 时，自增列是通过 auto-increment 计数器实现的，计数器的最大值是记录到内存中的，重启数据库后，会导致 auto-increment 计数器重置，从而会导致主键 ID 重置。」
「MyISam 引擎：MyISAM 表会把自增列(auto-increment 计数器)最大值是记录到数据文件里，重启 MySQL 自增列(计数器)最大值不会丢失，从而使用自增列作为主键 ID 时也不会丢失。」
innodb 主键重置问题在 MySQL 低版本中，InnoDB 表中使用自增的 auto-increment 计数器 会把值存放在内存中，不会写入磁盘。一旦 MySQL 服务重启，这个值就丢了，InnoDB 引擎会根据表中现有的数据重新计算该计数器的值：获取表中最大的自增主键 ID 作为auto-increment 计数器的最大计数，当 insert 数据时，在 auto-increment 计数器最大值上 1。
先创建一张 user 表，新增几条数据：
//1.创建user表：自增列作为主键ID
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `age` int(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

//2.插入5条数据
INSERT INTO `user`(`name`, age) VALUES('刘备1', 21);
INSERT INTO `user`(`name`, age) VALUES('刘备2', 22);
INSERT INTO `user`(`name`, age) VALUES('刘备3', 23);
INSERT INTO `user`(`name`, age) VALUES('刘备4', 24);
INSERT INTO `user`(`name`, age) VALUES('刘备5', 25);
场景一「mysql 数据库不重启时，innodb 自增主键 ID 会根据 auto-increment 计数器一直递增。」
向 user 表里插入 5 条数据，主键 ID 按自增列通过 auto-increment 计数器实现自增。
在 user 表里删除 id 为 4、5 的数据，再向 user 表中插入一条数据，主键 ID 是 auto-increment 的值 6。
场景二mysql 数据库重启后，innodb 自增主键 ID 会根据 auto-increment 计数器的重置而重置。
在场景一的基础上，在删除 id 为 6、3 的数据后，此时 auto-increment 计数器的值为 7，user 表里的 id 最大是 2。
然后重启数据库后，auto-increment 计数器的值变为 3，也就是 user 表里的自增列 ID 的最大值 2 加 1。
搜索公众号后端架构师后台回复“架构整洁”，获取一份惊喜礼包。
此时在插入数据时，自增 ID 会从 3 开始自增。Innodb 表中把自增列作为主键 ID 时，在 mysql 重启后就会存在 ID 重置问题。**删除数据后，再重启，AUTO_INCREMENT 会查询表里最大 ID 并进行重置，重置后和重启前AUTO_INCREMENT 计数器的值不同。**在 MyISAM 引擎表中的自增列不会存在这个问题。
MySQL 8.0 auto-increment 计数器逻辑在 MySQL 8.0 中，这个计数器的逻辑变了：每当计数器的值有变，InnoDB 会将其写入 redo log，保存到引擎专用的系统表中。MySQL 正常关闭后重启：从系统表中获取计数器的值。MySQL 故障后重启：从系统表中获取计数器的值；从最后一个检查点开始扫描 redo log 中记录的计数器值；取这两者的最大值作为新值。
总结1）如果 mysql 重启了，那么 innodb 表在启动后，AUTO_INCREMENT 值会自动检测出、并重置为当前表中自增列的最大值 +1。2）假如一个表里 AUTO_INCREMENT 计数器的值是 10，此时执行update table set id = 15 where id = 9后，如果这时再继续插入数据，到了自增 ID=15 的时候是会报错。
但是这个时候继续插入，就不会报错。因为刚才即使报错了，AUTO_INCREMENT 的值依旧会增加。3）现在使用的一般都是 innodb 引擎，如果将 myisam 引擎转换过来的时候，一定要小心这个引擎在自增 id 上的不同表现。在主从使用不同引擎的时候,也会出现问题，最好将引擎改完一致性的。
你还有什么想要补充的吗？
PS：欢迎在留言区留下你的观点，一起讨论提高。如果今天的文章让你有新的启发，欢迎转发分享给更多人。


版权申明：内容来源网络，版权归原创者所有。除非无法确认，我们都会标明作者及出处，如有侵权烦请告知，我们会立即删除并表示歉意。谢谢!

欢迎加入后端架构师交流群，在后台回复“学习”即可。

最近面试BAT，整理一份面试资料《Java面试BAT通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。在这里，我为大家准备了一份2021年最新最全BAT等大厂Java面试经验总结。
别找了，想获取史上最简单的Java大厂面试题学习资料
扫下方二维码回复「面试」就好了


猜你还想看
阿里、腾讯、百度、华为、京东最新面试题汇集
统一处理 try...catch 这么香，求求你不要再满屏写了，再发现扣绩效！

955.WLB 不加班公司名单（持续更新）

MySQL 大批量插入，如何过滤掉重复数据？

嘿，你在看吗？