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

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

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

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

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

解決方案

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

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

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

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

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

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;

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

到此這篇關于mysql 大表批量刪除大量數據的實現方法的文章就介紹到這了,更多相關mysql 大表批量刪除內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！