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

SQL Server 索引檢視表實戰：從建立到效能驗證的完整指南

編輯：關於SqlServer

前言

一般檢視表只是儲存查詢定義，不佔用儲存空間，每次查詢即時運算。索引檢視表 透過建立唯一叢集索引，將結果集**實體化** 儲存於資料庫，並在基底表異動時自動維護，極大提升彙總查詢效能。本文完整示範建立流程、必要條件、效能對比與權衡考量。

建立索引檢視表的嚴格前提條件

檢視表定義必須滿足所有條件，才能建立索引：

WITH SCHEMABINDING：綁定結構描述，防止基底表結構變更導致檢視失效
引用基底表必須使用**兩部分名稱** (schema.table)
不得使用 SELECT *，必須明確列出欄位
聚合函數必須使用 COUNT_BIG(*) 而非 COUNT(*)
不得包含 OUTER JOIN、DISTINCT、TOP、UNION、子查詢、ORDER BY 等
所有運算式必須為確定性
Session 設定需固定：ANSI_NULLS ON, ANSI_PADDING ON, ANSI_WARNINGS ON, CONCAT_NULL_YIELDS_NULL ON, QUOTED_IDENTIFIER ON, NUMERIC_ROUNDABORT OFF

完整建立範例

1. 建立基底表與測試資料


CREATE TABLE SalesHistory (
    SaleID INT IDENTITY(1,1),
    Product VARCHAR(30),
    SaleDate SMALLDATETIME,
    SalePrice MONEY
);

DECLARE @i SMALLINT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('Computer', DATEADD(MM, @i, '20060310'), DATEPART(MS, GETDATE()) + @i + 57);
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('BigScreen', DATEADD(MM, @i, '20060302'), DATEPART(MS, GETDATE()) + @i + 13);
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('PoolTable', DATEADD(MM, @i, '20070211'), DATEPART(MS, GETDATE()) + @i + 29);
    SET @i = @i + 1;
END
-- 共 30,000 列

2. 一般檢視表 (無索引)


CREATE VIEW vw_SalesByProduct
AS
SELECT Product, COUNT_BIG(*) AS ProductCount, SUM(SalePrice) AS TotalSales
FROM dbo.SalesHistory
GROUP BY Product;

查詢 WHERE Product = 'Computer' → **表格掃描**，掃描 30,000 列。

3. 索引檢視表 (含唯一叢集索引)


-- 必須先設定 Session 選項 (SSMS 預設通常已符合)
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO

CREATE VIEW dbo.vw_SalesByProduct_Indexed
WITH SCHEMABINDING
AS
SELECT Product,
       COUNT_BIG(*) AS ProductCount,
       SUM(ISNULL(SalePrice, 0)) AS TotalSales  -- ISNULL 確保確定性
FROM dbo.SalesHistory
GROUP BY Product;
GO

-- 建立唯一叢集索引 (實體化關鍵步驟)
CREATE UNIQUE CLUSTERED INDEX idx_SalesView ON vw_SalesByProduct_Indexed (Product);
GO

4. 驗證實體化與空間佔用


EXEC sp_spaceused 'vw_SalesByProduct_Indexed';
/* 結果範例：
name                      rows    reserved   data       index_size unused
vw_SalesByProduct_Indexed 3       16 KB      8 KB       8 KB       0 KB
*/

僅 3 列 (三種產品)，佔用 16 KB，確認已實體化。

效能對比驗證


-- 一般檢視表：表格掃描
SET STATISTICS IO ON;
SELECT Product, TotalSales, ProductCount
FROM vw_SalesByProduct
WHERE Product = 'Computer';
-- Table 'SalesHistory'. Scan count 1, logical reads 124...

-- 索引檢視表：叢集索引搜尋
SELECT Product, TotalSales, ProductCount
FROM vw_SalesByProduct_Indexed
WHERE Product = 'Computer';
-- Table 'vw_SalesByProduct_Indexed'. Scan count 1, logical reads 3...

邏輯讀從 124 降至 3，效能提升 ~40 倍。

維護成本與權衡分析

面向	一般檢視表	索引檢視表
查詢效能 (彙總/聯集)	即時運算，大表慢	預先計算，極快
儲存空間	無	佔用資料/索引頁面
DML 影響 (INSERT/UPDATE/DELETE)	無額外成本	每筆異動需同步維護索引，增加寫入延遲
鎖定/封鎖風險	低	較高 (索引維護需鎖定)
適用場景	頻繁異動、少量查詢、臨時報表	讀多寫少、大量彙總、儀表板、資料倉庫維度查詢

進階技巧與最佳實務

非叢集輔助索引：建立叢集索引後，可再加非叢集索引支援其他查詢模式
參數化查詢：應用端使用參數化，避免編譯計畫因字面值不同而無法重用
查詢提示 NOEXPAND (Enterprise Edition)：
```
SELECT * FROM vw_SalesByProduct_Indexed WITH (NOEXPAND) WHERE Product = 'Computer';
```
強制使用索引檢視表，即使查詢優化器判斷展開較佳
分區對齊：大型資料倉庫可將索引檢視表建立在分區結構描述上
監控維護開銷：sys.dm_db_index_operational_stats 觀察 leaf_insert_count, leaf_delete_count, leaf_update_count