怎么在mysql中批量刪除大量數(shù)據(jù)

怎么在mysql中批量刪除大量數(shù)據(jù)？針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

首先鎖住這條記錄，數(shù)據(jù)原有的被廢棄，記錄頭發(fā)生變化，主要是打上了刪除標(biāo)記。也就是原有的數(shù)據(jù) deleted_flag 變成 1，代表數(shù)據(jù)被刪除。但是數(shù)據(jù)沒(méi)有被清空，在新一行數(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ì)有很長(zhǎng)時(shí)間，鎖表鎖很長(zhǎng)時(shí)間，期間這個(gè)表無(wú)法處理線上業(yè)務(wù)。

• 由于產(chǎn)生了大量 binlog 導(dǎo)致主從同步壓力變大

• 由于標(biāo)記刪除產(chǎn)生了大量的存儲(chǔ)碎片。由于 MySQL 是按頁(yè)加載數(shù)據(jù)，這些存儲(chǔ)碎片不僅大量增加了隨機(jī)讀取的次數(shù)，并且讓頁(yè)命中率降低，導(dǎo)致頁(yè)交換增多。

• 由于產(chǎn)生了大量日志，我們可以看到這張表的占用空間大大增高。

解決方案

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

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

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;

關(guān)于怎么在mysql中批量刪除大量數(shù)據(jù)問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。