mysql 大表批量刪除大量數(shù)據(jù)的實(shí)現(xiàn)方法

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

假設(shè)表的引擎是 Innodb， MySQL 5.7+

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

之后，相關(guān)數(shù)據(jù)的索引需要更新，清除這些數(shù)據(jù)。并且，會(huì)產(chǎn)生對(duì)應(yīng)的 binlog 與 redolog 日志。
如果 delete 的數(shù)據(jù)是大量的數(shù)據(jù)，則會(huì)：

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

解決方案

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

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

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

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

還有一種方案是，新建一張同樣結(jié)構(gòu)的表，在原有表上加上觸發(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è)務(wù)有新數(shù)據(jù)會(huì)同步。之后，將所有企業(yè)類型的數(shù)據(jù)，插入新表，同時(shí)如果已存在則證明發(fā)生了更新同步就不插入。個(gè)體戶數(shù)據(jù)由于業(yè)務(wù)變化，并不在這個(gè)表上更新，所以這樣通過了無表鎖同步實(shí)現(xiàn)了大表的數(shù)據(jù)清理

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