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

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

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

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

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

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

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

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

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

這張表是有時間索引的。

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

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

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

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

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

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

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

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

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

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

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

我簡化下，幾條 delete 語句如下所示：

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 語句，它們之間是否會發(fā)生競爭鎖呢？

開始表演

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

答案是它們之間會沖突，會競爭鎖。

一切拿事實(shí)說話，為了這個事實(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ù)量演示

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

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

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

不提交事務(wù)。

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

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

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

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

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

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

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

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

我們再來執(zhí)行select * from sys.innodb_lock_waits \G;，看下對應(yīng)的 lock_id 是不是我們執(zhí)行的語句：

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

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

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

我們來 explain 一下這個 delete 語句：

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

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

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

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

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

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

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

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

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

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

不提交事務(wù)。

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

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

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

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

小結(jié)

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

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

最后

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

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

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

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

難受。

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

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

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

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