您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

海量資料庫 (千萬筆) 查詢優化與分頁演算法實戰方案

編輯：關於SqlServer

場景：辦公自動化系統紅頭文件表 (TGongwen) 超過 1000 萬筆

CREATE TABLE [dbo].[TGongwen] (
    [Gid] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [title] [varchar](80) NULL,
    [fariqi] [datetime] NULL,
    [neibuYonghu] [varchar](70) NULL,
    [reader] [varchar](900) NULL  -- 逗號分隔的閱讀者清單
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

核心優化策略

1. 索引設計：覆蓋索引 + 篩選條件順序

高選擇性欄位優先：fariqi (日期)、neibuYonghu (發布單位)。
建立覆蓋索引包含 title、reader 避免書籤查找。
範例：CREATE INDEX IX_TGongwen_Date_Dept ON TGongwen(fariqi, neibuYonghu) INCLUDE (title, reader);

2. 分頁查詢演算法比較 (SQL Server 2000 環境)

方法	原理	優點	缺點
NOT IN / NOT EXISTS	排除前 N 筆 ID	邏輯直觀	大頁碼效能急劇下降
MAX(ID) 配合 TOP	記住上一頁最大 ID，下一頁 WHERE ID > @lastMax	極快、無深分頁問題	需 ID 連續遞增、不支援任意跳頁
衍生表 + ROW_NUMBER (2005+)	視窗函數編號	標準、支援任意跳頁	2000 不支援
暫存表分頁	先寫入暫存表加身分識別欄，再查詢範圍	穩定、可重複讀	額外寫入開銷

3. 針對本案例推薦：MAX(ID) 游標式分頁 (適合「下一頁」導向)

-- 第一頁
SELECT TOP 20 Gid, title, fariqi, neibuYonghu
FROM TGongwen
WHERE fariqi BETWEEN '2004-01-01' AND '2004-12-31'
  AND neibuYonghu = '通信科'
ORDER BY Gid;

-- 記住最後一筆 Gid = @lastGid
-- 下一頁
SELECT TOP 20 Gid, title, fariqi, neibuYonghu
FROM TGongwen
WHERE fariqi BETWEEN '2004-01-01' AND '2004-12-31'
  AND neibuYonghu = '通信科'
  AND Gid > @lastGid
ORDER BY Gid;

4. 任意跳頁需求：暫存表 + 身分識別欄

CREATE TABLE #PageCache (RowID INT IDENTITY(1,1), Gid INT, title VARCHAR(80), ...);
INSERT INTO #PageCache (Gid, title, ...)
SELECT Gid, title, ... FROM TGongwen WHERE ... ORDER BY Gid;

-- 第 N 頁 (每頁 20 筆)
SELECT * FROM #PageCache WHERE RowID BETWEEN (@Page-1)*20+1 AND @Page*20;