萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

千萬級資料去重實戰:臨時表 + 索引 + 分批刪除

場景

表 data_content_152 5000 萬行,實際唯一身份證號 SFZHM 僅 180 萬,需高效去重保留最小 id。

三步走方案(實測 10 分鐘內完成)

步驟 1:建立去重鍵對應表(只掃描一次,利用索引)


CREATE TABLE tmp3 AS
SELECT MIN(id) AS col1 FROM data_content_152 GROUP BY SFZHM;
-- 若 SFZHM 有索引:掃描索引即可,秒級完成
-- 無索引:全表掃描,建議先加索引 ALTER TABLE data_content_152 ADD INDEX idx_sfzhm (SFZHM);

步驟 2:建立目標表結構(輕量,僅保留必要欄位)


CREATE TABLE `res` (
  `id` INT(11),
  `sfz` CHAR(20)
) ENGINE=MyISAM;  -- 或 InnoDB,視後續使用

步驟 3:JOIN 回原表抽取完整資料(或僅需欄位)


INSERT INTO res (id, sfz)
SELECT d.id, d.SFZHM
FROM data_content_152 d
JOIN tmp3 t ON d.id = t.col1;
  • 此步驅動表 tmp3(180 萬行),反查主鍵 id,極快。
  • 若需全欄位,改 SELECT d.*,目標表結構同步調整。

為何比 NOT IN / NOT EXISTS 快百倍?

寫法執行計畫複雜度
DELETE ... WHERE id NOT IN (SELECT ...)Dependent Subquery,每行執行一次子查詢O(N²)
DELETE ... WHERE NOT EXISTS (...)同上,MySQL 5.6+ 可優化為 Semi-JoinO(N log N)
本文 JOIN 方案驅動表 180 萬,反查主鍵,單次掃描O(N)

大表去重通用模板


-- 1. 確保去重鍵有索引
ALTER TABLE big_table ADD INDEX idx_dedup_key (dedup_key);

-- 2. 建立保留主鍵表
CREATE TABLE keep_ids AS
SELECT MIN(id) AS keep_id FROM big_table GROUP BY dedup_key;

-- 3. 分批刪除(避免長事務、複製表)
SET @batch = 5000;
REPEAT
  DELETE b FROM big_table b
  LEFT JOIN keep_ids k ON b.id = k.keep_id
  WHERE k.keep_id IS NULL
  LIMIT @batch;
UNTIL ROW_COUNT() = 0 END REPEAT;

-- 4. 清理
DROP TABLE keep_ids;

替代方案:新建表換名(停機窗口允許時最快)


CREATE TABLE new_table LIKE big_table;
ALTER TABLE new_table ADD UNIQUE INDEX uk_dedup (dedup_key);
INSERT IGNORE INTO new_table SELECT * FROM big_table;
RENAME TABLE big_table TO big_table_old, new_table TO big_table;
  • INSERT IGNORE 自動跳過重複鍵,單次全表掃描,無事務爆炸風險。
  • 需雙倍磁碟空間,完成後 DROP TABLE big_table_old 釋放。
copyright © 萬盛學電腦網 all rights reserved