多聚合操作下SQL子查询性能优化求助

阿华AIGC实验室

2026-5-25

优化按Project分组的多表聚合SQL性能指南

嘿，碰到SQL性能拉胯的问题确实头疼！我帮你拆解下可能的原因和优化方向，你可以照着试试：

一、先定位性能瓶颈：用执行计划找问题

第一步先搞清楚到底慢在哪，运行下面的命令看执行计划：

EXPLAIN ANALYZE 你的原SQL语句;

重点看这几个点：

有没有出现Seq Scan（全表扫描），如果有说明缺少合适的索引；
有没有Hash Join或Nested Loop耗时过长，可能是JOIN逻辑有问题；
聚合阶段（GroupAggregate）的行数是不是远超预期，说明数据量没控制住。

二、最常见的性能问题及优化方案

1. 缺少针对性索引，导致全表扫描

如果你的基础表没有针对project字段的索引，每次分组聚合都要扫遍整个表，数据量大的时候肯定慢。

优化方法：给每个需要聚合的表建覆盖索引，包含分组字段和所有聚合用到的字段，比如：

-- 给table1建覆盖索引
CREATE INDEX idx_table1_project_metrics ON table1(project, count, opened, clicked, started_watching, views);

-- 其他表同理，替换table名称即可
CREATE INDEX idx_table2_project_metrics ON table2(project, count, opened, clicked, started_watching, views);

这样数据库可以直接从索引里拿到所有需要的数据，不用回表查询，速度会快很多。

2. 子查询未预聚合，导致外层处理大量原始数据

如果你的子查询是直接把多个表的原始数据UNION ALL后再聚合，相当于让数据库处理几倍于单表的原始数据，性能自然差。

优化方法：先在每个子查询里按project预聚合，再UNION ALL，减少外层需要处理的数据量：

SELECT 
    project,
    SUM(sub.total_count) AS count,
    -- 注意：如果需要准确的平均值，要用加权平均（见下面的说明）
    AVG(sub.avg_opened) AS opened,
    AVG(sub.avg_clicked) AS clicked,
    AVG(sub.avg_started_watching) AS started_watching,
    SUM(sub.total_views) AS views
FROM (
    -- 表1先按project聚合
    SELECT 
        project,
        SUM(count) AS total_count,
        AVG(opened) AS avg_opened,
        AVG(clicked) AS avg_clicked,
        AVG(started_watching) AS avg_started_watching,
        SUM(views) AS total_views
    FROM table1
    GROUP BY project

    UNION ALL

    -- 表2同理预聚合
    SELECT 
        project,
        SUM(count) AS total_count,
        AVG(opened) AS avg_opened,
        AVG(clicked) AS avg_clicked,
        AVG(started_watching) AS avg_started_watching,
        SUM(views) AS total_views
    FROM table2
    GROUP BY project

    -- 其他表依次添加
) sub
GROUP BY project;

3. 平均值计算逻辑不准确且低效

直接用AVG(sub.opened)得到的是各表平均值的平均，不是所有数据的真实平均值（比如表A的project有100条数据，avg是5；表B的同project有200条数据，avg是6，直接AVG会得到5.5，但真实平均值是(1005+2006)/300≈5.67）。同时这种计算方式也需要处理更多中间数据。

优化方法：改用加权平均，在子查询里统计总和和行数，外层计算真实平均值：

SELECT 
    project,
    SUM(sub.total_count) AS count,
    -- 加权平均：总数值和 / 总行数和
    SUM(sub.opened_sum) / SUM(sub.opened_rows) AS opened,
    SUM(sub.clicked_sum) / SUM(sub.clicked_rows) AS clicked,
    SUM(sub.started_watching_sum) / SUM(sub.started_watching_rows) AS started_watching,
    SUM(sub.total_views) AS views
FROM (
    SELECT 
        project,
        SUM(count) AS total_count,
        SUM(opened) AS opened_sum,
        COUNT(opened) AS opened_rows, -- 注意：如果字段有NULL，要用COUNT(*)或COUNT(1)根据需求调整
        SUM(clicked) AS clicked_sum,
        COUNT(clicked) AS clicked_rows,
        SUM(started_watching) AS started_watching_sum,
        COUNT(started_watching) AS started_watching_rows,
        SUM(views) AS total_views
    FROM table1
    GROUP BY project

    UNION ALL

    SELECT 
        project,
        SUM(count) AS total_count,
        SUM(opened) AS opened_sum,
        COUNT(opened) AS opened_rows,
        SUM(clicked) AS clicked_sum,
        COUNT(clicked) AS clicked_rows,
        SUM(started_watching) AS started_watching_sum,
        COUNT(started_watching) AS started_watching_rows,
        SUM(views) AS total_views
    FROM table2
    GROUP BY project
) sub
GROUP BY project;