面試官：MySQL 自增主鍵一定是連續的嗎？大部分人都會答錯！-有解無憂

測驗環境：

MySQL版本：8.0

資料庫表：T （主鍵id，唯一索引c，普通欄位d）

如果你的業務設計依賴于自增主鍵的連續性，這個設計假設自增主鍵是連續的，但實際上，這樣的假設是錯的，因為自增主鍵不能保證連續遞增，

推薦一個開源免費的 Spring Boot 實戰專案：

https://github.com/javastacks/spring-boot-best-practice

一、自增值的屬性特征：

1. 自增主鍵值是存盤在哪的？

MySQL5.7版本

在 MySQL 5.7 及之前的版本，自增值保存在記憶體里，并沒有持久化，每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個表當前的自增值，

MySQL8.0之后版本

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

可以通過看表詳情查看當前自增值，以及查看表引數詳情AUTO_INCREMENT值(AUTO_INCREMENT就是當前資料表的自增值)

2. 自增主鍵值的修改機制？

在表t中，我定義了主鍵id為自增值，在插入一行資料的時候，自增值的行為如下：

如果插入資料時 id 欄位指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增欄位；
如果插入資料時 id 欄位指定了具體的值，就直接使用陳述句里指定的值，

根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同，假設，某次要插入的值是 X，當前的自增值是 Y，

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

二、新增陳述句自增主鍵是如何變化的：

我們執行以下SQL陳述句，來觀察自增主鍵是如何進行變化的

insert into t values(null, 1, 1);

流程圖如下所示

流程步驟：

AUTO_INCREMENT=1 （表示下一次插入資料時，如果需要自動生成自增值，會生成 id=1，）
insert into t values(null, 1, 1) （執行器呼叫 InnoDB 引擎介面寫入一行，傳入的這一行的值是 (0,1,1)）
get AUTO_INCREMENT=1 （InnoDB 發現用戶沒有指定自增 id 的值，獲取表 t 當前的自增值 1 ）
AUTO_INCREMENT=2 insert into t values(1, 1, 1) （將傳入的行的值改成 (1,1,1)，并把自增值改為2）
insert （1，1，1) 執行插入操作，至此流程結束

大家可以發現，在這個流程當中是先進行自增值的+1，在進行新增陳述句的執行的，大家可以發現這個操作并沒有進行原子操作，如果SQL陳述句執行失敗，那么自增是不是就不會連續了呢？

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

當我執行以下SQL陳述句時

insert into t values(null, 1, 1);

第一次我們可以進行新增成功，根據自增值的修改機制，如果插入資料時 id 欄位指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增欄位；

當我們第二次在執行以下SQL陳述句時，就會出現錯誤，因為我們表中c欄位是唯一索引，會出現Duplicate key error錯誤導致新增失敗，

例如：

AUTO_INCREMENT=2 （表示下一次插入資料時，如果需要自動生成自增值，會生成 id=2，）
insert into t values(null, 1, 1) （執行器呼叫 InnoDB 引擎介面寫入一行，傳入的這一行的值是 (0,1,1)）
get AUTO_INCREMENT=2 （InnoDB 發現用戶沒有指定自增 id 的值，獲取表 t 當前的自增值 2 ）
AUTO_INCREMENT=3 insert into t values(2, 1, 1) （將傳入的行的值改成 (2,1,1)，并把自增值改為3）
insert （2，1，1) 執行插入操作，由于已經存在 c=1 的記錄，所以報 Duplicate key error，陳述句回傳，

可以看到，這個表的自增值改成 3，是在真正執行插入資料的操作之前，這個陳述句真正執行的時候，因為碰到唯一鍵 c 沖突，所以 id=2 這一行并沒有插入成功，但也沒有將自增值再改回去，所以，在這之后，再插入新的資料行時，拿到的自增 id 就是 3，也就是說，出現了自增主鍵不連續的情況，

四、自增主鍵值不連續情況：（事務回滾）

其實事務回滾原理也和上面一樣，都是因為例外導致新增失敗，但是自增值沒有進行回退，

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

批量插入資料的陳述句，MySQL 有一個批量申請自增 id 的策略：

陳述句執行程序中，第一次申請自增 id，會分配 1 個；
1 個用完以后，這個陳述句第二次申請自增 id，會分配 2 個；
2 個用完以后，還是這個陳述句，第三次申請自增 id，會分配 4 個；
依此類推，同一個陳述句去申請自增 id，每次申請到的自增 id 個數都是上一次的兩倍，

執行以下SQL陳述句（在表t中先新增了4條資料，在創建表tt把表t資料進行批量新增）

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table tt like t;
insert into tt(c,d) select c,d from t;

insert into tt values(null, 5,5);

第一次申請到了 id=1，第二次被分配了 id=2 和 id=3，第三次被分配到 id=4 到 id=7，當我們再執行 insert into t2 values(null, 5,5)，實際上插入的資料就是（8,5,5)，出現了自增主鍵不連續的情況，

六、自增主鍵值的優化

1.什么是自增鎖

自增鎖是一種比擬非凡的表級鎖，并且在事務向蘊含了 AUTO_INCREMENT 列的表中新增資料時就會去持有自增鎖，假如事務 A 正在做這個操作，如果另一個事務 B 嘗試執行 INSERT陳述句，事務 B 會被阻塞住，直到事務 A 開釋自增鎖，

2.自增鎖有哪些優化

在 MySQL 5.0 版本的時候，自增鎖的范圍是陳述句級別，也就是說，如果一個陳述句申請了一個表自增鎖，這個鎖會等陳述句執行結束以后才釋放，顯然，這樣設計會影響并發度，在MySQL 5.1.22 版本引入了一個新策略，新增引數 innodb_autoinc_lock_mode，默認值是 1，

傳統模式（Traditional）

這個引數的值被設定為 0 時，表示采用之前 MySQL 5.0 版本的策略，即陳述句執行結束后才釋放鎖；

傳統模式他可以保證資料一致性，但是如果有多個事務并發的執行 INSERT 操作，AUTO-INC的存在會使得 MySQL 的性能略有降落，因為同時只能執行一條 INSERT 陳述句，

間斷模式（Consecutive）

這個引數的值被設定為 1 時：普通 insert 陳述句，自增鎖在申請之后就馬上釋放；類似 insert … select 這樣的批量插入資料的陳述句，自增鎖還是要等陳述句結束后才被釋放；

間斷模式他可以保證資料一致性，但是如果有多個事務并發的執行 INSERT 批量操作時，就會進行鎖等待狀態，如果我們業務插入資料量很大時，這個時候MySQL的性能就會大大下降，

穿插模式（Interleaved）

這個引數的值被設定為 2 時，所有的申請自增主鍵的動作都是申請后就釋放鎖，

穿插模式他沒有進行任何的上鎖設定，在一定情況下是保證了MySQL的性能，但是他無法保證資料的一致性，如果我們在穿插模式下進行主從復制時，如果你的binlog格式不是row格式，主從復制就會出現不一致，

七、MySQL8.0做了哪些優化

在MySQL8.0之后版本，已經默認設定為 innodb_autoinc_lock_mode=2 ， binlog_format=row.，這樣更有利與我們在 insert … select 這種批量插入資料的場景時，既能提升并發性，又不會出現資料一致性問題，

著作權宣告：本文為CSDN博主「又欠」的原創文章，遵循CC 4.0 BY-SA著作權協議，轉載請附上原文出處鏈接及本宣告，原文鏈接：https://blog.csdn.net/qq_48157004/article/details/128356734

近期熱文推薦：

1.1,000+ 道 Java面試題及答案整理(2022最新版)

2.勁爆！Java 協程要來了，，，

3.Spring Boot 2.x 教程，太全了！

4.別再寫滿屏的爆爆爆炸類了，試試裝飾器模式，這才是優雅的方式！！

5.《Java開發手冊（嵩山版）》最新發布，速速下載！

覺得不錯，別忘了隨手點贊+轉發哦！

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/555893.html

標籤：Java

上一篇：【爬蟲案例】用Python爬大麥網任意城市的近期演出活動！

下一篇：返回列表