mysql中，一張表里有3億數(shù)據(jù)。

Mysql中，一張表里有3億數(shù)據(jù)，未分表，其中一個字段是企業(yè)類型，企業(yè)類型是一般企業(yè)和個體戶，個體戶的數(shù)據(jù)量差不多占50%，根據(jù)條件把個體戶的行都刪掉。請問如何操作？

福哥答案2021-01-09：

面試的時候，說的是分批刪除，沒讓面試官滿意。

網(wǎng)上答案:1：

Mysql數(shù)據(jù)庫因生產(chǎn)原因需要刪除大量數(shù)據(jù)，因數(shù)據(jù)量太大接近上億條，用常規(guī)delete刪除小數(shù)據(jù)可以，刪除幾千萬大數(shù)據(jù)量會非常慢，并且不會釋放出磁盤空間，還需要optimize或repair來壓縮數(shù)據(jù)表來釋放硬盤空間，時間更長，需要幾天時間，太慢了！因此采用新建表，導入無需刪除的數(shù)據(jù)，然后刪除老表，把新表改名為老表，步驟如下：

1、基于老表新建新表！

create table sell_new like sell;

2、插入數(shù)據(jù)（幾千萬的數(shù)據(jù)量一定要分批插入，一次50萬為最佳，畢竟mysql的數(shù)據(jù)處理能力有限），可以按ID查詢后插入！

insert into sell_new select * from sell where itemid>500000 and itemid<=5500000;

新表中只保留有用的數(shù)據(jù)，硬盤空間得以釋放！

3、drop刪除掉老表

drop table sell;

4、重命名新表為“sell”

alter table sell_new rename to sell;

以上是mysql數(shù)據(jù)庫上億級大數(shù)據(jù)如何快速刪除操作流程！

網(wǎng)上答案2：

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

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

之后，相關(guān)數(shù)據(jù)的索引需要更新，清除這些數(shù)據(jù)。并且，會產(chǎn)生對應的 binlog 與 redolog 日志。

如果 delete 的數(shù)據(jù)是大量的數(shù)據(jù)，則會：

1.如果不加 limit 則會由于需要更新大量數(shù)據(jù)，從而索引失效變成全掃描導致鎖表，同時由于修改大量的索引，產(chǎn)生大量的日志，導致這個更新會有很長時間，鎖表鎖很長時間，期間這個表無法處理線上業(yè)務。

2.由于產(chǎn)生了大量 binlog 導致主從同步壓力變大。

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

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

解決方案

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

但是，存儲碎片，主從同步，占用空間的問題并沒有解決?？梢栽趧h除完成后，通過如下語句，重建表：

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

注意這句話其實就是重建你的表，雖然你的表的引擎已經(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è)務有新數(shù)據(jù)會同步。之后，將所有企業(yè)類型的數(shù)據(jù)，插入新表，同時如果已存在則證明發(fā)生了更新同步就不插入。個體戶數(shù)據(jù)由于業(yè)務變化，并不在這個表上更新，所以這樣通過了無表鎖同步實現(xiàn)了大表的數(shù)據(jù)清理。

網(wǎng)上答案3：

刪除達標上的多行數(shù)據(jù)時，innodb會超出lock table size的限制，最小化的減少鎖表的時間的方案是：

1選擇不需要刪除的數(shù)據(jù)，并把它們存在一張相同結(jié)構(gòu)的空表里 。

2重命名原始表，并給新表命名為原始表的原始表名 。

3刪掉原始表 。

總結(jié)一下就是，當時刪除大表的一部分數(shù)據(jù)時可以使用 見新表，拷貝數(shù)據(jù)，刪除舊表，重命名的方法。

MySQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。