MySQL 上大量刪除的技巧

在「大表删除数据的思路」這篇提到 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 是相同的。

This entry was posted in Computer, Database, Murmuring, MySQL, Software. Bookmark the permalink.

One Response to MySQL 上大量刪除的技巧

  1. Pingback: 網站製作學習誌 » [Web] 連結分享

Leave a Reply

Your email address will not be published. Required fields are marked *