MySQL主鍵自增會(huì)遇到的坑怎么解決

這篇文章主要介紹“MySQL主鍵自增會(huì)遇到的坑怎么解決”的相關(guān)知識(shí)，小編通過(guò)實(shí)際案例向大家展示操作過(guò)程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL主鍵自增會(huì)遇到的坑怎么解決”文章能幫助大家解決問(wèn)題。

1. 為什么不用 UUID

所以，如果我們使用 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í)候 IO 次數(shù)增加，查詢效率降低。

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

對(duì)于上面提到的兩個(gè)使用 UUID 作為主鍵的問(wèn)題，使用主鍵自增顯然都可以解決。主鍵自增，每次只需要往樹的末尾添加就行了，基本上不會(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í)，大家都需要去查詢這個(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)有嵌套子查詢并且能夠確定具體插入多少行的插入叫做 simple insert，不過(guò)需要注意的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。

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

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

將數(shù)據(jù)插入分為這三類，主要是因?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ǔ)句開始的時(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 呢？

這得看情況。

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

• row：binlog 中記錄的是具體的值而不是原始的 SQL，舉一個(gè)簡(jiǎn)單例子，假設(shè)表中有一個(gè)字段是 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 會(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 開始計(jì)的，現(xiàn)在假設(shè)我有如下插入 SQL：

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

插入完成之后，我們來(lái)看查詢結(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 開始了。

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