面試官:MySQL自增主鍵為什么不是連續(xù)的?

程序員的成長(zhǎng)之路互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享? 關(guān)注

閱讀本文大概需要 3?分鐘。

來(lái)自：blog.csdn.net/jack1liu/article/details/99699201

一 前言

提出這個(gè)問(wèn)題，是因?yàn)樵诠ぷ髦邪l(fā)現(xiàn) mysql 中的 user 表的 id 默認(rèn)是自增的，但是數(shù)據(jù)庫(kù)存儲(chǔ)的結(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)建時(shí)間',?
?`update_time`?datetime?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',?
?PRIMARY?KEY?(`id`),UNIQUE?KEY?`idx_name`?(`name`))?
ENGINE=InnoDB?AUTO_INCREMENT=1?DEFAULT?CHARSET=utf8mb4?COMMENT='user表'

user 表存儲(chǔ)：

二 自增值存儲(chǔ)說(shuō)明

1.1.MyISAM 引擎的自增值保存在數(shù)據(jù)文件中。1.2.InnoDB 引擎的自增值，其實(shí)是保存在了內(nèi)存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實(shí)現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復(fù)為 MySQL 重啟前的值”，具體情況是：

• 在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里。每次重啟后，第一次打開(kāi)表的時(shí)候，都會(huì)去找自增值的最大值?max(id)，然后將?max(id) + 1?作為這個(gè)表當(dāng)前的自增值。

• 在 MySQL 8.0 版本，將自增值的變更記錄在了?redo log?中，重啟的時(shí)候依靠?redo log恢復(fù)重啟之前的值。

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

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

• 如果插入數(shù)據(jù)時(shí) id 字段指定為 0、null 或未指定值，那么就把這個(gè)表當(dāng)前的?AUTO_INCREMENT值填到自增字段；

• 如果插入數(shù)據(jù)時(shí) id 字段指定了具體的值，就直接使用語(yǔ)句里指定的值。

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

• 如果 X<Y，那么這個(gè)表的自增值不變；

• 如果 X≥Y，就需要把當(dāng)前自增值修改為新的自增值。

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

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

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

1. 當(dāng)執(zhí)行上述 SQL 時(shí)，執(zhí)行器調(diào)用 InnoDB 引擎接口寫(xiě)入一行，傳入的這一行的值是?(0,"張三")；
2. InnoDB 發(fā)現(xiàn) SQL 沒(méi)有指定自增 id 的值，獲取 user 表當(dāng)前的自增值 2；
3. 將傳入的行的值改成?(2,"張三");
4. 將表的自增值改成 3；
5. 繼續(xù)執(zhí)行插入數(shù)據(jù)操作。

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

5.1 唯一鍵沖突

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

5.2 事務(wù)回滾

假設(shè)同時(shí)需要對(duì) user、staff 表進(jìn)行寫(xiě)庫(kù)操作，執(zhí)行 SQL 的時(shí)候 user 表?id = 10，此時(shí)在內(nèi)存中的自增 id 為11；staff 表?id = 20，此時(shí)內(nèi)存中的自增 id 為21，一旦事務(wù)執(zhí)行失敗，事務(wù)回滾，寫(xiě)庫(kù)失敗，則 user 表沒(méi)有?id = 10?這條記錄，staff 表沒(méi)有?id = 20?這條記錄，user 表從11開(kāi)始寫(xiě)入，staff 表從21開(kāi)始寫(xiě)入，如此產(chǎn)生 id 不連續(xù)的現(xiàn)象。

5.3 批量寫(xiě)庫(kù)操作

對(duì)于批量插入數(shù)據(jù)的語(yǔ)句，MySQL 有一個(gè)批量申請(qǐng)自增 id 的策略：

1. 語(yǔ)句執(zhí)行過(guò)程中，第一次申請(qǐng)自增 id，會(huì)分配 1 個(gè)；
2. 1 個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增 id，會(huì)分配 2 個(gè)；
3. 2 個(gè)用完以后，還是這個(gè)語(yǔ)句，第三次申請(qǐng)自增 id，會(huì)分配 4 個(gè)；

依此類推，同一個(gè)語(yǔ)句去申請(qǐng)自增 id，每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍。假設(shè)批量往 user 表中寫(xiě)入四條記錄，則這四條記錄將分為三次申請(qǐng)id，第一次分配到?id = 1，第二次分配到?id = 2、3?，第三次分配到?id = 4、5、6、7，當(dāng)批量寫(xiě)入四條記錄之后，id = 1、2、3、4將會(huì)入庫(kù)，但是?id = 5、6、7就被廢棄了，下一個(gè) id 從8開(kāi)始。

六 參考文檔

• https://time.geekbang.org/column/intro/139

<END>

推薦閱讀：

Java使用 try catch會(huì)影響性能？
Java 自帶的性能調(diào)優(yōu)神器??！

    
        互聯(lián)網(wǎng)初中高級(jí)大廠面試題(9個(gè)G)
      
    

內(nèi)容包含Java基礎(chǔ)、JavaWeb、MySQL性能優(yōu)化、JVM、鎖、百萬(wàn)并發(fā)、消息隊(duì)列、高性能緩存、反射、Spring全家桶原理、微服務(wù)、Zookeeper......等技術(shù)棧！

?戳閱讀原文領(lǐng)取！ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??朕已閱?