如何从现有表导入海量数据并去重以创建新表？

一、创建新表并一次性导入去重数据 #

如果新表尚未创建，直接在创建阶段完成去重导入是最高效的方式：

方法1：用DISTINCT整行去重

适用于整行重复的场景，直接提取源表中唯一的记录：

CREATE TABLE new_table AS
SELECT DISTINCT * FROM source_table;

若需针对特定字段去重（比如按user_id保留最新记录），调整查询逻辑即可：

CREATE TABLE new_table AS
SELECT * FROM source_table
WHERE (user_id, create_time) IN (
    SELECT user_id, MAX(create_time) FROM source_table GROUP BY user_id
);

方法2：用GROUP BY分组去重

适合需要按指定字段分组，同时对其他字段做聚合处理的场景：

CREATE TABLE new_table AS
SELECT col1, col2, MAX(col3) AS col3
FROM source_table
GROUP BY col1, col2;

这里按col1和col2去重，col3取最大值，可根据需求替换MAX为MIN、FIRST_VALUE等聚合函数。

二、新表已存在时，导入去重且不重复的数据 #

如果新表已经创建，需要导入源表中既无自身重复，又不与新表现有数据冲突的记录：

方法1：NOT EXISTS过滤重复

INSERT INTO new_table (col1, col2, col3)
SELECT DISTINCT col1, col2, col3
FROM source_table
WHERE NOT EXISTS (
    SELECT 1 FROM new_table
    WHERE new_table.col1 = source_table.col1
      AND new_table.col2 = source_table.col2 -- 此处为判断重复的关键字段
);

方法2：LEFT JOIN过滤

部分数据库对LEFT JOIN的查询优化更友好，适合超大规模数据：

INSERT INTO new_table (col1, col2, col3)
SELECT DISTINCT s.col1, s.col2, s.col3
FROM source_table s
LEFT JOIN new_table t ON s.col1 = t.col1 AND s.col2 = t.col2
WHERE t.col1 IS NULL;

三、海量数据场景的性能优化 #

1. 提前创建索引：在源表的去重关键字段（如user_id）和新表对应字段上建索引，能大幅提升去重查询速度。
2. 分批导入：避免一次性导入锁表或超时，按主键范围拆分批次：

   INSERT INTO new_table (col1, col2, col3)
   SELECT DISTINCT col1, col2, col3
   FROM source_table
   WHERE id BETWEEN 1 AND 100000 -- 按主键分批次
     AND NOT EXISTS (...)
   

3. 临时表中转：先将去重后的数据导入临时表，再同步到新表，减少主表锁竞争：

   CREATE TEMP TABLE temp_distinct AS SELECT DISTINCT * FROM source_table;
   INSERT INTO new_table SELECT * FROM temp_distinct;
   DROP TABLE temp_distinct;
   

4. 利用数据库特有特性：
   • MySQL：给新表的去重字段加唯一索引后，用ON DUPLICATE KEY UPDATE跳过重复：

     CREATE UNIQUE INDEX idx_unique_col ON new_table(col1, col2);
     INSERT INTO new_table (col1, col2, col3)
     SELECT col1, col2, col3 FROM source_table
     ON DUPLICATE KEY UPDATE 1=1; -- 无实际更新，仅跳过重复
     

   • PostgreSQL：用ON CONFLICT DO NOTHING直接跳过重复：

     INSERT INTO new_table (col1, col2, col3)
     SELECT col1, col2, col3 FROM source_table
     ON CONFLICT (col1, col2) DO NOTHING;
     

内容的提问来源于stack exchange，提问作者charan