MySQL 主鍵自增注意事項，你學會了嗎？

作者：江南一點雨 2024-06-07 10:14:23

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

很多小伙伴應該知道，在 MySQL 中主鍵不應該使用隨機字符串。但是主鍵不用隨機字符串用什么？主鍵自增？主鍵自增就是最佳方案嗎？有沒有其他坑？今天我們就來討論下這個話題。

1. 為什么不用 UUID

經過上篇文章的介紹，我們知道在 MySQL 中，主鍵索引就是聚簇索引，MySQL 表中的數據是根據主鍵值聚集在一起的，聚簇索引是一棵 B+Tree，這棵樹中的數據是有序的。

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

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

基于上面的分析，我們在 MySQL 中盡量不使用 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 開始了。

3. 小結

好啦，這就是關于主鍵自增的一個小小知識點，小伙伴們一定要根據實際情況來為 innodb_autoinc_lock_mode 屬性取一個合適的值。

責任編輯：武曉燕來源：江南一點雨