MySQL8新特性:自增主鍵的持久化詳解
前言
自增主鍵沒有持久化是個比較早的bug,這點從其在官方bug網(wǎng)站的id號也可看出(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值時,并沒有重用被刪除的3,而是分配了4。
刪除id為4的記錄,重啟數(shù)據(jù)庫,重新插入一個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,按照重啟前的操作邏輯,此處應該分配5啊。
這就是自增主鍵沒有持久化的bug。究其原因,在于自增主鍵的分配,是由InnoDB數(shù)據(jù)字典內部一個計數(shù)器來決定的,而該計數(shù)器只在內存中維護,并不會持久化到磁盤中。當數(shù)據(jù)庫重啟時,該計數(shù)器會通過下面這種方式初始化。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
MySQL 8.0的解決思路
將自增主鍵的計數(shù)器持久化到redo log中。每次計數(shù)器發(fā)生改變,都會將其寫入到redo log中。如果數(shù)據(jù)庫發(fā)生重啟,InnoDB會根據(jù)redo log中的計數(shù)器信息來初始化其內存值。為了盡量減小對系統(tǒng)性能的影響,計數(shù)器寫入到redo log中,并不會馬上刷新。具體可參考:https://dev.mysql.com/worklog/task/?id=6204
因自增主鍵沒有持久化而出現(xiàn)問題的常見場景:
1. 業(yè)務將自增主鍵作為業(yè)務主鍵,同時,業(yè)務上又要求主鍵不能重復。
2. 數(shù)據(jù)會被歸檔。在歸檔的過程中有可能會產生主鍵沖突。
所以,強烈建議不要使用自增主鍵作為業(yè)務主鍵。刨除這兩個場景,其實,自增主鍵沒有持久化的問題并不是很大,遠沒有想象中的”臭名昭著“。
最后,給出一個歸檔場景下的解決方案,
創(chuàng)建一個存儲過程,根據(jù)table2(歸檔表)自增主鍵的最大值來初始化table1(在線表)。這個存儲過程可放到init_file參數(shù)指定的文件中,該文件中的SQL會在數(shù)據(jù)庫啟動時執(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 ;
總結
以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。
相關文章
mysql-connector-java與mysql版本的對應關系說明
這篇文章主要介紹了mysql-connector-java與mysql版本的對應關系說明,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-02-02mysql 獲取昨天日期、今天日期、明天日期以及前一個小時和后一個小時的時間
這篇文章主要介紹了mysql 獲取昨天日期、今天日期、明天日期以及前一個小時和后一個小時的時間,需要的朋友可以參考下2018-01-01Linux7.6二進制安裝Mysql8.0.27詳細操作步驟
大家好,本篇文章主要講的是Linux7.6二進制安裝Mysql8.0.27詳細操作步驟,感興趣的同學快來看一看吧,希望對你起到幫助2021-11-11關于Mysql-connector-java驅動版本問題總結
這篇文章主要介紹了Mysql-connector-java驅動版本問題,本文給大家介紹的很詳細,通過原因說明問題小結個人建議給大家展示的很好,需要的朋友可以參考下2021-06-06