最近公司需要統(tǒng)一更改一些老表的主鍵類型，以前表的主鍵都是 int 類型，這次要改成 bigint。

然后我整理的時(shí)候發(fā)現(xiàn)一張表，里面竟然有 5 億的數(shù)據(jù)，之前排查問(wèn)題優(yōu)化過(guò)幾條慢 sql，這個(gè)表的查詢竟然沒(méi)進(jìn)慢 sql 名單，有點(diǎn)突破我的認(rèn)知，平日使用也沒(méi)啥問(wèn)題。

后面還發(fā)現(xiàn)了好多張 3000w 到 8000w 的表，里面字段數(shù)量也比較正常，10個(gè)左右，也在好好的用著，所以不要死板的聽網(wǎng)上說(shuō)超過(guò) 1000w、2000w 就要分表啥的。

避免提前優(yōu)化，出了問(wèn)題再處理才是王道，因?yàn)槟闾崆白龅囊恍?zhǔn)備，很大可能是無(wú)用功，浪費(fèi)了感情和精力。

話說(shuō)回 5 億數(shù)據(jù)的這張表。

當(dāng)天晚上執(zhí)行修改類型語(yǔ)句時(shí)，由于執(zhí)行時(shí)間超過(guò)了自建 sql 平臺(tái)的時(shí)間閾值（平臺(tái)發(fā)現(xiàn)一條 sql 執(zhí)行超過(guò) 2 小時(shí)就會(huì)主動(dòng)關(guān)閉連接）。

而這個(gè)修改類型的 modify 語(yǔ)句又不能分開執(zhí)行，只能一次性執(zhí)行，所以就尬住了。

當(dāng)時(shí)還有一條方案是繞開 sql 平臺(tái)， 讓 dba 在外面直接執(zhí)行，后面由于時(shí)間太晚了，所以就等第二天再說(shuō)。

到了第二天，分析了下這張表，發(fā)現(xiàn)其實(shí)之前的數(shù)據(jù)都是沒(méi)用的，可以進(jìn)行歸檔，也就是把 21 年的數(shù)據(jù)移到另一張表中，只留下 22 年的數(shù)據(jù)。

這張表是有時(shí)間索引的。

我查了下 21 年的數(shù)據(jù)大概有 3 億多，刪除這些數(shù)據(jù)后，估計(jì)能減少一半多 modify 的時(shí)間，而且本身這張表也是要?dú)w檔的，只是今年忘了做了（說(shuō)明一直沒(méi)遇到查詢慢的問(wèn)題）。

所以方案就變成，先進(jìn)行數(shù)據(jù)歸檔，即 insert into  21年的數(shù)據(jù)到新表中，然后 delete 這張表里面 21 年的數(shù)據(jù)，然后再 modify 更改類型。

insert into 和 delete 語(yǔ)句都很簡(jiǎn)單，但是由于數(shù)據(jù)量太大，避免長(zhǎng)事務(wù)的問(wèn)題，dba要求我們自行拆分 sql 語(yǔ)句給他執(zhí)行。

當(dāng)時(shí)我就尋思著：這拆分也得開發(fā)來(lái)拆？DBA 就僅僅是個(gè)無(wú)情的執(zhí)行機(jī)器？

行吧，拆就拆，然后我就將 insert into 拆成了 100 條， delete 也拆成了 100 條給了 DBA。

當(dāng)天晚上 DBA 又執(zhí)行了一波，不過(guò)當(dāng)時(shí)的 delete 有好幾條失敗了，他詢問(wèn)我，這個(gè)表當(dāng)前還會(huì)有請(qǐng)求讓其變更嗎？

我說(shuō)不可能，因?yàn)檫@張表相當(dāng)于流水表，刪除的是 21 年的數(shù)據(jù)，當(dāng)前不可能有 21 年數(shù)據(jù)的變更，但確實(shí)是報(bào)錯(cuò)了，我看了下錯(cuò)誤，鎖超時(shí)。

當(dāng)時(shí)我就奇怪，為什么有鎖等待超時(shí)，現(xiàn)在不可能有業(yè)務(wù)在操作 21年的數(shù)據(jù)。

后面我才發(fā)現(xiàn) DBA 是在并行執(zhí)行多條 delete 語(yǔ)句。

于是，我在群里跟 DBA 說(shuō)應(yīng)該因?yàn)槟悴⑿袌?zhí)行多條 delete ，它們之前有競(jìng)爭(zhēng)關(guān)系，而一條 delete 刪除的數(shù)據(jù)挺多的，所以等鎖等超時(shí)了。

DBA 來(lái)了句：有 id 范圍限制的，delete 之間應(yīng)該不會(huì)有沖突的。

我簡(jiǎn)化下，幾條 delete 語(yǔ)句如下所示：

delete from yes where date < '2022-06-25' and (id >= 1 and id <10)
delete from yes where date < '2022-06-25' and (id >= 10 and id <20)
delete from yes where date < '2022-06-25' and (id >= 20 and id <30)

好了，背景交代完畢，看到這你可以思考一下，并行執(zhí)行上面這幾條 delete 語(yǔ)句，它們之間是否會(huì)發(fā)生競(jìng)爭(zhēng)鎖呢？

開始表演

當(dāng)前事務(wù)隔離級(jí)別為：可重復(fù)讀隔離級(jí)別，mysql 版本5.7+。

答案是它們之間會(huì)沖突，會(huì)競(jìng)爭(zhēng)鎖。

一切拿事實(shí)說(shuō)話，為了這個(gè)事實(shí)首先我們得有一張表。

CREATE TABLE `yes` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(45) DEFAULT NULL,
 `address` varchar(45) DEFAULT NULL,
 `date` date DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

小數(shù)據(jù)量演示

先來(lái)看看小數(shù)據(jù)量，可以看到數(shù)據(jù)庫(kù)就 5 條數(shù)據(jù)。

這時(shí)候我在客戶端 A 執(zhí)行一條 delete 語(yǔ)句：

delete from yes where date < '2022-06-25' and (id > 1 and id <3)

不提交事務(wù)。

此時(shí)在客戶端 B 執(zhí)行另一條 delete語(yǔ)句：

delete from yes where date < '2022-06-25' and (id >= 3 and id <5)

可以看到，此時(shí)發(fā)生了阻塞：

是不是有點(diǎn)奇怪？看起來(lái)它們之間沒(méi)有沖突的呀？

讓我們執(zhí)行下 select * from information_schema.innodb_locks;，看看鎖的詳情：

可以看到 lock_mode 是 X 鎖，說(shuō)明是排他鎖，然后 lock_type 是 RECORD 說(shuō)明是行鎖。

lock_index 是 PRIMARY ，說(shuō)明鎖的是主鍵索引， lock_data 是 3，也就是鎖的是主鍵 ID 為 3 的那條記錄。

此時(shí)我們就知道了，確實(shí)發(fā)生了競(jìng)爭(zhēng)，且競(jìng)爭(zhēng)之地發(fā)生在主鍵索引上，用的是行鎖，沖突的那行就是 ID 為 3 的那行。

我們?cè)賮?lái)執(zhí)行select * from sys.innodb_lock_waits \G;，看下對(duì)應(yīng)的 lock_id 是不是我們執(zhí)行的語(yǔ)句：

可以看到，確實(shí)是 10586 被阻塞了，對(duì)應(yīng)的就是客戶端 B 執(zhí)行的那條語(yǔ)句。

這個(gè)實(shí)驗(yàn)已經(jīng)和那天晚上阻塞的情況吻合了，當(dāng)然結(jié)果是結(jié)果，重要的是搞清楚為什么會(huì)這樣。

讓我們繼續(xù)往下看。

我們來(lái) explain 一下這個(gè) delete 語(yǔ)句：

發(fā)現(xiàn) delete 語(yǔ)句用的是主鍵索引，即使 date 列有索引能也能覆蓋到條件字段（id），用的也是主鍵索引。

但是講道理即使用的主鍵索引也不對(duì)呀，有 id < 3這個(gè)條件，為什么會(huì)鎖 id=3 這行？

因?yàn)樵诳芍貜?fù)讀隔離級(jí)別下，實(shí)際上范圍加鎖(id >1)規(guī)則是會(huì)往后遍歷，直到掃描到不滿足條件 即 id = 3 的那行，然后停止，因此這條語(yǔ)句最后掃描到的那行恰巧就是 id =3 的這一行，于是鎖住了它。

此時(shí)另一條 delete 語(yǔ)句執(zhí)行的時(shí)候是需要 id =3 這條記錄的行鎖(這個(gè)沒(méi)啥疑問(wèn)吧？)，所以就競(jìng)爭(zhēng)了，然后由于第一條語(yǔ)句 delete 的數(shù)據(jù)量大，所以執(zhí)行的久，于是就觸發(fā)了第二條的鎖超時(shí)。

好了，通過(guò)小數(shù)據(jù)分析得到的結(jié)果已經(jīng)和那天晚上執(zhí)行的結(jié)果一致，其實(shí)到這已經(jīng)可以結(jié)束了，但是為了嚴(yán)謹(jǐn)一些，現(xiàn)在我們拿大數(shù)據(jù)量來(lái)繼續(xù)實(shí)驗(yàn)一次。

大數(shù)據(jù)量演示

為了更加真實(shí)，首先我多加了一些字段：

然后隨機(jī)插入了 1000w 數(shù)據(jù)：

這時(shí)候我在客戶端 A 執(zhí)行 delete 語(yǔ)句：

delete from yes where date < '2022-06-25' and (id > 1 and id <100000)

不提交事務(wù)。

此時(shí)在客戶端 B 執(zhí)行另一條 delete 語(yǔ)句：

delete from yes where date < '2022-06-25' and (id >= 100000 and id <200000)

同樣發(fā)生了阻塞，而且一樣用的是主鍵，同樣還是競(jìng)爭(zhēng)的是邊界值的那一行 id:100000：

好了，大數(shù)據(jù)量的也測(cè)試過(guò)了，得到一樣的結(jié)論，這樣就能解釋為啥當(dāng)天并行執(zhí)行多條 delete 語(yǔ)句會(huì)出現(xiàn)鎖超時(shí)的情況。

小結(jié)

在可重復(fù)讀隔離級(jí)別下，帶上索引鍵和主鍵通過(guò)范圍搜索條件來(lái)執(zhí)行 delete  語(yǔ)句，不論數(shù)據(jù)量大還是小，mysql 都會(huì)利用主鍵索引來(lái)掃描記錄（我猜測(cè)反正都要?jiǎng)h數(shù)據(jù)，即本來(lái)就要?jiǎng)h除二級(jí)索引和聚簇索引的數(shù)據(jù)，所以索性就用主鍵索引掃描？）

而范圍掃描加鎖的數(shù)據(jù)會(huì)掃到第一個(gè)不滿足條件的記錄，即第一個(gè)不滿足條件的記錄也會(huì)被上鎖，因此并行刪除的時(shí)候因?yàn)檫吔缰诞a(chǎn)生了競(jìng)爭(zhēng)關(guān)系，又由于 delete 語(yǔ)句執(zhí)行的時(shí)間長(zhǎng)，導(dǎo)致了 lock wait timeout 的報(bào)錯(cuò)。

最后

好了，分析結(jié)束。

話說(shuō)今天隨機(jī)插入數(shù)據(jù)的時(shí)候搞了我好久……寫了個(gè)存儲(chǔ)過(guò)程來(lái)插，但是執(zhí)行了半天發(fā)生一直插不進(jìn)去，一直在 runing，就非常的納悶，想著一千萬(wàn)數(shù)據(jù)也不需要這么久的啊。

后面奇了怪了，于是新建了一張表，分分鐘就插成功了。于是又回來(lái)看之前的表，看來(lái)看去看不出個(gè)所以然，于是準(zhǔn)備把這張表刪了，發(fā)現(xiàn)刪都刪不掉，最終發(fā)現(xiàn)我有個(gè)小窗口執(zhí)行的語(yǔ)句把整個(gè)表鎖了....所以怎么都插不進(jìn)去。

前后搞了 3 個(gè)多小時(shí)，最終執(zhí)行的結(jié)果就花了 2 分鐘……

難受。

話說(shuō)回來(lái)，這 DBA 是真的懶，感覺(jué)他的活都不用動(dòng)腦，搞啥都是 sql 平臺(tái)上我們提交sql，由我們的技術(shù)負(fù)責(zé)人審核，審核過(guò)了，他在界面上點(diǎn)一下執(zhí)行就行。

前面說(shuō)的拆 SQL 這種非業(yè)務(wù)相關(guān)的也得我們拆，給他排的整整齊齊讓他執(zhí)行。

平時(shí)我們監(jiān)控報(bào)警，什么數(shù)據(jù)庫(kù) CPU 報(bào)警了，也是報(bào)警到我們這邊，由我們來(lái)看具體是什么導(dǎo)致的。

總之，不要過(guò)多信任 DBA，一切還是得靠自己，自己行才是真的行，包括 DBA 告訴你的一些結(jié)論，還是自己實(shí)驗(yàn)最為靠譜。