目錄
- 一 前言
- 二 自增值存儲說明
- 三 自增值修改機制
- 四 自增值修改時機
- 五 導致自增值不連續(xù)的原因
- 5.1 唯一鍵沖突
- 5.2 事務回滾
- 5.3 批量寫庫操作
- 六 參考文檔
一 前言
提出這個問題,是因為在工作中發(fā)現(xiàn) mysql 中的 user 表的 id 默認是自增的,但是數(shù)據(jù)庫存儲的結(jié)果卻不是連續(xù)的。
user 表結(jié)構(gòu):
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '遞增id',
`name` varchar(20),
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`id`),UNIQUE KEY `idx_name` (`name`))
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='user表'
user 表存儲:
二 自增值存儲說明
1.1 MyISAM 引擎的自增值保存在數(shù)據(jù)文件中。
1.2 InnoDB 引擎的自增值,其實是保存在了內(nèi)存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才實現(xiàn)了“如果發(fā)生重啟,表的自增值可以恢復為 MySQL 重啟前的值”,具體情況是:
- 在 MySQL 5.7 及之前的版本,自增值保存在內(nèi)存里。每次重啟后,第一次打開表的時候,都會去找自增值的最大值 max(id),然后將 max(id) + 1 作為這個表當前的自增值。
- 在 MySQL 8.0 版本,將自增值的變更記錄在了 redo log 中,重啟的時候依靠 redo log 恢復重啟之前的值。
三 自增值修改機制
在 MySQL 里面,如果字段 id 被定義為 AUTO_INCREMENT,在插入一行數(shù)據(jù)的時候,自增值的行為如下:
- 如果插入數(shù)據(jù)時 id 字段指定為 0、null 或未指定值,那么就把這個表當前的 AUTO_INCREMENT 值填到自增字段;
- 如果插入數(shù)據(jù)時 id 字段指定了具體的值,就直接使用語句里指定的值。
根據(jù)要插入的值和當前自增值的大小關(guān)系,自增值的變更結(jié)果也會有所不同。假設,某次要插入的值是 X,當前的自增值是 Y。
- 如果 XY,那么這個表的自增值不變;
- 如果 X≥Y,就需要把當前自增值修改為新的自增值。
新的自增值生成算法是:從 auto_increment_offset 開始,以 auto_increment_increment 為步長,持續(xù)疊加,直到找到第一個大于 X 的值,作為新的自增值。其中,auto_increment_offset 和 auto_increment_increment 是兩個系統(tǒng)參數(shù),分別用來表示自增的初始值和步長,默認值都是 1。
四 自增值修改時機
insert into user values(null, '張三');
1 當執(zhí)行上述 SQL 時,執(zhí)行器調(diào)用 InnoDB 引擎接口寫入一行,傳入的這一行的值是 (0,"張三");
2 InnoDB 發(fā)現(xiàn) SQL 沒有指定自增 id 的值,獲取 user 表當前的自增值 2;
3 將傳入的行的值改成 (2,"張三");
4 將表的自增值改成 3;
5 繼續(xù)執(zhí)行插入數(shù)據(jù)操作。
五 導致自增值不連續(xù)的原因
5.1 唯一鍵沖突
假設執(zhí)行 SQL 的時候 user 表 id = 10,此時在內(nèi)存中的自增 id 為11,此時發(fā)生唯一鍵沖突寫庫失敗,則 user 表沒有 id = 10 這條記錄,之后 id 從11開始寫入,因此 id 是不連續(xù)的。
5.2 事務回滾
假設同時需要對 user、staff 表進行寫庫操作,執(zhí)行 SQL 的時候 user 表 id = 10,此時在內(nèi)存中的自增 id 為11;staff 表 id = 20,此時內(nèi)存中的自增 id 為21,一旦事務執(zhí)行失敗,事務回滾,寫庫失敗,則 user 表沒有 id = 10 這條記錄,staff 表沒有 id = 20 這條記錄,user 表從11開始寫入,staff 表從21開始寫入,如此產(chǎn)生 id 不連續(xù)的現(xiàn)象。
5.3 批量寫庫操作
對于批量插入數(shù)據(jù)的語句,MySQL 有一個批量申請自增 id 的策略:
1. 語句執(zhí)行過程中,第一次申請自增 id,會分配 1 個;
2. 1 個用完以后,這個語句第二次申請自增 id,會分配 2 個;
3. 2 個用完以后,還是這個語句,第三次申請自增 id,會分配 4 個;
依此類推,同一個語句去申請自增 id,每次申請到的自增 id 個數(shù)都是上一次的兩倍。
假設批量往 user 表中寫入四條記錄,則這四條記錄將分為三次申請id,
第一次分配到 id = 1,第二次分配到 id = 2、3 ,第三次分配到 id = 4、5、6、7,當批量寫入四條記錄之后,id = 1、2、3、4將會入庫,但是 id = 5、6、7就被廢棄了,下一個 id 從8開始。
六 參考文檔
https://time.geekbang.org/column/intro/139
到此這篇關(guān)于為什么mysql自增主鍵不是連續(xù)的 的文章就介紹到這了,更多相關(guān)mysql自增主鍵連續(xù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
您可能感興趣的文章:- MySQL中的主鍵以及設置其自增的用法教程
- mysql修改自增長主鍵int類型為char類型示例
- MySQL的自增ID(主鍵) 用完了的解決方法
- 淺談MySQL中的自增主鍵用完了怎么辦
- MySQL8新特性:自增主鍵的持久化詳解
- mysql非主鍵自增長用法實例分析
- Mysql自增主鍵id不是以此逐級遞增的處理
- 詳解MySQL自增主鍵的實現(xiàn)