SQL Server分区查询性能优化：简化高效查询方案

问题分析 #

查询A写法简洁，但SQL Server优化器未能利用表的分区有序性（分区12的DateAndTime全部大于分区11，以此类推），导致需要扫描所有符合条件的行后再排序，效率极低。而查询B通过手动指定从最新分区到目标分区依次扫描并取TOP20，利用了分区的天然顺序，一旦凑够20条结果即可提前终止，因此性能优异，但写法过于繁琐。

简化方案 #

我们可以用递归CTE生成需要扫描的分区号（从12到2倒序），再通过CROSS APPLY对每个分区执行TOP20查询，最终合并结果取TOP20。这种写法既保留了查询B的高性能逻辑，又和查询A一样简洁：

WITH PartitionNumbers AS (
    -- 从最新的分区12开始
    SELECT 12 AS PartitionNum
    UNION ALL
    -- 递归生成到分区2（对应2024年2月）
    SELECT PartitionNum - 1
    FROM PartitionNumbers
    WHERE PartitionNum > 2
)
SELECT TOP 20
    e.Email,
    e.DateAndTime,
    e.Serial
FROM PartitionNumbers p
CROSS APPLY (
    -- 每个分区内取符合条件的TOP20最新记录
    SELECT TOP 20
        Email,
        DateAndTime,
        Serial
    FROM EmailIdx WITH (NOLOCK)
    WHERE
        $PARTITION.PartitionFunction(DateAndTime) = p.PartitionNum
        AND Email = 'common_email@example.com'
        AND DateAndTime < 2412230000000000000
        AND DateAndTime > 2402110000000000000
    ORDER BY DateAndTime DESC
) e
-- 最终按日期倒序取TOP20
ORDER BY e.DateAndTime DESC

方案优势 #

• 性能与查询B一致：按分区从新到旧依次扫描，每个分区内利用聚簇索引(Email, DateAndTime, Serial)的顺序，快速定位符合条件的最新20条记录，一旦合并结果够20条即可停止扫描，避免不必要的IO。
• 简洁易维护：无需手动编写11个UNION ALL，如果分区范围变化，只需修改递归CTE的起始/终止分区号即可。
• 逻辑清晰：明确体现了从最新分区开始扫描的优化思路，可读性更强。

为什么查询A无法自动优化？ #

你的聚簇索引顺序是Email ASC, DateAndTime ASC, Serial ASC，而查询需要按DateAndTime DESC排序。SQL Server优化器默认不会主动利用分区的有序性来调整扫描顺序，而是会先检索所有符合Email和DateAndTime范围的行，再执行排序操作，这在数据量较大时会产生极高的性能开销。

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