使用 MySQL 時(shí)，我們經(jīng)常會(huì)遇到大表清理的情況。做大表清理的目的，一般是為了減表空間使用，提高表的操作性能。今天來(lái)聊一聊怎樣做大表清理。

一、SQL 清除

最直接的方式就是使用 delete 語(yǔ)句來(lái)刪除，可以使用主鍵：

delete from test1 where id in(...) order by id;

delete 語(yǔ)句存在的問(wèn)題是 InnoDB 引擎只會(huì)把 SQL 中刪除的記錄標(biāo)記為刪除，并不會(huì)回收磁盤空間，也就是說(shuō)磁盤數(shù)據(jù)文件大小并不會(huì)減小。當(dāng)然刪除的這條數(shù)據(jù)在磁盤文件中的位置是可以復(fù)用的，比如刪除一條 id 為 4 的記錄，就可以成功插入一條 id 為 4 的記錄。

delete 語(yǔ)句刪除還有一個(gè)缺點(diǎn)是會(huì)留下大量磁盤碎片，影響索引性能。

注意： 1.使用 delete 語(yǔ)句時(shí)可以使用 order by 對(duì)刪除條件進(jìn)行排序，這樣可以保證刪除順序，避免全表掃描； 2.刪除之前要做備份。

二、邏輯刪除

既然使用 delete 語(yǔ)句做清理不能釋放表空間，那我們不如對(duì)數(shù)據(jù)做邏輯刪除。

//del_flag=1 表示邏輯刪除
update test1 set del_flag = '1' where id in(...);

這樣做的好處是并不會(huì)留下磁盤碎片，對(duì)索引性能沒(méi)有影響。但也存在缺點(diǎn)，那就是歸檔的時(shí)候需要對(duì)所有數(shù)據(jù)的刪除標(biāo)識(shí)（del_flag）做判斷。

三、使用分區(qū)表

如果業(yè)務(wù)上沒(méi)有特殊要求，可以使用分區(qū)表，對(duì)分區(qū)直接做清理。比如以月為單位創(chuàng)建分區(qū)，對(duì)三個(gè)月以上的表做歸檔后直接把分區(qū)表 drop 掉。

ALTER TABLE test1 DROP PARTITION part202503;

分區(qū)表清理適合用于定期清理的場(chǎng)景，而且分區(qū)鍵必須要跟清理?xiàng)l件相符合，每月按周、按月、按季度，建表之前需要提前規(guī)劃好清理策略。

如果分區(qū)鍵需要按照業(yè)務(wù)屬性（比如身份證號(hào)）來(lái)定義，按照分區(qū)清理可能就不合適了。

四、使用臨時(shí)表

MySQL 官方文檔給的一種刪除方式是先把不刪除的數(shù)據(jù)備份到一張臨時(shí)表，然后再把原表改名，把臨時(shí)表改成原表名字，最后 drop 掉原表。

//1.備份數(shù)據(jù)到臨時(shí)表
INSERT INTO test1_copy SELECT * FROM test1 WHERE id in(...) ;
//2.把原表改名，把臨時(shí)表改名為原表名字
RENAME TABLE test1 TO test1_old, test1_copy TO test1_copy;
//3.刪除原表
DROP TABLE test1_old;

RENAME 語(yǔ)句可以防止其他會(huì)話再操作 test1 表，所以這個(gè)過(guò)程不會(huì)有并發(fā)問(wèn)題。

但是在寫入頻率高的情況下，如果服務(wù)不中斷，并不能保證執(zhí)行備份語(yǔ)句和 rename 語(yǔ)句之間沒(méi)有數(shù)據(jù)寫入。

五、重建表

為了避免上一節(jié)存在的問(wèn)題，可以使用重建表的語(yǔ)句：

alter table test1 engine=InnoDB

在 MySQL 5.6 之后，支持 Online DDL，所以 SQL 執(zhí)行過(guò)程中，test1 表依然可以進(jìn)行增刪改操作，這些操作會(huì)記錄在日志文件中，重建表完成后，在新表上做重放，因此不用擔(dān)心丟失數(shù)據(jù)。Online DDL 重建表的流程如下：

1. 建立一個(gè)臨時(shí)文件，掃描 test1 表的所有記錄并生成 B+ 樹(shù)，存儲(chǔ)到臨時(shí)文件中；

2. 生成臨時(shí)文件的過(guò)程中，對(duì) test1 的所有增刪改操作記錄到一個(gè)日志文件中；

3. 臨時(shí)文件生成后，將日志文件中的操作在臨時(shí)文件做重放，這樣臨時(shí)文件的數(shù)據(jù)跟 test1 數(shù)據(jù)文件中數(shù)據(jù)邏輯上相同；

4. 用臨時(shí)文件替換test1 表的數(shù)據(jù)文件。

六、使用歸檔工具

可以考慮使用歸檔工具比如 Percona Toolkit。

七、總結(jié)

大表清理是工作中經(jīng)常遇到的情況，大表清理的方法有很多，可以根據(jù)自己實(shí)際的業(yè)務(wù)場(chǎng)景選擇合理的清理方式，無(wú)論選擇哪一種方案，都要注意一下三點(diǎn)：

1. 清理之前做好數(shù)據(jù)備份；

2. 清理過(guò)程要評(píng)估是否對(duì)業(yè)務(wù)有影響，是否會(huì)中段業(yè)務(wù)；

3. 確定好清理周期。