一

提出這個問題，是因為在工作中發(fā)現(xiàn) mysql 中的 user 表的 id 默認(rèn)是自增的，但是數(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ā)生重啟，表的自增值可以恢復(fù)為 MySQL 重啟前的值”，具體情況是：

• 在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里。每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id) + 1 作為這個表當(dāng)前的自增值。
• 在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時候依靠 redo log 恢復(fù)重啟之前的值。

三 自增值修改機(jī)制

在 MySQL 里面，如果字段 id 被定義為 AUTO_INCREMENT，在插入一行數(shù)據(jù)的時候，自增值的行為如下：

• 如果插入數(shù)據(jù)時 id 字段指定為 0、null 或未指定值，那么就把這個表當(dāng)前的 AUTO_INCREMENT 值填到自增字段；
• 如果插入數(shù)據(jù)時 id 字段指定了具體的值，就直接使用語句里指定的值。

根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系，自增值的變更結(jié)果也會有所不同。假設(shè)，某次要插入的值是 X，當(dāng)前的自增值是 Y。

• 如果 X<Y，那么這個表的自增值不變；
• 如果 X≥Y，就需要把當(dāng)前自增值修改為新的自增值。

新的自增值生成算法是：從 auto_increment_offset 開始，以 auto_increment_increment 為步長，持續(xù)疊加，直到找到第一個大于 X 的值，作為新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是兩個系統(tǒng)參數(shù)，分別用來表示自增的初始值和步長，默認(rèn)值都是 1。

四 自增值修改時機(jī)

insert into user values(null, '張三'); 

1 當(dāng)執(zhí)行上述 SQL 時，執(zhí)行器調(diào)用 InnoDB 引擎接口寫入一行，傳入的這一行的值是 (0,"張三")；

2InnoDB 發(fā)現(xiàn) SQL 沒有指定自增 id 的值，獲取 user 表當(dāng)前的自增值 2；

3將傳入的行的值改成 (2,"張三");

4將表的自增值改成 3；

5 繼續(xù)執(zhí)行插入數(shù)據(jù)操作。

五 導(dǎo)致自增值不連續(xù)的原因

5.1唯一鍵沖突

假設(shè)執(zhí)行 SQL 的時候 user 表 id = 10，此時在內(nèi)存中的自增 id 為11，此時發(fā)生唯一鍵沖突寫庫失敗，則 user表沒有 id = 10 這條記錄，之后 id 從11開始寫入，因此 id 是不連續(xù)的。

5.2 事務(wù)回滾

假設(shè)同時需要對 user、staff 表進(jìn)行寫庫操作，執(zhí)行 SQL 的時候 user 表 id = 10，此時在內(nèi)存中的自增 id 為11；staff 表 id = 20，此時內(nèi)存中的自增 id 為21，一旦事務(wù)執(zhí)行失敗，事務(wù)回滾，寫庫失敗，則 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ù)都是上一次的兩倍。

假設(shè)批量往 user 表中寫入四條記錄，則這四條記錄將分為三次申請id，

第一次分配到 id = 1，第二次分配到 id = 2、3，第三次分配到 id = 4、5、6、7，當(dāng)批量寫入四條記錄之后，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)文章希望大家以后多多支持本站！

海外服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。