從 MySQL 5.7 表中刪除大量行 + ibdata 增加問題

我在 Windows 上使用 MySQL 5.7。

我有幾個表可以清理數據。最大表的 ibd 文件大小為 300GB，有近 15 億行。我需要在其中保留大約 2.9 億行。所以需要刪除一個非常大的塊。

從閱讀文件有兩種方法

1. 之後的 DELETE 語句和 OPTIMIZE TABLE
2. 將數據複製到新表，刪除舊表並重命名新表。

在這種情況下，第二種選擇似乎要好得多，但是有什麼潛在的問題需要我們尋找嗎？停機時間並不是真正的問題。

另一個問題，我看到大表上的刪除語句（刪除超過 100 萬行）可能會卡住，然後導致 ibdata 增長（數百 MB），即使 innodb_file_per_table 選項已打開。

我認為這與臨時表有關，但找不到解釋。有任何想法嗎？

最好的方法是創建一個包含 290M 行的表，然後使用 RENAME TABLE 並 DROP 舊的 1500M 行表。這就像您的選擇 2，只是它在刪除之前重命名。這種重新排列避免了失去數據的微小機會。

您的選擇 1 非常糟糕，因為它將保存 1210M 行以供可能的恢復或ROLLBACK. 非常昂貴，非常慢，使用大量磁碟（某處）。

我在http://mysql.rjweb.org/doc.php/deletebig中討論了這個和其他選項

這也顯示了“分塊” a DELETE(or UPDATE) 的方法，這樣它就不會膨脹 undo/redo 日誌。

類似於 Rick James 的回答，我經常使用的一個方法是 Dump⇢Truncate⇢Insert：

0. 禁用外鍵檢查（如果適用）：

SET FOREIGN_KEY_CHECKS=0;

1. 使用WHERE子句轉儲表：

mysqldump -u {username} -p {dbname} {tablename} --where="created_at >= '2021-01-01 00:00:00'" > tablename.sql

2. 截斷表：

TRUNCATE TABLE {tablename};

3. 重新載入數據：

mysql -u {username} -p {dbname} < tablename.sql

4. 重新啟用外鍵檢查（如果適用）：

SET FOREIGN_KEY_CHECKS=1;

這裡需要注意的是，沒有簡單的方法可以從表的前一個化身中反向填充更多數據。

引用自：https://dba.stackexchange.com/questions/298276