SQL Server 2005 索引視圖效能優化實戰指南（上）

一、索引視圖核心原理：從虛擬到物理的質變

傳統視圖僅是儲存的 `SELECT` 定義 (元資料)，每次引用時由優化器展開、重新執行底層聯結/聚合。索引視圖 在視圖上建立唯一叢集索引，將結果集物質化持久化至資料頁，實現「預計算、共享重用」：

• 寫入時維護：基表 DML 自動同步更新索引視圖 (單一交易內)
• 讀取時零成本：直接掃描索引葉層，避免重複聯結/聚合運算
• 優化器自動匹配：Enterprise/Developer 版查詢無需引用視圖名，優化器自動判斷是否替換

二、適用場景判斷矩陣

三、索引視圖強制限制清單 (必讀)

視圖定義必須滿足所有條件，才能建立唯一叢集索引：

1. ANSI_NULLS、QUOTED_IDENTIFIER、CONCAT_NULL_YIELDS_NULL、ARITHABORT、NUMERIC_ROUNDABORT 等選項正確設定
2. 視圖定義不得引用：其他視圖、衍生表、行集函數、UNION、DISTINCT (聚合除外)、TOP、ORDER BY
3. 所有函數必須為決定性 (如 `ISNULL`、`COALESCE`、`DATEADD`)；禁止 `GETDATE()`、`RAND()`、`NEWID()`、`@@SPID` 等
4. 聚合函數僅允許：SUM、COUNT_BIG、AVG、MIN、MAX；必須包含 COUNT_BIG(*)
5. GROUP BY 可選；若無則視圖為單一資料列 (標量聚合)
6. 參考表必須以兩部分名稱 `schema.table` 指定；同一資料庫內
7. 視圖擁有者與基表擁有者相同 (Schema 綁定)
8. 建立索引前需 `WITH SCHEMABINDING`

四、建立索引視圖標準範本

SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
GO

CREATE VIEW dbo.SalesSummary
WITH SCHEMABINDING
AS
SELECT
    ProductID,
    OrderDate,
    COUNT_BIG(*) AS Cnt,
    SUM(Quantity) AS TotalQty,
    SUM(UnitPrice * Quantity) AS TotalAmt
FROM dbo.SalesOrderDetail sod
JOIN dbo.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY ProductID, OrderDate;
GO

-- 唯一叢集索引 (鍵含 GROUP BY 欄位)
CREATE UNIQUE CLUSTERED INDEX IDX_SalesSummary_Prod_Date
ON dbo.SalesSummary (ProductID, OrderDate);
GO

-- 可選：非叢集索引支援其他查詢模式
CREATE NONCLUSTERED INDEX IDX_SalesSummary_TotalAmt
ON dbo.SalesSummary (TotalAmt) INCLUDE (TotalQty);

五、優化器自動匹配機制深度解析

Enterprise / Developer 版

• 查詢 無需引用視圖名稱
• 優化器在「匹配階段」將查詢邏輯樹與索引視圖定義樹進行同構比對
• 若視圖涵蓋查詢所需欄位/聯結/聚合，且成本更低 → 自動替換
• 可用 OPTION (EXPAND VIEWS) 強制展開 (不使用視圖索引)

Standard / Express / Workgroup 版

• 必須在查詢中明確引用視圖名稱
• 並加上 WITH (NOEXPAND) 提示，否則優化器仍會展開視圖定義
• 範例：SELECT * FROM dbo.SalesSummary WITH (NOEXPAND) WHERE ProductID = 100;

六、非叢集索引策略：支援多樣查詢模式

唯一叢集索引鍵通常對應 `GROUP BY` 欄位。當查詢含非鍵欄位篩選/排序時，需輔助索引：

• 覆蓋索引：`INCLUDE` 常用 SELECT 欄位，避免書籤查找
• 篩選索引 (2008+)：`WHERE TotalAmt > 10000` 替代全視圖索引
• 索引維護成本：每個非叢集索引增加 DML 開銷；監控 `sys.dm_db_index_usage_stats` 評估 ROI

七、實戰效能基準：百萬級訂單明細測試

測試環境：SQL Server 2005 SP4, 8-core, 32GB RAM, 100萬筆 SalesOrderDetail, 3萬筆 SalesOrderHeader

八、常見陷阱與避坑指南

1. 忘記 COUNT_BIG(*)：視圖定義缺少此欄位 → 無法建立叢集索引
2. 浮點數聚合：`SUM(FloatCol)` 非決定性 → 改用 `DECIMAL`/`MONEY`
3. 外鍵未索引：基表聯結欄位無索引 → 視圖維護時掃描基表
4. 過度索引：單視圖超過 5 個非叢集索引 → 寫入延遲激增
5. 版本限制誤解：Standard 版未加 `NOEXPAND` → 優化器展開視圖，效能反而下降

九、監控與維護腳本

-- 檢視視圖索引使用統計
SELECT
    OBJECT_NAME(s.object_id) AS ViewName,
    i.name AS IndexName,
    s.user_seeks, s.user_scans, s.user_lookups,
    s.last_user_seek, s.last_user_scan
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsIndexedView') = 1;

-- 視圖定義與選項檢查
SELECT
    OBJECT_NAME(object_id) AS ViewName,
    uses_ansi_nulls, uses_quoted_identifier,
    is_schema_bound, has_unchecked_assembly_data
FROM sys.views
WHERE OBJECTPROPERTY(object_id, 'IsIndexedView') = 1;

下篇將進階探討：分區視圖與索引視圖結合、參數化查詢下的匹配失效分析、資料倉庫 ETL 階段利用索引視圖加速彙總、以及遷移至 SQL Server 2012+ 後的柱存索引替代方案對比。