講講關(guān)于SQL大表刪除的問題

今天來講講關(guān)于大表刪除 的問題。

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

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

deletefromyeswherecreate_date>"2020-12-31"andcreate_date

	

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

	長事務(wù)

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

	那么一次性 delete 完的方案是不行的，因為這會涉及到長事務(wù)的問題 。

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

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

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

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

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

	那面對可能發(fā)生長事務(wù)的 SQL 我們怎么拆 呢？

	拆 SQL

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

	
deletefromyeswherecreate_date>"2020-12-31"andcreate_date

	

	看到這條 SQL，如果要拆分，想必很多小伙伴會覺得很簡單，按日期拆不就完事了？

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

	

	......

	這當(dāng)然可以，恭喜你，你已經(jīng)拆分成功了，沒錯就這么簡單。

	但是，如果 create_date 沒有索引怎么辦？

	沒索引的話，上面這就全表掃描了??？

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

	我們直接一個 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)然你也可以再精確些，通過日期篩選來得到 maxId，這影響不大（不滿足條件的 SQL 執(zhí)行很快，不會耗費很多時間）。

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

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

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

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

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

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

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

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

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

	具體操作也簡單：

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

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

	將 yes 表 rename 成 yes_233；

	將 yes_temp 表 rename 成 yes。

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

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

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

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

	最后

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

	





	審核編輯：劉清