SQL Server 2000 索引結構完整指南：從原理到實戰的效能優化大全

SQL Server 2000 索引架構與效能優化：理論、實戰、最佳實務全景圖

一、淺顯易懂的索引結構類比

聚集索引 = 字典正文

• 字典按拼音排序，正文本身就是目錄
• 查「安」→ 翻前部；查「張」→ 翻後部
• **資料物理順序 = 索引順序**，無需額外查找
• 每表**僅能有一個**（目錄只能按一種方式排序）

非聚集索引 = 部首檢字表

• 不認識字音時，查部首 → 頁碼 → 翻頁
• 檢字表順序（馳→張→弩）≠ 正文順序
• **兩步驟**：查索引 → 回表取資料
• 可建立多個（多種檢索方式）

二、聚集 vs 非聚集索引選擇決策表

範圍查詢為何聚集索引極快？

日期欄位建聚集索引 → 查 2004/1/1-10/1 只需定位首尾 → 直接讀取連續頁面。非聚集索引需：查索引每筆頁碼 → 隨機讀取頁面。

三、實戰索引誤區與修正

誤區 1：主鍵預設聚集索引 = 資源浪費

• ID 自增主鍵業務不可知，無查詢價值
• 違反「大量不同值不宜建聚集索引」原則
• 真實業務查詢核心：**日期** + **用戶名**

實測對比（1000萬筆，近 3 月 25萬筆）

相同結果集，**聚集索引選擇正確提速 22-53 倍**。

誤區 2：建立索引必提速 → 索引類型關鍵

同一欄位、同一查詢，**聚集 vs 非聚集**效能天壤之別。fariqi 5003 個不同值，完美符合聚集索引「不重不疏」原則。

誤區 3：所有重要欄位塞進聚集索引 → 複合索引陷阱

測試複合聚集索引（fariqi + neibuyonghu）：

**鐵律：複合索引前導欄必須是最頻繁查詢欄位**。

四、書本沒有的索引實戰經驗

1. 聚集索引比非聚集主鍵快

WHERE fariqi='2004-9-16' (聚集)     -- 3326ms
WHERE gid<=250000 (主鍵非聚集)       -- 4470ms

快近 1/4。

2. 聚集索引 ORDER BY 更快，小數據量差異大

ORDER BY fariqi (聚集)  -- 12936ms
ORDER BY gid (主鍵)     -- 18843ms

快 3/10。小數據量差異更明顯。

3. 聚集索引範圍查詢時間與資料比例成正比

fariqi > '2004-1-1'          (100萬筆) -- 6343ms
fariqi > '2004-6-6'          (50萬筆)  -- 3170ms
fariqi = '2004-9-16'         (25萬筆)  -- 3326ms
fariqi > '2004-1-1' AND < '2004-6-6' (50萬筆) -- 3280ms

4. 日期精度不影響查詢速度

同樣 50 萬筆：精確到日 vs 精確到秒 → 查詢耗時無顯著差異（6390ms vs 6453ms）。

五、查詢優化器與 SARG 完整實戰（同文章 86507 核心內容）

參考前文「SQL Server 索引結構與查詢優化（二）」第 3 節 11 大實戰法則。

六、分頁算法演進四代方案（同文章 86496 核心內容）

參考前文「SQL Server 分頁查詢優化實戰」第 3-4 節。

七、通用高效分頁存儲過程 pagination3（生產級完整版）

CREATE PROCEDURE pagination3
    @tblName VARCHAR(255), @strGetFields VARCHAR(1000) = '*',
    @fldName VARCHAR(255) = '', @PageSize INT = 10, @PageIndex INT = 1,
    @doCount BIT = 0, @OrderType BIT = 0, @strWhere VARCHAR(1500) = ''
AS
BEGIN
    DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400)
    IF @doCount != 0
    BEGIN
        IF @strWhere != '' SET @strSQL = "SELECT COUNT(*) AS Total FROM [" + @tblName + "] WHERE " + @strWhere
        ELSE SET @strSQL = "SELECT COUNT(*) AS Total FROM [" + @tblName + "]"
    END
    ELSE
    BEGIN
        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
        IF @PageIndex = 1
        BEGIN
            IF @strWhere != '' SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strGetFields + " FROM [" + @tblName + "] WHERE " + @strWhere + " " + @strOrder
            ELSE SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strGetFields + " FROM [" + @tblName + "] " + @strOrder
        END
        ELSE
        BEGIN
            SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strGetFields + " FROM [" + @tblName + "] WHERE [" + @fldName + "]" + @strTmp + "([" + @fldName + "]) FROM (SELECT TOP " + STR((@PageIndex-1)*@PageSize) + " [" + @fldName + "] FROM [" + @tblName + "]" + @strOrder + ") AS tblTmp)" + @strOrder
            IF @strWhere != '' SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strGetFields + " FROM [" + @tblName + "] WHERE [" + @fldName + "]" + @strTmp + "([" + @fldName + "]) FROM (SELECT TOP " + STR((@PageIndex-1)*@PageSize) + " [" + @fldName + "] FROM [" + @tblName + "] WHERE " + @strWhere + " " + @strOrder + ") AS tblTmp) AND " + @strWhere + " " + @strOrder
        END
    END
    EXEC(@strSQL)
END

八、聚集索引重要性總結與選擇法則

參考文章 86506 核心結論：新增高精度時間欄位作聚集索引，同時滿足範圍查詢與分頁排序雙重需求。

九、結語：CPU 為大數據量查詢核心瓶頸

• 測試環境：雙 Xeon 2.4GHz HT, 1GB RAM, Win2003, SQL 2000 SP3
• 大量查詢時 CPU 持續 70-100%，記憶體穩定
• 優化方向：減少 CPU 運算（掃描、排序、函數計算），而非單純加記憶體

附錄：索引優化檢查清單

1. ☐ 每表是否有且僅有一個聚集索引？
2. ☐ 聚集索引是否建在最頻繁範圍查詢/排序欄位？
3. ☐ 複合索引前導欄是否為最高頻查詢欄位？
4. ☐ 是否避免在高頻更新欄位建聚集索引？
5. ☐ 非聚集索引是否覆蓋高頻查詢欄位（含 INCLUDE）？
6. ☐ 定期檢查索引碎片（>30% 重組，>50% 重建）？
7. ☐ 統計資料是否自動/定期更新？
8. ☐ 是否移除未使用/重複索引？