在 MySQL 日常維護中,刪除數據通常使用 DELETE 或 TRUNCATE。但當表數據量達到千萬甚至億級時,這兩條命令都會暴露嚴重效能問題:
lock_table_size 限制。MySQL 官方文檔針對大表刪除大量行給出了經典策略——不使用 DELETE,改用「建新表、拷貝保留數據、原子重命名、刪舊表」。
-- 1. 建立結構相同的空表
CREATE TABLE t_copy LIKE t;
-- 2. 僅插入需要保留的數據(可加 WHERE 條件)
INSERT INTO t_copy SELECT * FROM t WHERE keep_condition;
-- 3. 原子重命名:舊表改名,新表上位
RENAME TABLE t TO t_old, t_copy TO t;
-- 4. 異步刪除舊表(不阻塞業務)
DROP TABLE t_old;
RENAME TABLE 是原子操作,只持有極短暫的元數據鎖。INSERT 可控制事務大小,配合 LIMIT 分頁寫入。DROP TABLE 直接釋放磁盤空間,無碎片殘留。-- 假設主鍵為 id,每次處理 5000 行
INSERT INTO t_copy SELECT * FROM t
WHERE keep_condition AND id > last_max_id
ORDER BY id LIMIT 5000;
Percona Toolkit 中的 pt-archiver 專為歸檔/刪除大表設計,支援分批、檢查從庫延遲、自動建索引等企業級特性。
LOW_PRIORITY、QUICK、IGNORE 並不能本質解決大表刪除鎖表問題。DELETE ... LIMIT 1000 循環,仍會因頻繁掃描索引導致高 CPU。面對超大表部分數據刪除,「換表法」是經得起生產環境驗證的黃金方案。核心思想是用空間換時間、用元數據操作換數據操作,將長時間的行級鎖轉化為極短的表級元數據鎖,最大程度保障業務連續性。