使用 MySQL 時，我們經常會遇到大表清理的情況。做大表清理的目的，一般是為了減表空間使用，提高表的操作性能。今天來聊一聊怎樣做大表清理。

一、SQL 清除

最直接的方式就是使用 delete 語句來刪除，可以使用主鍵：

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

delete 語句存在的問題是 InnoDB 引擎只會把 SQL 中刪除的記錄標記為刪除，并不會回收磁盤空間，也就是說磁盤數據文件大小并不會減小。當然刪除的這條數據在磁盤文件中的位置是可以復用的，比如刪除一條 id 為 4 的記錄，就可以成功插入一條 id 為 4 的記錄。

delete 語句刪除還有一個缺點是會留下大量磁盤碎片，影響索引性能。

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

二、邏輯刪除

既然使用 delete 語句做清理不能釋放表空間，那我們不如對數據做邏輯刪除。

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

這樣做的好處是并不會留下磁盤碎片，對索引性能沒有影響。但也存在缺點，那就是歸檔的時候需要對所有數據的刪除標識（del_flag）做判斷。

三、使用分區表

如果業務上沒有特殊要求，可以使用分區表，對分區直接做清理。比如以月為單位創建分區，對三個月以上的表做歸檔后直接把分區表 drop 掉。

ALTER TABLE test1 DROP PARTITION part202503;

分區表清理適合用于定期清理的場景，而且分區鍵必須要跟清理條件相符合，每月按周、按月、按季度，建表之前需要提前規劃好清理策略。

如果分區鍵需要按照業務屬性（比如身份證號）來定義，按照分區清理可能就不合適了。

四、使用臨時表

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

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

RENAME 語句可以防止其他會話再操作 test1 表，所以這個過程不會有并發問題。

但是在寫入頻率高的情況下，如果服務不中斷，并不能保證執行備份語句和 rename 語句之間沒有數據寫入。

五、重建表

為了避免上一節存在的問題，可以使用重建表的語句：

alter table test1 engine=InnoDB

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

1. 建立一個臨時文件，掃描 test1 表的所有記錄并生成 B+ 樹，存儲到臨時文件中；

2. 生成臨時文件的過程中，對 test1 的所有增刪改操作記錄到一個日志文件中；

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

4. 用臨時文件替換test1 表的數據文件。

六、使用歸檔工具

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

七、總結

大表清理是工作中經常遇到的情況，大表清理的方法有很多，可以根據自己實際的業務場景選擇合理的清理方式，無論選擇哪一種方案，都要注意一下三點：

1. 清理之前做好數據備份；

2. 清理過程要評估是否對業務有影響，是否會中段業務；

3. 確定好清理周期。