如何优化含CTE的分页查询？解决重函数条件性能瓶颈

问题背景 #

现有如下简化后的CTE分页查询，核心是基于SortDateColumn排序实现每页10行的分页，但WHERE子句中的FIRST_HEAVY_FUNCTION和SECOND_HEAVY_FUNCTION是性能瓶颈——保留时查询耗时可达4分钟，移除后仅需8-20秒。已通过索引优化将耗时从15分钟降至当前水平，但仍需进一步优化：

WITH CTE
(
      Columns,
      DeliverDate,
      LastReplayDate
)
AS
(
      SELECT IIF(LastReplayDate IS NULL, IIF(LastReplayDate>= DeliverDate, LastReplayDate,DeliverDate),LastReplayDate) AS SortDateColumn,R.* FROM
             (
                    SELECT 
                    Columns,
                    DeliverDate,
                    LastReplayDate
                    FROM MY_TABLE

                    WHERE
                           CONDITIONS
                           AND ( FIRST_HEAVY_FUNCTION)
                           AND ( SECOND_HEAVY_FUNCTION)
             ) R

             ORDER BY SortDateColumn DESC
                    OFFSET (@CurrentPageIndex - 1) * @PageSize ROWS
             FETCH NEXT 10 ROWS ONLY
)
SELECT CTE. *
FROM CTE
OPTION (RECOMPILE);

关于将重函数移到CTE外部的可行性 #

可以实现，但直接移到CTE外部会导致分页逻辑先取10行再过滤，大概率返回不足10条符合条件的记录。因此需要调整逻辑，通过批量取候选数据并过滤，直到凑够10条有效记录，思路如下：

1. 用临时表/表变量存储候选数据，每次取一批远大于10条的未处理数据（比如50条）
2. 对候选数据应用重函数过滤，筛选出符合条件的记录
3. 如果筛选后的数据不足10条，继续取下一批候选数据，直到满足10条或无更多数据

示例简化代码：

DECLARE @Result TABLE (Columns ..., DeliverDate DATE, LastReplayDate DATE, SortDateColumn DATE)
DECLARE @Offset INT = (@CurrentPageIndex - 1) * @PageSize
DECLARE @BatchSize INT = 50 -- 可根据实际调整批量大小
DECLARE @TotalFound INT = 0
DECLARE @CurrentBatchOffset INT = @Offset

WHILE @TotalFound < 10
BEGIN
    -- 取一批候选数据，暂不应用重函数
    INSERT INTO @Result
    SELECT TOP (@BatchSize)
           IIF(LastReplayDate IS NULL, IIF(LastReplayDate>= DeliverDate, LastReplayDate,DeliverDate),LastReplayDate) AS SortDateColumn,
           Columns,
           DeliverDate,
           LastReplayDate
    FROM MY_TABLE
    WHERE CONDITIONS
    ORDER BY SortDateColumn DESC
    OFFSET @CurrentBatchOffset ROWS
    FETCH NEXT @BatchSize ROWS ONLY

    -- 过滤不符合重函数条件的记录，留存有效数据
    DELETE FROM @Result
    OUTPUT deleted.* INTO #FinalResult
    WHERE NOT (FIRST_HEAVY_FUNCTION) OR NOT (SECOND_HEAVY_FUNCTION)

    SET @TotalFound = (SELECT COUNT(*) FROM #FinalResult)
    SET @CurrentBatchOffset += @BatchSize

    -- 无更多候选数据则退出循环
    IF @@ROWCOUNT = 0 BREAK
END

-- 返回最多10条有效结果
SELECT TOP 10 * FROM #FinalResult
ORDER BY SortDateColumn DESC

其他优化方案 #

1. 重函数结果预计算与持久化 #

把FIRST_HEAVY_FUNCTION和SECOND_HEAVY_FUNCTION的计算结果提前存储到MY_TABLE的新增字段中（比如IsFirstValid、IsSecondValid），通过以下方式维护：

• 新增字段后，一次性批量计算历史数据的结果
• 创建触发器，在数据插入/更新时自动计算并更新这两个字段
• 用定时任务（如SQL Agent作业）定期同步计算结果
  查询时直接用这两个字段过滤，避免实时调用重函数。

2. 重函数改写优化 #

• 如果是标量值函数，改写成内联表值函数——标量函数逐行计算性能极差，内联表值函数可被查询优化器展开，大幅提升性能
• 拆解函数内部逻辑，用纯SQL语句替代复杂计算，减少不必要的步骤

3. 索引优化升级 #

创建覆盖索引，包含排序字段、过滤条件字段以及需要返回的列，让查询完全走索引，避免回表：

-- 若SortDateColumn是计算列，可先持久化再加入索引
CREATE NONCLUSTERED INDEX IX_MY_TABLE_Pagination ON MY_TABLE (CONDITIONS字段1, CONDITIONS字段2, SortDateColumn DESC)
INCLUDE (Columns, DeliverDate, LastReplayDate)

4. 替换OFFSET/FETCH为键集分页 #

OFFSET/FETCH在页码较大时需扫描前面所有行，性能急剧下降。改用键集分页，基于上一页最后一条记录的SortDateColumn值定位下一页：

-- 假设上一页最后一条的SortDateColumn值为@LastSortDate
SELECT TOP 10
       IIF(LastReplayDate IS NULL, IIF(LastReplayDate>= DeliverDate, LastReplayDate,DeliverDate),LastReplayDate) AS SortDateColumn,
       Columns,
       DeliverDate,
       LastReplayDate
FROM MY_TABLE
WHERE CONDITIONS
  AND FIRST_HEAVY_FUNCTION
  AND SECOND_HEAVY_FUNCTION
  AND SortDateColumn < @LastSortDate -- 有重复值时可调整为<=
ORDER BY SortDateColumn DESC

这种方式可直接利用索引定位，性能远优于OFFSET/FETCH。

5. 查询计划与统计信息优化 #

• 更新表统计信息：UPDATE STATISTICS MY_TABLE，确保查询优化器生成最优执行计划
• 查看执行计划，排查全表扫描、键查找等低效操作，针对性调整索引或查询逻辑
• 若大部分查询集中在前几页，可添加OPTION (OPTIMIZE FOR (@CurrentPageIndex = 1))，让优化器针对高频场景生成计划

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