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

1. 為什么不用 UUID

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

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

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

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

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

那么主鍵自增就是最佳方案嗎？主鍵自增有沒(méi)有一些需要注意的問(wèn)題？

2. 主鍵自增的問(wèn)題

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

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

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

為了解決這個(gè)問(wèn)題，我們就需要選擇適合自己的 innodb_autoinc_lock_mode。

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

首先，我們?cè)谙驍?shù)據(jù)表中插入數(shù)據(jù)的時(shí)候，一般來(lái)說(shuō)有三種不同的形式，分別如下：

• insert into user(name) values('javaboy') 或者 replace into user(name) values('javaboy') ，這種沒(méi)有嵌套子查詢(xún)并且能夠確定具體插入多少行的插入叫做 simple insert，不過(guò)需要注意的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。
• load data 或者 insert into user select ... from ....，這種都是批量插入，叫做 bulk insert，這種批量插入有一個(gè)特點(diǎn)就是插入多少條數(shù)據(jù)在一開(kāi)始是未知的。
• insert into user(id,name) values(null,'javaboy'),(null,'江南一點(diǎn)雨')，這種也是批量插入，但是跟第二種又不太一樣，這種里邊包含了一些自動(dòng)生成的值（本案例中的主鍵自增），并且能夠確定一共插入多少行，這種稱(chēng)之為 mixed insert，對(duì)于前面第一點(diǎn)提到的 INSERT ... ON DUPLICATE KEY UPDATE 也算是一種 mixed insert。

將數(shù)據(jù)插入分為這三類(lèi)，主要是因?yàn)樵谥麈I自增的時(shí)候，鎖的處理方案不同，我們繼續(xù)往下看。

2.2 innodb_autoinc_lock_mode

我們可以通過(guò)控制 innodb_autoinc_lock_mode 變量的值，來(lái)控制在主鍵自增的時(shí)候，MySQL 鎖的處理思路。

innodb_autoinc_lock_mode 變量一共有三個(gè)不同的取值：

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

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

這得看情況。

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

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

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

回到我們的問(wèn)題：

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

2.3 實(shí)踐

接下來(lái)我們來(lái)通過(guò)一個(gè)簡(jiǎn)單的 SQL 來(lái)和小伙伴們演示一下 innodb_autoinc_lock_mode 不同取值對(duì)應(yīng)不同結(jié)果的情況。

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

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

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

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

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

現(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;

這個(gè)自增是從 100 開(kāi)始計(jì)的，現(xiàn)在假設(shè)我有如下插入 SQL：

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

插入完成之后，我們來(lái)看查詢(xún)結(jié)果：

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

接下來(lái)，我把 innodb_autoinc_lock_mode 取值改為 1，如下：

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

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

3. 小結(jié)

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

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

最新評(píng)論