mysql 大表批量刪除大量數據的實現方法

Posted on by

問題參考自：https://www.zhihu.com/question/440066129/answer/1685329456 ，mysql中，一張表裡有3億數據，未分表，其中一個字段是企業類型，企業類型是一般企業和個體戶，個體戶的數據量差不多占50%，根據條件把個體戶的行都刪掉。請問如何操作？答案為個人原創

假設表的引擎是 Innodb， MySQL 5.7+

刪除一條記錄，首先鎖住這條記錄，數據原有的被廢棄，記錄頭發生變化，主要是打上瞭刪除標記。也就是原有的數據 deleted_flag 變成 1，代表數據被刪除。但是數據沒有被清空，在新一行數據大小小於這一行的時候，可能會占用這一行。這樣其實就是存儲碎片。

之後，相關數據的索引需要更新，清除這些數據。並且，會產生對應的 binlog 與 redolog 日志。
如果 delete 的數據是大量的數據，則會：

  • 如果不加 limit 則會由於需要更新大量數據，從而索引失效變成全掃描導致鎖表，同時由於修改大量的索引，產生大量的日志，導致這個更新會有很長時間，鎖表鎖很長時間，期間這個表無法處理線上業務。
  • 由於產生瞭大量 binlog 導致主從同步壓力變大
  • 由於標記刪除產生瞭大量的存儲碎片。由於 MySQL 是按頁加載數據，這些存儲碎片不僅大量增加瞭隨機讀取的次數，並且讓頁命中率降低，導致頁交換增多。
  • 由於產生瞭大量日志，我們可以看到這張表的占用空間大大增高。

解決方案

我們很容易想到，在 delete 後加上 limit 限制控制其數量，這個數量讓他會走索引，從而不會鎖整個表。

但是，存儲碎片，主從同步，占用空間的問題並沒有解決。可以在刪除完成後，通過如下語句，重建表：

alter table 你的表 engine=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

註意這句話其實就是重建你的表，雖然你的表的引擎已經是 innodb 瞭，加上後面的, ALGORITHM=INPLACE, LOCK=NONE 可以不用鎖表就重建表。

還有一種方案是，新建一張同樣結構的表，在原有表上加上觸發器：

create trigger person_trigger_update AFTER UPDATE on 原有表 for each row 
begin set @x = "trigger UPDATE";
Replace into 新表 SELECT * from 原有表 where 新表.id = 原有表.id;
END IF;
end;

這樣可以保證線上業務有新數據會同步。之後，將所有企業類型的數據，插入新表，同時如果已存在則證明發生瞭更新同步就不插入。個體戶數據由於業務變化，並不在這個表上更新，所以這樣通過瞭無表鎖同步實現瞭大表的數據清理

到此這篇關於mysql 大表批量刪除大量數據的實現方法的文章就介紹到這瞭,更多相關mysql 大表批量刪除內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：