MySQL自增主鍵一定是連續(xù)的嗎？

測(cè)試環(huán)境：

MySQL版本：8.0

數(shù)據(jù)庫(kù)表：T （主鍵id，唯一索引c，普通字段d）

如果你的業(yè)務(wù)設(shè)計(jì)依賴于自增主鍵的連續(xù)性，這個(gè)設(shè)計(jì)假設(shè)自增主鍵是連續(xù)的。但實(shí)際上，這樣的假設(shè)是錯(cuò)的，因?yàn)樽栽鲋麈I不能保證連續(xù)遞增。

一、自增值的屬性特征：

1. 自增主鍵值是存儲(chǔ)在哪的？

MySQL5.7版本

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

MySQL8.0之后版本

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

可以通過(guò)看表詳情查看當(dāng)前自增值，以及查看表參數(shù)詳情AUTO_INCREMENT值(AUTO_INCREMENT就是當(dāng)前數(shù)據(jù)表的自增值)

2. 自增主鍵值的修改機(jī)制？

在表t中，我定義了主鍵id為自增值，在插入一行數(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

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

二、新增語(yǔ)句自增主鍵是如何變化的：

我們執(zhí)行以下SQL語(yǔ)句，來(lái)觀察自增主鍵是如何進(jìn)行變化的

insertintotvalues(null,1,1);

流程圖如下所示

流程步驟：

AUTO_INCREMENT=1（表示下一次插入數(shù)據(jù)時(shí)，如果需要自動(dòng)生成自增值，會(huì)生成 id=1。）

insert into t values(null, 1, 1)（執(zhí)行器調(diào)用 InnoDB 引擎接口寫(xiě)入一行，傳入的這一行的值是 (0,1,1)）

get AUTO_INCREMENT=1（InnoDB 發(fā)現(xiàn)用戶沒(méi)有指定自增 id 的值，獲取表 t 當(dāng)前的自增值 1 ）

AUTO_INCREMENT=2 insert into t values(1, 1, 1)（將傳入的行的值改成 (1,1,1)，并把自增值改為2）

insert （1，1，1)執(zhí)行插入操作，至此流程結(jié)束

大家可以發(fā)現(xiàn)，在這個(gè)流程當(dāng)中是先進(jìn)行自增值的+1，在進(jìn)行新增語(yǔ)句的執(zhí)行的。大家可以發(fā)現(xiàn)這個(gè)操作并沒(méi)有進(jìn)行原子操作，如果SQL語(yǔ)句執(zhí)行失敗，那么自增是不是就不會(huì)連續(xù)了呢？

三、自增主鍵值不連續(xù)情況：（唯一主鍵沖突）

當(dāng)我執(zhí)行以下SQL語(yǔ)句時(shí)

insertintotvalues(null,1,1);

第一次我們可以進(jìn)行新增成功，根據(jù)自增值的修改機(jī)制。如果插入數(shù)據(jù)時(shí) id 字段指定為 0、null 或未指定值，那么就把這個(gè)表當(dāng)前的AUTO_INCREMENT值填到自增字段；

當(dāng)我們第二次在執(zhí)行以下SQL語(yǔ)句時(shí)，就會(huì)出現(xiàn)錯(cuò)誤。因?yàn)槲覀儽碇衏字段是唯一索引，會(huì)出現(xiàn)Duplicate key error錯(cuò)誤導(dǎo)致新增失敗。

例如：

AUTO_INCREMENT=2（表示下一次插入數(shù)據(jù)時(shí)，如果需要自動(dòng)生成自增值，會(huì)生成 id=2。）

insert into t values(null, 1, 1)（執(zhí)行器調(diào)用 InnoDB 引擎接口寫(xiě)入一行，傳入的這一行的值是 (0,1,1)）

get AUTO_INCREMENT=2（InnoDB 發(fā)現(xiàn)用戶沒(méi)有指定自增 id 的值，獲取表 t 當(dāng)前的自增值 2 ）

AUTO_INCREMENT=3 insert into t values(2, 1, 1)（將傳入的行的值改成 (2,1,1)，并把自增值改為3）

insert （2，1，1)執(zhí)行插入操作，由于已經(jīng)存在 c=1 的記錄，所以報(bào)Duplicate key error，語(yǔ)句返回。

可以看到，這個(gè)表的自增值改成 3，是在真正執(zhí)行插入數(shù)據(jù)的操作之前。這個(gè)語(yǔ)句真正執(zhí)行的時(shí)候，因?yàn)榕龅轿ㄒ绘I c 沖突，所以 id=2 這一行并沒(méi)有插入成功，但也沒(méi)有將自增值再改回去。所以，在這之后，再插入新的數(shù)據(jù)行時(shí)，拿到的自增 id 就是 3。也就是說(shuō)，出現(xiàn)了自增主鍵不連續(xù)的情況。

四、自增主鍵值不連續(xù)情況：（事務(wù)回滾）

其實(shí)事務(wù)回滾原理也和上面一樣，都是因?yàn)楫惓?dǎo)致新增失敗，但是自增值沒(méi)有進(jìn)行回退。

五、自增主鍵值不連續(xù)情況：（批量插入）

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

語(yǔ)句執(zhí)行過(guò)程中，第一次申請(qǐng)自增 id，會(huì)分配 1 個(gè)；

1 個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增 id，會(huì)分配 2 個(gè)；

2 個(gè)用完以后，還是這個(gè)語(yǔ)句， 第三次申請(qǐng)自增 id，會(huì)分配 4 個(gè)；

依此類推，同一個(gè)語(yǔ)句去申請(qǐng)自增 id，每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍。

執(zhí)行以下SQL語(yǔ)句（在表t中先新增了4條數(shù)據(jù)，在創(chuàng)建表tt把表t數(shù)據(jù)進(jìn)行批量新增）

insertintotvalues(null,1,1);
insertintotvalues(null,2,2);
insertintotvalues(null,3,3);
insertintotvalues(null,4,4);
createtablettliket;
insertintott(c,d)selectc,dfromt;

insertintottvalues(null,5,5);

第一次申請(qǐng)到了 id=1，第二次被分配了 id=2 和 id=3， 第三次被分配到 id=4 到 id=7。當(dāng)我們?cè)賵?zhí)行insert into t2 values(null, 5,5)，實(shí)際上插入的數(shù)據(jù)就是（8,5,5)，出現(xiàn)了自增主鍵不連續(xù)的情況。

六、自增主鍵值的優(yōu)化

1.什么是自增鎖

自增鎖是一種比擬非凡的表級(jí)鎖。并且在事務(wù)向蘊(yùn)含了AUTO_INCREMENT列的表中新增數(shù)據(jù)時(shí)就會(huì)去持有自增鎖，假如事務(wù) A 正在做這個(gè)操作，如果另一個(gè)事務(wù) B 嘗試執(zhí)行 INSERT語(yǔ)句，事務(wù) B 會(huì)被阻塞住，直到事務(wù) A 開(kāi)釋自增鎖。

2.自增鎖有哪些優(yōu)化

在 MySQL 5.0 版本的時(shí)候，自增鎖的范圍是語(yǔ)句級(jí)別。也就是說(shuō)，如果一個(gè)語(yǔ)句申請(qǐng)了一個(gè)表自增鎖，這個(gè)鎖會(huì)等語(yǔ)句執(zhí)行結(jié)束以后才釋放。顯然，這樣設(shè)計(jì)會(huì)影響并發(fā)度。在MySQL 5.1.22 版本引入了一個(gè)新策略，新增參數(shù)innodb_autoinc_lock_mode，默認(rèn)值是 1。

傳統(tǒng)模式（Traditional）

這個(gè)參數(shù)的值被設(shè)置為 0 時(shí)，表示采用之前 MySQL 5.0 版本的策略，即語(yǔ)句執(zhí)行結(jié)束后才釋放鎖；

傳統(tǒng)模式他可以保證數(shù)據(jù)一致性，但是如果有多個(gè)事務(wù)并發(fā)的執(zhí)行 INSERT 操作，AUTO-INC的存在會(huì)使得 MySQL 的性能略有降落，因?yàn)橥瑫r(shí)只能執(zhí)行一條 INSERT 語(yǔ)句。

間斷模式（Consecutive）

這個(gè)參數(shù)的值被設(shè)置為 1 時(shí)：普通 insert 語(yǔ)句，自增鎖在申請(qǐng)之后就馬上釋放；類似insert … select這樣的批量插入數(shù)據(jù)的語(yǔ)句，自增鎖還是要等語(yǔ)句結(jié)束后才被釋放；

間斷模式他可以保證數(shù)據(jù)一致性，但是如果有多個(gè)事務(wù)并發(fā)的執(zhí)行 INSERT 批量操作時(shí)，就會(huì)進(jìn)行鎖等待狀態(tài)。如果我們業(yè)務(wù)插入數(shù)據(jù)量很大時(shí)，這個(gè)時(shí)候MySQL的性能就會(huì)大大下降。

穿插模式（Interleaved）

這個(gè)參數(shù)的值被設(shè)置為 2 時(shí)，所有的申請(qǐng)自增主鍵的動(dòng)作都是申請(qǐng)后就釋放鎖。

穿插模式他沒(méi)有進(jìn)行任何的上鎖設(shè)置。在一定情況下是保證了MySQL的性能，但是他無(wú)法保證數(shù)據(jù)的一致性。如果我們?cè)诖┎迥Ｊ较逻M(jìn)行主從復(fù)制時(shí)，如果你的binlog格式不是row格式，主從復(fù)制就會(huì)出現(xiàn)不一致。

七、MySQL8.0做了哪些優(yōu)化

在MySQL8.0之后版本，已經(jīng)默認(rèn)設(shè)置為innodb_autoinc_lock_mode=2，binlog_format=row.。這樣更有利與我們?cè)趇nsert … select這種批量插入數(shù)據(jù)的場(chǎng)景時(shí)，既能提升并發(fā)性，又不會(huì)出現(xiàn)數(shù)據(jù)一致性問(wèn)題。





審核編輯：劉清