在上篇文章中，松哥和小伙伴們分享了 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é)點(diǎn)（就像在數(shù)組中插入一條記錄），挪動后面的節(jié)點(diǎn)，就有可能涉及到頁分裂，插入效率就會降低。

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

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

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

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

2. 主鍵自增的問題

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

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

首先最容易想到的就是在高并發(fā)插入的時候產(chǎn)生的尾部熱點(diǎn)問題，并發(fā)插入時，大家都需要去查詢這個值然后計算出自己的主鍵值，那么主鍵的上界就會成為熱點(diǎn)數(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，這種批量插入有一個特點(diǎn)就是插入多少條數(shù)據(jù)在一開始是未知的。
• insert into user(id,name) values(null,'javaboy'),(null,'江南一點(diǎn)雨')，這種也是批量插入，但是跟第二種又不太一樣，這種里邊包含了一些自動生成的值（本案例中的主鍵自增），并且能夠確定一共插入多少行，這種稱之為 mixed insert，對于前面第一點(diǎn)提到的 INSERT ... ON DUPLICATE KEY UPDATE 也算是一種 mixed insert。

將數(shù)據(jù)插入分為這三類，主要是因?yàn)樵谥麈I自增的時候，鎖的處理方案不同，我們繼續(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ù)的問題。

從上面的介紹中小伙伴們可以看到，實(shí)際上第三種，也就是 innodb_autoinc_lock_mode 取值為 2 的情況下，并發(fā)效率是最強(qiáng)的，那么我們是不是就應(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ù)制的安全性。
• 以上兩點(diǎn)主要是針對 InnoDB 存儲引擎，如果是 MyISAM 存儲引擎，都是先獲取 AUTO-INC 鎖，插入完成再釋放，相當(dāng)于 innodb_autoinc_lock_mode 變量的取值對 MyISAM 不生效。

2.3 實(shí)踐

接下來我們來通過一個簡單的 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,'江南一點(diǎn)雨'),(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。**這就是因?yàn)槲覀冊O(shè)置了 innodb_autoinc_lock_mode=1，此時，執(zhí)行 simple insert 插入的時候，系統(tǒng)一看我要插入 4 條記錄，就直接給我提前拿了 4 個 ID 出來，分別是 100、101、102 以及 103，結(jié)果該 SQL 實(shí)際上只用了兩個 ID，剩下兩個沒用，但是下次插入還是從 104 開始了。

3. 小結(jié)

好啦，這就是關(guān)于主鍵自增的一個小小知識點(diǎn)，小伙伴們一定要根據(jù)實(shí)際情況來為 innodb_autoinc_lock_mode 屬性取一個合適的值。

以上就是MySQL主鍵自增會遇到的坑及解決方法的詳細(xì)內(nèi)容，更多關(guān)于MySQL主鍵自增的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論