前言

自增主鍵沒(méi)有持久化是個(gè)比較早的bug，這點(diǎn)從其在官方bug網(wǎng)站的id號(hào)也可看出（https://bugs.mysql.com/bug.php?id=199）。由Peter Zaitsev（現(xiàn)Percona CEO）于2003年提出。歷史悠久且臭名昭著。

首先，直觀的重現(xiàn)下。

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.36 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.35 sec)

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
+----+
rows in set (0.01 sec)

雖然id為3的記錄刪除了，但再次插入null值時(shí)，并沒(méi)有重用被刪除的3，而是分配了4。

刪除id為4的記錄，重啟數(shù)據(jù)庫(kù)，重新插入一個(gè)null值。

mysql> delete from t1 where id=4;
# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
rows in set (0.00 sec)

可以看到，新插入的null值分配的是3，按照重啟前的操作邏輯，此處應(yīng)該分配5啊。

這就是自增主鍵沒(méi)有持久化的bug。究其原因，在于自增主鍵的分配，是由InnoDB數(shù)據(jù)字典內(nèi)部一個(gè)計(jì)數(shù)器來(lái)決定的，而該計(jì)數(shù)器只在內(nèi)存中維護(hù)，并不會(huì)持久化到磁盤中。當(dāng)數(shù)據(jù)庫(kù)重啟時(shí)，該計(jì)數(shù)器會(huì)通過(guò)下面這種方式初始化。

SELECT MAX(ai_col) FROM table_name FOR UPDATE; 

MySQL 8.0的解決思路

將自增主鍵的計(jì)數(shù)器持久化到redo log中。每次計(jì)數(shù)器發(fā)生改變，都會(huì)將其寫入到redo log中。如果數(shù)據(jù)庫(kù)發(fā)生重啟，InnoDB會(huì)根據(jù)redo log中的計(jì)數(shù)器信息來(lái)初始化其內(nèi)存值。為了盡量減小對(duì)系統(tǒng)性能的影響，計(jì)數(shù)器寫入到redo log中，并不會(huì)馬上刷新。具體可參考：https://dev.mysql.com/worklog/task/?id=6204

因自增主鍵沒(méi)有持久化而出現(xiàn)問(wèn)題的常見(jiàn)場(chǎng)景：

1. 業(yè)務(wù)將自增主鍵作為業(yè)務(wù)主鍵，同時(shí)，業(yè)務(wù)上又要求主鍵不能重復(fù)。

2. 數(shù)據(jù)會(huì)被歸檔。在歸檔的過(guò)程中有可能會(huì)產(chǎn)生主鍵沖突。

所以，強(qiáng)烈建議不要使用自增主鍵作為業(yè)務(wù)主鍵。刨除這兩個(gè)場(chǎng)景，其實(shí)，自增主鍵沒(méi)有持久化的問(wèn)題并不是很大，遠(yuǎn)沒(méi)有想象中的”臭名昭著“。

最后，給出一個(gè)歸檔場(chǎng)景下的解決方案，

創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，根據(jù)table2（歸檔表）自增主鍵的最大值來(lái)初始化table1（在線表）。這個(gè)存儲(chǔ)過(guò)程可放到init_file參數(shù)指定的文件中，該文件中的SQL會(huì)在數(shù)據(jù)庫(kù)啟動(dòng)時(shí)執(zhí)行。

DELIMITER ;;
CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
set @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');
 prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
 set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');
 prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
IF @max1  @max2 THEN
 set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;
SELECT 'updated' as `status`;
else
SELECT 'no update needed' as `status`;
END IF;
END ;;
DELIMITER ;

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。