深入瞭解MySQL中的自增主鍵

firefule發表於2021-09-11
本篇文章帶大家瞭解一下MySQL中的自增主鍵,介紹一下自增值修改機制、自增值的修改時機、自增鎖的最佳化方法等等,有需要的朋友可以學習瞭解一下~

深入瞭解MySQL中的自增主鍵

一、自增值儲存在哪兒?

不同的引擎對於自增值的儲存策略不同

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 2321)為例,透過下面這個語句序列驗證一下:

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值,再試圖執行插入語句,報主鍵衝突錯誤

相關學習推薦:(影片)

以上就是深入瞭解MySQL中的自增主鍵的詳細內容,更多請關注php中文網其它相關文章!

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/1834/viewspace-2827347/,如需轉載,請註明出處,否則將追究法律責任。

相關文章