无法使用DISTINCT/GROUP BY时的高性能SQL去重实现方案

性能差的根因 #

你写的NOT EXISTS逻辑本身是正确的，100条数据就跑5秒的核心原因是缺少对应索引，导致子查询走全表扫描做嵌套判断，先做索引优化就能解决99%的性能问题：

-- 建立email、id的联合索引，字段顺序不要调换
CREATE INDEX idx_contacts_email_id ON contacts(email, id);

加完索引后，子查询判断「同email下是否存在更小id」的操作会直接走索引二分查找，不需要遍历全表，哪怕是百万级数据量，查询耗时也会降到毫秒级，不需要修改你原有的SQL逻辑。

其他可替代DISTINCT/GROUP BY的实现方案 #

如果需要换写法，以下两种方案性能表现也很优秀，均满足不使用DISTINCT/GROUP BY的要求：

1. 窗口函数方案（可读性最佳）
   用ROW_NUMBER()窗口函数按email分区，给同分区内的记录按id从小到大排序打标，只取每个分区排序第一的记录即可：

   SELECT id, email
   FROM (
       SELECT
           id,
           email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS row_rank
       FROM contacts
   ) ranked
   WHERE row_rank = 1;
   

   这个写法逻辑直观，在有联合索引的场景下性能和优化后的NOT EXISTS基本持平。

2. 左连接判空方案
   逻辑和NOT EXISTS完全等价，通过左连接关联同email下id更小的记录，关联结果为空的记录就是每个email对应的最小id记录：

   SELECT a.*
   FROM contacts a
   LEFT JOIN contacts b
   ON a.email = b.email AND a.id > b.id
   WHERE b.id IS NULL;
   

   这个写法同样依赖上面提到的(email, id)联合索引获得高性能，无索引场景下和原NOT EXISTS写法性能表现一致。

所有上述方案的性能基础都是(email, id)联合索引，没有索引的前提下，任何写法在数据量上涨后都会出现明显的性能下滑。

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