快捷導(dǎo)航

MySQL主鍵自增會遇到的坑及解決方法

更新時間：2023年04月20日 14:53:43 作者：江南一點雨

主鍵不用隨機(jī)字符串用什么？主鍵自增？主鍵自增就是最佳方案嗎？有沒有其他坑？今天我們就來討論下這個話題，感興趣的小伙伴可以收藏一下

在上篇文章中，松哥和小伙伴們分享了 MySQL 的聚簇索引，也順便和小伙伴們分析了為什么在 MySQL 中主鍵不應(yīng)該使用隨機(jī)字符串。但是主鍵不用隨機(jī)字符串用什么？主鍵自增？主鍵自增就是最佳方案嗎？有沒有其他坑？今天我們就來討論下這個話題。

1. 為什么不用 UUID

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

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

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

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

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

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

2. 主鍵自增的問題

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

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

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

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

2.1 數(shù)據(jù)插入的三種形式

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

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，這種批量插入有一個特點就是插入多少條數(shù)據(jù)在一開始是未知的。
insert into user(id,name) values(null,'javaboy'),(null,'江南一點雨')，這種也是批量插入，但是跟第二種又不太一樣，這種里邊包含了一些自動生成的值（本案例中的主鍵自增），并且能夠確定一共插入多少行，這種稱之為 mixed insert，對于前面第一點提到的 INSERT ... ON DUPLICATE KEY UPDATE 也算是一種 mixed insert。

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

2.2 innodb_autoinc_lock_mode

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

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

0: 這個表示 traditional，在這種模式下，我們上面提到的三種不同的插入 SQL，對于自增鎖的處理方案是一致的，都是在插入 SQL 語句開始的時候，獲取到一個表級的 AUTO-INC 鎖，然后當(dāng)插入 SQL 執(zhí)行完畢之后，再釋放掉這把鎖，這樣做的好處是可以確保在批量插入的時候，自增主鍵是連續(xù)的。
1: 這個表示 consecutive，在這種模式下，對 simple insert（能夠確定具體插入行數(shù)的，對應(yīng)上面 1、3 兩種情況）做了一些優(yōu)化，由于 simple insert 插入多少行這個很好計算，于是可以一次性生成幾個連續(xù)的值用在對應(yīng)的插入 SQL 語句上，這樣就可以提前釋放掉 AUTO-INC 鎖，可以減少鎖等待，提高并發(fā)插入效率。
2: 這個表示 interleaved，這種情況下不存在 AUTO-INC 鎖，來一個處理一個，批量插入的時候，就有可能出現(xiàn)主鍵雖然自增，但是不連續(xù)的問題。

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

這得看情況。

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

row：binlog 中記錄的是具體的值而不是原始的 SQL，舉一個簡單例子，假設(shè)表中有一個字段是 UUID，用戶執(zhí)行的 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 會根據(jù)具體的 SQL 語句來決定日志的形式，也就是在 statement 和 row 之間選擇一種。

對于這三種不同的模式，很明顯，在主從復(fù)制的時候，statement 模式可能會導(dǎo)致主從數(shù)據(jù)不一致，所以現(xiàn)在 MySQL 默認(rèn)的 binlog 格式都是 row。

回到我們的問題：

如果 binlog 格式是 row，那么我們就可以設(shè)置 innodb_autoinc_lock_mode 的值為 2，這樣就能盡最大程度保證數(shù)據(jù)并發(fā)插入的能力，同時不會發(fā)生主從數(shù)據(jù)不一致的問題。
如果 binlog 格式是 statement，那么我們最好設(shè)置 innodb_autoinc_lock_mode 的值為 1，這樣對于 simple insert 的并發(fā)插入能力進(jìn)行了提高，批量插入還是先獲取 AUTO-INC 鎖，等插入成功之后再釋放，這樣也能避免主從數(shù)據(jù)不一致，保證數(shù)據(jù)復(fù)制的安全性。
以上兩點主要是針對 InnoDB 存儲引擎，如果是 MyISAM 存儲引擎，都是先獲取 AUTO-INC 鎖，插入完成再釋放，相當(dāng)于 innodb_autoinc_lock_mode 變量的取值對 MyISAM 不生效。

2.3 實踐

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

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

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

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

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

可以看到，現(xiàn)在就已經(jīng)改過來了。

現(xiàn)在假設(shè)我有如下表：

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 開始計的，現(xiàn)在假設(shè)我有如下插入 SQL：

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

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

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

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

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

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