一道经典的MySQL面试题，答案出现三次反转

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

  前几天偶然看到你们在讨论一道面试题，并且答案也不够统一，我感受蛮有意思，在此就作一个解读，整个过程当中确实会有几处反转。java

 咱们先来看下题目：mysql

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

和后面的一些题目总体来看，难度不大，都是一些看起来很基础的问题，可是这道题目引发了个人注意，由于这道题目的背景过于开放，因此答案也是不固定的，而这也是咱们在技术学习中须要保持的严谨态度。算法

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

按照咱们常规理解的逻辑，ID自增，应该是18，按照这个逻辑怎么都不该该是15吧？数据库

可是这个答案对吗？显然不是，咱们进入第一轮反转。学习

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

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

我认真查了一下这个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 的视图语句，也是能够对列值溢出进行有效判断的。