詳解MySQL自增主鍵的實現

Posted on by

一、自增值保存在哪兒？

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

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

推薦資料：

https://time.geekbang.org/column/article/80531

到此這篇關於詳解MySQL自增主鍵的實現的文章就介紹到這瞭,更多相關MySQL自增主鍵內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：