好險！一入職，就遇到MySQL這么大Bug！差點背鍋走人

群里一網(wǎng)友這兩天剛?cè)肼毿鹿?，遇到一個重啟 MySQL 服務后，自動增長值丟失問題，差點背鍋走人。下面我們一起來回顧一下這個問題。

在 mysql 中用自增列作為主鍵時，先往表里插入 5 條數(shù)據(jù)，此時表里數(shù)據(jù) id 為 1、2、3、4、5，如果此時刪除 id=4、5 的數(shù)據(jù)后，再重啟數(shù)據(jù)庫，重啟成功后向表里 insert 數(shù)據(jù)的時候，INNODB、MyISAM 引擎下 ID 分別是從幾開始增加？如果你沒經(jīng)歷過，或者當面試時被問到這個問題時，相信多數(shù)人都是一臉懵逼。MD 誰有事沒事去重啟線上數(shù)據(jù)庫嘛。最主要的是很多沒有測試過這個場景，沒有這方面的經(jīng)驗，我在這里做個筆記，大家輕噴！

MySQL 通常使用的引擎都是 INNODB，在建表時，一般使用自增列作為表的主鍵，這樣的表對提高性能有一定的幫助。但是自增列有一個坑，并且這個坑存在了很久，一直到 MySQL 8.0 版本，才修復了這個坑，這個坑就是表的自增列變量 auto_increment 在 MySQL 重啟后，有可能丟失。

• 「innodb 引擎(低版本)：Innodb 表中把自增列作為主鍵 ID 時，自增列是通過 auto-increment 計數(shù)器實現(xiàn)的，計數(shù)器的最大值是記錄到內(nèi)存中的，重啟數(shù)據(jù)庫后，會導致 auto-increment 計數(shù)器重置，從而會導致主鍵 ID 重置?！?/strong>

• 「MyISam 引擎：MyISAM 表會把自增列(auto-increment 計數(shù)器)最大值是記錄到數(shù)據(jù)文件里，重啟 MySQL 自增列(計數(shù)器)最大值不會丟失，從而使用自增列作為主鍵 ID 時也不會丟失?！?/strong>

innodb 主鍵重置問題

在 MySQL 低版本中，InnoDB 表中使用自增的 auto-increment 計數(shù)器 會把值存放在內(nèi)存中，不會寫入磁盤。一旦 MySQL 服務重啟，這個值就丟了，InnoDB 引擎會根據(jù)表中現(xiàn)有的數(shù)據(jù)重新計算該計數(shù)器的值：獲取表中最大的自增主鍵 ID 作為auto-increment 計數(shù)器的最大計數(shù)，當 insert 數(shù)據(jù)時，在 auto-increment 計數(shù)器最大值上 1。

先創(chuàng)建一張 user 表，新增幾條數(shù)據(jù)：

//1.創(chuàng)建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條數(shù)據(jù)
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 數(shù)據(jù)庫不重啟時，innodb 自增主鍵 ID 會根據(jù) auto-increment 計數(shù)器一直遞增?！?/strong>

向 user 表里插入 5 條數(shù)據(jù)，主鍵 ID 按自增列通過 auto-increment 計數(shù)器實現(xiàn)自增。

在 user 表里刪除 id 為 4、5 的數(shù)據(jù)，再向 user 表中插入一條數(shù)據(jù)，主鍵 ID 是 auto-increment 的值 6。

場景二

** mysql 數(shù)據(jù)庫重啟后，innodb 自增主鍵 ID 會根據(jù) auto-increment 計數(shù)器的重置而重置。**

在場景一的基礎上，在刪除 id 為 6、3 的數(shù)據(jù)后，此時 auto-increment 計數(shù)器的值為 7，user 表里的 id 最大是 2。

然后重啟數(shù)據(jù)庫后，auto-increment 計數(shù)器的值變?yōu)?3，也就是 user 表里的自增列 ID 的最大值 2 加 1。

此時在插入數(shù)據(jù)時，自增 ID 會從 3 開始自增。Innodb 表中把自增列作為主鍵 ID 時，在 mysql 重啟后就會存在 ID 重置問題。**刪除數(shù)據(jù)后，再重啟，AUTO_INCREMENT 會查詢表里最大 ID 并進行重置，重置后和重啟前AUTO_INCREMENT 計數(shù)器的值不同。**在 MyISAM 引擎表中的自增列不會存在這個問題。

MySQL 8.0 auto-increment 計數(shù)器邏輯

在 MySQL 8.0 中，這個計數(shù)器的邏輯變了：每當計數(shù)器的值有變，InnoDB 會將其寫入 redo log，保存到引擎專用的系統(tǒng)表中。MySQL 正常關(guān)閉后重啟：從系統(tǒng)表中獲取計數(shù)器的值。MySQL 故障后重啟：從系統(tǒng)表中獲取計數(shù)器的值；從最后一個檢查點開始掃描 redo log 中記錄的計數(shù)器值；取這兩者的最大值作為新值。

總結(jié)

1）如果 mysql 重啟了，那么 innodb 表在啟動后，AUTO_INCREMENT 值會自動檢測出、并重置為當前表中自增列的最大值 +1。2）假如一個表里 AUTO_INCREMENT 計數(shù)器的值是 10，此時執(zhí)行update table set id = 15 where id = 9后，如果這時再繼續(xù)插入數(shù)據(jù)，到了自增 ID=15 的時候是會報錯。但是這個時候繼續(xù)插入，就不會報錯。因為剛才即使報錯了，AUTO_INCREMENT 的值依舊會增加。3）現(xiàn)在使用的一般都是 innodb 引擎，如果將 myisam 引擎轉(zhuǎn)換過來的時候，一定要小心這個引擎在自增 id 上的不同表現(xiàn)。在主從使用不同引擎的時候,也會出現(xiàn)問題，最好將引擎改完一致性的。