MySQL含GROUP BY的SELECT语句返回多余单条记录问题排查

我有一张存储文件列表及对应文件大小的Files表，需求是筛选出文件名与文件大小均重复的文件记录，最初编写的SELECT语句如下：

SELECT FileName, FileSize FROM Files WHERE FileName IN 
    (
    SELECT FileName FROM Files WHERE FileSize > 10240 
    GROUP BY FileName, FileSize HAVING Count(*) > 1
    )
ORDER BY FileName, FileSize;

梳理返回结果时发现异常记录：

022704.jpg  40960
022704.jpg  42080
022704.jpg  42080
022704.jpg  42080
022704.jpg  42080

其中第一条记录的文件大小和其余同文件名记录不一致，执行如下查询验证：

SELECT FileName, FileSize from Files WHERE FileName = '022704.jpg' AND FileSize = 40960;

验证结果确认，该文件名+文件大小的组合仅存在1条记录，不符合筛选重复组合的预期。

尝试自行修改为如下SQL后，出现严重性能问题，执行5分钟仍未返回结果：

SELECT FileName, FileSize FROM Files WHERE FileName IN 
    (
    SELECT FileName FROM Files WHERE FileSize > 10024 
    GROUP BY FileName, FileSize HAVING Count(*) > 1
    )
AND FileSize in 
    (
    SELECT FileSize FROM Files WHERE FileSize > 10024 
    GROUP BY FileName, FileSize HAVING Count(*) > 1
    )
ORDER BY FileName, FileSize;

• 初始SQL核心问题：子查询虽然按FileName, FileSize双字段分组筛选重复组合，但最终仅返回FileName字段供外层IN判断。外层逻辑等价于「只要文件名存在任意重复的大小记录，就返回该文件名下所有大小的记录」，完全没有校验文件大小是否属于重复组合。示例中022704.jpg因存在大小为42080的重复记录命中子查询，连带唯一的40960大小记录也被错误返回。
• 自行修改的SQL问题：一是逻辑依然错误，两个独立IN子查询分别判断文件名、文件大小是否存在重复，未校验两个字段是否属于同一组重复组合，会返回大量误匹配数据；二是两个子查询会重复扫描全表做匹配，数据量稍大就会出现执行效率极低的问题；另外还误将文件大小阈值从10240写为10024，会引入不符合大小要求的脏数据。

多列匹配写法（兼容绝大多数SQL引擎） #

直接使用多字段元组匹配语法，子查询仅需执行一次，同时返回重复的文件名+文件大小组合，外层同时匹配两个字段，逻辑准确且性能优异：

SELECT FileName, FileSize 
FROM Files 
WHERE (FileName, FileSize) IN (
    SELECT FileName, FileSize 
    FROM Files 
    WHERE FileSize > 10240 
    GROUP BY FileName, FileSize 
    HAVING COUNT(*) > 1
)
ORDER BY FileName, FileSize;

窗口函数写法（支持MySQL8+、PostgreSQL、SQL Server等现代SQL引擎） #

大表场景下性能更优，通过分区计数直接标记每组(文件名+文件大小)的重复次数，无需二次匹配分组结果：

SELECT FileName, FileSize
FROM (
    SELECT 
        FileName, 
        FileSize,
        COUNT(*) OVER(PARTITION BY FileName, FileSize) AS repeat_cnt
    FROM Files
    WHERE FileSize > 10240
) t
WHERE repeat_cnt > 1
ORDER BY FileName, FileSize;

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