共計 3241 個字符，預計需要花費 9 分鐘才能閱讀完成。
這篇文章主要介紹了 MySQL 中自增主鍵的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。
一、自增值保存在哪兒？
不同的引擎對于自增值的保存策略不同
1.MyISAM 引擎的自增值保存在數據文件中
2.InnoDB 引擎的自增值，在 MySQL5.7 及之前的版本，自增值保存在內存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id)+ 步長作為這個表當前的自增值
select max(ai_col) from table_name for update;在 MySQL8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時候依靠 redo log 恢復重啟之前的值
二、自增值修改機制
如果字段 id 被定義為 AUTO_INCREMENT，在插入一行數據的時候，自增值的行為如下：
1. 如果插入數據時 id 字段指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增字段
2. 如果插入數據時 id 字段指定了具體的值，就直接使用語句里指定的值
假設，某次要插入的值是 X，當前的自增值是 Y
1. 如果 X Y，那么這個表的自增值不變
2. 如果 X =Y，就需要把當前自增值修改為新的自增值
新的自增值生成算法是：從 auto_increment_offset（初始值）開始，以 auto_increment_increment（步長）為步長，持續疊加，直到找到第一個大于 X 的值，作為新的自增值
三、自增值的修改時機
創建一個表 t，其中 id 是自增主鍵字段、c 是唯一索引，建表語句如下：
CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)) ENGINE=InnoDB;假設，表 t 里面已經有了 (1,1,1) 這條記錄，這時再執行一條插入數據命令：
insert into t values(null, 1, 1);執行流程如下：
1. 執行器調用 InnoDB 引擎接口寫入一行，傳入的這一行的值是(0,1,1)
2.InnoDB 發現用于沒有指定自增 id 的值，獲取表 t 當前的自增值 2
3. 將傳入的行的值改成(2,1,1)
4. 將表的自增值改成 3
5. 繼續執行插入數據操作，由于已經存在 c = 1 的記錄，所以報 Duplicate key error（唯一鍵沖突），語句返回
對應的執行流程圖如下：
在這之后，再插入新的數據行時，拿到的自增 id 就是 3。出現了自增主鍵不連續的情況
唯一鍵沖突和事務回滾都會導致自增主鍵 id 不連續的情況
四、自增鎖的優化
自增 id 鎖并不是一個事務鎖，而是每次申請完就馬上釋放，以便允許別的事務再申請
但在 MySQL5.0 版本的時候，自增鎖的范圍是語句級別。也就是說，如果一個語句申請了一個表自增鎖，這個鎖會等語句執行結束以后才釋放
MySQL5.1.22 版本引入了一個新策略，新增參數 innodb_autoinc_lock_mode，默認值是 1
1. 這個參數設置為 0，表示采用之前 MySQL5.0 版本的策略，即語句執行結束后才釋放鎖
2. 這個參數設置為 1
普通 insert 語句，自增鎖在申請之后就馬上釋放
類似 insert … select 這樣的批量插入數據的語句，自增鎖還是要等語句結束后才被釋放
3. 這個參數設置為 2，所有的申請自增主鍵的動作都是申請后就釋放鎖
為了數據的一致性，默認設置為 1
如果 sessionB 申請了自增值以后馬上就釋放自增鎖，那么就可能出現這樣的情況：
sessionB 先插入了兩行數據(1,1,1)、(2,2,2)
sessionA 來申請自增 id 得到 id=3，插入了(3,5,5)
之后，sessionB 繼續執行，插入兩條記錄(4,3,3)、(5,4,4)
當 binlog_format=statement 的時候，兩個 session 是同時執行插入數據命令的，所以 binlog 里面對表 t2 的更新日志只有兩種情況：要么先記 sessionA 的，要么先記錄 sessionB 的。無論是哪一種，這個 binlog 拿到從庫執行，或者用來恢復臨時實例，備庫和臨時實例里面，sessionB 這個語句執行出來，生成的結果里面，id 都是連續的。這時，這個庫就發生了數據不一致
解決這個問題的思路：
1）讓原庫的批量插入數據語句，固定生成連續的 id 值。所以，自增鎖直到語句執行結束才釋放，就是為了達到這個目的
2）在 binlog 里面把插入數據的操作都如實記錄進來，到備庫執行的時候，不再依賴于自增主鍵去生成。也就是把 innodb_autoinc_lock_mode 設置為 2，同時 binlog_format 設置為 row
如果有批量插入數據（insert … select、replace … select 和 load data）的場景時，從并發插入數據性能的角度考慮，建議把 innodb_autoinc_lock_mode 設置為 2，同時 binlog_format 設置為 row，這樣做既能并發性，又不會出現數據一致性的問題
對于批量插入數據的語句，MySQL 有一個批量申請自增 id 的策略：
1. 語句執行過程中，第一次申請自增 id，會分配 1 個
2.1 個用完以后，這個語句第二次申請自增 id，會分配 2 個
3.2 個用完以后，還是這個語句，第三次申請自增 id，會分配 4 個
4. 依次類推，同一個語句去申請自增 id，每次申請到的自增 id 個數都是上一次的兩倍
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 t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);insert … select，實際上往表 t2 中插入了 4 行數據。但是，這四行數據是分三次申請的自增 id，第一次申請到了 id=1，第二次被分配了 id= 2 和 id=3，第三次被分配到 id= 4 到 id=7
由于這條語句實際上只用上了 4 個 id，所以 id= 5 到 id= 7 就被浪費掉了。之后，再執行 insert into t2 values(null, 5,5)，實際上插入了的數據就是(8,5,5)
這是主鍵 id 出現自增 id 不連續的第三種原因
五、自增主鍵用完了
自增主鍵字段在達到定義類型上限后，再插入一行記錄，則會報主鍵沖突的錯誤
以無符號整型（4 個字節，上限就是
2
32
?
1
2^{32}-1
232?1）為例，通過下面這個語句序列驗證一下：
CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);第一個 insert 語句插入數據成功后，這個表的 AUTO_INCREMENT 沒有改變（還是 4294967295），就導致了第二個 insert 語句又拿到相同的自增 id 值，再試圖執行插入語句，報主鍵沖突錯誤
感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“MySQL 中自增主鍵的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!
