子查询中使用groupBy的两种SQL查询，哪种性能更优？

已知columns表与tasks表为一对多关系（一个Column对应多个Tasks，一个Task归属一个Column），以下针对你提出的两种查询的性能分析做验证：

第一种查询 #

SELECT id,
       name,
       color,
       created_at,
       CASE
           WHEN jt.tc IS NULL THEN 0
           ELSE jt.tc
       END
FROM columns AS c1
LEFT JOIN
  (SELECT count(*) AS tc,
          column_id
   FROM tasks AS t
   GROUP BY column_id) AS jt ON c1.id=jt.column_id
WHERE board_id = 'some id here';

你的分析完全正确：子查询jt会对整个tasks表的所有记录进行分组统计。当tasks表数据量极大时，全表分组计算的开销会非常高，直接导致查询速度急剧下降。

第二种查询 #

SELECT id,
       name,
       color,
       created_at,
       CASE
           WHEN jt.tc IS NULL THEN 0
           ELSE jt.tc
       END
FROM columns AS c1
LEFT JOIN
  (SELECT count(*) AS tc,
          column_id
   FROM tasks AS t
   LEFT JOIN columns c ON t.column_id = c.id
   WHERE c.board_id = 'some id here'
   GROUP BY column_id) AS jt ON c1.id=jt.column_id
WHERE board_id = 'some id here';

你的分析同样准确：子查询jt通过关联columns表并添加c.board_id = 'some id here'的过滤条件，只会统计属于目标board下的tasks数据，大幅缩小了分组计算的数据范围，能有效降低查询的性能开销。

补充优化建议 #

还可以进一步简化查询逻辑，不需要在子查询里关联columns表，直接利用tasks表的column_id关联已过滤后的columns结果，写法更简洁，数据库优化器也更容易生成高效的执行计划：

SELECT c1.id,
       c1.name,
       c1.color,
       c1.created_at,
       COALESCE(COUNT(t.id), 0) AS tc
FROM columns AS c1
LEFT JOIN tasks t ON c1.id = t.column_id
WHERE c1.board_id = 'some id here'
GROUP BY c1.id, c1.name, c1.color, c1.created_at;

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