SQL Server大表MAX()查询慢但窗口函数写法快的原因咨询

首先要明确：你当前用的窗口函数写法本身逻辑存在缺陷，性能好是优化器简化执行逻辑+测试数据巧合共同导致的，结果不具备通用性，不要直接上生产。

• 两种传统写法慢的核心原因
  不管是SELECT MAX(tm2.ID)的聚合写法，还是SELECT TOP 1 ID ... ORDER BY ID DESC的写法，都属于相关子查询：执行逻辑是外层查询每匹配到一条MsgType='LV_BLIM'的记录，就单独触发一次子查询，到TBLMSG_NICHOLAS表中筛选对应GroupID、msgtype为LV_ALLOC的记录计算最大值。如果表上没有针对(msgtype, GroupID, ID)建覆盖索引，单次子查询就需要做全表扫描或者大范围索引扫描，外层符合条件的行数越多，总执行次数就越多，8分钟的耗时就是反复扫描大表堆出来的。
• 窗口函数写法快的真实原因
  你写的max(tm2.ID) OVER (PARTITION BY ID)逻辑本身不成立：分区键和聚合计算的字段都是ID，相当于每一行单独作为一个分区，计算得到的最大值就是当前行自身的ID，根本不是你需要的「同GroupID下的最大ID」。
  SQL Server查询优化器在生成执行计划时，会识别出这个窗口计算没有实际意义，直接把整个子查询的逻辑做简化，甚至不会逐行计算匹配最大值，只需要做轻量的存在性校验就能返回结果，扫描的数据量比前两种写法低几个数量级，才会出现1秒返回的情况。

风险提示：这个窗口函数写法的结果完全不可靠。只要同一个GroupID下存在2条及以上msgtype为LV_ALLOC的记录，子查询就会返回多行值，直接触发「子查询返回的值不止一个」的运行时错误；就算没有触发报错，返回的也不是你预期的最大ID，只是测试数据分布碰巧让结果和预期一致而已。

不要依赖错误写法的偶然高性能，从索引和查询结构两方面调整即可拿到稳定的毫秒级性能：

• 给TBLMSG_NICHOLAS表创建匹配查询逻辑的覆盖索引，索引键顺序参考：(msgtype, GroupID, ID DESC)。索引建好后，不管是MAX聚合还是TOP 1的写法，都可以通过索引Seek直接定位到每个GroupID对应的最大ID，不需要扫描多余数据。
• 避免在JOIN关联条件中写逐行执行的相关子查询，先通过一次表扫描预计算出所有GroupID对应的最大ID，再和主表关联，参考代码如下：

-- 单次扫描表，预聚合所有需要的组最大ID
WITH GroupAllocMaxID AS (
    SELECT 
        GroupID, 
        MAX(ID) AS MaxLVAllocID
    FROM [dbo].[TBLMSG_NICHOLAS] WITH (NOLOCK)
    WHERE msgtype = 'LV_ALLOC'
    GROUP BY GroupID
)
-- 主查询直接关联预聚合结果，避免逐行计算
INNER JOIN LongviewHoldTable lvhold WITH (NOLOCK) 
    ON lvhold.MsgID = CASE 
        WHEN tm.MsgType = 'LV_BLIM' THEN g.MaxLVAllocID
        ELSE tm.ID
    END
LEFT JOIN GroupAllocMaxID g 
    ON g.GroupID = tm.GroupID
WHERE
    TA.TARGETTASKID IS NOT NULL AND
    TA.RESPONSE IS NULL

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