如何在PostgreSQL中高效查找不区分大小写的重复邮箱记录？

你的原查询超时的核心原因是关联子查询会对每条记录触发一次全表扫描，25万条数据会产生25万次全表遍历，计算量直接拉到千万级，自然无法快速完成。以下是几个实用的解决思路：

1. 先分组筛选重复邮箱，再关联原表 #

先通过分组找出所有存在重复的小写邮箱，再关联原表获取完整记录，全程仅需两次全表扫描，性能大幅提升：

SELECT u.*
FROM user u
JOIN (
    SELECT LOWER(email) AS lower_email
    FROM user
    GROUP BY lower_email
    HAVING COUNT(*) > 1
) dup ON LOWER(u.email) = dup.lower_email

2. 添加函数索引加速查询 #

如果你的数据库支持函数索引（如MySQL 8.0+、PostgreSQL、Oracle），给LOWER(email)创建索引，让查询直接利用索引过滤，避免全表扫描：

-- MySQL/PostgreSQL 语法
CREATE INDEX idx_user_lower_email ON user(LOWER(email));

-- Oracle 语法
CREATE INDEX idx_user_lower_email ON user(LOWER(email));

创建索引后，无论是用上面的分组查询还是优化后的子查询，速度都会显著提升。

3. 利用数据库的不区分大小写排序规则（仅限MySQL） #

如果你的email字段使用的是不区分大小写的排序规则（如utf8_general_ci），可以直接对email分组，无需调用LOWER()函数：

SELECT u.*
FROM user u
JOIN (
    SELECT email
    FROM user
    GROUP BY email
    HAVING COUNT(*) > 1
) dup ON u.email = dup.email

注意：若排序规则为utf8_bin这类区分大小写的类型，此方法不生效。

额外建议：重复数据清理 #

如果需要清理重复数据，可以保留每个邮箱的一条记录（比如最小ID的记录），删除其他重复项：

-- MySQL 环境下保留最小ID，删除其他重复记录
DELETE u1
FROM user u1
JOIN user u2 ON LOWER(u1.email) = LOWER(u2.email)
WHERE u1.id > u2.id;

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