萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 千萬級大數據分頁存儲過程:五大算法實測與二分法優化終極方案

面對 200 萬+ 測試資料集,本文實測五種經典分頁算法在不同頁碼 (第 2/1000/10000/100000/200000 頁) 的耗時表現,並引入**二分法優化**將深分頁效能提升 4-5 倍,給出生產級通用存儲過程完整代碼。

測試環境與資料

CREATE DATABASE Data_Test;
CREATE TABLE tb_TestTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    UserName NVARCHAR(20) NOT NULL,
    UserPWD NVARCHAR(20) NOT NULL,
    UserEmail NVARCHAR(40) NULL
);
-- 插入 2,000,000 筆測試資料
SET IDENTITY_INSERT tb_TestTable ON;
DECLARE @i INT = 1;
WHILE @i <= 2000000 BEGIN
    INSERT tb_TestTable(ID, UserName, UserPWD, UserEmail) 
    VALUES (@i, N'admin', N'admin888', N'test@yahoo.com.cn');
    SET @i += 1;
END
SET IDENTITY_INSERT tb_TestTable OFF;

五大算法實測 (單位:ms,每頁 10 筆,測 5 次取平均)

算法第 2 頁第 1,000 頁第 10,000 頁第 100,000 頁第 200,000 頁排名
1. TOP + NOT IN016474759533
2. TOP + MAX(ID)516353256231
3. 中間變量法9669709609459335
4. ROW_NUMBER()00343657102
5. CTE + ROW_NUMBER7807967987808054

**結論**:`TOP + MAX(ID)` (關鍵欄位有叢集索引) 效能最穩;`ROW_NUMBER()` 寫法現代、可維護、效能次之;`NOT IN` 深分頁劇烈劣化;中間變量/CTE 完全不可用。

終極優化:二分法 穿透深分頁

核心思想:根據頁碼位於前半段或後半段,動態選擇**正序向後求**或**倒序向前求**,再反轉結果,使首尾頁效能對稱。

完整存儲過程代碼 (生產級,支援多表連接、動態排序、DISTINCT、總頁數輸出)

CREATE PROCEDURE dbo.usp_PagedQuery_2Part
    @TblName       NVARCHAR(200),      -- 目標表/連接視圖
    @FldName       NVARCHAR(500) = N'*', -- 選取欄位
    @PageSize      INT = 10,
    @PageIndex     INT = 1,
    @FldSort       NVARCHAR(200) = NULL, -- 排序欄位,如 'ID ASC, CreateTime DESC'
    @SortDir       BIT = 0,             -- 0=ASC(預設), 1=DESC (最後一欄方向)
    @StrCondition  NVARCHAR(1000) = NULL, -- WHERE 條件 (不含 WHERE)
    @PK            NVARCHAR(150),       -- 主鍵/唯一鍵 (雙向定位用)
    @Distinct      BIT = 0,             -- 是否 DISTINCT
    @TotalPages    INT OUTPUT,
    @TotalRows     BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @SQLCnt NVARCHAR(MAX), @OrderBy NVARCHAR(200);
    DECLARE @StartRow BIGINT, @EndRow BIGINT, @Reverse BIT;
    
    -- 1. 總筆數
    SET @SQLCnt = N'SELECT @TotalRows = COUNT_BIG(' + CASE WHEN @Distinct=1 THEN N'DISTINCT ' + @PK ELSE N'*' END + N') FROM ' + @TblName;
    IF @StrCondition IS NOT NULL SET @SQLCnt += N' WHERE ' + @StrCondition;
    EXEC sp_executesql @SQLCnt, N'@TotalRows BIGINT OUTPUT', @TotalRows OUTPUT;
    SET @TotalPages = CEILING(@TotalRows * 1.0 / @PageSize);
    IF @PageIndex > @TotalPages SET @PageIndex = @TotalPages;
    IF @PageIndex < 1 SET @PageIndex = 1;
    
    -- 2. 判斷正/反向
    SET @Reverse = CASE WHEN @PageIndex > @TotalPages / 2 THEN 1 ELSE 0 END;
    SET @StartRow = CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex) * @PageSize + 1 ELSE (@PageIndex - 1) * @PageSize + 1 END;
    SET @EndRow   = CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex + 1) * @PageSize ELSE @PageIndex * @PageSize END;
    
    -- 3. 構建 ORDER BY (支援多欄位、最後一欄方向由 @SortDir 控制)
    SET @OrderBy = COALESCE(@FldSort, @PK + N' ASC');
    IF @Reverse = 1 
        SET @OrderBy = REPLACE(@OrderBy + N',', N' ASC,', N' DESC,')  -- 簡易反轉,生產建議解析重組
    
    -- 4. 動態 SQL (ROW_NUMBER 視窗)
    SET @SQL = N'
    ;WITH Numbered AS (
        SELECT ' + @FldName + N', ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + N') AS RN
        FROM ' + @TblName + CASE WHEN @StrCondition IS NOT NULL THEN N' WHERE ' + @StrCondition ELSE N'' END + N'
    )
    SELECT ' + @FldName + N' FROM Numbered WHERE RN BETWEEN ' + CAST(@StartRow AS NVARCHAR) + N' AND ' + CAST(@EndRow AS NVARCHAR) + N'
    ORDER BY ' + CASE WHEN @Reverse = 1 THEN N'RN DESC' ELSE N'RN ASC' END + N';';
    
    EXEC sp_executesql @SQL;
END

二分法實測效能

頁碼原 ROW_NUMBER二分法提升
第 2 頁0 ms156 ms- (首頁無需優化)
第 100,000 頁365 ms470 ms略增 (中間地帶)
第 199,999 頁710 ms156 ms4.5 倍
第 159,999 頁~550 ms296 ms近 2 倍

現代替代方案:Keyset Pagination (游標分頁)

-- 客戶端記住上一頁最後一筆的排序鍵值
SELECT TOP (@PageSize) * FROM dbo.BigTable
WHERE (SortCol1, PK) > (@LastSortCol1, @LastPK)  -- 行值比較 (2008+ 支援)
ORDER BY SortCol1, PK;
  • **無深分頁效能衰退**,適合無限滾動/APP 列表
  • **不支援跳頁**、**不支援總頁數**、**排序欄位必須唯一/含主鍵**

二分法 `ROW_NUMBER` 方案兼顧跳頁、總頁數、任意排序與深分頁效能,是傳統 Web 分頁的最佳選擇;新架構優先考慮 Keyset Pagination。

copyright © 萬盛學電腦網 all rights reserved