MySQL 主鍵自增也有坑？

資料庫工作筆記發表於2023-04-20
MySql

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

1. 為什麼不用 UUID

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

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

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

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

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

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

2. 主鍵自增的問題

以下內容，有一個共同的大前提，就是我們的表設定了主鍵自增。

一般來說，主鍵自增是沒有什麼問題的。但是，如果在高併發環境下，就會有問題了。

首先最容易想到的就是在高併發插入的時候產生的尾部熱點問題，併發插入時，大家都需要去查詢這個值然後計算出自己的主鍵值，那麼主鍵的上界就會成為熱點資料，併發插入時這裡會產生鎖競爭。

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

2.1 資料插入的三種形式

首先，我們在向資料表中插入資料的時候，一般來說有三種不同的形式，分別如下：

  1. insert into user(name) values('javaboy') 或者 replace into user(name) values('javaboy') ，這種沒有巢狀子查詢並且能夠確定具體插入多少行的插入叫做 simple insert，不過需要注意的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。
  2. load data 或者 insert into user select ... from ....，這種都是批次插入，叫做 bulk insert，這種批次插入有一個特點就是插入多少條資料在一開始是未知的。
  3. 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 的取值：

MySQL 主鍵自增也有坑？

可以看到，我使用的 8.0.32 這個版本目前預設值是 2。

我先把它改成 0，修改方式就是在 /etc/my.cnf 檔案中新增一行 innodb_autoinc_lock_mode=0：

MySQL 主鍵自增也有坑？

改完之後再重啟檢視，如下：

MySQL 主鍵自增也有坑？

可以看到，現在就已經改過來了。

現在假設我有如下表：

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,'),(null,'lisi');

插入完成之後，我們來看查詢結果：

MySQL 主鍵自增也有坑？

按照我們前文的介紹，這個情況應該是可以解釋的通的，我這裡不再贅述。

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

MySQL 主鍵自增也有坑？

還是上面相同的 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 屬性取一個合適的值。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70027826/viewspace-2947089/，如需轉載，請註明出處，否則將追究法律責任。

相關文章