今天來講講關于大表刪除 的問題。

比如，你現在需要刪除一張一共有 5 億數據的表里面的 2021 年數據，假設這張表叫 yes。

我相信你腦子在 1s 內肯定會蹦出這條 SQL :

deletefromyeswherecreate_date>"2020-12-31"andcreate_date

	

	如果直接執行這條 SQL 會發生什么問題呢？

	長事務

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

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

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

	連接都阻塞住了，業務線程自然就阻塞了，也就是說你的服務線程都在等待數據庫的響應，然后可能還會影響到別的服務，可能產生雪崩，于是就 GG 了。

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

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

	綜上，我們需要避免長事務的發生。

	那面對可能發生長事務的 SQL 我們怎么拆 呢？

	拆 SQL

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

	
deletefromyeswherecreate_date>"2020-12-31"andcreate_date

	

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

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

	

	......

	這當然可以，恭喜你，你已經拆分成功了，沒錯就這么簡單。

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

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

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

	我們直接一個 select min(id)... 和 select max(id).... 得到這張表的主鍵最小值和最大值，假設答案是 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

	

	當然你也可以再精確些，通過日期篩選來得到 maxId，這影響不大（不滿足條件的 SQL 執行很快，不會耗費很多時間）。

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

	如果哪條語句執行出錯，只會回滾小部分數據，我們重新排查下就好了，影響不大。

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

	當然，并行可能有鎖競爭的情況，導致個別語句等待超時。不過影響不大，只要機器狀態好，執行得快，因為鎖競爭導致的等待并不一定會超時，如果個別 SQL 超時的話，重新執行就好了。

	有時候要轉換思路

	關于大表刪除有時候要轉換思路，把刪除轉成插入 。

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

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

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

	具體操作也簡單：

	創建一張新表，名為 yes_temp；

	將 yes 表的 2000W 數據 select into 到 yes_temp 中；

	將 yes 表 rename 成 yes_233；

	將 yes_temp 表 rename 成 yes。

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

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

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

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

	最后

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

	





	審核編輯：劉清