SQL多表关联分组聚合查询性能过慢问题咨询

耗时是否正常 #

5秒的耗时属于不正常范围。当前涉及的表总数据量并不大，核心的700万行GroupStepResults表在合理的索引和查询逻辑下，这类关联聚合查询的耗时应该控制在百毫秒级别。

核心问题原因 #

当前查询的性能瓶颈主要来自两点：

• 缺少合适的覆盖索引，执行过程中需要对GroupStepResults表做大量的聚簇索引键查找，额外产生了大量IO开销
• 查询执行逻辑是先全量关联4张表再做聚合，没有优先利用小表的过滤条件裁剪大表的扫描范围

可行优化方案 #

1. 新增覆盖索引（优先级最高） #

直接创建以下3个非聚簇覆盖索引，即可避免执行过程中的回表操作，减少90%以上的IO开销：

-- TaskHistories覆盖索引：优先过滤Type=0的记录，直接返回需要关联的TaskHistoryId
CREATE NONCLUSTERED INDEX IX_TaskHistories_Type_TaskHistoryId ON dbo.TaskHistories (Type, TaskHistoryId);

-- GroupStepResults覆盖索引：覆盖所有关联字段，不需要回表查聚簇索引
CREATE NONCLUSTERED INDEX IX_GroupStepResults_CoverJoin ON dbo.GroupStepResults (TaskHistoryId, GroupId, InformationId);

-- Information覆盖索引：覆盖关联和聚合需要的两个字段，避免回表读取大text字段
CREATE NONCLUSTERED INDEX IX_Information_InformationId_TimeStamp ON dbo.Information (InformationId, TimeStamp);

2. 优化SQL查询逻辑 #

调整查询逻辑，优先过滤小表再关联大表，同时用int类型的GroupId分组替代guid类型的Guid分组，进一步提升分组效率：

SELECT g.Guid AS GroupGuid, t.LastProcessed
FROM (
    -- 先做内层过滤聚合，提前裁剪数据量
    SELECT gsr.GroupId, MAX(i.TimeStamp) AS LastProcessed
    FROM TaskHistories th
    INNER JOIN GroupStepResults gsr ON th.TaskHistoryId = gsr.TaskHistoryId
    INNER JOIN Information i ON gsr.InformationId = i.InformationId
    WHERE th.Type = 0
    GROUP BY gsr.GroupId
) t
INNER JOIN Groups g ON t.GroupId = g.GroupId;

3. 可选优化（适合高频查询场景） #

如果该查询是高频查询，且数据实时性要求不高，可创建一个定时更新的汇总表，直接存储每个Group的LastProcessed时间，查询时直接读汇总表，耗时可以降到10毫秒以内。

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