[轉]MySQL 上大量刪除的技巧 at Gea-Suan Lin’s BLOG

MySQL 上大量刪除的技巧 at Gea-Suan Lin’s BLOG

在「大表删除数据的思路」這篇提到 MySQL 刪除的技巧。

MySQL 的刪除不建議直接刪，也就是像這種 query 應該要避免：

DELETE FROM `table` WHERE `lastupdated_at` <>

因為在巨大的 table 裡面，這類的 query 可能會跑幾分鐘。

一般建議多幾筆 query 刪除：

DELETE FROM `table` WHERE `lastupdated_at` <>

跑到適當地條件成立時為止。

會需要這樣設計，其中一個主要的原因是因為 MySQL 的 replication 架構：在 master 上面的寫入時是 multi-threading，但在 slave 上面的更新卻只有一個 thread。所以，如果有單一 query 跑太久，會造成有一段時間 master/slave 資料不一致。

另外一個可能的原因是 table 使用 MyISAM。因為 MyISAM 寫入時會鎖住 table，如果花太久時間會使得 SELECT 要等待 query 結束，這點在有 web 的服務要避免 (因為前端的 user 會卡住)。分成多次寫入可以讓 query 在兩次寫入中間取得資料。

原文的建議是將每個要刪除的 entry 都展開成一筆 DELETE，這個方法有不少壞處，應該要避免。

• 在 replication 架構下會產生大量的 binlog，雖然是徹底打散了，但反而大幅增加 client 與 server 之間的傳輸成本。
• 在 InnoDB 的表格裡，我們會把innodb_flush_log_at_trx_commit 設為 1 或 2，確保在 crash 時仍然可以回復資料，代價是每次更新時的 transaction log 都會 fsync()，強制寫回硬碟。如果有大量的 query 進來時，會產生很大量的 random write。

折衷的辦法是使用 LIMIT 限制數量，不過這樣還不夠，因為 MySQL 會認定這個 query 在 master 與 slave 上可能會有不同的結果 (即使兩邊資料一樣，底層的 data structure 可能不同，而造成 LIMIT 後出來的 Result Set 不一樣)，為了資料安全而決定切到 ROW mode。

所以另外加上 ORDER BY `id` 確保 master 與 slave 所取得的 entry 是相同的。