SQL Server百万级数据分页查询同时获取总计数的高效方法

• 拆分查询，分别获取分页数据与总计数
  把原查询拆分为两个逻辑：先处理筛选/连接得到中间数据集，再分别获取分页结果和总计数，避免窗口函数强制扫描全表。
  示例代码（使用临时表复用中间结果，适合复杂筛选场景）：

  DECLARE @pageNum  INT = 1;
  DECLARE @pageSize INT = 25; 
  DECLARE @totalCount INT;
  
  -- 临时表存储筛选后的数据
  SELECT t1.col1, t1.col2, t2.col3, t2.col4
  INTO #TempFiltered
  FROM mytable t1
  LEFT JOIN mysecondtable t2 ON t1.col1 = t2.col3
  -- 此处添加你的筛选条件
  
  -- 获取总计数
  SELECT @totalCount = COUNT(*) FROM #TempFiltered;
  
  -- 获取分页数据并带上总计数
  SELECT col1, col2, col3, col4, @totalCount AS totalCount
  FROM #TempFiltered
  ORDER BY col1
  OFFSET (@pageNum-1)*@pageSize ROWS
  FETCH NEXT @pageSize ROWS ONLY;
  
  -- 清理临时表
  DROP TABLE #TempFiltered;
  

  若筛选逻辑简单，也可使用CTE替代临时表，减少IO开销。

• 利用系统视图获取近似总计数（适合非精确场景）
  如果业务允许使用近似值，可通过sys.dm_db_partition_stats快速获取表的总行数，无需扫描全表，速度极快。
  示例代码：

  DECLARE @pageNum  INT = 1;
  DECLARE @pageSize INT = 25; 
  DECLARE @approxTotal INT;
  
  -- 获取近似总计数
  SELECT @approxTotal = SUM(row_count)
  FROM sys.dm_db_partition_stats
  WHERE object_id = OBJECT_ID('mytable') 
    AND index_id < 2; -- 聚集索引或堆表
  
  -- 获取分页数据并带上近似计数
  SELECT 
      t1.col1, t1.col2, t2.col3, t2.col4,
      @approxTotal AS totalCount
  FROM mytable t1
  LEFT JOIN mysecondtable t2 ON t1.col1 = t2.col3
  -- 添加筛选条件
  ORDER BY t1.col1
  OFFSET (@pageNum-1)*@pageSize ROWS
  FETCH NEXT @pageSize ROWS ONLY;
  

• 优化索引，提升窗口函数效率
  若坚持使用原窗口函数写法，需确保查询涉及的字段有合适的索引，减少扫描范围：

  • 给mytable.col1创建非聚集索引，包含查询所需的col2字段（匹配ORDER BY和查询列）
  • 给mysecondtable.col3创建非聚集索引，包含col4字段（匹配连接条件和返回列）
    示例索引创建语句：

  CREATE NONCLUSTERED INDEX IX_mytable_col1 ON mytable(col1)
  INCLUDE (col2);
  
  CREATE NONCLUSTERED INDEX IX_mysecondtable_col3 ON mysecondtable(col3)
  INCLUDE (col4);
  

  索引生效后，窗口函数的COUNT(*) OVER()操作会利用索引快速统计，避免全表扫描。

• 改用ROW_NUMBER()结合TOP实现分页
  部分场景下，OFFSET/FETCH处理大偏移量效率较低，可结合ROW_NUMBER()实现分页，同时保留总计数：

  DECLARE @pageNum  INT = 1;
  DECLARE @pageSize INT = 25; 
  DECLARE @startRow INT = (@pageNum-1)*@pageSize + 1;
  DECLARE @endRow INT = @pageNum*@pageSize;
  
  WITH NumberedData AS (
      SELECT 
          t1.col1, t1.col2, t2.col3, t2.col4,
          ROW_NUMBER() OVER(ORDER BY t1.col1) AS RowNum,
          COUNT(*) OVER() AS totalCount
      FROM mytable t1
      LEFT JOIN mysecondtable t2 ON t1.col1 = t2.col3
      -- 添加筛选条件
  )
  SELECT col1, col2, col3, col4, totalCount
  FROM NumberedData
  WHERE RowNum BETWEEN @startRow AND @endRow;
  

  注意：若总计数仍慢，优先选择拆分查询方案。

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