今天來(lái)講講關(guān)于大表刪除 的問(wèn)題。

比如，你現(xiàn)在需要?jiǎng)h除一張一共有 5 億數(shù)據(jù)的表里面的 2021 年數(shù)據(jù)，假設(shè)這張表叫 yes。

我相信你腦子在 1s 內(nèi)肯定會(huì)蹦出這條 SQL :

deletefromyeswherecreate_date>"2020-12-31"andcreate_date

	

	如果直接執(zhí)行這條 SQL 會(huì)發(fā)生什么問(wèn)題呢？

	長(zhǎng)事務(wù)

	我們需要關(guān)注到一個(gè)前提：這張表有 5 億的數(shù)據(jù)，所以它是一張超大表，因此這個(gè) where 條件可能涉及非常多的數(shù)據(jù)，所以我們可以從離線數(shù)倉(cāng)或者備庫(kù)查下數(shù)據(jù)量，然后我們發(fā)現(xiàn)這條 SQL 會(huì)刪除 3 億左右的數(shù)據(jù)。

	那么一次性 delete 完的方案是不行的，因?yàn)檫@會(huì)涉及到長(zhǎng)事務(wù)的問(wèn)題 。

	長(zhǎng)事務(wù)涉及到加鎖，只會(huì)在事務(wù)執(zhí)行完畢后才會(huì)釋放鎖，由于長(zhǎng)事務(wù)鎖了很多數(shù)據(jù)，如果期間有頻繁的 DML 想要操作這些數(shù)據(jù)，那么就會(huì)造成阻塞。

	連接都阻塞住了，業(yè)務(wù)線程自然就阻塞了，也就是說(shuō)你的服務(wù)線程都在等待數(shù)據(jù)庫(kù)的響應(yīng)，然后可能還會(huì)影響到別的服務(wù)，可能產(chǎn)生雪崩，于是就 GG 了。

	長(zhǎng)事務(wù)可能會(huì)造成主從延遲，你想想主庫(kù)執(zhí)行了好久，才執(zhí)行完給從庫(kù)，從庫(kù)又要重放好久，期間可能有很長(zhǎng)一段時(shí)間數(shù)據(jù)是不同步的。

	還有一種情況，業(yè)務(wù)都有個(gè)特殊停機(jī)窗口，你覺(jué)得你可以為所欲為，然后開始執(zhí)行長(zhǎng)事務(wù)了，然后執(zhí)行了 5 小時(shí)之后，不知道啥情況拋錯(cuò)了，事務(wù)回滾了，于是浪費(fèi)了 5 個(gè)小時(shí)，還得重新開始。

	綜上，我們需要避免長(zhǎng)事務(wù)的發(fā)生。

	那面對(duì)可能發(fā)生長(zhǎng)事務(wù)的 SQL 我們?cè)趺床?/strong> 呢？


	拆 SQL

	我們就以上面這條 SQL 為例：

	
deletefromyeswherecreate_date>"2020-12-31"andcreate_date

	

	看到這條 SQL，如果要拆分，想必很多小伙伴會(huì)覺(jué)得很簡(jiǎn)單，按日期拆不就完事了？

	
deletefromyeswherecreate_date>"2020-12-31"andcreate_date="2021-02-01"andcreate_date

	

	......

	這當(dāng)然可以，恭喜你，你已經(jīng)拆分成功了，沒(méi)錯(cuò)就這么簡(jiǎn)單。

	但是，如果 create_date 沒(méi)有索引怎么辦？

	沒(méi)索引的話，上面這就全表掃描了啊？

	影響不大，沒(méi)有索引我們就給他創(chuàng)造索引條件，這個(gè)條件就是主鍵。

	我們直接一個(gè) select min(id)... 和 select max(id).... 得到這張表的主鍵最小值和最大值，假設(shè)答案是 233333333 和 666666666。

	然后我們就可以開始操作了：

	
deletefromyeswhere(id>=233333333andid"2020-12-31"andcreate_date=233433333andid<233533333)?and?create_date?>"2020-12-31"andcreate_date

	

	......

	
deletefromyeswhere(id>=666566666andid<=666666666)?and?create_date?>"2020-12-31"andcreate_date

	

	當(dāng)然你也可以再精確些，通過(guò)日期篩選來(lái)得到 maxId，這影響不大（不滿足條件的 SQL 執(zhí)行很快，不會(huì)耗費(fèi)很多時(shí)間）。

	這樣一來(lái) SQL 就滿足了分批的操作，且用得上索引。

	如果哪條語(yǔ)句執(zhí)行出錯(cuò)，只會(huì)回滾小部分?jǐn)?shù)據(jù)，我們重新排查下就好了，影響不大。

	而且拆分 SQL 之后還可以并行提高執(zhí)行效率 。

	當(dāng)然，并行可能有鎖競(jìng)爭(zhēng)的情況，導(dǎo)致個(gè)別語(yǔ)句等待超時(shí)。不過(guò)影響不大，只要機(jī)器狀態(tài)好，執(zhí)行得快，因?yàn)殒i競(jìng)爭(zhēng)導(dǎo)致的等待并不一定會(huì)超時(shí)，如果個(gè)別 SQL 超時(shí)的話，重新執(zhí)行就好了。

	有時(shí)候要轉(zhuǎn)換思路

	關(guān)于大表刪除有時(shí)候要轉(zhuǎn)換思路，把刪除轉(zhuǎn)成插入 。

	假設(shè)還是有一張 5 億的數(shù)據(jù)表，此時(shí)你需要?jiǎng)h除里面 4.8 億的數(shù)據(jù)，那這時(shí)候就不要想著刪除了，要想著插入。

	道理很簡(jiǎn)單，刪除 4.8 億的數(shù)據(jù)，不如把要的 2000W 插入到新表中，我們后面業(yè)務(wù)直接用新表就好了。

	這兩個(gè)數(shù)據(jù)量對(duì)比，時(shí)間效率差異不言而喻了吧？

	具體操作也簡(jiǎn)單：

	創(chuàng)建一張新表，名為 yes_temp；

	將 yes 表的 2000W 數(shù)據(jù) select into 到 yes_temp 中；

	將 yes 表 rename 成 yes_233；

	將 yes_temp 表 rename 成 yes。

	貍貓換太子，大功告成啦！

	之前有個(gè)記錄表我們就是這樣操作的，就 select into 近一個(gè)月的數(shù)據(jù)到新表中，以前老數(shù)據(jù)就不管了，然后 rename 一下，執(zhí)行得非常快。

	本來(lái)預(yù)估 2 小時(shí)的 SQL 操作，1 分鐘就搞定了。

	這種類似的操作是有工具的，比如 pt-online-schema-change 等，不過(guò)我沒(méi)用過(guò)，有興趣的小伙伴可以自己去看看，道理是一樣的，多了幾個(gè)觸發(fā)器，這里不多贅述了。

	最后

	咱們開發(fā)還是得多學(xué)一些數(shù)據(jù)庫(kù)的操作和原理，因?yàn)楹枚鄶?shù)據(jù)庫(kù)的操作都需要親力親為，小公司沒(méi) DBA 的話就不說(shuō)了，大公司的話咱也不知道 DBA 到底會(huì)關(guān)心到哪個(gè)程度，還是得靠自己靠譜。

	





	審核編輯：劉清