MySQL主鍵自增會遇到的坑怎么解決

這篇文章主要介紹“MySQL主鍵自增會遇到的坑怎么解決”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL主鍵自增會遇到的坑怎么解決”文章能幫助大家解決問題。

1. 為什么不用 UUID

所以，如果我們使用 UUID 字符串作為主鍵，那么就會導致每次數據插入的時候，都需要在 B+Tree 中尋找到適合它自己的位置，找到之后就有可能要挪動后面的節點（就像在數組中插入一條記錄），挪動后面的節點，就有可能涉及到頁分裂，插入效率就會降低。

另一方面，在非聚簇索引中，葉子結點保存的是主鍵值，主鍵如果是一個很長的 UUID 字符串，就會占據較大的存儲空間（相對 int 而言），那么同一個葉子結點能夠保存的主鍵值數量就會減少，進而可能會導致樹變高，樹變高，意味著查詢的時候 IO 次數增加，查詢效率降低。

基于上面的分析，我們在 MySQL 中盡量不使用 UUID 作為主鍵，不用 UUID，可能會有小伙伴想到，那我使用主鍵自增行不行？

對于上面提到的兩個使用 UUID 作為主鍵的問題，使用主鍵自增顯然都可以解決。主鍵自增，每次只需要往樹的末尾添加就行了，基本上不會涉及到頁分裂問題；主鍵自增意味著主鍵是數字，占用的存儲空間相對來說就比較小，對非聚簇索引的影響也會小一些。

那么主鍵自增就是最佳方案嗎？主鍵自增有沒有一些需要注意的問題？

2. 主鍵自增的問題

以下內容，有一個共同的大前提，就是我們的表設置了主鍵自增。

一般來說，主鍵自增是沒有什么問題的。但是，如果在高并發環境下，就會有問題了。

首先最容易想到的就是在高并發插入的時候產生的尾部熱點問題，并發插入時，大家都需要去查詢這個值然后計算出自己的主鍵值，那么主鍵的上界就會成為熱點數據，并發插入時這里會產生鎖競爭。

為了解決這個問題，我們就需要選擇適合自己的 innodb_autoinc_lock_mode。

2.1 數據插入的三種形式

首先，我們在向數據表中插入數據的時候，一般來說有三種不同的形式，分別如下：

• insert into user(name) values('javaboy') 或者 replace into user(name) values('javaboy') ，這種沒有嵌套子查詢并且能夠確定具體插入多少行的插入叫做 simple insert，不過需要注意的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。

• load data 或者 insert into user select ... from ....，這種都是批量插入，叫做 bulk insert，這種批量插入有一個特點就是插入多少條數據在一開始是未知的。

• insert into user(id,name) values(null,'javaboy'),(null,'江南一點雨')，這種也是批量插入，但是跟第二種又不太一樣，這種里邊包含了一些自動生成的值（本案例中的主鍵自增），并且能夠確定一共插入多少行，這種稱之為 mixed insert，對于前面第一點提到的 INSERT ... ON DUPLICATE KEY UPDATE 也算是一種 mixed insert。

將數據插入分為這三類，主要是因為在主鍵自增的時候，鎖的處理方案不同，我們繼續往下看。

2.2 innodb_autoinc_lock_mode

我們可以通過控制 innodb_autoinc_lock_mode 變量的值，來控制在主鍵自增的時候，MySQL 鎖的處理思路。

innodb_autoinc_lock_mode 變量一共有三個不同的取值：

• 0: 這個表示 traditional，在這種模式下，我們上面提到的三種不同的插入 SQL，對于自增鎖的處理方案是一致的，都是在插入 SQL 語句開始的時候，獲取到一個表級的 AUTO-INC 鎖，然后當插入 SQL 執行完畢之后，再釋放掉這把鎖，這樣做的好處是可以確保在批量插入的時候，自增主鍵是連續的。

• 1: 這個表示 consecutive，在這種模式下，對 simple insert（能夠確定具體插入行數的，對應上面 1、3 兩種情況）做了一些優化，由于 simple insert 插入多少行這個很好計算，于是可以一次性生成幾個連續的值用在對應的插入 SQL 語句上，這樣就可以提前釋放掉 AUTO-INC 鎖，可以減少鎖等待，提高并發插入效率。

• 2: 這個表示 interleaved，這種情況下不存在 AUTO-INC 鎖，來一個處理一個，批量插入的時候，就有可能出現主鍵雖然自增，但是不連續的問題。

從上面的介紹中小伙伴們可以看到，實際上第三種，也就是 innodb_autoinc_lock_mode 取值為 2 的情況下，并發效率是最強的，那么我們是不是就應該設置 innodb_autoinc_lock_mode=2 呢？

這得看情況。

松哥之前寫過一篇文章和小伙伴們介紹 MySQL binlog 日志文件的三種格式：

• row：binlog 中記錄的是具體的值而不是原始的 SQL，舉一個簡單例子，假設表中有一個字段是 UUID，用戶執行的 SQL 是 insert into user(username,uuid) values('javaboy',uuid())，那么最終記錄到 binlog 中的 SQL 是 insert into user(username,uuid) values('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’)。

• statement：binlog 中記錄的就是原始的 SQL 了，以 row 中的為例，最終 binlog 中記錄的就是 insert into user(username,uuid) values('javaboy',uuid())。

• mixed：在這種模式下，MySQL 會根據具體的 SQL 語句來決定日志的形式，也就是在 statement 和 row 之間選擇一種。

對于這三種不同的模式，很明顯，在主從復制的時候，statement 模式可能會導致主從數據不一致，所以現在 MySQL 默認的 binlog 格式都是 row。

回到我們的問題：

• 如果 binlog 格式是 row，那么我們就可以設置 innodb_autoinc_lock_mode 的值為 2，這樣就能盡最大程度保證數據并發插入的能力，同時不會發生主從數據不一致的問題。

• 如果 binlog 格式是 statement，那么我們最好設置 innodb_autoinc_lock_mode 的值為 1，這樣對于 simple insert 的并發插入能力進行了提高，批量插入還是先獲取 AUTO-INC 鎖，等插入成功之后再釋放，這樣也能避免主從數據不一致，保證數據復制的安全性。

• 以上兩點主要是針對 InnoDB 存儲引擎，如果是 MyISAM 存儲引擎，都是先獲取 AUTO-INC 鎖，插入完成再釋放，相當于 innodb_autoinc_lock_mode 變量的取值對 MyISAM 不生效。

2.3 實踐

接下來我們來通過一個簡單的 SQL 來和小伙伴們演示一下 innodb_autoinc_lock_mode 不同取值對應不同結果的情況。

首先，我們可以通過如下 SQL 查看當前 innodb_autoinc_lock_mode 的取值：

可以看到，我使用的 8.0.32 這個版本目前默認值是 2。

我先把它改成 0，修改方式就是在 /etc/my.cnf 文件中添加一行 innodb_autoinc_lock_mode=0：

改完之后再重啟查看，如下：

可以看到，現在就已經改過來了。

現在假設我有如下表：

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

這個自增是從 100 開始計的，現在假設我有如下插入 SQL：

insert into user(id,username) values(1,'javaboy'),(null,'江南一點雨'),(3,'www.javaboy.org'),(null,'lisi');

插入完成之后，我們來看查詢結果：

按照我們前文的介紹，這個情況應該是可以解釋的通的，我這里不再贅述。

接下來，我把 innodb_autoinc_lock_mode 取值改為 1，如下：

還是上面相同的 SQL，我們再執行一遍。執行完成之后結果也和上文相同。

但是！！！**當上面的 SQL 執行完畢之后，如果我們還想再插入數據，并且新插入的 ID 不指定值，則我們發現自動生成的 ID 值為 104。**這就是因為我們設置了 innodb_autoinc_lock_mode=1，此時，執行 simple insert 插入的時候，系統一看我要插入 4 條記錄，就直接給我提前拿了 4 個 ID 出來，分別是 100、101、102 以及 103，結果該 SQL 實際上只用了兩個 ID，剩下兩個沒用，但是下次插入還是從 104 開始了。

關于“MySQL主鍵自增會遇到的坑怎么解決”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。