表 data_content_152 5000 萬行,實際唯一身份證號 SFZHM 僅 180 萬,需高效去重保留最小 id。
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);
CREATE TABLE `res` (
`id` INT(11),
`sfz` CHAR(20)
) ENGINE=MyISAM; -- 或 InnoDB,視後續使用
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.*,目標表結構同步調整。| 寫法 | 執行計畫 | 複雜度 |
|---|---|---|
| DELETE ... WHERE id NOT IN (SELECT ...) | Dependent Subquery,每行執行一次子查詢 | O(N²) |
| DELETE ... WHERE NOT EXISTS (...) | 同上,MySQL 5.6+ 可優化為 Semi-Join | O(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 釋放。