SQL Server 海量資料查詢優化與分頁演算法終極方案：百萬級至千萬級實戰總結

專案背景與挑戰

• 系統： 公安「辦公自動化」系統，核心表 `TGongwen` (紅頭文件) **1000 萬筆** 資料。
• 環境： SQL Server 2000 SP3, HP ML350 G3 (雙 Xeon 2.4GHz HT, 1GB RAM, Windows 2003 Ent)。
• 痛點： 全表掃描 128 秒、傳統分頁存儲過程深分頁超時、CPU 持續 100% 成為瓶頸 (而非記憶體)。

一、因地制宜建立「適當」索引：聚集索引選型決定生死

1. 深入理解聚集 vs 非聚集索引

• 聚集索引 = 字典正文： 資料物理順序即索引順序。查範圍只需定位首尾，**極快**。
• 非聚集索引 = 部首檢字表： 索引存鍵值 + 書籤 (RID/聚集鍵)。查範圍需逐個書籤回表，**隨機 I/O 多、慢**。
• 鐵律： 每表**僅一個**聚集索引，資源極其寶貴，必須給最高價值列。

2. 何時用聚集/非聚集：決策表

3. 實測對比：聚集索引放錯地方 = 系統癱瘓

場景： 查詢近 3 個月文件 (25 萬筆)，`fariqi` (發文日期) 有 5003 個不同值，符合「少數不同值、頻繁範圍查詢」特徵。

結論： **主鍵 (GID) 做聚集索引是資源浪費** (業務從不按 ID 查範圍)。將聚集索引給 `fariqi` (日期)，查詢速度從「超時」降至「秒級」。

4. 複合聚集索引陷阱：前導列決定一切

建立複合聚集索引 `(fariqi, neibuyonghu)` (日期、發文單位)：

鐵律： 複合索引**前導列必須是最高頻篩選列**；僅用後綴列查詢需另建單列索引。

5. 其他實測發現

• 聚集索引比非聚集主鍵 `ORDER BY` 快 30% (小數據量更明顯)。
• 聚集索引範圍查詢耗時與結果集佔比**線性相關** (100 萬行 6.3 秒、50 萬行 3.2 秒、25 萬行 3.3 秒)。
• 日期列含時分秒**不影響**聚集索引範圍查詢速度 (同樣 50 萬行，精確到秒 vs 精確到日，耗時相近)。

二、改善 SQL 語句：SARGable 寫法與進階技巧

1. 查詢優化器自動優化 WHERE 順序

`WHERE name='zhangsan' AND tid>10000` vs `WHERE tid>10000 AND name='zhangsan'` **效率相同**。優化器會根據統計資訊選擇最佳存取路徑 (SARG)。

2. SARG (Search Argument) 核心規則

形式：`Column Operator Constant/Variable` (列在運算子左側)。滿足 SARG 才能 Index Seek。

3. 實測驗證：常見迷思破解

• EXISTS vs IN： 同樣子查詢，**效能完全相同** (掃描次數、邏輯讀一致)。`NOT EXISTS` 也同 `NOT IN`。
• CHARINDEX vs LIKE '%...%'： **效能完全相同** (同樣掃描計數、邏輯讀、耗時)。函數無法神奇繞過掃描。
• UNION vs OR：
  • 不同列 `OR` → `UNION` **大幅提升** (68 秒 → 9 秒，邏輯讀 40萬 → 6.7萬)。
  • **相同列 `OR`** (如 `fariqi='A' OR fariqi='B'`) → `UNION` **反而變慢** (6.4 秒 → 11.6 秒)。優化器對相同列 OR 已優化為索引合併/範圍掃描。
• SELECT * 危害： 少查一欄 (`reader` 900 字元) 速度提升 3 倍 (4.6 秒 → 1.3 秒 → 0.08 秒)。**按需取列**。
• COUNT(*) 不慢： `COUNT(*)` ≈ `COUNT(PK)` (1.5 秒) < `COUNT(其他列)` (長度越長越慢)。優化器自動選最小列統計。
• ORDER BY 聚集索引列 = 不排序速度： 物理順序即邏輯順序，無額外排序開銷。
• TOP 關鍵字威力： `SELECT TOP 10 * FROM (SELECT TOP 10000 ...) ...` 外層只回 10 行，**物理 I/O 決定耗時**，外層比內層快。善用 `TOP` 限制回傳量。

三、分頁演算法演進：從游標到 Keyset Pagination 的終極方案

方案演進對比 (1000 萬筆，主鍵 GID 非聚集，按 GID 排序)

方案 3 核心邏輯 (Keyset Pagination / Seek Method)

-- 升序分頁 (第 @PageIndex 頁，每頁 @PageSize)
SELECT TOP @PageSize *
FROM TGongwen
WHERE GID > (
    SELECT MAX(GID) FROM (
        SELECT TOP ((@PageIndex - 1) * @PageSize) GID 
        FROM TGongwen 
        ORDER BY GID
    ) AS T
)
ORDER BY GID;

-- 降序分頁
SELECT TOP @PageSize *
FROM TGongwen
WHERE GID < (
    SELECT MIN(GID) FROM (
        SELECT TOP ((@PageIndex - 1) * @PageSize) GID 
        FROM TGongwen 
        ORDER BY GID DESC
    ) AS T
)
ORDER BY GID DESC;

原理： 利用 **唯一、有序、可比較** 的鍵值 (主鍵/聚集索引列) 作為「分水嶺」，透過 `>` / `<` 運算子 (滿足 SARG) 直接定位下一頁起始位置，**完全避開 `NOT IN/EXISTS` 反向掃描**。

通用分頁存儲過程 `pagination3` (含總數統計、升降序、條件篩選)

CREATE PROCEDURE pagination3
    @tblName       VARCHAR(255),      -- 表名
    @strGetFields  VARCHAR(1000) = '*', -- 回傳欄位
    @fldName       VARCHAR(255) = '',   -- 排序欄位 (必須唯一，建議 PK/聚集鍵)
    @PageSize      INT = 10,
    @PageIndex     INT = 1,
    @doCount       BIT = 0,          -- 1=回傳總筆數
    @OrderType     BIT = 0,          -- 1=DESC, 0=ASC
    @strWhere      VARCHAR(1500) = ''  -- WHERE 條件 (不含 WHERE 關鍵字)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400);

    -- 1. 總數統計 (可選)
    IF @doCount != 0
    BEGIN
        SET @strSQL = 'SELECT COUNT(*) AS Total FROM [' + @tblName + ']' + 
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END;
        EXEC(@strSQL);
        RETURN; -- 只回總數
    END

    -- 2. 構建排序與比較運算子
    IF @OrderType != 0
    BEGIN
        SET @strTmp = '< (SELECT MIN(';  -- 降序：找上一頁最小值
        SET @strOrder = ' ORDER BY [' + @fldName + '] DESC';
    END
    ELSE
    BEGIN
        SET @strTmp = '> (SELECT MAX(';  -- 升序：找上一頁最大值
        SET @strOrder = ' ORDER BY [' + @fldName + '] ASC';
    END

    -- 3. 第一頁直走 TOP (最快)
    IF @PageIndex = 1
    BEGIN
        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields + 
                      ' FROM [' + @tblName + ']' + 
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END + 
                      @strOrder;
    END
    ELSE
    BEGIN
        -- 4. 非第一頁：Keyset 定位
        DECLARE @skipRows INT = (@PageIndex - 1) * @PageSize;
        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields + 
                      ' FROM [' + @tblName + '] ' +
                      ' WHERE [' + @fldName + '] ' + @strTmp + '[' + @fldName + ']) FROM (' +
                      ' SELECT TOP ' + CAST(@skipRows AS VARCHAR) + ' [' + @fldName + '] ' +
                      ' FROM [' + @tblName + ']' + 
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END + 
                      @strOrder + ') AS tblTmp)' +
                      CASE WHEN @strWhere != '' THEN ' AND ' + @strWhere ELSE '' END + 
                      @strOrder;
    END

    EXEC(@strSQL);
END

四、聚集索引的重要性與終極選型策略

分頁與查詢優化的共同核心：聚集索引

• 查詢優化：聚集索引**最快縮小範圍** (範圍掃描)。
• 分頁排序：聚集索引**最快排序/定位** (物理順序 = 邏輯順序，Keyset Pagination 依賴 `MAX/MIN` 瞬時定位)。

矛盾與解決：業務查詢列 ≠ 分頁排序列

案例： 業務高頻按 `fariqi` (日期) 篩選 → 聚集索引宜給 `fariqi`。但分頁需按唯一鍵 `GID` 排序 → `fariqi` 有重複值，無法 `MAX/MIN` 定位。

完美解決方案：新增高精度時間戳列做聚集索引

1. 新增列 `CreateTimeStamp` `DATETIME` `DEFAULT GETDATE()` (精確到毫秒/微秒)。
2. 加 `UNIQUE` 約束 (極小概率衝突仍防禦)。
3. **將聚集索引移至 `CreateTimeStamp`**。

雙贏效果：

• 查詢：仍可用 `CreateTimeStamp` 做日期範圍查詢 (精度更高，效能不減)。
• 分頁：`CreateTimeStamp` 唯一、有序，完美支援 Keyset Pagination (`> MAX` / `< MIN`)。
• 實測：大小數據量分頁**穩定在幾十毫秒甚至 0 毫秒**，日期範圍查詢無遲鈍。

聚集索引選型黃金法則

1. 最高頻、最強篩選力的範圍查詢列 (縮小掃描範圍)。
2. 最高頻、需排序/分頁的唯一有序列 (支援 Keyset Pagination)。
3. 若單列無法同時滿足，**新增衍生列** (如高精度時間戳) 兼顧兩者。

五、硬體瓶頸觀察：CPU 而非記憶體

實測 1000 萬筆複雜查詢/分頁，**CPU 持續 70%-100% (雙核超執緒)**，記憶體佔用平穩 (1GB 足夠)。結論：SQL Server 大量邏輯讀/排序/雜湊/並行計算為 **CPU 密集型**。升級 CPU 核心數/主頻、優化並行度 (`MAXDOP`)、減少編譯/重編譯 (參數化查詢、計畫指南) 為擴容關鍵。

總結：海量資料優化核心清單

1. **聚集索引給對的列** (業務範圍查詢 + 分頁排序雙贏，必要時加衍生列)。
2. **非聚集索引輔助** 高頻點查詢/連接列 (含覆蓋索引)。
3. **SQL 寫法 SARGable** (列不運算、拒負向邏輯、按需取列、善用 TOP/Keyset Pagination)。
4. **統計資訊自動/定時更新** (啟用 `AUTO_UPDATE_STATISTICS`)。
5. **分頁採用 Keyset Pagination (方案 3)**，棄用 `NOT IN/EXISTS`、游標、臨時表。
6. **監控 CPU 瓶頸**，優化並行度、減少重編譯、考慮擴展 CPU 資源。

最終驗證： 同一 1000 萬筆環境，優化前首頁載入超時，優化後 **分頁穩定 < 10ms，範圍查詢 < 3s**，系統正式上線運行至今穩定。