MySQL开启only_full_group_by时高效查询专属任务的优化方案问询

问题背景 #

现有test表数据如下：

需求：查询仅分配给指定用户的task值——例如查询user=4时返回21、22；查询user=3时返回23。要求不能关闭only_full_group_by（不符合SQL标准，易产生不可预期结果）。

更高效的实现方案 #

方案1：使用NOT EXISTS（推荐，性能最优） #

利用NOT EXISTS直接排除存在其他用户的task，若test表有(task, user)联合索引，可大幅提升查询效率：

SELECT DISTINCT t.task
FROM test t
WHERE t.user = 4
AND NOT EXISTS (
    SELECT 1
    FROM test t2
    WHERE t2.task = t.task
    AND t2.user != 4
)

逻辑说明：

1. 先筛选出当前用户（示例为4）拥有的所有task
2. 排除掉那些存在其他用户也持有的task
3. 去重后得到仅当前用户持有的task列表

方案2：使用窗口函数（单次表扫描） #

通过窗口函数一次性统计每个task的相关数据，再筛选符合条件的结果：

SELECT DISTINCT task
FROM (
    SELECT task,
           COUNT(DISTINCT user) OVER (PARTITION BY task) AS user_count,
           MAX(CASE WHEN user = 4 THEN 1 ELSE 0 END) OVER (PARTITION BY task) AS has_target_user
    FROM test
) t
WHERE user_count = 1
AND has_target_user = 1

逻辑说明：

1. 子查询中按task分组，统计每个task的不同用户数user_count，同时标记该task是否包含目标用户has_target_user
2. 外层筛选出用户数为1（仅一个用户持有）且包含目标用户的task

对比原方案的优势 #

原有子查询关联方案需要两次扫描表（一次分组统计、一次关联筛选），上述两种方案：

• NOT EXISTS方案可利用索引快速定位数据，避免不必要的全表扫描
• 窗口函数方案仅需一次全表扫描，比原方案的两次扫描更高效

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