夢想的天空

paul

From :
http://blog.gslin.org/archives/2009/11/04/2146/

在「大表删除数据的思路」這篇提到 MySQL 刪除的技巧。
MySQL 的刪除不建議直接刪，也就是像這種 query 應該要避免：

DELETE FROM `table` WHERE `lastupdated_at` < 1234567890

因為在巨大的 table 裡面，這類的 query 可能會跑幾分鐘。
一般建議多幾筆 query 刪除：

DELETE FROM `table` WHERE `lastupdated_at` < 1234567890 ORDER BY `id` LIMIT 0, 10000

跑到適當地條件成立時為止。
會需要這樣設計，其中一個主要的原因是因為 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 是相同的。