如何优化两个逻辑相似仅聚合维度不同的SQL JOIN查询

你的原始写法确实存在冗余问题：两个子查询重复执行了表关联、条件过滤逻辑，相当于两次扫描全量符合条件的数据，数据规模大时性能损耗非常明显，可以通过以下方案优化：

方案1：窗口函数优化（适用支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

这是最优的实现方案，仅需一次表扫描即可得到全部结果，省去了第二次子查询和后续的关联操作：

SELECT 
    `description`,
    `object`,
    COUNT(`id`) AS `count_errors`,
    SUM(COUNT(`id`)) OVER (PARTITION BY `object`) AS `count_total`
FROM `metrics_event`
INNER JOIN `metrics_session` ON `metrics_event`.`session_id` = `metrics_session`.`id`
WHERE `metrics_session`.`training_id` = 4 
  AND `metrics_session`.`completed_at` IS NOT NULL
GROUP BY `object`, `description`
ORDER BY `count_errors` DESC

逻辑说明：先做一次表关联和条件过滤，按object+description分组统计每个组合的错误数count_errors，再通过窗口函数按object分区，将同一个object下所有分组的count_errors求和，直接得到该object的总错误数count_total，和你原始查询的输出结果完全一致。

方案2：兼容老版本数据库的优化方案（如MySQL 5.x不支持窗口函数） #

如果数据库不支持窗口函数，也可以提取公共数据集复用，避免重复关联扫描两张表：

-- 先提取符合条件的公共数据集，仅执行一次关联过滤
WITH filtered_event AS (
    SELECT 
        `metrics_event`.`id`,
        `metrics_event`.`description`,
        `metrics_event`.`object`
    FROM `metrics_event`
    INNER JOIN `metrics_session` ON `metrics_event`.`session_id` = `metrics_session`.`id`
    WHERE `metrics_session`.`training_id` = 4 
      AND `metrics_session`.`completed_at` IS NOT NULL
)
SELECT 
    obj_desc.description,
    obj_desc.object,
    obj_desc.count_errors,
    obj_total.count_total
FROM (
    SELECT description, object, COUNT(id) AS count_errors
    FROM filtered_event
    GROUP BY description, object
) AS obj_desc
JOIN (
    SELECT object, COUNT(id) AS count_total
    FROM filtered_event
    GROUP BY object
) AS obj_total ON obj_desc.object = obj_total.object
ORDER BY count_errors DESC

额外性能优化建议 #

• 给metrics_session创建联合索引 (training_id, completed_at, id)，可直接通过索引定位符合条件的会话记录，无需回表
• 给metrics_event创建联合索引 (session_id, object, description, id)，关联时可直接从索引中获取全部所需字段，避免全表扫描

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