MySQL 刪除重複數據實戰：臨時表法去除 10 倍冗余數據

項目背景與問題發現

Linux 性能採集項目中，發現入庫極慢。經定位，同一設備（conf_id）同一時間點（insert_time）存在大量重複行，導致表膨脹 10 倍（427,366 行 vs 去重後 42,387 行）。

表結構

CREATE TABLE perf_linux (
  id VARCHAR(255) PRIMARY KEY,
  conf_id VARCHAR(255) NOT NULL,
  insert_time DATETIME,
  cpu_usage FLOAT(11,2),
  memory_usage FLOAT(11,2),
  io_usage_write FLOAT(11,2),
  io_usage_read FLOAT(11,2)
);

解決思路：分組保留一條 → 臨時表重建

1. 建立結構相同的臨時表並複製數據

   CREATE TABLE perf_linux_t LIKE perf_linux;
   INSERT INTO perf_linux_t SELECT * FROM perf_linux;

2. 新增自增主鍵便於定位

   ALTER TABLE perf_linux_t
         ADD COLUMN auto_id INT NOT NULL AUTO_INCREMENT,
         DROP PRIMARY KEY,
         ADD PRIMARY KEY (auto_id);

3. 查詢每組最小 auto_id（保留第一條）

   SELECT MIN(auto_id) FROM perf_linux_t GROUP BY insert_time, conf_id;

4. 直接 DELETE 會報錯「You can't specify target table for update in FROM clause」，需再建一張中間表 tmp 存放保留行。
5. 將符合條件的行插入 tmp

   CREATE TABLE tmp LIKE perf_linux_t;
   INSERT INTO tmp SELECT * FROM perf_linux_t
   WHERE auto_id IN (SELECT MIN(auto_id) FROM perf_linux_t GROUP BY insert_time, conf_id);

6. 清理 tmp 多餘欄位，截斷原表，回寫數據，刪除中間表

   ALTER TABLE tmp DROP COLUMN auto_id, DROP PRIMARY KEY;
   TRUNCATE TABLE perf_linux;
   INSERT INTO perf_linux SELECT * FROM tmp;
   DROP TABLE tmp;
   DROP TABLE perf_linux_t;

結果驗證

數據量從 427,366 降至 42,387，冗余消除 90%，入庫速度顯著提升。但根因（採集端重複發送）仍需進一步排查。

替代方案（MySQL 8.0+ / MariaDB 10.2+）

支援 ROW_NUMBER() 窗口函數，單條 SQL 完成：

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY conf_id, insert_time ORDER BY id) AS rn
  FROM perf_linux
)
DELETE FROM perf_linux WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

最佳實踐建議

• 源頭治理：採集端去重或加冪等鍵。
• 表級約束：UNIQUE KEY uk_conf_time (conf_id, insert_time) 防重複寫入（需處理衝突邏輯）。
• 大表去重：分批次 DELETE ... LIMIT 10000 避免長事務鎖表。