偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

MySQL 主鍵自增注意事項(xiàng),你學(xué)會(huì)了嗎?

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

很多小伙伴應(yīng)該知道,在 MySQL 中主鍵不應(yīng)該使用隨機(jī)字符串。但是主鍵不用隨機(jī)字符串用什么?主鍵自增?主鍵自增就是最佳方案嗎?有沒有其他坑?今天我們就來討論下這個(gè)話題。

1. 為什么不用 UUID

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

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

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

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

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

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

2. 主鍵自增的問題

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

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

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

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

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

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

  • 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,這種批量插入有一個(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

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

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

  • 0: 這個(gè)表示 traditional,在這種模式下,我們上面提到的三種不同的插入 SQL,對(duì)于自增鎖的處理方案是一致的,都是在插入 SQL 語句開始的時(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 語句上,這樣就可以提前釋放掉 AUTO-INC 鎖,可以減少鎖等待,提高并發(fā)插入效率。
  • 2: 這個(gè)表示 interleaved,這種情況下不存在 AUTO-INC 鎖,來一個(gè)處理一個(gè),批量插入的時(shí)候,就有可能出現(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,舉一個(gè)簡單例子,假設(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 語句來決定日志的形式,也就是在 statement 和 row 之間選擇一種。

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

回到我們的問題:

  • 如果 binlog 格式是 row,那么我們就可以設(shè)置 innodb_autoinc_lock_mode 的值為 2,這樣就能盡最大程度保證數(shù)據(jù)并發(fā)插入的能力,同時(shí)不會(huì)發(fā)生主從數(shù)據(jù)不一致的問題。
  • 如果 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í)踐

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

首先,我們可以通過如下 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)改過來了。

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

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

圖片圖片

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

接下來,我把 innodb_autoinc_lock_mode 取值改為 1,如下:

圖片圖片

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

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

3. 小結(jié)

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

責(zé)任編輯:武曉燕 來源: 江南一點(diǎn)雨
相關(guān)推薦

2022-12-06 07:53:33

MySQL索引B+樹

2023-05-05 06:54:07

MySQL數(shù)據(jù)查詢

2023-03-26 22:31:29

2023-03-07 07:50:15

Transactio事務(wù)代碼

2025-03-27 03:40:00

分布式系統(tǒng)Kafka

2024-01-19 08:25:38

死鎖Java通信

2024-02-04 00:00:00

Effect數(shù)據(jù)組件

2023-07-26 13:11:21

ChatGPT平臺(tái)工具

2023-01-10 08:43:15

定義DDD架構(gòu)

2023-11-01 07:28:31

MySQL日志維護(hù)

2023-08-01 12:51:18

WebGPT機(jī)器學(xué)習(xí)模型

2024-01-02 12:05:26

Java并發(fā)編程

2011-04-20 14:52:02

數(shù)據(jù)庫優(yōu)化

2023-10-10 11:04:11

Rust難點(diǎn)內(nèi)存

2024-05-06 00:00:00

InnoDBView隔離

2023-01-30 09:01:54

圖表指南圖形化

2024-07-31 08:39:45

Git命令暫存區(qū)

2022-07-08 09:27:48

CSSIFC模型

2023-12-12 08:02:10

2024-08-06 09:47:57

點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)