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

問題參考自: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!