如何优化识别40万行数据表重复记录的SQL查询语句

1. 索引优化（核心解决点） #

你当前的三个字段是单独建立的单值索引，无法匹配多字段等值匹配的查询需求，这是执行计划走全表扫描的核心原因。需要创建联合覆盖索引：

CREATE INDEX idx_dup_match ON dupe_table (first_name, last_name, postal_code, id);

• 前三个字段按你匹配的顺序放在索引前缀，满足等值查询的索引命中规则
• 末尾附加主键id，构成覆盖索引，查询时不需要回表读取原数据，性能提升10倍以上

2. 查询逻辑优化 #

原自连接逻辑是全表级别的笛卡尔积匹配，时间复杂度为O(n²)，40万条数据的计算量极大，推荐改用「先筛重复组再配对」的逻辑，性能提升非常明显：

优化后写法1（分组先筛重复组） #

WITH dup_groups AS (
    SELECT 
        first_name, 
        last_name, 
        postal_code, 
        GROUP_CONCAT(id ORDER BY id SEPARATOR ',') AS id_list,
        COUNT(*) AS dup_count
    FROM dupe_table
    GROUP BY first_name, last_name, postal_code
    HAVING dup_count >= 2
)
SELECT 
    t1.id AS id1,
    t2.id AS id2,
    3 AS weight
FROM dupe_table t1
JOIN dupe_table t2
  ON t1.first_name = t2.first_name
  AND t1.last_name = t2.last_name
  AND t1.postal_code = t2.postal_code
-- 仅关联已经确认是重复的组，避免全表匹配
JOIN dup_groups g
  ON t1.first_name = g.first_name
  AND t1.last_name = g.last_name
  AND t1.postal_code = g.postal_code
WHERE t1.id < t2.id;

如果你的数据库支持窗口函数，还可以进一步简化：

优化后写法2（窗口函数标记重复） #

WITH marked_dup AS (
    SELECT 
        id,
        first_name,
        last_name,
        postal_code,
        COUNT(*) OVER (PARTITION BY first_name, last_name, postal_code) AS dup_count
    FROM dupe_table
)
SELECT 
    t1.id id1, 
    t2.id id2, 
    3 weight
FROM marked_dup t1
JOIN marked_dup t2
  ON t1.first_name = t2.first_name
  AND t1.last_name = t2.last_name
  AND t1.postal_code = t2.postal_code
WHERE t1.dup_count >=2 AND t2.dup_count >=2 AND t1.id < t2.id;

3. 额外优化建议 #

• 如果数据重复率超过20%，可以先把dup_groups的结果导入临时表再做关联，避免占用原表的索引资源
• 如果需要返回的重复对数量极大，可以按id范围分批查询，比如每次加条件WHERE t1.id BETWEEN 1 AND 50000，避免一次性加载过多数据导致内存溢出

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