一道經(jīng)典的MySQL面試題，答案出現(xiàn)三次反轉(zhuǎn)

作者：楊建榮的學(xué)習(xí)筆記 2019-10-21 10:57:56

前幾天偶然看到大家在討論一道面試題，而且答案也不夠統(tǒng)一，我感覺(jué)蠻有意思，在此就做一個(gè)解讀，整個(gè)過(guò)程中確實(shí)會(huì)有幾處反轉(zhuǎn)。

我們先來(lái)看下題目：

一張表，里面有ID自增主鍵，當(dāng)insert了17條記錄之后，刪除了第15,16,17條記錄，再把MySQL重啟，再Insert一條記錄，這條記錄的ID是18還是15.

和后面的一些題目整體來(lái)看，難度不大，都是一些看起來(lái)很基礎(chǔ)的問(wèn)題，但是這道題目引起了我的注意，因?yàn)檫@道題目的背景過(guò)于開(kāi)放，所以答案也是不固定的，而這也是我們?cè)诩夹g(shù)學(xué)習(xí)中需要保持的嚴(yán)謹(jǐn)態(tài)度。

首先這道題整體來(lái)看，想表達(dá)的是對(duì)于MySQL中自增列的理解。

按照我們常規(guī)理解的邏輯，ID自增，應(yīng)該是18，按照這個(gè)邏輯怎么都不應(yīng)該是15吧?

但是這個(gè)答案對(duì)嗎?顯然不是，我們進(jìn)入第一輪反轉(zhuǎn)。

確實(shí)，對(duì)于自增列的問(wèn)題，這個(gè)是MySQL里面飽受詬病的老問(wèn)題了。如果節(jié)點(diǎn)重啟，會(huì)從數(shù)據(jù)列中按照max(id)+1的方式來(lái)處理，在多環(huán)境歷史數(shù)據(jù)歸檔的情況下，如果主庫(kù)重啟，很可能會(huì)出現(xiàn)數(shù)據(jù)不一致的情況，記得在MySQL bug中很多人留言，說(shuō)十多年前的老問(wèn)題了，怎么還不解決。

而在OpenWorld上面Percona CEO Peter也再次提到了這個(gè)問(wèn)題。

一道經(jīng)典的MySQL面試題，答案出現(xiàn)三次反轉(zhuǎn)

我認(rèn)真查了一下這個(gè)bug的歷史，巧合的是，這個(gè)問(wèn)題是Peter在十幾年前提出的，時(shí)光荏苒，一直沒(méi)有修復(fù)。

好的，按照MySQL bug的思路來(lái)理解，答案應(yīng)該是15了。

但是這個(gè)答案對(duì)嗎?顯然不是，我們進(jìn)入第二輪反轉(zhuǎn)。

這個(gè)題目的背景是不夠清晰的，這個(gè)表的存儲(chǔ)引擎沒(méi)有說(shuō)是InnoDB還是MyISAM,所以存在不確定性，這么說(shuō)的意義在于，自增列的信息在MyISAM和InnoDB中的維護(hù)邏輯是不大一樣的，在MyISAM中是存儲(chǔ)持久化在文件中的，當(dāng)數(shù)據(jù)庫(kù)重啟之后，是可以通過(guò)持久化的信息持續(xù)對(duì)ID進(jìn)行自增的，而InnoDB的自增列信息既不在.frm文件，也不在.ibd文件中，所以在此啟動(dòng)的時(shí)候會(huì)按照max(id)+1的算法進(jìn)行修復(fù)。

所以如果是MyISAM,則答案應(yīng)該是18，而如果是InnoDB，則答案是15。

我們可以綜合對(duì)比，用一個(gè)小的測(cè)試來(lái)模擬復(fù)現(xiàn)，我們選擇的是MySQL 5.7環(huán)境。

為了對(duì)比明顯，我們創(chuàng)建兩張表test_innodb和test_myisam，分別對(duì)應(yīng)InnoDB和MyISAM存儲(chǔ)引擎，來(lái)做同樣的操作，看看重啟后的差異情況。

>>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;

插入幾行數(shù)據(jù)，查看數(shù)據(jù)：

>>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

查看兩張表的數(shù)據(jù)情況，數(shù)據(jù)是完全一樣。

>>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)

在1,2，3的基礎(chǔ)上，我們繼續(xù)插入值為5，跳過(guò)id值為4。

>>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)

此時(shí)查看test_innodb自增列已經(jīng)開(kāi)始增長(zhǎng)，值為6。

>>show create table test_innodb\G CREATE TABLE `test_innodb` ( `id` int(11) NOT AUTO_INCREMENT, `name` varchar(30) DEFAULT , PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf81 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 AUTO_INCREMENT, `name` varchar(30) DEFAULT , PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf81 row in set (0.00 sec)

同理test_myisam也做同樣的測(cè)試，結(jié)果是完全一樣的，在此略過(guò)日志。

我們停止數(shù)據(jù)庫(kù)

>>shutdown;Query OK, 0 rows affected (0.00 sec)

重啟數(shù)據(jù)庫(kù)

#mysqld_safe --defaults-file=/data/mysql_5723/my.cnf &

此時(shí)查看test_innodb和test_myisam的自增列就開(kāi)始出現(xiàn)差異了。

MyISAM存儲(chǔ)引擎的表test_myisam的自增列還是不變，為6。

>>show create table test_myisam\G CREATE TABLE `test_myisam` ( `id` int(11) NOT AUTO_INCREMENT, `name` varchar(30) DEFAULT , PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf81 row in set (0.00 sec)

而InnoDB存儲(chǔ)引擎的表test_innodb的自增列卻變?yōu)榱?。

>>show create table test_innodb\G *************************** 1. row *************************** Table: test_innodbCreate Table: CREATE TABLE `test_innodb` ( `id` int(11) NOT AUTO_INCREMENT, `name` varchar(30) DEFAULT , PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

我們繼續(xù)插入一條數(shù)據(jù)，保持id列自增。

>>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列已經(jīng)分道揚(yáng)鑣了。

>>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)

小結(jié)：對(duì)于MyISAM和InnoDB的表，因?yàn)榇鎯?chǔ)引擎對(duì)于自增列的實(shí)現(xiàn)機(jī)制不同，ID值也可能會(huì)有所不同，對(duì)于InnoDB存儲(chǔ)引擎的表，ID是按照max(id)+1的算法來(lái)計(jì)算的。

但是這個(gè)答案對(duì)嗎?顯然不是，因?yàn)檫€是不夠嚴(yán)謹(jǐn)，我們進(jìn)入第三輪反轉(zhuǎn)。

這個(gè)問(wèn)題不夠嚴(yán)謹(jǐn)是因?yàn)榧夹g(shù)是逐步發(fā)展的，這個(gè)問(wèn)題在MySQL 8.0中有了答案，對(duì)于InnoDB的自增列信息，如果斷電之后會(huì)直接丟失，很可能造成級(jí)聯(lián)從庫(kù)間的數(shù)據(jù)同步出現(xiàn)問(wèn)題，而在MySQL 8.0之后，這個(gè)信息寫(xiě)入了共享表空間中，所以服務(wù)重啟之后，還是可以繼續(xù)追溯這個(gè)自增列的ID變化情況的。

限于篇幅，因?yàn)闇y(cè)試日志是很相似的，我就直接給出測(cè)試后的日志，這是在數(shù)據(jù)庫(kù)重啟之后的自增列情況，可以看到test_innodb和test_myisam的自增列是完全一樣的。

mysql> show create table test_myisam\G*************************** 1. row *************************** Table: test_myisamCreate Table: CREATE TABLE `test_myisam` ( `id` int(11) NOT AUTO_INCREMENT, `name` varchar(30) DEFAULT , PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec) 
mysql> show create table test_innodb\G *************************** 1. row *************************** Table: test_innodbCreate Table: CREATE TABLE `test_innodb` ( `id` int(11) NOT AUTO_INCREMENT, `name` varchar(30) DEFAULT , PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)