这是学习笔记的第 2137 篇文章

 

  前几天偶然看到大家在讨论一道面试题,而且答案也不够统一,我感觉蛮有意思,在此就做一个解读,整个过程中确实会有几处反转。

 

 我们先来看下题目:

一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把MySQL重启,再Insert一条记录,这条记录的ID是18还是15.

 

和后面的一些题目整体来看,难度不大,都是一些看起来很基础的问题,但是这道题目引起了我的注意,因为这道题目的背景过于开放,所以答案也是不固定的,而这也是我们在技术学习中需要保持的严谨态度。

 

首先这道题整体来看,想表达的是对于MySQL中自增列的理解。

按照我们常规理解的逻辑,ID自增,应该是18,按照这个逻辑怎么都不应该是15吧?

 

但是这个答案对吗?显然不是,我们进入第一轮反转。

 

 

确实,对于自增列的问题,这个是MySQL里面饱受诟病的老问题了
。如果节点重启,会从数据列中按照max(id)+1的方式来处理,在多环境历史数据归档的情况下,如果主库重启,很可能会出现数据不一致的情况,记得在MySQL
bug中很多人留言,说十多年前的老问题了,怎么还不解决。

而在OpenWorld上面Percona CEO Peter也再次提到了这个问题。

 

我认真查了一下这个bug的历史,巧合的是,这个问题是Peter在十几年前提出的,时光荏苒,一直没有修复。

好的,按照MySQL bug的思路来理解,答案应该是15了。

 

但是这个答案对吗?显然不是,我们进入第二轮反转。

 

这个题目的背景是不够清晰的,这个表的存储引擎没有说是InnoDB还是MyISAM,所以存在不确定性,这么说的意义在于,自增列的信息在MyISAM和InnoDB中的维护逻辑是不大一样的,在MyISAM中是存储持久化在文件中的,当数据库重启之后,是可以通过持久化的信息持续对ID进行自增的,而InnoDB的自增列信息既不在.frm文件,也不在.ibd文件中,所以在此启动的时候会按照max(id)+1的算法进行修复。

所以如果是MyISAM,则答案应该是18,而如果是InnoDB,则答案是15.

 

我们可以综合对比,用一个小的测试来模拟复现,我们选择的是MySQL 5.7环境。

 

为了对比明显,我们创建两张表test_innodb和test_myisam,分别对应InnoDB和MyISAM存储引擎,来做同样的操作,看看重启后的差异情况。

 

>>create table test_innodb(id int primary key auto_increment,name varchar(30)) engine=innodb;
>>create table test_myisam(id int primary key auto_increment,name varchar(30))
engine=myisam;
插入几行数据,查看数据

 
>>insert into test_innodb(name) values('aa'),('bb'),('cc'); Query OK, 3 rows
affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 >>insert into
test_myisam(name) values('aa'),('bb'),('cc'); Query OK, 3 rows affected (0.00
sec) Records: 3 Duplicates: 0 Warnings: 0
 
>>select *from test_innodb; +----+------+ | id | name | +----+------+ | 1 | aa
| | 2| bb  | | 3 | cc | +----+------+ 3 rows in set (0.00 sec) >>select *from
test_myisam; +----+------+ | id | name | +----+------+ | 1 | aa | | 2| bb  | |
3 | cc | +----+------+ 3 rows in set (0.00 sec)
 
>>insert into test_innodb(id,name) values(5,'ee'); Query OK, 1 row affected
(0.00 sec) >>insert into test_myisam(id,name) values(5,'ee'); Query OK, 1 row
affected (0.00 sec)
 

此时查看test_innodb自增列已经开始增长,值为6.
>>show create table test_innodb\G CREATE TABLE `test_innodb` ( `id` int(11)
NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) )
ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
 

删除id=5的记录
>>delete from test_innodb where id=5; Query OK, 1 row affected (0.01 sec)
删除记录之后,自增列还是保持不变。
>>show create table test_innodb\G CREATE TABLE `test_innodb` ( `id` int(11)
NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) )
ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
同理test_myisam也做同样的测试,结果是完全一样的,在此略过日志。
>>shutdown; Query OK, 0 rows affected (0.00 sec)
 

重启数据库
#mysqld_safe --defaults-file=/data/mysql_5723/my.cnf &
此时查看test_innodb和test_myisam的自增列就开始出现差异了。

MyISAM存储引擎的表test_myisam的自增列还是不变,为6.
>>show create table test_myisam\G CREATE TABLE `test_myisam` ( `id` int(11)
NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) )
ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
而InnoDB存储引擎的表test_innodb的自增列却变为了4
>>show create table test_innodb\G *************************** 1. row
*************************** Table: test_innodb Create Table: CREATE TABLE
`test_innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30)
DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT
CHARSET=utf8
 
>>insert into test_innodb(name) values('ee'); Query OK, 1 row affected (0.00
sec) >>insert into test_myisam(name) values('ee');
Query OK, 1 row affected (0.00 sec)
可以看到两张表的id列已经分道扬镳了。
>>select *from test_innodb; +----+------+ | id | name | +----+------+ | 1 | aa
| | 2 | bb | | 3 | cc | | 4 | ee | +----+------+ 4 rows in set (0.00 sec)
>>select *from test_myisam; +----+------+ | id | name | +----+------+ | 1 | aa
| | 2 | bb | | 3 | cc | | 6 | ee | +----+------+ 4 rows in set (0.00 sec)

小结:对于MyISAM和InnoDB的表,因为存储引擎对于自增列的实现机制不同,ID值也可能会有所不同,对于InnoDB存储引擎的表,ID是按照max(id)+1的算法来计算的。

 

但是这个答案对吗?显然不是,因为还是不够严谨,我们进入第三轮反转。

 

 

这个问题不够严谨是因为技术是逐步发展的,这个问题在MySQL
8.0中有了答案,对于InnoDB的自增列信息,如果断电之后会直接丢失,很可能造成级联从库间的数据同步出现问题,而在MySQL
8.0之后,这个信息写入了共享表空间中,所以服务重启之后,还是可以继续追溯这个自增列的ID变化情况的。 

限于篇幅,因为测试日志是很相似的,我就直接给出测试后的日志,这是在数据库重启之后的自增列情况,可以看到test_innodb和test_myisam的自增列是完全一样的。
mysql> show create table test_myisam\G *************************** 1. row
*************************** Table: test_myisam Create Table: CREATE TABLE
`test_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30)
DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> show
create table test_innodb\G *************************** 1. row
*************************** Table: test_innodb Create Table: CREATE TABLE
`test_innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30)
DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
我们做一个小结:

 

在MySQL 8.0之前:

    1)如果是MyISAM表,则数据库重启后,ID值为18

    2)如果是InnoDB表,则数据库重启后,ID值为15

在MySQL 8.0开始,

    1)如果是MyISAM表,则数据库重启后,ID值为18

    2)如果是InnoDB表,则数据库重启后,ID值为18

 

此处需要补充的是,对于ID自增列,在MySQL 5.7中可以使用sys
schema来进行有效监控了,可以查看视图schema_auto_increment_columns      来进行列值溢出的有效判断。 

更难能可贵的是,如果是MySQL 5.7版本以下,虽然没有sys schema特性,但是可以复用MySQL
5.7中的schema_auto_increment_columns 的视图语句,也是可以对列值溢出进行有效判断的。 

 

 

 

 

 

技术
©2019-2020 Toolsou All rights reserved,
指定位置输出字符串--PTA判断当前对象是不是数组的4种方式冲突声明(conflicting declaration)解决Vue el-select 获取label值新手玩海思HI3520D开发板(一,升级)Map 判断key对应的value值是否存在-containsKey()【JAVA】【华为校园招聘笔试-软件】2020-09-09C#/.NET 系统优化专题(redis第六篇 数据结构【List】)迷宫的最短路径之BFS算法(python实现)Result返回结果信息的封装