MySQL 8.0.28中高效查询同名重复用户数据的优化方法咨询

针对你需要查询users表中first_name和last_name重复的全部字段的需求，结合MySQL 8.0.28的特性，推荐以下两种高效规范的方案，核心是利用联合索引避免无意义的计算：

1. 优先推荐：窗口函数方案 #

MySQL 8.0支持窗口函数，可在一次扫描中完成分组计数，无需额外子查询关联，且完全符合ONLY_FULL_GROUP_BY规则：

第一步：创建联合索引（关键优化） #

先给first_name和last_name创建联合索引，让后续查询直接利用索引加速：

CREATE INDEX idx_users_first_last ON users(first_name, last_name);

第二步：窗口函数查询语句 #

SELECT *
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY first_name, last_name) AS duplicate_count
    FROM users
) AS user_with_duplicate
WHERE duplicate_count > 1;

优势：

• 窗口函数COUNT(*) OVER (PARTITION BY ...)会基于联合索引快速分组计数，避免全表扫描
• 无需拼接字符串或嵌套关联，逻辑清晰，性能最优
• 完全兼容ONLY_FULL_GROUP_BY模式

2. 备选：JOIN关联分组结果方案 #

如果更习惯用子查询关联的方式，也可以先找出重复的姓名组合，再关联原表获取所有字段：

SELECT u.*
FROM users u
JOIN (
    SELECT first_name, last_name
    FROM users
    GROUP BY first_name, last_name
    HAVING COUNT(*) > 1
) AS duplicate_groups
ON u.first_name = duplicate_groups.first_name 
AND u.last_name = duplicate_groups.last_name;

优势：

• 子查询的GROUP BY会利用联合索引快速筛选出重复的姓名组合
• 关联操作同样依赖联合索引，比CONCAT方案效率高很多
• 同样无需关闭ONLY_FULL_GROUP_BY

为什么你的原有方案效率低？ #

• 最初的EXISTS关联方案：如果没有索引，会触发嵌套循环逐行比对，8000条数据会产生大量重复计算
• CONCAT方案：因为使用字符串拼接函数，MySQL无法利用first_name和last_name的索引，必须逐行计算拼接结果，导致性能损耗

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