SQL Server 千萬級數據分頁查詢實戰：測試數據建構與表結構設計

千萬級資料分頁查詢優化系列（一）：測試環境建構與基礎架構

本系列探討如何在 1000 萬筆資料的 MS SQL Server 中實現快速資料提取與分頁。首先建立真實業務場景的測試表結構與海量測試資料。

核心表結構：紅頭文件表 TGongwen

CREATE TABLE [dbo].[TGongwen] (
    [Gid] [int] IDENTITY(1,1) NOT NULL,      -- 主鍵，自增 ID
    [title] [varchar](80) COLLATE Chinese_PRC_CI_AS NULL,  -- 文件標題
    [fariqi] [datetime] NULL,                -- 發文日期
    [neibuYonghu] [varchar](70) COLLATE Chinese_PRC_CI_AS NULL,  -- 發文用戶
    [reader] [varchar](900) COLLATE Chinese_PRC_CI_AS NULL     -- 閱讀用戶（逗號分隔）
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

分批次插入 1000 萬筆測試資料

第一批：最早 25 萬筆（固定日期 2004-2-5）

DECLARE @i INT
SET @i = 1
WHILE @i <= 250000
BEGIN
    INSERT INTO Tgongwen(fariqi, neibuyonghu, reader, title) 
    VALUES('2004-2-5', '通信科', 
           '通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,治安支隊,外事科',
           '這是最先的25萬條記錄')
    SET @i = @i + 1
END

第二批：中間 25 萬筆（固定日期 2004-9-16）

DECLARE @i INT
SET @i = 1
WHILE @i <= 250000
BEGIN
    INSERT INTO Tgongwen(fariqi, neibuyonghu, reader, title) 
    VALUES('2004-9-16', '辦公室',
           '辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科',
           '這是中間的25萬條記錄')
    SET @i = @i + 1
END

第三批：最後 50 萬筆（高精度時間戳，模擬真實業務）

DECLARE @h INT
SET @h = 1
WHILE @h <= 100
BEGIN
    DECLARE @i INT
    SET @i = 2002
    WHILE @i <= 2003
    BEGIN
        DECLARE @j INT
        SET @j = 0
        WHILE @j < 50
        BEGIN
            DECLARE @k INT
            SET @k = 0
            WHILE @k < 50
            BEGIN
                INSERT INTO Tgongwen(fariqi, neibuyonghu, reader, title) 
                VALUES(CAST(@i AS VARCHAR(4)) + '-8-15 3:' + 
                       CAST(@j AS VARCHAR(2)) + ':' + CAST(@k AS VARCHAR(2)),
                       '通信科',
                       '辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科',
                       '這是最後的50萬條記錄')
                SET @k = @k + 1
            END
            SET @j = @j + 1
        END
        SET @i = @i + 1
    END
    SET @h = @h + 1
END

測試資料分布特徵

註：原文程式碼透過三層迴圈（100 × 2 × 50 × 50 = 500,000）生成最後 50 萬筆，配合前兩批共 100 萬筆。實際千萬級測試需擴大迴圈倍數或執行多輪。

效能測量標準化方法

DECLARE @d DATETIME
SET @d = GETDATE()
-- 待測試的 SELECT 查詢
SELECT [執行耗時(毫秒)] = DATEDIFF(ms, @d, GETDATE())

硬體測試環境

• 伺服器：HP ML 350 G3
• CPU：雙 Intel Xeon 超執行緒 2.4GHz
• 記憶體：1GB
• OS：Windows Server 2003 Enterprise Edition
• DB：SQL Server 2000 SP3

系列文章導覽

1. 本文：測試環境建構與表結構設計
2. 索引結構原理與聚集/非聚集索引選擇策略
3. 查詢優化實戰：SARG、SQL 寫法陷阱與效能對比
4. 分頁演進：從游標到高效存儲過程的四代方案
5. 聚集索引選擇矛盾與突破：時間型聚集索引雙重效用
6. 總結：CPU 而非記憶體是大數據量查詢的關鍵瓶頸

關鍵洞察預告

• 主鍵 ≠ 最佳聚集索引：ID 自增列作聚集索引屬資源浪費
• 日期欄位作聚集索引：同時支援範圍查詢與高效排序
• 重複日期值問題：需增加毫秒精度時間欄位 + UNIQUE 約束
• 大數據量下 CPU 為核心瓶頸，非記憶體