介紹
你好。 我是ningenMe，網絡開發人員。
正如標題所說，我的故事是物理刪除MySQL中300億條記錄的故事。
我對此產生了興趣，所以我決定做一個提醒（說明）。
主頁 - 警報
我使用和維護的批處理服務器有一個定期進程，每天從 MySQL 收集上個月的數據。
通常這個過程會在1小時左右完成，但這次足足有7、8個小時才完成，而且警報還沒有停止彈出……
尋找原因
我嘗試重新啟動該進程並查看日誌，但沒有發現任何問題。
查詢已正確索引。 但當我思考問題出在哪裡時，我意識到數據庫的大小相當大。
hoge_table | 350'000'000 |350 億條記錄。 索引似乎工作正常，只是非常慢。
每月所需的數據收集量約為 12 條記錄。 看起來select命令花了很長時間，事務很長時間沒有執行。
DB
它本質上是一個每天增加約 400 個條目的表。 該數據庫原本應該只收集上個月的數據，因此預計它能夠承受這個數據量，但不幸的是，不包括旋轉操作。
這個數據庫不是我開發的。 我從另一位開發人員那裡接手了它，所以它仍然感覺像是技術債務。
有一天，每天插入的數據量變得越來越大，最終達到了極限。 假設在處理如此大量的數據時，有必要將它們分開，但不幸的是，這並沒有做到。
然後我就開始行動了。
更正
減小數據庫本身的大小並減少處理它的時間比改變邏輯本身更為合理。
如果你刪除300億條記錄，情況應該會發生很大的變化，所以我決定這樣做……呃，我認為這一定可以。
動作1
準備好可靠的備份後，我終於開始發送請求。
「發送請求」
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';“……”
“……”
“嗯……沒有回答。 也許這個過程需要很長時間？” — 我想，但為了以防萬一，我查看了 grafana，發現磁盤負載增長得非常快。
“危險。”我又想了想，立刻停止了這個請求。
動作2
分析完所有內容後，我意識到數據量太大，無法一次性刪除所有內容。
我決定編寫一個可以刪除大約 1 條記錄的腳本並啟動它。
「我執行劇本」
“現在這肯定會起作用，”我想。
動作3
第二種方法有效，但結果非常耗費勞動力。
如果要小心翼翼地做好每件事，沒有不必要的緊張，大約需要兩週的時間。 但這個場景仍然不能滿足業務需求，所以我們不得不放棄它。
所以我決定這樣做：
複製表並重命名
從上一步中，我意識到刪除如此大量的數據會產生同樣大的負載。 因此，我決定使用插入從頭開始創建一個新表，並將要刪除的數據移動到其中。
| hoge_table | 350'000'000|
| tmp_hoge_table | 50'000'000|如果將新表設為與上面相同的大小，數據處理速度也應該會快 1/7。
創建表並重命名後，我開始將其用作主表。 現在，如果我刪除包含 300 億條記錄的表，一切都應該沒問題。
我發現截斷或刪除產生的開銷比刪除更少，並決定使用此方法。
執行
「發送請求」
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';
“……”
“……”
“嗯……？”
動作4
本以為之前的想法可行，但是發送插入請求後，出現了多個錯誤。 MySQL 並不寬容。
我已經很累了，我開始想我不想再這樣做了。
我坐下來想了想，意識到也許一次插入查詢太多了......
我嘗試發送一個插入請求，獲取數據庫應在 1 天內處理的數據量。 發生了！
好吧，之後我們繼續發送相同數量數據的請求。 由於我們需要刪除一個月的數據，因此我們重複此操作大約 35 次。
重命名表
幸運之神眷顧了我：一切都很順利。
警報失踪
批處理速度有所提高。
以前這個過程需要大約一個小時，現在大約需要兩分鐘。
當我確定所有問題都解決後，我刪除了300億條記錄。 我刪除了桌子，感覺重獲新生。
概括
我意識到批處理中缺少旋轉處理，這是主要問題。 這種架構錯誤會導致時間的浪費。
從數據庫中刪除記錄時，您是否考慮過數據複製期間的負載？ 我們不要讓 MySQL 超載。
精通數據庫的人肯定不會遇到這樣的問題。 對於你們其他人，我希望這篇文章有用。
謝謝閱讀！
如果您告訴我們您是否喜歡這篇文章，翻譯是否清晰，對您是否有用，我們將非常高興？
來源： www.habr.com