來源：yes的練級攻略

大家好，我是yes。

上篇文章 關於一張 5 億資料表之我與 DBA 的 battle 發了之後，有好幾個小夥伴來問我 SQL 是怎麼拆的。

這篇我們來簡單盤下，其實拆 SQL 是因為涉及大表刪除的問題。

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

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

delete from yes where create_date > "2020-12-31" and create_date < "2022-01-01";

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

長事務

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

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

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

連線都阻塞住了，業務執行緒自然就阻塞了，也就是說你的服務執行緒都在等待資料庫的響應，然後可能還會影響到別的服務，可能產生雪崩，於是就 GG 了。

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

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

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

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

拆 SQL

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

delete from yes where create_date > "2020-12-31" and create_date < "2022-01-01";

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

delete from yes where create_date > "2020-12-31" and create_date < "2021-02-01";

delete from yes where create_date >= "2021-02-01" and create_date < "2021-03-01";

......

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

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

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

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

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

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

delete from yes where (id >= 233333333 and id < 233433333)  and create_date > "2020-12-31" and create_date < "2022-01-01";

delete from yes where (id >= 233433333 and id <233533333) and create_date > "2020-12-31" and create_date < "2022-01-01";

......

delete from yes where (id >= 666566666 and id <=666666666) and create_date > "2020-12-31" and create_date < "2022-01-01";

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

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

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

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

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

有時候要轉換思路

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

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

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

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

具體操作也簡單：

1. 建立一張新表，名為 yes_temp
2. 將 yes 表的 2000W 資料 select into 到 yes_temp 中
3. 將 yes 表 rename 成 yes_233
4. 將 yes_temp 表 rename 成 yes

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

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

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

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

最後

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

我翻了翻我之前的文章，好像 MySQL 相關寫的是最多的，有興趣的可以看下這個合集：MySQL合集

不過應該有挺多沒加到這個合集的，有空我再整理整理。

最近打算換工作的可以瞭解下我的這個服務：簡歷指導和模擬面試，已經幫助幾個小夥伴拿到大廠的 offer 了。

我是yes，從一點點到億點點我們下篇見~